TOTD #9: Using JDBC connection pool/JNDI name from GlassFish in Rails Application

Using the instructions followed in JRuby Hack Day and taking some help from Nick, I figured out how to use the JDBC connection pools configured in GlassFish using the JNDI names.

All the commands given below are relevant for GlassFish but the same concept will work where ever you deploy your WARed up JRuby on Rails application.

  1. Follow the bullet #1 and #2 from here to create a new application and database with the following exceptions:
    1. Use the name "jndi_rails" instead of "RailsApplication9" for the Project Name.
    2. Make sure to select "Access database using JDBC" in bullet # 2.2. This will bundle ActionRecord-JDBC plugin in your application.
  2. Create database using bullet # 3 from here. Use the following command instead to create the database instead:

    mysqladmin -u root create jndi_rails_production
  3. Create Model and Controller as described in bullet #4 here.
  4. Configure Model and Controller
    1. Configure Model
      1. In the NetBeans IDE, expand "Database Configurations", "migrate" and open "001_create_greetings.rb". Change the "self.up" helper method such that it looks like:

        def self.up
          create_table :greetings do |t|
            t.column :data, :string
          end
        end
      2. Expand "Configuration", open "database.yml", change the database name for development configuration from "jndi_rails_development" to "jndi_rails_production".
      3. Right-select the project, select 'Run Rake Target', 'db', 'migrate'. This generates the appropriate database tables and the following is shown in the output window:

        (in C:/Users/Arun Gupta/Documents/NetBeansProjects/jndi_rails)
        == CreateGreetings: migrating =================================================
        -- create_table(:greetings)
        -> 0.2650s
        == CreateGreetings: migrated (0.2650s) ========================================
      4. Add data to the database tables and give appropriate privileges using the following commands in a shell window:

        C:\\Program Files\\MySQL\\MySQL Server 5.0\\bin>mysql -u root
        Welcome to the MySQL monitor. Commands end with ; or \\g.
        Your MySQL connection id is 14
        Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

        Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

        mysql> use jndi_rails_production;
        Database changed
        mysql> insert into greetings values (1, "Hello from MySQL JNDI pool!");
        Query OK, 1 row affected (0.03 sec)

        mysql> grant all on jndi_rails_production.\* to arun@localhost identified by 'noway';
        Query OK, 0 rows affected (0.26 sec)

        mysql> flush privileges;
        Query OK, 0 rows affected (0.16 sec)

        mysql> quit;
        Bye
      5. Change the production database environment from:

        production:
          adapter: mysql
          database: jndi_rails_production
          username: root
          password:
          host: localhost


        to

        production:
          adapter: jdbc
          jndi: jdbc/jndi_rails
          driver: com.mysql.jdbc.Driver

        Notice only JDBC adpater and JNDI name is specified in the database configuration. This ensures that the database is resolved using only the JNDI name. Although "database", "username" and "password" attributes may be specified in addition to "jndi" and "driver" attributes. In this case, the Rails configuration falls back to pure-Ruby MySQL adapter.
    2. Configure Controller using bullet # 5.2 from here.
  5. Create the JDBC connection pool and resource
    1. In GLASSFISH_HOME\\bin, create the JDBC connection pool by giving the following command:

      asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property User=arun:Password=noway:URL=jdbc\\:mysql\\://localhost/jndi_rails_production jdbc/jndi_rails_pool

      The following output should be seen on the console:

      Command create-jdbc-connection-pool executed successfully.
    2. Create the JDBC resource by giving the following command:

      asadmin create-jdbc-resource --connectionpoolid jdbc/jndi_rails_pool jdbc/jndi_rails

      The following output should be seen on the console:

      Command create-jdbc-resource executed successfully.
  6. Create a WAR file as described in bullet #6 here
  7. Download, Install and Start GlassFish as described in bullet #7 here.
  8. Download and Install MySQL/J Connector in a new directory. Copy the JAR file from the main installation directory to 'GLASSFISH_HOME/lib' directory.
  9. Copy the WAR file (jndi_rails.war) in "domains/domain/autodeploy" directory.

