JRuby, JDBC, and DBI

• Tweet

When you connect to a database with JRuby, you usually go through something like ActiveRecord, part of the Ruby on Rails framework. However, if you just want to issue a single SQL command, this might be much too complex, in particular if you take the start-up time into account.

It turns out that you can very conveniently “hit the database” with dbi, Ruby’s Direct Database Interface. However, documentation on how to do this exactly are pretty scarce.

The following code connects to the ‘foobar’ PostgreSQL data base on localhost using the user ‘foobar’ and password ‘quux’:

# gems you need
#
# dbi
# dbd-jdbc
# jdbc-postgres
 
require 'java'
 
require 'rubygems'
require 'dbi'
require 'dbd/Jdbc'
require 'jdbc/postgres'
 
# connect to database 'foobar'
# with user 'foobar' and password 'quux'
DBI.connect('DBI:Jdbc:postgresql://localhost/foobar',
            'foobar', 'quux',
            'driver' => 'org.postgresql.Driver') do |dbh|
  puts "Connected"
  
end

(I also put this code down in a gist)

The actual complexity comes from the fact that you actually need three different gems: dbi for the database access, dbd-jdbc as a JDBC driver for dbi, and jdbc-postgres, the actual JDBC driver for PostgreSQL packaged as a ruby gem. Ah, and then you also need to know the name of the driver class.

In order to install these, you type

jruby -S gem install dbi dbd-jdbc jdbc-postgres

Once you have connected to the data base, you can issue normal SQL commands, see the excellent DBI tutorial. For example, you could count all entries of the table “phone_numbers” with

dbh.select_one "SELECT count(*) FROM phone_numbers"

Finally, here is how to connect to a MySQL data base:

DBI.connect("DBI:Jdbc:mysql://localhost/foobar", 
                'foobar', 'quux'
                'driver' => 'com.mysql.jdbc.Driver')

You also need to install the JDBC driver by installing the jdbc-mysql gem. The jruby-extras project contains more drivers for other data bases as well.

Posted by Mikio L. Braun at 2010-03-10 17:05:00 +0000