_     _ _                 _          _                 
   (_)   | | |               | |        | |                
    _  __| | |__   ___ ______| |__   ___| |_ __   ___ _ __ 
   | |/ _` | '_ \ / __|______| '_ \ / _ \ | '_ \ / _ \ '__|
   | | (_| | |_) | (__       | | | |  __/ | |_) |  __/ |   
   | |\__,_|_.__/ \___|      |_| |_|\___|_| .__/ \___|_|   
  _/ |                                    | |              
 |__/                                     |_|              

jdbc-helper

A JDBC helper for Ruby/Database developers. JDBCHelper::Connection object wraps around a JDBC connection and provides far nicer interface to crucial database operations from primitive selects and updates to more complex ones involving batch updates, prepared statements and transactions. As the name implies, this gem only works on JRuby.

Tested on MySQL 5.5, Oracle 11g R2, PostgreSQL 9.0.4 and MS SQL Server 2008 R2.

Installation

Install gem

gem install jdbc-helper

Setting up CLASSPATH

Add the appropriate JDBC drivers to the CLASSPATH.

export CLASSPATH=$CLASSPATH:~/lib/mysql-connector-java-5.1.16-bin.jar:~/lib/ojdbc6.jar

In Ruby

require 'jdbc-helper'

Examples

Connecting to a database

# :driver and :url must be given
conn = JDBCHelper::Connection.new(
             :driver => 'com.mysql.jdbc.Driver',
             :url    => 'jdbc:mysql://localhost/test')
conn.close


# Optional :user and :password
conn = JDBCHelper::Connection.new(
             :driver   => 'com.mysql.jdbc.Driver',
             :url      => 'jdbc:mysql://localhost/test',
             :user     => 'mysql',
             :password => '')
conn.close


# MySQL shortcut connector
JDBCHelper::MySQLConnector.connect('localhost', 'mysql', '', 'test')

# Oracle shortcut connector
JDBCHelper::OracleConnector.connect(host, user, password, service_name)

# PostgreSQL shortcut connector
JDBCHelper::PostgresConnector.connect(host, user, password, db)

# MS SQL Server shortcut connector
JDBCHelper::SqlServerConnector.connect(host, user, password, db)

Querying database table

conn.query("SELECT a, b, c FROM T") do |row|
  row.labels
  row.rownum

  row.a, row.b, row.c          # Dot-notation
  row[0], row[1], row[2]       # Numeric index
  row['a'], row['b'], row['c'] # String index. Case-insensitive.
  row[:a], row[:b], row[:c]    # Symbol index. Case-insensitive.

  row[0..-1]                   # Range index. Returns an array of values.
  row[0, 3]                    # Offset and length. Returns an array of values.
end

# Returns an array of rows when block is not given
rows = conn.query("SELECT b FROM T")
uniq_rows = rows.uniq

# You can even nest queries
conn.query("SELECT a FROM T") do |row1|
  conn.query("SELECT * FROM T_#{row1.a}") do |row2|
    # ...
  end
end

# `enumerate' method returns an Enumerable object if block is not given.
# When the result set of the query is expected to be large and you wish to
# chain enumerators, `enumerate' is much preferred over `query'. (which returns the
# array of the entire rows)
conn.enumerate("SELECT * FROM LARGE_T").each_slice(1000) do |slice|
  slice.each do | row |
    # ...
  end
end

Updating database table

del_count = conn.update("DELETE FROM T")

Executing any SQL

rset = conn.execute("SELECT * FROM T")
rset.each do |row|
  # Returned result must be used or closed
end

del_count = conn.execute("DELETE FROM T")

Transaction

committed = conn.transaction do |tx|
  # ...
  # Transaction logic here
  # ...

  if success
    tx.commit
  else
    tx.rollback
  end
  # You never reach here.
end

Using batch interface

conn.add_batch("DELETE FROM T");
conn.execute_batch
conn.add_batch("DELETE FROM T");
conn.clear_batch

Using prepared statements

p_sel = conn.prepare("SELECT * FROM T WHERE b = ? and c = ?")
p_sel.query(100, 200) do |row|
  p row
end
p_sel.close

p_upd = conn.prepare("UPDATE T SET a = ? WHERE b = ?")
count = 0
100.times do |i|
  count += p_upd.update('updated a', i)
end

p_upd.add_batch('pstmt + batch', 10)
p_upd.add_batch('pstmt + batch', 20)
p_upd.add_batch('pstmt + batch', 30)
p_upd.execute_batch
p_upd.close

