[ Team LiB ] Previous Section Next Section

Recipe 21.1 Accessing a Database from a Servlet Without DataSource


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


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


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;
        java.io.PrintWriter out = response.getWriter( );

            "<html><head><title>Servlet Database Access</title></head><body>");

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

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

            //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>");

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


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

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

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

            } catch (SQLException sqle){ }

     } //doGet

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

  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.

  1. 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

Chapter 23 on the JSTL shows how to use a JSP to access a database without a DataSource configuration.

See Also

The JDBC specification: http://java.sun.com/products/jdbc/download.html; Recipe 21.2-Recipe 21.6 on configuring and using DataSources on Tomcat and WebLogic; Recipe 21.7 and Recipe 21.8 on calling stored procedures from servlets and JSPs; Recipe 21.9 on converting a java.sql.ResultSet object to a javax.servlet.jsp.jstl.sql Result; Recipe 21.10 and Recipe 21.11 on using transactions in servlets and JSPs; Recipe 21.12 on finding out information about a ResultSet.

    [ Team LiB ] Previous Section Next Section