The application is accessible at "http://localhost:8080/jndi_rails/say/hello".

An alternative approach to use the connection pools is discussed here. Lou also nicely describes the benefits of connection pooling.

Connecting to Oracle From Rails explains how to connect to Oracle (instead of JavaDB) with JRuby.

Please leave suggestions on other TOTD that you'd like to see. A complete archive is available here.

Technorati: totd rubyonrails jruby ruby netbeans glassfish connectionpooling jndi jdbc jrubyonglassfish mysql

Comments:

[Trackback] Day 2 started with regular announcements and keynote by DHH. The demographic distribution of approximately 750 attendees was shown in the filler slides right before the keynote: Germany 29% Sweden 3% UK 17% Spain 3% United States 11% Norway...

Posted by Arun Gupta's Blog on September 18, 2007 at 07:30 AM PDT #

This is all great but wouldn't the main benefit of using the connection pool be that many ruby apps would all share it? Assuming they are each using their own schema, how do you get them to change the context they are executing sql in when they check out a connection from the pool?

Posted by Matt Field on September 30, 2007 at 10:13 PM PDT #

[Trackback] "sakila" is the sample database shipped with MySQL (pronounced as my ess-kew-ell). In the context of Sun Microsystems announcing the agreement to acquire MySQL, I'd like to dedicate this entry to show how this sample database can be exposed a...

Posted by Arun Gupta's Blog on January 23, 2008 at 08:46 PM PST #

[Trackback] GlassFish v3 Gem allows JRuby-on-Rails application to be launched in GlassFish v3 server. It provides a robust alternative to WEBrick and Mongrel for development and deployment of your JRuby-on-Rails applications. The Gem was originally announced here...

Posted by Arun Gupta's Blog on February 07, 2008 at 08:33 PM PST #

Hi.
It perfect, but what about when you have, let's say 5 databases in the same server and you web application need data from all of them. How do you do that?

Thanks
Greetings

Posted by David on March 02, 2008 at 04:39 AM PST #

David,

How do you do multiple databases in Rails ? I expect the same mechanism will be extended for JNDI as well.

-Arun

Posted by Arun Gupta on March 09, 2008 at 01:49 PM PDT #

Hi,

Can you provide me some link where i can connect to mysql database in glass fish server console for my ejb project on netbeans.

thanks in advance
abishek kumar.

Posted by Abishek Kumar on March 18, 2008 at 09:27 AM PDT #

Hi Abhishek,

Here is a screencast shows how to do this for NetBeans in general:

http://www.netbeans.org/download/flash/netbeans_61/mysql_demo/mysql_demo.html

You should be able to use this for your EJB projects as well.

Thanks,
-Arun

Posted by Arun Gupta on March 18, 2008 at 11:07 AM PDT #

Here is how I solved (from a user in IceFaces tfreyne)

in web.xml
Code:

<!-- first datasource-->
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/SOME_NAME</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<!-- second datasource-->
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/SOME_NAME2</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

and in context.xml
Code:

<Context path="/si3_8" docBase="si3_8" debug="1" reloadable="true">
<!-- First one-->
<Resource name="jdbc/SOME_NAME" auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/DATABASE1" username="si3_8"
password="si3" maxActive="20" maxIdle="10" maxWait="-1"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true" />

<!-- Second one-->
<Resource name="jdbc/SOME_NAME2" auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/DATABASE2" username="si3_8"
password="si3" maxActive="20" maxIdle="10" maxWait="-1"
removeAbandoned="true" removeAbandonedTimeout="60"
logAbandoned="true" />

</Context>

Posted by David on March 20, 2008 at 07:06 AM PDT #

Hi Arun,

I finally setup rails on Glassfish using glassfish_rails 0.2.0 gem thanks to your instructions.

