Database access makes up a major portion of Java applications development because at some point applications will have to interact with some other kind of database system.

With this interactivity so unavoidable, you need to have an understanding of Java DataBase Connectivity (JDBC). JDBC functions in much the same way that Open DataBase Connectivity (ODBC) does—it provides a way for applications to interact with multiple databases. Here are some tips on how to use JDBC to interact with almost any database.

Get the right driver
The first thing you need is the correct JDBC driver for the database you will connect to. If you need a driver that you don’t have, try the database vendor’s site, as vendors often post drivers for their systems as freely downloadable files.

If you can’t find the driver specific to your database, Sun provides a JDBC-to-ODBC bridge driver that might work just fine. Because you need at least the JDK 1.1 in order to use JDBC, you’ll already have the JDBC-to-ODBC bridge installed. You’ll create a datasource within ODBC for a database, and the JDBC driver will then use that ODBC-defined datasource to interact with the database.

Using code to connect
Now let’s look at some code that enables JDBC to connect to a database. We’ll break the code down into connection, statement execution, and results retrieval activities.

Let’s start with the connection. Consider the following code snippet:
try
{
 Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”).newInstance();
}
catch (ClassNotFoundException cnfe)
{
 System.err.println (“Error Loading Driver : ” + cnfe);
 System.exit(0);
}

Since we’ve already discussed the JDBC-to-ODBC bridge, we’ll use that driver in this example. The above code attempts to load the JDBC/ODBC driver and, if it fails, prints an error and exits. (If you have a vendor-specific driver, you’d of course use that one in place of the “sun.jdbc.odbc.JdbcOdbcDriver” statement.)

Next, you have to connect to the database. JDBC datasources are identified by URLs, but they’re not quite like the URLs used to connect to a Web site. Here’s an example:
jdbc:odbc:demodb

This URL uses the JDBC/ODBC bridge to connect to the ODBC datasource (defined as demodb). If it’s possible that the datasource name could change, it’s used as a variable after the driver specification:
String dbName = “demodb”;
String url = “jdbc:odbc:” + dbName;

Connecting and querying with URLs
You can now put your connection statement URL to work, using it to gain a connection to the database and perform a query.
Connection dbConn = DriverManager.getConnection
(url, “dbuser”, “dbpassword”);

This statement will connect to the database demodb using the username and password combination of dbuser and dbpassword. It will return a database connection object that’s been defined as dbConn.

In order to use this connection, you’ll have to define a Statement variable so that an SQL statement can be sent to the database for execution.
Statement dbStatement = dbConn.createStatement();

Now you’re ready to use the dbStatement variable to run a query. The Statement variable has two methods available for usage—executeUpdate() and executeQuery(). The executeUpdate() method allows you to modify things within the database. For example, you can create an object or update a table. Let’s use the executeUpdate() method to update a table that contains the fields FirstName, LastName, and EmployeeID.
dbStatement.executeUpdate (“insert into employee values
 (‘John’, ‘Doe’, ‘01’);”);
dbConn.commit();

As you can see, values are inserted into the table. The Connection method commit() is then used to commit this transaction to the database.

Retrieving and displaying data
We’ll conclude our discussion of JDBC by retrieving data from the database and displaying it. This time, we’ll use the executeQuery() method, which will retrieve data and return the result set object. The statement will look like this:
ResultSet rs = dbStatement.executeQuery
(“select * from employee”);

All rows that exist within employee will be retrieved and stored within the ResultSet object rs. Not surprisingly, the ResultSet object has methods defined to facilitate this activity.

First, the next() method loops through the values contained within the ResultSet. When called, it advances to the next record in the ResultSet and returns TRUE or FALSE—TRUE if there’s another record to move to, and FALSE if not.

Some other methods include getInt(), getString(), getBytes(), getBoolean()—one exists for every data type. Depending upon the type of data contained within the field to be retrieved, you’ll need to use the appropriate get method. Let’s look at an example of the retrieval and printing of all the values contained within the ResultSet rs:
while (rs.next())
{
 System.out.println (“First Name : ” + rs.getString(“FirstName”));
 System.out.println (“Last Name : ” + rs.getString(“LastName”));
 System.out.println (“Employee ID: ” + rs.getString(“EmployeeID”));
 System.out.println ();
}

As you can see, it’s easy to use JDBC to connect to and interact with just about any database. If you haven’t yet used JDBC, the pointers provided here should get you well on your way!
This article features tips from our Java TechMail. If you would like to read more Java tips, sign up for the Java TechMail. Let us know what you think about this article and the Java TechMail. Send us an e-mail or post a comment below.
Database access makes up a major portion of Java applications development because at some point applications will have to interact with some other kind of database system.

