ExampleDataInsert.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.TLptsFileUtil; 
import com.lapetus_ltd.api.common.TLptsSysInfoUtil; 
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.TLptsDriverType; 
 
import java.sql.ResultSet; 
import java.util.ResourceBundle; 
 
 
// Class Description : Example of how to utilise the Data Insertion capability of db-JAPI. 
// 
// This class demonstrates how to use the API to insert data from either another data source or programmatically. 
// The data sources used for importation and insertion of the data are MS-SQL, POSTGRES, MS ACCESS and MySQL. 
// 
// So the first step below is to connect to all these data sources. Then programmatic insertions are performed on all 
// databases by creating new test tables and columns. 
// The insertion is performed on all databases from statements in our MySQL database. 
// 
 
 
public class ExampleDataInsert 
{ 
  // 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 connectionMySqlId = ""; 
  private String connectionMSSqlId = ""; 
  private String connectionPostgresId = ""; 
  private String connectionMsAccessId = ""; 
 
  public static void main(String[] args) 
  { 
    new ExampleDataInsert(); 
  } 
 
  public ExampleDataInsert() 
  { 
    // 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 InsertStatementListener()); 
 
 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
    ///////////            Connect to postgreSQL DB 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 
    TLptsDBConnectionType connectionType; 
 
    connectionType = connectToDB("POSTGRESQL_DB"); 
    connectionPostgresId = connectionType.getId(); // this is used later in the listeners 
    TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
    ////////////////////       Connect to MySQL DB 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 
    connectionType = connectToDB("MYSQL_DB"); 
    connectionMySqlId = connectionType.getId(); // this is used later in the listeners 
    TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
    ///////////////////    Connect to MS SQL DB 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 
    connectionType = connectToDB("MSSQL_DB"); 
    connectionMSSqlId = connectionType.getId(); // this is used later in the listeners 
    TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
    /////////////////// Connect to MS Access data source 
    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// 
 
    // the odbc drivers are Windows only 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      connectionType = connectToDB("ACCESS_DB"); 
      connectionMsAccessId = connectionType.getId(); // this is used later in the listeners 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
  } 
 
