Database Programming With Java + JDBC
Interacting with a database is a routine job in the application world. Quite often you need to store information in the database, fetch it to generate some form of report and update or delete it as required. Java provides the JDBC (Java Database Connectivity) API for executing SQL statements. JDBC, being Java-based, leverages the benefits of the language and provides a convenient way to communicate with different databases, such as Oracle, Informix, Sybase, etc.
In this article I will introduce you to the JDBC API, showing you how to connect to and work with a database.
What is JDBC?
The structured query language (SQL) is popularly used for database operations. You can execute SQL statements to perform 2 major types of operations:
1. DDL: Data definition language statements let you do jobs like creating a table.
2. DML: Data manipulation language statements let you do jobs like firing a select query. JDBC is an API specification to provide access to the database. In order to use it, you need to choose an appropriate JDBC driver. The driver acts as a gateway between your Java program and the database.
Q. Do I have any choice for which JDBC driver to use?
The different types of JDBC drivers are shown below:
1. Type 1: JDBC-ODBC bridge driver
This provides access to the database using an ODBC (Open Database Connectivity) driver. This is popularly used in development environments when you have an ODBC driver for your database. A JDBC-ODBC driver comes with the standard JDK distribution.
2. Type 2: Native-API partly Java driver
This provides access to the database by translating a JDBC call to a native database level API. Typically, it uses native C code to do this. This driver is not a good candidate for applets.
3. Type 3: JDBC-Net pure Java driver
This provides access to the database by translating a JDBC call into a Net protocol, which is then translated by the server. Since this is a pure Java driver, it’s a good candidate for use in applets.
4. Type 4: Native-protocol pure Java driver
This provides access to the database by translating a JDBC call to a database specific call, such as Oracle Call Level Interface (CLI). This is commonly used for server-side Java programming (such as servlets).
Q. Hmmm, that sounds familiar. How’s this different from ODBC?
Well, the point is not in difference. As you can see from the types of drivers above, Java has leveraged to a great extent from existing APIs like ODBC (Open Database Connectivity), other legacy implementations and even vendor specific implementations. All that wrapped together in an easy to use API specification works like a charm for many developers.
Q. What do I need to know if I want to use JDBC?
JDBC provides a high amount of simplicity to developers, however there are some basic things that are, in general, applicable to use it effectively:
1) Knowledge of SQL.
2) Access to a database and an appropriate JDBC driver. Most database vendors provide one or more types of JDBC drivers, downloadable via their website.
3) Familiarity with JDBC — That’s the goal of this series of articles.
Q. How do I use JDBC to talk to a database?
Shown below are the steps involved in using JDBC to talk to a database:
Load an appropriate JDBC driver.
1) Open a connection with the database.
2) Get a statement object to fire a database call.
3) Optionally, update the statement with any inputs. For example, you may want to pass the values for an insert clause.
4) Fire the statement.
5) If the statement returns any result it’s returned in the form of a java.sql.ResultSet object. This lets you traverse the results in a sequential manner.
6) Cleanup: close the associated objects. At this point, you may either close the database connection or may return it to a connection pool.
Java provides JDBC related APIs via the java.sql package.
As we discussed earlier, the role of a JDBC driver is to act as a gateway between your Java program and the database. It’s recommended to use an appropriate JDBC driver from the database vendor. However, you can use the JDBC-ODBC bridge driver bundled with the standard JDK distribution for development purposes only.
Opening a connection with the database requires a connect string, and an optional username and password. Once you have the connection established, it provides you with the necessary resources to access the database.
The statement lets you fire the SQL statements. This is a frequently used object in JDBC programming.
For select queries, the result is provided via a java.sql.ResultSet object. The ResultSet is an interface that provides convenient API’s to extract the returned data (more about this later).
And finally, you should do the clean up by closing the ResultSet, Statement and Connection objects. You may defer closing of certain objects, like the connection, if you are using some sort of object pooling.
Before We Start Coding
In order to do some hands on coding, you need to have access to the following:
1) A database management system, such as Microsoft Access
2) A JDBC driver
If you don’t have access to a DBMS, you can download the open source MySQL database at http://www.mysql.com. You can also download the JDBC driver for MySQL from this site.[Note] You need to have the driver classes in your CLASSPATH statement in order to load the driver. [End Note]
A simple example
What’s better than developing a small application to drill down into JDBC programming! Lets develop a small address book application that has the following features:
1) Lets you create the address book table
2) Lets you add an entry to the book
3) Lets you search for an entry in the book
This simple application will let us demonstrate the use of various JDBC classes and different types of SQL statements. It uses a database named “test_db” without any user ID or password. The database driver used is a type 4 driver for MySQL, however you can replace it with any other JDBC driver of your choice.
A word of caution: do not be overwhelmed by the lines of code below. Instead, concentrate on the core logic. Also, this is just a sample application to demonstrate the steps involved in JDBC programming. It’s not designed for a production system.
Having said that, lets get started:
The Sample Code… Explained
There are two classes in our sample application:
AddressBookEntry: Represents an entry in the database.
JDBCDemo1: The demo class.
The demo class puts forth several interesting aspects of JDBC programming. Let’s take a look at them one by one.
First of all, the main() method creates a database connection to be used later in the database operations. This is done via the getConnection() method, which loads the driver and uses a database URL to connect to the database.
Most JDBC drivers register themselves with java.sql.DriverManager when their class is loaded. Moving on, we then try to obtain a connection using the DriverManager class by supplying a database URL. This URL is in the following format:
… where subrotocol lets you specify which driver to use, and rest of the URL provides information to the driver about the database it should connect to. The DriverManager.getConnection() method also lets you specify the user ID and password to connect to the database. For the sake of simplicity, we aren’t using any.
The main() method presents a simple text-based menu to the user. Based on the choice (1-4), the method determines, what to do. Initially (when running this application for the first time), we should create the “address_book” table using choice 1. This fires the createTable() method.
It creates a java.sql.Statement object using the connection’s createStatement() method. It then uses this statement to fire the SQL statement, using the executeUpdate() method. This simple looking code has actually created a table in the “test_db”.
Pay special attention to the finally block of the method. It checks whether the statement object was created. If yes, it closes it. This releases the system resources associated with the statement object. It’s extremely important to make sure you release such crucial system resources as soon as possible (unless your program has been designed to defer this). But why do we do this inside of the finally block? Well, this is simply because it’s executed in all cases, except when there’s an unusual termination of the program, such as when the user hits the “Ctrl+C” key sequence.
Continuing on the same line, choice 2 lets you add an entry to the database. This is done in the add() method, which takes the nick name, name, and email as inputs. Again, the logic is similar to the createStatement() method, except this time we are firing an insert statement.
Choice 3 lets you search an entry in the addressbook with the specified nick name. The lookup() method provides this functionality. Notice that in this case we are firing the executeQuery() method. This facilitates executing a select query and extracting the results.
JDBC provides a nice object view of the fetched rows via a java.sql.ResultSet object. The first next() call provides information about the first row in the result. ResultSet provides numerous getXXX() types of methods to retrieve the value of a column as an appropriate data type. For instance, here we are using getString() to retrieve the values as java.lang.String objects.
Each getXXX() method takes the index of the column in the result whose value is to be retrieved. You can also specify the name of the column instead of its index. Did you notice something unusual in the code here?
Got it? The argument to the first getString() call is 1, not 0. This is one of the most common traps that many Java developers fail to realize initially, so sock it home that the index used as arguments to the JDBC APIs are 1-based.
When you prefer to quit the application, simply choose 4 and the main() function will close the database connection and exit.
In this article we’ve had a pretty involved discussion of JDBC programming. Although we did not touch upon any advanced topics, we did cover the typical steps and some good practices like performing clean up in the finally blocks.
A simple enhancement that you can take up as an assignment is to add modify and delete functionality to the sample application that we looked at in this article.
In my next article we will look at several advanced JDBC programming techniques, but until then, happy JDBC programming!
Nitin runs http://www.TechnoBuff.com, which provides Java developers will the tools, articles and resources they need to succeed.
Many more Java, ASP, PHP, .NET, and C++ articles like this one are available at http://www.devarticles.com. If you’re in search of free scripts to help make your life as a developer easier, why not check out http://www.devscripts.com.