JDBC for Oracle - Herong's Tutorial Examples - v3.13, by Herong Yang
Performance of Inserting Rows with a ResultSet
This section describes how to measure the performance of inserting rows using a ResultSet object.
Since ResultSet objects can also be used to insert rows, I wrote the following Java sample program to measure the performance of inserting multiple rows using a ResultSet object:
/* OraclePerformanceResultSet.java * Copyright (c) HerongYang.com. All Rights Reserved. */ import java.sql.*; public class OraclePerformanceResultSet { public static void main(String [] args) { Connection con = null; try { oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource(); ds.setDriverType("thin"); ds.setServerName("localhost"); ds.setPortNumber(1521); ds.setDatabaseName("XE"); ds.setUser("Herong"); ds.setPassword("TopSecret"); con = ds.getConnection(); // Delete all rows from the table Statement sta = con.createStatement(); sta.executeUpdate("DELETE FROM Profile"); // Start the test int count = 10000; long t1 = System.currentTimeMillis(); // ResultSet to insert rows Statement rs = con.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); ResultSet res = rs.executeQuery("SELECT * FROM Profile"); res.moveToInsertRow(); java.util.Random r = new java.util.Random(); for (int i = 0; i < count; i++) { res.updateInt("ID", i+1); res.updateString("FirstName", Integer.toHexString(r.nextInt(9999))); res.updateString("LastName", Integer.toHexString(r.nextInt(999999))); res.insertRow(); } rs.close(); // End the test long t2 = System.currentTimeMillis(); System.out.println("ResultSet insert "+count +" rows with "+(t2 -t1) +" milliseconds"); con.close(); } catch (Exception e) { e.printStackTrace(); } } }
Unfortunately, this Oracle JDBC driver does not support updatable result sets. I am getting this execution error:
java.sql.SQLException: Invalid operation for read only resultset: moveToInsertRow at oracle.jdbc.driver.BaseResultSet.moveToInsertRow(...) at OraclePerformanceResultSet.main(...)
Table of Contents
JDBC (Java Database Connectivity) Introduction
Oracle Express Edition Installation on Windows
Oracle - Reference Implementation of JdbcRowSet
PreparedStatement with Parameters
PreparedStatement in Batch Mode
Performance of Inserting Rows with a PreparedStatement
Performance of Inserting Rows with a Regular Statement
►Performance of Inserting Rows with a ResultSet
Oracle - JBDC CallableStatement
Oracle CLOB (Character Large Object) - TEXT
Oracle BLOB (Binary Large Object) - BLOB