TestStoredProcedures.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: 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"); 
      } 
    } 
  } 
}