Test code for JOIN
//
// 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.*;
import junit.framework.TestCase;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.ResourceBundle;
// ######################################################################################################
// #### Test code for the dbJAPI build process. This code will stop the build process on ERROR ####
// ######################################################################################################
//
// Class Description : This class tests all the JOIN types for correct functionality.
//
// This module tests the JOIN types (INNER, CROSS, NATURAL) against our MySql DB.
// There are two sets of JOINS for each kind tested. The one is written by hand and the other is generated from
// the settings in the criteria items. The two sets should be the same. They are run and checked against the records
// produced by each.
//
public class TestJoinClauses extends TestCase
{
private String connectionMySqlId = "";
private String joinStatementId = "";
private String crossJoinId = "";
private String leftOuterJoinId = "";
private String innerJoinId = "";
private String naturalJoinId = "";
private List<Integer> statementList = new LinkedList<Integer>();
private List<XLptsDBJoinType> joinList = new LinkedList<XLptsDBJoinType>();
private int joinIndex = 0;
static public void main(String[] args)
{
TestJoinClauses jop = new TestJoinClauses();
jop.testJOINS();
}
public void testJOINS()
{
// 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;
initJoinList();
//Connect To MySQL
connectionType = connectToDB();
if (connectionType != null)
{
connectionMySqlId = connectionType.getId();
TLptsFactoryConnection.initiateConnection(connectionType); // This generates another thread!!
}
}
// ####################################################################################
// C O N N E C T I O N
// ####################################################################################
private TLptsDBConnectionType connectToDB()
{
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("MY-SQL 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";
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;
}
private void initJoinList()
{
joinList.add(XLptsDBJoinType.CROSS);
joinList.add(XLptsDBJoinType.INNER);
joinList.add(XLptsDBJoinType.NATURAL);
}
// ####################################################################################
// CREATE THE JOIN STATEMENTS BY HAND
// ####################################################################################
private void createCrossJoinSQL(TLptsConnection connection)
{
TLptsStatement crossJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
crossJoinStatement.setSqlStatementUser("SELECT " +
"`account`.`idAccount`, " +
"`account`.`client_id`, " +
"`account`.`type`, " +
"`account`.`balance`, " +
"`clients`.`idClients`, " +
"`clients`.`First Name`, " +
"`clients`.`Last Name`, " +
"`clients`.`Address`, " +
"`clients`.`Phone Number` " +
"FROM `account` CROSS JOIN `clients` " +
"ON `clients`.`idClients` = `account`.`client_id`");
crossJoinStatement.setSqlStatementModified(true);
crossJoinStatement.setTitle("Cross Join Statement");
crossJoinId = crossJoinStatement.getId();
System.out.println("SQL Statement " + crossJoinStatement.getTitle() + ": " + crossJoinStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, crossJoinStatement);
}
// private void createLeftOuterJoinSQL(TLptsConnection connection)
// {
// TLptsStatement leftOuterJoin = new TLptsStatement(connection,XLptsDBTypeOfStatementType.SELECT);
// leftOuterJoin.setSqlStatementUser("SELECT " +
// "`account`.`idAccount`, " +
// "`account`.`client_id`, " +
// "`account`.`type`, " +
// "`account`.`balance`, " +
// "`clients`.`idClients`, " +
// "`clients`.`First Name`, " +
// "`clients`.`Last Name`, " +
// "`clients`.`Address`, " +
// "`clients`.`Phone Number` " +
// "FROM `account` LEFT OUTER JOIN `clients` " +
// "ON `clients`.`idClients` = `account`.`client_id`");
//
// leftOuterJoin.setSqlStatementModified(true);
// leftOuterJoin.setTitle("Left Outer Statement");
// leftOuterJoinId = leftOuterJoin.getId();
//
// System.out.println("SQL Statement " + leftOuterJoin.getTitle() + ": " + leftOuterJoin.getSqlStatementFormatted());
// TLptsFactoryStatement.createNewStatement(connection,leftOuterJoin);
// }
private void createInnerJoinSQL(TLptsConnection connection)
{
TLptsStatement innerJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
innerJoinStatement.setSqlStatementUser("SELECT " +
"`account`.`idAccount`, " +
"`account`.`client_id`, " +
"`account`.`type`, " +
"`account`.`balance`, " +
"`clients`.`idClients`, " +
"`clients`.`First Name`, " +
"`clients`.`Last Name`, " +
"`clients`.`Address`, " +
"`clients`.`Phone Number` " +
"FROM `account` INNER JOIN `clients` " +
"ON `clients`.`idClients` = `account`.`client_id`");
innerJoinStatement.setSqlStatementModified(true);
innerJoinStatement.setTitle("Implicit Statement");
innerJoinId = innerJoinStatement.getId();
System.out.println("SQL Statement " + innerJoinStatement.getTitle() + ": " + innerJoinStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, innerJoinStatement);
}
private void createNaturalJoinSQL(TLptsConnection connection)
{
TLptsStatement naturalJoinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
naturalJoinStatement.setSqlStatementUser("SELECT " +
"`account`.`idAccount`, " +
"`account`.`client_id`, " +
"`account`.`type`, " +
"`account`.`balance`, " +
"`clients`.`idClients`, " +
"`clients`.`First Name`, " +
"`clients`.`Last Name`, " +
"`clients`.`Address`, " +
"`clients`.`Phone Number` " +
"FROM `account` NATURAL JOIN `clients`");
naturalJoinStatement.setSqlStatementModified(true);
naturalJoinStatement.setTitle("Natural Statement");
naturalJoinId = naturalJoinStatement.getId();
System.out.println("SQL Statement " + naturalJoinStatement.getTitle() + ": " + naturalJoinStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, naturalJoinStatement);
}
// ####################################################################################
// CREATE THE JOIN STATEMENTS WITH THE DB-JAPI INTERFACE
// ####################################################################################
// this JOIN takes place on the client id, which is called client_id in the account table
// and idClients in the the Clients table.
private void createJoinStatement(TLptsConnection connection, XLptsDBJoinType joinType)
{
TLptsStatement joinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
joinStatement.getTableItemByName("clients").setSelected(true);
joinStatement.setSelectOnAllColumns("clients", true);
joinStatement.getTableItemByName("account").setSelected(true);
joinStatement.setSelectOnAllColumns("account", true);
joinStatement.getCriteriaType().setType(XLptsDBCriteriaType.JOIN);
TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType();
spit.setJoinType(joinType);
if (!joinType.equals(XLptsDBJoinType.NATURAL))
{
spit.setJoinOnUsingType(XLptsDBJoinOnUsingType.ON);
TLptsDBStatementJoinType spjot = new TLptsDBStatementJoinType();
spjot.setFromColumn(joinStatement.getColumnItemByName("clients", "idClients"));
spjot.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
spjot.addToColumnListItem(joinStatement.getColumnItemByName("account", "client_id"));
spit.addJoinOnListItem(spjot);
}
spit.setJoinLeftTable(joinStatement.getTableItemByName("account"));
spit.setJoinRightTable(joinStatement.getTableItemByName("clients"));
((TLptsDBStatementCriteriaType) joinStatement.getCriteriaType()).addCriteriaListItem(spit);
joinStatement.setTitle("Join");
joinStatement.setExecutable(true);
joinStatementId = joinStatement.getId();
System.out.println("SQL Statement " + joinStatement.getTitle() + ": " + joinStatement.getSqlStatementFormatted());
TLptsFactoryStatement.createNewStatement(connection, joinStatement);
}
// ###############################################################################################################
// #### 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 (connection.getId().equals(connectionMySqlId))
createCrossJoinSQL(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)
{
TLptsFactoryRowSet.executeSelectStatement(statement, 1, 0, false);
}
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
// ###############################################################################################################
private class RowSetListener implements ILptsFactoryRowSetListener
{
public void rowEvent(TLptsRowEvent rowEvent)
{
if (rowEvent.getStatement().getId().equals(joinStatementId))
{
if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.MOVED_TO_NEXT_RECORD)
{
if (statementList.contains(((Integer) rowEvent.getRowObjectList().get(0))))
statementList.remove(((Integer) rowEvent.getRowObjectList().get(0)));
else
TLptsLogger.logError("Object is not in list for join with id: " + rowEvent.getRowObjectList().get(0), null);
}
if (rowEvent.getEventType() == TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE)
{
if (statementList.isEmpty())
{
System.out.println("################################################");
System.out.println("#Test completed successfully for " + joinList.get(joinIndex - 1) + " #");
System.out.println("################################################");
// if(leftOuterJoinId.equals(""))
// createLeftOuterJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE()));
// else
if (innerJoinId.equals(""))
createInnerJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE()));
else if (naturalJoinId.equals(""))
createNaturalJoinSQL(((TLptsConnection) rowEvent.getStatement().getConnectionWE()));
else
System.exit(0);
}
}
}
}
public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent)
{
if (rowSetEvent.getStatement().getId().equals(crossJoinId) ||
rowSetEvent.getStatement().getId().equals(leftOuterJoinId) ||
rowSetEvent.getStatement().getId().equals(innerJoinId) ||
rowSetEvent.getStatement().getId().equals(naturalJoinId))
{
try
{
statementList.clear();
while (rowSetEvent.getRowSet().next())
statementList.add(rowSetEvent.getRowSet().getInt(1));
createJoinStatement(((TLptsConnection) rowSetEvent.getStatement().getConnection()), joinList.get(joinIndex++));
} catch (SQLException e)
{
TLptsLogger.logError("Sql Exception", e.getLocalizedMessage(), e);
}
return false;
}
return true;
}
}
}