  // #################################################################################### 
  //            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("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"; 
    } 
    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"; 
    } 
    if (title.equals("ACCESS_DB")) 
    { 
      String fileName = TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb1"); 
      connectionType.setDataFileUrl(fileName);  // Access file from sample-data directory 
      userName = bundle.getString("Testing.database.access.user"); 
      password = bundle.getString("Testing.database.access.password"); 
      driverName = "sun.jdbc.odbc.JdbcOdbcDriver"; 
    } 
 
    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; 
  } 
 
 
  private void insertData() 
  { 
    /***************************************** Programmatic Insert Data **********************************************/ 
    //Find the connection to add data 
 
    TLptsConnection odbcConnection = TLptsFactoryConnection.getConnection(connectionMsAccessId); 
    if (odbcConnection == null) 
    { 
      System.out.println("######################################"); 
      System.out.println("Cannot find the MS Access Connection."); 
      System.out.println("######################################"); 
      return; 
    } 
 
    // create a programmatic statement 
    createInsertStatement(odbcConnection, "testTable", "windows-1253"); 
 
 
    /*********************************Insert Data with Lapetus_Statement or Stored Procedure*****************************/ 
    /*********************************Get Data From MySQL****************************************************************/ 
 
    TLptsConnection mySqlConnection = TLptsFactoryConnection.getConnection(connectionMySqlId); 
    if (mySqlConnection == null) 
    { 
      System.out.println("######################################"); 
      System.out.println("Cannot find the MySQL Connection."); 
      System.out.println("######################################"); 
      return; 
    } 
 
    //Create Lapetus_Statement to provide the insert data 
    selectDataStatements(mySqlConnection, "customers", "idcustomers"); 
    selectDataStatements(mySqlConnection, "customers", "Occupation"); 
    selectDataStatements(mySqlConnection, "customers", "Gender"); 
    selectDataStatements(mySqlConnection, "customers", "NumChildren"); 
    selectDataStatements(mySqlConnection, "customers", "RegistrationDate"); 
 
    /*********************************Add Data to MS SQL Server**********************************************************/ 
    TLptsConnection msSqlConnection = TLptsFactoryConnection.getConnection(connectionMSSqlId); 
    if (msSqlConnection == null) 
    { 
      System.out.println("######################################"); 
      System.out.println("Cannot find the MS-SQL Connection."); 
      System.out.println("######################################"); 
      return; 
    } 
 
    createDynamicInsertStatement(msSqlConnection, "testTable", "UTF-16LE"); 
 
 
    /*********************************Add Data to PostgreSQL ************************************************************/ 
    TLptsConnection postgresConnection = TLptsFactoryConnection.getConnection(connectionPostgresId); 
    if (postgresConnection == null) 
    { 
      System.out.println("######################################"); 
      System.out.println("Cannot find the PostgreSQL Connection."); 
      System.out.println("######################################"); 
      return; 
    } 
 
    createDynamicInsertStatement(postgresConnection, "testTable", null); 
 
    /********************************Add Data to Microsoft Office Access***********************************************/ 
    //Find the connection to add data 
    odbcConnection = null; 
    for (TLptsConnection conn : TLptsFactoryConnection.getConnectionList()) 
      if (conn.getId().equals(connectionMsAccessId)) 
        odbcConnection = conn; 
 
    if (odbcConnection == null) 
      return; 
 
    createDynamicInsertStatement(odbcConnection, "testTable", "windows-1253"); 
  } 
 
 
  private void createDynamicInsertStatement(TLptsConnection connection, String tableName, String charset) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    table.setSelected(true); 
 
    // use the statements "idcustomers" "Occupation" "Gender" "NumChildren" and "RegistrationDate" as sources for data to insert 
    initColumn(statement, table.getTableName(), "ID", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "idcustomers", charset); 
    initColumn(statement, table.getTableName(), "Occupation", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Occupation", charset); 
    initColumn(statement, table.getTableName(), "Gender", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Gender", charset); 
    initColumn(statement, table.getTableName(), "NumChildren", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "NumChildren", charset); 
    initColumn(statement, table.getTableName(), "RegistrationDate", XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "RegistrationDate", charset); 
 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void createInsertStatement(TLptsConnection connection, String tableName, String charset) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.INSERT); 
    // set this to be compatible with all data sources 
    statement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE); 
    statement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE); 
 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    table.setSelected(true); 
 
    //Then select Columns and also set the appropriate variables 
    initColumn(statement, table.getTableName(), "ID", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "1", charset); 
    initColumn(statement, table.getTableName(), "Occupation", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "Software Developer", charset); 
    initColumn(statement, table.getTableName(), "Gender", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "M", charset); 
    initColumn(statement, table.getTableName(), "NumChildren", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "2", charset); 
    initColumn(statement, table.getTableName(), "RegistrationDate", XLptsDBUpInsDelSourceType.PROGRAMMATIC, "2010-06-21 12:54:31", charset); 
 
    statement.setTitle("Insert Statement"); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
    TLptsFactoryRowSet.executeDynamicInsert(statement); 
  } 
 
  private void initColumn(TLptsStatement statement, String tableName, String columnName, XLptsDBUpInsDelSourceType sourceType, String sourceString, 
                          String charset) 
  { 
    XLptsDBStatementColumnType column = statement.getColumnItemByName(tableName, columnName); 
 
    if (column != null) 
    { 
      TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(sourceType, sourceString); 
      column.setUpInsDelParameterItem(setType); 
      column.getUpInsDelParameterItem().setCharset(charset); 
    } 
  } 
 
  private void selectDataStatements(TLptsConnection connection, String tableName, String columnName) 
  { 
    TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
    // set the table and column 
    XLptsDBStatementTableType table = statement.getTableItemByName(tableName); 
    table.setSelected(true); 
    statement.getColumnItemByName(tableName, columnName).setSelected(true); 
 
    statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); 
    statement.setTitle(columnName); 
 
    TLptsFactoryStatement.createNewStatement(connection, statement); 
    System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 
  } 
 
 
//  ############################################################################################################### 
//  ####             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 (TLptsFactoryConnection.getConnectionListSize() == 4) 
        insertData(); 
    } 
 
    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 InsertStatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement targetStatement) 
    { 
    } 
 
    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 
//  ############################################################################################################### 
 
  // this just informs us that the insert has completed properly or there is an error. 
 
  private class RowSetListener implements ILptsFactoryRowSetListener 
  { 
    public void rowEvent(TLptsRowEvent rowEvent) 
    { 
      // it will never come in here as false was returned by processNewRowSetRows. 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.INSERT_COMPLETE) 
      { 
        System.out.println("Statement " + rowSetEvent.getStatement().getTitle() + " " + 
                           ((TLptsConnection) rowSetEvent.getStatement().getConnectionWE()).getTitle() + 
                           " insert execution complete..."); 
 
        System.out.println(rowSetEvent.getRows() + " rows inserted, " + rowSetEvent.getObjects() + " objects inserted"); 
 
        if (rowSetEvent.getRowSetsNotFinished().isEmpty()) 
          System.out.println("All row sets are in sync.");  // source data same size as set data 
        else 
          for (String rowSet : rowSetEvent.getRowSetsNotFinished()) 
            System.out.println(rowSet + " are not in sync."); 
      } 
 
      if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.ERROR_OCCURRED) 
        System.out.println("The insert was not successful. We need to check the output from the logger for more information."); 
 
      // we tell the rowset factory not to process the rows and send them to us via the rowEvent. 
      return false; 
    } 
  } 
}