TestSqlOperationsWithDataMySql.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.XLptsDBCriteriaType; 
import com.lapetus_ltd._2009.xml.types.XLptsDBTypeOfStatementType; 
import com.lapetus_ltd._2009.xml.types.XLptsDriverType; 
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.TLptsDBConnectionType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBStatementCriteriaItemType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBStatementCriteriaType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDriverType; 
import junit.framework.TestCase; 
 
import java.sql.SQLException; 
import java.util.LinkedList; 
import java.util.List; 
import java.util.ResourceBundle; 
 
 
// ###################################################################################################### 
// ####   Test code for the dbJAPI build process. This code will stop the build process on ERROR     #### 
// ###################################################################################################### 
 
// 
// Class Description : This is one of the many test modules for the statement operations. 
// 
// This module tests OPERATIONS (=,<,>,<>,etc) against many different data types. 
// This test executes static SELECT statements with sub-selects. The results are stored and then checked against the 
// same SQL statement written and executed against the dbJAPI interface. 
// 
 
 
public class TestSqlOperationsWithDataMySql extends TestCase 
{ 
  private String connectionMySqlId = ""; 
  private String transactionsId = ""; 
  private String itemsId = ""; 
  private String customersId = ""; 
  private String inStatementId = ""; 
  private String equalStatementId = ""; 
  private String greaterThanStatementId = ""; 
  private String greaterThanEqualStatementId = ""; 
  private String lessThanEqualStatementId = ""; 
  private String betweenStatementId = ""; 
  private String isNullAndLikeStatementId = ""; 
  private String isNotNullAndNotEqualStatementId = ""; 
  private String lessThanStatementId = ""; 
  private List<Integer> statementList = new LinkedList<Integer>(); 
  private List<String> operation = new LinkedList<String>(); 
  private List<Integer> staticCriteriaList = new LinkedList<Integer>(); 
  private int operationIndex = 0; 
 
