// // 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: 1048 $ // $LastChangedDate:: 2010-08-26 10:38:31#$ // ---------------------------------------- // import com.lapetus_ltd._2009.xml.types.*; import com.lapetus_ltd.api.TLptsMainDatabase; import com.lapetus_ltd.api.common.TLptsCharSetLocaleUtil; 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.ResultSet; import java.util.ResourceBundle; // ###################################################################################################### // #### Test code for the dbJAPI build process. This code will stop the build process on ERROR #### // ###################################################################################################### // // Class Description : This is the test module for the usage of database stored procedures. // // The MySQL is used for this with two different Stored Procedures called 'LessThen5201' and 'LessThan25000'. // These SPs select the rows from the Customers table that have IDs less than 5201 and 25000 respectively. // // The procedure follows this function path {@link #createTable}, {@link #insertData}, {@link #updateData}, // {@link #deleteData()} and {@link #selectData}. // public class TestStoredProcedures extends TestCase { private String connectionMySqlId = null; private String selectStatementVerifyId = null; static public void main(String[] args) { TestStoredProcedures tdd = new TestStoredProcedures(); tdd.test(); } public void test() { // 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 statements TLptsFactoryStatement.addListener(new StatementListener()); // register a listener for the rowsets produced TLptsFactoryRowSet.addListener(new RowSetListener()); TLptsDBConnectionType connectionType; //Connect To MY SQL 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 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("MYSQL_DB"); dbName = bundle.getString("Testing.database.mysql.testdb2"); userName = bundle.getString("Testing.database.mysql.user"); password = bundle.getString("Testing.database.mysql.password"); driverName = "com.mysql.jdbc.jdbc2.optional.MysqlDataSource"; 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; } // #################################################################################### // C R E A T E F U N C T I O N // #################################################################################### private void createTable() { //Find the Connection to Create table TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMySqlId); if (connection == null) { TLptsLogger.logError("Could not find the connection for the MySql database.", null); return; } // type of statement is CREATE TLptsStatement createStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.CREATE); TLptsDBCreateStatementRootType rootType = new TLptsDBCreateStatementRootType(); rootType.setTitle("root"); // table to be created TLptsDBCreateStatementTableType table = new TLptsDBCreateStatementTableType(); table.setTable("TestTable"); rootType.setTable(table); // permanent table TLptsDBCreateStatementTableTemporaryType temporary = new TLptsDBCreateStatementTableTemporaryType(); temporary.setTemporary(false); TLptsDBCreateStatementTableGlobalLocal globalLocal = new TLptsDBCreateStatementTableGlobalLocal(); globalLocal.setGlobalLocal(XLptsDBCreateStatementTableGlobalLocalType.GLOBAL); temporary.setGlobalLocal(globalLocal); table.setTemporary(temporary); // ### ID COLUMN ### 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); // ### ID-1 COLUMN ### idColumn = new TLptsDBCreateStatementColumnType(); idColumn.setColumn("ID-1"); sqlType = new TLptsDBCreateStatementColumnSqlType(idColumn); sqlType.setSqlType(java.sql.Types.INTEGER); idColumn.setSqlType(sqlType); table.addColumn(idColumn); createStatement.setCreateRootItem(rootType); System.out.println("SQL Statement " + createStatement.getTitle() + ": " + createStatement.getSqlStatementFormatted()); TLptsFactoryStatement.createNewStatement(connection, createStatement); } // #################################################################################### // I N S E R T F U N C T I O N // #################################################################################### private void insertData() { //Find the Connection TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMySqlId); if (connection == null) { TLptsLogger.logError("Could not find the connection for the MySql database.", null); return; } TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); XLptsDBStatementTableType table = statement.getTableItemByName("TestTable"); table.setSelected(true); // this is where we use the stored procedure from the same MySql DB (twice) XLptsDBStatementColumnType col = statement.getColumnItemByName("TestTable", "ID"); if (col != null) { col.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.STORED_PROCEDURE, "LessThan25000")); col.getUpInsDelParameterItem().setCharset(TLptsCharSetLocaleUtil.NO_CHARSET_TRANSLATION); } col = statement.getColumnItemByName("TestTable", "ID-1"); if (col != null) { col.setUpInsDelParameterItem(new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.STORED_PROCEDURE, "LessThan25000")); col.getUpInsDelParameterItem().setCharset(TLptsCharSetLocaleUtil.NO_CHARSET_TRANSLATION); } statement.setTitle("Insert Statement"); System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); TLptsFactoryRowSet.executeDynamicInsert(statement); } // #################################################################################### // D E L E T E F U N C T I O N // #################################################################################### // now we are going to delete based on data from the 'LessThan5201' Stored Procedure. private void deleteData() { TLptsConnection mysqlConnection = TLptsFactoryConnection.getConnection(connectionMySqlId); if (mysqlConnection == null) { TLptsLogger.logError("Could not find the Connection for MS SQL.", null); return; } TLptsStatement deleteStatement = new TLptsStatement(mysqlConnection, XLptsDBTypeOfStatementType.DELETE); deleteStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); deleteStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); XLptsDBStatementTableType table = deleteStatement.getTableItemByName("testTable"); table.setSelected(true); // all the values in the statement called ID will be used as criteria for deletion, and this means that all matching // rows with the values in the ID column will be removed from this table. XLptsDBStatementColumnType column = deleteStatement.getColumnItemByName(table.getTableName(), "ID"); if (column != null) { TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.STORED_PROCEDURE, "LessThan5201"); whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); whereType.setAndConnector(true); column.setUpInsDelParameterItem(whereType); } deleteStatement.setTitle("Delete Statement"); System.out.println("SQL Statement " + deleteStatement.getTitle() + ": " + deleteStatement.getSqlStatementFormatted()); TLptsFactoryRowSet.executeDynamicDelete(deleteStatement); } // #################################################################################### // U P D A T E F U N C T I O N // #################################################################################### private void updateData() { TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMySqlId); if (connection == null) { TLptsLogger.logError("Could not find the Connection for MySql.", null); return; } TLptsStatement updateStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.UPDATE); updateStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); updateStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); XLptsDBStatementTableType table = updateStatement.getTableItemByName("TestTable"); table.setSelected(true); XLptsDBStatementColumnType colX; colX = updateStatement.getColumnItemByName(table.getTableName(), "ID-1"); if (colX != null) { TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "1234"); colX.setUpInsDelParameterItem(setType); colX.getUpInsDelParameterItem().setCharset("UTF-16LE"); } colX = updateStatement.getColumnItemByName(table.getTableName(), "ID"); if (colX != null) { TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.STORED_PROCEDURE, "Get5799ForUpdate"); whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL); whereType.setAndConnector(true); colX.setUpInsDelParameterItem(whereType); } updateStatement.setTitle("Update Statement"); System.out.println("SQL Statement " + updateStatement.getTitle() + ": " + updateStatement.getSqlStatementFormatted()); TLptsFactoryRowSet.executeDynamicUpdate(updateStatement); } // #################################################################################### // S E L E C T F U N C T I O N // #################################################################################### private void selectData() { TLptsConnection mysqlConnection = TLptsFactoryConnection.getConnection(connectionMySqlId); if (mysqlConnection == null) { TLptsLogger.logError("Could not find the Connection for MS SQL.", null); return; } TLptsStatement statement = new TLptsStatement(mysqlConnection, XLptsDBTypeOfStatementType.SELECT); statement.setTitle("TestTableSelect"); statement.setXResultSetType(ResultSet.TYPE_FORWARD_ONLY); statement.setXResultSetConcurrency(ResultSet.CONCUR_READ_ONLY); XLptsDBStatementTableType table = statement.getTableItemByName("TestTable"); table.setSelected(true); statement.setSelectOnAllColumns(table.getTableName(), true); statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); statement.setExecutable(true); selectStatementVerifyId = statement.getId(); TLptsFactoryStatement.createNewStatement(mysqlConnection, statement); // spawns a seperate thread } // ############################################################################################################### // #### C L E A N U P F U N C T I O N S // ############################################################################################################### private void cleanup() { clearTable(); System.exit(0); } private void clearTable() { TLptsConnection connection; TLptsStatement lptsStatement; connection = TLptsFactoryConnection.getConnection(connectionMySqlId); lptsStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.OTHER); lptsStatement.setSqlStatementUser("DROP TABLE `storesdb`.`TestTable`;"); lptsStatement.setSqlStatementModified(true); TLptsFactoryStatement.executeOtherStatement(lptsStatement); System.out.println("TestTable has been DROPPED."); } // ############################################################################################################### // #### 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."); createTable(); } 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); // executes in this thread - no synchronisation issues System.out.println("Table Created for connection : " + connection.getTitle()); connection.clearCachedTablesAndColumns(); // the new table needs to be cached insertData(); } if (statement.getId().equals(selectStatementVerifyId)) TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false); } public void newStatementFailed(TLptsConnection connection) { TLptsLogger.logError("Failed 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 { int processedRecords = 0; public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) { if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.INSERT_COMPLETE) { deleteData(); return false; } if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.DELETE_COMPLETE) { updateData(); return false; } if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.UPDATE_COMPLETE) { selectData(); return false; } if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.NEW_SELECT_RESULTSET) { return true; // yes we want db-JAPI to process the records and give them to use one at a time for the SELECT statement. } return false; } // this is where we interrogate the records returned by the SELECT after INSERT, UPDATE and DELETE. public void rowEvent(TLptsRowEvent rowEvent) { if (rowEvent.getStatement().getId().equals(selectStatementVerifyId)) { if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.ERROR_OCCURRED)) { for (TLptsLog log : TLptsLogger.getLogList(true)) if (log.getType().equals(TLptsLogger.LOG_TYPE.ERROR)) System.out.println("ERROR LOG : " + log.getMessage() + " : " + log.getSupportingText() + " : " + log.getExceptionMessage()); fail(); return; } if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE)) { if (processedRecords < 100) fail("Expected more than 100 records in data set"); System.out.println("Records Processed : " + processedRecords); System.out.println("All tests passed for INSERT, UPDATE, DELETE and SELECT with Stored Procedures."); cleanup(); } // this is the first column, which happens to be the ID if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_PRIMARY_RECORD) || rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD)) if (rowEvent.getRowObjectList().get(0) instanceof Integer) { processedRecords++; Integer id = (Integer) rowEvent.getRowObjectList().get(0); Integer id_1 = (Integer) rowEvent.getRowObjectList().get(1); if (id < 5201) { if (id != 1234) fail("The records below 5201 were deleted and should not be in the select. 1234 was updated from 5799 after the delete."); } if (id > 25000) fail("The records with ID above 25000 were not selected by the STORED PROCEDURE, so they should not be in the data set."); if (id == 5799) { if (id_1 != 1234) fail("Update test not passed. Record 5799 is not updated to 1234 in column ID-1"); else System.out.println("Update Test verified"); } } else fail("Expected an integer value for ID"); } } } }