prefetchdemo.java

本套光盘提供了本书各章实例的所需的部分源程序文件以及数据库文件。读者 需要使用JDK 1.4(必需）版

import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OraclePreparedStatement;
import sqlj.runtime.ExecutionContext;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

/**
This demo shows how to set different prefetch values for
 SQLJ SELECT statements. It compares SQLJ and JDBC runs.
 Additionally, when creating the data in the PREFETCH_DEMO
 table, we  show how to batch INSERT statements in JDBC.
 SQLJ now also supports batching, and we show the source for
 the equivalent SQLJ batched insert as well.
这个实例显示了如何设置不用的预处理值,来提高数据库查询和操作的性能
**/

public class PrefetchDemo
{
  #sql static iterator PrefetchDemoCur (int n);

  public static void main(String[] args) throws SQLException
  {
     System.out.println("*** Start of Prefetch demo ***");

     Oracle.connect(PrefetchDemo.class,"connect.properties");
     OracleConnection conn =
        (OracleConnection) DefaultContext.getDefaultContext().getConnection();
     System.out.println("Connected.");

     try
     {
        try
        {
          #sql { DELETE FROM PREFETCH_DEMO };
        }
        catch (SQLException exn)
        {
          System.out.println("A SQL exception occurred: "+exn);
          System.out.println("Attempting to create the PREFETCH_DEMO table");

          try
          {
             #sql { DROP TABLE PREFETCH_DEMO };
          }
          catch (SQLException ex) { };
          try
          {
             #sql { CREATE TABLE PREFETCH_DEMO (n INTEGER) };
          }
          catch (SQLException ex)
          {
             System.out.println("Unable to create the PREFETCH_DEMO table: "+exn);
             System.exit(1);
          };
        }
   
        System.out.println(">>> Inserting data into the PREFETCH_DEMO table <<<");
       
 // 我们把所有的行都放在这里,所以只要运行一次就可以了
        int numRows = 1000;

        insertRowsBatchedJDBC(numRows, conn);
        // insertRowsBatchedSQLJ(numRows, conn);
   
        System.out.println(">>> Selecting data from the PREFETCH_DEMO table <<<");
   
       System.out.println("Default Row Prefetch value is:  "+ conn.getDefaultRowPrefetch());
   
        // 下面显示了行预取的设置:
        //  1. 非行都单独预取
        //  每十行预处理一次
        //  3. 一次性预取所有的行
        // 这些设置在JDBC和SQLJ上都可以运行
   
        int[] prefetch = new int[] { 1, conn.getDefaultRowPrefetch(),numRows / 10,  numRows };
   
        for (int i=0; i<prefetch.length; i++) 
        {
           selectRowsJDBC(prefetch[i], conn);
           selectRowsSQLJ(prefetch[i], conn, i);
        }
     }
     finally
     { 
       Oracle.close();
     }
  }

  public static void selectRowsSQLJ(int prefetch, OracleConnection conn, int i)                     throws SQLException
  {
    System.out.print("SQLJ: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    PrefetchDemoCur c;
    long start = System.currentTimeMillis();

     switch (i % 5) {
      case 0:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 1:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 2:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 3:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      default: #sql c = { SELECT n FROM PREFETCH_DEMO }; 
    }
    while (c.next()) { };
    c.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }
  public static void selectRowsJDBC(int prefetch, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("JDBC: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    long start = System.currentTimeMillis();
    PreparedStatement pstmt = conn.prepareStatement("SELECT n FROM PREFETCH_DEMO");
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) { };
    rs.close();
    pstmt.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedSQLJ(int n, OracleConnection conn)                     throws SQLException
  {
    System.out.print("SQLJ BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();

    ExecutionContext ec = new ExecutionContext();
    ec.setBatching(true);
    ec.setBatchLimit(n);

    for (int i=1; i<=n; i++)
    {
      #sql [ec] { INSERT INTO PREFETCH_DEMO VALUES(:i) };
    }

    ec.executeBatch();
    long delta = System.currentTimeMillis() - start;
    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedJDBC(int n, OracleConnection conn)                     throws SQLException
  {
    System.out.print("JDBC BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();
    int curExecuteBatch = conn.getDefaultExecuteBatch();
    conn.setDefaultExecuteBatch(n);

    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO PREFETCH_DEMO VALUES(?)");
    for (int i=1; i<=n; i++)
    {
      pstmt.setInt(1,i);
      pstmt.execute();
    }
    ((OraclePreparedStatement)pstmt).sendBatch();
    pstmt.close();
    conn.setDefaultExecuteBatch(curExecuteBatch);

    long delta = System.currentTimeMillis() - start;
    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }
}