// 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; } } }