Previous Table of Contents Next


Execution Batching

The JDBC drivers allow a user to batch INSERTs and UPDATEs to the server, which reduces round trips to the server. OraclePreparedStatement.setExecuteBatch is used to set a prepared statement object’s batch size:

import java.sql.*; 
import oracle.jdbc.driver.*; 
import java.io.*; 
import java.util.*; 
 
Connection conn =  DriverManager.getConnection 
(“jdbc;oracle;oci8:@local”,”scott”,”tiger”); 
// this statement opens a file and converts it to an data input stream in 
order to read it 
// line by line 
DataInputStream inputStream = new DataInputStream(new 
FileInputStream(“inputfile.csv”)); 
 
PreparedStatement prepStatement =  conn.prepareStatement (“insert into 
customer values (?, ?, ?)”); 
 
//Change batch size for this statement to 10 
((OraclePreparedStatement)ps).setExecuteBatch (10); 
 
String inputLine; 
 
while ((inputLine = inputStream.readLine()) != null) 
   { 
    // the following line calls a method that takes a 
    // line read from the file and parses it into multiple 
    // fields - 
    Vector rowValue = parseRow(inputLine); 
 
   // The following 3 statements insert the file fields in the Vector 
   // created before into a row in the table Customer 
   prepStatement.setInt (1, rowValue.elementAt(0).toString()); 
   prepStatement.setInt (2, rowValue.elementAt(1).toString()); 
   prepStatement.setInt (3, rowValue.elementAt(2).toString()); 
 
  //The following statement queues this for later execution 
  // on every 10th statement, the inserts will be posted to the 
  // database. 
   prepStatement.executeUpdate (); 
 
 }    // End of While Loop 
 
// This statement sends any remaining inserts to the database 
((OraclePreparedStatement) prepStatement).sendBatch (); 
 
prepStatement.close(); 
} 

PL/SQL Stored Procedures

Oracle JDBC drivers support execution of PL/SQL stored procedures and anonymous blocks. The following example shows a Java class callPLSQL calling an Oracle stored procedure and a function:

/* 
 * This example show how to call Oracle store procedures 
 * or functions from java via JDBC 
 */ 
import java.sql.*; 
class callPLSQL 
{ 
  public static void main (String args []) 
       throws SQLException, ClassNotFoundException 
  { 
    // Load the driver 
    Class.forName (“oracle.jdbc.driver.OracleDriver”); 
 
    // Connect to the database with the user “scott”, the 
    // password “tiger” and the TNS connect string “LOCAL” 
    Connection conn = 
      DriverManager.getConnection (“jdbc;oracle;oci8:@LOCAL”, “scott”, 
      “tiger”); 
 
 
// Call a procedure with an IN/OUT prameter 
// The procedure name is oracle_in_out_proc 
    { 
      CallableStatement inoutProc = conn.prepareCall (“begin 
oracle_in_out_proc (?); end;”); 
 
//  The following line registers the out parameter as being 
//  of type Char in position 1 of the stored procedure 
      inoutProc.registerOutParameter (1, Types.CHAR); 
//  The following line assigns the literal “testing” to the 
//  first input variable of the proc (it is now being used 
//  for input and output 
      inoutProc.setString (1, “testing”); 
//  The next line executes the proc 
      inoutProc.execute (); 
 
      System.out.println (“Out argument is: “ + inoutProc.getString (1)); 
    } 
// Call to a function with an OUT parameter and a return code 
//  This is simliar  to the call above, but the first “?” is 
// assigned to the return value from the function. 
    { 
      CallableStatement funcout = conn.prepareCall (“begin ? := funcout 
      (?); end;”); 
      funcout.registerOutParameter (1, Types.CHAR); 
      funcout.registerOutParameter (2, Types.CHAR); 
      funcout.execute (); 
      System.out.println (“Return value is: “ + funcout.getString (1)); 
      System.out.println (“Out argument is: “ + funcout.getString (2)); 
    } 
  } 
} 

Database Metadata

The Oracle JDBC drivers support all database metadata entry points. A full description of the Metadata class can be found in the Java.Sql specification in JDK 1.1+.

Streaming Data

The JDBC support of streaming, either from database to client or client to database (for all stream conversions—binary, ASCII, and Unicode), allows an efficient means to BLOB.

/* 
 * This example shows how to stream data from the database 
 */ 
import java.sql.*; 
import java.io.*; 
 
class streamExample 
{ 
  public static void main (String args []) 
       throws SQLException, ClassNotFoundException, IOException 
  { 
    // Load the driver 
    Class.forName (“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”); 
 
    // It’s faster when you don’t commit automatically 
    conn.setAutoCommit (false); 
 
    // Create a Statement 
    Statement stmt = conn.createStatement (); 
 
    // 
    // You can insert the data in Example.txt into the database. 
    File inFile = new File (“Example.txt”); 
    InputStream inputStr = new FileInputStream (“Example.txt”); 
    PreparedStatement prepStmt = 
      conn.prepareStatement (“insert into example (the_key, long_value) 
values (?, ?)”); 
    // Insert the key into the first column and the contents of 
    // the file into the second column.  The last attribute 
    // gives the AsciiStream the length of the file 
    prepStmt.setAsciiStream (2, inputStr, (int)inFile.length ()); 
    prepStmt.setString (1, “FirstExample”); 
    prepStmt.execute (); 
  } 
} 


Previous Table of Contents Next