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 (); 

  } 

}