TestUPDATE.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 junit.framework.TestCase;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
import java.util.ResourceBundle;
// ######################################################################################################
// #### Test code for the dbJAPI build process. This code will stop the build process on ERROR ####
// ######################################################################################################
//
// Class Description : This is the test procedure for verification of the dbJAPI Update capability.
//
// Two connections are used in this test, namely postgres and MS-SQL. There are other connections, which have been
// commented out for your reference.
//
// The test first updates the postgres db, then it selects the updated records and updates the MS-SQL with them.
// At the end the updated records are verified using maps created from the processed records of the postgres table.
//
public class TestUPDATE extends TestCase
{
private String msSqlSelectStatementId;
private String postgreSqlSelectStatementId;
private String connectionMSSqlId = null;
private String connectionPostgresId = null;
LinkedList<Integer> idListPostgreSQL = new LinkedList<Integer>();
private Map<Integer, String> occupationMapPostgreSQL = new HashMap<Integer, String>();
private Map<Integer, String> genderMapPostgreSQL = new HashMap<Integer, String>();
private Map<Integer, Integer> numChildrenMapPostgreSQL = new HashMap<Integer, Integer>();
LinkedList<Integer> idListMsSQL = new LinkedList<Integer>();
private Map<Integer, String> occupationMapMsSQL = new HashMap<Integer, String>();
private Map<Integer, String> genderMapMsSQL = new HashMap<Integer, String>();
private Map<Integer, Integer> numChildrenMapMsSQL = new HashMap<Integer, Integer>();
private int updateComplete = 0;
static public void main(String[] args)
{
TestUPDATE tdu = new TestUPDATE();
tdu.testConnections();
}
public void testConnections()
{
// 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))
{
fail("LOG ERROR :" + log.getMessage() + " : " + log.getSupportingText() + " : Exception : " + log.getExceptionMessage());
System.exit(0);
}
if (log.getType().equals(TLptsLogger.LOG_TYPE.WARNING))
System.out.println("LOG WARNING :" + log.getMessage() + " : " + log.getSupportingText());
if (log.getType().equals(TLptsLogger.LOG_TYPE.MESSAGE))
System.out.println("LOG MESSAGE :" + log.getMessage() + " : " + log.getSupportingText());
}
});
// 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 StatementListener());
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!!
}
}
// ####################################################################################
// 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
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;
}
// ###############################################################################################################
// #### U P D A T E F U N C T I O N S
// ###############################################################################################################
private void postgresqlSimpleUpdate(TLptsConnection connection)
{
//Create the statement and set the Type of Statement to Update.
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);
//ResultSet Concurrency should be concur_updatable
updateStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
// set the table and columns for updating
XLptsDBStatementTableType table = updateStatement.getTableItemByName("testTable");
table.setSelected(true);
XLptsDBStatementColumnType colX;
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());
// The formatted statement should be : UPDATE "testTable" SET "Occupation" = ? WHERE "Gender" = ?
// The executed statement is : UPDATE "testTable" SET "Occupation" = 'Software Developer' WHERE "Gender" = 'M'
// Finally just execute the statement.
TLptsFactoryRowSet.executeDynamicUpdate(updateStatement);
}
private void mssqlDynamicUpdate()
{
TLptsConnection postgresqlConnection = TLptsFactoryConnection.getConnection(connectionPostgresId);
if (postgresqlConnection == null)
return;
createSingleColumnStatement(postgresqlConnection, "testTable", "ID");
createSingleColumnStatement(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");
if (colX != null)
{
TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "Occupation");
colX.setUpInsDelParameterItem(setType);
colX.getUpInsDelParameterItem().setCharset("UTF-16LE");
}
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);
}
private void createSingleColumnStatement(TLptsConnection connection, String tableName, String columnName)
{
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
// set the table and columns for selecting
XLptsDBStatementTableType table = statement.getTableItemByName(tableName);
table.setSelected(true);
XLptsDBStatementColumnType column = statement.getColumnItemByName(tableName, columnName);
column.setSelected(true);
statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE);
// Set the name of the statement to the name of the column so that it makes sense
statement.setTitle(columnName);
TLptsFactoryStatement.createNewStatement(connection, statement);
}
private void getPostgreSqlData()
{
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionPostgresId);
if (connection == null)
TLptsLogger.logError("PostgreSQL Connection not found", null);
createPostgresStatement(connection);
}
private void createPostgresStatement(TLptsConnection connection)
{
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
statement.setSqlStatementUser("SELECT * FROM \"testTable\"");
// the statement is modified by hand so we need to specify that we want the user version of the SQL statement
statement.setSqlStatementModified(true);
statement.setTitle("Get PostgreSQL Data");
postgreSqlSelectStatementId = statement.getId();
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, statement);
}
private void getMsSqlData()
{
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (connection == null)
TLptsLogger.logError("MS SQL Connection not found", null);
createMSSQLStatement(connection);
}
private void createMSSQLStatement(TLptsConnection connection)
{
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
statement.setSqlStatementUser("SELECT * FROM [insurance_policy].[dbo].[testTable]");
statement.setSqlStatementModified(true);
statement.setTitle("Get Ms SQL Data");
msSqlSelectStatementId = statement.getId();
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, statement);
}
private void checkData(String db)
{
if (occupationMapPostgreSQL.isEmpty())
TLptsLogger.logError("Occupation Map for PostgreSQL is Empty", null);
if (genderMapPostgreSQL.isEmpty())
TLptsLogger.logError("Gender Map for PostgreSQL is Empty", null);
if (numChildrenMapPostgreSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for PostgreSQL is Empty", null);
if (occupationMapMsSQL.isEmpty())
TLptsLogger.logError("Occupation Map for " + db + " is Empty", null);
if (genderMapMsSQL.isEmpty())
TLptsLogger.logError("Gender Map for " + db + " is Empty", null);
if (numChildrenMapMsSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for " + db + " is Empty", null);
String occupationPostgreSQL;
String occupationMsSQL;
String genderPostgreSQL;
String genderMsSQL;
Integer numChildrenPostgreSQL;
Integer numChildrenMsSQL;
for (Integer id : idListPostgreSQL)
{
occupationPostgreSQL = occupationMapPostgreSQL.get(id);
occupationMsSQL = occupationMapMsSQL.get(id);
genderPostgreSQL = genderMapPostgreSQL.get(id);
genderMsSQL = genderMapMsSQL.get(id);
numChildrenPostgreSQL = numChildrenMapPostgreSQL.get(id);
numChildrenMsSQL = numChildrenMapMsSQL.get(id);
if (!idListMsSQL.contains(id))
TLptsLogger.logError(db + " does not contain id " + id, null);
else
idListMsSQL.remove(id);
if (occupationPostgreSQL == null)
TLptsLogger.logError("My SQL occupation for id " + id + " is null", null);
else
{
if (occupationMsSQL == null)
TLptsLogger.logError(db + " occupation for id " + id + " is null", null);
else
{
if (!occupationPostgreSQL.equals(occupationMsSQL))
TLptsLogger.logError("Occupation for id " + id + " does not match", null);
else
occupationMapMsSQL.remove(id);
}
}
if (genderPostgreSQL == null)
TLptsLogger.logError("My SQL gender for id " + id + " is null", null);
else
{
if (genderMsSQL == null)
TLptsLogger.logError(db + " gender for id " + id + " is null", null);
else
{
if (!genderPostgreSQL.equals(genderMsSQL))
{
if (genderMsSQL.equals(" "))
genderMsSQL = "";
if (!genderPostgreSQL.equals(genderMsSQL))
TLptsLogger.logError("Gender for id " + id + " does not match", null);
else
genderMapMsSQL.remove(id);
} else
genderMapMsSQL.remove(id);
}
}
if (numChildrenPostgreSQL == null)
TLptsLogger.logError("My SQL numChildren for id " + id + " is null", null);
else
{
if (numChildrenMsSQL == null)
TLptsLogger.logError(db + " numChildren for id " + id + " is null", null);
else
{
if (!numChildrenPostgreSQL.equals(numChildrenMsSQL))
TLptsLogger.logError("NumChildren for id " + id + " does not match", null);
else
numChildrenMapMsSQL.remove(id);
}
}
}
if (!idListMsSQL.isEmpty())
TLptsLogger.logError("Id List for " + db + " is not Empty", null);
if (!occupationMapMsSQL.isEmpty())
TLptsLogger.logError("Occupation Map for " + db + " is not Empty", null);
if (!genderMapMsSQL.isEmpty())
TLptsLogger.logError("Gender Map for " + db + " is not Empty", null);
if (!numChildrenMapMsSQL.isEmpty())
TLptsLogger.logError("NumChildren Map for " + db + " is not Empty", null);
System.out.println("***************************************************");
System.out.println("Data verified and passed for : " + db);
System.out.println("***************************************************");
if (db.equals("MS SQL"))
{
System.out.println("All test passed successfully...");
System.exit(0);
}
}
// ###############################################################################################################
// #### 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.");
// first we update the progres db, then on completion the ms sql db is updated.
if (connection.getId().equals(connectionPostgresId))
postgresqlSimpleUpdate(connection);
}
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 StatementListener implements ILptsFactoryStatementListener
{
public void newStatementProcessStarted(TLptsConnection connection)
{
}
public void newStatementCreated(TLptsConnection connection, TLptsStatement statement)
{
// after the updates, these statements are executed and used to verify the data
if (statement.getId().equals(msSqlSelectStatementId) ||
statement.getId().equals(postgreSqlSelectStatementId))
TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false);
}
public void newStatementFailed(TLptsConnection connection)
{
// this will cause a fail in the log listener
TLptsLogger.logError("Failed 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 has finished");
else
for (String rowSet : rowSetEvent.getRowSetsNotFinished())
System.out.println(rowSet + " did not finished");
// this is special case where we feed the new updated data in the progress db to the MS SQL db.
if (((TLptsConnection) rowSetEvent.getStatement().getConnection()).getId().equals(connectionPostgresId))
mssqlDynamicUpdate();
updateComplete++;
if (updateComplete == 2)
{
System.out.println("############################");
System.out.println("# Process Update Completed #");
System.out.println("############################");
getPostgreSqlData();
}
} catch (Exception e)
{
TLptsLogger.logError("Cannot get Connection Name", e);
}
}
if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.NEW_SELECT_RESULTSET && updateComplete == 2)
{
ResultSet rs = rowSetEvent.getRowSet();
try
{
if (rowSetEvent.getStatement().getId().equals(postgreSqlSelectStatementId))
{
while (rs.next())
{
idListPostgreSQL.add(rs.getInt(1));
occupationMapPostgreSQL.put(rs.getInt(1), rs.getString(2));
genderMapPostgreSQL.put(rs.getInt(1), rs.getString(3));
numChildrenMapPostgreSQL.put(rs.getInt(1), rs.getInt(4));
}
getMsSqlData();
} else if (rowSetEvent.getStatement().getId().equals(msSqlSelectStatementId))
{
while (rs.next())
{
idListMsSQL.add(rs.getInt(1));
occupationMapMsSQL.put(rs.getInt(1), rs.getString(2));
genderMapMsSQL.put(rs.getInt(1), rs.getString(3));
numChildrenMapMsSQL.put(rs.getInt(1), rs.getInt(4));
}
checkData("MS SQL");
}
} catch (SQLException sqle)
{
TLptsLogger.logError("SQL Exception", sqle.getLocalizedMessage(), sqle);
}
}
return false; // do not let dbJAPI process the result set.
}
}
}