README.md
# JDBC
[](https://codeclimate.com/github/tomekw/jdbc) [](https://badge.fury.io/rb/jdbc) [](https://circleci.com/gh/tomekw/jdbc)
JDBC meets JRuby.
Please note the project supports only JRuby (tested with 9.1.7.0+) on Java 8.
The public API is subject to change before version `1.0.0`.
The library is in the alpha state. It has been tested only with the `PostgreSQL` database, only basic queries and commands were tested.
Please raise issues if any of your use cases is not working and I will be very happy to help!
## Installation
Add this line to your application's Gemfile:
```ruby
gem "jdbc"
```
And then execute:
$ bundle
Or install it yourself as:
$ gem install jdbc
## Public API
| Method | Description | Returned value |
| ----------------------------- | ------------------------------------------- | ----------------------------------------------------------- |
| `command(sql, bindings = {})` | `INSERT`s, `UPDATE`s, `DELETE`s, etc. calls | Depending on the driver, either full record or primary keys |
| `ddl(sql, bindings = {})` | `CREATE TABLES`s, `ADD INDEX`es, etc. calls | `true` or driver exception |
| `query(sql, bindings = {})` | `SELECT`s, etc. calls | Ruby `Array` of `Hashes` |
## Usage
Install the database driver, for PostgreSQL:
```ruby
gem "jdbc-postgres"
```
Load the the database driver if needed, for PostgreSQL:
```ruby
require "jdbc/postgres"
Jdbc::Postgres.load_driver
```
Configure the connection pool:
```ruby
gem "hucpa"
```
```ruby
require "hucpa"
# Using the adapter option
options = {
adapter: :postgresql,
database_name: "jdbc",
password: "jdbc",
server_name: "postgres",
username: "jdbc"
}
# Using the jdbc_url option
options = {
jdbc_url: "jdbc:postgresql://postgres/jdbc",
password: "jdbc",
username: "jdbc"
}
connection_pool = Hucpa::ConnectionPool.new(options)
gateway = JDBC::Gateway.new(connection_pool: connection_pool)
```
Query for records:
```ruby
gateway.query("SELECT * FROM things")
=> [
{
id: 1,
name: "Foo",
created_at: Time.parse("2017-02-01 10:20:45")
},
{
id: 2,
name: "Bar",
created_at: Time.parse("2017-02-01 10:21:47")
}
]
```
Query bindings can be provided:
```ruby
gateway.query("SELECT * FROM things WHERE name = :name", name: "Foo")
=> [
{
id: 1,
name: "Foo",
created_at: Time.parse("2017-02-01 10:20:45")
}
]
```
Optionally, bindings can be annotated with a [JDBC type](#jdbc-types).
It is in fact required when value can be `nil`:
```ruby
gateway.query("SELECT * FROM things WHERE name = :name:VARCHAR OR (name IS NULL AND :name:VARCHAR IS NULL)", name: nil)
=> [
{
id: 3,
name: nil,
created_at: Time.parse("2017-02-02 10:20:45")
}
]
```
Pass commands:
```ruby
gateway.command("INSERT INTO things (name, created_at) VALUES (:name, :created_at)", name: "Foo", created_at: Time.parse("2017-02-02 10:20:45"))
=> [
{
id: 4,
name: "Foo",
created_at: Time.parse("2017-02-02 10:20:45")
}
]
```
```ruby
gateway.command("UPDATE things SET name = :name WHERE id < :id", name: "Bar", id: 2)
=> [
{
id: 1,
name: "Bar",
created_at: Time.parse("2017-02-02 10:20:45")
}
]
```
```ruby
gateway.command("DELETE FROM things WHERE id = :id", id: 1)
=> [
{
id: 1,
name: "Bar",
created_at: Time.parse("2017-02-02 10:20:45")
}
]
```
Invoke DDL calls:
```ruby
gateway.ddl("CREATE INDEX name_idx ON things(name)")
=> true
```
Close the connection pool:
```ruby
connection_pool.close
```
## (Known) things that won't work (yet)
* groupping SQL queries / commands in transactions
## JDBC types
* `ARRAY`
* `BIGINT`
* `BINARY`
* `BIT`
* `BLOB`
* `BOOLEAN`
* `CHAR`
* `CLOB`
* `DATALINK`
* `DATE`
* `DECIMAL`
* `DISTINCT`
* `DOUBLE`
* `FLOAT`
* `INTEGER`
* `JAVA_OBJECT`
* `LONGNVARCHAR`
* `LONGVARBINARY`
* `LONGVARCHAR`
* `NCHAR`
* `NCLOB`
* `NULL`
* `NUMERIC`
* `NVARCHAR`
* `OTHER`
* `REAL`
* `REF`
* `REF_CURSOR`
* `ROWID`
* `SMALLINT`
* `SQLXML`
* `STRUCT`
* `TIME`
* `TIME_WITH_TIMEZONE`
* `TIMESTAMP`
* `TIMESTAMP_WITH_TIMEZONE`
* `TINYINT`
* `VARBINARY`
* `VARCHAR`
## Development
Build the Docker image:
$ docker-compose build
Create services:
$ docker-compose create
Run specs:
$ docker-compose run --rm app rspec spec
Run console:
$ docker-compose run --rm app irb
## Contributing
Bug reports and pull requests are welcome on GitHub at https://github.com/tomekw/jdbc. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the [Contributor Covenant](http://contributor-covenant.org) code of conduct.
## License
The gem is available as open source under the terms of the [MIT License](http://opensource.org/licenses/MIT).