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:
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.
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:
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:
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>
<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.
) I need more dynamic processing in the mapping of a requested URI to a POJO/Controller. What does this mean?
Posted by Stew Welbourne 
