Copyright 2009,2010, Lapetus Systems Ltd. (All rights reserved)

com.lapetus_ltd.api.db.control
Class TLptsFactoryStatement

java.lang.Object
  extended by com.lapetus_ltd.api.db.control.TLptsFactoryStatement

public class TLptsFactoryStatement
extends java.lang.Object

Class Description : This is the class responsible for the creation and control of the TLptsStatements.

This class, as with all factories, provides a STATIC function interface and cannot be instantiated.
TLptsFactoryStatement creates and keeps a list of all the statements in the system. These statements are either
generated programmatically or they can be loaded from a project file (TLptsFactoryProject}).

This class has a listener ILptsFactoryStatementListener, which should be implemented by the user application.
The implementing class then has the ability to control and react to the statement events generated by calls to
createNewStatement(com.lapetus_ltd.api.db.control.TLptsConnection, com.lapetus_ltd._2009.xml.types.XLptsDBStatementType). The examples below demonstrate this structuring and work flow.

Ideally an application would implement all the factory listeners ILptsFactoryConnectionListener, ILptsFactoryStatementListener
and ILptsFactoryRowSetListener. This would then provide the application with all the required control
at the correct time, within the multi-tasking environment of Connection, Statement and RowSet processing.

$LastChangedRevision: 1225 $
$LastChangedDate:: 2010-12-06 09:47:57#$


Field Summary
static int DATETIME
          Used by MS-SQL for DATE-TIME.
static java.lang.String JOIN_CROSS
           
static java.lang.String JOIN_FULL_OUTER
           
static java.lang.String JOIN_IMPLICIT
           
static java.lang.String JOIN_INNER
           
static java.lang.String JOIN_LEFT_OUTER
           
static java.lang.String JOIN_NATURAL
           
static java.lang.String JOIN_RIGHT_OUTER
           
static java.lang.String JOIN_UNION
           
static java.lang.String ON
           
static java.lang.String OPERATION_BETWEEN
           
static java.lang.String OPERATION_EQUAL
           
static java.lang.String OPERATION_GREATER_THAN
           
static java.lang.String OPERATION_GREATER_THAN_EQUAL
           
static java.lang.String OPERATION_IN
           
static java.lang.String OPERATION_IS_NOT_NULL
           
static java.lang.String OPERATION_IS_NULL
           
static java.lang.String OPERATION_LESS_THAN
           
static java.lang.String OPERATION_LESS_THAN_EQUAL
           
static java.lang.String OPERATION_LIKE
           
static java.lang.String OPERATION_NOT_EQUAL
           
static int TABLE_TYPE_OTHER
          Alias and Synonym types of tables for Statement processing.
static int TABLE_TYPE_SYSTEM
          SYSTEM type of tables for Statement processing.
static int TABLE_TYPE_TEMP
          Temporary type of tables for Statement processing.
static int TABLE_TYPE_USER
          TABLE type of tables for Statement processing.
static int TABLE_TYPE_VIEW
          VIEW type of tables for Statement processing.
 
Method Summary
static void addListener(ILptsFactoryStatementListener listener)
           Adds a listener that implements ILptsFactoryStatementListener to the listener list.
static void addToStatementList(TLptsStatement statement)
          Adds a new statement to the statement list.
static void createNewStatement(TLptsConnection connection, XLptsDBStatementType statementType)
          This function creates a TLptsStatement class and initialises all the relative properties.
static void createNewStatement(TLptsConnection connection, XLptsDBTypeOfStatementType statementType)
          Creates a new statement based on a connection and statement type.
static void executeOtherStatement(TLptsStatement stmt)
          Executes a statement other than SELECT, INSERT, UPDATE or DELETE, and which has no resultset.
static java.util.List<java.lang.String> getColumnSqlTypeStringList()
          Gets a list with all SQL Types as string.
static int getSqlTypeFromString(java.lang.String typeString)
          Gets the sql type by it's string value.
static java.lang.String getSqlTypeString(int type)
           Gets the SQL Type as a string from a java.sql.Type.
static java.lang.String getSqlTypeStringCapital(int type)
           Get the SQL Type in capital letters by setting the java.sql.Types.
static TLptsStatement getStatement(java.lang.String id)
          Gets a statement by it's id.
static java.util.List<TLptsStatement> getStatementList()
           Gets the statement list for all connections.
static java.util.List<TLptsStatement> getStatementList(TLptsConnection connection)
           Gets all the statements for a connection.
static int getStatementListSize()
           Gets the statement list size.
static boolean initiatePrepareStatement(TLptsStatement lptsStatement)
          Initiates a Prepare Statement on the TLptsStatement.
