24.5. Using EnterpriseDB JDBC Connector with Java applications

24.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 EnterpriseDB 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());    
     }	
  }
}
	      

24.5.2. Loading EnterpriseDB JDBC Connector

The process of loading and instantiating the EnterpriseDB JDBC Connector 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 EnterpriseDB JDBC Connector jar file in the system CLASSPATH in Section 24.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.

24.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:

• jdbc:edb:// (database) 

• jdbc:edb:// (host) / (database) 

• jdbc:edb:// (host) : (port) / (database) 

Table 24-1 depicts the various connection parameters:

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);

24.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 24-2 depicts the various connection parameters:

24.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 EnterpriseDB JDBC Connector 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.