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

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

java.lang.Object
  extended by com.lapetus_ltd._2009.xml.types.XLptsDBStatementJoinType
      extended by com.lapetus_ltd.api.db.xml.types.TLptsDBStatementJoinType

public class TLptsDBStatementJoinType
extends XLptsDBStatementJoinType

Class Description : This initialises the JOIN type for the JOIN select processing.

This is set together with XLptsDBStatementCriteriaItemType.setJoinOnUsingType(com.lapetus_ltd._2009.xml.types.XLptsDBJoinOnUsingType) and XLptsDBStatementCriteriaItemType.setJoinType(com.lapetus_ltd._2009.xml.types.XLptsDBJoinType).
Depending on the type of JOIN the relative parameters need to be set. As an example look at the following SQL string.
JOIN: Return rows when there is at least one match in both tables LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table FULL JOIN: Return rows when there is a match in one of the tables

 Let us take the following two tables as examples for the JOINS below:

 The "Clients" table:
 C_Id LastName   FirstName Address       City
 1    Jenson     Bif       Fifth Ave 10  Some Place
 2    Peterson   Bill      Street 23     Some Place
 3    Johnasson  Kole      Street 20     Some Place

 The "Orders" table:
 O_Id OrderNo  C_Id
 1    12345    3
 2    23456    3
 3    34567    1
 4    45678    1
 5    56789    15

 SELECT Clients.LastName, Clients.FirstName, Orders.OrderNo
 FROM Clients INNER JOIN Orders ON Clients.C_Id=Orders.C_Id ORDER BY Clients.LastName
  RESULT :
  LastName  FirstName OrderNo
  Jenson    Bif       34567
  Jenson    Bif       45678
  Johnasson Kole      12345
  Johnasson Kole      23456

 SELECT Clients.LastName, Clients.FirstName, Orders.OrderNo
 FROM Clients FULL JOIN Orders ON Clients.C_Id=Orders.C_Id ORDER BY Clients.LastName
  RESULT :
  LastName  FirstName OrderNo
  Jenson    Bif       34567
  Jenson    Bif       45678
  Johnasson Kole      12345
  Johnasson Kole      23456
  Peterson  Bill
                      56789

 SELECT Clients.LastName, Clients.FirstName, Orders.OrderNo
 FROM Clients LEFT JOIN Orders ON Clients.C_Id=Orders.C_Id ORDER BY Clients.LastName
  RESULT :
  LastName  FirstName OrderNo
  Jenson    Bif       34567
  Jenson    Bif       45678
  Johnasson Kole      12345
  Johnasson Kole      23456
  Peterson  Bill   

 SELECT Clients.LastName, Clients.FirstName, Orders.OrderNo
 FROM Clients RIGHT JOIN Orders ON Clients.C_Id=Orders.C_Id ORDER BY Clients.LastName
  RESULT :
  LastName   FirstName   OrderNo
  Jenson     Bif         34567 
  Jenson     Bif         45678
  Johnasson  Kole        12345
  Johnasson  Kole        23456
                         56789

 The db-JAPI code that corresponds to the above SELECT JOIN statements is as follows:

 private void createJoinStatement(TLptsConnection connection, XLptsDBJoinType joinType)
 {
   // joinType = XLptsDBJoinType.CROSS, XLptsDBJoinType.FULL_OUTER, XLptsDBJoinType.INNER, XLptsDBJoinType.NATURAL, etc
 
   TLptsStatement joinStatement = new TLptsStatement(connection, XLptsDBTypeOfStatementType.SELECT);

   joinStatement.getTableItemByName("clients").setSelected(true);
   joinStatement.setSelectOnAllColumns("clients",true);
   joinStatement.getTableItemByName("Orders").setSelected(true);
   joinStatement.setSelectOnAllColumns("Orders",true);

   joinStatement.getCriteriaType().setType(XLptsDBCriteriaType.JOIN);

   TLptsDBStatementCriteriaItemType spit = new TLptsDBStatementCriteriaItemType();
   spit.setJoinLeftTable(joinStatement.getTableItemByName("Clients"));
   spit.setJoinRightTable(joinStatement.getTableItemByName("Orders"));
   spit.setJoinType(joinType);
   spit.setJoinOnUsingType(XLptsDBJoinOnUsingType.ON);
   TLptsDBStatementJoinType spjot = new TLptsDBStatementJoinType();
   spjot.setFromColumn(joinStatement.getColumnItemByName("Clients","C_Id"));
   spjot.setOperation(TLptsFactoryStatement.OPERATION_EQUAL);
   spjot.addToColumnListItem(joinStatement.getColumnItemByName("Orders","C_Id")); // if the operation is IN, can be many
   spit.addJoinOnListItem(spjot);

   ((TLptsDBStatementCriteriaType) joinStatement.getCriteriaType()).addCriteriaListItem(spit);

   joinStatement.setSqlStatementExtension("ORDER BY \"Clients.LastName\"");      // formatted for ODBC "-"
   joinStatement.setTitle("Join");
   joinStatement.setExecutable(true);
   joinStatementId = joinStatement.getId();

   System.out.println("SQL Statement " + joinStatement.getTitle() + ": " + joinStatement.getSqlStatementFormatted());
   TLptsFactoryStatement.createNewStatement(connection,joinStatement);
 }

 

$LastChangedRevision: 1052 $
$LastChangedDate:: 2010-08-27 12:21:22#$


Field Summary
 
Fields inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBStatementJoinType
fromColumn, isAndConnector, operation, toColumnListItem
 
