Skip to content
JamesGecko edited this page Jun 1, 2011 · 9 revisions

It is possible to use JDBC directly from within JRuby. A simple function using the MySQL JDBC driver to perform a SELECT is shown below:

  def vegetableFinder(vegetable)
    # This function takes a hashmap of vegetables and attempts to
    # find them from our grocery database. For each item found, we
    # call our 'makevegetablesoup' function.

    # Load all required gems
    require "rubygems"
    require "jdbc/mysql"
    require "java"

    begin
      # Prep the connection
      Java::com.mysql.jdbc.Driver
      userurl = "jdbc:mysql://HOST/DATABASE"
      connSelect = java.sql.DriverManager.get_connection(userurl, "USERNAME", "PASSWORD")
      stmtSelect = connSelect.create_statement

      # Define the query
      selectquery = %q{SELECT name, type, size, price
            FROM vegetables
            WHERE type = "#{vegetable["type"]}"
            AND size = "#{vegetable["size"]}}

      # Execute the query
      rsS = stmtSelect.execute_query(selectquery)

      # For each row returned do some stuff
      while (rsS.next) do
        veg = Hash.new
        veg["vegname"] = rsS.getObject("name")
        veg["vegtype"] = rsS.getObject("type")
        veg["vegprice"] = rsS.getObject("size")
        veg["vegsize"] = rsS.getObject("price")
        makevegetablesoup(veg)
      end
    end
    # Close off the connection
    stmtSelect.close
    connSelect.close
    return truth
  end

INSERTs and UPDATEs are much the same. Again, using the MySQL JDBC driver a simple insert is shown below:

  def groceryInsert(vegetable)
    # Inserts a new vegetable type into the grocery database

    # Load all required gems
    require "rubygems"
    require "jdbc/mysql"
    require "java"

    begin
      # Prep the connection
      Java::com.mysql.jdbc.Driver
      userurl = "jdbc:mysql://HOST/DATABASE"
      connInsert = java.sql.DriverManager.get_connection(userurl, "USERNAME", "PASSWORD")
      stmtInsert = connInsert.create_statement

      # Do the insert
      rsI = stmtInsert.execute_update("INSERT INTO vegetables (name,type,size,price)
          VALUES
               (
               '#{vegetable["name"]}',
               '#{vegetable["type"]}',
               '#{vegetable["size"]}',
               '#{vegetable["price"]}'
               );")
      end
    end
    # Close off the connection
    stmtInsert.close
    connInsert.close

    # Return the number of records inserted
    return rsI
  end

Remember to have the JDBC driver for your database in your CLASSPATH!


Here's an example of connecting to SQL Server using Microsoft's JDBC driver. The open source jTDS driver is another good option.

require 'rubygems'
require 'java'
require 'sqljdbc4.jar'

Java::com.microsoft.sqlserver.jdbc.SQLServerDriver
url = 'jdbc:sqlserver://host:1433;databaseName=database'
conn = java.sql.DriverManager.get_connection(url, "user", "password")
statement = conn.create_statement

q = "SELECT * FROM vegtables"
rs = statement.execute_query(q)

while (rs.next) do
  puts rs.getObject('name')
end

statement.close
Clone this wiki locally