How to initialize the SQLite3 JDBC driver in JRuby?

How do you access SQLite3 via JDBC without using active record?

Here's an example that works with JRuby 1.6.6 (in Ruby 1.8 compat mode) with jdbc-sqlite3 3.7.2.

require 'rubygems'
require 'jdbc/sqlite3'
require 'java'

org.sqlite.JDBC                 # load the driver so DriverManager detects it 
#Java::OrgSqlite::JDBC          # alternate means of same

connection = java.sql.DriverManager.getConnection 'jdbc:sqlite:test.sqlite3'
begin
  statement = connection.createStatement
  begin
    statement.executeUpdate("create table user (name varchar, pass varchar)")
    statement.executeUpdate("insert into user values ('alice', 1234)")
    statement.executeUpdate("insert into user values ('bob', 5678)")
    statement.executeUpdate("insert into user values ('charlie', 'asdf')")

    rs = statement.executeQuery("select * from user")
    begin
      puts "user\tpass"
      while rs.next
        puts ["#{rs.getString(1)}",
              "#{rs.getString(2)}"].join("\t")
      end
    ensure
      rs.close
    end

  ensure
    statement.close
  end
ensure
  connection.close
end

Output:

$ rm -f test.sqlite3; ruby sql.rb
user    pass
------------
alice   1234
bob     5678
charlie asdf

Install the jdbc-sqlite3 gem

Then, in your jruby script:

require 'jdbc/sqlite3'
url = "jdbc:sqlite:path.to.your.db"
begin        
    Java::org.sqlite.JDBC #initialize the driver
    connection = JavaLang::DriverManager.getConnection(url) #grab your connection
rescue => error      
    #handle error
end

The jdbc driver file sqlite-3.5.8.jar gets copied into the gem directory. It needs to be copied into the jruby/lib directory. Application also needs restart.

The driver file can be downloaded directly from here

http://files.zentus.com/sqlitejdbc/sqlitejdbc-v056.jar

http://www.zentus.com/sqlitejdbc/