  static public void main(String[] args) 
  { 
    TestSqlOperationsWithDataMySql sqlOpWithDataMySql = new TestSqlOperationsWithDataMySql(); 
    sqlOpWithDataMySql.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)) 
        { 
          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()); 
 
    initOperationList(); 
 
    TLptsDBConnectionType connectionType; 
 
    //Connect To MySQL 
    connectionType = connectToDB(); 
    if (connectionType != null) 
    { 
      connectionMySqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
  } 
 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB() 
  { 
    String dbName = "", userName = "", password = ""; 
    ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in here 
 
    dbName = bundle.getString("Testing.database.mysql.testdb2"); 
    userName = bundle.getString("Testing.database.mysql.user"); 
    password = bundle.getString("Testing.database.mysql.password"); 
 
    TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
    connectionType.setTitle("MYSQL_DB"); 
 
    XLptsDriverType driverType = TLptsDriverLoader.getDriverTypeByClassName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); 
 
    if (driverType == null) 
    { 
      System.out.println("Could not find driver for class com.mysql.jdbc.jdbc2.optional.MysqlDataSource"); 
      return null; 
    } 
 
    TLptsDriverType dt = new TLptsDriverType(driverType); 
 
    // 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 
    dt.setValueForInterfaceFunctionParameter("setDatabaseName", "Database Name", dbName); 
    connectionType.setDriverType(dt); 
 
    if (userName == null || password == null) 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(), driverType.getGuestPassword())); 
    else 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(userName, password)); 
 
    return connectionType; 
  } 
 
 
  private void initOperationList() 
  { 
    operation.add(TLptsFactoryStatement.OPERATION_IN); 
    operation.add(TLptsFactoryStatement.OPERATION_EQUAL); 
    operation.add(TLptsFactoryStatement.OPERATION_LESS_THAN); 
    operation.add(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
    operation.add(TLptsFactoryStatement.OPERATION_LESS_THAN_EQUAL); 
    operation.add(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
    operation.add(TLptsFactoryStatement.OPERATION_BETWEEN); 
    operation.add(TLptsFactoryStatement.OPERATION_LIKE); 
    operation.add(TLptsFactoryStatement.OPERATION_IS_NULL); 
    operation.add(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
    operation.add(TLptsFactoryStatement.OPERATION_IS_NOT_NULL); 
 
    staticCriteriaList.add(10); 
    staticCriteriaList.add(10); 
    staticCriteriaList.add(3); 
    staticCriteriaList.add(3); 
    staticCriteriaList.add(2); 
    staticCriteriaList.add(2); 
    staticCriteriaList.add(980); 
    staticCriteriaList.add(0); 
    staticCriteriaList.add(0); 
  } 
 
  private void createInStatement(TLptsConnection connection) 
  { 
    TLptsStatement inStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    inStatement.setSqlStatementUser("SELECT `transactions`.`idtransactions`, " + 
                                    "`transactions`.`customerID`, " + 
                                    "`transactions`.`storeID`, " + 
                                    "`transactions`.`Tameio`, " + 
                                    "`transactions`.`DateTime`, " + 
                                    "`transactions`.`isStoreCustomer`, " + 
                                    "`transactions`.`itemID`, " + 
                                    "`transactions`.`quanity`, " + 
                                    "`transactions`.`value` " + 
                                    "FROM `storesdb`.`transactions` " + 
                                    "WHERE " + 
                                    "`transactions`.`itemID` " + 
                                    "IN (SELECT  `items`.`iditems` " + 
                                    "FROM `storesdb`.`items` WHERE `items`.`iditems` < 10)"); 
 
    inStatement.setSqlStatementModified(true); 
 
    inStatement.setTitle("In Statement"); 
    inStatementId = inStatement.getId(); 
 
    System.out.println("SQL Statement " + inStatement.getTitle() + ": " + inStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, inStatement); 
  } 
 
  private void createEqualStatement(TLptsConnection connection) 
  { 
    TLptsStatement equalStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    equalStatement.setSqlStatementUser("SELECT `transactions`.`idtransactions`, " + 
                                       "`transactions`.`customerID`, " + 
                                       "`transactions`.`storeID`, " + 
                                       "`transactions`.`Tameio`, " + 
                                       "`transactions`.`DateTime`, " + 
                                       "`transactions`.`isStoreCustomer`, " + 
                                       "`transactions`.`itemID`, " + 
                                       "`transactions`.`quanity`, " + 
                                       "`transactions`.`value` " + 
                                       "FROM `storesdb`.`transactions` " + 
                                       "WHERE `transactions`.`itemID` " + 
                                       "= ANY(SELECT  `items`.`iditems` " + 
                                       "FROM `storesdb`.`items` WHERE `items`.`iditems` < 10)"); 
 
    equalStatement.setSqlStatementModified(true); 
    equalStatement.setTitle("Equal Statement"); 
    equalStatementId = equalStatement.getId(); 
 
    System.out.println("SQL Statement " + equalStatement.getTitle() + ": " + equalStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, equalStatement); 
  } 
 
  private void createLessThanAndLessThanEqualStatement(TLptsConnection connection) 
  { 
    TLptsStatement lessThanStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    lessThanStatement.setSqlStatementUser("SELECT `transactions`.`idtransactions`, " + 
                                          "`transactions`.`customerID`, " + 
                                          "`transactions`.`storeID`, " + 
                                          "`transactions`.`Tameio`, " + 
                                          "`transactions`.`DateTime`, " + 
                                          "`transactions`.`isStoreCustomer`, " + 
                                          "`transactions`.`itemID`, " + 
                                          "`transactions`.`quanity`, " + 
                                          "`transactions`.`value` " + 
                                          "FROM `storesdb`.`transactions` " + 
                                          "WHERE `transactions`.`itemID` = 1"); 
 
    lessThanStatement.setSqlStatementModified(true); 
 
    if (lessThanStatementId.isEmpty()) 
    { 
      lessThanStatementId = lessThanStatement.getId(); 
      lessThanStatement.setTitle("Less Than Statement"); 
    } else 
    { 
      lessThanEqualStatementId = lessThanStatement.getId(); 
      lessThanStatement.setTitle("Less Than Equal Statement"); 
    } 
 
    System.out.println("SQL Statement " + lessThanStatement.getTitle() + ": " + lessThanStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, lessThanStatement); 
  } 
 
  private void createGreaterThanAndGreaterThanEqualStatement(TLptsConnection connection) 
  { 
    TLptsStatement greaterThanStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    greaterThanStatement.setSqlStatementUser("select `transactions`.`idtransactions`, " + 
                                             "`transactions`.`customerID`, " + 
                                             "`transactions`.`storeID`, " + 
                                             "`transactions`.`Tameio`, " + 
                                             "`transactions`.`DateTime`, " + 
                                             "`transactions`.`isStoreCustomer`, " + 
                                             "`transactions`.`itemID`, " + 
                                             "`transactions`.`quanity`, " + 
                                             "`transactions`.`value` " + 
                                             "FROM `storesdb`.`transactions` " + 
                                             "WHERE `transactions`.`itemID` > 980 AND " + 
                                             "`transactions`.`itemID` < 990"); 
 
    greaterThanStatement.setSqlStatementModified(true); 
 
    if (greaterThanStatementId.isEmpty()) 
    { 
      greaterThanStatementId = greaterThanStatement.getId(); 
      greaterThanStatement.setTitle("Greater Than Statement"); 
    } else 
    { 
      greaterThanEqualStatementId = greaterThanStatement.getId(); 
      greaterThanStatement.setTitle("Greater Than Equal Statement"); 
    } 
 
    System.out.println("SQL Statement " + greaterThanStatement.getTitle() + ": " + greaterThanStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, greaterThanStatement); 
  } 
 
  private void createBetweenStatement(TLptsConnection connection) 
  { 
    TLptsStatement betweenStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    betweenStatement.setSqlStatementUser("SELECT `transactions`.`idtransactions`, " + 
                                         "`transactions`.`customerID`, " + 
                                         "`transactions`.`storeID`, " + 
                                         "`transactions`.`Tameio`, " + 
                                         "`transactions`.`DateTime`, " + 
                                         "`transactions`.`isStoreCustomer`, " + 
                                         "`transactions`.`itemID`, " + 
                                         "`transactions`.`quanity`, " + 
                                         "`transactions`.`value` " + 
                                         "FROM `storesdb`.`transactions` " + 
                                         "WHERE `transactions`.`itemID` = ANY(SELECT `items`.`iditems` " + 
                                         " FROM `storesdb`.`items` " + 
                                         " WHERE `items`.`iditems` BETWEEN 980 AND 983)"); 
 
    betweenStatement.setSqlStatementModified(true); 
 
    betweenStatement.setTitle("Between Statement"); 
    betweenStatementId = betweenStatement.getId(); 
 
    System.out.println("SQL Statement " + betweenStatement.getTitle() + ": " + betweenStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, betweenStatement); 
  } 
 
  private void createIsNullAndLikeStatement(TLptsConnection connection) 
  { 
    TLptsStatement isNullAndLikeStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    isNullAndLikeStatement.setSqlStatementUser("SELECT  " + 
                                               "`transactions`.`idtransactions`, " + 
                                               "`transactions`.`customerID`, " + 
                                               "`transactions`.`storeID`,  " + 
                                               "`transactions`.`Tameio`,  " + 
                                               "`transactions`.`DateTime`,  " + 
                                               "`transactions`.`isStoreCustomer`,  " + 
                                               "`transactions`.`itemID`,  " + 
                                               "`transactions`.`quanity`,  " + 
                                               "`transactions`.`value`  " + 
                                               "FROM `storesdb`.`transactions` " + 
                                               "WHERE `transactions`.`customerID` = ANY(SELECT `customers`.`idcustomers` " + 
                                               " FROM `storesdb`.`customers` " + 
                                               " WHERE `customers`.`Occupation` LIKE '') AND " + 
                                               " `transactions`.`isStoreCustomer` IS NULL"); 
 
    isNullAndLikeStatement.setSqlStatementModified(true); 
    isNullAndLikeStatement.setTitle("Is Null And Like Statement"); 
    isNullAndLikeStatementId = isNullAndLikeStatement.getId(); 
 
    System.out.println("SQL Statement " + isNullAndLikeStatement.getTitle() + ": " + isNullAndLikeStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, isNullAndLikeStatement); 
  } 
 
  private void createIsNotNullAndNotEqualStatement(TLptsConnection connection) 
  { 
    TLptsStatement isNotNullAndNotEqualStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    isNotNullAndNotEqualStatement.setSqlStatementUser("SELECT  " + 
                                                      "`transactions`.`idtransactions`, " + 
                                                      "`transactions`.`customerID`, " + 
                                                      "`transactions`.`storeID`,  " + 
                                                      "`transactions`.`Tameio`,  " + 
                                                      "`transactions`.`DateTime`,  " + 
                                                      "`transactions`.`isStoreCustomer`,  " + 
                                                      "`transactions`.`itemID`,  " + 
                                                      "`transactions`.`quanity`,  " + 
                                                      "`transactions`.`value`  " + 
                                                      "FROM `storesdb`.`transactions` " + 
                                                      "WHERE `transactions`.`customerID` = ANY(SELECT `customers`.`idcustomers` " + 
                                                      " FROM `storesdb`.`customers` " + 
                                                      " WHERE `customers`.`NumChildren` > 3 AND " + 
                                                      " `customers`.`NumChildren` <> 4) AND " + 
                                                      " `transactions`.`isStoreCustomer` IS NOT NULL"); 
 
    isNotNullAndNotEqualStatement.setSqlStatementModified(true); 
 
    isNotNullAndNotEqualStatement.setTitle("Is Not Null And Not Equal Statement"); 
    isNotNullAndNotEqualStatementId = isNotNullAndNotEqualStatement.getId(); 
 
    System.out.println("SQL Statement " + isNotNullAndNotEqualStatement.getTitle() + ": " + isNotNullAndNotEqualStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, isNotNullAndNotEqualStatement); 
  } 
 
  private void createDynamicSQL(TLptsConnection connection, String operation, Integer staticCriteria) 
  { 
    TLptsStatement itemsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    itemsStatement.getTableItemByName("items").setSelected(true); 
    itemsStatement.getColumnItemByName("items", "iditems").setSelected(true); 
    itemsStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setColumnItem(itemsStatement.getColumnItemByName("items", "iditems")); 
    spit.setOperation(TLptsFactoryStatement.OPERATION_LESS_THAN); 
    spit.addStaticStringListItem(staticCriteria.toString()); 
    ((TLptsDBStatementCriteriaType) itemsStatement.getCriteriaType()).addCriteriaListItem(spit); 
    itemsStatement.setTitle("items"); 
    itemsStatement.setExecutable(false); 
    itemsId = itemsStatement.getId(); 
    System.out.println("SQL Statement " + itemsStatement.getTitle() + ": " + itemsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, itemsStatement); 
 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.setPrimaryStatement(itemsStatement.getId(), false); 
    transactionsStatement.addPrimaryRelationship(7, new int[]{1}, operation); 
    transactionsStatement.setTitle("Transactions"); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
  private void createDynamicBetweenSQL(TLptsConnection connection, String operation) 
  { 
    TLptsStatement itemsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    itemsStatement.getTableItemByName("items").setSelected(true); 
    itemsStatement.getColumnItemByName("items", "iditems").setSelected(true); 
    itemsStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setColumnItem(itemsStatement.getColumnItemByName("items", "iditems")); 
    spit.setOperation(operation); 
    spit.addStaticStringListItem("980"); 
    spit.addStaticStringListItem("983"); 
    ((TLptsDBStatementCriteriaType) itemsStatement.getCriteriaType()).addCriteriaListItem(spit); 
    itemsStatement.setTitle("items"); 
    itemsStatement.setExecutable(false); 
    itemsId = itemsStatement.getId(); 
    System.out.println("SQL Statement " + itemsStatement.getTitle() + ": " + itemsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, itemsStatement); 
 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.setPrimaryStatement(itemsStatement.getId(), false); 
    transactionsStatement.addPrimaryRelationship(7, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    transactionsStatement.setTitle("Transactions Between"); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
  private void createDynamicIsNullAndLikeSQL(TLptsConnection connection) 
  { 
    TLptsStatement customersStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    customersStatement.getTableItemByName("customers").setSelected(true); 
    customersStatement.getColumnItemByName("customers", "idcustomers").setSelected(true); 
    customersStatement.getColumnItemByName("customers", "Occupation").setSelected(true); 
    customersStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType(); 
    spit.setColumnItem(customersStatement.getColumnItemByName("customers", "Occupation")); 
    spit.setOperation(TLptsFactoryStatement.OPERATION_LIKE); 
    spit.addStaticStringListItem(""); 
    ((TLptsDBStatementCriteriaType) customersStatement.getCriteriaType()).addCriteriaListItem(spit); 
    customersStatement.setTitle("customers"); 
    customersStatement.setExecutable(false); 
    customersId = customersStatement.getId(); 
    System.out.println("SQL Statement " + customersStatement.getTitle() + ": " + customersStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, customersStatement); 
 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.setPrimaryStatement(customersStatement.getId(), false); 
    transactionsStatement.addPrimaryRelationship(2, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    TLptsDBStatementCriteriaItemType spit2 = new TLptsDBStatementCriteriaItemType(); 
    spit2.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "isStoreCustomer")); 
    spit2.setOperation(TLptsFactoryStatement.OPERATION_IS_NULL); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit2); 
    transactionsStatement.setTitle("Transactions Is Null and Like"); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
  private void createDynamicIsNotNullAndNotEqualSQL(TLptsConnection connection) 
  { 
    TLptsStatement customersStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    customersStatement.getTableItemByName("customers").setSelected(true); 
    customersStatement.getColumnItemByName("customers", "idcustomers").setSelected(true); 
    customersStatement.getColumnItemByName("customers", "NumChildren").setSelected(true); 
    customersStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit1 = new TLptsDBStatementCriteriaItemType(); 
    spit1.setColumnItem(customersStatement.getColumnItemByName("customers", "NumChildren")); 
    spit1.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
    spit1.addStaticStringListItem("3"); 
    TLptsDBStatementCriteriaItemType spit2 = new TLptsDBStatementCriteriaItemType(); 
    spit2.setColumnItem(customersStatement.getColumnItemByName("customers", "NumChildren")); 
    spit2.setOperation(TLptsFactoryStatement.OPERATION_NOT_EQUAL); 
    spit2.addStaticStringListItem("4"); 
    ((TLptsDBStatementCriteriaType) customersStatement.getCriteriaType()).addCriteriaListItem(spit1); 
    ((TLptsDBStatementCriteriaType) customersStatement.getCriteriaType()).addCriteriaListItem(spit2); 
    customersStatement.setTitle("customers"); 
    customersStatement.setExecutable(false); 
    customersId = customersStatement.getId(); 
    System.out.println("SQL Statement " + customersStatement.getTitle() + ": " + customersStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, customersStatement); 
 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.setPrimaryStatement(customersStatement.getId(), false); 
    transactionsStatement.addPrimaryRelationship(2, new int[]{1}, TLptsFactoryStatement.OPERATION_EQUAL); 
    TLptsDBStatementCriteriaItemType spit3 = new TLptsDBStatementCriteriaItemType(); 
    spit3.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "isStoreCustomer")); 
    spit3.setOperation(TLptsFactoryStatement.OPERATION_IS_NOT_NULL); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit3); 
    transactionsStatement.setTitle("Transactions Is Not Null and Not Equal"); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
  private void createGreaterThanSQL(TLptsConnection connection) 
  { 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit1 = new TLptsDBStatementCriteriaItemType(); 
    spit1.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "itemID")); 
    spit1.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN); 
    spit1.addStaticStringListItem("980"); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit1); 
    TLptsDBStatementCriteriaItemType spit2 = new TLptsDBStatementCriteriaItemType(); 
    spit2.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "itemID")); 
    spit2.setOperation(TLptsFactoryStatement.OPERATION_LESS_THAN); 
    spit2.addStaticStringListItem("990"); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit2); 
    transactionsStatement.setTitle("transactions greater"); 
    transactionsStatement.setExecutable(true); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
  private void createGreaterThanEqualSQL(TLptsConnection connection) 
  { 
    TLptsStatement transactionsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    transactionsStatement.getTableItemByName("transactions").setSelected(true); 
    transactionsStatement.setSelectOnAllColumns("transactions", true); 
    transactionsStatement.getCriteriaType().setType(XLptsDBCriteriaType.STATIC); 
    TLptsDBStatementCriteriaItemType spit1 = new TLptsDBStatementCriteriaItemType(); 
    spit1.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "itemID")); 
    spit1.setOperation(TLptsFactoryStatement.OPERATION_GREATER_THAN_EQUAL); 
    spit1.addStaticStringListItem("980"); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit1); 
    TLptsDBStatementCriteriaItemType spit2 = new TLptsDBStatementCriteriaItemType(); 
    spit2.setColumnItem(transactionsStatement.getColumnItemByName("transactions", "itemID")); 
    spit2.setOperation(TLptsFactoryStatement.OPERATION_LESS_THAN); 
    spit2.addStaticStringListItem("990"); 
    ((TLptsDBStatementCriteriaType) transactionsStatement.getCriteriaType()).addCriteriaListItem(spit2); 
    transactionsStatement.setTitle("transactions greater than equal"); 
    transactionsStatement.setExecutable(true); 
    transactionsId = transactionsStatement.getId(); 
    System.out.println("SQL Statement " + transactionsStatement.getTitle() + ": " + transactionsStatement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, transactionsStatement); 
  } 
 
 
