Using More Advanced JDBC Features

Working with the Database Using JDBC

Now that the empty database has been created and the DSN connection is complete, you now can demonstrate how to use JDBC to manipulate the database.

First, define the functionality that will be used in this application. In fact, following an object-oriented approach, you will create a single class called JDBCDemo that will contain individual methods that will perform the following tasks:

Load the driver

Connect to the database

Create a statement

Create a table

Insert a record into that table

Commit the changes

Execute a query

Update a record

Close the connection

The ability to perform these functions will allow you to create a very powerful database application.

As stated earlier, you are going to encapsulate all of the functionality listed above in a class called JDBCDemo. Besides the methods needed to provide this functionality, you will create three class attributes called db_connection, db_statement, and result, as seen in Listing 1.

These class attributes are required because the information contained within them is needed by more than one method.

Because you are making the JDBCDemo class modular by encapsulating the specific JDBC functionality in separate methods, you can utilize these methods in various applications. Before you explore the JDBC code itself, create an application called JDBCApp that will invoke the methods of the JDBCDemo class. This application is presented in Listing 2.

Note that even though you have yet to see a single line of code in any of the methods of JDBCDemo, you can easily deduce how this application will flow as well as the direction that this tutorial is taking. After you instantiate a JDBCDemo object, you simply will proceed method by method to demonstrate how the JDBC functionality is used.

Loading the Driver

The first step is to connect to the driver (see last month's article for detailed information on loading the driver). In this case, you will use Sun's driver, sun.jdbc.odbc.JdbcOdbcDriver. You load it with the following line of code:

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

Note that in this application, I use a standard approach to catching exceptions and reporting successful completion of a task. Whenever a JDBC line of code is executed, Java requires that a try/catch block be provided. The complete method to load the driver is presented in Listing 3.

Using a DOS Shell, the compilation should compile cleanly, as shown in Figure 7.

Figure 7

See what happens when you load the driver so you can see what the output for this and the following methods will look like. I use the following command-line statement:

java -classpath .;C:j2sdk1.4.0jrelibrt.jar JDBCApp

Using a DOS Shell, the code should execute cleanly, as shown in Figure 8.

Figure 8

Note: When the code executes cleanly, a message box is displayed to help you develop and debug this application.

Connecting to the Database

In this example, you are connecting to the business.mdb database that you created at the start of this article. Remember that the DSN connection was named Business. To make the example simpler, you hard-code the DSN and assign it to a local attribute called dbName. Then, you need to build the URL that is used to connect to the database by appending the database name to the string "jdbc:odbc:". You use the following line to make the connection:

DBConnection = DriverManager.getConnection (url, "dba", "sql");

The complete method used to connect to the database is presented in Listing 4.

Creating the Table

In last month's article, you only connected and queried a table that was already in the database. Part of the exercise for this article is to create a brand new table. To accomplish this, you use a statement like the following (you may want to adjust the parameters based on your individual requirements).

Note: The specific syntax used in this article was found at the Microsoft Web site, which is noted at the end of this article. Please reference this site to learn about SQL syntax that is specific to Microsoft platforms.

The table you create is called tblCustomers. In this, example two fields that are defined. The first is an integer called CustomerID and the second is a string called Name. The complete method is presented in Listing 6.