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

com.lapetus_ltd.api.db.xml.types
Class TLptsDBUpInsDelWhereType

java.lang.Object
  extended by com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelType
      extended by com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelWhereType
          extended by com.lapetus_ltd.api.db.xml.types.TLptsDBUpInsDelWhereType

public class TLptsDBUpInsDelWhereType
extends XLptsDBUpInsDelWhereType

Class Description : One of the two classes that extend XLptsDBUpInsDelType for UPDATE, DELETE and INSERT commands.

The two classes that extend XLptsDBUpInsDelType are TLptsDBUpInsDelSetType and TLptsDBUpInsDelWhereType.

Both of these classes are used in the process of UPDATING, whereas only TLptsDBUpInsDelWhereType is used for DELETING and

and only TLptsDBUpInsDelSetType is used for INSERTING data.

So lets go into how it works in more detail. Both these classes are set against a column in a data set or statement.
The column is either used to be updated or as a pointer to which records are to be updated (like for the update or delete).
The column that is to have data inserted or updated against it is the SET column and uses the TLptsDBUpInsDelSetType in the
XLptsDBStatementColumnType.setUpInsDelParameterItem(com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelType) function. The column that specifies
which records (or rows) are to be updated is the WHERE column and needs the TLptsDBUpInsDelWhereType set in the
XLptsDBStatementColumnType.setUpInsDelParameterItem(com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelType) function.

Let us look at a diagrammatic depiction of how to use the two XLptsDBUpInsDelType class inheritors.

 In an UPDATE scenario in the table below we want to update the COLUMN 2 values and use COLUMN 5 to specify which rows to update.

                                               MyTable
                                TLptsStatement extends XLptsDBStatementType
                                                 |
           ------------------------------------------------------------------------------------
           |              |              |                |                 |                 |
        Column 1       Column 2       Column 3        Column 4           Column 5          Column 6
                          |                                                 |
                TLptsDBUpInsDelSetType                           TLptsDBUpInsDelWhereType

 

Only column 2's values will be affected by the update. Column 5 will not be affected. The WHERE on column 5 specifies which
values (rows) of column 2 will be updated, with the values specified in the TLptsDBUpInsDelSetType of column 2.
Both of the columns have data specified against them as source data XLptsDBUpInsDelType.getSourceType() and XLptsDBUpInsDelType.getSourceString().
But they are used in different ways. TLptsDBUpInsDelSetType uses the data to update each row of the selected rowset, and
TLptsDBUpInsDelWhereType uses it to select the rows or records to update.
Now, this is where it gets tricky but powerful, we can select a set of data from another SELECT statement and use it in both
cases for either updating or selecting (WHERE) rows of data.
Please note the following: If we have selected 100 rows for updating (100 rows satisfied the WHERE statement or there are 100 values
in the source data set) and there are 150 values in the dataset that is to be used by TLptsDBUpInsDelSetType to update the rows, then
only the first 100 will be updated and a warning will be logged in TLptsLogger.
This also applies the other way around, in other words the shortest set is used to specify the number of rows to update.
It also applies to more complex situations, like if all columns are to be updated by different sets of different lengths.
The shortest dataset governs the number of rows to be updated.

If no TLptsDBUpInsDelWhereType is specified, then all records are selected for UPDATE or DELETE.

If many columns are selected in the source Statement or Stored procedure, the first one is used for the relative operation.

The sources types are PROGRAMMATIC (from within the code), from another TLptsStatement or from a database STORED PROCEDURE.
In the later two cases we need to specify the name of the statement or stored procedure in XLptsDBUpInsDelType.setSourceString(java.lang.String).
And the relative type needs to be set in XLptsDBUpInsDelType.setSourceType(com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelSourceType).

Below is the code for the above diagram:


 // we will assume that the two SELECT statements called COLUMN_2_DATA and COLUMN_5_DATA, already exist
 // lets also assume that they have selected one column each, that has relative data for the UPDATE and WHERE.

 TLptsStatement updateStatement = new TLptsStatement(connection,XLptsDBTypeOfStatementType.UPDATE);
 updateStatement.setXResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
 updateStatement.setXResultSetConcurrency(ResultSet.CONCUR_UPDATABLE);
 XLptsDBStatementTableType table =  updateStatement.getTableItemByName("MyTable");
 table.setSelected(true);

 XLptsDBStatementColumnType column2,column5;

 column2 = updateStatement.getColumnItemByName(table.getTableName(),"COLUMN_2");
 if(column2 != null)
 {
   TLptsDBUpInsDelSetType setType = new TLptsDBUpInsDelSetType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT,"COLUMN_2_DATA");
   column2.setUpInsDelParameterItem(setType);
   column2.getUpInsDelParameterItem().setCharset("UTF-16LE");  // this is not required in most cases (see TestUPDATE)
 }

 column5 = updateStatement.getColumnItemByName(table.getTableName(),"COLUMN_5");
 if(column5 != null)
 {
   TLptsDBUpInsDelWhereType whereType = new TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType.LPTS_STATEMENT,"COLUMN_5_DATA");
   whereType.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
   whereType.setAndConnector(true);
   column5.setUpInsDelParameterItem(whereType);
 }
 updateStatement.setTitle("Update Statement");
 System.out.println("SQL Statement " + updateStatement.getTitle() + ": " + updateStatement.getSqlStatementFormatted());

 TLptsFactoryRowSet.executeDynamicUpdate(updateStatement);

 Note: Look at the ExampleDataUpdate and TestUPDATE for more details and complete code with comments.

 

