JDBC

I l @ ve RuBoard

PostgreSQL provides a type 4 JDBC driver. This indicates that the driver is written in Pure Java and is platform independent. Therefore, once compiled, the driver can be used on any system.

Compiling the Driver

To build the driver at compile time of the system, include the --with-java option of the configure command. Otherwise, if the system is already installed, it can still be compiled by entering the /src/interfaces/jdbc directory and issuing the make install command.

If you have installed on a packaged-based system, there is an appropriate RPM or DEB package for JDBC installation (for example, postgresql-jdbc-7.1.2-4PGDG.i386.rpm). Once installed, it usually resides in /usr/share/pgsql .

Upon completion, the JDBC driver will be in the current directory, named postgresql.jar .

Installing the Driver

To use the driver, the jar archive postgresql.jar needs to be included in the environment variable CLASSPATH . For example, to load the driver with the fictional Java application foo.jar , you would issue (this assumes using the Bash shell) the following:

 $ CLASSPATH=/usr/local/pgsql/lib/postgresql.jar  $ export CLASSPATH  $ java ./foo.jar 

Configuring Clients

Any Java source that uses JDBC needs to import the java.sql package using the following command:

 import java.sql.*; 

Don't Import the postgresql Package

Do not import the postgresql package. If you do, your source will not compile.

Connecting

To connect, you need to get a Connection instance from JDBC.To do this, you would use the DriverManager.getConnection() method:

 Connection db = DriverManager.getConnection(  url,user,pwd  ); 

Options	Description
url	Database URL.
user	Username to connect as.
pwd	Password of the user.

With JDBC, a database is represented by a uniform resource locator (URL). With PostgreSQL, this takes one of the following forms:

 jdbc:postgresql:  database  jdbc:postgresql:  //host/database  jdbc:postgresql:  //hostport/database  

Options	Description
database	Database name .
host	Hostname of the server (default is localhost).
port	Port number of the server (default is 5432).

Executing Queries

To submit a query to the database, a Statement object is necessary. The executeQuery() method will return a ResultSet instance that will contain the returned result. For instance:

 Statement myst = db.createStatement();  ResultSet myrs = myst.executeQuery("SELECT * FROM payroll");  [more code] 

Before the actual ResultSet can be accessed, an rs. next () function must be called. This function returns a TRUE value if there are more results present and prepares the tuple for processing.

Updating Records

To update a specific element or to execute any statement that does not result in a ResultSet , use the executeUpdate() method. For instance:

 Statement myst = db.createStatement();  Myst.executeUpdate("UPDATE payroll SET first_name='Steve'"); 

I l @ ve RuBoard