ExampleDataUpdate.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: 1190 $ 
// $LastChangedDate:: 2010-11-17 13:21:35#$ 
// ---------------------------------------- 
// 
 
import com.lapetus_ltd._2009.xml.types.*; 
import com.lapetus_ltd.api.TLptsMainDatabase; 
import com.lapetus_ltd.api.common.TLptsCryptoUtil; 
import com.lapetus_ltd.api.common.logger.ILptsLogListener; 
import com.lapetus_ltd.api.common.logger.TLptsLog; 
import com.lapetus_ltd.api.common.logger.TLptsLogger; 
import com.lapetus_ltd.api.db.control.*; 
import com.lapetus_ltd.api.db.utils.TLptsDriverLoader; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBConnectionType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelSetType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelWhereType; 
import com.lapetus_ltd.api.db.xml.types.TLptsDriverType; 
 
import java.sql.ResultSet; 
import java.util.ResourceBundle; 
 
 
// 
// Class Description : This class shows an example of programmatic and statement updates to database tables. 
// 
// The example goes about connecting to two data sources (postgres and mssql) and then updates the first manually (programmatically). 
// After the completion of the first rowset (seen at the botton in the rowset listener) the second data source is updated 
// from the first (ie the mssql db is updated with data from the postgres db) 
// 
 
 
public class ExampleDataUpdate 
{ 
  // we store the IDs as we are working with multiple connections that are interlinked. 
  // this way we can find the connection after it has successfully connected to the data source. 
  private String connectionMSSqlId = null; 
  private String connectionPostgresId = null; 
 
 
  public static void main(String[] args) 
  { 
    new ExampleDataUpdate(); 
  } 
 
