TestSqlOperationsOnOracleAdvanced.java

// 
// Lapetus Ltd Java Class.  Copyright (c) Lapetus Systems Ltd, 2009, 2010. 
// ----------------------------------------------------------------------- 
// This is the intellectual property of Lapetus Systems Ltd, Artemis, Greece. 
// -------------------------------------------------------------------------- 
// www.lapetus-ltd.com, www.lapetus.com.gr, www.lapetus.eu 
// ------------------------------------------------------- 
// $LastChangedRevision: 1203 $ 
// $LastChangedDate:: 2010-11-28 09:35:04#$ 
// ---------------------------------------- 
// 
 
import com.lapetus_ltd._2009.xml.types.*; 
import com.lapetus_ltd.api.TLptsMainDatabase; 
import com.lapetus_ltd.api.common.TLptsCryptoUtil; 
import com.lapetus_ltd.api.common.logger.ILptsLogListener; 
import com.lapetus_ltd.api.common.logger.TLptsLog; 
import com.lapetus_ltd.api.common.logger.TLptsLogger; 
import com.lapetus_ltd.api.db.control.*; 
import com.lapetus_ltd.api.db.utils.TLptsDriverLoader; 
import com.lapetus_ltd.api.db.xml.types.*; 
import junit.framework.TestCase; 
 
import java.sql.*; 
import java.util.*; 
 
 
// ###################################################################################################### 
// ####   Test code for the dbJAPI build process. This code will stop the build process on ERROR     #### 
// ###################################################################################################### 
 
// 
// Class Description : This is one of the test operations modules. 
// 
// This module tests OPERATIONS (=,<,>,<>,etc) against many different data types. 
// This class creates tables, inserts data based on criteria, selects the data with operations, updates with criteria, 
// deletes with criteria and verifies the data integrity. 
// 
// The tests in here are run against an Oracle database, but there are other similar modules for Postgres and MySQL. 
// 
// The test starts by connecting {@link #connectToDB} and creating a main table {@link #createAllTableStatements} through 
// {@link ConnectionStatementListener} called "OracleTable" and 10 other tables that only have two columns (ID and TYPE) 
// {@link #createIntegerTableStatement}. 
// 
// After the creation of the tables, the INSERT statement is executed {@link #insertDataIntoAllTables} and data is inserted 
// into the main table. At this point maps are generated with the inserted values for later selection and comparison. 
// 
// On completion of the INSERT {@link RowSetListener#processNewRowSetRows} new SELECT statements are generated 
// {@link #selectColumnStatement} for every column of the main table "OracleTable". 
// After all the selects have been created, data is inserted into all the tables from the selected statements 
// {@link #insertDataInMultipleTables}. 
// 
// Thereafter multiple new dynamic statements are generated {@link #dynamicStatement} through {@link RowSetListener#processNewRowSetRows}. 
// These dynamic statements are for the separate type tables (10 of them), which are declared as primary statements to the main table. 
// As these dynamic statements are being executed, each row is checked against the maps used to generate the main table. 
// {@link RowSetListener#rowEvent}. 
//  
// After completion of the verification for every dynamic statement, the update procedure commences {@link #updateData}. 
// This updates 4 columns using many operations from multiple columns with criteria. 
// The updated data is then selected and the expected data is verified {@link #selectAfterUpdateStatement} and {@link RowSetListener#rowEvent}. 
// 
// The data is then deleted {@link #deleteData} with multiple criteria, as with the update. It is then verified. 
// 
// This whole procedure uses most of the capabilities of dbJAPI, and controls the whole situation with the 3 listeners 
// for Connections, Statements and RowSets. The listeners are utilised in various ways. by many separate threads, 
// with no synchronisation required by the application (dbJAPI takes care of all that). 
// 
 
 
public class TestSqlOperationsOnOracleAdvanced extends TestCase 
{ 
  private String connectionCreateTableId = ""; 
  private String selectAfterUpdateStatementId = ""; 
  private String connectionId = ""; 
  private String selectStatementId = ""; 
 
  private List<Integer> idList = new LinkedList<Integer>(); 
  private Map<Integer, Integer> integerMap = new HashMap<Integer, Integer>(); 
  private Map<Integer, String> varcharacterMap = new HashMap<Integer, String>(); 
  private Map<Integer, Timestamp> timestampMap = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Float> floatMap = new HashMap<Integer, Float>(); 
  private Map<Integer, Short> smallintegerMap = new HashMap<Integer, Short>(); 
  private Map<Integer, Byte> charMap = new HashMap<Integer, Byte>(); 
 
  private List<Integer> idListSelected = new LinkedList<Integer>(); 
  private Map<Integer, Integer> integerMapSelected = new HashMap<Integer, Integer>(); 
  private Map<Integer, String> varcharacterMapSelected = new HashMap<Integer, String>(); 
  private Map<Integer, Timestamp> timestampMapSelected = new HashMap<Integer, Timestamp>(); 
  private Map<Integer, Float> floatMapSelected = new HashMap<Integer, Float>(); 
  private Map<Integer, Short> smallintegerMapSelected = new HashMap<Integer, Short>(); 
  private Map<Integer, Byte> charMapSelected = new HashMap<Integer, Byte>(); 
 
  private String idStatementId = ""; 
  private String integerStatementId = ""; 
  private String varcharStatementId = ""; 
  private String timestampStatementId = ""; 
  private String floatStatementId = ""; 
  private String smallIntegerStatementId = ""; 
  private String charStatementId = ""; 
 
  private String primaryStatementId = ""; 
  private String subStatementId = ""; 
 
  private int insertions = 0; 
  private int createdTables = 0; 
  private int dynamicIndex = 0; 
  private boolean isSubstitute = false; 
 
  private int NO_OF_INSERTIONS = 200; 
  private int MULTIPLE_LIMIT = 206; 
 
  private List<String> columnNames = new LinkedList<String>(); 
  private List<String> tableNames = new LinkedList<String>(); 
 
 
  static public void main(String[] args) 
  { 
    TestSqlOperationsOnOracleAdvanced sqlOperationsOnAdvanced = new TestSqlOperationsOnOracleAdvanced(); 
    sqlOperationsOnAdvanced.testOperations(); 
  } 
 
  public void testOperations() 
  { 
    // required so that the API can function correctly. 
    TLptsMainDatabase.init(); 
 
    // register a listener for the logging system. 
    TLptsLogger.addListener(new ILptsLogListener() 
    { 
      public void newLogGenerated(TLptsLog log) 
      { 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR)) 
        { 
          cleanUp(false); 
          fail("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
          System.exit(0); 
        } 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING)) 
          System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE)) 
          System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText()); 
      } 
    }); 
 
    // register a listener for the connections 
    TLptsFactoryConnection.addListener(new ConnectionStatementListener()); 
 
    // register a listener for the rowsets produced 
    TLptsFactoryRowSet.addListener(new RowSetListener()); 
 
    // register a listener for the statements 
    TLptsFactoryStatement.addListener(new StatementListener()); 
    columnAndTableNames(); 
 
    TLptsDBConnectionType connectionType; 
 
    //Connect To Oracle 
    connectionType = connectToDB(); 
    if (connectionType != null) 
    { 
      connectionCreateTableId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    String driverName = "", dbName = "", userName = "", password = ""; 
    ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle 
 
    TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
    connectionType.setTitle("ORACLE 11 DB"); 
 
    dbName = bundle.getString("Testing.database.oracle.testdb1"); 
    userName = bundle.getString("Testing.database.oracle.user2"); 
    password = bundle.getString("Testing.database.oracle.password2"); 
    driverName = "oracle.jdbc.pool.OracleDataSource"; 
 
    XLptsDriverType driver = TLptsDriverLoader.getDriverTypeByClassName(driverName); 
 
    if (driver == null) 
    { 
      System.out.println("Could not find driver for class " + driverName); 
      return null; 
    } 
 
    TLptsDriverType driverType = new TLptsDriverType(driver); 
    // changing values that are default in the driver.loader.xml file. 
    // the DB is now set correctly and will filter through to the Datasource interface execution 
    driverType.setValueForInterfaceFunctionParameter("setDatabaseName", "Database Name", dbName); 
 
    connectionType.setDriverType(driverType); 
 
    if (userName == null || password == null) 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(), 
                                                                                 driverType.getGuestPassword())); 
    else 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password)); 
 
    return connectionType; 
  } 
 
 
  private void createIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create integerTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("integerTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType integerColumn = new TLptsDBCreateStatementColumnType(); 
    integerColumn.setColumn("integerColumn"); 
    TLptsDBCreateStatementColumnSqlType integerSqlType = new TLptsDBCreateStatementColumnSqlType(integerColumn); 
    integerSqlType.setSqlType(java.sql.Types.INTEGER); 
    integerColumn.setSqlType(integerSqlType); 
    table.addColumn(integerColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createVarcharTableStatement(connection); 
  } 
 
  private void createVarcharTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create varcharTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("varcharTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType varcharColumn = new TLptsDBCreateStatementColumnType(); 
    varcharColumn.setColumn("varcharColumn"); 
    TLptsDBCreateStatementColumnSqlType varcharSqlType = new TLptsDBCreateStatementColumnSqlType(varcharColumn); 
    varcharSqlType.setSqlType(java.sql.Types.VARCHAR); 
    varcharColumn.setSqlType(varcharSqlType); 
    TLptsDBCreateStatementColumnSizeType varcharSize = new TLptsDBCreateStatementColumnSizeType(varcharColumn); 
    varcharSize.setSize1("50"); 
    varcharColumn.setSizes(varcharSize); 
    table.addColumn(varcharColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createTimestampTableStatement(connection); 
  } 
 
  private void createTimestampTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create timestampTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("timestampTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType timestampColumn = new TLptsDBCreateStatementColumnType(); 
    timestampColumn.setColumn("timestampColumn"); 
    TLptsDBCreateStatementColumnSqlType timestampSqlType = new TLptsDBCreateStatementColumnSqlType(timestampColumn); 
    timestampSqlType.setSqlType(java.sql.Types.TIMESTAMP); 
    timestampColumn.setSqlType(timestampSqlType); 
    table.addColumn(timestampColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createFloatTableStatement(connection); 
  } 
 
  private void createFloatTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create floatTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("floatTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType floatColumn = new TLptsDBCreateStatementColumnType(); 
    floatColumn.setColumn("floatColumn"); 
    TLptsDBCreateStatementColumnSqlType floatColumnSqlType = new TLptsDBCreateStatementColumnSqlType(floatColumn); 
    floatColumnSqlType.setSqlType(java.sql.Types.FLOAT); 
    floatColumn.setSqlType(floatColumnSqlType); 
    table.addColumn(floatColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createSmallIntegerTableStatement(connection); 
  } 
 
  private void createSmallIntegerTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create smallIntegerTable"); 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("smallIntegerTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType smallintegerColumn = new TLptsDBCreateStatementColumnType(); 
    smallintegerColumn.setColumn("smallintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType smallintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(smallintegerColumn); 
    smallintegerColumnSqlType.setSqlType(java.sql.Types.SMALLINT); 
    smallintegerColumn.setSqlType(smallintegerColumnSqlType); 
 
    table.addColumn(smallintegerColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createCharTableStatement(connection); 
  } 
 
  private void createCharTableStatement(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create charTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("charTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType charColumn = new TLptsDBCreateStatementColumnType(); 
    charColumn.setColumn("charColumn"); 
    TLptsDBCreateStatementColumnSqlType charColumnSqlType = new TLptsDBCreateStatementColumnSqlType(charColumn); 
    charColumnSqlType.setSqlType(java.sql.Types.CHAR); 
    charColumn.setSqlType(charColumnSqlType); 
 
    table.addColumn(charColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
  } 
 
  private void createAllTableStatements(TLptsConnection connection) 
  { 
    TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); 
    createStatement.setTitle("Create OracleTable"); 
 
    TLptsDBCreateStatementRootType rootCreateType = new TLptsDBCreateStatementRootType(); 
    rootCreateType.setTitle("root"); 
 
    TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); 
    table.setTable("OracleTable"); 
    rootCreateType.setTable(table); 
 
    TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); 
    temporary.setTemporary(false); 
    table.setTemporary(temporary); 
 
    TLptsDBCreateStatementColumnType idColumn = new TLptsDBCreateStatementColumnType(); 
    idColumn.setColumn("id"); 
    TLptsDBCreateStatementColumnSqlType sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); 
    sqlType.setSqlType(java.sql.Types.INTEGER); 
    idColumn.setSqlType(sqlType); 
    TLptsDBCreateColumnConstraintType columnConstraintType = new TLptsDBCreateColumnConstraintType(); 
    columnConstraintType.setColumnId(idColumn.getId()); 
    columnConstraintType.setConstraintType(XLptsDBCreateColumnConstraintParameterType.PRIMARY_KEY); 
    idColumn.addConstraint(columnConstraintType); 
    table.addColumn(idColumn); 
 
    TLptsDBCreateStatementColumnType integerColumn = new TLptsDBCreateStatementColumnType(); 
    integerColumn.setColumn("integerColumn"); 
    TLptsDBCreateStatementColumnSqlType integerSqlType = new TLptsDBCreateStatementColumnSqlType(integerColumn); 
    integerSqlType.setSqlType(java.sql.Types.INTEGER); 
    integerColumn.setSqlType(integerSqlType); 
    table.addColumn(integerColumn); 
 
    TLptsDBCreateStatementColumnType varcharColumn = new TLptsDBCreateStatementColumnType(); 
    varcharColumn.setColumn("varcharColumn"); 
    TLptsDBCreateStatementColumnSqlType varcharSqlType = new TLptsDBCreateStatementColumnSqlType(varcharColumn); 
    varcharSqlType.setSqlType(java.sql.Types.VARCHAR); 
    varcharColumn.setSqlType(varcharSqlType); 
    TLptsDBCreateStatementColumnSizeType varcharSize = new TLptsDBCreateStatementColumnSizeType(varcharColumn); 
    varcharSize.setSize1("50"); 
    varcharColumn.setSizes(varcharSize); 
    table.addColumn(varcharColumn); 
 
    TLptsDBCreateStatementColumnType timestampColumn = new TLptsDBCreateStatementColumnType(); 
    timestampColumn.setColumn("timestampColumn"); 
    TLptsDBCreateStatementColumnSqlType timestampSqlType = new TLptsDBCreateStatementColumnSqlType(timestampColumn); 
    timestampSqlType.setSqlType(java.sql.Types.TIMESTAMP); 
    timestampColumn.setSqlType(timestampSqlType); 
    table.addColumn(timestampColumn); 
 
    TLptsDBCreateStatementColumnType floatColumn = new TLptsDBCreateStatementColumnType(); 
    floatColumn.setColumn("floatColumn"); 
    TLptsDBCreateStatementColumnSqlType floatColumnSqlType = new TLptsDBCreateStatementColumnSqlType(floatColumn); 
    floatColumnSqlType.setSqlType(java.sql.Types.FLOAT); 
    floatColumn.setSqlType(floatColumnSqlType); 
    table.addColumn(floatColumn); 
 
    TLptsDBCreateStatementColumnType smallintegerColumn = new TLptsDBCreateStatementColumnType(); 
    smallintegerColumn.setColumn("smallintegerColumn"); 
    TLptsDBCreateStatementColumnSqlType smallintegerColumnSqlType = new TLptsDBCreateStatementColumnSqlType(smallintegerColumn); 
    smallintegerColumnSqlType.setSqlType(java.sql.Types.SMALLINT); 
    smallintegerColumn.setSqlType(smallintegerColumnSqlType); 
    table.addColumn(smallintegerColumn); 
 
    TLptsDBCreateStatementColumnType charColumn = new TLptsDBCreateStatementColumnType(); 
    charColumn.setColumn("charColumn"); 
    TLptsDBCreateStatementColumnSqlType charColumnSqlType = new TLptsDBCreateStatementColumnSqlType(charColumn); 
    charColumnSqlType.setSqlType(java.sql.Types.CHAR); 
    charColumn.setSqlType(charColumnSqlType); 
    table.addColumn(charColumn); 
 
    createStatement.setCreateRootItem(rootCreateType); 
 
    System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); 
 
    TLptsFactoryStatement.createNewStatement(connection, createStatement); 
    createIntegerTableStatement(connection); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  I N S E R T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void insertDataIntoAllTables(TLptsConnection connection) 
  { 
    for (int i = 0; i < NO_OF_INSERTIONS; i++) 
    { 
      idList.add(i); 
      integerMap.put(i, new Integer("1354" + i)); 
      varcharacterMap.put(i, "my var character" + i + "column"); 
      timestampMap.put(i, new Timestamp(110, i % 12, i % 28, i % 24, i % 60, i % 33, i)); 
      floatMap.put(i, new Float("158" + i + "12.25")); 
      smallintegerMap.put(i, new Short("" + i)); 
      charMap.put(i, (byte) (i % 2)); 
      insertStatement(connection, i); 
    } 
  } 
 
  private void insertStatement(TLptsConnection connection, Integer index) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("OracleTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnId = statement.getColumnItemByName("OracleTable", "id"); 
    if (columnId != null) 
      columnId.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, idList.get(index).toString())); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("OracleTable", "integerColumn"); 
    if (columnInteger != null) 
      columnInteger.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, integerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnVarchar = statement.getColumnItemByName("OracleTable", "varcharColumn"); 
    if (columnVarchar != null) 
      columnVarchar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, varcharacterMap.get(index))); 
 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("OracleTable", "timestampColumn"); 
    if (columnTimestamp != null) 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, timestampMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnFloat = statement.getColumnItemByName("OracleTable", "floatColumn"); 
    if (columnFloat != null) 
      columnFloat.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, floatMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("OracleTable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, smallintegerMap.get(index).toString())); 
 
    XLptsDBStatementColumnType columnChar = statement.getColumnItemByName("OracleTable", "charColumn"); 
    if (columnChar != null) 
      columnChar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, charMap.get(index).toString())); 
 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void selectColumnStatement(TLptsConnection connection) 
  { 
    createStatement(connection, "id"); 
    createStatement(connection, "integerColumn"); 
    createStatement(connection, "varcharColumn"); 
    createStatement(connection, "timestampColumn"); 
    createStatement(connection, "floatColumn"); 
    createStatement(connection, "smallintegerColumn"); 
    createStatement(connection, "charColumn"); 
  } 
 
  private void createStatement(TLptsConnection connection, String columnName) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    // set the table and columns for selecting 
    XLptsDBStatementTableType table = statement.getTableItemByName("OracleTable"); 
    table.setSelected(true); 
    statement.getColumnItemByName("OracleTable", columnName).setSelected(true); 
 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); 
    if (columnName.equals("id")) 
      idStatementId = statement.getId(); 
    else if (columnName.equals("integerColumn")) 
      integerStatementId = statement.getId(); 
    else if (columnName.equals("varcharColumn")) 
      varcharStatementId = statement.getId(); 
    else if (columnName.equals("timestampColumn")) 
      timestampStatementId = statement.getId(); 
    else if (columnName.equals("floatColumn")) 
      floatStatementId = statement.getId(); 
    else if (columnName.equals("smallintegerColumn")) 
      smallIntegerStatementId = statement.getId(); 
    else if (columnName.equals("charColumn")) 
      charStatementId = statement.getId(); 
    // Set the name of the statement to the name of the column so that it makes sense 
    statement.setTitle(columnName); 
    statement.setExecutable(false); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
 
  private void insertDataInMultipleTables(TLptsConnection connection) 
  { 
    setMultipleInserts(connection, "integerTable", "integerColumn", integerStatementId); 
    setMultipleInserts(connection, "varcharTable", "varcharColumn", varcharStatementId); 
    setMultipleInserts(connection, "timestampTable", "timestampColumn", timestampStatementId); 
    setMultipleInserts(connection, "floatTable", "floatColumn", floatStatementId); 
    setMultipleInserts(connection, "smallIntegerTable", "smallintegerColumn", smallIntegerStatementId); 
    setMultipleInserts(connection, "charTable", "charColumn", charStatementId); 
  } 
 
  private void setMultipleInserts(TLptsConnection connection, String tableName, String columnName, String statementId) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    if (table == null) 
      TLptsLogger.logError("Table not Found", null); 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnId = statement.getColumnItemByName(tableName, "id"); 
    if (columnId != null) 
      columnId.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, TLptsFactoryStatement.getStatement(idStatementId).getTitle())); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName(tableName, columnName); 
    if (columnInteger != null) 
      columnInteger.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, TLptsFactoryStatement.getStatement(statementId).getTitle())); 
 
    statement.setTitle("Insert " + columnName + " Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void dynamicStatement(TLptsConnection connection, String tableName, String columnName, boolean isSubstitute) 
  { 
    if (tableName.equals("charTable")) 
      selectDynamicData(2); 
    else 
      selectDynamicData(NO_OF_INSERTIONS); 
 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    statement.getTableItemByName(tableName).setSelected(true); 
    statement.getColumnItemByName(tableName, columnName).setSelected(true); 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    if (tableName.equals("tinyIntegerTable") || tableName.equals("charTable") || tableName.equals("charTable")) 
    { 
      TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
      spit.setColumnItem(statement.getColumnItemByName(tableName, "id")); 
      spit.setOperation(TLptsFactoryStatement.OPERATION_LESS_THAN); 
      if (tableName.equals("charTable")) 
        spit.addStaticStringListItem("2"); 
      ((TLptsDBStatementCriteriaType) statement.getCriteriaType()).addCriteriaListItem(spit); 
    } 
 
    statement.setTitle("Primary " + tableName); 
    statement.setExecutable(false); 
    primaryStatementId = statement.getId(); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
 
    TLptsStatement oracleTableStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    oracleTableStatement.getTableItemByName("OracleTable").setSelected(true); 
    oracleTableStatement.setSelectOnAllColumns("OracleTable", true); 
 
    // all integers are stored as NUMBERS (doubles) in the Oracle db 
    // so we need to tell db-JAPI that we want INTEGERS instead 
    oracleTableStatement.getColumnItemByName("OracleTable", "id").setSqlSubstitutedType(Types.INTEGER); 
    oracleTableStatement.getColumnItemByName("OracleTable", "integerColumn").setSqlSubstitutedType(Types.INTEGER); 
 
    oracleTableStatement.setPrimaryStatement(statement.getId(), isSubstitute); 
    oracleTableStatement.addPrimaryRelationship(dynamicIndex + 1, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    oracleTableStatement.setTitle("OracleTable sub of " + tableName); 
    subStatementId = oracleTableStatement.getId(); 
    System.out.println("SQL Statement " + oracleTableStatement.getTitle() + ": " + oracleTableStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, oracleTableStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                  S E L E C T       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void selectDynamicData(Integer index) 
  { 
    for (Integer i = 0; i < index; i++) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
    } 
  } 
 
  private void selectData(TLptsConnection connection) 
  { 
    Integer[] ids = {10, 11, 12, 13, 14, 15, 16, 17, 18, 19}; 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
    } 
    selectStatement(connection); 
  } 
 
  private void selectStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("OracleTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("OracleTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    // all integers are stored as NUMBERS (doubles) in the Oracle db 
    // so we need to tell db-JAPI that we want INTEGERS instead 
    selectStatement.getColumnItemByName("OracleTable", "id").setSqlSubstitutedType(Types.INTEGER); 
    selectStatement.getColumnItemByName("OracleTable", "integerColumn").setSqlSubstitutedType(Types.INTEGER); 
 
    TLptsDBStatementCriteriaItemType spitInteger = new TLptsDBStatementCriteriaItemType(); 
    spitInteger.setColumnItem(selectStatement.getColumnItemByName("OracleTable", "integerColumn")); 
    spitInteger.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
    spitInteger.addStaticStringListItem("13549"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitInteger); 
 
    TLptsDBStatementCriteriaItemType spitVarchar = new TLptsDBStatementCriteriaItemType(); 
    spitVarchar.setColumnItem(selectStatement.getColumnItemByName("OracleTable", "varcharColumn")); 
    spitVarchar.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
    spitVarchar.addStaticStringListItem("my var character1%"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitVarchar); 
 
    TLptsDBStatementCriteriaItemType spitFloat = new TLptsDBStatementCriteriaItemType(); 
    spitFloat.setColumnItem(selectStatement.getColumnItemByName("OracleTable", "floatColumn")); 
    spitFloat.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
    spitFloat.addStaticStringListItem("1581012.05"); 
    spitFloat.addStaticStringListItem("1581912.55"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitFloat); 
 
    selectStatement.setTitle("Select Statement"); 
    selectStatement.setExecutable(true); 
    selectStatementId = selectStatement.getId(); 
    System.out.println("SQL Statement " + selectStatement.getTitle() + ": " + selectStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, selectStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####                   U P D A T E       F U N C T I O N S 
//  ############################################################################################################### 
 
  private void updateData(TLptsConnection connection) 
  { 
    List<String> data = new LinkedList<String>(); 
    data.add("135419"); 
    data.add("my var character1%"); 
    data.add("2010-07-27 10:40:00"); 
    data.add("1581012.25"); 
    data.add("1581912.25"); 
    data.add("200"); 
    data.add("1"); 
 
    updateStatement(connection, data); 
  } 
 
  private void updateStatement(TLptsConnection connection, List<String> data) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.UPDATE); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName("OracleTable"); 
    if (table == null) 
    { 
      TLptsLogger.logError("Table not Found", null); 
      return; 
    } 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType columnInteger = statement.getColumnItemByName("OracleTable", "integerColumn"); 
    if (columnInteger != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(0)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
      where.setAndConnector(true); 
      columnInteger.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnVarchar = statement.getColumnItemByName("OracleTable", "varcharColumn"); 
    if (columnVarchar != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(1)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
      where.setAndConnector(true); 
      columnVarchar.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnTimestamp = statement.getColumnItemByName("OracleTable", "timestampColumn"); 
    if (columnTimestamp != null) 
      columnTimestamp.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(2))); 
 
    XLptsDBStatementColumnType columnFloat = statement.getColumnItemByName("OracleTable", "floatColumn"); 
    if (columnFloat != null) 
    { 
      TLptsDBUpInsDelWhereType where = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(3)); 
      where.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
      where.setBetweenOther(new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(4))); 
      where.setAndConnector(true); 
      columnFloat.setUpInsDelParameterItem(where); 
    } 
    XLptsDBStatementColumnType columnSmallInt = statement.getColumnItemByName("OracleTable", "smallintegerColumn"); 
    if (columnSmallInt != null) 
      columnSmallInt.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(5))); 
 
    XLptsDBStatementColumnType columnChar = statement.getColumnItemByName("OracleTable", "charColumn"); 
    if (columnChar != null) 
      columnChar.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, data.get(6))); 
 
    statement.setTitle("Update Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
 
    TLptsFactoryRowSet.executeDynamicUpdate(statement); 
  } 
 
  private void selectDataAfterUpdate(TLptsConnection connection) 
  { 
    List<Integer> ids = new LinkedList<Integer>(); 
    for (int i = 0; i < NO_OF_INSERTIONS; i++) 
      if (i > 9 && i < 20) 
        ids.add(i); 
 
    for (Integer i : ids) 
    { 
      idListSelected.add(i); 
      integerMapSelected.put(i, integerMap.get(i)); 
      varcharacterMapSelected.put(i, varcharacterMap.get(i)); 
      timestampMapSelected.put(i, timestampMap.get(i)); 
      floatMapSelected.put(i, floatMap.get(i)); 
      smallintegerMapSelected.put(i, smallintegerMap.get(i)); 
      charMapSelected.put(i, charMap.get(i)); 
    } 
    selectAfterUpdateStatement(connection); 
  } 
 
  private void selectAfterUpdateStatement(TLptsConnection connection) 
  { 
    TLptsStatement selectStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    selectStatement.getTableItemByName("OracleTable").setSelected(true); 
    selectStatement.setSelectOnAllColumns("OracleTable", true); 
    selectStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
 
    // all integers are stored as NUMBERS (doubles) in the Oracle db 
    // so we need to tell db-JAPI that we want INTEGERS instead 
    selectStatement.getColumnItemByName("OracleTable", "id").setSqlSubstitutedType(Types.INTEGER); 
    selectStatement.getColumnItemByName("OracleTable", "integerColumn").setSqlSubstitutedType(Types.INTEGER); 
 
    TLptsDBStatementCriteriaItemType spitFloat = new TLptsDBStatementCriteriaItemType(); 
    spitFloat.setColumnItem(selectStatement.getColumnItemByName("OracleTable", "floatColumn")); 
    spitFloat.setOperation(TLptsFactoryStatement.OPERATION_BETWEEN); 
    spitFloat.addStaticStringListItem("1581012.05"); 
    spitFloat.addStaticStringListItem("1581912.50"); 
    ((TLptsDBStatementCriteriaType) selectStatement.getCriteriaType()).addCriteriaListItem(spitFloat); 
 
    selectStatement.setTitle("Select After Update Statement"); 
    selectStatement.setExecutable(true); 
    selectAfterUpdateStatementId = selectStatement.getId(); 
    System.out.println("SQL Statement " + selectStatement.getTitle() + ": " + selectStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, selectStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####             C O N N E C T I O N      L I S T E N E R 
//  ############################################################################################################### 
 
  private class ConnectionStatementListener implements ILptsFactoryConnectionListener 
  { 
    public synchronized void newConnectionCreated(TLptsConnection connection) 
    { 
      System.out.println("New Connection created successfully. Statements can be processed."); 
      if (connection.getId().equals(connectionCreateTableId)) 
        createAllTableStatements(connection); 
      else if (connection.getId().equals(connectionId)) 
        insertDataIntoAllTables(connection); 
    } 
 
    public void newConnectionProcessStarted() 
    { 
      System.out.println("Connecting..."); 
    } 
 
    public void newConnectionFailed(TLptsLog log) 
    { 
      System.out.println("New Connection Failed. The logger got this."); 
    } 
 
    public void removedAndClosedConnection(TLptsConnection connection) 
    { 
      System.out.println("Connection removed from Connection Manager and closed successfully ... " + connection.getTitle()); 
    } 
  } 
 
//  ############################################################################################################### 
//  ####                   S T A T E M E N T       L I S T E N E R 
//  ############################################################################################################### 
 
  private class StatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) 
    { 
      if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.CREATE) 
      { 
        TLptsFactoryStatement.executeOtherStatement(statement); 
 
        System.out.println("Table Created for connection : " + connection.getTitle()); 
        createdTables++; 
        if (createdTables == 7) 
        { 
          TLptsFactoryConnection.removeConnection(connection, true); 
          TLptsDBConnectionType connectionType; 
 
          //Connect To Oracle 
          connectionType = connectToDB(); 
          if (connectionType != null) 
          { 
            connectionId = connectionType.getId(); 
            TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
          } 
        } 
      } else if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.SELECT) 
      { 
        if (!integerStatementId.isEmpty() && !varcharStatementId.isEmpty() && 
            !timestampStatementId.isEmpty() && !floatStatementId.isEmpty() && 
            !smallIntegerStatementId.isEmpty() && !charStatementId.isEmpty() && 
            insertions != MULTIPLE_LIMIT) 
          insertDataInMultipleTables(connection); 
        else 
          TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false); 
      } 
    } 
 
    public void newStatementFailed(TLptsConnection connection) 
    { 
      TLptsLogger.logError("Fail to Create Statement for connection " + connection.getTitle(), null); 
    } 
 
    public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) 
    { 
    } 
  } 
 
 
