|
In this last lab assignment, you will be asked to write simple command line Java programs to query Postgresql DB according to the specified parameters. These programs are kept very simple--in real world, you will find more complicated code in Java Applet, Java Servlet, Java Beans, etc. And you will find more advanced usages like stored procedure, transaction, connection pool, etc. But the basic idea remains the same: to get required data from the DB, correctly and efficiently.
1. Preparation:
- DB setup: we are using the same DB schema and data from lab2, so if your DB is different, please run those three script files from lab2.
- Java platform setup: J2SE 1.4.2_01 has already been installed on db.ic.ucsc.edu and you can directly type javac, java and other SDK commands from the console. Type java to make sure your SDK works. For security reasons, the Postgresql server is configured to accept local connections from db.ic.ucsc.edu, so please log on db.ic.ucsc.edu and do your Java development.
- CLASSPATH setup: Postgresql driver is located at /home/share/postgresql/java/postgresql.jar. To make your life easier, you should add Postgresql driver to your classpath. You also need to add current directory (.) for your Java program to the classpath.
For csh, sh, add this line to your .cshrc file: setenv CLASSPATH /home/share/postgresql/java/postgresql.jar:.
For bash, add this line to your .bash_profile: export CLASSPATH=/home/share/postgresql/java/postgresql.jar:.
- We added some new data to the DB for this lab, e.g. we made sure each bar in Bar table serves some beer in Serves table, each drinker in Drinker table likes some beer, and frequents some bar. so please follow step1 to step3 to set up the DB for evaluation. Use \i scriptfile.script in psql to run the script files. In case you need to remove the tuples from the five tables, use del_tables_content.script.
2. Write four Java programs, one for each of the following questions.
- These Java programs should interactively take input parameters, e.g. BarName, DrinkerName, for composing query conditions. You need to validate the input by checking its data type and value before you pass it to the SQL query. DB query is a heavy-weight service, so validate input at the client side will improve the performance. Generally speaking, it is also a good habit always to validate your input for any code. If the validation fails, you should let the user input a new parameter to try again (hint: use some form of loop structure).
- Properly handle empty query results and error messages. You need to prepare informative messages for empty query results, e.g. "cannot find drinkers younger than 21", "cannot find bars that sell
Holsten Premium beer". You also need to show exceptions and stacktrace if errors happen.
- Following Java coding convention and adding comments for Javadoc. It is good practice to follow Java coding conventions for portability and readability. Learn those conventions in Code Conventions for Java Programming Language. To ease the work of documentation, Javadoc Tool helps to extract code comments and format them into HTML documents. Read How to Write Doc Comments for the Javadoc Tool for details. Please add comments at appropriate places in your code to help TA understand your code. Optionally, you can play with Javadoc to create HTML documentation.
- A sample program (CheckAgeLimit.java) is provided to illustrate above requirements. This sample program returns drinker names and occupations given a user-specified age limit (integer). Note how it handles inappropriate data values and data types and how it deals with exceptions. For code comments, you can follow those examples if necessary. Replace username in some part of the code, e.g. line 18, 21, 24 with your CATS username.
Question 1. Find the average age of drinkers who frequent the bar with barid=BarName.
The user will be prompted to enter the BarName and the program will return the average age for the drinkers who frequent BarName. Please add appropriate messages as output to the user. Note that each drinker is only counted once even though he or she can frequent the bar multiple times.
Question 2 Find the minimum and maximum salary of drinkers who like drinking beers on day x.
The user will input an integer (1-7) and the program returns the max and min salary for the drinkers who like drinking beers on that day. Please use the Likes table for the query.
Quersion 3 Find the bar names whose number of customers exceed n.
The user will input an integer as a lower bound n and the program returns the bar names that have more customers than n. Note that the customers mean the people who frequent the bar, and each person is counted only once. The number of customers does NOT mean the capacity of the bar.
Question 4 Find the bars that serves all beers that drinker d likes.
The user will input a drinker name, and the program first returns the list of beers that he/she likes, then the list of bar names that serve ALL those beers.
Submission Summary (please read carefully about items to submit)
| Items to submit |
Description |
AvgDrinker.java, AvgDrinker.class, and AvgDrinker.log
|
AvgDrinker.java and AvgDrinker.class are the source and class files.
AvgDrinker.log records the session when you execute the program. Please try three drinker names: Hurry Back, Watering Hole, and 99 bottles and record the session. |
| MinMaxSalary.java, MinMaxSalary.class, and MinMaxSalary.log |
MinMaxSalary.java and MinMaxSalary.class are the source and class files.
MinMaxSalary.log is the session log. Please try five days: - 2, 1, 4, 5, and 20 and record the session. |
| BarLowerBound.java, BarLowerBound.class, and BarLowerBound.log |
BarLowerBound.java nad BarLowerBound.class are the source and class files.
BarLowerBound.log is the session log. Please try seven numbers: - 1, 0, 1, 3, 4, 5 and record the session. |
| FavoriteBar.java, FavoriteBar.class, and FavoriteBar.log |
FavoriteBar.java and FavoriteBar.class are the source and class files.
FavoriteBar.log is the session log. Please try five inputs: Mike, Bill, Jack, John, and Indiana Jones and record your session. |
You should use submit program to submit the assignments. If you dont know how to use submit, please read "how to use submit" for details. Sample submit commands for this lab assignment:
submit cmps180-wt.s04 lab4 FavoriateBar.java //to submit FavoriateBar.java
submit cmps180-wt.s04 lab4 FavoriateBar.java FavoriateBar.class and FavoriateBar.log //to submit three files together
peek cmps180-wt.s04 lab4 FavoriteBar.java //to examine the Favorite.java file you submitted
submit cmps180-wt.s04 lab4-late FavoriateBar.java //late submissions go to lab3-late directory
-
|
