Database Connection Pooling Using Glassfish and MySQL with Remote Client

Aim
The aim of this tutorial is to configure a simple JNDI MySQL database pool in glassfish and access it using a java servlet and a remote java client. Leave any questions or problems as comments and I will endeavour to answer them.

Assumptions
This article assumes that you have Galssfish 2 installed and a domain configured. Please see Installing and Setting up Glassfish 2 for more details. For purposes of this example the glassfish domain is running on port 8082 and the project directory is MySQLGlassfishJNDI. This also assumes you have MySQL installed and ready to go.

Versions used in this example
Sofware/ComponentImage
Windows XP SP2N/A
Glassfish 2glassfish-installer-v2.1-b60e-windows.jar
MySQL 5.1 (no install)mysql-noinstall-5.1.32-win32.zip
MySQL Connector Jmysql-connector-java-5.1.7-bin.jar
Java JDK 1.5.0N/A
Links to these files can be found here

Although this is a quick 'helloworld' tutorial we will not sacrifice neatness for speed - so all the source files, class files etc will be kept in separate directories and as clean as possible. You will thank me for this later.

Create this webapp directory structure in your working directory- similar to something you would get from eclipse. The structure used in this example is given below. MySQLGlassfishJNDI will be referred to as your project or working directory. Create this structure. PoolUser.java will be the servlet and PoolUserClient.java will be the remore client.

MySQLGlassfishJNDI
_|_src
_|__|_PoolUser.java
_|__|_PoolUserClient.java
_|_WEB-INF
_|__|_web.xml
_|__|_classes
_|_bin

The Glassfish app server is installed in the D:\downloads\glassfish directory for this tutorial. I have MySQL up and running with a database called test that has a table called example with 2 columns (id and data). I'm accessing this via the root user with password root. Make sure to replace these values with the ones for your table.

Configure the database pool in Glassfish
  1. Login to the admin console in you Glassfish domain (see Installing and Setting up Glassfish 2 for more details).
  2. `
  3. Select Resources -> JDBC -> Connection Pools. Select New.
  4. `
  5. Set the name as MySQLPool, select javax.sql.DataSource from the "Resource Type" dropdown and  select MySQL from the "Database Vendor" dropdown. Click Next.
  6. `
  7. Make sure the "DataSource Classname" is showing com.mysql.jdbc.jdbc2.optional.MysqlDataSource. Click Finish.
  8. `
  9. Now select the MySQLPool and select the "Additional Properties" tab on top.
  10. `
  11. Search for the "Url" parameter and modify it to reflect your database
    jdbc:mysql://:3306/test?user=root&password=root
    Press "Save." As there is no explicit host it's considered localhost, the name of the database is test (came with install) and I'm using the root user. You can use whatever you wish. For completeness I've included an excerpt from the dump of the 'example' table.
     1. CREATE TABLE `example` (
     2.   `id` int(11) DEFAULT NULL,
     3.   `data` varchar(100) DEFAULT NULL
     4. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
     5. SET character_set_client = @saved_cs_client;
     6. 
     7. --
     8. -- Dumping data for table `example`
     9. --
    10. 
    11. LOCK TABLES `example` WRITE;
    12. /*!40000 ALTER TABLE `example` DISABLE KEYS */;
    13. INSERT INTO `example` VALUES (1,'one'),(2,'two'),(3,'three');
    14. /*!40000 ALTER TABLE `example` ENABLE KEYS */;
    15. UNLOCK TABLES;
    Hide line numbers
  12. `
  13. Do not forget to copy the mysql-connector-java-5.1.7-bin.jar file to your glassfish\lib directory and restart the domain.
  14. `
  15. Now click on the "General" tab at the top. Select "Ping." You should get a successful message. If not you should look at the Glassfish logs for your domain.
  16. `
  17. Now select Resources -> JDBC Resources. Select "New" and under "JNDI Name" enter "jdbc/MySQLPool" and select "MySQLPool" from the "PoolName" drop down.
  18. `
  19. Now go to your glassfish\bin directory and use asadmin to make sure your jndi jdbc is visible.

    D:\downloads\glassfish\bin>asadmin list-jndi-entries --context jdbc
    Jndi Entries for server within jdbc context:
    MySQLPool: javax.naming.Reference
    Command list-jndi-entries executed successfully.
Write and compile the servlet

  1. Write the servlet and save it under the "src" directory in your working directory as PoolUser.java. I have used the default database called "test" which has a table called example. Note 'test' was configured in the previous section.
     1. import java.sql.Connection;
     2. import java.sql.SQLException;
     3. import java.sql.Statement;
     4. import java.sql.ResultSet;
     5. import javax.sql.DataSource;
     6. 
     7. import javax.servlet.*;
     8. import javax.servlet.http.*;
     9. 
    10. import javax.naming.*;
    11. 
    12. public class PoolUser extends HttpServlet {
    13.     
    14.     public static void main(String args[]){
    15.         new PoolUser().doGet(null, null);
    16.     }
    17.     
    18.     public void doGet(HttpServletRequest request, HttpServletResponse response){
    19.     try{
    20.         InitialContext ctx = new InitialContext();
    21.         DataSource ds = (DataSource)ctx.lookup("jdbc/MySQLPool");
    22.         
    23.         Connection conn = ds.getConnection();
    24.         Statement stmt = conn.createStatement();
    25.        
    26.         ResultSet rs = stmt.executeQuery("select * from example");
    27.         
    28.         response.setContentType("text/html");
    29.         
    30.         response.getWriter().println("<html><body>");
    31.         while(rs.next()){
    32.             response.getWriter().println(rs.getString("id")+" "+rs.getString("data")+"<br/>");
    33.             
    34.         }
    35.         response.getWriter().println("</body></html>");
    36.         
    37.         stmt.close();
    38.         conn.close();
    39.     }catch(Exception e){
    40.         e.printStackTrace();
    41.     }
    42.     }
    43. }
    Hide line numbers
  2. `
  3. open a promt to the "src" directory and Compile the code. Use the "-d" ooption to copy the class to the WEB-INF/classes directory.
    ..workspace\MySQLGlassfishJNDI>javac -extdirs D:\downloads\glassfish\lib PoolUser.java
    -d ..\WEB-INF\classes
  4. `
  5. Create the web.xml file in the same WEB-INF directory.
     1. <?xml version="1.0" encoding="UTF-8"?>
     2. <web-app>
     3.   <display-name>OpenJMS sender/publisher</display-name>
     4. 
     5.       <servlet>
     6.         <servlet-name>MySQL Pool User</servlet-name>
     7.         <servlet-class>PoolUser</servlet-class>
     8.       </servlet>
     9. 
    10.       <servlet-mapping>
    11.         <servlet-name>MySQL Pool User</servlet-name>
    12.         <url-pattern>/pooluser</url-pattern>
    13.     </servlet-mapping>
    14.     
    15. </web-app>
    16. 
    Hide line numbers

Deploying the Service
  1. Login to you Glassfish admin console (see Installing and Setting up Glassfish 2 for more details).
  2. `
  3. Select Applications -> Web Applications and select "Deploy."
  4. `
  5. Under "Location" select "Local packaged file or directory..." and select "Browse Folders". Now navigate to your working directory. This is MySQLGlassfishJNDI in the case of this example. Select "Choose Folder." This should deploy your application.
  6. `
  7. Now navigate to 'http://127.0.0.1:8082/MySQLGlassfishJNDI/pooluser.' You should see a response depending on what data you decided to display in your servlet.

