The Sixth Lab

JDBC for PostgreSQL

Octy 9, 2015

(I) Download JDBC Driver

  •         1) Download JDBC Driver for PostgreSQL here
                    Choose 9.2-1004 JDBC 4
  •         2) Upload the JDBC Driver to cs460 server.
                    You'd better create a new folder for JDBC Driver and code in this Lab.
                    Directly copy the jar file to the folder you created.


(II) Connect to PostgreSQL Server

  •         1) Log on to cs460 server
                    Go to the folder including JDBC Driver
  •         2) Download the sample code
                    Copy the java file to the folder including JDBC Driver.
  •         3) Modify the sample code.
                    Change [username in your email] to your real username. Use " to quote your username.
                    Change [password in your email] to your real password. Use " to quote your password.
  •         4) Compile the code.
                    Use javac ConnectPostgreSQL.java to compile the java code.
  •         5) Run the code.
                    Use java -cp .:postgresql-9.2-1004.jdbc4.jar ConnectPostgreSQL to run the code.
                    If you are using a different JDBC Driver, change the file name of the jar file.
                    The code successfully connects to PostgreSQL Sever if the console displays as follow:
                            Opened database successfully
                            ...
                            Closed database successfully


(III) Query by using JDBC

  •         1) Log on to cs460 server
                    Go to the folder including JDBC Driver
  •         2) Download the sample code
                    Copy the java file to the folder including JDBC Driver.
  •         3) Modify the sample code.
                    Change [username in your email] to your real username. Use " to quote your username.
                    Change [password in your email] to your real password. Use " to quote your password.
  •         4) Compile the code.
                    Use javac QueryPostgreSQL.java to compile the java code.
  •         5) Run the code.
                    Use java -cp .:postgresql-9.2-1004.jdbc4.jar QueryPostgreSQL to run the code.
  •         6) Examine the results.
                    Now, you should be able to see the query result to the first question in Lab 5.
  •         6) Compare two sample code files.
                    Learn how to execute the queries by using JDBC.


(IV) Learn java.sql Package

  •         1) Use DriverManager to connect a database.
                    The syntax is DriverManager.getConnection(jdbc:postgresql:database, username, password);
  •         2) Use Statement to manage query statements.
  •         3) Use ResultSet to manage query results.
                    A ResultSet object maintains a cursor pointing to its current row of data.
                    Use next() if you want to get the next row of data.
                    Use getString(int columnIndex) to retrieve the value of the designated column in the current row. (In String Format)
  •         4) Use ResultSetMetaData to get metadata of query results.
                    The metadata includes information like number of columns in the ResultSet.
  •         5) Close the connection of ResultSet, Statement and Connection. Otherwise, you will have RUNTIME ERRORS in your programming assignments.


(V) Exercise

  •         Write a jave file to execute the query statements in Lab 5.