Connecting to Other Databases

Connecting to MySQL

With the JNDI configuration set in the Resin configuration file, all an application has to do is access it. Listing 11.2 shows the code from Listing 11.1 changed to use the JNDI information.

Listing 11.2: MySQL connection using a JNDI configuration.

 Line 1: package servlet; import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; import javax.naming.*; import javax.sql.*; Line 10: public class JNDI extends HttpServlet {   DataSource _ds; public void init()   throws ServletException {   try {     Context ic =       (Context) new InitialContext();    _ds = (DataSource) ic.lookup("java:comp/env/jdbc/mysql"); Line 20:     if (_ds == null)       throw new ServletException("'jdbc/mysql' - unknown");     }   }   public void doGet(HttpServletRequest inRequest,     HttpServletResponse outResponse) Line 30:    throws ServletException, IOException {     Connection myConnection = null;     Statement myStatement = null;     ResultSet myResultSet = null;     outResponse.setContentType("test/html");     PrintWriter out = outResponse.getWriter();     try { Line 40:      myConnection = ds.getConnection();       myStatement = myConnection.createStatement();       myResultSet = myStatement.executeQuery("SELECT title,         price FROM product");       out.println("<HTML><HEAD><TITLE>MySQL          Example</TITLE></HEAD>");       out.println("<BODY>");       out.println("<UL>"); Line 50:      while (myResultSet.next()) {         out.println("<LI>" + myResultSet.getString("title")         + " " + myResultSet.getString("price"));       }       out.println("</UL>");       out.println("</BODY></HTML>");     }     catch(SQLException e) {   out.println("SQLException caught: "+ e.getMessage()); } finally {   myConnection.close(); } Line 60:  } }

The results of the application are shown in Figure 11.1.

click to expand
Figure 11.1: MySQL JDNI application output.

In the initial database access code, the application was responsible for loading the driver class as well as creating the connection to the database. In Listing 11.2, things are quite different. First, notice the addition of the init() method in lines 13-26 as well as the DataSource attribute in line 11.

These new pieces of code are used when the servlet is first called into action. The init() method handles finding the JNDI entries for the database and setting up the application to use the entry. The real work begins in line 17, where an InitialContext object is instantiated. This object pulls the configuration information added to the resin.conf file. In line 19, a call is made to the lookup() method to find the value represented by jdbc/mysql. Recall that this value was used in the res-ref-name element of the JNDI information put into the resin.conf file.

If the lookup() method can't find an appropriate entry in the configuration file, the DataSource variable _ds is set to null and an exception is thrown. If the lookup() method is successful, the DataSource variable _ds is assigned the values found as part of the matched jndi-name element. This is where all the JNDI magic takes places: A simple lookup populates an entire object with the information found. In this case, the DataSource class contains all the information necessary to create a connection to the database.

Once the init() method has finished, the doGet() method processes the call from the client's Web browser. Line 40 is the only other change made to the code from Listing 11.1. In this line, a Connection object is instantiated from the DataSource object. If the getConnection() method is successful in obtaining a connection with the associated database, a new Connection object is returned from the method. After this line, all the code is the same, and information is extracted from the database.