With this interactivity so unavoidable, you need to have an understanding of Java DataBase Connectivity (JDBC). JDBC functions in much the same way that Open DataBase Connectivity (ODBC) does—it provides a way for applications to interact with multiple databases. Here are some tips on how to use JDBC to interact with almost any database.

Get the right driver
The first thing you need is the correct JDBC driver for the database you will connect to. If you need a driver that you don’t have, try the database vendor’s site, as vendors often post drivers for their systems as freely downloadable files.

If you can’t find the driver specific to your database, Sun provides a JDBC-to-ODBC bridge driver that might work just fine. Because you need at least the JDK 1.1 in order to use JDBC, you’ll already have the JDBC-to-ODBC bridge installed. You’ll create a datasource within ODBC for a database, and the JDBC driver will then use that ODBC-defined datasource to interact with the database.

Using code to connect
Now let’s look at some code that enables JDBC to connect to a database. We’ll break the code down into connection, statement execution, and results retrieval activities.

Let’s start with the connection. Consider the following code snippet:
try
{
 Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”).newInstance();
}
catch (ClassNotFoundException cnfe)
{
 System.err.println (“Error Loading Driver : ” + cnfe);
 System.exit(0);
}

Since we’ve already discussed the JDBC-to-ODBC bridge, we’ll use that driver in this example. The above code attempts to load the JDBC/ODBC driver and, if it fails, prints an error and exits. (If you have a vendor-specific driver, you’d of course use that one in place of the “sun.jdbc.odbc.JdbcOdbcDriver” statement.)

Next, you have to connect to the database. JDBC datasources are identified by URLs, but they’re not quite like the URLs used to connect to a Web site. Here’s an example:
jdbc:odbc:demodb

This URL uses the JDBC/ODBC bridge to connect to the ODBC datasource (defined as demodb). If it’s possible that the datasource name could change, it’s used as a variable after the driver specification:
String dbName = “demodb”;
String url = “jdbc:odbc:” + dbName;

Connecting and querying with URLs
You can now put your connection statement URL to work, using it to gain a connection to the database and perform a query.
Connection dbConn = DriverManager.getConnection
(url, “dbuser”, “dbpassword”);

This statement will connect to the database demodb using the username and password combination of dbuser and dbpassword. It will return a database connection object that’s been defined as dbConn.

In order to use this connection, you’ll have to define a Statement variable so that an SQL statement can be sent to the database for execution.
Statement dbStatement = dbConn.createStatement();

Now you’re ready to use the dbStatement variable to run a query. The Statement variable has two methods available for usage—executeUpdate() and executeQuery(). The executeUpdate() method allows you to modify things within the database. For example, you can create an object or update a table. Let’s use the executeUpdate() method to update a table that contains the fields FirstName, LastName, and EmployeeID.
dbStatement.executeUpdate (“insert into employee values
 (‘John’, ‘Doe’, ‘01’);”);
dbConn.commit();

As you can see, values are inserted into the table. The Connection method commit() is then used to commit this transaction to the database.

Retrieving and displaying data
We’ll conclude our discussion of JDBC by retrieving data from the database and displaying it. This time, we’ll use the executeQuery() method, which will retrieve data and return the result set object. The statement will look like this:
ResultSet rs = dbStatement.executeQuery
(“select * from employee”);

All rows that exist within employee will be retrieved and stored within the ResultSet object rs. Not surprisingly, the ResultSet object has methods defined to facilitate this activity.

First, the next() method loops through the values contained within the ResultSet. When called, it advances to the next record in the ResultSet and returns TRUE or FALSE—TRUE if there’s another record to move to, and FALSE if not.

Some other methods include getInt(), getString(), getBytes(), getBoolean()—one exists for every data type. Depending upon the type of data contained within the field to be retrieved, you’ll need to use the appropriate get method. Let’s look at an example of the retrieval and printing of all the values contained within the ResultSet rs:
while (rs.next())
{
 System.out.println (“First Name : ” + rs.getString(“FirstName”));
 System.out.println (“Last Name : ” + rs.getString(“LastName”));
 System.out.println (“Employee ID: ” + rs.getString(“EmployeeID”));
 System.out.println ();
}

As you can see, it’s easy to use JDBC to connect to and interact with just about any database. If you haven’t yet used JDBC, the pointers provided here should get you well on your way!
This article features tips from our Java TechMail. If you would like to read more Java tips, sign up for the Java TechMail. Let us know what you think about this article and the Java TechMail. Send us an e-mail or post a comment below.