It is stable, but I've just discovered that file uploading is not working when file size exceeds 10 KB. Here is the error in log file:
Status: 500 Internal Server Error
stack level too deep
E:/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/action_controller/request.rb:547:in `read_multipart'

Somehow this problem does not happen with JRuby 1.1.2 on Mongrel.

Is there some mistake that I possibly made in configuring glassfish?

Thanks,
BX

Posted by BX on June 10, 2008 at 12:57 PM PDT #

BX, this could be a bug. Can you file one at:

https://glassfish.dev.java.net/issues/enter_bug.cgi?issue_type=DEFECT

using "jruby" subcomponent.

Posted by Arun Gupta on June 10, 2008 at 03:33 PM PDT #

A bug is submitted at:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=5135

Thanks,
BX

Posted by BX on June 11, 2008 at 06:09 PM PDT #

df

Posted by guest on June 12, 2008 at 02:39 PM PDT #

you're not alone , got the same problem. I submitted the bug ... we'll see... I really need this very soon to work!

When using Jruby1.1.2+Glassfish (latest gem available) and trying to upload a file > 10Kb; I got the following message in the log :
-------------------------------------------------------------------------------
/!\\ FAILSAFE /!\\ Sun Jun 15 22:03:29 +0200 2008
Status: 500 Internal Server Error
stack level too deep
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:547:in `read_multipart'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:547:in `loop'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:547:in `read_multipart'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:470:in `parse_multipart_form_parameters'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:400:in `parse_formatted_request_parameters'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/cgi_process.rb:80:in `request_parameters'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:304:in `parameters'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:22:in `request_method'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/request.rb:35:in `method'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/routing/route_set.rb:431:in `extract_request_environment'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/routing/route_set.rb:384:in `recognize'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/dispatcher.rb:148:in `handle_request'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/dispatcher.rb:107:in `dispatch'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/dispatcher.rb:104:in `dispatch'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/dispatcher.rb:120:in `dispatch_cgi'
C:/JRuby/jruby-1.1.2/lib/ruby/gems/1.8/gems/actionpack-2.1.0/lib/
action_controller/dispatcher.rb:35:in `dispatch'
<script>:62:in `service'
:1
------------------------------------------------------------------------------
Somehow this problem does not happen with JRuby 1.1.2 on Mongrel.

is there anything I can change in some configuration file to prevent this bug?

Posted by Fred on June 15, 2008 at 06:30 AM PDT #

[Trackback] Last day of Rails Conf Europe 2008 (Day 1 &amp; Day 2), and it's finally over! David Black's opening session talked about Ruby and Rails Symposium: Versions, Implementations, and the Future. Here is a brief summary of MRI Ruby...

Posted by Arun Gupta's Blog on September 05, 2008 at 03:19 PM PDT #

Hi Dear ,
I have a problem in connection pooling ,I want to create connection pooling with the help of factory parameter and I would like to pass oracle configuration.but at this time i am not able to get connection,and another way i sucessfully create connection.
Please help me out.
advance Thank,

From
Anurodh shrivastava

Posted by Anurodh Shrivastava on June 04, 2009 at 11:28 PM PDT #

Please ask your question to users@glassfish.dev.java.net.

Posted by Arun Gupta on June 08, 2009 at 09:50 AM PDT #

Hello Sir,

I am trying to learn many things related to EJB 3.0 J2EE application servers from ur screencast.

I am trying to use MySQL in JOnAS application server..
but i am not able to do it..
could u please provide some information about the configuration and small example in eclipse IDE...

Posted by ajinkya on November 02, 2010 at 10:33 AM PDT #

ajinkya,

Please consider posting your questions in JOnAS Application Server forums.

Posted by Arun Gupta on November 02, 2010 at 10:46 AM PDT #

Good instructions even though I had do some modifications in able to run everything on my setup. Cheers!

Posted by Frisör CSN on November 19, 2010 at 04:48 AM PST #

Post a Comment:
Comments are closed for this entry.
About

profile image
Arun Gupta is a technology enthusiast, a passionate runner, author, and a community guy who works for Oracle Corp.


Java EE 7 Samples

Stay Connected

Search

Archives
« February 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
       
       
Today