Getting Started with JDBC

By David Reilly

Accessing a database in Java doesn't have to be a difficult task. Using Java Database Connectivity (JDBC) you can easily access databases in your applications and applets via special drivers.

What is JDBC?

Java Database Connectivity (JDBC) provides Java developers with a standard API that is used to access databases, regardless of the driver and database product. To use JDBC, you'll need at least JDK 1.1, a database, and a JDBC driver. Installing the first two should be straightforward, but finding a JDBC driver requires a little more effort. JDBC presents a uniform interface to databases - change vendors and your applications only need to change their driver.

There are plenty of drivers now for JDBC that support popular databases. If you can use a JDBC driver that works specifically for your database, then that's great! If not, don't worry - Sun provides a driver that is compatible with ODBC, so you should be able to connect to any ODBC compliant database. The JDBC to ODBC bridge comes installed as part of JDK1.1, so if this is your target platform, the driver will already be installed. You'll need to create an ODBC datasource for your database, before your Java applications can access it.

Connecting to a database

In order to connect to a database, you need to perform some initialization first. Your JDBC driver has to be loaded by the Java Virtual Machine classloader, and your application needs to check to see that the driver was successfully loaded. We'll be using the ODBC bridge driver, but if your database vendor supplies a JDBC driver, feel free to use it instead.

// Attempt to load database driver
try
{
	// Load Sun's jdbc-odbc driver
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (ClassNotFoundException cnfe) // driver not found
{
	System.err.println ("Unable to load database driver");
	System.err.println ("Details : " + cnfe);
	System.exit(0);
}

We try to load the JdbcOdbcDriver class, and then catch the ClassNotFoundException if it is thrown. This is important, because the application might be run on a non-Sun virtual machine that doesn't include the ODBC bridge, such as Microsoft's JVM. If this occurs, the driver won't be installed, and our application should exit gracefully.

Once our driver is loaded, we can connect to the database. We'll connect via the driver manager class, which selects the appropriate driver for the database we specify. In this case, we'll only be using an ODBC database, but in more complex applications, we might wish to use different drivers to connect to multiple databases. We identify our database through a URL. No, we're not doing anything on the web in this example - a URL just helps to identify our database.

A JDBC URL starts with "jdbc:"  This indicates the protocol (JDBC). We also specify our database in the URL. As an example, here's the URL for an ODBC datasource called 'demo'. Our final URL looks like this :

jdbc:odbc:demo

To connect to the database, we create a string representation of the database. We take the name of the datasource from the command line, and attempt to connect as user "dba", whose password is "sql".

// Create a URL that identifies database
String url = "jdbc:odbc:" + args[0];

// Now attempt to create a database connection
Connection db_connection = 
	DriverManager.getConnection (url, "dba", "sql");

As you can see, connecting to a database doesn't take much code.

Executing database queries

In JDBC, we use a statement object to execute queries. A statement object is responsible for sending the SQL statement, and returning a set of results, if needed, from the query. Statement objects support two main types of statements - an update statement that is normally used for operations which don't generate a response, and a query statement that returns data.

// Create a statement to send SQL
Statement db_statement = db_connection.createStatement();

Once you have an instance of a statement object, you can call its executeUpdate and executeQuery methods. To illustrate the executeUpdate command, we'll create a table that stores information about employees. We'll keep things simple and limit it to name and employee ID.

// Create a simple table, which stores an employee ID and name
db_statement.executeUpdate 
   ("create table employee { int id, char(50) name };");

// Insert an employee, so the table contains data
db_statement.executeUpdate 
   ("insert into employee values (1, 'John Doe');");

// Commit changes
db_connection.commit();

Now that there's data in the table, we can execute queries. The response to a query will be returned by the executeQuery method as a ResultSet object. ResultSet objects store the last response to a query for a given statement object. Instances of ResultSet have methods following the pattern of getXX where XX is the name of a data type. Such data types include numbers (bytes, ints, shorts, longs, doubles, big-decimals), as well as strings, booleans, timestamps and binary data.

// Execute query
ResultSet result = db_statement.executeQuery
	("select * from employee");

// While more rows exist, print them
while (result.next() )
{
	// Use the getInt method to obtain emp. id
	System.out.println ("ID : " + result.getInt("ID"));

	// Use the getString method to obtain emp. name
	System.out.println ("Name : " + result.getString("Name"));
	System.out.println ();
}

Putting it all together

To show you just how JDBC applications work, I've put together a simple demonstration, that allows users to insert new employees into the system, and to obtain a list. The demonstration uses ODBC to connect to an Access database, which can be downloaded along with the source code.

Running the sample application is quite straightforward. First, you'll need to create an ODBC datasource for the access database. Next, using JDK1.1 or higher, run the JDBCDemo application, and pass the datasource name as a parameter.

	java JDBCDEMO demo

The demonstration application presents you with a menu, containing three options. Choosing the first operation allows you to add a new employee, using the SQL insert statement. Choosing the second option displays all employees in the system, using the SQL select statement. Finally, the third option closes the database connection and exits.

Menu

1. Add new employee
2. Show all employees
3. Exit
Choice : 1
ID : 3
Name : Bill Gates

Menu

1. Add new employee
2. Show all employees
3. Exit
Choice : 2
ID : 1
Name : David Reilly

ID : 2
Name : John Doe

ID : 3
Name : Bill Gates

Summary

Writing database applications in Java need be no more difficult than other languages. JDBC offers a wide range of functionality not discussed in this article, such as prepared statements for greater performance, stored procedures, scrollable result sets, batch updates and new datatypes (Fisher, 1998). Even without this advanced functionality, developers can easily interface with databases and create database aware software. Database drivers are available from a variety of vendors, and the Sun ODBC bridge can be used when a suitible driver is not available. When you need to interface with a database in your next project, don't worry - with a little practice, JDBC becomes easy!

References

Fisher, M. JDBC Database Access, in The Java Tutorial [online] at http://java.sun.com/docs/books/tutorial/

Back to main