JDBC  «Prev  Next»

Writing Information to Database - Exercise

Write Servlet that uses Database

Objective: Write code to connect, read, and write with a database.

Exercise Scoring

This exercise is worth 10 points. To receive credit, paste your completed code in the text box provided below, and click the Submit button to send your code.

Background Overview

You should have already set up an ODBC DSN that points to servletsample.mdb, the Access database we ahve provided.
If not, do so now. The database has a table called LOGINS with three fields: NAME, NUMBER, and LASTLOGIN. You are going to modify your form servlet to look up the user's name in the database and generate HTML showing the number and the last login. (You will not spend any effort on formatting these nicely, just dump them out.) Then your servlet will update the last login time in the database for that user.

Download files

You need to use the following file from the Exercise download: FormHello.java (the solution to the exercise in the previous module that generates a form, and then uses form variables to greet the student).

Instructions

Copy FormHello.java to HelloDatabase.java and change the name of the class to HelloDatabase.
Follow these steps:
  1. Add the import statement you will need to access the java.sql classes easily.
  2. Add a private Connection member variable called con to the servlet class.
  3. Add an override of init() that opens the connection to the data source. Remember that init() starts like this: 
    public void init(ServletConfig config) throws ServletException {
super.init(config);

  1. Add code to init() to load the JDBC driver and to get a connection object. Remember, the DSN you have already set up is called javadeploy.com.
  2. Remove the code in doPost() that generates HTML using the name from the form. Retain the code that extracts the name from the form – you will use that name in your SQL.
  3. Add code to doPost() to create an SQL statement attached to the connection.
  4. Add code to doPost() to execute a SELECT statement that will create a ResultSet of records matching the name that was in the database.
  5. Add code to doPost() to check if any records are in the dataset. If so, extract the NUMBER and LASTLOGIN fields from the first record, and use them in your generated HTML, to produce output something like this: Hello Kate, your number is: 123.
    Your last Login was 1999-05-31 10:00:59
  6. Also, if any records were in the dataset, update the LASTLOGIN field. Use the special SQL keyword NOW, like this: 
    stmt.executeUpdate("UPDATE Logins SET LastLogin = NOW WHERE NAME = '" + name + "'");
  7. Add code to doPost() to close your result set and your statement.
  8. Compile your servlet. Start the servlet runner. Copy the HelloDatabase.class file to the directory where servlet runner looks for servlets. Open a browser, and enter this URL: 
    http://localhost:8080/servlet/HelloDatabase
    You should see the form. Enter Kate and submit the form. You should get a response indicating your number is 123.
  9. Go back to doPost() and add code to emit different HTML when there are no records in the result set. For example, you might generate: Hello Mara, your number is not in the database.
Compile your servlet and test it again. This time enter a name other than Kate or Bill Gates. You should see the message you just added. Now use the Back button to return to the form and enter Kate again. You should see that your “last login” matches the time you tested the form in step 10.

Hints

In the last module you used the parameter value from the form directly in your HTML. In this exercise you will use it to build your SQL statement. You will use the values that rs.getString() returns in your HTML. If you are not sure what is going on, add System.out.println() calls anywhere you like. The output will appear in the MS-DOS prompt from which you started servlet runner. If you change code in the init() method, you must stop the servlet runner and start it again, otherwise init() will not be called the next time the servlet is loaded. Changes to doPost() do not require you to restart the servlet runner. Place your code into the text area below once it's all working smoothly.

Further exploration

If you have Access on your computer, or another program that can use Access files, you can examine the database directly to see what’s going on.