JDBC Basics

Thornton Rose
(Article text)

April Rose
(Example code and programs)

Published as "JDBC Basics", 6/8/99, Gamelan.com.

The Java Database Connectivity (JDBC) API is used to access databases from Java programs. There are several books available on the subject, but there are just a few things that you need to know to get started. In this article, we present the basics.

Tools

You will need the following tools to write Java programs that use JDBC:

JDK 1.1 or JDK 1.2 (Java 2)
A database
The JDBC driver for your database
Your favorite text editor.

Depending on the JDBC driver, the database can be anything from Oracle to a collection of dBase files. For this article, except for the section on stored procedures, tinySQL was used, because it is small and free (distributed under the GNU General Public License). tinySQL is a very minimal SQL engine that comes with a JDBC driver and supports the following SQL statements:

SELECT (simple joins, no aggregate functions, no nested queries)
UPDATE
INSERT
DELETE
CREATE TABLE
DROP TABLE

JDBC Driver

To communicate with your database using JDBC, you will need a JDBC driver. Usually you will get this from the database vendor, but there are several third-party drivers that work with most of the common databases (e.g., Oracle, Sybase). Also, you can use the JDBC-ODBC bridge if you are running your programs on Windows and are not doing anything too serious. The JDBC-ODBC bridge is a driver from Sun that allows Java programs to access databases via Microsoft's ODBC (Open Database Connectivity) API.

Before you can connect to a database or execute any database statements, the JDBC driver must be loaded. The primary way to do this is to use the Class.forName() method. For tinySQL, it would like this:

Class.forName("ORG.as220.tinySQL.textFileDriver");

For the JDBC-ODBC bridge, it would look like this:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

You only have to load the JDBC driver once in your program. After it has been loaded, it will be available via the DriverManager, which manages JDBC drivers.

Connection

With the JDBC driver loaded, getting a connection is easy. You import the JDBC classes (which are in the java.sql package) and call the getConnection() method of the DriverManager class. However, you will have to look up the parameters for your JDBC driver.

Here is fragment of code that gets a connection to a tinySQL database:

Connection = DriverManager.getConnection("jdbc:tinySQL");

Here is another fragment that gets a connection to an ODBC data source via the JDBC-ODBC bridge:

Connection dbConnection = DriverManager.getConnection("jdbc:odbc:foo");
// Note: "foo" must be defined as an ODBC system DSN.

MakeConnection is a simple program that loads a JDBC driver, connects to a data source, then closes the connection.

Statements

Once you have an open connection to a database, you can extract and manipulate data using SQL (Structured Query Language) via database statements. You use the Connection.createStatement() to create a database statement, like this:

Statement dbStatement = dbConnection.createStatement();

Then, you use Statement.executeQuery() or Statement.executeUpdate() to execute the SQL statement, like this:

// Create table.
dbStatement.executeUpdate("create table foo (f1 char(1), f2 number(1))");

// Insert data.
dbStatement.executeUpdate("insert into foo (f1, f2) values('A', 1)");

// Extract data.
ResultSet dbResultSet = dbStatement.executeQuery("select * from foo");

Result Sets

To extract data, you use a SELECT statement, executed via the Statement.executeQuery() method. This method returns a ResultSet object, which is a Java representation of the rows and columns of the data that results from the query. (You can think of it as a table.) To get the next available row, you call the next() method or the ResultSet object. To get a column you call the ResultSet.getX(), where "X" is the data type of the column (e.g. getInt()). Here is a fragment of code that displays a result set:

ResultSet dbResultSet = dbStatement.executeQuery("select f1, f2 from foo");

while (dbResultSet.next()) {
   System.out.println(dbResultSet.getString("f1") + ", " +
      dbResultSet.getInt("f2"));
}

Note that you can also use column positions to retrieve column values from the result set, like this:

// Get value of column 2
dbResultSet.getInt(2);

SimpleStatement is a simple program that connects to a data source, executes some statements, then displays the contents of a result set.

Stored Procedures

Some backend database servers, such as Oracle, provide the ability to store SQL procedures in the database. These procedures can then be executed by external programs to perform database operations. (This is often done to increase performance and to reduce application complexity.)

To call a stored procedure using JDBC, you use the CallableStatement class, like this:

CallableStatement cstmt = conn.prepareCall("{call FOO()}");
cstmt.execute();

Stored procedures can have parameters and a return value. The positions of the return value and the parameters are specified in the statement with questions marks, like this:

CallableStatement dbStoreProc = dbConnection.prepareCall(
   "{?=call PKG_CALLER_CREDITS.SUMMARY_REPORT(?,?)}" );

Both the return type and the types and values of the parameters must be registered before the procedure can be called. If a parameter or return value is going to come back from the stored procedure (often referred to as an "out" mode parameter), you register the type with CallableStatement.registerOutParameter(), like this:

dbStoredProc.registerOutParameter(1, java.sql.Types.INTEGER);

If the parameter is being sent into the procedure (often referred to as an "in" mode parameter), you set the value by calling the CallableStatement.setX(), where "X" is the type of the parameter, like this:

dbStoredProc.setString(2, displayName);

The return value is retrieved by calling CallableStatement.getX(), where "X" is the type of the return value, like this:

int reportKey = dbStoredProc.getInt(1);

Note that the first parameter of registerOutParameter(), getX(), and setX() is the position of the return value or parameter in the callable statement. For the callable statement shown above, the return value is in position 1, and the parameters are in positions 2 and 3.

StoredProcedureCall is an example program that calls a stored procedure in an Oracle database.

Gotchas

In an applet running in the Internet Explorer Java VM, sometimes the JDBC driver will not load properly using Class.forName(). A workaround is to instantiate the driver directly, then use it to get a Connection object, like this:

import java.sql.*;
import sun.jdbc.odbc.JdbcOdbcDriver;

...

Driver dbDriver = new JdbcOdbcDriver();
Connection dbConnection = dbDriver.getConnection("jdbc:odbc:foo");

The JDBC-ODBC bridge is not multithreaded. This means that you cannot execute simultaneous statements or run asynchronous queries with the JDBC-ODBC bridge.

Closing a statement or a connection can sometimes throw an exception. This is not a major problem, but it is something that should be handled in your code.