17.2 Basic JDBC Examples

In this section, we present two simple JDBC examples that connect to the Microsoft Access Northwind database (shown in Figure 17-2) and perform a simple query. The Northwind database is included in the samples section of Microsoft Office. To configure the Northwind database for access from JDBC, see Section 18.1.

Figure 17-2. Microsoft Access Northwind sample database showing the first four columns of the Employees table. See Section 18.1 for information on using this database.

Northwind is a good database for testing and experimentation since it is already installed on many systems and since the JDBC-ODBC bridge for connecting to Microsoft Access is already bundled in the JDK. However, Microsoft Access is not intended for serious online databases. For production purposes, a higher-performance option like MySQL (see Section 18.2), Oracle9i (see Section 18.3), Microsoft SQL Server, Sybase, or DB2 is far better.

The first example, Listing 17.1, presents a standalone class called NorthwindTest that follows the seven steps outlined in the previous section to display the results of querying the Employee table.

The results for the NorthwindTest are shown in Listing 17.2. Since NorthwindTest is in the coreservlets package, it resides in a subdirectory called coreservlets . Before compiling the file, set the CLASSPATH to include the directory containing the coreservlets directory. See Section 2.7 (Set Up Your Development Environment) for details. With this setup, simply compile the program by running javac NorthwindTest.java from within the coreservlets subdirectory (or by selecting "build" or "compile" in your IDE). To run NorthwindTest , you need to refer to the full package name with java coreservlets. NorthwindTest .

The second example, Listing 17.3 ( NorthwindServlet ), connects to the database from a servlet and presents the query results as an HTML table. Both Listing 17.1 and Listing 17.3 use the JDBC-ODBC bridge driver, sun.jdbc.odbc.JdbcOdbcDriver , included with the JDK.

