ExampleDataDelete.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.TLptsDBUpInsDelWhereType;
import com.lapetus_ltd.api.db.xml.types.TLptsDriverType;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
// Class Description : This is an example of how to use dbJAPI to delete records from a data source.
//
// This module is run directly after the INSERT AND UPDATE, so that the records inserted and updated can be deleted.
// In this example we depict two ways of deleting data, namely programmatic and using another SELECT statement.
// In both cases we use the XLptsDBUpInsDelWhereType to specify the criteria for the deletion. In both cases there is
// a source string. For the programmatic the string is used as the static criteria and for the statement type it is
// used for the name of the statement to use as a data set for the delete.
// In other words, with the programmatic the string is used like in the WHERE of a SELECT statement against the operation
// and column name.
// For the statement type, the other select statement is executed and the resulting set of data is used to select the rows
// to be deleted in the DELETE statement.
//
public class ExampleDataDelete
{
private String connectionMSSqlId = null;
private String connectionPostgresId = null;
public static void main(String[] args)
{
new ExampleDataDelete();
}
public ExampleDataDelete()
{
// 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 DeleteStatementListener());
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!!
}
// ##############################################################################################################
// See ExampleConnectionUsage.connectToDB for an example of how to connect to other Data Sources
// ##############################################################################################################
}
// ####################################################################################
// DELETE WITH PROGRAMMATIC OR STATIC (WHERE)
// ####################################################################################
private void mssqlSimpleDelete()
{
//Find the Connection to delete data
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (connection == null)
return;
//Create the statement and set the Type of Statement to Delete.
TLptsStatement deleteStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE);
// set this to be compatible with all DBs
deleteStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
// ResultSet Concurrency must be updatable
deleteStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
// set the table and columns for selecting
XLptsDBStatementTableType table = deleteStatement.getTableItemByName("testTable");
table.setSelected(true);
XLptsDBStatementColumnType col = deleteStatement.getColumnItemByName(table.getTableName(), "ID");
if (col != null)
{
// we want to delete every record with the ID equals to 181
TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.PROGRAMMATIC, "181");
//Set the operation we want as ILPtsStatement.OPERATION_*
whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
// if more than one WHERE is added then AND/OR needs to be set
whereType.setAndConnector(true);
col.setUpInsDelParameterItem(whereType);
}
deleteStatement.setTitle("Delete Statement");
System.out.println("SQL Statement " + deleteStatement.getTitle() + ": " + deleteStatement.getSqlStatementFormatted());
TLptsFactoryRowSet.executeDynamicDelete(deleteStatement);
}
// ####################################################################################
// DELETE USING THE DATA FROM ANOTHER LAPETUS STATEMENT
// ####################################################################################
private void postgreSqlDynamicDelete()
{
// this connection is used for creating the SELECT that will be criteria data set for DELETING from
// the Postgres DB. This TLptsStatement is called "ID" and is used as the WHERE TYPE below.
TLptsConnection mssqlConnection = TLptsFactoryConnection.getConnection(connectionMSSqlId);
if (mssqlConnection == null)
return;
//Create Lapetus_Statement to get data
selectDataStatements(mssqlConnection, "ID");
//Find the Connection to delete data
TLptsConnection connection = TLptsFactoryConnection.getConnection(connectionPostgresId);
if (connection == null)
return;
TLptsStatement deleteStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.DELETE);
deleteStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
deleteStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
XLptsDBStatementTableType table = deleteStatement.getTableItemByName("testTable");
table.setSelected(true);
XLptsDBStatementColumnType col = deleteStatement.getColumnItemByName(table.getTableName(), "ID");
if (col != null)
{
// this is the select statement from the MS-SQL DB.
TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT, "ID");
whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
whereType.setAndConnector(true);
col.setUpInsDelParameterItem(whereType);
}
deleteStatement.setTitle("Delete Statement");
System.out.println("SQL Statement " + deleteStatement.getTitle() + ": " + deleteStatement.getSqlStatementFormatted());
TLptsFactoryRowSet.executeDynamicDelete(deleteStatement); // this executes the SELECT together with the DELETE
}
private void selectDataStatements(TLptsConnection connection, String columnName)
{
TLptsStatement statement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);
// only one column is selected
statement.setSqlStatementUser("SELECT [testTable].[ID] FROM [testTable] WHERE [testTable].[Occupation] <> N'ΟΙΚΙΑΚΑ'");
statement.setSqlStatementModified(true);
statement.setTitle(columnName);
statement.setExecutable(false); // just in case a statement listener every want to execute this
System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted());
// we create it here, but it is executed with the delete as it is related to that
// in other words we do not need to execute it
TLptsFactoryStatement.createNewStatement(connection, statement);
}
// ####################################################################################
// 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;
}
// ###############################################################################################################
// #### 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(connectionMSSqlId))
//MS SQL simple delete
mssqlSimpleDelete();
}
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 DeleteStatementListener implements ILptsFactoryStatementListener
{
public void newStatementProcessStarted(TLptsConnection connection)
{
}
public void newStatementCreated(TLptsConnection connection, TLptsStatement statement)
{
if (statement.getTypeOfStatement() == XLptsDBTypeOfStatementType.SELECT)
TLptsLogger.logMessage("Select Statement Created for connection " + connection.getTitle(), null);
}
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)
{
}
public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent)
{
if (rowSetEvent.getEventType() == TLptsRowSetEvent.EVENT_TYPE.DELETE_COMPLETE)
{
try
{
System.out.println("Statement " + rowSetEvent.getStatement().getTitle() + " " + ((TLptsConnection) rowSetEvent.getStatement().getConnection()).getTitle() + " delete execution complete...");
System.out.println(rowSetEvent.getRows() + " rows deleted");
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");
if (((TLptsConnection) rowSetEvent.getStatement().getConnection()).getId().equals(connectionMSSqlId))
//PostgresSQL dynamic delete
postgreSqlDynamicDelete();
} catch (SQLException e)
{
TLptsLogger.logError("Cannot get Connection Name", e);
}
}
return true;
}
}
}