//  ############################################################################################################### 
//  ####             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(connectionMySqlId)) 
        createInStatement(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()); 
    } 
  } 
 
  private void processComplete(TLptsRowEvent rowEvent) 
  { 
    if (statementList.isEmpty()) 
    { 
      System.out.println("################################################"); 
      if (rowEvent.getStatement().getId().equals(customersId)) 
        System.out.println("#Test completed successfully for operation: " + operation.get(operationIndex - 1) + " and operation :" + operation.get(operationIndex++) + " #"); 
      else 
        System.out.println("#Test completed successfully for operation: " + operation.get(operationIndex - 1) + " #"); 
      System.out.println("################################################"); 
      try 
      { 
        if (equalStatementId.equals("")) 
          createEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (lessThanStatementId.equals("")) 
          createLessThanAndLessThanEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (greaterThanStatementId.equals("")) 
          createGreaterThanAndGreaterThanEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (lessThanEqualStatementId.equals("")) 
          createLessThanAndLessThanEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (greaterThanEqualStatementId.equals("")) 
          createGreaterThanAndGreaterThanEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (betweenStatementId.equals("")) 
          createBetweenStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (isNullAndLikeStatementId.equals("")) 
          createIsNullAndLikeStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else if (isNotNullAndNotEqualStatementId.equals("")) 
          createIsNotNullAndNotEqualStatement(((TLptsConnection) rowEvent.getStatement().getConnection())); 
        else 
          System.exit(0); 
      } catch (SQLException e) 
      { 
        TLptsLogger.logError("SQL Exception", e.getLocalizedMessage(), e); 
      } 
    } else 
      TLptsLogger.logError("List is not Empty for operation : " + operation.get(operationIndex - 1), null); 
  } 
 
 
