Chapter 13 Using Connector/J with GlassFish

Table of Contents

13.1 A Simple JSP Application with GlassFish, Connector/J and MySQL
13.2 A Simple Servlet with GlassFish, Connector/J and MySQL

This section explains how to use MySQL Connector/J with GlassFish ™ Server Open Source Edition 3.0.1. GlassFish can be downloaded from the GlassFish website.

Once GlassFish is installed, make sure it can access MySQL Connector/J. To do this, copy the MySQL Connector/J jar file to the domain-dir/lib directory. For example, copy mysql-connector-java-5.1.30-bin.jar to C:\glassfish-install-path\domains\domain-name\lib. Restart the GlassFish Application Server. For more information, see Integrating the JDBC Driver in GlassFish Server Open Source Edition Administration Guide, available at GlassFish Server Documentation.

You are now ready to create JDBC Connection Pools and JDBC Resources.

Creating a Connection Pool

  1. In the GlassFish Administration Console, using the navigation tree navigate to Resources, JDBC, Connection Pools.

  2. In the JDBC Connection Pools frame click New. You will enter a two step wizard.

  3. In the Name field under General Settings enter the name for the connection pool, for example enter MySQLConnPool.

  4. In the Resource Type field, select javax.sql.DataSource from the drop-down listbox.

  5. In the Database Vendor field, select MySQL from the drop-down listbox. Click Next to go to the next page of the wizard.

  6. You can accept the default settings for General Settings, Pool Settings and Transactions for this example. Scroll down to Additional Properties.

  7. In Additional Properties you will need to ensure the following properties are set:

    • ServerName - The server to connect to. For local testing this will be localhost.

    • User - The user name with which to connect to MySQL.

    • Password - The corresponding password for the user.

    • DatabaseName - The database to connect to, for example the sample MySQL database World.

  8. Click Finish to exit the wizard. You will be taken to the JDBC Connection Pools page where all current connection pools, including the one you just created, will be displayed.

  9. In the JDBC Connection Pools frame click on the connection pool you just created. Here, you can review and edit information about the connection pool. Because Connector/J does not support optimized validation queries, go to the Advanced tab, and under Connection Validation, configure the following settings:

    • Connection Validation - select Required.

    • Validation Method - select table from the drop-down menu.

    • Table Name - enter DUAL.

  10. To test your connection pool click the Ping button at the top of the frame. A message will be displayed confirming correct operation or otherwise. If an error message is received recheck the previous steps, and ensure that MySQL Connector/J has been correctly copied into the previously specified location.

Now that you have created a connection pool you will also need to create a JDBC Resource (data source) for use by your application.

Creating a JDBC Resource

Your Java application will usually reference a data source object to establish a connection with the database. This needs to be created first using the following procedure.

13.1 A Simple JSP Application with GlassFish, Connector/J and MySQL

This section shows how to deploy a simple JSP application on GlassFish, that connects to a MySQL database.

This example assumes you have already set up a suitable Connection Pool and JDBC Resource, as explained in the preceding sections. It is also assumed you have a sample database installed, such as world.

The main application code, index.jsp is presented here:


<%@ page import="java.sql.*, javax.sql.*, java.io.*, javax.naming.*" %>
<html>
<head><title>Hello world from JSP</title></head>
<body>
<%
  InitialContext ctx;
  DataSource ds;
  Connection conn;
  Statement stmt;
  ResultSet rs;

  try {
    ctx = new InitialContext();
    ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource");
    //ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource");
    conn = ds.getConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT * FROM Country");

    while(rs.next()) {
%>
    <h3>Name: <%= rs.getString("Name") %></h3>
    <h3>Population: <%= rs.getString("Population") %></h3>
<%    
    }
  }
  catch (SQLException se) {
%>
    <%= se.getMessage() %>
<%      
  }
  catch (NamingException ne) {
%>  
    <%= ne.getMessage() %>
<%
  }
%>
</body>
</html>

In addition two XML files are required: web.xml, and sun-web.xml. There may be other files present, such as classes and images. These files are organized into the directory structure as follows:

index.jsp
WEB-INF
   |
   - web.xml
   - sun-web.xml

The code for web.xml is:


<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
  <display-name>HelloWebApp</display-name>  
  <distributable/>
  <resource-ref>
    <res-ref-name>jdbc/MySQLDataSource</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>                
  </resource-ref>
</web-app>

The code for sun-web.xml is:


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 8.1 Servlet 2.4//EN" "http://www.sun.com/software/appserver/dtds/sun-web-app_2_4-1.dtd">
<sun-web-app>
  <context-root>HelloWebApp</context-root>
  <resource-ref>
    <res-ref-name>jdbc/MySQLDataSource</res-ref-name>
    <jndi-name>jdbc/MySQLDataSource</jndi-name>  
  </resource-ref> 
</sun-web-app>

These XML files illustrate a very important aspect of running JDBC applications on GlassFish. On GlassFish it is important to map the string specified for a JDBC resource to its JNDI name, as set up in the GlassFish administration console. In this example, the JNDI name for the JDBC resource, as specified in the GlassFish Administration console when creating the JDBC Resource, was jdbc/MySQLDataSource. This must be mapped to the name given in the application. In this example the name specified in the application, jdbc/MySQLDataSource, and the JNDI name, happen to be the same, but this does not necessarily have to be the case. Note that the XML element <res-ref-name> is used to specify the name as used in the application source code, and this is mapped to the JNDI name specified using the <jndi-name> element, in the file sun-web.xml. The resource also has to be created in the web.xml file, although the mapping of the resource to a JNDI name takes place in the sun-web.xml file.

