A quick and dirty jruby mysql jdbc wrapper

Posted by ochronus on May 25, 2009 in database, ruby | 6 comments

I’ve started using jruby more and more, but I’m a bit unsatisfied by the current status of mysql support, the jdbc api is a bit cumbersome to me (of course only after getting lazy because of the convenience of the original ruby mysql gem) – especially about SELECTs and using their result set. Thus, I wrote a small callback wrapper, see below:

require 'jdbc/mysql'
require 'java'
$KCODE = "u"
 
class MySQL
  def initialize(dbhost, user, pass, db, encoding="UTF8")
    Java::com.mysql.jdbc.Driver
    userurl = "jdbc:mysql://#{dbhost}/#{db}"
    @connSelect = java.sql.DriverManager.get_connection(userurl, user, pass)
    @stmtSelect = @connSelect.create_statement
    @stmtSelect.execute_query("SET NAMES #{encoding}")
    @stmtSelect.execute_query("SET CHARACTER SET #{encoding}")  
  end
 
  def query(querystr)
    res = @stmtSelect.execute_query(querystr)
    meta = res.getMetaData
    colcnt = meta.getColumnCount
    names = {}
    colcnt.times {|i|
      names[i+1] = meta.getColumnName(i+1)
    }
    while (res.next) do
      row = {}
      colcnt.times {|i|
        row[names[i+1]]= res.getString(i+1)
      }
      yield row
    end
  end
end
 
sql = MySQL.new('127.0.0.1', 'root', nil, 'whatever')
qstring = "SELECT something1, something2 FROM MyCoolTable"
sql.query(qstring){ |row| 
  pp row     # this is a hash keyed by the column names
}

I hacked this together in 5 minutes, I’m sure there’s much room for improvement :)

 

Related posts:

  1. A dead simple trick to detect top unindexed or badly indexed mysql queries
  2. On Oracle buying Sun
  3. Sun’s JRuby team moves to Engine Yard

1 Comment 1 Tweet

6 comments

  1. ochronus
    May 25, 2009

    A nice quick wrapper for SELECT-s for jdbc-mysql! No more iterating through the resultset like in java

    This comment was originally posted on Digg

    [Reply]

  2. osman
    May 25, 2009

    I highly recommend you http://sequel.rubyforge.org.

    [Reply]

  3. Chad Johnson
    May 28, 2009

    Ruby DBI has a JRuby JDBC driver called dbd-jdbc (it works with all JDBC drivers) :

    http://kenai.com/projects/dbd-jdbc/pages/Home

    Ruby DBI docs :

    http://ruby-dbi.rubyforge.org/

    Gem install :

    `jruby -S gem install dbi dbd-jdbc`

    -CJ

    [Reply]

  4. ochronus
    May 28, 2009

    @Chad Johnson
    Thanks, I didn’t know about this one! Looks promising, nice tip!

    [Reply]

  5. Chris St. John
    September 23, 2009

    @Chad Johnson
    Worked great, thanks Chad.

    [Reply]

Leave a Reply

Additional comments powered by BackType