Using MySQL with JRuby outside of ActiveRecord

UPDATE(Jun. 12/2012): Turns out things have changed a bit since this post was written, JDBC has been put directly into JRuby. You can check it out here.

I was playing around yesterday with more JRuby stuff and wandered my way into needing to access MySQL from JRuby, but outside of an ActiveRecord environment. Of course naively I originally tried to use the mysql gem for Ruby, but it failed since it seems that gem is native code only (one of the annoyances of JRuby). But I've been using JRuby with MySQL for nearly 6 months now and never had problems. So I dug into the activerecord-jdbcmysql-adapter gem and some related ones and discovered some things that were a bit annoying. It turns out that those things all use the java.sql stuff, instead of some Ruby-baked solution.

Now I haven't really ever worked with java.sql. In fact, I don't recall ever working with a DB outside of a dynamic language. And it looks painful. There's all sorts of getString() this and getRef() that, blah blah.

So I decided to roll up a nice and simple JRuby class for JDBC. Here's the whole code:

require 'java'
require 'rubygems'
require 'jdbc/mysql'
include_class "com.mysql.jdbc.Driver"

class JdbcMysql
  def initialize(host = nil, username = nil, password = nil, db = nil, port = nil)
    host ||= "localhost"
    port ||= 3306

    address = "jdbc:mysql://#{host}:#{port}/#{db}"
    @connection = java.sql.DriverManager.getConnection(address, username, password)
  end

  def query sql
    resultSet = @connection.createStatement.executeQuery sql

    meta = resultSet.getMetaData
    column_count = meta.getColumnCount

    rows = []

    while resultSet.next
      res = {}

      (1..column_count).each do |i|
        name = meta.getColumnName i
        case meta.getColumnType i
        when java.sql.Types::INTEGER
          res[name] = resultSet.getInt name
        else
          res[name] = resultSet.getString name
        end
      end

      rows << res
    end
    rows
  end
end

It may not be the most flexible of classes right now, but it should get the job done. And it is wide open for improvement.

For this to work you'll need the jdbc-mysql gem:

jgem install jdbc-mysql

While you'd think the jdbc-mysql gem would have something like the above, all it does is include the Java MySQL driver (aka com.mysql.jdbc.Driver).

So now if you want to use the class:

db = JdbcMysql.new("localhost", "me", "secret", "my_database")

res = db.query "SELECT * FROM my_table"

res.each do |row|
  puts row["value"]
end

Much easier!