If you do not have this mapping set up correctly in the XML files you will not be able to lookup the data source using a JNDI lookup string such as:

ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource");

You will still be able to access the data source directly using:

ds = (DataSource) ctx.lookup("jdbc/MySQLDataSource");

With the source files in place, in the correct directory structure, you are ready to deploy the application:

  1. In the navigation tree, navigate to Applications - the Applications frame will be displayed. Click Deploy.

  2. You can now deploy an application packaged into a single WAR file from a remote client, or you can choose a packaged file or directory that is locally accessible to the server. If you are simply testing an application locally you can simply point GlassFish at the directory that contains your application, without needing to package the application into a WAR file.

  3. Now select the application type from the Type drop-down listbox, which in this example is Web application.

  4. Click OK.

Now, when you navigate to the Applications frame, you will have the option to Launch, Redeploy, or Restart your application. You can test your application by clicking Launch. The application will connection to the MySQL database and display the Name and Population of countries in the Country table.

13.2 A Simple Servlet with GlassFish, Connector/J and MySQL

This section describes a simple servlet that can be used in the GlassFish environment to access a MySQL database. As with the previous section, this example assumes the sample database world is installed.

The project is set up with the following directory structure:

index.html
WEB-INF
   |
   - web.xml
   - sun-web.xml
   - classes
        |
        - HelloWebServlet.java
        - HelloWebServlet.class

The code for the servlet, located in HelloWebServlet.java, is as follows:


import javax.servlet.http.*;
import javax.servlet.*;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;

public class HelloWebServlet extends HttpServlet {

  InitialContext ctx = null;
  DataSource ds = null;
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;

  String sql = "SELECT Name, Population FROM Country WHERE Name=?";

  public void init () throws ServletException {
    try {
      ctx = new InitialContext();
      ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MySQLDataSource");
      conn = ds.getConnection();
      ps = conn.prepareStatement(sql);
    }
    catch (SQLException se) {
      System.out.println("SQLException: "+se.getMessage());
    }
    catch (NamingException ne) {
      System.out.println("NamingException: "+ne.getMessage());  
    }  
  }

  public void destroy () {
    try {
      if (rs != null)
        rs.close();
      if (ps != null)
        ps.close();
      if (conn != null)
        conn.close();
      if (ctx != null)
        ctx.close(); 
    }     
    catch (SQLException se) {
      System.out.println("SQLException: "+se.getMessage());
    }
    catch (NamingException ne) {
      System.out.println("NamingException: "+ne.getMessage());  
    }  
  }

  public void doPost(HttpServletRequest req, HttpServletResponse resp){
    try {
      String country_name = req.getParameter("country_name");    
      resp.setContentType("text/html");
      PrintWriter writer = resp.getWriter();
      writer.println("<html><body>");
      writer.println("<p>Country: "+country_name+"</p>");
      ps.setString(1, country_name);
      rs = ps.executeQuery();
      if (!rs.next()){
        writer.println("<p>Country does not exist!</p>");
      }
      else {
        rs.beforeFirst();
        while(rs.next()) {
          writer.println("<p>Name: "+rs.getString("Name")+"</p>");
          writer.println("<p>Population: "+rs.getString("Population")+"</p>");
        }
      }
      writer.println("</body></html>");
      writer.close(); 
    }
    catch (Exception e) {
      e.printStackTrace();
    }  
  }

  public void doGet(HttpServletRequest req, HttpServletResponse resp){
    try {    
      resp.setContentType("text/html");
      PrintWriter writer = resp.getWriter();
      writer.println("<html><body>");
      writer.println("<p>Hello from servlet doGet()</p>");
      writer.println("</body></html>");
      writer.close(); 
    }
    catch (Exception e) {
      e.printStackTrace();
    }  
  }
}

In the preceding code a basic doGet() method is implemented, but is not used in the example. The code to establish the connection with the database is as shown in the previous example, Section 13.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”, and is most conveniently located in the servlet init() method. The corresponding freeing of resources is located in the destroy method. The main functionality of the servlet is located in the doPost() method. If the user enters into the input form a country name that can be located in the database, the population of the country is returned. The code is invoked using a POST action associated with the input form. The form is defined in the file index.html:


<html>
  <head><title>HelloWebServlet</title></head>
  
  <body>
    <h1>HelloWebServlet</h1>
    
    <p>Please enter country name:</p>
    
    <form action="HelloWebServlet" method="POST">
      <input type="text" name="country_name" length="50" />
      <input type="submit" value="Submit" />
    </form>
    
  </body>
</html>

The XML files web.xml and sun-web.xml are as for the example in the preceding section, Section 13.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”, no additional changes are required.

When compiling the Java source code, you will need to specify the path to the file javaee.jar. On Windows, this can be done as follows:

shell> javac -classpath c:\glassfishv3\glassfish\lib\javaee.jar HelloWebServlet.java 

Once the code is correctly located within its directory structure, and compiled, the application can be deployed in GlassFish. This is done in exactly the same way as described in the preceding section, Section 13.1, “A Simple JSP Application with GlassFish, Connector/J and MySQL”.

Once deployed the application can be launched from within the GlassFish Administration Console. Enter a country name such as England, and the application will return Country does not exist!. Enter France, and the application will return a population of 59225700.