jdbc-helper

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

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

conn.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')

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

table.close

# 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