This series, The Object-Oriented Thought Process, is intended for someone just learning an object-oriented language and who wants to understand the basic concepts before jumping into the code, or someone who wants to understand the infrastructure behind an object-oriented language he or she is already using. These concepts are part of the foundation that any programmer will need to make the paradigm shift from procedural programming to object-oriented programming.
Click here to start at the beginning of the series.
In keeping with the code examples used in the previous articles, Java will be the language used to implement the concepts in code. One of the reasons that I like to use Java is because you can download the Java compiler for personal use at the Sun Microsystems Web site http://java.sun.com/. You can download the J2SE 1.4.2 SDK (software development kit) to compile and execute these applications and I will provide the code listings for all examples in this article. I have the SDK 1.4.0 loaded on my machine. I will also provide figures and the output (when appropriate) for these examples. See the previous articles in this series for detailed descriptions for compiling and running all the code examples.
In the previous article, I covered the basics of connecting to a database using Java Database Connectivity (JDBC). In these examples, you will connect to a Microsoft Access database; however, you are not limited to a specific technology when using JDBC. In future articles, you will explore other databases.
To demonstrate the basics of using JDBC, you utilized an Access database that was previously created. You then proceeded to connect to the database and perform various simple queries. By using the coding techniques in these examples, you were able to verify that JDBC was performing as designed and that the physical connection to the database was made successfully.
One of the interesting exercises of the last column was to navigate through the Microsoft Windows Control Panel to properly configure the ODBC Data Source. In this column, we will explore JDBC code that will actually create a table and then perform more advanced transactions such as deleting and inserting records into those tables.
A JDBC Tutorial, Part 2
For these JDBC installments, I take a somewhat different approach from the previous articles in the series by using a tutorial format. The reason for this is that, to utilize JDBC functionality, you must interact directly with the operating system that you are working on; thus, there is a certain amount of system configuration that you must complete.
To demonstrate the system configuration aspects, the liberal use of screen shots is very helpful. I actually use this tutorial technique in the classroom when teaching an Object Technology class that I offer. It is presented in HTML format just like the content of this article. In this way, the students can follow the examples and apply the concepts directly to their computer. When this tutorial is complete, you will have a working application that connects directly to a database using JDBC.
Creating the Database & DSN Connection
As with last month’s column, before you delve into the actual code, you must first set up the Data Source for the database. In fact, even before you do this, you must create the database itself. Previously, you used a database that was built using the functionality provided by Microsoft Access. In this column, you’ll use Access to actually create the blank database and then use JDBC to do the rest.
Creating the Microsoft Access Database
To start, create a directory called JDBCApp off your root. You will use this directory structure to make the example a bit easier to understand. Once the directory is created, launch Access, create a Blank Database, and save it in this directory as seen in Figure 1.
Figure 1
That is all you have to do in interacting with Microsoft Access (at least the application itself). You will use Access to inspect what your application created; however, you will use JDBC to perform subsequent tasks.
Creating the DSN Connection
Now, make the DSN connection. To accomplish this with Microsoft Windows, you first need to bring up the Control Pane,l as indicated in Figure 2, and click on the Administrative Tools icon.
Figure 2
Once you get to the Administrative Tools dialog box, you then click on the Data Sources icon as seen in Figure 3.
Figure 3
Clicking on the Data Sources icon will bring you to the ODBC Data Source Administrator that will allow you to specify the name and the driver to be used by the application, as shown in Figure 4.
Figure 4
Once you are in the ODBC Data Source Administrator, click on the File DSN tab and then the Add button. (Note that you won’t see the Business System Data Source Name until you complete the add process.) The Microsoft ODBC Access Setup dialog box appears as in Figure 5.
Figure 5
There are two things that must be completed in this dialog box:
- Choose a Data Source Name.
- Click on the Select button and find the business.mdb file that you previously created as shown in Figure 6. Then, click the OK button.
Figure 6
At this point, the DSN connection is complete and you can proceed to the second part of this tutorial, developing the code to utilize the database and the DSN connection that you created.
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.
Listing 1
public class JDBCDemo {
Connection db_connection;
Statement db_statement;
ResultSet result;
}
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.
Listing 2
public class JDBCApp {
public static void main(String args[]){
// Create an instance of JDBCDemo
JDBCDemo dbInstance =new JDBCDemo();
dbInstance.loadDriver();
dbInstance.connectToDB();
dbInstance.createStatement();
dbInstance.createTable();
dbInstance.insertRecord("1", "John Doe");
dbInstance.insertRecord("2", "Jane Smith");
dbInstance.commitChanges();
dbInstance.executeQuery();
dbInstance.updateRecord();
dbInstance.executeQuery();
dbInstance.closeConnection();
}
} // end JDBCApp()
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.
Listing 3
public void loadDriver(){
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
System.out.println ("+++++++++++++++++++");
System.out.println ("+ database driver loaded +");
System.out.println ("+++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to load database driver: n" + excep);
System.exit(0);
}
}
Compiling the Code
Before you look at any more code, let me cover how you are going to compile both the JDBCDemo class and the JDBCApp application. I use the following command-line statements:
javac -classpath .;C:j2sdk1.4.0jrelibrt.jar JDBCApp.java javac -classpath .;C:j2sdk1.4.0jrelibrt.jar JDBCDemo.java
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.
Listing 4
public void connectToDB(){
String dbName = "Business";
String url = "jdbc:odbc:" + dbName;
try {
DBConnection = DriverManager.getConnection (url, "dba", "sql");
System.out.println ("+++++++++++++++++++++++++++++++");
System.out.println ("+ connected to database to DB + " + dbName);
System.out.println ("+++++++++++++++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to connect to database: n" + excep);
System.exit(0);
}
}
Creating the Statement
Once the connection is confirmed, you now can perform various SQL statements. As with many of the other methods, this is done with a single statement.
DBStatement = DBConnection.createStatement();
Note: Most of the code in all of these methods is in place for exception handling.
The method to create the statement is shown in Listing 5.
Listing 5
public void createStatement(){
try {
DBStatement = DBConnection.createStatement();
System.out.println ("+++++++++++++++++++++");
System.out.println ("+ statement created +");
System.out.println ("+++++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to create statement: n" + excep);
System.exit(0);
}
}
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.
To create the table, this method uses the folloinwg line of code:
String custTable =
"CREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL,
[Name] TEXT(50) NOT NULL);
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.
Listing 6
public void createTable(){
String custTable =
"CREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL,
[Name] TEXT(50) NOT NULL)";
try {
DBStatement.executeUpdate(custTable);
System.out.println ("+++++++++++++++++");
System.out.println ("+ table created +");
System.out.println ("+++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to create table: n" + excep);
System.exit();
}
}
At this point, you can actually see evidence of the table creation by inspecting the database directly. When you open the database in Access, you will see the tblCustomers table, as seen in Figure 9.
Figure 9
Open up the table and inspect the structure that should include the two fields that were created. The screen shot for this can be seen in Figure 10.
Figure 10
Inserting the Record
Now that the table is created, you can proceed to insert some individual records. Rather than hard code all of the information, I decided to pass a couple of parameters so that I can vary the information inserted into the record. In this case, hard-coding the information made testing more difficult. I use the following three lines of code to build the string containing the SQL information for the insertion of the record into the table.
String data = "("+ id + "," + "'" + name + "'" + ");";
String sqlRecord = "insert into tblCustomers values " + data;
System.out.println ("record = " + sqlRecord);
When the passed parameters are as follows, ID = 1 and name = “John Doe”, the resulting string will look like this:
"insert into tblCustomers values (1,'John Doe')"
When this statement is completed successfully, the table will contain a record with this information. The complete method is presented
in Listing 7.
Listing 7
public void insertRecord(String id, String name){
String data = "("+ id + "," + "'" + name + "'" + ");";
String sqlRecord = "insert into tblCustomers values " + data;
System.out.println ("record = " + sqlRecord);
try {
DBStatement.executeUpdate (sqlRecord);
System.out.println ("+++++++++++++++++++");
System.out.println ("+ record inserted +");
System.out.println ("+++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to insert record: n" + excep);
System.exit(0);
}
}
When you execute these insertions, you see the output presented in Figure 11.
Figure 11
Committing the Changes
As I progressed with the code for this application, one of the more frustrating points was when the statements appeared to be working properly, yet the changes were not showing up in the database. At some point, I realized that I was not performing the basic task of committing the changes. In short, the transactions were being executed but they were not being committed to the database—thus whatever changes I was making were being lost. So, the moral of the story is to make sure that you place the following code into your application:
DBConnection.commit();
The complete method is presented in Listing 8.
Listing 8
public void commitChanges(){
try {
DBConnection.commit();
System.out.println ("+++++++++++++++++++++");
System.out.println ("+ changes committed +");
System.out.println ("+++++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to commit changes: n" + excep);
System.exit(0);
}
}
When the changes are committed, you can inspect the database again to see that the insertions were actually performed—see Figure 12.
Figure 12
Executing a Query
Perhaps the most complicated code in this example is the code to build and execute a query. The code for this method is presented in Listing 9. There is a detailed description of this code in last month’s article. Rather than review the code here, please reference last month’s article.
In a nutshell, in this example I hard-coded the following statement:
"select * from tblCustomers"
When executed, this SQL query will return all the records in the tblCustomers table into the result set.
Listing 9
public void executeQuery(){
try {
DBResultSet= DBStatement.executeQuery (
"select * from tblCustomers");
System.out.println ("Query Successful");
while (DBResultSet.next()) {
System.out.println ("ID : " + DBResultSet.getInt("CustomerID"));
System.out.println ("Name : " + DBResultSet.getString("Name"));
System.out.println ();
}
} catch (Exception excep) {
System.out.println ("Unable to execute query: n" + excep);
System.exit(0);
}
}
When you execute the query, the application performs the select statement and returns that contents of the table, as can be seen in Figure 13.
Figure 13
Updating the Record
Now that you have inserted records into the database, one of the important tasks left to perform is to update a record that already exists. To accomplish this, you need to build an SQL transaction and then use the executeUpdate() method. Again, for simplicity you can hardcode an SQL statement that will change the name where the CustomerID = 2 to “Jane Jones”. The string looks like this:
"UPDATE tblCustomers SET [Name] = 'Jane Jones' WHERE CustomerID = 2"
The executeUpdate() is used in the following code:
DBStatement.executeUpdate (sqlRecord);
The code for this method is presented in Listing 10.
Listing 10
public void updateRecord(){
String sqlRecord =
"UPDATE tblCustomers SET [Name] = 'Jane Jones' WHERE CustomerID = 2";
try {
DBStatement.executeUpdate (sqlRecord);
System.out.println ("++++++++++++++++++");
System.out.println ("+ record updated +");
System.out.println ("++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to update record: n" + excep);
System.exit(0);
}
}
When the updates are executed, the output is shown in Figure 14.
Figure 14
And, of course, you can inspect the database to make sure that the change has been made and that Jane Smith is now Jane Doe—see Figure 15.
Figure 15
Closing the Connection
Finally, you need to close the connection. This is another point where I ran into some confusion. Just as when I failed to commit the transaction, failing to close the connection can lead to the same problem. In some cases, the changes to the database were not permanent until the connection was closed. This is accomplished with the single line of code:
DBConnection.close();
The complete code for this method is presented in Listing 11.
Listing 11
public void closeConnection(){
try {
DBConnection.close();
System.out.println ("+++++++++++++++++++++");
System.out.println ("+ connection closed +");
System.out.println ("+++++++++++++++++++++");
} catch (Exception excep) {
System.out.println ("Unable to close connection: n" + excep);
System.exit(0);
}
}
Conclusion
In this article, you further explored the basic concepts involved with connecting to a database from a Java application using JDBC. The concepts covered in this article provide a strong framework for just about any database application that you may want to develop.
Although this code works on an Microsoft Access database on a Microsoft Windows platform, there are changes required to connect to and use another database. This is true whether using a completely different database on Microsoft Windows or another database on a non-Windows platform.
Download the Code
Download the accompanying database here.
References
- Gilbert, Stephen, and Bill McCarty: Object-Oriented Design in Java. The Waite Group, 1998.
- Tyma, Paul, Gabriel Torok and Troy Downing:Java Primer Plus. The Waite Group, 1996.
- Jaworski, Jamie: Java 1.1 Developers Guide. Sams Publishing, 1997.
- www.javasoft.com
- http://msdn2.microsoft.com/en-us/library/cf131f6b.aspx
About the Author
Matt Weisfeld is a faculty member at Cuyahoga Community College (Tri-C) in Cleveland, Ohio. Matt is a member of the Information Technology department, teaching programming languages such as C++, Java, and C# .NET as well as various web technologies. Prior to joining Tri-C, Matt spent 20 years in the information technology industry gaining experience in software development, project management, business development, corporate training, and part-time teaching. Matt holds an MS in computer science and an MBA in project management. Besides The Object-Oriented Thought Process
, which is now in it’s second edition, Matt has published two other computer books, and more than a dozen articles in magazines and journals such as Dr. Dobb’s Journal, The C/C++ Users Journal, Software Development Magazine, Java Report, and the international journal Project Management. Matt has presented at conferences throughout the United States and Canada.
