Feb 6, 2009

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!

2 comments:

Mai said...

Thank you. This is just what I was looking for.

Big Busta said...

...thanks bud...good post!