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:
jdbc:subprotocol:subname
… 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.
Conclusion
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.