static void removeListener(ILptsFactoryStatementListener listener)
           Remove a listener that implements ILptsFactoryStatementListener from the list.
static void removeStatement(TLptsStatement stmt)
          Removes a statement from the statement list.
static TLptsDBStatementGroupType zgSG()
          Obfuscated, as it is not required by the application.
static void zI()
          Obfuscated, as it is not required by the application.
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Field Detail

DATETIME

public static final int DATETIME
Used by MS-SQL for DATE-TIME. This value is similar to java.sql.Types.TIMESTAMP, but is not included in the Types class.

See Also:
Constant Field Values

JOIN_CROSS

public static final java.lang.String JOIN_CROSS
See Also:
Constant Field Values

JOIN_FULL_OUTER

public static final java.lang.String JOIN_FULL_OUTER
See Also:
Constant Field Values

JOIN_IMPLICIT

public static final java.lang.String JOIN_IMPLICIT
See Also:
Constant Field Values

JOIN_INNER

public static final java.lang.String JOIN_INNER
See Also:
Constant Field Values

JOIN_LEFT_OUTER

public static final java.lang.String JOIN_LEFT_OUTER
See Also:
Constant Field Values

JOIN_NATURAL

public static final java.lang.String JOIN_NATURAL
See Also:
Constant Field Values

JOIN_RIGHT_OUTER

public static final java.lang.String JOIN_RIGHT_OUTER
See Also:
Constant Field Values

JOIN_UNION

public static final java.lang.String JOIN_UNION
See Also:
Constant Field Values

ON

public static final java.lang.String ON
See Also:
Constant Field Values

OPERATION_BETWEEN

public static final java.lang.String OPERATION_BETWEEN
See Also:
Constant Field Values

OPERATION_EQUAL

public static final java.lang.String OPERATION_EQUAL
See Also:
Constant Field Values

OPERATION_GREATER_THAN

public static final java.lang.String OPERATION_GREATER_THAN
See Also:
Constant Field Values

OPERATION_GREATER_THAN_EQUAL

public static final java.lang.String OPERATION_GREATER_THAN_EQUAL
See Also:
Constant Field Values

OPERATION_IN

public static final java.lang.String OPERATION_IN
See Also:
Constant Field Values

OPERATION_IS_NOT_NULL

public static final java.lang.String OPERATION_IS_NOT_NULL
See Also:
Constant Field Values

OPERATION_IS_NULL

public static final java.lang.String OPERATION_IS_NULL
See Also:
Constant Field Values

OPERATION_LESS_THAN

public static final java.lang.String OPERATION_LESS_THAN
See Also:
Constant Field Values

OPERATION_LESS_THAN_EQUAL

public static final java.lang.String OPERATION_LESS_THAN_EQUAL
See Also:
Constant Field Values

OPERATION_LIKE

public static final java.lang.String OPERATION_LIKE
See Also:
Constant Field Values

OPERATION_NOT_EQUAL

public static final java.lang.String OPERATION_NOT_EQUAL
See Also:
Constant Field Values

TABLE_TYPE_OTHER

public static final int TABLE_TYPE_OTHER
Alias and Synonym types of tables for Statement processing. Used by TLptsStatement.initTablesAndColumns(int).

See Also:
Constant Field Values

TABLE_TYPE_SYSTEM

public static final int TABLE_TYPE_SYSTEM
SYSTEM type of tables for Statement processing. On some systems, like Access, this contains the User Tables. Used by TLptsStatement.initTablesAndColumns(int).

See Also:
Constant Field Values

TABLE_TYPE_TEMP

public static final int TABLE_TYPE_TEMP
Temporary type of tables for Statement processing. Used by TLptsStatement.initTablesAndColumns(int).

See Also:
Constant Field Values

TABLE_TYPE_USER

public static final int TABLE_TYPE_USER
TABLE type of tables for Statement processing. In Access the user tables are under TABLE_TYPE_SYSTEM. Used by TLptsStatement.initTablesAndColumns(int).

See Also:
Constant Field Values

TABLE_TYPE_VIEW

public static final int TABLE_TYPE_VIEW
VIEW type of tables for Statement processing. Used by TLptsStatement.initTablesAndColumns(int).

See Also:
Constant Field Values
Method Detail

addListener

public static void addListener(ILptsFactoryStatementListener listener)

Adds a listener that implements ILptsFactoryStatementListener to the listener list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :

Example :

 