  public ExampleDataUpdate() 
  { 
    // 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)) 
          System.out.println("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING)) 
          System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
        if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE)) 
          System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage()); 
      } 
    }); 
 
    // 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 UpdateStatementListener()); 
 
    TLptsDBConnectionType connectionType; 
 
    //Connect To PostgreSQL 
    connectionType = connectToDB("POSTGRESQL_DB"); 
    if (connectionType != null) 
    { 
      connectionPostgresId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
    //Connect To To MS SQL 
    connectionType = connectToDB("MSSQL_DB"); 
    if (connectionType != null) 
    { 
      connectionMSSqlId = connectionType.getId(); 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
//    ############################################################################################################## 
//        See ExampleConnectionUsage.connectToDB for an example of how to connect to other Data Sources 
//    ############################################################################################################## 
  } 
 
  // #################################################################################### 
  //            C O N N E C T I O N 
  // #################################################################################### 
 
  private TLptsDBConnectionType connectToDB(String title) 
  { 
    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(title); 
 
    if (title.equals("MSSQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.mssql.testdb1"); 
      userName = bundle.getString("Testing.database.mssql.user"); 
      password = bundle.getString("Testing.database.mssql.password"); 
      driverName = "com.microsoft.sqlserver.jdbc.SQLServerDataSource"; 
    } 
    if (title.equals("POSTGRESQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.postgresql.testdb1"); 
      userName = bundle.getString("Testing.database.postgresql.user"); 
      password = bundle.getString("Testing.database.postgresql.password"); 
      driverName = "org.postgresql.ds.PGSimpleDataSource"; 
    } 
    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 
    if (!title.equals("ACCESS_DB")) 
      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; 
  } 
 
 
  // we are going to programmatically update the "occupation" to "Software Developer" for all males (Gender='M') 
  private void postgresqlSimpleUpdate(TLptsConnection connection) 
  { 
    TLptsStatement updateStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.UPDATE); 
    //The resultSet should be Scroll Sensitive. MS SQL does not support scroll insensitive or forward only for update 
    //data. Access supports scroll sensitive but not forward only. 
    updateStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    updateStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
 
    XLptsDBStatementTableType table = updateStatement.getTableItemByName("testTable"); 
    table.setSelected(true); 
 
    XLptsDBStatementColumnType colX = updateStatement.getColumnItemByName(table.getTableName(), "Occupation"); 
    if (colX != null) 
    { 
      // Now we have to set the update parameters for this column 
      // If we are going to update the row values of this column we have to set the required TLptsDBUpInsDelSetType parameters 
      TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "Software Developer"); 
      colX.setUpInsDelParameterItem(setType); 
 
      // If we want the db-JAPI to handle the column as another SQL Type we can set this type to this parameters like: 
      // colX.getUpInsDelParameterItem().setSqlSubstituteType(java.sql.Types.BINARY); 
 
      // Note the following when it comes to updating STRING values in different types of data sources: 
      // 1. Access requires the setCharset as it cannot update STRING values directly. 
      // 2. PostgreSQL and MySQL support direct update of the string values, without setting the charset. 
      // 3. MS SQL has two types of strings, n-type (nvarchar etc) and simple types (varchar etc.). N-types 
      //    requires the setCharset(), but the normal char does not require it. 
      // colX.getUpInsDelParameterItem().setCharset("windows-1253"); 
    } 
 
    colX = updateStatement.getColumnItemByName(table.getTableName(), "Gender"); 
    if (colX != null) 
    { 
      TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "M"); 
      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); 
  } 
 
 
  // This time we will update the "occupation" column with data from the 'Occupation' SELECT Statement. 
  // The rows that will be updated are those with value that are in the "ID" SELECT Statement. 
  private void mssqlDynamicUpdate() 
  { 
    TLptsConnection postgresqlConnection = TLptsFactoryConnection.getConnection(connectionPostgresId); 
    if (postgresqlConnection == null) 
      return; 
    selectDataStatements(postgresqlConnection, "testTable", "ID"); 
    selectDataStatements(postgresqlConnection, "testTable", "Occupation"); 
 
    TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId); 
    if (connection == 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(), "Occupation"); 
 
    // UPDATE 
    if (colX != null) 
    { 
      TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Occupation"); 
      colX.setUpInsDelParameterItem(setType); 
      colX.getUpInsDelParameterItem().setCharset("UTF-16LE"); 
    } 
 
    // WHERE CRITERIA 
    colX = updateStatement.getColumnItemByName(table.getTableName(), "ID"); 
    if (colX != null) 
    { 
      TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "ID"); 
      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); // executes a separate thread 
  } 
 
 
  // this creates the OCCUPATION and ID Statements to be used by the dynamic update. 
 
  private void selectDataStatements(TLptsConnection connection, String tableName, String columnName) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    table.setSelected(true); 
 
    // another way to select a column 
    for (XLptsDBStatementColumnType column : statement.getColumnItemsForTable(table.getTableName())) 
      if (column.getColumnName().equals(columnName)) 
        column.setSelected(true); 
 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); 
    statement.setTitle(columnName); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
  } 
 
 
//  ############################################################################################################### 
//  ####             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(connectionPostgresId)) 
        postgresqlSimpleUpdate(connection); // performs the simple update 
    } 
 
    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 UpdateStatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) 
    { 
      if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.SELECT) 
        TLptsLogger.logMessage("Select Statement created for connection " + connection.getTitle(), null); 
    } 
 
    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) 
    { 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.UPDATE_COMPLETE) 
      { 
        try 
        { 
          System.out.println("Statement " + rowSetEvent.getStatement().getTitle() + " " + 
                             ((TLptsConnection) rowSetEvent.getStatement().getConnection()).getTitle() + 
                             " update execution complete..."); 
 
          System.out.println(rowSetEvent.getRows() + " rows updated, " + rowSetEvent.getObjects() + " objects updated"); 
 
          if (rowSetEvent.getRowSetsNotFinished().isEmpty()) 
            System.out.println("All row sets were updated uniformly."); 
          else 
            for (String rowSet : rowSetEvent.getRowSetsNotFinished()) 
              System.out.println(rowSet + " did not complete it's whole data set."); 
 
          // now we can kick off the update of the dynamic data after the postgres db has been updated 
          if (((TLptsConnection) rowSetEvent.getStatement().getConnection()).getId().equals(connectionPostgresId)) 
            mssqlDynamicUpdate(); 
 
        } catch (Exception e) 
        { 
          TLptsLogger.logError("Cannot get Connection Name", e); 
        } 
      } 
 
      return true; 
    } 
  } 
}