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.
0. 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:.
1. A Sample Program (Sample.java) is provided here to give you some flavor of JDBC programming. The code select every tuple in the Bar table. You can try it out. Please make sure you properly change line 19.
conn = DriverManager.getConnection("jdbc:postgresql://db.ic.ucsc.edu/username", "username", "username1234" ); //Making the Connection
jdbc:postgresql://db.ic.ucsc.edu/username is the URL for your DB, replace the username with your CATS username, e.g. gaurav.
username is the DB username, replace it with your CATS username, e.g. gaurav.
username1234 is the password for your DB, replace it with your CATS username+1234, e.g. gaurav1234.
Use javac to compile the file:
%db> javac Sample.java
Use java to run the comipled class file:
%db> java Sample
OR (if you did not set CLASSPATH to include postgresql.jar)
%db> java -classpath /home/share/postgresql/java/postgresql.jar:. Sample
Previous TA Gaurav Vijayvargiya has written a short tutorial about this Sample file with explanation of possible errors, you can find it here.
2. Write four Java programs, one for each of the following questions.
Question 1. Table Creation. Create a new Beer table in the DB. The schema is described as follows:
Beer(Brand, Supplier_Price, Supplier_ID)
Brand: VARCHAR(20) PRIMARY KEY
Supplier_Price: NUMERIC(3,2)
Supplier_ID: INTEGER
Question 2 Query Execution. Find names, age and occupations for drinkers whose age is below 21. One possible output of your query results can be like this:
Name: John | Age: 18 | Occupation: Student
Name: Jane | Age: 20 | Occupation: Student
Quersion 3 Tuple Insertion. Insert a new tuple into Drinker table with the value ('Mary', 25, 'Graphics Designer', 75080.53).
Question 4 Tuple Update. Update Jack's salary because he got a raise of 10000, so his new salary is 96098.15 now.
3.Optional assignment (not for submission). For those who have extra time, you can try to play with EMS Postgresql Manager, a GUI client for Postgresql. Most of the commercial DBMS provides similar GUI-based management tools, so you might find it useful to get some flavor of such tools. Here are the basic instructions:
1) Download and install EMS Postgresql Manager from its download site. Both Win32 and Linux 30-day trial versions are available. I tried Professional for Windows(full installation package) v2.4.0.1, just download, unzip and run the setup file. Linux version should be similar.
2) Register your DB. Open EMS Postgresql Manager and select Database->Register Database... from menu or press shift+alt+R. Because db.ic.ucsc.edu requires ssh connection, we need to configure SSH tunneling. Figure 1 shows how to configure SSH tunneling on SSH tab. Figure 2 shows how to configure the rest parts on General tab. When you are done, you can press the "Test Connect" button and see if the connection is successfully set up.
3) When you finish the basic configuration, you can connect to the DB and start viewing and managing the DB. Refer to the manual and help file on specific topics.
