JDBC Drivers

Though you'll use JDBC extensively to access your database, it doesn't provide built-in support for any particular database vendor. Instead, it assumes that an appropriate JDBC driver implementation has been loaded. In the first example, you loaded this driver class with Class.forName() . In the J2EE example, you just specified the driver class name in your data source configuration and the server loaded it for you.

Out of the box, OC4J includes drivers for the following databases listed in Table 5-1.

Table 5-1: JDBC Drivers Provided with OC4J

Database

Driver Class Name

Oracle

oracle.jdbc.driver.OracleDriver

Description: Includes a pure-Java driver implementation that connects directly to the database, as well as an Oracle Call Interface (OCI) driver that uses JNI to connect to a native Oracle client installation. When using the OCI driver, ensure that the ORACLE_HOME, LD_LIBRARY_PATH , and TNS_ADMIN environment variables are set properly so that the driver can find your Oracle client installation.

MS SQL Server

com. merant . datadirect .jdbc.sqlserver.SQLServerDriver

Description: Pure-Java driver

IBM DB/2

com.merant.datadirect.jdbc.db2.DB2Driver

Description: Pure-Java driver

Sybase

com.merant.datadirect.jdbc.sybase.SybaseDriver

Description: Pure-Java driver

If you need to connect to another type of database, you'll need to get a JDBC driver implementation for that database from the vendor and put it on your OC4J classpath. Usually, this means downloading a JAR and dropping it in OC4J_HOME/j2ee/home/lib .

At the time of this writing, JDBC drivers for PostgreSQL and MySQL were available at the following URLs listed in Table 5-2:

Table 5-2: Additional JDBC Drivers

Database

Driver Class Name

URL

PostgreSQL

org.postgresql.Driver

http://jdbc.postgresql.org/download.html

MySQL

com.mysql.jdbc.Driver

www.mysql.com/downloads/api-jdbc.html

Choosing the Right Driver

As noted in the previous section, Oracle provides two types of drivers, OCI and thin, for client-side development. It also provides drivers intended for use when working inside the Oracle database, but we won't cover those here).

The Oracle thin driver is a pure Java implementation, which makes it a Type 4 JDBC driver. The JDBC thin driver uses Java TCP sockets to connect directly to the database by emulating the Oracle SQL*Net Protocol. Thus, the thin driver requires a TCP/IP listener up and running at the database server, which is usually the case anyway. No software is required on the client-side (the machine on which the driver is operated), other than a suitable JRE. As a result, the thin driver is platform-independent and can thus be used both in a traditional 3-tier setup, and even in a 2- tier setup without an Oracle client, such as a Java applet accessed through a browser. If your environment has web-based clients , then the thin driver is the correct choice.

The Oracle OCI driver (often called the thick driver) provides a part-Java, part-native JDBC implementation, which makes it a Type 2 JDBC driver. It interacts with the Oracle data-base through the OCI by invoking C language routines in the Oracle OCI libraries. As a result, Oracle client software has to be installed for each client connecting with Oracle through the OCI driver. This makes the OCI driver platform-specific, and it's supported only on those platforms for which the Oracle client software is supported. The OCI driver supports all installed Oracle Net Services adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

Note 

With Oracle 10g, there's now an option called "Instant Client," which significantly reduces the number of files it requires on the client machine.

If your JDBC client application is going to run on a machine where the Oracle client is installed, then you should consider the JDBC OCI driver because it will often provide better performance compared to the JDBC thin driver. If you need to use a networking protocol other than TCP/IP (a rare situation), then you must use the OCI driver.

If you do have a choice of driver, then it's a good idea to run simple benchmarks tests to verify that the driver will give you the best performance for your application. This is usually quite a straightforward process. You can create a simple database table (called measurements , in the following example) to store your timing data. Then, having established your connection and so on (not shown here), your JDBC code may contain something like the following:

 //execute a series of SQL statements against the standard EMP, DEPT tables     pstmt1 = conn.prepareStatement("insert into dept (deptno, dname, loc) "                                    + "values (?, ?, ?)");     pstmt2 = conn.prepareStatement("update emp set deptno = ? "                                    + "where ename in ('SMITH','SCOTT')");     pstmt3 = conn.prepareStatement("select count(*) from dept"); // Insert your timing data into the measurements table     pstmt4 = conn.prepareStatement("insert into measurements (name, x, y) "                                    + "values (?, ?, ?)");     // execute the tests, for varying numbers of executions     try {       for (int i = 0; i < Integer.parseInt(args[0]); i++) {         execute(1);         execute(10);         execute(20);         execute(30);         execute(40);         execute(50);       }     } finally {       pstmt1.close();       pstmt2.close();       pstmt3.close();       pstmt4.close();       conn.close();     }   } 
Note 

Of course, in your own benchmarks you'll want to incorporate tests that match what your application is doing. For example, handling LOBs is one of those things that can change performance between the drivers.

The following execute() method takes the number of executions to make for an argument. It goes into a for loop and executes the specified number of statements. To measure the time it takes to execute, it uses the current system time, obtained with a call to System.getCurrentTimeMillis() , as follows :

 public static void execute(int count) throws SQLException {   ResultSet rs;   int cnt;   // start the measurements   long timestamp = System.currentTimeMillis();   long time;   // execute a series of statements   for (int i = 0; i < count; i++) {     // create a new department     pstmt1.setInt(1, 50 + i);     pstmt1.setString(2, "DEPT #" + i);     pstmt1.setString(3, "CITY #" + i);     pstmt1.executeUpdate();     // move Smith and Scott to the new department     pstmt2.setInt(1, 50 + i);     pstmt2.executeUpdate();     // select the number of departments     rs = pstmt3.executeQuery();     cnt = rs.next() ? rs.getInt(1) : 0;     rs.close(); } 

Finally, the results of the measurements are logged in to the database, as shown here:

 time = System.currentTimeMillis() -timestamp;     pstmt4.setString(1, "JDBC with OCI");     pstmt4.setInt(2, count);     pstmt4.setLong(3, time);     pstmt4.executeUpdate();     conn.commit();   } } 

You would repeat the tests a number of times for a specific driver in order to gain an average value, and then repeat the tests for your second choice of driver. You can then gather the results as follows:

 SQL> select x, oci, thin, thin-oci, trunc((thin-oci)/oci*100, 1) pct   2    from (   3      select x,   4        trunc(avg (decode(name, 'JDBC with OCI', y, null)),1) oci,   5        trunc(avg (decode(name, 'JDBC with thin', y, null)),1) thin   6        from measurements   7       group by x   8 ); 


Oracle Application Server 10g. J2EE Deployment and Administration
Oracle Application Server 10g: J2EE Deployment and Administration
ISBN: 1590592352
EAN: 2147483647
Year: 2004
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net