Using table wrappers (since 0.2.0)

# For more complex examples, refer to test/test_object_wrapper.rb
SQL = JDBCHelper::SQL

# Creates a table wrapper
table = conn.table('test.data')

# Counting the records in the table
table.count
table.count(:a => 10)
table.where(:a => 10).count

table.empty?
table.where(:a => 10).empty?

# Selects the table by combining select, where, and order methods
table.select('a apple', :b).where(:c => (1..10)).order('b desc', 'a asc') do |row|
  puts row.apple
end

# Build select SQL
sql = table.select('a apple', :b).where(:c => (1..10)).order('b desc', 'a asc').sql

# Updates with conditions
table.where(:c => 3).update(:a => 'hello', :b => SQL.expr('now()'))

# Insert into the table
table.insert(:a => 10, :b => 20, :c => SQL.expr('10 + 20'))
table.insert_ignore(:a => 10, :b => 20, :c => 30)
table.replace(:a => 10, :b => 20, :c => 30)

# Update with common default values
with_defaults = table.default(:a => 10, :b => 20)
with_defaults.insert(:c => 30)
with_defaults.where('a != 10 or b != 20').update   # sets a => 10, b => 20

# Batch updates with batch method
table.batch.insert(:a => 10, :b => 20, :c => SQL.expr('10 + 20'))
table.batch.insert_ignore(:a => 10, :b => 20, :c => 30)
conn.execute_batch

# Delete with conditions
table.delete(:c => 3)
# Or equivalently,
table.where(:c => 3).delete

# Truncate or drop table (Cannot be undone)
table.truncate!
table.drop!

Building complex where clauses

SQL = JDBCHelper::SQL                  # Shortcut. Or you can just include JDBCHelper

# With Hash
scope = table.where(
  :a => 'abc',                         # a = 'abc'
  :b => (1..10),                       # and b >= 1 and b <= 10
  :c => (1...10),                      # and c >= 1 and c < 10
  :d => %w[a b c],                     # and d in ('a', 'b', 'c')
  :e => SQL.expr('sysdate'),           # and e = sysdate
  :f => SQL.not_null,                  # and f is not null
  :g => SQL.gt(100),                   # and g > 100
  :h => SQL.lt(100),                   # and h < 100
  :i => SQL.like('ABC%'),              # and i like 'ABC%'
  :j => SQL.not_like('ABC%'),          # and j not like 'ABC%'
  :k => SQL.le( SQL.expr('sysdate') )  # and k <= sysdate
)
scope.update(:a => 'xyz')

# With Array
scope = table.where(["a = ? or b > ?", 'abc', 10])

Invalid use of dynamic conditions

TableWrapper object internally creates JDBC PreparedStatements. If you dynamically build many condition-strings as the following example, it would soon fail because there will be too many open PreparedStatements.

10000.times do |idx|
  table.count("id = #{idx}")
end

Correct ways of doing the same would be as follows.

10000.times do |idx|
  # 1. with Hash
  table.count('id' => idx)

  # 2. with Array
  table.count(["id = ?", idx])
end

Using function wrappers (since 0.2.2)

conn.function(:mod).call 5, 3
conn.function(:coalesce).call(nil, nil, 'king')

Using procedure wrappers (since 0.3.0)

# Working with IN/INOUT/OUT parameteres
# Bind by ordinal number
conn.procedure(:update_and_fetch_something).call(
         100,                 # Input parameter 
         ["value", String],   # Input/Output parameter
         Fixnum               # Output parameter
)

# Bind by parameter name
conn.procedure(:update_and_fetch_something).call(
         :a => 100, :b => ["value", String], :c => Fixnum)

Using sequence wrappers (since 0.4.2)

seq = conn.sequence(:my_seq)
next = seq.nextval
curr = seq.currval
seq.reset!
seq.reset! 100

Contributing to jdbc-helper

  • Check out the latest master to make sure the feature hasn't been implemented or the bug hasn't been fixed yet
  • Check out the issue tracker to make sure someone already hasn't requested it and/or contributed it
  • Fork the project
  • Start a feature/bugfix branch
  • Commit and push until you are happy with your contribution
  • Make sure to add tests for it. This is important so I don't break it in a future version unintentionally.
  • Please try not to mess with the Rakefile, version, or history. If you want to have your own version, or is otherwise necessary, that is fine, but please isolate to its own commit so I can cherry-pick around it.

Copyright (c) 2011 Junegunn Choi. See LICENSE.txt for further details.