Lab 7: SQL and JDBC

PostgreSQL

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

Using PostgreSQL on fred

1. In a terminal, ssh into fred, using the command ssh -XY fred
2. Run the PostgreSQL client and connect to the cs335 database: psql cs335

   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

3. List all the tables in the database using the command \dt
4. Write and execute the SQL statement to select all the columns and rows from the Users table
5. Write and execute the SQL statement to select the id from the Users table where the username='username' and the password=md5('passw0rd').
6. Modify the previous command so that the password is 'password' and execute the command. How are the results different from the previous query?

Alternatively, you can try using pgadmin3 to look at the databse.

JDBC (45 pts)

For the rest of the lab, you don't need to be logged into fred. However, you may find it useful to try out your SQL statements on the command-line before putting them into JDBC code.

Getting the PosgreSQL JDBC Driver

Copy postgresql.jar from /csdept/courses/cs335/shared into your cs335 directory.

API for java.sql

JDBC Practice

Create a new Dynamic Web Project for this lab. Create a new Java class (not a servlet) called JDBCPractice that has a main method. What is an appropropriate package for this class?

1. Copy postgresql.jar into your WEB-INF/lib directory. Refresh to see the jar file.
2. Follow the instructions from the lecture about how you connect to a database using JDBC and apply those in the main method. What packages do you need? What steps do you need to take? You may want to look at the examples. You may also want to look at the JavaDocs.

   Run this class as a Java application.

   Note: you won't be able to do this from a non-cs.wlu.edu computer. The DB isn't set up to handle connections from other users.

3. Execute the statement that finds out the names of the groups that each user belongs to. Iterate through the ResultSet and display the results in a nice way. (You may get 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 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. (id is automatically filled in.)
5. Create and execute PreparedStatement that updates a user's group membership to include your newly created group. (A user of your choice.)
6. Create and execute 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.)

LoginServlet (55 pts)

1. Copy your LoginServlet from Lab5 into an appropriate package in this lab/project. (You may need to update web.xml, depending on how you do this step.)
   Also, copy your JSPs from Lab5 into the appropriate directory in this project.
2. 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 old error messages/attributes that check if the username or password wasn't entered.)

   Put the JDBC setup (e.g., DriverManager, Connection) in the init method. Review our process from Lab 4 if needed. You'll need Connection to be an instance variable of your servlet so that you can use it in the doPost method.

   Close the connection in the servlet's destroy method.

3. Get the user's "real" name from the database and save it as an attribute in the HttpSession object because you'll want to use the user's real name for more than just this request.
4. Modify the congratulations JSP so that it says "Welcome Back, <name>!" with the name (that was stored in the HttpSession) plugged in.
5. 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

6. Test your code several times.

Turnin

Export the project as a .war file and save in your turnin directory.

Grading (100 pts)

This lab is due tonight at midnight.

• JDBC Practice (45 pts)
• LoginServlet and Congratulations.jsp (55 pts)