Section 12.1. Using JDBC

   

---

12.1 Using JDBC

JDBC is made up of more than 20 classes in the java.sql package. JDBC is based on (and works similarly to) Microsoft's ODBC ( Open Database Connectivity). A chief difference is that it is implemented in Java, not C, and has the advantage of hindsight, which makes it simpler.

JDBC supports transaction management, precompiled statements, stored procedure calls, and use of metadata such as number of rows returned by a query.

Connecting to a database and using the result set works the same way whether you are writing a desktop application with a GUI or a Web application written with JavaServer Pages. There are several steps required to perform any interaction with a database:

1. Load the driver for this database

2. Establish a connection to this database

3. Create a Statement object

4. Execute the statement

5. Do something with the result set

6. Close the connection

We will cover these now in turn .

12.1.1 Loading the Driver

In ColdFusion, you can create a datasource in the ColdFusion Administrator before you start working with a database in your templates. Alternatively, you can connect to a database using the ability (introduced in ColdFusion 5) to create a datasource on the fly, directly inside your template code. In Java, there is no Administrator, so you must always specify how you are going to make a connection directly in your Java code.

A driver is a software program that talks to a particular database server. There are many different drivers available for each kind of database. In order to use a particular driver to connect to a database, you need to download or purchase the driver, install it on your class path , and load the class. The class will create an instance of the driver and register it with the JDBC driver manager.

There are two related concepts implicit here. The first is that you need to be able to load a class without knowing its name when you compile the code. The second is that you need to load a class without creating an instance of it, so that the class itself can do this when necessary. The Class class allows us to do both of these things.

The Class class is a direct descendant of java.lang.Object . Instances of the Class class represent classes and interfaces running in a Java program. The forName() method returns the Class object associated with the class (or interface) within the name you specify as a string. Supply this name to the method as a fully qualified class name (that is, include all of the package names ). The call to Class.forName() can throw a ClassNotFoundException , and it is a good idea to throw this exception and place a handler inside a catch block, like this:

 try {     Class.forName("packageName.driver"); } catch(ClassNotFoundException cfne) {     System.err.println("Cannot find driver: " + cfne); } 

When a regular Java program is compiled, the compiler identifies the classes that the program uses. It then creates instructions for the JVM to load those classes at execution time. JDBC allows a number of different drivers to be loaded, and the driver name is therefore not known at compile timeonly at execution time. How do you know what string to pass to the forName() method? This depends on which driver you use.

Most database driver vendors will distribute the driver in a .jar file. There are a number of ways to ensure that extensions can be found:

• You can place the .jar file in your class path, or in <JAVA_HOME>/jre/lib/ext . You can place any .jar files or other extensions to Java in this folder under the JDK, and they will be available to the system when you restart the JVM.

• You can also use the -classpath option for the compiler and JVM, adding the path name to the .jar file.

• Another option is to add the full path name of your driver library to the $CLASSPATH variable in your startup file (this is autoexec.bat for Windows).

A complete listing of available JDBC drivers can be found at Sun's Web site at http://industry.java.sun.com/products/jdbc. Currently there are around 200, and all major databases are supported. These drivers are divided into four types: Type I, II, III, and IV. Each of the four driver types have somewhat different personalities and uses, and we will look at these in turn.

12.1.1.1 TYPE I DRIVER: JDBC-ODBC BRIDGE

The Type I driver is actually a bridge between a Java application and an existing ODBC connection. This type of driver is the only one to ship with the JDK, which means that if you have a current ODBC database connection, you can start using it right away without having to fetch another driver. This driver was provided by Sun initially because (obviously) there were no drivers existing for JDBC when it was first introduced, and ODBC was a popular driver mechanism.

The advantage of the Type I driver is that it is easy and quick to use, because it ships with the JDK and it allows you to use any existing ODBC connections you may have. The disadvantage of this driver type is that it requires three tiers in which to operate : the Java application including the JDBC bridge, the ODBC connection, and the database itself. This means that it is a very slow connection and is not suitable for a production environment.

