Unless you write only the most trivial applications, you’re likely to need access to a database at some point during your Java programming lifetime. So it’s probably a good idea for you to get comfortable with Java’s data access API, Java Database Connectivity (JDBC). That’s exactly what we’ll try to help you do in this article.
Now, I’m going to assume that you have at least a rudimentary knowledge of databases and are familiar with terms like table, column, row, and stored procedure. I’m also assuming that you have some familiarity with another data access library, like DAO or ADO, to the extent that you can successfully connect to a given database and retrieve some data. A basic knowledge of SQL is assumed as well. In a nutshell, while this will be an introductory JDBC tutorial of sorts, it’s not going to be Data Access 101.
First off, when approaching a project that’s going to require data access, you’ll need to decide on a driver. There are four types of JDBC drivers:
- · The type 1 driver is also known as the JDBC-ODBC Bridge driver. It basically amounts to a Java interface for an Open Database Connectivity (ODBC) driver. The Java2 platform includes a type 1 driver.
- · A type 2, or Native Protocol, Partly Java driver, is essentially a Java-compatible wrapper for the proprietary database system’s client-side driver.
- · A type 3, or Net Protocol, All Java driver, makes JDBC calls using a network protocol supported by the database vendor. Then, some form of server-side middleware (usually from a third party) converts the net protocol calls into the proprietary instructions that the database system understands.
- · Finally, we have type 4, Native Protocol, All Java drivers. These are similar to type 2 drivers, in that both are essentially Java wrappers for a database’s proprietary interface. However, type 4 drivers reside entirely on the database server and require no client-side code or configuration.
The choice of driver type is primarily driven by two factors: what’s available for the database system you are using and whether or not configuring the client is going to be problematic. Since type 1 and 2 drivers both require some client configuration, they usually aren’t appropriate choices for an Internet-based application. Type 3 drivers are usually only commercially available for a fee, and type 4 drivers aren’t available for all database systems and may never be available for all of the major ones. I doubt we’ll be seeing one for SQL Server very soon.
Since you are guaranteed to have a type 1 driver, the examples in this article will be using that one. As a result, you may need to define an ODBC data source on your machine before you can run my sample code. Don’t fret; the syntax for using other driver types is similar to the type 1 syntax. You’ll just be using a different class name and connection string.
Class is in session
The objects in the java.sql package define a basic set of functionality that allows Java applications to work with data from a database in an abstract fashion. The classes you should be interested in are:
- · Driver—This represents the database driver that converts JDBC calls into native database commands.
- · DriverManager—This catalogs, manages, and loads Drivers.
- · Connection—This represents an open session on a database.
- · ResultSet—This represents rows of data retrieved from a table in the database.
- · Statement—This provides a mechanism for executing SQL statements.
- · PreparedStatement—This is a subclass of Statement that can be used to repeatedly execute a SQL statement. It provides support for replaceable parameters in the SQL. When a PreparedStatement is first executed, the SQL it contains is compiled and cached so that it can be used repeatedly, usually at a performance advantage over repeated use of a Statement.
- · CallableStatement—This is a subclass of PreparedStatement that is used to execute stored procedures in the database.
They aren’t really classes
If you look at Sun’s documentation for these objects, you’ll see that they aren’t referred to as classes but as interfaces. Don’t let that throw you. When a programmer creates a JDBC driver, he or she implements these interfaces as concrete classes for an application programmer (like you) to use.
To access a database, you would load the driver using Class.ForName and then call the DriverManager.getConnection method, specifying a database URL. The format of this URL is dependent on the type of driver you are using and, aside from the driver name, is really the only part of the code that changes based on driver type. The DriverManager will attempt to open the database, returning a Connection object if successful or throwing a SQLException if unsuccessful. You can then use the Connection to create Statement, PreparedStatement, or CallableStatement objects using the createStatement, prepareStatement, and prepareCall methods, respectively, to manipulate or retrieve data from the database.
Getting some results
You retrieve data by passing a SQL Select command to the statement object or subobject executeQuery method. It will return a ResultSet object that represents the rows of data your query selected. Unless you specify otherwise, a ResultSet has a forward-only, nonupdateable cursor (meaning you can only move forward in the data set and can’t make any changes). You can request a particular type of cursor when you create a statement by passing parameters to the statement creation method. To modify data using SQL Update, Insert, or Delete operations, you pass the SQL command to the executeUpdate method. Take a look at Listing A for some code that should solidify this process for you.
The methods of the ResultSet object allow you to move to different rows in the data set, retrieve and set column values from a particular row, and insert new rows into or delete rows from the data set. You retrieve a column’s data using a set of methods that begin with the word get, while updates are performed using another set that begins with the word set. These methods all have different names that indicate the kind of data they are designed to deal with: getString and setString, for example, deal with string data.
You can specify a column by its position (getString(2) retrieves the data in the third column) or by name (getString(“CustomerName”)). When dealing with field names, JDBC is case-insensitive. The code in Listing B illustrates how to move around, retrieve, and update the data in a ResultSet.
Not just data but metadata
One final trick before I go. There are situations when you need to know something about the structure of a database, like the number of columns in a table. JDBC’s ResultSetMetaData object, created using the ResultSet.getMetaData method, can be used to figure out all kinds of information about the structure of a ResultSet‘s underlying database object.
As with most things Java, JDBC—the term used to collectively refer to the classes that allow Java applications to access databases—can seem a little weird at first blush. Once you get past initial first impressions, however, you’ll find JDBC to be a powerful data access library.