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

Using More Advanced JDBC Features

  • January 26, 2006
  • By Matt Weisfeld
  • Send Email »
  • More Articles »

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





Page 2 of 4



Comment and Contribute

 


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

 

 


Sitemap | Contact Us

Rocket Fuel