18.5. Using EDB Connector/Java with Java applications

Now that we have all our requirements in place, we can start writing Java code to access an EDB database using EDB Connector/Java.

18.5.1. HelloWorld Test Program

Lets start off with a traditional Hello World application. The code in the following listing creates a Java application that pulls data from a EDB database.


import java.sql.*;

public class HelloWorld 
{
  public static void main(String[] args)
  {
    try
    {
	  Class.forName("com.edb.Driver");
	  Connection con = DriverManager.getConnection("jdbc:edb://localhost:5444/edb","enterprisedb","enterprisedb");	
	  Statement statement = con.createStatement();
	  ResultSet rs = statement.executeQuery("SELECT * FROM emp");

	  while(rs.next()) {
             System.out.println(rs.getString(1));
	  }	   

	  rs.close();
	  statement.close();
	  con.close();
	  System.out.println("Command successfully executed");
     }

     catch(ClassNotFoundException e)
     {
	   System.out.println("Class Not Found : " + e.getMessage()); 
     }
	
     catch(SQLException exp) {
	System.out.println("SQL Exception: " + exp.getMessage());
	System.out.println("SQL State: " + exp.getSQLState());
	System.out.println("Vendor Error: " + exp.getErrorCode());    
     }	
  }
}
	      

18.5.2. Loading EDB Connector/Java

The process of loading and instantiating the EDB Connector/Java begins with the

Class.forName

method. The forName method of the Class object is designed for dynamically loading Java classes at runtime. Due to this statement the JVM (Java Virtual Machine) attempts to find the class passed to this method as a parameter using the current system path as well as any additional paths defined when the JVM is executed. In our case this is the Driver class in the

com.edb

package. We explained the placement of the EDB Connector/Java jar file in the system CLASSPATH in Section 18.4.2 so that this class can be found by the JVM. Once the file is found, the Driver will register itself with a static class called DriverManager, which is responsible for managing all the JDBC drivers installed on the current system.

In case the JVM is unable to locate the driver, it outputs a message to the console and exits the application. The DriverManager is designed to handle multiple JDBC driver objects as long as they register with a class, meaning that you can write a Java application that connects with more than one database system via JDBC. Please keep in mind that no connection is made with the database at this time and point, and that the driver is simply loaded.

18.5.3. Connecting to the Database

Once the driver has been loaded and registered with the JVM, we attempt to build a connection to the database. The code responsible for this action is:

Connection con = DriverManager.getConnection ("jdbc:edb://localhost:5444/edb","enterprisedb","enterprisedb");	

The DriverManager class has a static method called getConnection which is responsible for creating a connection to the database. Once the getConnection method is called, the DriverManager needs to decide which JDBC driver to use for connecting to the database.

This decision is taken based on the parameter passed to the getConnection method which expects a connection string in the form of a URL (Uniform Resource Locator) in place of the first parameter. A URL is used because it provides a common way of locating resources found on the Internet. A URL takes the following general format:

<protocol>:<subprotocol>:<subname>

In the world of JDBC, the protocol is defined as jdbc, and the subprotocol is typically the name of the driver this particular connecting URL needs to use. In the case of EnterpriseDB, this subprotocol is defined as edb.

Hence our URL takes one of the following forms:

Table 18-1 depicts the various connection parameters:

Table 18-1. Connection Parameters

NameDescription
host The host name of the server. Defaults to localhost.
port The port number the server is listening on. Defaults to the EnterpriseDB standard port number (5444).
database The database name.

To connect, you need to get a Connection instance from JDBC. To do this, you use the

DriverManager.getConnection()

method as follows:

Connection db = DriverManager.getConnection(url, username, password);

18.5.4. Additional Connection Properties

In addition to the standard connection parameters the driver supports a number of additional properties which can be used to specify additional driver behavior specific to EnterpriseDB. These properties may be specified in either the connection URL or an additional Properties object parameter to:

DriverManager.getConnection()

The following examples illustrate the use of both methods to establish a SSL connection.

String url = "jdbc:edb://localhost:5444/edb";
Properties props = new Properties();
props.setProperty("user","enterprisedb");
props.setProperty("password","enterprisedb");
props.setProperty("ssl","true");
Connection conn = DriverManager.getConnection(url, props);

Table 18-2 depicts the various connection parameters:

Table 18-2. Additional Connection Properties

NameData TypeDescription
userStringThe database user on whose behalf the connection is being made.
passwordStringThe database user's password.
loglevelint Set the amount of logging information printed to the DriverManager's current value for LogStream or LogWriter. It currently supports values of

  1. com.edb.Driver.DEBUG

  2. com.edb.Driver.INFO

INFO will log very little information while DEBUG will produce significant detail. This property is only really useful if you are a developer or are having problems with the driver.
charSetStringThe character set to use for data sent to the database or recieved from the database.
prepareThresholdintDetermine the number of PreparedStatement executions required before switching over to use server side prepared statements. The default is zero meaning never use server side prepared statements.

18.5.5. Handling Errors

When making connections with external resources, errors are bound to occur, so you code should make take such errors into account. Both JDBC and EDB Connector/Java provide various types of errors. Our sample program demonstrates the use of this error handling via the try/catch blocks which are there to capture a SQLException when it occurs.

JDBC drivers implement three different pieces of error information. These are:

  1. The exception itself.

  2. The SQL State.

  3. A vendor code.

The following piece of code outputs the values of these three components should an error occur when trying to accomplish some JDBC task.

System.out.println("SQL Exception: " + exp.getMessage());
System.out.println("SQL State: " + exp.getSQLState());
System.out.println("Vendor Error: " + exp.getErrorCode());   

Suppose we try connecting to a database that does not exist on the specified host, then the following error would be displayed on the console:

SQL Exception: Backend start-up failed: FATAL: database "dummy" does not exist 
Location: File: postinit.c, Routine: InitPostgres, Line: 272
Server SQLState: 3D000.

SQL State: 3D000

Vendor Error: 0

In a production system we would probably want to log the error to a log file so that we can easily trace the cause of an error should it occur.