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.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

/**
 Before executing this demo, or compiling this demo with
 online checking, you must run the SQL script PrefetchDemo.sql.

 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.
 Note that SQLJ currently does not support JDBC batching.
 However, it is always possible to interoperate with JDBC
 when it is necessary to exploit batching.
**/

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
     {
       #sql { DELETE FROM PREFETCH_DEMO };
     }
     catch (SQLException exn)
     {
       System.out.println("A SQL exception occurred: "+exn);
       System.out.println("You probably forgot to run the PrefetchDemo.sql script");
       System.out.println("Run the script and then try again.");
       System.exit(1);
     }

     System.out.println(">>> Inserting data into the PREFETCH_DEMO table <<<");

     // We batch _all_ rows here, so there is only a single roundtrip.
     int numRows = 1000;
     insertRowsBatchedJDBC(numRows,conn);

     System.out.println(">>> Selecting data from the PREFETCH_DEMO table <<<");

     System.out.println("Default Row Prefetch value is:  " 
                        + conn.getDefaultRowPrefetch());

     // We show four row prefetch settings:
     //  1. every row fetched individually
     //  2. prefetching the default number of rows (10)
     //  4. prefetching the default number of rows (10)
     //  3. prefetching all of the rows at once
     //
     // each setting is run with JDBC and with 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);
     }

  }

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

    PrefetchDemoCur c;

    long start = System.currentTimeMillis();
    #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 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.");
  }

}