//  ############################################################################################################### 
//  ####                    R O W S E T      L I S T E N E R 
//  ############################################################################################################### 
 
  private class RowSetListener implements ILptsFactoryRowSetListener 
  { 
    public void rowEvent(TLptsRowEvent rowEvent) 
    { 
      if (rowEvent.getStatement().getId().equals(selectStatementId) || 
          rowEvent.getStatement().getId().equals(selectAfterUpdateStatementId) || 
          rowEvent.getStatement().getId().equals(subStatementId)) 
      { 
        if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD)) 
        { 
          int index = 0; 
          Integer id = -1; 
 
          for (Object obj : rowEvent.getRowObjectList()) 
          { 
            if (index == 0) 
              id = ((Integer) obj); 
            else if (index == 1) 
            { 
              if (integerMapSelected.get(id) != null) 
                integerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Integer is not in list : " + obj, null); 
            } else if (index == 2) 
            { 
              if (varcharacterMapSelected.get(id) != null) 
                varcharacterMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Varcharacter is not in list : " + obj, null); 
            } else if (index == 3) 
            { 
              if (timestampMapSelected.get(id) != null) 
                timestampMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Timestamp is not in list : " + obj, null); 
            } else if (index == 4) 
            { 
              if (floatMapSelected.get(id) != null) 
                floatMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Float is not in list : " + obj, null); 
            } else if (index == 5) 
            { 
              if (smallintegerMapSelected.get(id) != null) 
                smallintegerMapSelected.remove(id); 
              else 
                TLptsLogger.logError("SmallInteger is not in list : " + obj, null); 
            } else if (index == 6) 
            { 
              if (charMapSelected.get(id) != null) 
                charMapSelected.remove(id); 
              else 
                TLptsLogger.logError("Char is not in list : " + obj, null); 
            } 
            idListSelected.remove(id); 
            index++; 
          } 
        } else if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) && !rowEvent.getStatement().getId().equals(subStatementId)) 
        { 
          if (idListSelected.isEmpty() && integerMapSelected.isEmpty() && 
              varcharacterMapSelected.isEmpty() && floatMapSelected.isEmpty() && 
              smallintegerMapSelected.isEmpty() && charMapSelected.isEmpty()) 
          { 
            System.out.println("#######################"); 
            System.out.println("# Comparison Complete #"); 
            System.out.println("#######################"); 
            if (selectAfterUpdateStatementId.equals("")) 
              updateData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
            else 
              cleanUp(true); 
          } else 
          { 
            String lists = ""; 
            if (!idListSelected.isEmpty()) lists += "ID "; 
            if (!integerMapSelected.isEmpty()) lists += "INTEGER "; 
            if (!varcharacterMapSelected.isEmpty()) lists += "VARCHAR "; 
            if (!floatMapSelected.isEmpty()) lists += "FLOAT "; 
            if (!smallintegerMapSelected.isEmpty()) lists += "SMALL "; 
            if (!charMap.isEmpty()) lists += "CHAR "; 
            TLptsLogger.logError("The following List(s) is not Empty : " + lists + ". Values were not filtered correctly by the SELECT relative operation(s).", null); 
          } 
        } 
      } 
      if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) && rowEvent.getStatement().getId().equals(primaryStatementId)) 
      { 
        if (idListSelected.isEmpty() && integerMapSelected.isEmpty() && 
            varcharacterMapSelected.isEmpty() && floatMapSelected.isEmpty() && 
            smallintegerMapSelected.isEmpty() && charMapSelected.isEmpty()) 
        { 
          System.out.println("############################################################"); 
          System.out.println("# Comparison Complete for statement : " + rowEvent.getStatement().getTitle() + " #"); 
          System.out.println("############################################################"); 
 
          if ((dynamicIndex == 1 && !isSubstitute) || (dynamicIndex == 2 && isSubstitute)) 
            dynamicIndex++; 
 
          if (dynamicIndex < 5) 
            dynamicStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE()), tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
          else if (!isSubstitute) 
          { 
            isSubstitute = true; 
            dynamicIndex = 0; 
            dynamicStatement(((TLptsConnection) rowEvent.getStatement().getConnectionWE()), tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
          } else if (isSubstitute) 
            selectData(((TLptsConnection) rowEvent.getStatement().getConnectionWE())); 
        } else 
          TLptsLogger.logError("A List is not Empty!", null); 
      } 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.INSERT_COMPLETE) 
      { 
        insertions++; 
        if (insertions == NO_OF_INSERTIONS) 
        { 
          System.out.println("###########################################"); 
          System.out.println("# Data Insertion for OracleTable Complete! #"); 
          System.out.println("###########################################"); 
          clearStatements(); 
          selectColumnStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE())); 
        } else if (insertions == MULTIPLE_LIMIT) 
        { 
          System.out.println("###############################################"); 
          System.out.println("# Data Insertion for Primary Tables Complete! #"); 
          System.out.println("###############################################"); 
          clearStatements(); 
          dynamicStatement(((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()), 
                           tableNames.get(dynamicIndex), columnNames.get(dynamicIndex++), isSubstitute); 
        } 
        return false; 
      } else if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.UPDATE_COMPLETE) 
      { 
        selectDataAfterUpdate((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()); 
        return false; 
      } else 
        return true; 
    } 
  } 
 
  private void clearStatements() 
  { 
    for (TLptsStatement stmt : TLptsFactoryStatement.getStatementList()) 
      TLptsFactoryStatement.removeStatement(stmt); 
  } 
 
  private void columnAndTableNames() 
  { 
    tableNames.add("integerTable"); 
    tableNames.add("varcharTable"); 
    tableNames.add("timestampTable"); 
    tableNames.add("floatTable"); 
    tableNames.add("smallIntegerTable"); 
    tableNames.add("charTable"); 
 
    columnNames.add("integerColumn"); 
    columnNames.add("varcharColumn"); 
    columnNames.add("timestampColumn"); 
    columnNames.add("floatColumn"); 
    columnNames.add("smallintegerColumn"); 
    columnNames.add("charColumn"); 
  } 
 
  private void cleanUp(boolean isExit) 
  { 
    try 
    { 
      System.out.println("######################"); 
      System.out.println("# Test Completed!    #"); 
      System.out.println("# Deleting Tables... #"); 
      clearStatements(); 
      TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionId); 
 
      if (connection != null) 
      { 
        Statement statement = connection.createStatement(); 
        statement.executeUpdate("DROP TABLE charTable "); 
        statement.executeUpdate("DROP TABLE integerTable "); 
        statement.executeUpdate("DROP TABLE floatTable "); 
        statement.executeUpdate("DROP TABLE smallIntegerTable "); 
        statement.executeUpdate("DROP TABLE timestampTable "); 
        statement.executeUpdate("DROP TABLE varcharTable "); 
        statement.executeUpdate("DROP TABLE OracleTable "); 
        System.out.println("# Tables Deleted     #"); 
      } 
      System.out.println("######################"); 
 
      if (isExit) 
        System.exit(0); 
    } catch (SQLException e) 
    { 
      TLptsLogger.logWarning("SQL Exception", e.getMessage(), e); 
      if (isExit) 
        System.exit(0); 
    } 
  } 
}