Laboratory Exercise 7

Using SQL with Java

CMP 420: Database Systems
Lehman College, City University of New York
27 March 2003

To embed SQL commands inside a Java program, we're using the JDBC interface. You can find the API's for JDBC at Sun's webpage. To use the JDBC interface, you first have to make sure that your java programs can find the java archive that contains the file (that is, set the path to find the .jar file). Then, you need to implicitly load the driver, before issuing SQL commands.

Setting up your `CLASSPATH`

First, locate the java archive file for JDBC, by typing:

    locate jdbc | grep jar

What's the full name of the .jar file? What is the complete path to the file?

Next, we need to include the .jar in the CLASSPATH, or places to look for the libraries or archives used by the program. To make sure that the CLASSPATH is set whenever you log on, you can set it in the .bash_profile, which is executed whenever you open a new terminal window. To do this, edit the .bash_profile and add the lines:

    CLASSPATH=/usr/lib/pgsql/jdbc6.5-1.2.jar:.
    export CLASSPATH

The `.' at the end of the CLASSPATH says to also look in the current directory for classes. The export statement means that the environment variable will continue to exist after the file has finished executing.

To immediately put the changes in to effect that you entered in your .bash_profile, type at the command line:

    source .bash_profile

To check that it was successful, print the environment variables, searching (or "grepping") for the ones that include the word PATH:

    printenv | grep PATH

You should see two different path variables-- one for locating executable programs and one for loading classes into java.

Loading the `driver` class

The file TestDB.java is a simple java program that includes the import statement and java command for loading the necessary driver. Compile it, and make sure it runs (if it does, it will say "Test successful").

Whenever you use SQL statements in your java program, you will have to include these two commands. Note that the forName method could throw a ClassNotFoundException and must be caught and handled.

Connecting to the Database

Before you can issue query and update commands, you first must say which database you would like to use. This is equivalent to giving the name of the database when you launch psql (for example, psql testDB). To connect to a database, you need to specify the name of the database (prefaced by the interface names: "jdbc:postgresql:"), your username, and the password (since we have not yet used password protection on the databases, we use the word "pass" for this argument). For example, to connect to the database myDB, as user testPerson, with no password, you would have the following command:

    Connection db = DriverManager.getConnection("jdbc:postgresql:myDB", 
                                                "testPerson", "pass");

Note that this method, as well as the ones in the next section, can throw SQLExceptions that must be caught and handled.

Modify the java program from the previous section to connect to the database you created for Lab 2.

Query Statements in Java

To query the database, you first need a Statement object (you can reuse it for later statements) and an object of type ResultSet to hold the results of your query. For example, the following would print out the company names from your database:

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("select co_name from companies");
    System.out.println("Company Name");
    while(rs.next()) {
       System.out.println(rs.getString(1));
    }
    rs.close();
    st.close();

These statements could throw an SQLException that must be caught and handled.

Modify your java program to print out the company names from the database.

Update Statements in Java

To update the database (or execute any statement that doesn't return results), you use the executeUpdate() method of the Statement class. For example, if you wanted to create a new table, t with two attributes, one an integer and one for time, you use the following statement:

    st.executeUpdate("create table t(id int4, date timestamp);");

For example, to insert new values into the table, you could use the following statements:

   st.executeUpdate("insert into t values(3, now());");
   st.executeUpdate("insert into t values(2, now());");

These statements could throw an SQLException that must be caught and handled.

Java Programs for this Lab

At the command line, create a database for this lab.

Write a java program that creates 2 tables:

     Employees(ID, Name, Address, StartYear, StopYear, Salary)
     Colleges(SchoolName, StudentName)

and adds in several employees and keeps track of the schools they attended. Type these by hand. In a future lab, we will load in employees automatically, using a Java progra.

Write another program that prints out the number of years each employee was employed (that is, the difference between StartYear and StopYear).

Final Comments

Before leaving the lab, you need to log out and shut down your computer. First, you should quit all applications. Then, go to the K menu in the bottom left hand corner and select Log Out. Choose the Shut Down option, and shut off the monitor when you're done.

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.