Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
This section provides sample applications for performance enhancement features such as update batching:
The sample applications for Oracle-specific performance enhancements are located in the following directory on the product CD:
[Oracle Home]/jdbc/demo/samples/oci8/basic-samples
The standard update batching sample is located in the jdbc20-samples
directory.
This sample shows how to use standard update batching as specified by JDBC 2.0. For more information, see "Standard Update Batching".
For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".
/** * A simple sample to demonstrate standard JDBC 2.0 update batching. */ import java.sql.*; public class BatchUpdates { public static void main(String[] args) { Connection conn = null; Statement stmt = null; PreparedStatement pstmt = null; ResultSet rset = null; int i = 0; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); conn = DriverManager.getConnection( "jdbc:oracle:oci8:@", "scott", "tiger"); stmt = conn.createStatement(); try { stmt.execute( "create table mytest_table (col1 number, col2 varchar2(20))"); } catch (Exception e1) {} // // Insert in a batch. // pstmt = conn.prepareStatement("insert into mytest_table values (?, ?)"); pstmt.setInt(1, 1); pstmt.setString(2, "row 1"); pstmt.addBatch(); pstmt.setInt(1, 2); pstmt.setString(2, "row 2"); pstmt.addBatch(); pstmt.executeBatch(); // // Select and print results. // rset = stmt.executeQuery("select * from mytest_table"); while (rset.next()) { System.out.println(rset.getInt(1) + ", " + rset.getString(2)); } } catch (Exception e) { e.printStackTrace(); } finally { if (stmt != null) { try { stmt.execute("drop table mytest_table"); } catch (Exception e) {} try { stmt.close(); } catch (Exception e) {} } if (pstmt != null) { try { pstmt.close(); } catch (Exception e) {} } if (conn != null) { try { conn.close(); } catch (Exception e) {} } } } }
This sample shows how to use Oracle update batching, with the batch being executed implicitly when the batch value (the number of statements to collect before sending them to the database) is reached.
For information about Oracle update batching, see "Oracle Update Batching".
For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".
/* * This sample shows how to use the batching extensions. * In this example, we set the defaultBatch value from the * connection object. This affects all statements created from * this connection. * It is possible to set the batch value individually for each * statement. The API to use on the statement object is setExecuteBatch(). */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import oracle.jdbc.driver.* in order to use the // API extensions. import oracle.jdbc.driver.*; class SetExecuteBatch { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // Default batch value set to 2 for all prepared statements belonging // to this connection. ((OracleConnection)conn).setDefaultExecuteBatch (2); PreparedStatement ps = conn.prepareStatement ("insert into dept values (?, ?, ?)"); ps.setInt (1, 12); ps.setString (2, "Oracle"); ps.setString (3, "USA"); // No data is sent to the database by this call to executeUpdate System.out.println ("Number of rows updated so far: " + ps.executeUpdate ()); ps.setInt (1, 11); ps.setString (2, "Applications"); ps.setString (3, "Indonesia"); // The number of batch calls to executeUpdate is now equal to the // batch value of 2. The data is now sent to the database and // both rows are inserted in a single roundtrip. int rows = ps.executeUpdate (); System.out.println ("Number of rows updated now: " + rows); ps.close (); conn.close(); } }
This sample shows how to use Oracle update batching, with the batch being executed explicitly with a sendBatch()
call.
For information about Oracle update batching, see "Oracle Update Batching".
For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".
/* * This sample shows how to use the batching extensions. * In this example, we demonstrate the use of the "sendBatch" API. * This allows the user to actually execute a set of batched * execute commands. */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import oracle.jdbc.driver.* in order to use the // API extensions. import oracle.jdbc.driver.*; class SendBatch { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); Statement stmt = conn.createStatement (); // Default batch value set to 50 for all prepared statements belonging // to this connection. ((OracleConnection)conn).setDefaultExecuteBatch (50); PreparedStatement ps = conn.prepareStatement ("insert into dept values (?, ?, ?)"); ps.setInt (1, 32); ps.setString (2, "Oracle"); ps.setString (3, "USA"); // this execute does not actually happen at this point System.out.println (ps.executeUpdate ()); ps.setInt (1, 33); ps.setString (2, "Applications"); ps.setString (3, "Indonesia"); // this execute does not actually happen at this point int rows = ps.executeUpdate (); System.out.println ("Number of rows updated before calling sendBatch: " + rows); // Execution of both previously batched executes will happen // at this point. The number of rows updated will be // returned by sendBatch. rows = ((OraclePreparedStatement)ps).sendBatch (); System.out.println ("Number of rows updated by calling sendBatch: " + rows); ps.close (); conn.close (); } }
This section demonstrates how to use Oracle row prefetching-functionality, setting the row prefetch value in the connection object and thereby affecting every statement produced from that connection.
Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.
For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".
/* * This sample shows how to use the Oracle performance extensions * for row-prefetching. This allows the driver to fetch multiple * rows in one round-trip, saving unecessary round-trips to the database. * * This example shows how to set the rowPrefetch for the connection object, * which will be used for all statements created from this connection. * Please see RowPrefetch_statement.java for examples of how to set * the rowPrefetch for statements individually. */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import oacle.jdbc.driver in order to use the oracle extensions. import oracle.jdbc.driver.*; class RowPrefetch_connection { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // set the RowPrefetch value from the Connection object // This sets the rowPrefetch for *all* statements belonging // to this connection. // The rowPrefetch value can be overriden for specific statements by // using the setRowPrefetch API on the statement object. Please look // at RowPrefetch_statement.java for an example. // Please note that any statements created *before* the connection // rowPrefetch was set, will use the default rowPrefetch. ((OracleConnection)conn).setDefaultRowPrefetch (30); Statement stmt = conn.createStatement (); // Check to verify statement rowPrefetch value is 30. int row_prefetch = ((OracleStatement)stmt).getRowPrefetch (); System.out.println ("The RowPrefetch for the statement is: " + row_prefetch + "\n"); ResultSet rset = stmt.executeQuery ("select ename from emp"); while(rset.next ()) { System.out.println (rset.getString (1)); } rset.close (); stmt.close (); conn.close (); } }
This section demonstrates how to use Oracle row prefetching functionality, setting the row prefetch value in a particular statement object to override the value in the connection object producing the statement.
Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.
For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".
/* * This sample shows how to use the Oracle performance extensions * for row-prefetching. This allows the driver to fetch multiple * rows in one round-trip, saving unecessary round-trips to the database. * This example shows how to set the rowPrefetch for individual * statements. */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import oracle.jdbc.driver in order to use the // Oracle extensions import oracle.jdbc.driver.*; class RowPrefetch_statement { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // get the value of the default row prefetch from the connection object int default_row_prefetch = ((OracleConnection)conn).getDefaultRowPrefetch (); System.out.println ("The Default RowPrefetch for the connection is: " + default_row_prefetch); Statement stmt = conn.createStatement (); // set the RowPrefetch value from the statement object // This sets the rowPrefetch only for this particular statement. // All other statements will use the default RowPrefetch from the // connection. ((OracleStatement)stmt).setRowPrefetch (30); // Check to verify statement rowPrefetch value is 30. int row_prefetch = ((OracleStatement)stmt).getRowPrefetch (); System.out.println ("The RowPrefetch for the statement is: " + row_prefetch + "\n"); ResultSet rset = stmt.executeQuery ("select ename from emp"); while(rset.next ()) { System.out.println (rset.getString (1)); } rset.close (); stmt.close (); stmt.close (); } }
This sample shows how to use Oracle extensions to predefine result set column types to reduce round trips to the database for a query.
For information about column type definitions, see "Defining Column Types".
/* * This sample shows how to use the "define" extensions. * The define extensions allow the user to specify the types * under which to retrieve column data in a query. * * This saves round-trips to the database (otherwise necessary to * gather information regarding the types in the select-list) and * conversions from native types to the types under which the user * will get the data. * * This can also be used to avoid streaming of long columns, by defining * them as CHAR or VARCHAR types. */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import oracle.jdbc.driver.* in order to use the // API extensions. import oracle.jdbc.driver.*; class DefineColumnType { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); Statement stmt = conn.createStatement (); // Call DefineColumnType to specify that the column will be // retrieved as a String to avoid conversion from NUMBER to String // on the client side. This also avoids a round-trip to the // database to get the column type. // // There are 2 defineColumnType API. We use the one with 3 arguments. // The 3rd argument allows us to specify the maximum length // of the String. The values obtained for this column will // not exceed this length. ((OracleStatement)stmt).defineColumnType (1, Types.VARCHAR, 7); ResultSet rset = stmt.executeQuery ("select empno from emp"); while (rset.next ()) { System.out.println (rset.getString (1)); } // Close the resultSet rset.close(); // Close the statement stmt.close (); // Close the connection conn.close(); } }