prefetch

One of the best features of an Oracle database is its ability to fetch more than one row at a time. This greatly increases performance by reducing the number of trips between the client program (Java) and the Oracle database. The following example shows a Java program that connects to an Oracle database and sets the prefetch to 100 for the connection. This results in the program requesting 100 rows at a time for any query using this connection in the program.

/* 

 * This example show how to call Oracle via JDBC 

 * and use Oracle’s prefetch functionality 

 */ 

import java.sql.*; 

// You need to import oracle.jdbc.driver.*  for the 

// Oracle specific extentions 

import oracle.jdbc.driver.*; 

 

 

class oraPrefetch 

{ 

  public static void main (String args []) 

       throws SQLException, ClassNotFoundException 

  { 

 

    // Load the Oracle JDBC driver 

    Class.forName (“oracle.jdbc.driver.OracleDriver”); 

  // Connection to retrieve the tables to be extracted 

    Connection Con = DriverManager.getConnection 

(“jdbc;oracle;oci8:@LOCAL”, “scott”, “tiger”); 

    Statement tableStmt = Con.createStatement (); 

  // ** Oracle specific extention to prefetch 100 rows at a time 

    ((OracleConnection)Con).setDefaultRowPrefetch (100); 

  // ** Get all tables in the database 

       ResultSet rset = tableStmt.executeQuery (“select table_name from 

       all_tables”); 

   } 

 } 

Define Query Columns

Defining columns allows a programmer to define the types of columns in a query, which saves a round trip to the database to get the column types. The following example demonstrates setting the columns of a query to String types:

/* 

 * This sample shows how to use the “define columns” 

 * extensions in Oracle. 

 */ 

import java.sql.*; 

 

// You need to import oracle.jdbc.driver.* in order to use the 

// API extensions. 

import oracle.jdbc.driver.*; 

 

class defineColType 

{ 

  public static void main (String args []) 

       throws SQLException, ClassNotFoundException 

  { 

    // Load the JDBC driver 

    Class.forName (“oracle.jdbc.driver.OracleDriver”); 

 

    // Connect to the “LOCAL” database as “scott” and password 

    // “tiger” 

    Connection conn = 

      DriverManager.getConnection (“jdbc;oracle;oci8:@LOCAL”, “scott”, 

      “tiger”); 

 

    Statement stmt = conn.createStatement (); 

 

    // Call DefineColumnType to specify that the column will be 

    // retrieved as a String to avoid NUMBER to String conversion 

    // A round-trip to the database to get the column type is 

    // also avoided.  The (OracleStatment)stmt portion of the following 

    // statement is Oracle specific and is needed to allow us to 

    // call the defineColumnType method. 

 

    ((OracleStatement)stmt).defineColumnType (1, Types.VARCHAR, 2); 

 

ResultSet rSet = stmt.executeQuery (“select empno from emp”); 

    while (rSet.next ()) 

    { 

      System.out.println (rSet.getString (1)); 

    } 

    stmt.close (); 

  } 

} 

Last Words on JDBC

Although JDBC is limited to Java, it is of primary importance to the next generation of applications, because it builds on Java’s strengths. JDBC (specifically Oracle’s JDBC Thin) enables a developer to create applications with the functionality of a Windows-based client/server application, while not having to deal with the configuration and support of the application user’s PC. It is also encouraging to see JDBC develop very robust capabilities and support over a very short period of time, as compared to ODBC or other data access methods. As in ODBC’s involvement with the success of client/server applications, JDBC will play a major role in the success of Thin Client/Internet-based applications being developed now and in the future.

Summary

Both ODBC and JDBC have their separate and distinct roles in facilitating the access of data from Oracle8. ODBC, after several years of enhancements and underlying architecture changes from Microsoft, remains the primary method of accessing Oracle data from the Microsoft Windows environments. JDBC, in contrast, has been created solely for use by programs written in Java and exists on almost every hardware and operating system combination in existence. In their respective areas, both ODBC and JDBC will be the primary means of accessing Oracle data for some time to come.