JavaData & JavaConnecting to a Database with JDBC

Connecting to a Database with JDBC

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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 two previous articles in this series, I covered the topics of object signatures and object serialization. In this article, you will explore the ways to connect your Java applications to a database using Java Database Connectivity (JDBC). In this specific case, you will connect to a Microsoft Access database and utilize several aspects of JDBC functionality.

A JDBC Tutorial

For this installment, I am taking a somewhat different approach from the previous articles by using a tutorial format to illustrate the basic concepts of JDBC. The reason for this is that, to utilize JDBC functionality, you must interact directly with your operating system; 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 uses JDBC to connect directly to a database. As stated earlier, the application you develop here will present code to interact with a Microsoft Access database. In this installment, I will demonstrate the basic techniques necessary to get JDBC up and running and communicating with the database. This includes the code for the actual database connection, doing simple queries, and so forth. In next month’s article, you will explore JDBC even further by taking advantage of functionality such as table creation, record updates, and much more.

Creating the Database & DSN Connection

Before I launch into the actual application code, you first should create the database you will utilize in this tutorial. There are really no constraints to which database implementation you can use. Even though you use Microsoft Windows XP and Access in this example, you could choose any operating system and/or database that supports Java technology. In my class, students have used various platforms, including a Linux/MySQL solution. The primary difference when using another platform solution is in the way in which the driver and operating system connections are completed. The Java code should not vary except in the string that loads the driver—which you will see later on.

The application will work even is you don’t create the database yourself. You could, for example, simply place the Access database file on your system and let the code connect to it. However, you will create the Access database so you can see how the process works from start to finish. If you are using another database system, you can modify the instructions in the next section to conform to the system that you are using.

Creating the Microsoft Access Database

The design of your database is quite simple and represents a small baseball equipment operation. The database will contain four fields: ID, Name, Price, and Quantity, as illustrated in Table 1.

ID Name Price Quantity
1 Baseball $2.00 200
2 Bat $20.00 20
3 Glove $80.00 0
4 Catchers Glove $100.00 1

Table 1

All you need to do is create the Access database and enter the data. To do this, launch Microsoft Access and create a blank database called purchaseOrder.mdb. The initial screen is shown in Figure 1.

Figure 1

After you specify the name of the database as purchaseOrder.mdb, you then need to create the single table that you will utilize in this example. To accomplish this, you can use the Create table by entering data option as seen in Figure 2.

Figure 2

After you enter all of the data in the table you designed, your database should look like Figure 3.

Figure 3

Making the DSN Connection

Now that the database is complete, there is one more step you need to take to ensure the Java application you develop can make a connection to it. This step is highly dependent on the platform that you are using. In this example, you need to make a Data Source name (DSN) connection to the database. This is the mechanism that allows the application to connect to the database itself.

To accomplish this, you first need to bring up the Control Panel as indicated in Figure 4 and click on the Administrative Tools icon.

Figure 4

Once you get to the Administrative Tools dialog box, you then click on the Data Sources icon as seen in Figure 5.

Figure 5

Clicking on the Data Sources icon will bring you to the ODBC Data Source Administrator (see Figure 6) that will allow you to specify the name and the driver to be used by the application.

Figure 6

Once you are in the ODBC Data Source Administrator, click on the File DSN tab and click on the Add button (note that you won’t see the purchaseOrder System Data Source Name until you complete the add process). The Microsoft ODBC Access Setup dialog box appears as in Figure 7.

Figure 7

There are two things that must be completed in this dialog box:

  1. Create a Data Source Name.
  2. Click on the Select button and literally find the purchaseOrder.mdb file that you previously created, as shown in Figure 8.

Figure 8

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.

Developing the Java/JDBC Application

With the DSN defined, you now can begin to develop the code to interact with the purchaseOrder database. To accomplish this, I have created two classes for the project. The first is the application called JDBCTest, which contains the main method; and the second is called RunDB, which neatly provides all of the functionality that you are interested in for this simple example. In short, the RunDB class contains methods that encapsulate the behaviors that you want to demonstrate. In next month’s article, you will add functionality to this class by adding more behaviors.

As far as this example is concerned, the mission is simple: You want to cover the following topics:

  1. Loading the database driver
  2. Connecting to the DSN
  3. Building a statement
  4. Executing a query

You can start by developing the RunDB class. The structure of the class is very simple. You must import the java.sql.* package to allow you access to the package that contains the JDBC code. You also must provide two class attributes of type Connection and Statement. As the names imply, you will use these attributes to store the connection and statement information. Access to these attributes is required by more than one method; thus, you need to define them for the scope of the class the framework for the RunDB class that is shown in Listing 1.

//RunDB
import java.sql.*;

public class RunDB {

   Connection connection;
   Statement statement;

   ... behaviors

}

Listing 1

Loading the Database Driver

The first piece of functionality that you need to address is loading the JDBC driver for the specific platform by creating a method called loadDriver( ). In this case, you will load the ODBC/JDBC bridge by passing a string through the Class.forname( ) command as seen in Listing 2.

// Load the Driver

public void loadDriver() throws ClassNotFoundException{
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}

Listing 2

Note: All of the methods in this class will throw the ClassNotFoundException exception. At this point in the development of the application, it is easier to catch all of the exceptions at the application level and not deal with them separately in each method.

If successful, this class will load the driver provided by the Java implementation:

sun.jdbc.odbc.JdbcOdbcDriver.

Connecting to the DSN