Connecting with a JDBC-ODBC Bridge

Let's briefly look at how we can connect to a database from a Java application using an existing ODBC datasource. I have an Access database on a local machine called JavaForCF.mdb . In the Windows Control Panel I have created an ODBC datasource called "javaforcf." Because the JDBC-ODBC bridge comes with the JDK, it is free, and I do not need to get any other package to start using this connection. I can simply load this driver using a statement like this:

 // load the class for this driver  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");       // create a new connection object, specifying       // the name of the datasource Connection con = DriverManager.getConnection("jdbc:odbc:javaforcf","", ""); 

This Access database has a table called Items . This table has columns called itemid, itemname, price, and so forth. Shortly, we will see a complete example of how to reference these columns to retrieve data.

12.1.1.2 TYPE II DRIVER: NATIVE API/JAVA DRIVER

This type of driver takes commands in JDBC and converts them to native calls specific to the DBMS. These conversions communicate directly with the database. The advantage of this type of driver is that it is faster than a Type I driver. However, it still requires specific files to be loaded on every client, which makes it generally unsuitable for production.

12.1.1.3 TYPE III DRIVER: JDBC-NET PURE JAVA DRIVER

The JDBC-Net Pure Java Driver takes commands in JDBC and converts them into a database-independent network protocol. This gets sent to a middleware server that translates the network protocol into a protocol specific to the DBMS in use. This in turn is sent to the specific database that is being accessed. The advantage of this system is its flexibility. It gives the developer a good deal of control over fine-grained aspects of the connection. The disadvantage is that because the translation must occur twice (once when the connection is established, and again when the results are returned), this is not the fastest possible way to connect, and it requires more work.

12.1.1.4 TYPE IV DRIVER: NATIVE PROTOCOL PURE JAVA DRIVER

The Type IV driver is written entirely in Java and communicates with the database directly. JDBC commands entered are converted into the database's native protocol on the fly, eliminating the need for a middle tier completely. These types of drivers are the fastest available and are generally preferable for production environments. Some can be downloaded for free and others are available for a fee. Prices for such drivers, which implement different feature sets and offer different speeds or support packages, can range from free (such as Microsoft's Type IV driver for SQL Server) to a couple thousand dollars for third-party drivers. Many excellent ones are free, however.

Another advantage to using Type IV drivers is that they are easy to deploy, because all you need is the vendor's Java class file in your class path. To makes things easier, the driver should include example applications that demonstrate the URL and class name required to load and use the JDBC driver in your applications.

