ExampleConnectionUsage.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.XLptsDBSqlDatabaseFormat; 
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.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.TLptsDriverType; 
 
import java.util.ResourceBundle; 
 
 
// Class Description : This is an example of how to use db-JAPI for connection and controlling database information (without the GUI). 
// 
// This class provides code for connecting to a data source, creating a statement and executing a simple rowset query. 
// 
// For this code to work we use our own sample data file (excel format), which means that this code cannot 
// be run on a non-Windows system as is. 
// But in saying that, with very small changes (swapping comments and changing parameters), a connection 
// can be made to your MYSQL, POSTGRES or even MSSQL from a non-Windows System. (code included in large comment block) 
// 
// The output of this application is a simple dump of the objects of each row in the row set. 
 
 
public class ExampleConnectionUsage 
{ 
  public static void main(String[] args) 
  { 
    new ExampleConnectionUsage(); 
  } 
 
  public ExampleConnectionUsage() 
  { 
    // 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 
    TLptsFactoryStatement.addListener(new StatementListener()); 
 
    // register a listener for the rowsets produced 
    TLptsFactoryRowSet.addListener(new RowSetListener()); 
 
    // the ODBC drivers are Windows only 
    if (TLptsSysInfoUtil.isHostWindows()) 
    { 
      System.out.println("This is a Windows HOST. Able to test ODBC"); 
      ResourceBundle bundle = ResourceBundle.getBundle("resources"); // we have stored all the database info in this bundle 
      String fileName = TLptsFileUtil.getCurrentDirectory() + bundle.getString("Testing.database.access.testdb1"); 
 
      TLptsDBConnectionType connectionType = new TLptsDBConnectionType(); 
      connectionType.setTitle(fileName); 
 
      XLptsDriverType driverType = TLptsDriverLoader.getDriverTypeByClassName("sun.jdbc.odbc.JdbcOdbcDriver"); 
 
      if (driverType != null) 
        connectionType.setDriverType(new TLptsDriverType(driverType)); 
      else 
      { 
        System.out.println("Could not find driver for class : sun.jdbc.odbc.JdbcOdbcDriver. Cannot connect."); 
        return; 
      } 
 
      connectionType.setDataFileUrl(fileName); 
      connectionType.setConnectionString(driverType.getConnectionStringFormat()); 
      // we will set the default credentials from the driver loader file. 
      // Note the passwords are never stored in the OPEN. They are decrypted whenever required and the credentials are thrown away after usage. 
      connectionType.setCredentials(TLptsCryptoUtil.defaultEncryptCredentialsRSA(driverType.getGuestUser(), driverType.getGuestPassword())); 
 
      TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!! 
    } 
  } 
 
  // #################################################################################### 
  // COMMENTED OUT FOR DEMONSTRATION PURPOSES. SHOWS HOW TO CONNECT TO OTHER DATA SOURCES 
  // #################################################################################### 
 
/* 
  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("ORACLE_DB")) 
    { 
      dbName = bundle.getString("Testing.database.oracle.testdb1"); 
      userName = bundle.getString("Testing.database.oracle.user"); 
      password = bundle.getString("Testing.database.oracle.password"); 
      driverName = "oracle.jdbc.pool.OracleDataSource"; 
    } 
    if (title.equals("MYSQL_DB")) 
    { 
      dbName = bundle.getString("Testing.database.mysql.testdb1"); 
      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; 
  } 
*/ 
 
//  ############################################################################################################### 
//  ####             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."); 
 
      TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT); 
      statement.setTitle("TRANS"); 
      statement.getTableItemByName("TRANSACTION").setSelected(true); 
      statement.setSelectOnAllColumns("TRANSACTION", true); 
      // the statement is modified by hand and will not be generated from the selected tables and columns. 
      statement.setSqlStatementModified(true); 
      // the formatting are for ODBC, which is automatically set for Oracle, MySql, PostgreSql, MS-SQL and ODBC. 
      statement.setSqlDatabaseFormat(XLptsDBSqlDatabaseFormat.DEFAULT_ODBC); 
      // set so that it is executed. The default is FALSE 
      statement.setExecutable(true); 
      TLptsFactoryStatement.createNewStatement(connection, statement); 
    } 
 
    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 class StatementListener implements ILptsFactoryStatementListener 
  { 
    public void newStatementProcessStarted(TLptsConnection connection) 
    { 
    } 
 
    public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) 
    { 
      System.out.println("New Statement created ..." + connection.getTitle()); 
      TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false); 
    } 
 
    public void newStatementFailed(TLptsConnection connection) 
    { 
    } 
 
    public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) 
    { 
      System.out.println("Statement removed and closed successfully ... " + statement.getTitle()); 
    } 
  } 
 
 
//  ############################################################################################################### 
//  ####                    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.getEventType().equals(TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD)) 
      { 
//        rowEvent.getRowObjectList(); 
//        rowEvent.getColumnNameList(); 
//        rowEvent.getColumnTypeList(); 
//        rowEvent.getByteValueList(); 
 
        System.out.println(); 
        for (Object obj : rowEvent.getRowObjectList()) 
          System.out.print(obj.toString() + " "); 
      } 
 
      if (rowEvent.getEventType().equals(TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE)) 
        System.exit(0); 
    } 
 
    public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent) 
    { 
      return true; 
    } 
  } 
}