Listing 17.1 NorthwindTest.java

 package coreservlets; import java.sql.*; /** A JDBC example that connects to the MicroSoft Access sample  *  Northwind database, issues a simple SQL query to the  *  employee table, and prints the results.  */ public class NorthwindTest {   public static void main(String[] args) {     String driver = "sun.jdbc.odbc.JdbcOdbcDriver";     String url = "jdbc:odbc:Northwind";     String username = ""; // No username/password required     String password = ""; // for desktop access to MS Access.     showEmployeeTable(driver, url, username, password);   }   /** Query the employee table and print the first and    *  last names.    */   public static void showEmployeeTable(String driver,                                        String url,                                        String username,                                        String password) {     try {       // Load database driver if it's not already loaded.  Class.forName(driver);  // Establish network connection to database.  Connection connection =   DriverManager.getConnection(url, username, password);  System.out.println("Employees\n" + "==========");       // Create a statement for executing queries.  Statement statement = connection.createStatement();   String query =   "SELECT firstname, lastname FROM employees";  // Send query to database and store results.  ResultSet resultSet = statement.executeQuery(query);  // Print results.  while(resultSet.next()) {   System.out.print(resultSet.getString("firstname") + " ");   System.out.println(resultSet.getString("lastname"));  }  connection.close();  } catch(ClassNotFoundException cnfe) {       System.err.println("Error loading driver: " + cnfe);     } catch(SQLException sqle) {       System.err.println("Error with connection: " + sqle);     }   } } 

Listing 17.2 NorthwindTest Result

 Prompt>  java coreservlets.NorthwindTest  Employees ========== Nancy Davolio Andrew Fuller Janet Leverling Margaret Peacock Steven Buchanan Michael Suyama Robert King Laura Callahan Anne Dodsworth 

For the second example, NorthwindServlet (Listing 17.3), the information for performing the query is taken from an HTML form, NorthwindForm.html , shown in Listing 17.4. Here, you can enter the query into the form text area before submitting the form to the servlet. The servlet reads the driver, URL, username, password, and the query from the request parameters and generates an HTML table based on the query results. The servlet also demonstrates the use of DatabaseMetaData to look up the product name and product version of the database. The HTML form is shown in Figure 17-3; Figure 17-4 shows the result of submitting the form. For this example, the HTML form and servlet are located in the Web application named jdbc . For more information on creating and using Web applications, see Section 2.11.

Listing 17.3 NorthwindServlet.java

 package coreservlets; import java.io.*; import java.sql.*; import javax.servlet.*; import javax.servlet.http.*; /** A simple servlet that connects to a database and  *  presents the results from the query in an HTML  *  table. The driver, URL, username, password,  *  and query are taken from form input parameters.  */ public class NorthwindServlet extends HttpServlet {   public void doPost(HttpServletRequest request,                      HttpServletResponse response)       throws ServletException, IOException {     response.setContentType("text/html");     PrintWriter out = response.getWriter();     String docType =       "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " +       "Transitional//EN\"\n";     String title = "Northwind Results";     out.print(docType +               "<HTML>\n" +               "<HEAD><TITLE>" + title + "</TITLE></HEAD>\n" +               "<BODY BGCOLOR=\"#FDF5E6\"><CENTER>\n" +               "<H1>Database Results</H1>\n");  String driver = request.getParameter("driver");   String url = request.getParameter("url");   String username = request.getParameter("username");   String password = request.getParameter("password");   String query = request.getParameter("query");  showTable(driver, url, username, password, query, out);     out.println("</CENTER></BODY></HTML>");   }   public void showTable(String driver, String url,                         String username, String password,                         String query, PrintWriter out) {     try {       // Load database driver if it's not already loaded.  Class.forName(driver);  // Establish network connection to database.  Connection connection =   DriverManager.getConnection(url, username, password);  // Look up info about the database as a whole.  DatabaseMetaData dbMetaData = connection.getMetaData();  out.println("<UL>");  String productName =   dbMetaData.getDatabaseProductName();   String productVersion =   dbMetaData.getDatabaseProductVersion();  out.println("  <LI><B>Database:</B> " + productName +                   "  <LI><B>Version:</B> " + productVersion +                   "</UL>");  Statement statement = connection.createStatement();  // Send query to database and store results.  ResultSet resultSet = statement.executeQuery(query);  // Print results.       out.println("<TABLE BORDER=1>");  ResultSetMetaData resultSetMetaData =   resultSet.getMetaData();   int columnCount = resultSetMetaData.getColumnCount();  out.println("<TR>");       // Column index starts at 1 (a la SQL), not 0 (a la Java).       for(int i=1; i <= columnCount; i++) {         out.print("<TH>" +  resultSetMetaData.getColumnName(i)  );       }       out.println();       // Step through each row in the result set.       while(resultSet.next()) {         out.println("<TR>");         // Step across the row, retrieving the data in each         // column cell as a String.         for(int i=1; i <= columnCount; i++) {           out.print("<TD>" +  resultSet.getString(i)  );         }         out.println();       }       out.println("</TABLE>");  connection.close();  } catch(ClassNotFoundException cnfe) {       System.err.println("Error loading driver: " + cnfe);     } catch(SQLException sqle) {       System.err.println("Error connecting: " + sqle);     } catch(Exception ex) {       System.err.println("Error with input: " + ex);     }   } } 

Listing 17.4 NorthwindForm.html

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Simple Query Form</TITLE> <LINK REL=STYLESHEET       HREF="JSP-Styles.css"       TYPE="text/css"> </HEAD> <BODY> <H2>Query Input:</H2> <FORM ACTION="/jdbc/servlet/coreservlets.NorthwindServlet"       METHOD="POST"> <TABLE>   <TR><TD>Driver:       <TD><  INPUT TYPE="TEXT" NAME="driver"   VALUE="sun.jdbc.odbc.JdbcOdbcDriver" SIZE="35">  <TR><TD>URL:       <TD>  <INPUT TYPE="TEXT" NAME="url"   VALUE="jdbc:odbc:Northwind" SIZE="35">  <TR><TD>Username:       <TD><INPUT TYPE="TEXT" NAME="username">   <TR><TD>Password:       <TD>  <INPUT TYPE="PASSWORD" NAME="password">  <TR><TD VALIGN="TOP">Query:       <TD><  TEXTAREA ROWS="5" COLS="35" NAME="query"></TEXTAREA>  <TR><TD COLSPAN="2" ALIGN="CENTER"><INPUT TYPE="SUBMIT"> </TABLE> </FORM> </BODY></HTML> 

Figure 17-3. NorthwindForm.html : front end to servlet that queries the Northwind database.

Figure 17-4. Result of querying the Northwind database.

In the preceding example, the HTML table was generated from the query results within a servlet. In Volume 2 of this book, we present various custom tags to generate the HTML table from the query results in the JSP page itself. Furthermore, if your development model favors JSP, the JSP Standard Tag Library (JSTL) provides an sql:query action to query a database and store the query result in a scoped variable for processing on the JSP page. JSTL is also covered in Volume 2 of this book.