Writing and compiling the remote client

  1. Write the remote client and save it under the src directory as PoolUserClient.java.
     1. import java.io.*;
     2. import java.util.*;
     3. 
     4. import java.sql.Connection;
     5. import java.sql.SQLException;
     6. import java.sql.Statement;
     7. import java.sql.ResultSet;
     8. import javax.sql.DataSource;
     9. 
    10. 
    11. import javax.naming.*;
    12. 
    13. public class PoolUserClient {
    14.     
    15.     public static void main(String args[]){
    16.         new PoolUserClient().Go();
    17.     }
    18.     
    19.     public void Go(){
    20.     try{
    21.     Properties props = new Properties();
    22.     props.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.enterprise.naming.SerialInitContextFactory");       
    23.     props.put(Context.PROVIDER_URL,"iiop://127.0.0.1:3700");  
    24.         InitialContext ctx = new InitialContext(props);
    25. 
    26.         DataSource ds = (DataSource)ctx.lookup("jdbc/MySQLPool");
    27.         Connection conn = ds.getConnection();
    28.         Statement stmt = conn.createStatement();
    29.         ResultSet rs = stmt.executeQuery("select * from example");
    30.         
    31.         while(rs.next())
    32.           System.out.println(rs.getString("id")+" "+rs.getString("data"));
    33.         
    34.         stmt.close();
    35.         conn.close();
    36.     }catch(Exception e){
    37.         e.printStackTrace();
    38.     }
    39.     }
    40. }
    Hide line numbers
  2. `
  3. Now compile the client and install it into the "bin" directory in your working directory.
    ..workspace\MySQLGlassfishJNDI\src<javac -d ..\bin -extdirs D:\downloads\glassfish\lib PoolUserClient.java
  4. `
  5. Copy these files below from the glassfish\lib directory to the bin directory under your working directory
    appserv-rt.jar
    appserv-admin.jar
    appserv-deployment-client.jar
    javaee.jar

    Copy the imqjmsra.jar file from the glassfish\lib\install\applications\jmsra to the bin directory in your working directory.

    This is the best way to isolate these files so you can be sure to pick up the correct one, without specifying directories.
  6. `
  7. Now cd into the bin directory. Run the client.
    ..workspace\MySQLGlassfishJNDI\bin<java -Djava.ext.dirs=. PoolUserClient.java
    You should see the result on the screen.

Back to the tutorial trail | Home.

4 comments:

Big Endian said...

Hey, thanks for commenting on my blog post. I like your blog a lot, and it's in the same venue as mine, so I've added your blog to my blog roll.

Big Endian

P.s. Love the 7/9 pic

Tida19 said...

Your blog is benefits for me. Keeping update
I already make link to your blog.

Tida.
http://asabc.wordpress.com/

MR.Travel said...

thank you for this article .

http://www.isamsung.cz.cc

Naviya Nair said...

Very interesting and good Explanation
ASP NET Training
ASP NET Training
ASP NET Online Training
C-Sharp Training
Dot Net Training in Chennai
Online .Net Training


MVC Training
WCF Training
Web-API Training
LINQ Training
Entity Framework
Training

Dot Net Interview Questions