Many dynamic Web applications use databases to store and manage data. Servlets can connect to any relational database via JDBC. In Chapter 33, "Advanced Java Database Programming," you learned how to create Java programs to access and manipulate relational databases via JDBC. Connecting a servlet to a database is no different from connecting a Java application or applet to a database. If you know Java servlets and JDBC, you can combine them to develop interesting and practical Web-based interactive projects.
To demonstrate connecting to a database from a servlet, let us create a servlet that processes a registration form. The client enters data in an HTML form and submits it to the server, as shown in Figure 34.15. The result of the submission is shown in Figure 34.16. The server collects the data from the form and stores it in a database.
The registration data is stored in an Address table consisting of the following fields: firstName , mi , lastName , street , city , state , zip , telephone , and email , defined in the following statement:
create table Address ( firstname varchar( 25 ), mi char( 1 ), lastname varchar( 25 ), street varchar( 40 ), city varchar( 20 ), state varchar( 2 ), zip varchar( 5 ), telephone varchar( 10 ), email varchar( 30 ) )
MySQL, Oracle, and Access were used in Chapter 32. You can use any relational database. An ODBC data source ExampleMDBDataSource was used in Chapter 32. This example assumes that the table is stored in this data source. If the servlet uses a database driver other than the JDBC-ODBC driver (e.g., the MySQL JDBC driver and the Oracle JDBC driver), you need to place the JDBC driver (e.g., mysqljdbc.jar for MySQL and classes12.jar for Oracle) into c:\jakarta-tomcat-5.5.9\common\lib .
Create an HTML file named SimpleRegistration.html in Listing 34.6 for collecting the data and sending it to the database using the post method. This file is almost identical to Listing 34.3, Student_Registration_Form.html. You should place this file under c:\jakarta-tomcat-5.5.9\webapps\liangweb .
1 <!-- SimpleRegistration.html --> 2 <html> 3 <head> 4 <title> Simple Registration without Confirmation </title> 5 </head> 6 <body> 7 Please register to your instructor's student address book. 8 9 <form method = "post" action = "/liangweb/SimpleRegistration" > 10 <p> Last Name <font color = "#FF0000" > * </font> 11 <input type = "text" name = "lastName" > 12 First Name <font color = "#FF0000" > * </font> 13 <input type = "text" name = "firstName" > 14 MI <input type = "text" name = "mi" size = "3" > 15 </p> 16 <p> Telephone 17 <input type = "text" name = "telephone" size = "20" > 18 Email 19 <input type = "text" name = "email" size = "28" > 20 </p> 21 <p> Street <input type = "text" name = "street" size = "50" > 22 </p> 23 <p> City <input type = "text" name = "city" size = "23" > 24 State 25 <select size = "1" name = "state" > 26 <option value = "GA" > Georgia-GA </option> 27 <option value = "OK" > Oklahoma-OK </option> 28 <option value = "IN" > Indiana-IN </option> 29 </select> 30 Zip <input type = "text" name = "zip" size = "9" > 31 </p> 32 <p> <input type = "submit" name = "Submit" value = "Submit" > 33 <input type = "reset" value = "Reset" > 34 </p> 35 </form> 36 <p><font color = "#FF0000" > * required fields </font></p> 37 </body> 38 </html> |
Create the servlet named SimpleRegistration in Listing 34.7 and compile it into c:\jakarta-tomcat-5.5.9\webapps\liangweb\WEB-INF\classes .
1 import javax.servlet.*; 2 import javax.servlet.http.*; 3 import java.io.*; 4 import java.sql.*; 5 6 public class SimpleRegistration extends HttpServlet { 7 // Use a prepared statement to store a student into the database 8 private PreparedStatement pstmt; 9 10 /** Initialize variables */ 11 public void init() throws ServletException { 12 initializeJdbc(); 13 } 14 15 /** Process the HTTP Post request */ 16 public void doPost(HttpServletRequest request, HttpServletResponse 17 response) throws ServletException, IOException { 18 response.setContentType( "text/html" ); 19 PrintWriter out = response.getWriter(); 20 21 // Obtain parameters from the client 22 String lastName = request.getParameter( "lastName" ) ; 23 String firstName = request.getParameter( "firstName" ); 24 String mi = request.getParameter( "mi" ); 25 String phone = request.getParameter( "telephone" ); 26 String email = request.getParameter( "email" ); 27 String address = request.getParameter( "street" ); 28 String city = request.getParameter( "city" ); 29 String state = request.getParameter( "state" ); 30 String zip = request.getParameter( "zip" ); 31 32 try { 33 if (lastName.length() == firstName.length() == ) { 34 out.println( "Last Name and First Name are required" ); 35 return ; // End the method 36 } 37 38 storeStudent(lastName, firstName, mi, phone, email, address, 39 city, state, zip); 40 41 out.println(firstName + " " + lastName + 42 " is now registered in the database" ); 43 } 44 catch (Exception ex) { 45 out.println( "Error: " + ex.getMessage()); 46 } 47 finally { 48 out.close(); // Close stream 49 } 50 } 51 52 /** Initialize database connection */ 53 private void initializeJdbc() { 54 try { 55 // Declare driver and connection string 56 String driver = "sun.jdbc.odbc.JdbcOdbcDriver" ; 57 String connectionString = "jdbc:odbc:exampleMDBDataSource" ; 58 // For MySQL 59 // String driver = "com.mysql.jdbc.Driver"; 60 // String connectionString = "jdbc:mysql://localhost/test"; 61 // For Oracle 62 // String driver = "oracle.jdbc.driver.OracleDriver"; 63 // String connectionString = "jdbc:oracle:" + 64 // "thin:scott/tiger@liang.armstrong.edu:1521:orcl"; 65 66 // Load the driver 67 Class.forName(driver); 68 69 // Connect to the sample database 70 Connection conn = DriverManager.getConnection 71 (connectionString); 72 73 // Create a Statement 74 pstmt = conn.prepareStatement( "insert into Address " + 75 "(lastName, firstName, mi, telephone, email, street, city, " 76 + "state, zip) values (?, ?, ?, ?, ?, ?, ?, ?, ?)") ; 77 } 78 catch (Exception ex) { 79 ex.printStackTrace(); 80 } 81 } 82 83 /** Store a student record to the database */ 84 private void storeStudent(String lastName, String firstName, 85 String mi, String phone, String email, String address, 86 String city, String state, String zip) throws SQLException { 87 pstmt.setString( 1 , lastName); 88 pstmt.setString( 2 , firstName); 89 pstmt.setString( 3 , mi); 90 pstmt.setString( 4 , phone); 91 pstmt.setString( 5 , email); 92 pstmt.setString( 6 , address); 93 pstmt.setString( 7 , city); 94 pstmt.setString( 8 , state); 95 pstmt.setString( 9 , zip); 96 pstmt.executeUpdate(); 97 } 98 } |
The init method (line 11) is executed once when the servlet starts. After the servlet has started, the servlet can be invoked many times as long as it is alive in the servlet container. Load the driver, and connect to the database from the servlet's init method. If a prepared statement or a callable statement is used, it should also be created in the init method. In this example, a prepared statement is desirable, because the servlet always uses the same insert statement with different values.
A servlet can connect to any relational database via JDBC. The initializeJdbc method in this example loads a JDBC-ODBC bridge driver (line 56). Use of the other driver is commented in the code (line 58 “64). Once connected, it creates a prepared statement for inserting a student record into the database. The Access database and the Oracle database are the same as were used in Chapter 32, "Java Database Programming." To use the MS Access database, the ODBC data source exampleMDBDataSource must be created. To use the Oracle database, you must have the Oracle JDBC Thin driver in the library of the project.
Last name and first name are required fields. If either of them is empty, the servlet sends an error message to the client (lines 33 “36). Otherwise, the servlet stores the data in the database using the prepared statement.