//  ############################################################################################################### 
//  ####                   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) 
    { 
      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(transactionsId)) 
      { 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD) 
        { 
          if (statementList.contains(((Integer) rowEvent.getRowObjectList().get(0)))) 
            statementList.remove(((Integer) rowEvent.getRowObjectList().get(0))); 
          else 
            TLptsLogger.logError("Object is not in list for operation : " + operation.get(operationIndex - 1) + " id: " + rowEvent.getRowObjectList().get(0), null); 
        } 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE && 
            (rowEvent.getStatement().getTitle().equals("transactions greater") || rowEvent.getStatement().getTitle().equals("transactions greater than equal"))) 
        { 
          processComplete(rowEvent); 
        } 
      } 
 
      if (rowEvent.getStatement().getId().equals(itemsId)) 
      { 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) 
        { 
          processComplete(rowEvent); 
        } 
      } 
 
      if (rowEvent.getStatement().getId().equals(customersId)) 
      { 
        if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE) 
        { 
          processComplete(rowEvent); 
        } 
      } 
 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getStatement().getId().equals(equalStatementId) || 
          rowSetEvent.getStatement().getId().equals(lessThanStatementId) || 
          rowSetEvent.getStatement().getId().equals(inStatementId) || 
          rowSetEvent.getStatement().getId().equals(greaterThanStatementId) || 
          rowSetEvent.getStatement().getId().equals(lessThanEqualStatementId) || 
          rowSetEvent.getStatement().getId().equals(greaterThanEqualStatementId) || 
          rowSetEvent.getStatement().getId().equals(betweenStatementId) || 
          rowSetEvent.getStatement().getId().equals(isNullAndLikeStatementId) || 
          rowSetEvent.getStatement().getId().equals(isNotNullAndNotEqualStatementId)) 
      { 
        try 
        { 
          statementList.clear(); 
          while (rowSetEvent.getRowSet().next()) 
            statementList.add(rowSetEvent.getRowSet().getInt(1)); 
          if (rowSetEvent.getStatement().getId().equals(equalStatementId) || 
              rowSetEvent.getStatement().getId().equals(lessThanStatementId) || 
              rowSetEvent.getStatement().getId().equals(inStatementId) || 
              rowSetEvent.getStatement().getId().equals(lessThanEqualStatementId)) 
            createDynamicSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection()), operation.get(operationIndex), staticCriteriaList.get(operationIndex++)); 
          else if (rowSetEvent.getStatement().getId().equals(greaterThanStatementId)) 
          { 
            createGreaterThanSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection())); 
            operationIndex++; 
          } else if (rowSetEvent.getStatement().getId().equals(greaterThanEqualStatementId)) 
          { 
            createGreaterThanEqualSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection())); 
            operationIndex++; 
          } else if (rowSetEvent.getStatement().getId().equals(betweenStatementId)) 
          { 
            createDynamicBetweenSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection()), operation.get(operationIndex++)); 
          } else if (rowSetEvent.getStatement().getId().equals(isNullAndLikeStatementId)) 
          { 
            createDynamicIsNullAndLikeSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection())); 
            operationIndex++; 
          } else if (rowSetEvent.getStatement().getId().equals(isNotNullAndNotEqualStatementId)) 
          { 
            createDynamicIsNotNullAndNotEqualSQL(((TLptsConnection) rowSetEvent.getStatement().getConnection())); 
            operationIndex++; 
          } 
        } catch (SQLException e) 
        { 
          TLptsLogger.logError("Sql Exception", e.getLocalizedMessage(), e); 
        } 
        return false; 
      } 
      return true; 
    } 
  } 
}