Using JDBC (Java Database Connectivity)

JDBC References

Environment Setting

JDBC Programming

Milestone 2

JDBC References

• JDBC API Documentation fo J2SE 1.4.2

  You may begin by reading the basic tutorial, and refer to the topics with more details and API specification later.

• PostgreSQL JDBC Documentation

  Chapter 2, 3, 5 are relevant to this milestone.

Environment Setting

With the default installation of Pervasive Postgres, the PostgreSQL JDBC driver can be found under the directory 'C:\Program Files\Pervasive Postgres\jdbc'. If you cannot find it, download it at http://jdbc.postgresql.org/download/postgresql-8.0-310.jdbc3.jar.

Add the JDBC driver to your Java classpath. In Windows, you can set the CLASSPATH user environment variable (Control Panel->System->Advanced->Environment Variables) or save the following in a bat file which is executed before running your JDBC programs

SET CLASSPATH=%CLASSPATH%;C:\Program Files\Pervasive Postgres\jdbc\postgresql-8.0-310.jdbc3.jar

JDBC Programming

The following are the major steps in using JDBC in your programs.

Importing JDBC

import the java.sql package, using

import java.sql.*;

Loading the driver

The most common method to use is the following statement

Class.forName("org.postgresql.Driver");

This may throw a ClassNotFound exception.

Connecting to the server

Connect to the PostgreSQL database server using

Connection con = DriverManager.getConnection("jdbc:postgresql://hostname/databasename", username, password);

This step and the following three may throw a SQLException.

Creating a statement

Creating a normal statement using

Statement stmt = con.createStatement();

or a prepared statement using

PreparedStatement pstmt = con.prepareStatement(statement-template);

Issuing queries or updates

With a normal statement, using

ResultSet rs = stmt.executeQuery(SQL-SELECT-statement);

or

stmt.executeUpdate(SQL-INSERT-or-UPDATE-or-DELETE-statement);

Retrieving results

Retrieve the rows in the result using

while (rs.next()) {
    String s1 = rs.getString(column-name-or-index1);
    float n = rs.getFloat(column-name-or-index2);
    ...
}

MileStone 2

There are two programs for milestone 2. The first program uses JDBC to do bulking loading of proteins. The second program uses JDBC to retrieve proteins, compute matches and save the results into the database.

The first program is provided to get your familiar with JDBC programming. You are expected to do the second one by yourselves.

Program 1

Postgres.java, ProteinLoader.java

Data: bacil-gb.txt, ecoli-gb.txt, ecoli-sp.txt, human-gb.txt, yeast-gb.txt

To import these data, you first need to delete existing records from the database.

Program 2

You will

• Use JDBC to retrieve proteins from the database.

• Use SmithWatermann algorithm to compute a HSP (with or without gaps) for each pair of proteins. To make your programming easier, only store one HSP with the maximum score for each pair. Using JDBC, first save the HSPs into the match table and then into the HSP table. The contents of the two tables are basically the same in this case. Note: you should use the Blosum62 matrix to compute similarity scores.