_ _ _ _ _
(_) | | | | | | |
_ __| | |__ ___ ______| |__ ___| |_ __ ___ _ __
| |/ _` | '_ \ / __|______| '_ \ / _ \ | '_ \ / _ \ '__|
| | (_| | |_) | (__ | | | | __/ | |_) | __/ |
| |\__,_|_.__/ \___| |_| |_|\___|_| .__/ \___|_|
_/ | | |
|__/ |_|
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, MS SQL Server 2008 R2 and Cassandra 1.1.1 (CQL3).
gem install jdbc-helper
Add the appropriate JDBC drivers to the CLASSPATH.
export CLASSPATH=$CLASSPATH:~/lib/mysql-connector-java-5.1.16-bin.jar:~/lib/ojdbc6.jar
require 'jdbc-helper'
# :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: password)
conn.close
jdbc-helper provides shortcut connectors for the following databases so that you don't have to specify lengthy class names and JDBC URLs.
JDBCHelper::MySQL
)JDBCHelper::MariaDB
)JDBCHelper::Oracle
)JDBCHelper::PostgreSQL
)JDBCHelper::MSSQL
)JDBCHelper::Cassandra
)JDBCHelper::FileMaker
)JDBCHelper::SQLite
)mysql = JDBCHelper::MySQL.connect(host, user, password, db)
mariadb = JDBCHelper::MariaDB.connect(host, user, password, db)
oracle = JDBCHelper::Oracle.connect(host, user, password, service_name)
postgres = JDBCHelper::PostgreSQL.connect(host, user, password, db)
mssql = JDBCHelper::MSSQL.connect(host, user, password, db)
cassandra = JDBCHelper::Cassandra.connect(host, keyspace)
filemaker = JDBCHelper::FileMaker.connect(host, user, password, db)
sqlite = JDBCHelper::SQLite.connect(file_path)
# With extra parameters
mysql = JDBCHelper::MySQL.connect(host, user, password, db,
rewriteBatchedStatements: true)
# With connection timeout of 30 seconds
mysql = JDBCHelper::MySQL.connect(host, user, password, db,
rewriteBatchedStatements: true, timeout: 30)
# When block is given, connection is automatically closed after the block is executed
JDBCHelper::Cassandra.connect(host, keyspace) do |cc|
# ...
end
conn.query('SELECT a, b, c FROM T') do |row|
row.labels
row.rownum
a, b, c = row
a, b, c = row.a, row.b, row.c # Dot-notation
a, b, c = row[0], row[1], row[2] # Numeric index
a, b, c = row['a'], row['b'], row['c'] # String index. Case-insensitive.
a, b, c = 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.
row.to_h # Row as a Hash
end
# You can even nest queries
conn.query('SELECT a FROM T') do |row1|
conn.query("SELECT * FROM T_#{row1.a}") do |row2|
# ...
end
end
# Connection::ResultSet object is returned when block is not given
# - ResultSet is automatically closed when entirely iterated
rows = conn.query('SELECT * FROM T')
uniq_rows = rows.to_a.uniq
# However, partially consumed ResultSet objects *must be closed* manually
rset = conn.query('SELECT * FROM T')
rows = rset.take(2)
rset.close
# Enumerator chain
conn.query('SELECT * FROM LARGE_T').each_slice(1000).with_index do |slice, idx|
slice.each do |row|
# ...
end
end
del_count = conn.update('DELETE FROM T')
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')
committed = conn.transaction do |tx|
# ...
# Transaction logic here
# ...
if success
tx.commit
else
tx.rollback
end
# You never reach here.
end
conn.add_batch('DELETE FROM T')
conn.execute_batch
conn.add_batch('DELETE FROM T')
conn.clear_batch
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
java
methodconn.java.setAutoCommit false
pstmt = conn.prepare(sql)
pstmt.java.getMetaData
# Creates a table wrapper
table = conn.table('test.data')
# Or equievalently,
table = conn['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, order, limit and fetch_size methods
table.select('a apple', :b).where(c: (1..10)).order('b desc', 'a asc').fetch_size(100).limit(1000).each 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: 'now()' })
# Insert into the table
table.insert(a: 10, b: 20, c: { sql: '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: '10 + 20' })
table.batch.insert_ignore(a: 10, b: 20, c: 30)
table.batch.where(a: 10).update(a: 20)
table.execute_batch :insert, :update
# 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!
# With any number of Strings, Arrays and Hashes
scope = table.where(
"x <> 'hello'", # x <> 'hello'
["y = ? or z > ?", 'abc', 10], # and (y = 'abc' or z > 10)
a: 'abc', # and a = 'abc'
b: (1..10), # and b between 1 and 10
c: (1...10), # and c >= 1 and c < 10
d: %w[a b c], # and d in ('a', 'b', 'c')
e: { sql: 'sysdate' }, # and e = sysdate
f: { not: nil }, # and f is not null
g: { gt: 100, le: 200 }, # and g > 100 and g <= 200
h: { lt: 100 }, # and h < 100
i: { like: 'ABC%' }, # and i like 'ABC%'
j: { not: { like: 'ABC%' } }, # and j not like 'ABC%'
k: { le: { sql: 'sysdate' } } # and k <= sysdate
)
scope.update(a: 'xyz')
A TableWrapper object internally builds SQL strings and creates JDBC PreparedStatement object for each distinct SQL.
If you build many number of where-clause Strings as shown in the following code, soon there will be too many open PreparedStatements, and if the number exceeds the system limit, an error will be thrown.
table = connection['table']
# Leads to 10000 PreparedStatements !!
10000.times do |idx|
table.count("id = #{idx}")
# select count(*) from table where id = 0
# select count(*) from table where id = 1
# select count(*) from table where id = 2
# select count(*) from table where id = 3
# ...
end
In that case, you can close
the table wrapper to close all the open PreparedStatements.
table.close
However, you should always prefer using much more efficient Hash or Array expression over plain String, so you don't have to worry about the proliferation of PreparedStatements.
# 20000 queries but only a single PreparedStatement
10000.times do |idx|
# 1. with Hash
table.count('id' => idx)
# select count(*) from table where id = ?
# 2. with Array
table.count(["id = ?", idx])
# select count(*) from table where id = ?
end
conn.function(:mod).call 5, 3
conn.function(:coalesce).call(nil, nil, 'king')
# 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)
seq = conn.sequence(:my_seq)
next = seq.nextval
curr = seq.currval
seq.reset!
seq.reset! 100
Copyright (c) 2011 Junegunn Choi. See LICENSE.txt for further details.