public class MyApp { private StatementListener listener;

public MyApp() { ... listener = new StatementListener() TLptsFactoryStatement.addListener(listener); } ... private myAppExit() { ... TLptsFactoryStatement.removeListener(listener); ... System.exit(0); } ... private class UpdateStatementListener implements ILptsFactoryStatementListener { public void newStatementProcessStarted(TLptsConnection connection) { }

public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) {

}

public void newStatementFailed(TLptsConnection connection) { }

public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) { } }

}

Parameters:
listener - is the ILptsFactoryStatementListener that is added to the list and receives notification of statement events.

addToStatementList

public static void addToStatementList(TLptsStatement statement)
Adds a new statement to the statement list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : No

Notes :
This is done internally in the application when a statement is created.
The application could use this to store a TLptsStatement in the statement factory list for later retrieval
with getStatement(String).

Parameters:
statement - to add in the statement list

createNewStatement

public static void createNewStatement(TLptsConnection connection,
                                      XLptsDBStatementType statementType)
This function creates a TLptsStatement class and initialises all the relative properties.
The SQL string is initialised to an empty string from this function.

Thread Safe : Yes

Spawns its own Thread : Yes (sort of), the new statement is queued on the statement thread.

May Return NULL : N/A

Notes :

Example :

 {
   ...
   selectDataStatements(postgresqlConnection, "testTable", "ID");
   ...
 }
 

...

private void selectDataStatements(TLptsConnection connection, String tableName, String columnName) { TLptsStatement statement = new TLptsStatement(connection,XLptsDBTypeOfStatementType.SELECT); // set the table and columns for selection within the SQL statement XLptsDBStatementTableType table = statement.getTableItemByName(tableName); table.setSelected(true); // just one column is selected to be output to the rowset. for(XLptsDBStatementColumnType column : statement.getColumnItemsForTable(table.getTableName())) if(column.getColumnName().equals(columnName)) column.setSelected(true);

statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); // no criteria processing is required. statement.setTitle(columnName); System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); TLptsFactoryStatement.createNewStatement(connection,statement); }

private class UpdateStatementListener 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); // this is a good place to execute the statement TLptsFactoryRowSet.executeSelectStatement(statement,1,0,false); // we want all the records. }

public void newStatementFailed(TLptsConnection connection) { TLptsLogger.logError("Failed to Create Statement for connection "+connection.getTitle(),null); }

public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) { } }

Parameters:
connection - The existing connection
statementType - This is the statement to add with all the settings required.

createNewStatement

public static void createNewStatement(TLptsConnection connection,
                                      XLptsDBTypeOfStatementType statementType)
Creates a new statement based on a connection and statement type.

Thread Safe : Yes

Spawns its own Thread : Yes (sort of), the new statement is queued on the statement thread.

May Return NULL : N/A

Notes :

Example :

 

... TLptsFactoryStatement.createNewStatement(connection,XLptsDBTypeOfStatementType.SELECT); ... private class statementListener implements ILptsFactoryStatementListener { public void newStatementProcessStarted(TLptsConnection connection) { }

public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) { if(statement.getTypeOfStatement().equals(XLptsDBTypeOfStatementType.SELECT)) { // set the table and columns for selecting XLptsDBStatementTableType table = statement.getTableItemByName(tableName); table.setSelected(true); //statement.setSelectOnAllColumns(table.getTable(),true); // this selects all columns statement.getColumnItemByName(tableName,columnName).setSelected(true); // just one column is set

statement.getCriteriaType().setType(XLptsDBCriteriaType.NONE); statement.setTitle(columnName);

System.out.println("SQL Statement " + statement.getTitle() + ": " + statement.getSqlStatementFormatted()); 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) { } }

Parameters:
connection - The existing connection
statementType - the type of statement

executeOtherStatement

public static void executeOtherStatement(TLptsStatement stmt)
Executes a statement other than SELECT, INSERT, UPDATE or DELETE, and which has no resultset.

Thread Safe : Yes.

Spawns its own Thread : NO.

May Return NULL : n/a

Notes :
This does not produce any resultsets, so it is not in the RowSet factory.
Use this to execute any statement that does not return a value and is not supported in the Rowset Factory.
In other words, use this if the statement is not SELECT, INSERT, UPDATE or DELETE.
Use this for DROP, CREATE etc.
Example :

 TLptsStatement createStatement = new TLptsStatement(connection,XLptsDBTypeOfStatementType.CREATE); // or OTHER
 

String sqlString = "CREATE TABLE `myTable`"; sqlString += "( myTableID INT PRIMARY KEY,"; sqlString += "myName VARCHAR(50) REFERENCES `clients` (`FirstName`),"; sqlString += "hasChild CHAR(1) CHECK (hasChild IN ('Y','N')),"; sqlString += "mySalary DECIMAL(10,2) NOT NULL,"; sqlString += "myInsuranceID VARCHAR(50) UNIQUE)";

