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:

  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
figs/jsjc_2101.gif

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 .



Java Servlet & JSP Cookbook
Java Servlet & JSP Cookbook
ISBN: 0596005725
EAN: 2147483647
Year: 2004
Pages: 326

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net