A Java Database Connectivity Average reader rating: 0
by Frederick F. Chew	News    2/2/2005 11:43:34 PM
From 1994 to early 1996, Internet technology was used strictly for advertising products and for the distribution of public information. Rarely was the network utilized for serious business applications such as inventory management, sales order management, and customer call tracking. Since most business data reside in relational databases, there was no easy way to access such data stores. The solution usually required a CGI script written in C, a UNIX shell language, Visual Basic, or Perl--which meant that the solution would be platform-specific. Furthermore, each vendor would provide a proprietary scheme to make native calls to his database product, which meant that migration from o­ne vendor's database to another could not be done without discarding and rewriting major chunks of code. The popularization of Java as an easy-to-use, object-oriented programming language for the Internet has broken many of the traditional barriers to portability. The introduction of the Java Database Connectivity (JDBC) package in 1996 was a major contribution to the "write o­nce, run anywhere" philosophy. Since then, JDBC has become an industry standard way to access SQL relational databases. A Java application that interacts with a particular database via JDBC does not need to be rewritten if the underlying database changes. For the developer who is already familiar with the Java language, learning JDBC is not difficult. The JDBC API, which is represented by the java.sql package in the Java Developer's Kit (JDK), is a major tool for building three-tier, client-server systems. The purpose of this article is to provide a tutorial o­n the nuts and bolts of JDBC. The Four Types of JDBC Drivers Sun Microsystems has defined four categories for JDBC drivers. Figure 1 illustrates the general architecture for each category. Type 1 Type 1 is the JDBC-to-ODBC bridge for connecting Java applications to relational databases that have Microsoft ODBC support. The bridge was initially developed by Intersolv (a leading ODBC driver vendor) and it is included as part of the JDK. This approach is regarded as an interim solution to database connectivity, but it may be the o­nly approach if JDBC drivers of the other types do not exist. Advantages Where ODBC drivers are already being used, this is an economical approach to connecting Java applications to the databases. Most contemporary databases have ODBC support, which makes it easy to build prototypes with Java. Disadvantages The JDBC-to-ODBC bridge has overhead as the JDBC driver manager needs to call the ODBC driver manager, which routes the call to the appropriate database. This approach requires ODBC native libraries to be installed o­n the client environment. Furthermore, ODBC data sources must be defined, which can be a time-consuming, forbidding task if there are thousands of clients. In some cases, it may not be permissible to tamper with a client's environment. Client code written as untrusted applets cannot use this approach. The bridge is a native C library (JdbcOdbc.dll) and the applet's security mechanism prevents access to any library modules outside its virtual machine. Since the bridge is an add-on for ODBC, it will work o­nly as well as the ODBC driver. If the ODBC driver has bugs, these bugs will remain with the use of the bridge. Type 2 Type 2 is a Java-to-Native-API driver, where a Java component makes calls to a native API module supplied by the vendor. This module would communicate directly with the vendor's database. Architecturally, type 2 is similar to type 1 in that a bridge approach is used. Advantages Generally speaking, this approach has less overhead than the JDBC-to-ODBC bridge, as the type 2 driver interfaces directly to the database. Disadvantages Like the type 1 approach, native binary code from the vendor must be installed o­n each client machine. If there are many such machines, the cost in licenses and installation time can be very high. As in the type 1 approach, these native libraries cannot be called by untrusted applets. This approach is not a purely Java solution and constraints you to what a database vendor provides. Type 3 The type 3 approach uses an all-Java client component that makes socket calls to a database-independent middleware server. The middleware server takes the client calls and translates them into the API specific to the driver (which is of type 1, 2, or 4). Type 3 is essentially a comprehensive, three-tier subsystem. A good example of the type 3 driver is Symantec's dbAnywhere (for Windows 95, Windows 98, and Windows NT), which is what the example code will be based upon. The dbAnywhere driver supports Microsoft SQL Server, Oracle, Sybase, Informix, and any ODBC-compliant database. With this driver, a client can connect to multiple databases from different vendors. Advantages This is the most flexible scheme for situations where a client needs to communicate with multiple databases from different vendors. The middleware server routes the calls to the appropriate destination. The client environment does not need native binary code modules to be installed. This approach can be used by untrusted applets. The driver would be downloaded along with the applet's bytecode files. The footprint of the type 3 driver is generally smaller than o­ne of the other types. Disadvantages Firms not associated with any particular database vendor produce most of the type 3 drivers. Since the solutions are highly flexible, they are generally expensive. Type 4 The type 4 driver is a pure Java module that communicates directly with the database engine through its native protocol. Depending o­n how the vendor wrote the driver, it may be deployable over the Internet. Advantages Type 4 drivers are highly efficient, as communication goes straight to the database. There are no native binary libraries or middleware servers. This is the solution for situations where performance is the main concern. Like type 3 drivers, the type 4 driver can be downloaded along with the applet's class files. There is nothing to preinstall o­n the client environment. Disadvantages Each type 4 driver is highly specific to a particular brand of database. Generally, it is purchased from the vendor who sells the database. Unlike type 3, a type 4 driver is not designed to work with databases of different vendors. The following URL from Javasoft provides information o­n the JDBC driver vendors and the drivers that are currently available: http://java.sun.com/products/jdbc/drivers.html The JDBC URL Whether you choose to use the JDBC-to-ODBC bridge from the JDK or a vendor's JDBC driver, you must initialize your driver before you can connect to the database of your choice. As part of this initialization process, you must supply a special JDBC URL to the JDBC driver manager. The URL has a general format that could also be influenced by special instructions from the vendor. In this section, we will look at two examples. The general format for the JDBC URL would be something like the following: jdbc: <sub-protocol>://<host_name>:<port_number>/<database name> If we use the JDBC-to-ODBC bridge, a specific example might be: jdbc:odbc://PLUTO.MRP.ACME/Part_Master The sub-protocol is odbc, the host name is PLUTO.MRP.ACME, and the database is Part_Master. The port number defaults to 8081. The dotted decimal IP address may be substituted for the host name. If we chose to use the Symantec dbAnywhere JDBC driver, the general format for the JDBC URL would be something like: jdbc: <sub-protocol>://<host_name>:<port_number>/<product_name>/<server_name>/<database_name> Given the vendor's instructions, if we plan to connect to a Microsoft SQL Server 6.5 database, the URL may look like: jdbc:dbaw://PLUTO.MRP.ACME:8889/SQL_Server/Inventory/Part_Master In this example, the port number is 8889 and we are specifying a Microsoft SQL Server database as the product name. The server name (the name registered as the database server) is called Inventory and the database we want to connect to is called Part_Master. In many situations, the database server name may simply be the host name of the machine. Before we use the URL to make connections, we will first need to load the desired bridge or driver. Loading the Driver To load the driver into the Java runtime environment, it is necessary to use the static Class.forName() method somewhere close to the top of the start-up method (static void main() for applications and init() for applets). Here is how the method is used with the JDBC-to-ODBC bridge: Class.forName("sun.jdbc.odbc.JdbcOdbcdriver"); Or, if we are using Symantec dbAnywhere: Class.forName("symantec.itools.db.jdbc.Driver"); To make sure that these drivers can be found, it is necessary to set the CLASSPATH environment variable properly. This variable is usually set through the installation program of the JDK or the vendor's product. The following illustrates the CLASSPATH variable being set: SET CLASSPATH=c:\java\lib\classes.zip;c:\dbAnywhere\lib\dbaw.zip;. File classes.zip contains the JDBC-to-ODBC driver and file dbaw.zip contains the dbAnywhere driver. If a Java applet were to load the dbAnywhere driver, it would do so via the HTML CODEBASE attribute of the APPLET tag. Creating Connection Objects With the JDBC driver loaded, we can proceed to create Connection objects for each database we wish to connect. We make use of the static getConnection() method of class DriverManager: String url = "jdbc:dbaw://PLUTO:8889/SQL_Server/PLUTO/Part_Master"; Connection connect = null; try { connect = DriverManager.getConnection(url, user_name, user_password); } catch(SQLException err) { System.out.println(err); System.exit(-1); } The user_name and user_password parameters are String objects and represent the user already registered with the desired database. Creating Statement Objects Next, we use our handle to the Connection object to create a Statement object. The Statement object uses either the executeQuery() or the executeUpdate() method to execute the SQL string statement. The completion of either method returns a ResultSet object containing the rows that satisfy the conditions specified in the SQL statement: Statement statement = null; ResultSet result_set = null; String sql_query = "select part_number, description, unit_price from raw_stock where unit_price > 3.00"; try { statement = connect.createStatement(); result_set = statement.executeQuery(sql_query); // This method is for SQL SELECT // process result_set } catch(SQLException err) { System.out.println(err); // perform any clean-up or alternate course of action } If the SQL statement involved an INSERT, UPDATE, or DELETE operation, we would use the Statement.executeUpdate() method. The executeUpdate() method is also appropriate for Data Definition Language (DDL) statements that perform life cycle operations o­n tables (CREATE TABLE, ALTER TABLE, or DROP TABLE). The return value of Statement.executeQuery() is never null, although the ResultSet object may be empty. Processing ResultSet Objects If the ResultSet object is not empty, we may iterate through the rows as follows: Statement statement = null; ResultSet result_set = null; String sql_query = "select part_number, description, unit_price from raw_stock where unit_price > 3.00"; try { statement = connect.createStatement(); result_set = statement.executeQuery(sql_query); // This method is for SQL SELECT while (result_set.next()) { // First call will position internal iterator to the first row System.out.print(result_set.getString(1)); System.out.print(", "); System.out.print(result_set.getString(2)); System.out.print(", "); System.out.println(result_set.getFloat(3)); } } catch(SQLException err) { System.out.println(err); System.exit(-1); } The statements in bold are additions to the previous code fragment. Using the ResultSet.next() method, we set the internal iterator of the object to point to the first row. Using a series of accessor methods that are named getXXX, we obtain the values for each of the columns: part_number, description, and unit_price. The first two columns are of type String, while unit_price is of type Float. The values for the columns are retrieved in the order that the column names appear in the SQL statement (part_number corresponds to 1, description to 2, and unit_price to 3). Instead of retrieving a column value using an integer index, we can also use a column name: System.out.println(result_set.getString("part_number")); All of the getXXX methods are overloaded to accept a single String parameter. At this point, we have all of the basics to do an input-output operation against a relational database using JDBC. The steps are as follows: (1) load the JDBC driver that will work with the databases, (2) obtain o­ne or more Connection objects to the databases, (3) create Statement objects that represent the SQL operations, and (4) obtain ResultSet objects that represent the results of the operations. In the following sections, we will look at how to increase the efficiency of these operations. Using Prepared Statement Objects In many circumstances, we would need to repeat an SQL operation. When we use Statement objects as in the previous example, the database engine is actually performing four steps o­n the SQL statement: (1) the statement is parsed, (2) the statement is compiled, (3) the SQL operation is planned and finally, (4) the operation is executed. In reality, steps 1 through 3 need to be done o­nly o­nce. This can be achieved using PreparedStatement objects. A PreparedStatement object is defined as a subclass of the Statement object. Rewriting the relevant portions of our previous example to use PreparedStatement objects, we would obtain the following: PreparedStatement statement = null; ResultSet result_set = null; String sql_query = "select part_number, description, unit_price from raw_stock where unit_price > 3.00"; try { statement = connect.prepareStatement(sql_query); result_set = statement.executeQuery(); // First execution of the SQL statement while (result_set.next()) { // Process result_set contents as shown earlier } // More statements result_set.close(); // Immediately relinquish the resources of the original ResultSet result_set = statement.executeQuery(); // Second execution of the SQL statement // Process new result_set contents } catch(SQLException err) { System.out.println(err); System.exit(-1); } The statements in boldface illustrate the additions or changes over the previous example. Note that in this example, the executeQuery() method of interface PreparedStatement does not take a String argument. This method o­nly executes the SQL operation that has been already parsed, compiled, and planned. Passing Parameters into the SQL Operation From the way things stand, it is a bit of a nuisance to create a PreparedStatement object every time a minor condition changes. Fortunately, JDBC allows us to specify wildcard parameters in our SQL string statement. Such parameters are indicated with the help of the question mark (?). Below is a modification of the previous example using the ? wildcard: PreparedStatement statement = null; ResultSet result_set = null; String sql_query = "select part_number, description, unit_price from raw_stock where unit_price > ?"; try { statement = connect.prepareStatement(sql_query); statement.setFloat(1, 0.30f); result_set = statement.executeQuery(); // First execution of the SQL statement while (result_set.next()) { // Process result_set contents as shown earlier } // More statements result_set.close(); // Immediately relinquish the resources of the original ResultSet statement.setFloat(1, 2.00f); result_set = statement.executeQuery(); // Second execution of the SQL statement // Process new result_set contents } catch(SQLException err) { System.out.println(err); System.exit(-1); } The boldface statements illustrate the additions and changes over the previous example. In this example, there is o­ne wildcard parameter. The parameter is of type float and hence, we would need to use the setFloat(int, float) method of interface PreparedStatement to specify a value before the execution. The PreparedStatement interface has a number of setXXX methods, where XXX indicates the name of the type. The first argument to a setXXX method is the relative position of the wildcard parameter and the second argument is the actual value to be substituted. When there are multiple wildcard parameters, we identify the particular wildcard by counting from left to right beginning with 1. Inquiring about the Database At this point, I will digress slightly to talk about database metadata. Metadata is data that describes the characteristics or properties of a particular database. The java.sql package includes an interface called DatabaseMetaData that allows a developer to find information such as the database product name, the database product version, the JDBC driver name being used, whether the database supports stored procedures, etc. This ability to make runtime inquiries about the database can be important to determining what action to take. The following code fragment is a small sample of the data that can be provided by a DatabaseMetaData object: try { DatabaseMetaData dbmetadata = connect.getMetaData(); // connect is a Connection object System.out.println("DB Product Name: "+dbmetadata.getDatabaseProductName()); System.out.println("DB Product Version: "+dbmetadata.getDatabaseProductVersion()); System.out.println("JDBC Driver Name: "+dbmetadata.getDriverName()); System.out.println("JDBC Driver Version: "+dbmetadata.getDriverVersion()); System.out.println("JDBC Major Version Number: "+dbmetadata.getDriverMajorVersion()); System.out.println("JDBC Minor Version Number: "+dbmetadata.getDriverMinorVersion()); System.out.println("Database User Name: "+dbmetadata.getUserName()); if (dbmetadata.supportsStoredProcedures()) System.out.println("Database supports stored procedures"); else System.out.println("Database does not support stored procedures"); if (dbmetadata.supportsOuterJoins()) System.out.println("Database supports outer joins"); else System.out.println("Database does not support outer joins"); if (dbmetadata.isReadOnly()) System.out.println("Database is read-only"); else System.out.println("Database is not read-only"); } catch(SQLException err) { System.out.println(err); System.exit(-1); }Using the Symantec dbAnywhere JDBC driver, the output of the above might look like: DB Product Name: Microsoft SQL Server DB Product Version: 6.50.255 JDBC Driver Name: Microsoft SQL Server JDBC Driver Version: 1.21 JDBC Major Version Number: 1 JDBC Minor Version Number: 21 Database User Name: dbo Database supports stored procedures Database supports outer joins Database is not read-only The java.sql package also has an interface called ResultSetMetaData that contains metadata about a ResultSet. The ResultSetMetaData interface would allow you to find the column names that constitute the rows, the SQL types of the columns, the precision of real number columns, and so forth. Using CallableStatement Objects If your database supports stored procedures, you can make use of the CallableStatement interface to invoke those procedures. In JDBC, a CallableStatement object is defined as a subclass of the PreparedStatement object. Below is a code fragment showing how a CallableStatement object is used: CallableStatement statement = null; ResultSet result_set = null; try { DatabaseMetaData dbmetadata = connect.getMetaData(); // connect is a Connection object if (dbmetadata.supportsStoredProcedures()) { statement = connect.prepareCall("{call sp_select_parts_by_unit_price(?)}"); System.out.println("First query..."); statement.setFloat(1, 0.30f); result_set = statement.executeQuery(); while (result_set.next()) { // Process result_set as shown earlier } System.out.println("\nSecond query..."); statement.setFloat(1, 2.00f); result_set.close(); result_set = statement.executeQuery(); while (result_set.next()) { // Process result_set as shown earlier } } // if } catch(SQLException err) { System.out.println(err); System.exit(-1); }  In this example, the important difference from the previous PreparedStatement example is the use of the prepareCall() method. This method is passed a string ("{call sp_select_parts_by_unit_price(?)}") that represents the name of the stored procedure already registered with the Microsoft SQL Server database. Stored procedures can also be written to accept any number of parameters. The values of such parameters would be passed via the setXXX methods. For this example, the stored procedure sp_select_parts_by_unit_price(?) is defined as follows: CREATE PROCEDURE sp_select_parts_by_unit_price @unit_price float = 0.0 AS select part_number, description, unit_price from raw_stock where unit_price > @unit_price GO The parameters for a stored procedure can be either IN or OUT parameters. Unless specified, a parameter is understood to be an IN parameter (such as the o­nly parameter in sp_select_parts_by_unit_price(?)). To declare an OUT parameter (which means that the data is flowing from the stored procedure to the application), we would need to use the registerOutParameter() method from the CallableStatement interface: statement = connect.prepareCall("{call sp_find_no_of_parts_by_price(?,?)}"); statement.setFloat(1, 0.30f); statement.registerOutParameter(2, java.sql.Types.INTEGER); statement.executeQuery(); int count = statement.getInt(2); System.out.println("Count = "+count); In the code fragment above, the stored procedure, sp_find_no_of_parts_by_price, takes an IN parameter (first position) and an OUT parameter (second position). We know that the second parameter is an OUT parameter and that its SQL type is INTEGER because that is how the procedure is defined in the MS SQL Server database: CREATE PROCEDURE sp_find_no_of_parts_by_price@unit_price FLOAT,@counter INTEGER OUTPUT AS select @counter=COUNT(*) from raw_stock where unit_price > @unit_price GO Cleaning Up the Resources The Java environment has a garbage collector that releases resources when they are no longer in use. When there are many input-output operations against a database, it is preferable to have the application relinquish the resources instead of waiting for the garbage collector. For each of the Connection, Statement, and ResultSet interfaces, there is a close() method for this purpose: result_set.close(); ... result_set = statement.executeQuery(); ... result_set.close(); // Close the ResultSet first ... statement.close(); // Close the Statement next ... connect.close(); // Close the Connection last Mapping SQL Types to Java Types At this point, we have the fundamentals to do database access with JDBC. I have provided some tables to map SQL data types to the Java data types and vice versa. This information is important as the developer will need to choose the correct setXXX or getXXX methods from the Statement and ResultSet interfaces. Tables 1 and 2 illustrate the mappings. Frederick F. Chew is the author of The Java/C++ Cross-Reference Handbook, published by Hewlett-Packard Professional Books/Prentice Hall, ISBN 0-13-848318-3. He is a software engineer with Interactive Transaction Services (iPIN), a San Francisco company that specializes in virtual payment services for the Internet.   References Chew, Frederick F., The Java/C++ Cross-Reference Handbook (Hewlett-Packard Professional Books/Prentice Hall, ISBN 0-13-848318-3, 1998). Friedrichs, Jurgen and Jubin, Henri, Java Thin-Client Programming for a Network Computing Environment (IBM Books/Prentice Hall, ISBN 0-13-011117-1, 1999). Hamilton, Graham, Cattell, Rick and Fisher, Maydene, JDBC Database Access with Java: A Tutorial and Annotated Reference (Addison-Wesley, ISBN 0-201-30995-5, 1997). Moss, Karl, Java Servlets (McGraw-Hill, ISBN 0-07-913779-2, 1998). Reese, George, Database Programming with JDBC and Java (O'Reilly and Associates, ISBN 1-56592-270-0, 1997). Interesting Web Sites http://java.sun.com http://devresource.hp.com/devresource/Topics/Java/Java.html http://www.javaworld.com http://www.symantec.com/dba/index.html http://microsoft.com/sql/
Rated 0 by other users. What do you think? [rate this article]