Constructor Summary
TLptsDBStatementJoinType()
           This constructor initiates parameter join type and initializes the toColumnList list.
TLptsDBStatementJoinType(XLptsDBStatementColumnType fromCol, java.lang.String op, boolean isAnd, java.util.List<XLptsDBStatementColumnType> list)
           This constructor initiates the TLptsDBStatementJoinType from the arguments supplied.
TLptsDBStatementJoinType(XLptsDBStatementJoinType spjot)
           This constructor copies the parameter join type information from another XLptsDBStatementJoinType.
 
Method Summary
 void addToColumnListItem(XLptsDBStatementColumnType item)
           Adds the column to the toColumn list.
 boolean equals(java.lang.Object obj)
           Check for equal TLptsDBStatementJoinType objects.
 java.util.List<XLptsDBStatementColumnType> getToColumnListItem()
           Returns a copy of the actual list.
 int getToColumnListItemSize()
           Returns the size of the actual list.
 void removeToColumnListItem(XLptsDBStatementColumnType item)
           Removes the column from the toColumn list.
 
Methods inherited from class com.lapetus_ltd._2009.xml.types.XLptsDBStatementJoinType
getFromColumn, getOperation, isIsAndConnector, setFromColumn, setIsAndConnector, setOperation
 
Methods inherited from class java.lang.Object
clone, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
 

Constructor Detail

TLptsDBStatementJoinType

public TLptsDBStatementJoinType()

This constructor initiates parameter join type and initializes the toColumnList list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : n/a.

Notes : Default Constructor set fromColumn null, operation as empty string and initialize toColumn list.

Example :


 TLptsDBStatementJoinType pjot = new TLptsDBStatementJoinType();

 


TLptsDBStatementJoinType

public TLptsDBStatementJoinType(XLptsDBStatementColumnType fromCol,
                                java.lang.String op,
                                boolean isAnd,
                                java.util.List<XLptsDBStatementColumnType> list)

This constructor initiates the TLptsDBStatementJoinType from the arguments supplied.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never.

Notes : Use this functions if you want to set fromColumn, operation and the toColumn list.

Example :


 TLptsDBStatementColumnType fromColumn = new TLptsDBStatementColumnType();
 String operation = TLptsStatement.OPERATION_GREATER_THAN;
 LinkedList toColumnList;
 TLptsDBStatementJoinType pjot = new TLptsDBStatementJoinType(fromColumn,operation,toColumnList);

 

Parameters:
fromCol - the from column.
op - the operand
list - the toColumnList

TLptsDBStatementJoinType

public TLptsDBStatementJoinType(XLptsDBStatementJoinType spjot)

This constructor copies the parameter join type information from another XLptsDBStatementJoinType.

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 XLptsDBStatementJoinType class.

Example :


 public void example(XLptsDBStatementJoinType spjot)
 {
   TLptsDBStatementJoinType pjot = new TLptsDBStatementJoinType(spjot);
 }

 

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

addToColumnListItem

public void addToColumnListItem(XLptsDBStatementColumnType item)

Adds the column to the toColumn list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : n/a

Notes : Use this to add to the list. Getting the list with getToColumnListItem and
then adding does not have any affect.

Example :


 TLptsDBStatementJoinType pjot =  new TLptsDBStatementJoinType();
 TLptsDBStatementColumnType column = new TLptsDBStatementColumnType();
 plot.set...
 pjot.addToColumnListItem(column);

 

Parameters:
item - the column to add in the list.

equals

public boolean equals(java.lang.Object obj)

Check for equal TLptsDBStatementJoinType objects.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : n/a

Notes :

Example :


 TLptsDBStatementJoinType pjot1 = new TLptsDBStatementJoinType();
 TLptsDBStatementJoinType pjot2 = new TLptsDBStatementJoinType();
 return pjot1.equals(pjot2);

 

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

getToColumnListItem

public java.util.List<XLptsDBStatementColumnType> getToColumnListItem()

Returns a copy of the actual list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never. The result may be an empty list.

Notes : Do not add to this list as there is no result. Use the addToColumnListItem function.

Example :


 // let us duplicate all items in the list
 // this can be done with great safety as we are working with two lists.

 for(XLptsDBStatementColumnType column : pjot.getToColumnListItem())
     pjot.addToColumnListItem(new TLptsStatementType(column));

 

Overrides:
getToColumnListItem in class XLptsDBStatementJoinType
Returns:
Returns a list of connections of this group.

getToColumnListItemSize

public int getToColumnListItemSize()

Returns the size of the actual list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : Never. The result may be an empty list.

Notes : Do not use getToColumnListItem().size() as it is less efficient.

Example :


 TLptsDBStatementJoinType pjot = new TLptsDBStatementJoinType();
 if(pjot.getToColumnListItemSize() > 0)
   return true;
 else
   return false;

 

Returns:
Returns the number of connections.

removeToColumnListItem

public void removeToColumnListItem(XLptsDBStatementColumnType item)

Removes the column from the toColumn list.

Thread Safe : Yes

Spawns its own Thread : No

May Return NULL : n/a

Notes : Use this to remove from the list. Getting the list with getToColumnListItem and
then removing does not have any affect.

Example :


 TLptsDBStatementJoinType pjot =  new TLptsDBStatementJoinType();
 String columnName = "id";
 for(XLptsDBStatementColumnType column : pjot.getToColumnListItem())
   if(column.getColumn().equals(columnName))
   {
     pjot.removeToColumnListItem(column);
     break;
   }

 

Parameters:
item - the column to remove from list.


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