$LastChangedRevision: 1165 $
$LastChangedDate:: 2010-11-03 10:37:03#$


Field Summary
 
Fields inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelWhereType
andConnector, betweenOther, operation
 
Fields inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelType
charset, columnId, columnIndex, columnName, id, sourceString, sourceType, sqlType, tableId, tableName
 
Constructor Summary
TLptsDBUpInsDelWhereType()
           This constructor initiates the UpInsDelType.
TLptsDBUpInsDelWhereType(XLptsDBStatementColumnType sct)
           This constructor initiate defaults and set Column details from TLptsDBStatementColumnType.
TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType sourceType, java.lang.String sourceString)
           This constructor initiates the UpInsDelType.
TLptsDBUpInsDelWhereType(XLptsDBUpInsDelWhereType uidt)
           This constructor copies the UpInsDelType information from an TLptsDBUpInsDelWhereType.
 
Method Summary
 boolean equals(java.lang.Object obj)
           Check for equal TLptsDBUpInsDelWhereType objects.
 
Methods inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelWhereType
getBetweenOther, getOperation, isAndConnector, setAndConnector, setBetweenOther, setOperation
 
Methods inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBUpInsDelType
getCharset, getColumnId, getColumnIndex, getColumnName, getId, getSourceString, getSourceType, getSqlType, getTableId, getTableName, setCharset, setColumnId, setColumnIndex, setColumnName, setId, setSourceString, setSourceType, setSqlType, setTableId, setTableName
 
Methods inherited from class java.lang.Object
clone, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

TLptsDBUpInsDelWhereType

public TLptsDBUpInsDelWhereType()

This constructor initiates the UpInsDelType.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never.

Notes : Default Constructor set id, type as NONE, operation as EQUAL, set as false, and as false, sqlType and sqlSubstituteType to -11 and other variables are set to empty string.

Example :


 TLptsDBUpInsDelWhereType upInsDelType = new TLptsDBUpInsDelWhereType();

 


TLptsDBUpInsDelWhereType

public TLptsDBUpInsDelWhereType(XLptsDBStatementColumnType sct)

This constructor initiate defaults and set Column details from TLptsDBStatementColumnType.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never.

Notes : Use this function every time you need to copy or instantiate a type TLptsDBUpInsDelWhereType class with TLptsDBStatementColumnType information.

Example :


 public void example(TLptsDBStatementColumnType sct)
 {
   TLptsDBUpInsDelWhereType upInsDelType = new TLptsDBUpInsDelWhereType(sct);
 }

 }

Parameters:
sct - the class object to copy.

TLptsDBUpInsDelWhereType

public TLptsDBUpInsDelWhereType(XLptsDBUpInsDelSourceType sourceType,
                                java.lang.String sourceString)

This constructor initiates the UpInsDelType.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never.

Notes : Default Constructor set id, type as NONE, operation as EQUAL, set as false, and as false, sqlType and sqlSubstituteType to -11 and other variables are set to empty string.

Example :


 TLptsDBUpInsDelWhereType upInsDelType = new TLptsDBUpInsDelWhereType();

 


TLptsDBUpInsDelWhereType

public TLptsDBUpInsDelWhereType(XLptsDBUpInsDelWhereType uidt)

This constructor copies the UpInsDelType information from an TLptsDBUpInsDelWhereType.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never.

Notes : Use this function every time you need to copy or instantiate a type TLptsDBUpInsDelWhereType class.

Example :


 public void example(TLptsDBUpInsDelWhereType uidt)
 {
   TLptsDBUpInsDelWhereType upInsDelType = new TLptsDBUpInsDelWhereType(uidt);
 }

 

Parameters:
uidt - the class object to copy.
Method Detail

equals

public boolean equals(java.lang.Object obj)

Check for equal TLptsDBUpInsDelWhereType objects.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : n/a

Notes : Use this to compare two TLptsDBUpInsDelWhereType objects by there id's.

Example :


 TLptsDBUpInsDelWhereType upInsDelType1 = new TLptsDBUpInsDelWhereType();
 TLptsDBUpInsDelWhereType upInsDelType2 = new TLptsDBUpInsDelWhereType();
 return upInsDelType1.equals(upInsDelType2);

 

Overrides:
equals in class java.lang.Object
Parameters:
obj - the DB workspace type
Returns:
true if they are equal, else false.


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