Here is an example of connecting to a popular (and free) Type IV driver for the MySQL database:

 try {         // load driver         Class.forName("org.gjt.mm.mysql.Driver");         // make connection       con = DriverManager.getConnection("jdbc:mysql:// localhost/javaforcf","","");     }     catch (ClassNotFoundException cnfe) {         error = "Class not found: can't find driver";         throw new ClassNotFoundException(error);     }     catch (SQLException sqle) {         error = "SQL Exception: can't connect to DB";         throw new SQLException(error);     }     catch (Exception e) {         error = "Exception: unknown";         throw new Exception(error);     } } //end connect 

Note that writing this code in your database will simply load the driver and make a Connection object. Nothing else that you would notice happens. That is, no object is returned.

12.1.1.5 SPECIFYING THE DRIVER WITH PROPERTIES

You can also specify the driver to use in a properties file. You may recall our using these to store a favorite color parameter value in Chapter 11. You can set a system-level property by passing it in at the command line for your application, like this:

 java someProgram -Djdbc-driver=package.drivername 

You can retrieve the value for the driver like this:

 String myDriver =  System.getProperty("jdbc-driver"); 

Once you have retrieved the driver name, you can pass it to the Class.forName() call.

You can also load the properties from a properties file, which is read at runtime. Using the getProperty() method, we get a string back. Here is a fragment of how this works:

 ...  Properties p = new Properties(); p.load(new FileInputStream("JDBC.prop")); // get values for the keys in which we're interested String driver = p.getProperty("jdbc-driver"); String url = p.getProperty("JDBC-url"); Class.forName(driver); Connection con = DriverManager.getConnection(url); ... 

12.1.2 Establish a Connection

Once the driver has been loaded, you must obtain a connection. You do this by writing a URL that specifies the location of the database to connect to, including username and password, and then passing the string to the getConnection() method of the java.sql.DriverManager class, like this:

 Connection con;  String url ="jdbc:mysql://localhost/  databaseName","usernameHere","passwordHere"; con = DriverManager.getConnection(url); 

Note that there are two alternate ways to use the overloaded getConnection() method:

 getConnection(String url) 

which accepts only the location of the database, and

 getConnection(String url, Properties info) 

where info is a list of arbitrary string tag-value pairs that you can send in as connection arguments. This is similar to connectstring , introduced in ColdFusion 5.

The connection represents the established possibility of interacting with a database. Without a connection, there is nothing you can do with the database. So the Connection object is used to handle the input and output generated in your database interactions.

URLs that refer to databases you want to connect to using JDBC are prefixed with jdbc: as the protocol, in the place of, for instance, http: . You should specify a port number as well if that is in use. The format of the URL will depend on what driver you use. We have seen examples using Access and MySQL. Here are varying examples for PostgreSQL, Oracle, and Sybase:

[View full width]

 
 [View full width] 
 String hostName = "data.mycompany.com";  String database = "myDB"; String port = "5678";      // a Postgres URL with username and password String a PGURL = "jdbc:postgresql:" + hostName + ":" + port + "/" + database + ";  user=myUserName;password=myPassword";      // an Oracle URL String  anOracleURL = "jdbc:oracle:thin:@" + hostName + ":" + port + ":" + database;      // a Sybase URL String aSybaseURL = "jdbc:sybase"Tds:" + hostName + ":" + port + ":" + "?SERVICENAME=" +  database; 

The Connection class contains the methods required to control the database connection. You make the connection URL as a string and pass it to an instance of the Connection class to establish the connection.

Note

The getConnection() method throws an SQLException, which means you need to use a try/catch block around your DriverManager object business.

In ColdFusion, you automatically get information about the querying process returned to you in the form of the CFQuery object. This allows you to do things such as check the number of records your query returned by referencing #queryName. recordcount # . If you want such information in a Java application, you need to specify that explicitly. At this stage in your app, you create a DatabaseMetaData object, which is returned by the Connection object's getMetaData() method.

12.1.3 Create a Statement Object

Once you have a connection, you need to make the statement that you want to send off to the database for processing. The Statement object allows you to store the SQL statement and then execute it. A statement looks like this:

 Statement stmt = con.createStatement(); 

12.1.4 Execute the Statement

Once you have a Statement object, you need to send it to the database. Then write the SQL you want to send to the database as a string. Then call the executeQuery() method of the Statement object, passing the SQL string to it as a parameter. This operation will return a ResultSet object. The ResultSet contains the data retrieved by the execution of the query.

Here an important distinction is made. In ColdFusion, you can write whatever SQL statements you like, and the database will execute them just the same, as long as the statement is between <cfquery> tags. In Java, you use different methods to execute a query SELECT statement and an UPDATE statement.

SELECT statements use the executeQuery() method of the Statement object. If you want to update the information in the database (that is, write an UPDATE , INSERT , or DELETE statement), then you need to use a different method: the executeUpdate() method.

12.1.4.1 PREPARED STATEMENTS

The prepareStatement() method allow you to execute a precompiled query in an efficient manner. You use a prepareStatement() when you want to run a parameterized query; that is, a query where the bulk of the statement is always the same, but the statement has one or more variables that change the result set somewhat. The advantage is that Java can store all of the parts of the query that do not change and only rebuild the variable element of the statement. For instance, you use a prepareStatement() in Java when you would write this kind of statement in ColdFusion:

 <cfquery...>     select * from items    where itemid = #itemid#; </cfquery> 

In place of the ColdFusion itemid variable above, use a question mark ( ? ) in Java to specify a placeholder for a variable:

 String theSQL = "select * from items where itemid = ?;"; 

Each time you use a prepared statement, you need to replace the ? placeholder with the actual value you want to pass into the query. To do so, you write a setSomething() call to replace the marker with the value; the "something" is the type of value to be sent in.

In order to update data in the database, that is, to UPDATE or DELETE data, use an executeUpdate() method. For example, when you would write the following in ColdFusion:

 <cfquery...>     UPDATE items    SET title='something'    WHERE itemid=#itemid#; </cfquery> 

Write the SQL string as above, using the ? in place of the variable you want to pass to the statement. Then call the executeUpdate() method in place of the executeQuery() , as shown in the code example below.

Here is a method that gets called to retrieve the single item in the database matching the ID passed as a parameter. This is a very typical operation when you need to, for instance, view the detail of a product in an online store or view a single news article out of a list, and so forth.

[View full width]

 
 [View full width] 
 public ResultSet viewOneItem(String itemID) throws     SQLException, Exception {    ResultSet rs = null;    try {         PreparedStatement getArchived;  String s = new String("select itemName,   itemText, createdDate from items where    itemID = ?");  getArchived = con.prepareStatement(s);  getArchived.setInt(1, Integer.parseInt(itemID));   rs = getArchived.executeQuery();   // a second ? would have index of 2.   // SQL is 1-based like CF, not 0-based like Java  }     catch (SQLException sqle) {          sqle.printStackTrace();          error = "SQL Error:";          throw new SQLException(error);     }     catch (Exception e) {          e.printStackTrace();          error = "An unknown exception occured while executing query";          throw new Exception(error);     }     return rs;  }   // end viewOneItem 

Creating a PreparedStatement object is a very expensive process. So once a PreparedStatement is closed, a driver that is compliant with JDBC 3.0 (the most recent as of this writing) will place the statement in a local cache instead of discarding it. This way, the application can retrieve the statement already cached if it attempts to create the same query later. This obviously saves time- and resource-consuming authentication at the database and network traffic.

12.1.5 Process the Result Set

The ResultSet object returned by an executed query exposes a next () method which allows you to move through the results one row at a time. There are also various get methods that take a column index or column name as an argument and return the result as a variety of data types. As you might imagine, these methods include getInt() to return the column data as an int type, getString() to retrieve the value as a string, and so on.

Generally in ColdFusion one will get a result set from a query and then output it in a loop of some sort . The <cfoutput> tag can implicitly loop over a result set, and you can explicitly perform this using a <cfloop> and specifying a query attribute.

Note

There is a convention in SQL that result sets start with the index 1, not 0. This is of course contrary to Java convention for arrays, collections, and other data types, and as such can be difficult to remember.

In Java, a while loop is often used to iterate over a result set:

 // assume item object  item.connect();    // run some query ResultSet rs = item.doTheQuery(); while (rs.next()) {   // do something ... } 

You cannot simply start referencing individual values within a query as you can with ColdFusion. Without traversing the result, you are likely to get an error informing you that the result set is at the end.

12.1.5.1 HANDLING NULLS

It is quite rare to find a database that does not contain null values. Consider what we know about Java primitives. They do not have a null equivalent the way an object does. This is a problem if the implicit conversion performed by Java is incongruent with the meaning inhering in your data scheme. That is, what if an integer value was left null in your database? Because Java has no null equivalent for primitive types, the result is generally converted to 0. This could be discordant with the rest of your result set if the 0 has meaning or is used in a test.

The workaround for such issues is to use the wasNull() method to check whether the previous value in the result set was null. If it was, you can convert it to some other, meaningless value:

 int numbers rs.getInt("nums");  if (rs.wasNull()) {        // convert to safe value     numbers = -123 } 

12.1.5.2 HANDLING AUTO-GENERATED KEYS

In previous versions of JDBC, developers needed to execute a SELECT statement against the database if they needed to know the primary key they just generated after performing an insert. This is a common issue in ColdFusion applications: for example, you insert a new user into the Users table, then you need to get its newly generated primary key to make an appropriate insert into a UsersRoles join table. As of JDBC, Java developers do not have to return to the database for such an operation.

Note

Note that some of the JDBC 3.0 features discussed are optional and as such are implementation dependent. Your driver or your database may not support such features. I know it sounds awful , but you'll have to consult your specific documentation on such matters. (At least I won't suggest you "contact your network administrator" to get the answer!)

Here's how you do it; it's easy:

 // insert new row, then specify column to be returned as key  int rowcount = executeUpdate("INSERT into Users (username)         VALUES ('doodlehead'), "userID"); // now the userID for this user is available ResultSet rs = stmt.getGeneratedKeys(); 

12.1.6 Close the Connection

Once you have performed the database operation, then you must close the connection to the database to save unnecessary consumption of resources. However, if you plan on hitting the database again, then it is best to wait until you are completely finished before calling this method. This is because every interaction with the database requires an open connection, and opening a connection is a very expensive operation. You close a connection like this:

 connection.close() 

If you do not release the resources consumed by your connection explicitly using close() , the system will eventually release them automatically. Closing the statement will always close the accompanying ResultSet . A Statement , however, may be reused, so explicitly closing the result set will help conserve resources.

You can write an explicit disconnect() method that you can call in your application to help this process. It would look something like this:

 // assume prior definition of Connection con  public void disconnect() throws SQLException {      try {          if ( con != null )          {              //close connection              con.close();          }      }      catch (SQLException sqle) {          error = ("SQLException: unable to close connection");          throw new SQLException(error);      }  } // end disconnect() 

Now that you have the basic steps down, let's look at a complete example. Here we'll query the items listed in the fledgling WWWBooks bookstore database. So far, they have only a few Shakespeare plays. This listing will connect to a local Access database called javaforcf (which has an ODBC connection already established for it), create a statement to find all of the item names along with their IDs and prices, and display the results on the command line.

12.1.7 SelectTest.java

 package chp12;  // a simple query to connect to // an Access database with an ODBC // connection established on the local // system import java.sql.*; public class SelectTest {    // no-arg constructor public SelectTest() { } public void queryDatabase() {     Connection con = null;     ResultSet rs = null; try {     // connect     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");     con = DriverManager.getConnection("jdbc:odbc:javaforcf");     // a simple query     String queryString = "SELECT * FROM items;";     Statement stmt = con.createStatement();     rs = stmt.executeQuery(queryString);     System.out.println("ID  ItemName   Price");     System.out.println("__  ________   _____");   while(rs.next()) {     System.out.print(rs.getInt("itemID") + "  ");     System.out.print(rs.getString("itemname") + "\t");     System.out.print(rs.getString("itemprice"));     System.out.println();   } } catch (ClassNotFoundException cnfe) {      System.out.println("Class not found exception thrown: Could not locate DB driver"); } catch (SQLException sqle) {      System.out.println("SQL Exception: problem reported by DB" + sqle); } catch (Exception e) {      System.out.println("An unknown error occurred while connecting to DB"); } finally {     try {        if (rs != null) {           // close the result set           rs.close();       }        if (con != null) {          // close connection always          con.close();        }     }     catch (SQLException sqle) {        System.out.println("Unable to close DB connection");     }   } } /// eoq public static void main (String args[]) {      SelectTest st = new SelectTest();      st.queryDatabase();    } }//eof 

The result looks something like this:

 ID  ItemName   Price  __  ________   _____ 1  King Lear    15.00 2  Measure for Measure  24.50 3  Hamlet       29.95 4  Twelfth Night        23.50 

There is not a built-in, straightforward way to determine the number of rows returned by a result set. This is somewhat maddening for ColdFusion developers who find such an operation so easy ( #cfquery.recordcount# ). The best way to do this is to iterate through the result set to generate a count. You can set a variable and do a var++ with each iteration of the result set, and then output this number if need be (such as how many items a search query found).

---

Top