Once the driver is loaded, the next order of business is to make the connection to the DSN, and thus the purchaseOrder database that you created earlier. You create a method called makeConnection( ) and use the DriverManager class and its getConnection( ) method, as seen in Listing 3.

// Make the Connection
public void makeConnection() throws SQLException {
   connection=DriverManager.getConnection("jdbc:odbc:purchaseOrder");
}

Listing 3

Note: The syntax specifies the name of the DSN, and not the purchaseOrder.mdb file itself. An error will be generated at this point if the DSN was not completed properly or if the database is not in its proper place.

If everything works, you are now ready to do some actual SQL code.

Building a Statement & Executing a Query

For this simple example, you will build the statement by hard-coding the SQL statement. In this case, you will issue an SQL select statement that will select all of the records from the transaction table.

SELECT * FROM Transaction

You create a method called executeQuery( ) and use the Statement class and its execute( ) method to initiate the SQL select statement that you want.

boolean foundResults =
   statement.execute("SELECT * FROM Transaction");

If the statement execution is successful, you retrieve the result set with the following code:

ResultSet set = statement.getResultSet();

With the result set captured, you then can output the results via a method you supply, called displayResults( ). Listing 4 shows the entire executeQuery( ) method.

// Execute the query
public void executeQuery() throws SQLException {

   boolean foundResults =
      statement.execute("SELECT * FROM Transaction");
   if(foundResults){
      ResultSet set = statement.getResultSet();
      if(set!=null) displayResults(set);
   }else {
      connection.close();
   }
}

Listing 4

The displayResults( ) method is simply a loop that iterates through the ResultSet. One of the interesting issues is how you can capture the metadata as well as the data itself. Remember that metadata is actually data that describes data. The metadata contains such information as the number of columns and the column names. You use the ResultSetMetaData class to capture the metadata. Then, you can use methods such as the ResultSetMetaData class’s getColumnCount( ) method.

You create a single string that you will use to append all information that you want to output. That string can be written to either the screen or a file. To make things more interesting, you can create a document with XML-like tags using the column name as the tag names. You will use this technique in future columns when you learn about XML parsing. An XML tag would look like this:

<Name>Baseball</Name>

Listing 5 shows the complete displayResults( ) method.

// Display the Results
void displayResults(ResultSet rs) throws SQLException {
   ResultSetMetaData metaData = rs.getMetaData();
   int columns=metaData.getColumnCount();
   String text="";

   while(rs.next()){
      for(int i=1;i<=columns;++i) {
         text+="<"+metaData.getColumnName(i)+">";
         text+=rs.getString(i);
         text+="</"+metaData.getColumnName(i)+">";
         text+="n";
      }
      text+="n";
   }
   System.out.println(text);
}

Listing 5

Considering the powerful functionality that you have covered, there really is not that much code involved. Although hard coding the SQL statement is obviously not extensible, you can use this technique to verify that you have successfully connected and interacted with the database. The complete code for this application is presented in Listing 6. When we run this application and successfully connect to the purchaseOrder database, the output in Figure 10 is produced. Note that it is in pseudo XML-like tags. In later installments, you will complete the generation process of complete XML documents.

// JDBC|Test – complete code
public class JDBCTest {

   public static void main(String args[]){

      RunDB runDB = new RunDB();

      try{
         runDB.loadDriver();
         runDB.makeConnection();
         runDB.buildStatement();
         runDB.executeQuery();
      }catch(Exception e){
         e.printStackTrace();
      }

   }
}

//RunDB
import java.sql.*;

public class RunDB {

   Connection connection;
   Statement statement;

   public void loadDriver() throws ClassNotFoundException{
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   }

   public void makeConnection() throws SQLException {
      connection=
         DriverManager.getConnection("jdbc:odbc:purchaseOrder")
   }

   public void buildStatement() throws SQLException {
      statement = connection.createStatement();
   }

   public void executeQuery() throws SQLException {

      boolean foundResults =
         statement.execute("SELECT * FROM Transaction");
      if(foundResults){
         ResultSet set = statement.getResultSet();
         if(set!=null) displayResults(set);
      }else {
         connection.close();
      }
   }

   void displayResults(ResultSet rs) throws SQLException {
      ResultSetMetaData metaData = rs.getMetaData();
      int columns=metaData.getColumnCount();
      String text="";

      while(rs.next()){
         for(int i=1;i<=columns;++i) {
            text+="<"+metaData.getColumnName(i)+">";
            text+=rs.getString(i);
             text+="</"+metaData.getColumnName(i)+">";
             text+="n";
         }
         text+="n";
      }

      System.out.println(text);

   }

}

Listing 6

Figure 9

Conclusion

In this article, you covered the basic concepts involved with connecting to a database from a Java application using JDBC. Incorporating a database into an application greatly expands your development options. Although this is a series of articles on object technologies, you may have noticed that you used a relational database, Microsoft Access. You will explore this issue in more detail next month. Also, as already noted, JDBC can be used in conjunction with any number of databases as long as you have the appropriate driver installed on your system.

Next month, you will write a complete Java application that will connect to the database and use various SQL commands to utilize the database, such as creating tables, updating records, and so forth. After implementing next month’s example, you will be able to create a complete database application using Java and JDBC.

References

Gilbert, Stephen, and Bill McCarty: Object-Oriented Design in Java. The Waite Group, 1998.

Meyers, Scott: Effective C++. Addison-Wesley, 1992.

Tyma, Paul, Gabriel Torok and Troy Downing: Java Primer Plus. The Waite Group, 1996.

Ambler, Scott: The Object Primer. Cambridge University Press, 1998.

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 its 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.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories