[jruby-user] Using JDBC from JRuby
Partogi, Joshua
Dear all,
I want to access JDBC from JRuby. How do I do this? Has anyone got a simple sample code on how to do it?
Many thanks
The information in this email (and any attachments) is confidential. If you are not the intended recipient, you must not use or disseminate the information. If you have received this email in error, please immediately notify me by "Reply" command and permanently delete the original and any copies or printouts thereof. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by American International Group, Inc. or its subsidiaries or affiliates either jointly or severally, for any loss or damage arising in any way from its use.
John Snowdon
An example function I have that uses JDBC:
def studentphoneFind(studentnumber)
#
# Finds a studentnumber record in the USER_DATA student_phone table
# Returns true if the record is found, otherwise false.
#
require "rubygems"
require "jdbc/mysql"
require "java"
truth = false
begin
# Prep the connection
Java::com.mysql.jdbc.Driveruserurl = "jdbc:mysql://HOST/DATABASE"
connSelect = java.sql.DriverManager.get_connection(userurl, "USERNAME", "PASSWORD")
stmtSelect = connSelect.create_statement
# Define the query
selectquery = "SELECT student_id, student_address_type_id
FROM student_phone
WHERE student_id = \"#{studentnumber["student_id"].to_s}\"
AND student_address_type_id = \"#{studentnumber["student_address_type_id"].to_s}\";"
# Execute the query
rsS = stmtSelect.execute_query(selectquery)
# Do stuff with the results
while (rsS.next) do
.
.
.
end
end
# Close the connection
stmtSelect.close
connSelect.close
return truth
end
Hope that helps
John
---
Unix & Web Infrastructure Management
Faculty of Medical Sciences Computing
University of Newcastle
Email : j.p.s...@ncl.ac.uk
Partogi, Joshua
Vladimir Sizikov
This is a really nice code snippet that should be on our wiki! :)
http://wiki.jruby.org/wiki/Main_Page
Probably, as a new page linked from
"Hints, Tips and Documents" section.
Thanks,
--Vladimir
---------------------------------------------------------------------
To unsubscribe from this list, please visit:
http://xircles.codehaus.org/manage_email
John Snowdon
Regards
John
---
Unix & Web Infrastructure Management
Faculty of Medical Sciences Computing
University of Newcastle
Email : j.p.s...@ncl.ac.uk
Web: http://www.ncl.ac.uk/medev
Larry Myers
quite happily up on rubyforge.
http://jdbc-wrapper.rubyforge.org/
Ruby's blocks make it quite easy to break out of the try/catch/finally
pain you usually have to do with JDBC in Java.
The gem has been tested with mysql, h2, and derby, but should work
fine with any jdbc driver and database.
- Larry
Bryan Castillo
> I actually created a ruby gem specifically for this purpose. It lives quite
> happily up on rubyforge.
>
> http://jdbc-wrapper.rubyforge.org/
>
> Ruby's blocks make it quite easy to break out of the try/catch/finally pain
> you usually have to do with JDBC in Java.
>
Ruby's blocks are quite nice for JDBC. I am doing something similar.
I had tried using a jdbc/dbi driver, but had some problems with it (I
don't remember what they were at the moment). I didn't actually wrap
JDBC like your package does, I chose to decorate java classes with
ruby like functionality.
module JdbcHelper
['javax.sql.DataSource', 'java.sql.Connection',
'java.sql.Statement'].each do |j_class|
JavaUtilities.extend_proxy(j_class) do
def with_closeable_resource(resource)
begin
yield(resource)
ensure
resource.close
end
end
end
end
JavaUtilities.extend_proxy('javax.sql.DataSource') do
def with_connection(&block)
with_closeable_resource(self.get_connection, &block)
end
def transaction
with_connection { |c| c.transaction { yield(c) } }
end
def execute_update(sql, *params, &block)
self.transaction { |connection| connection.execute_update(sql,
*params, &block) }
end
def each_array(sql, *params, &block)
self.transaction { |connection| connection.each_array(sql,
*params, &block) }
end
def each_hash(sql, *params, &block)
self.transaction { |connection| connection.each_hash(sql,
*params, &block) }
end
end
JavaUtilities.extend_proxy('java.sql.Connection') do
def mysql?
!self.meta_data.database_product_name.downcase.index("mysql").nil?
end
def postgres?
!self.meta_data.database_product_name.downcase.index("postgres").nil?
end
def with_statement(&block)
with_closeable_resource(self.create_statement, &block)
end
def with_prepared_statement(sql, &block)
with_closeable_resource(self.prepare_statement(sql), &block)
end
def with_callable_statement(sql, &block)
with_closeable_resource(self.prepare_call(sql), &block)
end
def transaction
ac = self.auto_commit
begin
self.auto_commit = false if ac
success = false
begin
rc = yield
success = true
return rc
ensure
if success
self.commit
else
self.rollback
end
end
ensure
self.auto_commit = ac if ac
end
end
def execute_update(sql, *params)
rc = -1
if params.empty?
with_statement { |st| rc = st.execute_update(sql) }
else
with_prepared_statement(sql) do |st|
st.set_parameters(*params)
rc = st.execute_update
end
end
rc
end
def each_array(sql, *params, &block)
if params.empty?
with_statement do |st|
st.enable_streaming_results
st.each_array(sql, &block)
end
else
with_prepared_statement(sql) do |st|
st.set_parameters(*params)
st.enable_streaming_results
st.each_array(&block)
end
end
end
def each_hash(sql, *params, &block)
if params.empty?
with_statement do |st|
st.enable_streaming_results
st.each_hash(sql, &block)
end
else
with_prepared_statement(sql) do |st|
st.set_parameters(*params)
st.enable_streaming_results
st.each_hash(&block)
end
end
end
end
JavaUtilities.extend_proxy('java.sql.Statement') do
def execute_with_result_set(sql = nil, &block)
rs = nil
if sql.nil?
rs = self.execute_query
else
rs = self.execute_query(sql)
end
with_closeable_resource(rs, &block)
end
def enable_streaming_results
# Tries to use streaming result sets
if self.connection.postgres?
self.fetch_size = 100
elsif self.connection.mysql?
self.fetch_size = java.lang.Integer::MIN_VALUE
end
end
def each_array(sql = nil, &block)
execute_with_result_set(sql) { |rs| rs.each_array(&block) }
end
def each_hash(sql = nil, &block)
execute_with_result_set(sql) { |rs| rs.each_hash(&block) }
end
end
JavaUtilities.extend_proxy('java.sql.PreparedStatement') do
def set_parameters(*params)
i = 1
params.each do |param|
if param.nil?
self.set_null(i, java.sql.Types::NULL)
elsif param.kind_of? java.lang.Object
self.set_object(i, param)
elsif param.kind_of? Float
self.set_double(i, param)
elsif param.kind_of? Fixnum
self.set_long(i, param)
elsif param.kind_of? Bignum
self.set_big_integer(i, param)
elsif param.kind_of? TrueClass or param.kind_of? FalseClass
self.set_boolean(i, param)
elsif param.kind_of? Time
self.set_timestamp(i, java.sql.Timestamp.new((param.to_f *
1000).to_i))
elsif param.kind_of? DateTime
self.set_timestamp(i,
java.sql.Timestamp.new((Time.parse(param.to_s).to_f * 1000).to_i))
elsif param.kind_of? Date
self.set_date(i, java.sql.Date.value_of(param))
else
self.set_string(i, param.to_s)
end
i+= 1
end
end
end
JavaUtilities.extend_proxy('java.sql.ResultSet') do
def each_array(&block)
md = self.meta_data
ncols = md.column_count
columns = (1..ncols).collect { |i| md.column_name(i) }
while self.next
row = (1..ncols).collect { |i| self.get_object(i) }
if block.arity == 2
block.call(row, columns)
else
block.call(row)
end
end
end
def each_hash
md = self.meta_data
ncols = md.column_count
columns = (1..ncols).collect { |i| md.column_name(i) }
while self.next
row = {}
(1..ncols).each { |i| row[columns[i-1]] = self.get_object(i) }
yield(row)
end
end
end
end
JohnEbenzer JohnEbenzer
Vladimir Sizikov
On Wed, Jun 18, 2008 at 10:10 PM, Larry Myers <la...@larrymyers.com> wrote:
> I actually created a ruby gem specifically for this purpose. It lives quite
> happily up on rubyforge.
>
> http://jdbc-wrapper.rubyforge.org/
I've played a bit with this gem today, since we're getting a constant stream of
questions about pure JDBC access for some reason :)
One of the popular issues is the need for jdbc driver in the
classpath. Given that activerecord-jdbc doesn't really require the
dirvers to be on the classpath, I thought that something could be done
here as well.
For example, in MySQL case, the driver comes with jdbc-mysql gem and
it would be great to be able to just take advantage of that.
So, when I do:
require 'rubygems'
require 'jdbc/mysql' # load jdbc dirver for mysql
require 'jdbc' # load jdbc-wrapper gem
require 'jruby'
and then:
connection = JDBC::DB.new(:mysql, 'host', 3306, 'user', 'pass',
'blog_development')
This doesn't work with jdbc-wrapper 0.4. We get the ClassNotFoundException,
since java.lang.Class.forName is useed in db.rb, in initialize.
The better alternative here would be to change that forName call to:
::JDBC.module_eval {
include_class(adapter.class_name)
}
That way, proper (JRuby's) classloader will be used. For more robust way
of doing this (and doing this only once instead of every DB.new call), take
a look at jdbc_adapter.rb file from activerecord-jdbc, 'driver_class' method
(it makes sure that the class is loaded once and only once, and loads
it with better name):
http://is.gd/Fq0
After that change, things *almost* work without jdbc driver in the classpath.
All is left to do is to set the proper context classloader so that
JDBC's DriverManager could find the drivers properly.
That's what I do in my script:
require 'jruby'
# We need this otherwise JDBC's DriverManager won't be able
# to find JDBC drivers available to JRuby's classloader
java.lang.Thread.currentThread.setContextClassLoader(JRuby.runtime.jruby_class_loader)
And after that, we can easily work with jdbc driver that comes in the
gem, and with
no classpath.
Here's the full script, just in case anybody interested:
require 'rubygems'
require 'jdbc/mysql' # load jdbc dirver for mysql
require 'jdbc' # load jdbc-wrapper gem
require 'jruby'
# We need this otherwise JDBC's DriverManager won't be able
# to find JDBC drivers available to JRuby's classloader
java.lang.Thread.currentThread.setContextClassLoader(JRuby.runtime.jruby_class_loader)
# Provide verbose info on what's going on
java.sql.DriverManager.setLogStream(java.lang.System.out)
connection = JDBC::DB.new(:mysql, 'host', 3306, 'user', 'pass',
'blog_development')
Thanks,
--Vladimir
Larry Myers
This definitely solves one of the remaining usability issues of the library.
I'll work on getting these changes into the code and update the gem.
I'll post something to the list once it's done.
- Larry
Thomas E Enebo
Can you add an entry to our wiki with this content? Perhaps we
need a more general entry on classpath+classloaders in JRuby?
-Tom
--
Blog: http://www.bloglines.com/blog/ThomasEEnebo
Email: en...@acm.org , tom....@gmail.com
