August 22, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Connecting to a Database with JDBC

  • December 16, 2005
  • By Matt Weisfeld
  • Send Email »
  • More Articles »

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.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel