X

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

Guest Author

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

Join the discussion

Comments ( 20 )
  • Arun Gupta's Blog Tuesday, September 18, 2007
    [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...
  • Matt Field Monday, October 1, 2007

    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?


  • Arun Gupta's Blog Thursday, January 24, 2008
    [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...
  • Arun Gupta's Blog Friday, February 8, 2008
    [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...
  • David Sunday, March 2, 2008

    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


  • Arun Gupta Sunday, March 9, 2008

    David,

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

    -Arun


  • Abishek Kumar Tuesday, March 18, 2008

    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.


  • Arun Gupta Tuesday, March 18, 2008

    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


  • David Thursday, March 20, 2008

    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>


  • BX Tuesday, June 10, 2008

    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


  • Arun Gupta Tuesday, June 10, 2008

    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.


  • BX Thursday, June 12, 2008
  • guest Thursday, June 12, 2008

    df


  • Fred Sunday, June 15, 2008

    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?


  • Arun Gupta's Blog Friday, September 5, 2008
    [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...
  • Anurodh Shrivastava Friday, June 5, 2009

    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


  • Arun Gupta Monday, June 8, 2009

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


  • ajinkya Tuesday, November 2, 2010

    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...


  • Arun Gupta Tuesday, November 2, 2010

    ajinkya,

    Please consider posting your questions in JOnAS Application Server forums.


  • Fris&ouml;r CSN Friday, November 19, 2010

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


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.