Glassfish v2 UR1 and MySQL Connection Pool

I’ve just spent the last 2 days getting a MySQL connection pool enabled on Glassfish. I need to caveat what I’m about to say with the fact that this is the first time I’ve attempted to do this – hailing from a Ruby/Rails background so there may be obvious points I’ve missed, but there-again I’ve been on the Sun site, Google and all sorts of technical resources for 2 days trying to get a clear example of this. My initial goal is simply to prove connectivity between user, servlet, jdbc connection pool and datasource in a very simple way. (My intended next steps will start to introduce more advanced data access and persistence methods).

The scenario is simple. A Glassfish hosted Http Servlet which relies upon a container connection-pool to get access to a a simple MySQL5.1 datasource. I had the initial  direct jdbc connection working immediately, such that my servlet created all necessary connectivity with the datasource in-line. Here is a snippet from my servlet doGet() function:

String url = "jdbc:mysql://localhost:3306/mysql_database_name";
 String query = "SELECT * FROM APP_TEMPLATES";
 try {
  Class.forName ("com.mysql.jdbc.Driver");
  Connection con = DriverManager.getConnection( url, "user", "password" );
  Statement stmt = con.createStatement ();
  ResultSet rs = stmt.executeQuery (query);
  printResultSet ( resp, rs );
  rs.close();
  stmt.close();
  con.close();
  } // end try

 Next step was to drop the connectivity down into a container-managed scheme and the JDBC RESOURCES and JDBC CONNECTION-POOLS in Glassfish seemed like the best way to go. This is where my pain started. I was only able to find partial configuration examples, where some code samples showed the servlet end (i.e. JNDI resolution to a notional JDBC resource) and others showed XML container descriptors used by a notional client. I had to make assumptions about the linkages between the configs…this being my first attempt at configuring one of these things.

So, I followed the basic Glassfish Administration steps of creating a Connection Pool, using the Administration console (http://localhost:4848). The creation of the pool is relatively straight forwards. Firstly specify the basicis:

connection pool basics

 And then clicking next, fill out any connector/driver specific attributes. Most of the important settings are right at the bottom of the list of properties – I left all of the upper properties at their default values specified by the driver type.

cp advanced

Once you have completed the advanced settigns you can save and test the pool connectivity with the datasource by using the PING option at the top of the page:

cp ping

Once we have the fully-functional container connection-pool, the next step is to declate the JDBC Resource which presents this connection pool to the application code. For this you must use the other Administration option in the JDBC section:

jdbc resource

This is pretty straight forwards – and the JNDI Name must have the ‘/jdbc’ prefix to show up in the correct context branch for when you locate the resource programatically (more on this later). At this point you have the Glassfish container configured correctly to offer you a jdbc connection pool and resource.

Turning my attention to the application code, I then modified my Servlet code to do two things:

1. Introduce an init() method to enable me to resolve and obtain required references to the connection pool via the JNDI resource name I’ve configured via container Admin earlier. Assume that the name of my JDBC Resource is “jdbc/myJDBCResourceName“.

public void init() throws ServletException{
  Context env=null;
  try{
  env=(Context) new InitialContext().lookup("java:comp/env");
  pool=(DataSource)env.lookup("jdbc/myJDBCResourceName");
  if(pool==null){
  throw new ServletException("'unknown DataSource");
  }
  }catch(NamingException ne){
  throw new ServletException(ne.getMessage());
  }
  }

 2. Modify my doGET() method to take advantage of the pool object, thus replacing the earlier direct driver connection I was using. Notice that the pool object is my reference to the underlying array of connections at runtime. This really simplifies my doGet() connectivity code:

try{
  conn=pool.getConnection();
  stmt=conn.createStatement();
  rs=stmt.executeQuery(sql);
  rsm=rs.getMetaData();
  // Do Stuff With the Result Set
  }catch(Exception e){
  throw new ServletException(e.getMessage());
  }finally{
  try{
  if(stmt!=null) stmt.close();
  if(conn!=null) conn.close();
  }catch(SQLException sqle){}
  }

Here I begain to hit problems – where each time I deployed and executed my application I was seeing JNDI resolution exceptions telling me that ‘no object is bound to java:comp/env/jdbc/myJDBCResourceName’. This is where my lack of familiarity with container deployment descriptors hit very hard!

I then learned that I had to include references to the JDBC resources in the deployment descriptors web.xml and sun-web.xml. I found various instructions about how to go about this – and Netbeans6.0 (my IDE) provides a nice visual overlay for the XML files in question. The first file web.xml requires the registration of an entry in the Resource References section. The reason I believe is to provide a linkage between the resource expression used in the web-app code, to a known resource in the container config.

Similarly the sun-web.xml provides more specific container mappings between ‘abstract’ resource references in the web application and specific resources named within the container (Glassfish config). As such a similar entry is needed in this secondary file….and this is where I had most difficulty.

  • In simple terms my code was referencing pool=(DataSource)env.lookup(“jdbc/myJDBCResourceName”);
  • I had configured a JDBC resource names “jdbc/myJDBCResourceName” in the Glassfish container, which mapped to a Connection Pool called MyConnectionPool which I had confirmed as working correctly.
  • I had a web.xml  Resource Reference of:    

    <resource-ref>
        <res-ref-name>jdbc/myJDBCResourceName</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

  • I had a sun-web.xml Resource-Reference of:

  <resource-ref>
    <res-ref-name>jdbc/myJDBCResourceName</res-ref-name>
    <jndi-name>MyConnectionPool</jndi-name>
  </resource-ref>

So what was the problem? I was constantly being informed that my Servlet could not locate the “MyConnectionPool” object…but all of my mappings from application code down to container were intact! Frustration was not the word.

However I then learned that I had made a mistake when inferring meaning for one of the settings – specifically the sun-web.xml resource reference of <jndi-name>MyConnectionPool</jndi-name>

I had made a false assumption that the 2 levels of xml descriptor were there to enable an APPLICATION to RESOUCE, and then a RESOURCE to CONNECTION POOL mapping. However the connection pool should not be referenced at all and in fact, the JNDI name was required to be set to the same as as the resource name? The following tweak to the sun-web.xml fixed the problem and my Servlet worked perfectly:

  <resource-ref>
    <res-ref-name>jdbc/myJDBCResourceName</res-ref-name>
    <jndi-name>jdbc/myJDBCResourceName</jndi-name>
  </resource-ref>

At this point I lost the plot in terms of understanding the need for this second descriptor which effectively has to have the same JNDI name specified twice – and is effectively a null mapping and therefore just unecessary complexity!?!  At this point I realised that I was potentially able to decouple the resource references in the application code with those at the web.xml level and again from the sun-web.xml level and use the following configuration:

web.xml:

    <resource-ref>
        <res-ref-name>aResourceName</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

sun-web.xml:

  <resource-ref>
    <res-ref-name>aResourceName</res-ref-name>
    <jndi-name>jdbc/myJDBCResourceName</jndi-name>
  </resource-ref>

With this configuration my servlet connectivity routine has use the new resource reference ‘aResourceName’ which has no relationship to the container JNDI resource name, and it is the xml descriptors which enable the linkage to be established at runtime. As such with this config in place, the container connection pool is working fine, although I’m currently at a loss in understanding the relative merits of each approach. I’d appreciate input from anyone who knows the official line on how the servlet, web.xml, sun-web.xml and container JNDI naming conventions should be established – but at least I have a way forwards now.

Advertisements

24 Responses to Glassfish v2 UR1 and MySQL Connection Pool

  1. Stew says:

    So a couple of hours after documenting my route to this point I discovered the use of a persistence unit is far simpler and encapsulates the mapping to the underlying container source along with the Object-Relational stuff. Using a persistence unit – from scratch took seconds….if only I’d have started out that way. It’s the best way to learn !

  2. Finally I found a quick and very usefull tutorial “Glassfish v2 UR1 and (MySQL) Connection Pool”

    Did not found any relevant information before, Glassfish seems promissing server but still more people have to get involved for experience feedback.

    Thx.

  3. William Baker says:

    There are multiple ways to deploy projects. In development, on of the easier ways is directory based, ie, no WAR, EAR, etc. Although changes to most content files take effect immediately, not all changes do. For newbies, restarting the app-server after changing files would seem to be the quick solution. However, not all changes even take effect after a server restart! In particular, it appears changes to web.xml and sun-web.xml will not take effect even after a glassfish server restart! The application must be re-deployed. NetBeans does this somewhat behind the scenes. Other developers may need to understand the asadmin commands in more detail.

  4. yogesh ghodke says:

    I am facing one problem.
    I want to provide different data sources for different applications in web.xml and context.xml. This should lead to create war for different applications without modifying other contents or coding. Is it possible. Is there any way that will change the data source for application without deploying application in tomcat?

    Thanks in advance.

  5. Stew Welbourne says:

    Sorry – can’t help you with that one…

  6. Julian Osorio Amaya says:

    Hello everybody

    I’m trying to create a Connection Pool using mysql
    the datasource class name is com.mysql.jdbc.jdbc2.optional.MysqlDataSource
    the resource type is javax.sql.DataSource
    and then leave the default settings in General and Advanced options
    in the additional properties options set the info from mysql database
    servername
    username
    password
    port
    databasename

    save the changes and try to make a ping
    next shows the next message:

    Error An error has occurred
    Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource

    what can I do now?

    Thanks for your help

  7. Todd Isaacs says:

    I had to drop the mysql-connector-java*.jar file in the appserver lib directory to get the ping to work. I also had to restart the appserver.

  8. Francois van Niekerk says:

    Thanx so much. It took me ages to get this working and usually I would get something working in almost no time. I was getting really frustrated with this.

    You saved me quite a bit of wasted time 😀 thanx

  9. Stew Welbourne says:

    Great news. Thanks for taking the time to provide this positive feedback !

  10. Ajdin Brandic says:

    Very useful instructions/tutorial. I had an error similar to “Julian Osorio Amaya” which occurred when I tried to Ping. Basically I had to put mysql driver into “glasfish\domains\domain1\lib\ext” folder. For those MySQL driver can be downloaded from http://dev.mysql.com/downloads/connector/. Perhaps you could add this bit to your tutorial. Thanks

  11. Gus Gonzalez says:

    Hi everyone!!! I was expending all day triying to set a mysql connection pool, I already did everything that you said but I still have the same problem javax.resource.ResourceException: Class name is wrong or classpath is not set for : com.mysql.jdbc.jdbc2.optional.MysqlDataSource. Put the mysql-connector*.jar in lib/ext/ restart the server and nothing and set a classpath into JVM settings. Please some idea?

  12. shadowBean says:

    I have the same problem with Gus and Julian. Tried putting connector in many places and set system environment in windows.. Nothing works. Any more ideas please.

  13. Szmg says:

    I tried

    pool=(DataSource)new InitialContext().lookup(“jdbc/myJDBCResourceName”);

    without xml modifications, and it works.

  14. Baptiste says:

    Very helpful article.

    For those who get the “Class name is wrong or classpath is not set …” error, read the different replies above carefully.
    In particular, as Todd Isaacs points it, you have to restart Glassfish, after you have put your JDBC driver into the lib/ext folder.

  15. nacho cano says:

    i solved the problem “Error An error has occurred: Unknow problem” replacing the file C:\Program Files\glassfish-v2ur2\lib\mysql-connector-java-5.1.6-bin.jar for this one mysql-connector-java-5.0.8-bin.jar

  16. I had to drop the connection jar in C:\Sun\SDK\bin and also add the URL property to jdbc:mysql://localhost:3306/mysql and then it I was able to get success from the ping

  17. Darwin Layog says:

    Thanks for all the posts. I have spent a couple of days solving the classname/classpath error. Adding the JDBC driver into the lib/ext folder made it work.

  18. komarios says:

    There is another simple GlassFish mysql connection pool tutorial here :
    http://mariosgaee.blogspot.com/2009/12/mysql-connection-pool-in-glassfish.html

  19. Pedro Franco says:

    Segui os mesmos passos descritos no artigo e ainda continuo tendo o mesmo problema: javax.servlet.ServletException: javax.naming.NameNotFoundException: No object bound to name java:comp/env/jdbc/Connection
    o que pode ser? a única coisa diferente eh que estou usando banco postgres

  20. Narayan says:

    Excellent post.

    Thanks for all you help today.

  21. Kandy says:

    Thanks for the post,
    I spent three days trying to configure the datasource, and thanks to your post it works fine now.
    best regards from Vigo, Spain.

  22. how to remove the connection says:

    Thank you for the article,
    but I want to know
    1) How to remove the connection pool, I delete the connection pool on admin console, but late on it come back
    Thank you

  23. Dean says:

    Thanks for working through this, it is very helpful. I have been struggling with this for a few days. The complexity of the setup process indicates a poor design. Whoever created this needs a lesson in using abstraction properly. This aspect of GlassFish is badly in need of a refactor. Connection pools should be the default and a simple entry in the web.xml should create the connection pool when the app loads.

    They’ve recently added dependency injection via the @Resource annotation and element in web.xml, but judging by the forum posts, CDI works sporadically or not at all. The element doesn’t even appear the web.xml’s XSD.

    All-in-all I hope Oracle will devote more experienced programmers to at least consult on GlassFish so we don’t end up with sub-standard designs, as in this case.

    Thanks again Stew.

  24. Dean says:

    Looks like data-source element was filtered in the above post…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: