Recipe 21.1 Accessing a Database from a Servlet Without DataSource

Problem

You want to access a database from a servlet without a DataSource configuration for the database.

Solution

Use the Java Database Connectivity (JDBC) API to access a java.sql.Connection object that connects the servlet with the database.

Discussion

On occasion, developers require a quick, less elegant solution to accessing a database. This recipe explains how to use the java.sql.DriverManager class to obtain a connection to a datasource in a servlet. The DriverManager class communicates with a database driver, which is software that allows Java code to interact with a particular database, such as MySQL or Oracle.

The preferred design is to use a javax.sql.Datasource to get a database connection from a connection pool, as described in Recipe 21.2-Recipe 21.6.

Example 21-1 accomplishes this task in its doGet( ) service method.

Example 21-1. A servlet accesses a database using the JDBC API

package com.jspservletcookbook;           

import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class DatabaseServlet extends HttpServlet {

  public void doGet(HttpServletRequest request, 
     HttpServletResponse response) throws ServletException,
       java.io.IOException {
    
        String sql = "select * from athlete";

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        ResultSetMetaData rsm = null;
        
        response.setContentType("text/html");
        java.io.PrintWriter out = response.getWriter( );

        out.println(
            "<html><head><title>Servlet Database Access</title></head><body>");

        out.println("<h2>Database info</h2>");
        out.println("<table border='1'><tr>");
       
        try{
            
            //load the database driver
            Class.forName ("oracle.jdbc.driver.OracleDriver");

            //The JDBC URL for this Oracle database
            String url = "jdbc:oracle:thin:@192.168.0.2:1521:ORCL";

            //Create the java.sql.Connection to the database, using the 
            //correct username and password
            conn = DriverManager.getConnection(url,"scott", "tiger");
   
            //Create a statement for executing some SQL
            stmt = conn.createStatement( );
      
            //Execute the SQL statement
            rs = stmt.executeQuery(sql);
            
            //Get info about the return value in the form of
            //a ResultSetMetaData object
            rsm = rs.getMetaData( );

            int colCount =  rsm.getColumnCount( );
            
            //print column names in table header cells
            for (int i = 1; i <=colCount; ++i){
                
                out.println("<th>" + rsm.getColumnName(i) + "</th>");

            }
            
            out.println("</tr>");
          
            while( rs.next( )){
                
                 out.println("<tr>");
                
                //print the values for each column
                for (int i = 1;  i <=colCount; ++i)
                    out.println("<td>" + rs.getString(i) + "</td>");
                
                 out.println("</tr>");

                }

        } catch (Exception e){
            
            throw new ServletException(e.getMessage( ));

        } finally {
            
            try{
                
                //this will close any associated ResultSets
                if(stmt != null)
                    stmt.close( );

                if (conn != null)
                    conn.close( );

            } catch (SQLException sqle){ }
            
        }//finally
      
        out.println("</table><br><br>");

        out.println("</body>");
        out.println("</html>");
    
      
     } //doGet
}

Here are the steps needed to run a servlet, as shown in Example 21-1:

Take the JAR file that contains your database driver, and store it either in a common server directory, such as Tomcat's <Tomcat-root>/common/lib directory or in the WEB-INF/lib directory of your web application.

Change the extension of the Oracle JDBC driver (such as classes12.zip) to .jar, so that the Java classes that it contains can be loaded properly into the JVM.

Derive the database URL from vendor literature, and the username and password for the database from a database administrator (that might be you!) or other appropriate means. The code will not be able to access the database without a valid username and password.

The downside of this approach is that you are mixing up sensitive database security information with servlet code. It makes more sense to adopt the strategies that the upcoming five recipes describe, beginning with Recipe 21.2, "Configuring a DataSource in Tomcat."

Figure 21-1 shows the result of running this servlet.

Figure 21-1. A servlet that displays some database information