// // 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; } } }