createStatement.setSqlStatementUser(sqlString); createStatement.setSqlStatementModified(true); // do not automatically calculate it

TLptsFactoryStatement.createNewStatement(connection, createStatement); ... private class CreateStatementListener implements ILptsFactoryStatementListener { ... public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) { TLptsFactoryStatement.executeOtherStatement(statement); // no result produced, and is run in the current thread } ... }


Parameters:
stmt - The CREATE statement to be executed.

getColumnSqlTypeStringList

public static java.util.List<java.lang.String> getColumnSqlTypeStringList()
Gets a list with all SQL Types as string.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never, an empty list in the worst case.

 Notes :  List values are : Array,
                                   Big Integer,
                                   Binary,
                                   Bit,
                                   Blob,
                                   Boolean,
                                   Character,
                                   Clob,
                                   Data link,
                                   Date,
                                   Decimal,
                                   Distinct,
                                   Double,
                                   Float,
                                   Integer,
                                   Java Object,
                                   Long N.Var.Char,
                                   Long Var.Binary,
                                   Long Var.Char,
                                   N.Char,
                                   N.Clob,
                                   Null,
                                   Numeric,
                                   N.Var.Char,
                                   Other,
                                   Real,
                                   Reference,
                                   Row ID,
                                   Small Integer,
                                   SQL XML,
                                   Structure,
                                   Time,
                                   TimeStamp,
                                   Tiny Integer,
                                   Var.Binary,
                                   Var.Character
 

Example :
 sqlTypeExists("Long N.Var.Char");
 sqlTypeExists("var. date");
 ...
 private boolean sqlTypeExists(String type)
 {
    return TLptsFactoryStatement.getSQLColumnSqlTypeStringList().contains(type);
 }
 

Result 1: true Result 2: false

Returns:
a list with all SQL Types as string

getSqlTypeFromString

public static int getSqlTypeFromString(java.lang.String typeString)
Gets the sql type by it's string value.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :

Example :

 

//Create statement ... //Selects all column with sql type "Long N.Var.Char" for(XLptsDBStatementColumnType column : deleteStatement.getColumnListItem()) if(column.getSqlType()==TLptsFactoryStatement.getSqlTypeFromString("Long N.Var.Char")) column.setSelected(true); ...

Parameters:
typeString - the sql type as a string
Returns:
the java.sql.Types in integer value

getSqlTypeString

public static java.lang.String getSqlTypeString(int type)

Gets the SQL Type as a string from a java.sql.Type.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : No, it returns 'OTHER' when the type is not found.

Notes : Useful for GUI display of the Java SQL Type.

Example :

 

TLptsStatement statement = new TLptsStatement(connection,XLptsDBTypeOfStatementType.SELECT); ... System.out.println("This is a list of all Selected columns in the data source :"); for(XLptsDBStatementColumnType column : statement.getColumnListItem()) System.out.println("Name=" + column.getColumnName + ", SQL type="+TLptsFactoryStatement.getSqlTypeString(column.getSqlType()));

Parameters:
type - The java.sql.Type value.
Returns:
the sql type in string format with capital letters.

getSqlTypeStringCapital

public static java.lang.String getSqlTypeStringCapital(int type)

Get the SQL Type in capital letters by setting the java.sql.Types.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Yes

Notes :

Example :

 

TLptsStatement statement = new TLptsStatement(connection,XLptsDBTypeOfStatementType.SELECT); ... System.out.println("This is a list of all Selected column in the data source :"); for(XLptsDBStatementColumnType column : statement.getColumnListItem()) System.out.println("Name=" + column.getColumnName + ", SQL type="+TLptsFactoryStatement.getSqlTypeStringCapital(column.getSqlType()));

Parameters:
type - java.sql.Types
Returns:
the The string relating to the types.

getStatement

public static TLptsStatement getStatement(java.lang.String id)
Gets a statement by it's id.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Yes

Notes :

Example :

 private String statementId;
 ...
 // Create Statement and execute
 ...
 private class RowSetListener implements ILptsFactoryRowSetListener
 {
   public void rowEvent(TLptsRowEvent rowEvent)
   {
     if (rowEvent.getStatement().getId().equals(statementId))
     {
       if(rowEvent.getEventType()==TLptsRowEvent.EVENT_TYPE.PROCESSING_COMPLETE)
       {
         TLptsFactoryStatement.removeStatement(TLptsFactoryStatement.getStatement(itemsId));
       }
     }
   }
   public boolean processNewRowSetRows(TLptsRowSetEvent rowSetEvent)
   {
     return true;
   }
 }
 

Parameters:
id - The unique TLptsStatement ID
Returns:
The statement with the specified ID

getStatementList

public static java.util.List<TLptsStatement> getStatementList()

Gets the statement list for all connections.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never, an empty list in the worst case.

Notes : All the statements in the whole system.

Example :

 

private void closeAllStatements() { for(TLptsStatement statement : TLptsFactoryStatement.getStatementList()) { TLptsFactoryStatement.removeStatement(statement); // the listener is informed ILptsFactoryStatementListener.removedAndClosedStatement(TLptsConnection, TLptsStatement) on actual removal } }

Returns:
a list with statements for all connections

getStatementList

public static java.util.List<TLptsStatement> getStatementList(TLptsConnection connection)

Gets all the statements for a connection.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never, an empty list in the worst case.

Notes :

Example :

 

//Processing row sets for each statement and then remove statement from list ... TLptsFactoryStatement.removeStatement(statement); ... //After processing all statements we want to close the connection if(TLptsFactoryStatement.getStatementList(connection).size()==0) TLptsFactoryConnection.removeConnection(connection,false); else TLptsLogger.logWarning("Connection can not be removed, there are statements in process",null);

Parameters:
connection - is the connection to get all statements
Returns:
a list with all statements for a connection

getStatementListSize

public static int getStatementListSize()

Gets the statement list size.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :

Example :

 System.out.println("There are " + TLptsFactoryStatement.getStatementListSize() + " statements in the systems at the moment");
 

Returns:
get the size of the statement list

initiatePrepareStatement

public static boolean initiatePrepareStatement(TLptsStatement lptsStatement)
Initiates a Prepare Statement on the TLptsStatement.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :
This action does two things
(1) It creates a PrepareStatement from the declared driver.
(2) It verifies that the SQL string supplied is syntactically correct.

It is only required before executing a CREATE statement. (as shown below)

Example :

 //Create a statement
 ...
 TLptsFactoryStatement.createNewStatement(connection, statement);
 

...

private class statementListener implements ILptsFactoryStatementListener { public void newStatementProcessStarted(TLptsConnection connection) { }

public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) { if(statement.getTypeOfStatement()==XLptsDBTypeOfStatementType.CREATE) { if (!TLptsFactoryStatement.initiatePrepareStatement(statement)) return; statement.executeUpdate(); } }

public void newStatementFailed(TLptsConnection connection) { TLptsLogger.logError("Fail to Create Statement for connection "+connection.getTitle(),null); }

public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) { } }

Parameters:
lptsStatement - is the statement to initiate as a prepare statement
Returns:
true if initialization completed successfully, false otherwise

removeListener

public static void removeListener(ILptsFactoryStatementListener listener)

Remove a listener that implements ILptsFactoryStatementListener from the list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :

Example :

 

public class MyApp { private StatementListener listener;

public MyApp() { ... listener = new StatementListener() TLptsFactoryStatement.addListener(listener); } ... private myAppExit() { ... TLptsFactoryStatement.removeListener(listener); ... System.exit(0); } ... private class UpdateStatementListener implements ILptsFactoryStatementListener { public void newStatementProcessStarted(TLptsConnection connection) { }

public void newStatementCreated(TLptsConnection connection, TLptsStatement statement) {

}

public void newStatementFailed(TLptsConnection connection) { }

public void removedAndClosedStatement(TLptsConnection connection, TLptsStatement statement) { } } }

Parameters:
listener - is the ILptsFactoryStatementListener to remove from the list

removeStatement

public static void removeStatement(TLptsStatement stmt)
Removes a statement from the statement list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : N/A

Notes :
The statement is closed and the listener is informed ILptsFactoryStatementListener.removedAndClosedStatement(TLptsConnection, TLptsStatement) on actual removal.

Example :

 

// Processing row sets for each statement and then remove statement from list ... TLptsFactoryStatement.removeStatement(statement); ...

// After processing all statements we want to close the connection if(TLptsFactoryStatement.getStatementList(connection).size()==0) TLptsFactoryConnection.removeConnection(connection,false); else TLptsLogger.logWarning("Connection can not be removed, there are statements in process",null);

Parameters:
stmt - is the TLptsStatement to remove from statement list

zgSG

public static TLptsDBStatementGroupType zgSG()
Obfuscated, as it is not required by the application.


zI

public static void zI()
Obfuscated, as it is not required by the application.



Copyright 2009,2010, Lapetus Systems Ltd. (All rights reserved)