Skip to main content.

Lab 8: SQL and JDBC

Goals

After completing this lab, you should be able to

--- JDBC Lab FAQ ---

Objective: Set Up

ssh into a lab machine and then run labhelp so that I can keep track of the help queue.

Objective: Learning and Using PostgreSQL

For these exercises and for development, we'll use the PostgreSQL database management system (DBMS). For more information about using PostgreSQL, see the PostgreSQL page on the Wiki.

Using PostgreSQL at W&L

  1. See the instructions for connecting to our course database.

    Here is a dump from the (original) cs335 database so that you can see the definitions of the tables and initial data values. Focus on the CREATE TABLE statements.

    Alternatively, you can use the \d tablename command to view a table's columns and types,
    e.g., \d Users

  2. List all the tables in the database using the command \dt
  3. Write and execute the SQL statement to select all the columns and rows from the Users table

    Later, as the results get longer, use the spacebar to page through results and use q to exit out of the pages.

  4. Write and execute the SQL statement to select the id from the Users table where the username='username' and the password=md5('passw0rd').
  5. Modify the previous command so that the password is 'password' and execute the command. How are the results different from the previous query?
  6. Use \q to quit out of PostgreSQL

Objective: Set Up for JDBC

Getting the PostgreSQL JDBC Driver

Download the PostgreSQL JDBC Driver from PostgreSQL. You should get the current version, for Java 8 or newer.

Eclipse Set Up

Objective: Learning JDBC (45 pts)

For the rest of the lab, you should try out your SQL statements on the command-line before putting them into JDBC code.

API for java.sql

If you find yourself thinking "I'll just fix this query in Java code," STOP! Let the database do the work. It likely can do what you want faster.

  1. Create a new Java class (not a servlet) called JDBCPractice that has a main method. What is an appropriate package for this class?
  2. Follow the instructions from class about how you connect to a database using JDBC and apply those in the main method. You'll need to update the url, username, password, etc., based on the instructions linked above. What classes/packages do you need? What steps do you need to take?

    You may want to look at the example. You may also want to look at the Javadocs.

    Run this class as a Java application.

  3. In the command-line, execute the statement that finds out the names of the groups that each user belongs to. (As in, for each user, we want to see all the groups that user is in.) Then, update your Java code to execute that statement. Iterate through the ResultSet and display the results in a nice way. (Be careful with this query. Even if you do the query right, you may get duplicates. But, if you do the query wrong, you'll get even more--and incorrect--duplicates. I used to have the users2groups table set up such that the userid-groupid were unique, but then students would get errors if they ran the program multiple times and get confused, so I took that out. So, now the confusion is pushed to here.)

    How can you test that your query is correct?

    How can you handle if there are duplicate groups?

    Test your program.

  4. Create and execute a PreparedStatement that inserts a new group into the Groups table, providing the group's name and description as parameters. (The database is set up so that the id is automatically filled in.)
  5. Create and execute a PreparedStatement that updates a user's group membership so that they are now also a member of your newly created group. (A user of your choice.) Note that you'll need to look up (programmatically) the newly created group's id.
  6. Create and execute a PreparedStatement that finds out the names of the groups that a user belongs to, providing the username as a parameter. Display the results of the query.
  7. Don't forget to close the DB connection when you're done with it.
  8. Run and test your program. (During this process, you'll add rows to the table that the rest of the class can also see.)

Objective: Using JDBC in a Servlet (55 pts)

  1. Modify your LoginServlet or web.xml so that it has the following init parameters for the database connection:
    • db_hostname
    • db_database
    • db_username
    • db_password
  2. Modify your LoginServlet so that it does the JDBC setup (e.g., DriverManager, Connection) in the init method. (Why is this the appropriate place for this code?) Review our process from Lab 5 if needed. You'll need Connection to be an instance variable of your servlet so that you can use it in the doPost method. Use the init parameters in your setup.

  3. Close the connection in the servlet's destroy method.
  4. Modify your LoginServlet so that it checks if the username and password match an entry in the database. Note the password was given when you were writing your practice queries above. (Keep your original error messages/attributes that check if the username or password wasn't entered.)
  5. After that is working, make your PreparedStatement an instance variable and instantiate it in the init method. (Why should it be a PreparedStatement?) In the class's Javadoc comments, discuss why the instantiation of the prepared statement should be in the init method.
  6. Get the user's "real" name from the database and save it as a session attribute because you'll want to use the user's real name for more than just this request. (Stick with the best design principles.)
  7. Modify the congratulations JSP so that it says "Welcome Back, <name>!" with the name (that was stored in the session) plugged in.
  8. If you haven't already, copy the AuthenticatedTest servlet and the authenticated test HTML page and JSPs and add them to this project.
  9. Modify authenticated.jsp so that it displays the user's name (from the session attribute you set up previously) in the welcome.
  10. Test that everything works, i.e., after a successful login, if the user does the authenticated test, they will see the promised land page, with their name in the welcome. This showed how session state can be maintained across multiple requests and displayed in the JSPs.

    Now, back to databases...

  11. Modify the LoginServlet so that it creates an ArrayList of Strings of the logged-in user's groups (sorted by name, ascending) and passes the ArrayList as an attribute (whose attribute is most fitting for this case?) to the congratulations JSP to display.

    Practice your SQL statement in the terminal before attempting to put it into Java code. Label the groups and display them as an unordered list.

    In the end, the response may look something like:

    Welcome Back, Test McUser

    Your Group Membership:

    • Coin Collecting
    • Gardening

  12. Test your code several times.
  13. Finally, test that everything works, end to end/start to finish; correct and incorrect passwords; group names, authenticated test.

Submission

Export the project as a .war file, including the source code, and save in your GitHub repository (still from Lab 4).

Grading (100 pts)

This lab is due tonight at 11:59 p.m.