jdbc-helper

A JDBC helper for Ruby/Database developers. JDBCHelper::Connection object wraps around a JDBC connection and provides much 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.

Examples

Prerequisites

Add JDBC driver of the DBMS you’re willing to use to your CLASSPATH

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

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
conn = JDBCHelper::MySQLConnector.connect('localhost', 'mysql', '', 'test')
conn.close

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")

Transaction

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

    if success
        tx.commit
    else
        tx.rollback
    end
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

table = conn.table('test.data')
table.count
table.empty?
table.select(:c => 3) do |row|
  puts row.a
end
table.update(:a => 1, :b => 2, :where => { :c => 3 })
table.insert(:a => 10, :b => 20, :c => 30)
table.insert_ignore(:a => 10, :b => 20, :c => 30)
table.replace(:a => 10, :b => 20, :c => 30)
table.delete(:c => 3)
table.truncate_table!
table.drop_table!

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, [“value”, String], Fixnum)

# Bind by parameter name
conn.procedure(:update_and_fetch_something).call(

:a => 100, :b => [“value”, String], :c => Fixnum)

Notes on vendor-independence

jdbc-helper tries to be a vendor-independent library, so that it behaves the same on any RDBMS, and that is why it’s built on JDBC in the first place which can be used to minimize the amount of vendor-specific code. (Ideally, one codebase for all RDBMSs.) So far so good, but not great. It has small amount of code that is vendor-specific, non-standard JDBC. And it has been confirmed to work the same for both MySQL and Oracle, except for stored procedures. MySQL and Oracle implement parameter binding of CallableStatement differently. See the following example.

# Let's say we have a stored procedure named my_procedure
# which takes 3 parameters, param1, param2, and param3.

# Creates a CallableStatemet
cstmt = conn.prepare_call("{call my_procedure(?, ?, ?)}")

cstmt.call(10, "ten", Fixnum)
  # OK for both MySQL and Oracle. Filling paramters sequentially.

cstmt.call(:param1 => 10, :param2 => "ten", :param3 => Fixnum)
  # MySQL automatically binds values by the parameter names in the original procedure definition.
  # Oracle fails to do so.
cstmt.close

# For Oracle, if you prepare as follows,
cstmt = conn.prepare_call("{call my_procedure(:p1, :p2, :p3)}")

# Then you can do this. 
cstmt.call(:p1 => 10, :p2 => "ten", :p3 => Fixnum)
  # However, p1, p2, p3 have nothing to do with the original
  # parameter names (param1, param2, param3) in the procedure definition.
  # They're just aliases for ordinal positions.
  # So, it's semantically different from the first example.

cstmt.close

There’s no way to find out the original parameter names and their ordinal positions without looking up the metadata of the database, which requires having to write vendor-specific code branches, and that is definitely not desirable. ProcedureWrapper prepares call with ‘?’s as the first example. So it would work fine for MySQL, but you can’t use it with parameter names on Oracle.

# Good for both
conn.procedure(:my_procedure).call(10, "ten", Fixnum)

# Only for MySQL
conn.procedure(:my_procedure).call(:param1 => 10, :param2 => "ten", :param3 => Fixnum)

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.