HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML
 

"Strategies for Writing Java Stored
 ORACLE Databases"
Volume: 4 Issue: 12, p.20

	

Listing 1: 

//Title:      Utility Stored Procedures 
//Version: 
//Copyright:  Copyright (c) 1999 
//Author:     Derek C. Ashmore 
//Company:    Delta Vortex Technologies 
//Description: Utilities which are callable 
//           within the Oracle JVM 
  

import java.lang.*; 
import java.sql.*; 
import oracle.sql.*; 
import oracle.jdbc.driver.*; 
  

public class OracleProcs { 
  

  private static OracleConnection 
  currentConnection = null; 
  

  private static OracleConnection get- 
  Connection(){ 
      return currentConnection; 
  } 
  

  private static void setConnection( 
     OracleConnection oracleConnection) 
            throws SQLException { 
   currentConnection = oracleConnection; 
   currentConnection.setDefaultRow- 
   Prefetch(100); 
   currentConnection.setDefaultExe- 
    cuteBatch(20); 
    currentConnection.setAutoCommit 
    (false); 
} 
  

private static void setDefaultConnection() 
           throws SQLException { 
     setConnection((OracleConnection) 
        new OracleDriver().defaultCon- 
        nection()); 
} 
  

public static double getUniqueIdenti-fier( 
String tableName, 
String columnName, 
String sequenceName) 
  throws SQLException { 
  double  uniqueID = 0; 
  boolean idFound = false; 
  OracleResultSet nextvalResults = null; 
  OracleResultSet existResults = null; 
  

  if (currentConnection == null) 
      setDefaultConnection(); 
  

  // Select of sequence value 
  String SQLStatement = "select " + 
      sequenceName.toUpperCase() + 
      ".nextval from dual"; 
  OraclePreparedStatement nextvalStmt = 
      (OraclePreparedStatement) 
    currentConnection.prepareState- 
    ment(SQLStatement); 
  

  // Verification of non-existence 
  SQLStatement = "select count(*) from " 
      + tableName.toUpperCase() + 
      " where " + columnName.toUpper- 
      Case() + 
      " = ? and rownum <= 1"; 
      OraclePreparedStatement existStmt = 
         (OraclePreparedStatement) 
        currentConnection.prepareState- 
        ment(SQLStatement); 
  

  // Find next unused value 
  while (!idFound)   { 
     nextvalResults = (OracleResultSet) 
       nextvalStmt.executeQuery(); 
     nextvalResults.next(); 
       uniqueID = nextvalResults.get 
       Double(1); 
          existStmt.setDouble(1, 
          uniqueID); 
          existResults = (OracleResult 
          Set) 
             existStmt.executeQuery(); 
     existResults.next(); 
       if (existResults.getInt(1) == 0) 
           idFound = true; 
     nextvalResults.close(); 
     existResults.close(); 
      } 
  

      nextvalStmt.close(); 
      existStmt.close(); 
  

      return  uniqueID; 
  } 
  

} 
  

Listing 2: 

import java.lang.*; 
import java.sql.*; 
import oracle.sql.*; 
import oracle.jdbc.driver.*; 
  

public class SqlTester { 
  

private static Connection nullConnect; 
  

private static Connection getConnection 
     (int arraySize) { 
    try { 
    Connection conn = new 
     OracleDriver().defaultConnection(); 
    ((OracleConnection)conn).setDefault- 
    RowPrefetch 
      (arraySize); 
    return conn; 
    } 
    catch (SQLException sqlError) { 
    System.out.println("This didn't 
    work!"); 
    return nullConnect; 
    } 
} 
private static Connection getConnection() { 
   return getConnection(1); 
} 
  

public static int performReadTest(int arraySize) { 
  String objectName; 
  int objectCount = 0; 
  

  try { 
  Connection DBConnect = 
  getConnection(arraySize); 
  Statement sqlStmt = DBConnect.create- 
  Statement(); 
  String sql = "select object_name from 
  dba_objects"; 
  ResultSet dbResults = sqlStmt.execute- 
  Query(sql); 
  while (dbResults.next())  { 
    objectName = dbResults.getString(1); 
    objectCount = objectCount + 1; 
  } 
  dbResults.close(); 
  sqlStmt.close(); 
  } 
  catch (SQLException sqlError) { 
  System.out.println("We errored!"); 
  } 
  return objectCount; 
} 
public static int performReadTest() 
{ 
  return performReadTest(1); 
} 
} 
  

Listing 3: 

create or replace function readtestplsql return number is 
cursor OBJ_CSR is 
   select object_name from dba_objects; 
H_OBJ_NAME  dba_objects.object_name%type; 
NBR_OBJECTS number := 0; 
begin 
open OBJ_CSR; 
fetch OBJ_CSR into H_OBJ_NAME; 
while (OBJ_CSR%found) loop 
    fetch OBJ_CSR into H_OBJ_NAME; 
    NBR_OBJECTS := NBR_OBJECTS + 1; 
end loop; 
return NBR_OBJECTS; 
end; 
/ 

Listing 4: 

SQL> select readtestplsql from dual; 
  

READTESTPLSQL 
------------- 
        11668 
  

Elapsed: 00:00:03.40 
  

SQL> select readtestjava(1) from dual; 
  

READTESTJAVA(1) 
--------------- 
          11668 
  

Elapsed: 00:00:10.29 
  

SQL> select readtestjava(10) from dual; 
  

READTESTJAVA(10) 
---------------- 
           11668 
  

Elapsed: 00:00:02.78 
  

SQL> select readtestjava(50) from dual; 
  

READTESTJAVA(50) 
---------------- 
           11668 
  

Elapsed: 00:00:02.22 
  

SQL> select readtestjava(100) from dual; 
  

READTESTJAVA(100) 
----------------- 
            11668 
  

Elapsed: 00:00:02.20 
  

SQL> select readtestjava(200) from dual; 
  

READTESTJAVA(200) 
----------------- 
            11668 
  

Elapsed: 00:00:02.14 
  

  public class Tester { 
  

public static void counter(int inputValue) { 
    int count = 0; 
    while (count++ <= inputValue); 
    } 
  

public static int timeCounter(int inputValue) { 
   int timeElapsed; 
   long timeMarking = System.current- 
   TimeMillis(); 
   counter(inputValue); 
   timeElapsed = (int) (System.current- 
   TimeMillis() 
      - timeMarking); 
   return timeElapsed; 
   } 
  

public static void main (String argv[]) { 
    Integer tempInteger = new 
    Integer(argv[0]); 
    int nbrIterations = 
    tempInteger.intValue(); 
    long timeMarking = System.current- 
    TimeMillis(); 
  

    counter(nbrIterations); 
    System.out.println (System.current- 
    TimeMillis() 
       - timeMarking); 
    } 
} 
  SQL> select timecounterjava(10000) from dual; 
TIMECOUNTERJAVA(10000) 
---------------------- 
                     4 
SQL> select timecounterjava(100000) from dual; 
TIMECOUNTERJAVA(100000) 
----------------------- 
                     42 
SQL> select timecounterjava(1000000) from dual; 
TIMECOUNTERJAVA(1000000) 
------------------------ 
                     420 
  
  

  Java (Outside Oracle JVM) with JIT enabled 
->java derek 10000 
1 
->java derek 100000 
6 
->java derek 1000000 
59 
Java (Outside Oracle JVM) with JIT disabled 
->java derek 10000 
2 
->java derek 100000 
27 
->java derek 1000000 
267 

  
  
      
 

All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: [email protected]

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.