Example of INSERT
//
// 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;
}
}
}