Page #483 (35.10. Forwarding Requests from JavaServer Pages) | Introduction to Java Programming-Comprehensive Version (6th Edition)

[Page 1232 ( continued )]

35.11. Case Study: Browsing Database Tables

This section presents a very useful JSP application for browsing tables. When you start the application, the first page prompts the user to enter the JDBC driver, URL, username, and password for a database, as shown in Figure 35.8. After you log in to the database, you can select a table to browse, as shown in Figure 35.9. Upon clicking the Browse Table Content button, the table content is displayed, as shown in Figure 35.10.

Figure 35.8. To access a database, you need to provide the JDBC driver, URL, username, and password.

[Page 1233]

Figure 35.9. You can select a table to browse from this page.

Figure 35.10. The contents of the selected table are displayed.

Create a JavaBeans component named DBBean.java (Listing 35.21) and compile it into c:\jakarta-tomcat-5.5.9\webapps\liangweb\WEB-INF\classes\chapter35 .

Listing 35.21. DBBean.java

(This item is displayed on pages 1233 - 1235 in the print version)

 1    package   chapter35;  2 3   import   java.sql.*; 4 5 .    public class   DBBean  { 6   private   Connection connection =   null   ; 7   private   String username; 8   private   String password; 9   private   String driver; 10   private   String url; 11 12  /** Initialize database connection */  13   public void   initializeJdbc() { 14   try   { 15 System.out.println(   "Driver is "   + driver); 16  Class.forName(driver);  17 18  // Connect to the sample database  19  connection = DriverManager.getConnection(url, username,  20  password);  21 }

[Page 1234]

 22   catch   (Exception ex) { 23 ex.printStackTrace(); 24 } 25 } 26 27  /** Get tables in the database */  28   public   String[] getTables() { 29 String[] tables =   null   ; 30 31   try   { 32  DatabaseMetaData dbMetaData = connection.getMetaData();  33  ResultSet rsTables = dbMetaData.getTables(   null   ,   null   ,   null   ,  34    new   String[] {   "TABLE"   });  35 36   int   size =     ; 37   while   (rsTables. next ()) size++; 38 39  rsTables = dbMetaData.getTables(   null   ,   null   ,   null   ,  40    new   String[] {   "TABLE"   });  41 42 tables =   new   String[size]; 43   int   i =     ; 44   while   (  rsTables.next()  ) 45  tables[i++] = rsTables.getString(   "TABLE_NAME"   );  46 } 47   catch   (Exception ex) { 48 ex.printStackTrace(); 49 } 50 51    return   tables;  52 } 53 54  /** Return connection property */  55   public   Connection getConnection() { 56   return   connection; 57 } 58 59   public void   setUsername(String newUsername) { 60 username = newUsername; 61 } 62 63   public   String getUsername() { 64   return   username; 65 } 66 67   public void   setPassword(String newPassword) { 68 password = newPassword; 69 } 70 71   public   String getPassword() { 72   return   password; 73 } 74 75   public void   setDriver(String newDriver) { 76 driver = newDriver; 77 } 78 79   public   String getDriver() { 80   return   driver; 81 }

[Page 1235]

 82 83   public void   setUrl(String newUrl) { 84 url = newUrl; 85 } 86 87   public   String getUrl() { 88   return   url; 89 } 90 }

Create an HTML file named DBLogin.html (Listing 35.22) that prompts the user to enter database information and three JSP files named DBLoginInitialization.jsp (Listing 35.23), Table.jsp (Listing 35.24), and BrowseTable.jsp (Listing 35.25) to process and obtain database information. All the files are stored in c:\jakarta-tomcat5.5.9\webapps\liangweb .

Listing 35.22. DBLogin.html

 1  <!-- DBLogin.html -->  2   <html>   3   <head>   4   <title>   5 DBLogin 6   </title>   7   </head>   8   <body>   9   <form method   =   "post"   10   action   =    "/liangweb/DBLoginInitialization.jsp"      >   11 JDBC Driver 12   <select name   =   "driver"   size   =   "1"   >   13   <option>   sun.jdbc.odbc.JdbcOdbcDriver   </option>   14   <option>   oracle.jdbc.driver.OracleDriver   </option>   15   </select><br /><br />   16 JDBC URL 17   <select name   =   "url"   size   =   "1"   > 18   <option>   jdbc:odbc:ExampleMDBDataSource   </option>   19   <option>   jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl   </option>   20   <option>   jdbc:oracle:thin:@localhost:1521:test   </option>   21   </select><br /><br />   22 Username   <input name   =   "username"   /><br /><br />   23 Password   <input name   =   "password"   /><br /><br />   24   <input type   =   "submit"   name   =   "Submit"   value   =   "Login"   />   25   <input type   =   "reset"   value   =   "Reset"   />   26   </form>   27   </body>   28   </html>

Listing 35.23. DBLoginInitialization.jsp

(This item is displayed on pages 1235 - 1236 in the print version)

 1  <!-- DBLoginInitialization.jsp -->  2    <%@ page import   =   "chapter35.DBBean"   %>    3    <jsp:useBean id   =   "dBBeanId"   scope =   "session"    4    class   =   "chapter35.DBBean"   >  5    </jsp:useBean>    6    <jsp:setProperty name   =   "dBBeanId"   property   =   "*"   />    7   <html>   8   <head>   9   <title>   DBLoginInitialization   </title>   10   </head>   11   <body>

[Page 1236]

 12 13  <% ” ” Connect to the database ” ”%>  14   <%    dBBeanId.initializeJdbc();    %>   15 16   <% if   (dBBeanId.getConnection() ==   null   ) {   %>   17 Error: Login failed. Try again. 18   <%   } 19 else {   %>   20    <jsp:forward page   =   "Table.jsp"   />    21   <% } %>   22   </body>   23   </html>

Listing 35.24. Table.jsp

 1  <!-- Table.jsp -->  2    <%@ page import   =   "chapter35.DBBean"   %>    3    <jsp:useBean id   =   "dBBeanId"   scope   =   "session"    4    class   =   "chapter35.DBBean"   >  5    </jsp:useBean>    6   <html>   7   <head>   8   <title>   Table   </title>   9   </head>   10   <body>   11   <%    String[] tables = dBBeanId.getTables();  12   if   (tables ==   null   ) {   %>   13 No tables 14   <%   } 15   else   {   %>   16   <form method   =   "post"   action   =   "BrowseTable.jsp"   > 17 Select a table 18   <select name   =   "tablename"   size   =   "1"   > 19   <% for   (   int   i =     ; i < tables.length; i++) {   %>   20   <option><%=   tables[i]   %></option>   21   <%   } 22 }   %>   23   </select><br /><br /><br />   24   <input type   =   "submit"   name   =   "Submit"   25   value   =   "Browse Table Content"   >   26   <input type   =   "reset"   value   =   "Reset"   >   27   </form>   28   </body>   29   </html>

Listing 35.25. BrowseTable.jsp

(This item is displayed on pages 1236 - 1237 in the print version)

 1  <!-- BrowseTable.jsp -->  2   <%@ page import   =   "chapter35.DBBean"   %>   3   <jsp:useBean id   =   "dBBeanId"   scope   =   "session"   4   class   =   "chapter35.DBBean"   >   5   </jsp:useBean>   6   <%@ page import   =   "java.sql.*"   %>   7   <html>   8   <head>   9   <title>   BrowseTable   </title>   10   </head>   11   <body>

[Page 1237]

 12 13   <%   String tableName = request.getParameter(   "tablename"   ); 14 15 ResultSet rsColumns = dBBeanId.getConnection().getMetaData(). 16 getColumns(   null   ,   null   , tableName,   null   ); 17   %>   18   <table border   =   "1"   >   19   <tr>   20   <%   // Add column names to the table 21   while   (rsColumns.next()) {   %>   22   <td><%=   rsColumns.getString(   "COLUMN_NAME"   )   %></td>   23   <%   }   %>   24   </tr>   25 26   <%   Statement statement = 27 dBBeanId.getConnection().createStatement(); 28  ResultSet rs = statement.executeQuery(  29    "select * from "   + tableName);  30 31  // Get column count  32 int columnCount = rs.getMetaData().getColumnCount(); 33 34  // Store rows to rowData  35   while   (rs.next()) { 36 out.println(   "<tr>"   ); 37   for   (   int   i = 0; i < columnCount; i++) {   %>   38   <td><%=    rs.getObject(i +   1   )    %></td>   39   <%   } 40 out.println(   "</tr>"   ); 41 }   %>   42   </table>   43   </body>   44   </html>

You start the application from DBLogin.html. This page prompts the user to enter a JDBC driver, URL, username, and password to log into a database. A list of accessible drivers and URLs is provided in the selection list. You must make sure that these databases are accessible from the JSP server and that their drivers are installed in c:\jakarta-tomcat-5.5.9\common\lib .

When you click the Login button, DBLoginInitialization.jsp is invoked. When this page is processed for the first time, an instance of DBBean named dBBeanId is created. The input parameters driver , url , username , and password are passed to the bean properties. The initializeJdbc method loads the driver and establishes a connection to the database. If login fails, the connection property is null . In this case, an error message is displayed. If login succeeds, control is forwarded to Table.jsp.

Table.jsp shares dBBeanId with DBLoginInitialization.jsp in the same session, so it can access connection through dBBeanId and obtain tables in the database using the database metadata. The table names are displayed in a selection box in a form. When the user selects a table name and clicks the Browse Table Content button, BrowseTable.jsp is processed.

BrowseTable.jsp shares dBBeanId with Table.jsp and DBLoginInitialization.jsp in the same session. It retrieves the table contents for the selected table from Table.jsp.

JSP Scripting Constructs Syntax

<%= Java expression %> The expression is evaluated and inserted into the page.
<% Java statement %> Java statements inserted in the jspService method.
<%! Java declaration %> Defines data fields and methods .

[Page 1238]
<% ” ” JSP comment %> The JSP comments do not appear in the resultant HTML file.
<%@ directive attribute="value" %> The JSP directives give the JSP engine information about the JSP page. For example, <%@ page import="java.util.*, java.text.*"%> imports java.util.* and java.text.* .
<jsp:useBean id="objectName" scope="scopeAttribute" class="ClassName" /> Creates a bean if new. If a bean is already created, associates the id with the bean in the same scope.
<jsp:useBean id="objectName" scope="scopeAttribute" class="ClassName" > statements </jsp:useBean> The statements are executed when the bean is created. If a bean with the same id and class name already exists, the statements are not executed.
<jsp:getProperty name="beanId" property="sample" /> Gets the property value from the bean, which is the same as <%= beanId.getSample() %> .
<jsp:setProperty name="beanId" property="sample" value="test1" /> Sets the property value for the bean, which is the same as <% beanId.setSample("test1"); %> .
<jsp:setProperty name="beanId" property="score" param="score" /> Sets the property with an input parameter.
<jsp:setProperty name="beanId" property="*" /> Associates and sets all the bean properties in beanId with the input parameters that match the property names.
<jsp:forward page="destination" /> Forwards this page to a new page.

JSP Predefined Variables

application represents the ServletContext object for storing persistent data for all clients .
config represents the ServletConfig object for the page.
out represents the character output stream, which is an instance of PrintWriter , obtained from response.getWriter() .
page is alternative to this .
request represents the client's request, which is an instance of HttpServletRequest in the servlet's service method.
response represents the client's response, which is an instance of HttpServletResponse in the servlet's service method.
session represents the HttpSession object associated with the request, obtained from request.getSession() .