/**********************************************************************
Copyright (c) 2002 Mike Martin (TJDO) and others. All rights reserved.
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
Contributors:
2003 Erik Bengtson - fixed raised java.lang.NoSuchMethodError on
toString when appending an StringBuffer to
another StringBuffer
2003 Andy Jefferson - coding standards
2004 Andy Jefferson - changed to use fully qualified table names
2005 Andy Jefferson - sorted toString() to not modify fields.
2005 Andy Jefferson - added LEFT OUTER JOIN capability, and ANSI/non-ANSI syntax
2005 Andy Jefferson - added use of aliases on table names to allow for self-references etc
...
**********************************************************************/
package org.jpox.store.rdbms;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import org.jpox.exceptions.JPOXException;
import org.jpox.store.mapped.DatastoreContainerObject;
import org.jpox.store.mapped.DatastoreField;
import org.jpox.store.mapped.FetchStatement;
import org.jpox.store.mapped.mapping.JavaTypeMapping;
import org.jpox.store.rdbms.adapter.RDBMSAdapter;
import org.jpox.util.Localiser;
/**
* Representation of a Fetch statement for RDBMS datastores.
* <P>
* Provides a simplification over QueryStatement which is totally general.
* Fetch statements only provide select of columns, (inner) joins to superclass tables/secondary tables,
* and (left outer) joins to related table columns.
* </P>
* <P>
* Takes the traditional SQL form of "SELECT .... FROM ... WHERE ... " and can have an optional locking
* on the selected rows where required. Can operate using ANSI92 SQL JOIN syntax, or custom JOIN syntax
* where the DatabaseAdapter doesnt support ANSI (e.g Oracle 8).
* </P>
*
* @version $Revision: 1.21 $
**/
public class RDBMSFetchStatement implements FetchStatement
{
/** Localiser of messages. */
protected static final Localiser LOCALISER = Localiser.getInstance("org.jpox.store.rdbms.Localisation",
RDBMSManager.class.getClassLoader());
/** The primary table, which will have an alias of "THIS". */
protected final DatastoreContainerObject mainTable;
/** The selected columns. */
protected final ArrayList selected = new ArrayList();
/** Any WHERE conditions specified. */
protected StringBuffer conditionList = new StringBuffer();
/** Map of mappings to inner join to (from the id of the main table), keyed by the table alias */
protected final Map innerTableMappings = new HashMap();
/** Map of mappings to left outer join to (from the id of the main table), keyed by the table alias */
protected final Map outerTableMappings = new HashMap();
/**
* Constructor.
* @param mainTable The table to select from
*/
public RDBMSFetchStatement(DatastoreContainerObject mainTable)
{
this.mainTable = mainTable;
}
/**
* Add column(s) to the SELECT clause using a LEFT OUTER JOIN to join to a related table.
* Used in 3 situations :-
* <UL>
* <LI>1-1 bidirectional relation where we have a FK on the other side, and so we join to
* the other table, and select the "id" of the other table.</LI>
* <LI>1-N bidirectional join table relation where we have a join table and we are the "N" side
* and so join to the join table, and select the "owner" of the other table.</LI>
* <LI>Secondary table field where we have outer=true specified</LI>
* </UL>
* The discriminator part is optional and allows for a case where the referenced table has a
* discriminator so that we can select the correct referenced object type (where the subclasses
* of the referenced type use the same table as the referenced type).
* @param refMapping The reference mapping in the other table that we join to our id
* @param selectMapping The mapping of the field in the referenced table to select in the fetch
* @param refDiscrimMapping Mapping for discriminator in the related table
* @param discrimValues Possible values to accept for the discriminator in the related table
* @param useExistingJoinIfPossible WHether to reuse an existing join to the same table where possible
* @return The parameter position(s) of the column(s) in the statement.
*/
public int[] selectOuterJoin(JavaTypeMapping refMapping, JavaTypeMapping selectMapping,
JavaTypeMapping refDiscrimMapping, Object[] discrimValues, boolean useExistingJoinIfPossible)
{
if (mainTable.getIDMapping().getNumberOfDatastoreFields() != refMapping.getNumberOfDatastoreFields())
{
throw new JPOXException(LOCALISER.msg("052221",
"" + mainTable.getIDMapping().getNumberOfDatastoreFields(), "" + refMapping.getNumberOfDatastoreFields())).setFatal();
}
String alias = null;
if (useExistingJoinIfPossible)
{
// Check if the outer join is already present
Iterator iter = outerTableMappings.entrySet().iterator();
while (iter.hasNext())
{
Map.Entry entry = (Map.Entry)iter.next();
OuterJoinDefinition join = (OuterJoinDefinition)entry.getValue();
if (join.getReferenceMapping().equals(refMapping))
{
alias = (String)entry.getKey();
// Add the selected column to our list
int[] refColumnIds = new int[selectMapping.getNumberOfDatastoreFields()];
int countReferenceFields = selectMapping.getNumberOfDatastoreFields();
for (int i=0;i<countReferenceFields;i++)
{
String column_ref = getColumnReference(alias, selectMapping.getDataStoreMapping(i).getDatastoreField());
selected.add(column_ref);
refColumnIds[i] = selected.indexOf(column_ref) + 1;
}
return refColumnIds;
}
}
}
if (alias == null)
{
alias = "OUTER" + (outerTableMappings.size()+1);
outerTableMappings.put(alias, new OuterJoinDefinition(refMapping, refDiscrimMapping, discrimValues));
// Add the selected column to our list
int[] refColumnIds = new int[selectMapping.getNumberOfDatastoreFields()];
int countReferenceFields = selectMapping.getNumberOfDatastoreFields();
for (int i=0;i<countReferenceFields;i++)
{
String column_ref = getColumnReference(alias, selectMapping.getDataStoreMapping(i).getDatastoreField());
selected.add(column_ref);
refColumnIds[i] = selected.indexOf(column_ref) + 1;
}
return refColumnIds;
}
return null;
}
/**
* Add column(s) to the SELECT clause using a LEFT OUTER JOIN to join to a related table.
* Used in 2 situations :-
* <UL>
* <LI>1-1 bidirectional relation where we have a FK on the other side, and so we join to
* the other table, and select the "id" of the other table.</LI>
* <LI>1-N bidirectional join table relation where we have a join table and we are the "N" side
* and so join to the join table, and select the "owner" of the other table.</LI>
* </UL>
* The reference type mapping is optional and allows for the case where we have subclasses of
* the referenced type so that we can join to the correct subclass type (where the subclasses of
* the referenced type have their own tables).
* @param refMapping The reference mapping in the other table that we join to our id
* @param selectMapping The mapping of the field to select in the fetch
* @param refTypeMapping Mapping to the type of the referenced table (subclass table)
* @return The parameter position(s) of the column(s) in the statement.
*/
public int[] selectOuterJoin(JavaTypeMapping refMapping, JavaTypeMapping selectMapping,
JavaTypeMapping refTypeMapping)
{
if (mainTable.getIDMapping().getNumberOfDatastoreFields() != refMapping.getNumberOfDatastoreFields())
{
throw new JPOXException(LOCALISER.msg("052221",
"" + mainTable.getIDMapping().getNumberOfDatastoreFields(), "" + refMapping.getNumberOfDatastoreFields())).setFatal();
}
String alias = "OUTER" + (outerTableMappings.size()+1);
outerTableMappings.put(alias, new OuterJoinDefinition(refMapping, refTypeMapping));
// Add the selected column to our list
int[] refColumnIds = new int[selectMapping.getNumberOfDatastoreFields()];
int countReferenceFields = selectMapping.getNumberOfDatastoreFields();
for (int i=0;i<countReferenceFields;i++)
{
String column_ref = getColumnReference(alias, selectMapping.getDataStoreMapping(i).getDatastoreField());
selected.add(column_ref);
refColumnIds[i] = selected.indexOf(column_ref) + 1;
}
return refColumnIds;
}
/**
* Add a column to the SELECT clause.
* @param col The column to add
* @return The parameter position of this column in the statement.
*/
public int select(DatastoreField col)
{
String columnID = referenceDatastoreField(col);
if (!selected.contains(columnID))
{
selected.add(columnID);
}
return selected.indexOf(columnID) + 1;
}
/**
* Adds an expression to the SELECT clause.
* @param expr the expr to add to the select clause
* @return The parameter position of this expression in the statement.
*/
public int select(String expr)
{
if (!selected.contains(expr))
{
selected.add(expr);
}
return selected.indexOf(expr) + 1;
}
/**
* Method to specify a column to be referenced.
* The column is either in the main table or is assumed to be in the table
* of a superclass of the main class.
* @param col The column
* @return The column statement text
*/
public String referenceDatastoreField(DatastoreField col)
{
DatastoreContainerObject t = col.getDatastoreContainerObject();
String alias = null;
if (t.equals(mainTable))
{
alias = "THIS";
}
else
{
if (alias == null)
{
// Check if we have an inner join to this table
Iterator iter = innerTableMappings.entrySet().iterator();
while (iter.hasNext())
{
Map.Entry entry = (Map.Entry)iter.next();
InnerJoinDefinition join = (InnerJoinDefinition)entry.getValue();
if (t.equals(join.getTable()))
{
alias = (String)entry.getKey();
break;
}
}
}
}
if (alias == null)
{
// No join present so create INNER join
alias = "INNER" + (innerTableMappings.size()+1);
innerTableMappings.put(alias, new InnerJoinDefinition(t, t.getIDMapping()));
}
return col.applySelectFunction(alias + '.' + col.getIdentifier().toString());
}
/**
* Method to return a reference to a column to be selected.
* @param col The column
* @return The column statement name
*/
private String getColumnReference(String alias, DatastoreField col)
{
return col.applySelectFunction(alias + '.' + col.getIdentifier().toString());
}
/**
* Method to add an extra condition to the WHERE clause.
* @param condition The condition
*/
public void andCondition(String condition)
{
if (conditionList.length() > 0)
{
conditionList.append(" AND ");
}
conditionList.append('(').append(condition).append(')');
}
/**
* Method to return the statement assuming no locking of rows.
* @return The statement
*/
public String toString()
{
return toString(false);
}
/**
* Method to return the statement.
* @param lock Whether to lock the rows found by this SELECT
* @return The statement
*/
public String toString(boolean lock)
{
final RDBMSAdapter datastoreAdapter = ((RDBMSAdapter) mainTable.getStoreManager().getDatastoreAdapter());
// Build the SELECT list
StringBuffer stmt = new StringBuffer("SELECT ");
Iterator iter = selected.iterator();
while (iter.hasNext())
{
stmt.append(iter.next());
if (iter.hasNext())
{
stmt.append(',');
}
}
// Generate the FROM clause
String[] mainTableColumnID = new String[mainTable.getIDMapping().getNumberOfDatastoreFields()];
int countIdFields = mainTable.getIDMapping().getNumberOfDatastoreFields();
for (int j=0; j<countIdFields; j++)
{
mainTableColumnID[j] = referenceDatastoreField(mainTable.getIDMapping().getDataStoreMapping(j).getDatastoreField());
}
stmt.append(" FROM ").append(mainTable.toString()).append(" ").append("THIS");
// Add any locking of the rows for datastores that require WITH option to be placed after the FROM clause
if (lock && datastoreAdapter.getPlaceWithOptionAfterFromClause() && datastoreAdapter.getSelectWithLockOption() != null)
{
stmt.append(" WITH ").append(datastoreAdapter.getSelectWithLockOption());
}
StringBuffer joinConditions = new StringBuffer();
RDBMSAdapter dba = (RDBMSAdapter)mainTable.getStoreManager().getDatastoreAdapter();
// LEFT OUTER JOINs
if (!outerTableMappings.isEmpty())
{
JavaTypeMapping mainIdMapping = mainTable.getIDMapping();
iter = outerTableMappings.entrySet().iterator();
while (iter.hasNext())
{
Map.Entry entry = (Map.Entry)iter.next();
String alias = (String)entry.getKey();
OuterJoinDefinition def = (OuterJoinDefinition)entry.getValue();
JavaTypeMapping refMapping = def.getReferenceMapping();
JavaTypeMapping refDiscrimMapping = def.getReferenceDiscrimMapping();
Object[] refDiscrimValues = def.getDiscriminatorValues();
JavaTypeMapping refTypeMapping = def.getReferenceTypeMapping();
String refTableName = ((Column)refMapping.getDataStoreMapping(0).getDatastoreField()).getDatastoreContainerObject().toString();
if (dba.supportsAnsiJoinSyntax())
{
// ANSI syntax left outer join
stmt.append(" LEFT OUTER JOIN ").append(refTableName).append(" ").append(alias);
if (datastoreAdapter.getPlaceWithOptionWithinJoinClauses())
{
stmt.append(" WITH ").append(datastoreAdapter.getSelectWithLockOption());
}
stmt.append(" ON ");
if (refDiscrimMapping != null && refDiscrimValues != null)
{
stmt.append("(");
}
int countMainIdFields = mainIdMapping.getNumberOfDatastoreFields();
for (int i=0; i<countMainIdFields; i++)
{
if (i > 0)
{
stmt.append(" AND ");
}
stmt.append(getColumnReference("THIS", mainIdMapping.getDataStoreMapping(i).getDatastoreField()));
stmt.append(" = ");
stmt.append(getColumnReference(alias, refMapping.getDataStoreMapping(i).getDatastoreField()));
}
if (refDiscrimMapping != null && refDiscrimValues != null)
{
// Add constraint on discriminator in related table
stmt.append(" AND (");
for (int i=0;i<refDiscrimValues.length;i++)
{
if (i > 0)
{
stmt.append(" OR ");
}
stmt.append(getColumnReference(alias, refDiscrimMapping.getDataStoreMapping(0).getDatastoreField()));
stmt.append(" = ");
// TODO Cater for numeric discriminator types
// TODO Should we always use single quote for character values ?
stmt.append("'" + refDiscrimValues[i] + "'");
}
stmt.append("))");
}
else if (refTypeMapping != null)
{
// Add join to reference type mapping table from reference table
String refTypeTableName = refTypeMapping.getDataStoreMapping(0).getDatastoreField().getDatastoreContainerObject().toString();
stmt.append(" INNER JOIN ").append(refTypeTableName).append(" ").append(alias + "_SUB");
if (datastoreAdapter.getPlaceWithOptionWithinJoinClauses())
{
stmt.append(" WITH ").append(datastoreAdapter.getSelectWithLockOption());
}
stmt.append(" ON ");
int countRefTypeTableFields = refTypeMapping.getNumberOfDatastoreFields();
JavaTypeMapping refIdMapping = refMapping.getDatastoreContainer().getIDMapping();
for (int i=0; i<countRefTypeTableFields; i++)
{
if (i > 0)
{
stmt.append(" AND ");
}
stmt.append(getColumnReference(alias + "_SUB", refTypeMapping.getDataStoreMapping(i).getDatastoreField()));
stmt.append(" = ");
stmt.append(getColumnReference(alias, refIdMapping.getDataStoreMapping(i).getDatastoreField()));
}
}
}
else
{
// Non-ANSI syntax - add table to "stmt", and join definition to "joinConditions"
stmt.append(',').append(refTableName).append(" ").append(alias);
if (refDiscrimMapping != null && refDiscrimValues != null)
{
joinConditions.append("(");
}
int countMainIdFields = mainIdMapping.getNumberOfDatastoreFields();
for (int i=0; i<countMainIdFields; i++)
{
if (joinConditions.length() > 0)
{
joinConditions.append(" AND ");
}
joinConditions.append(
dba.getNonAnsiLeftOuterJoinWhereClause(mainIdMapping.getDataStoreMapping(i).getDatastoreField().toString(),
refMapping.getDataStoreMapping(i).getDatastoreField().toString()));
}
if (refDiscrimMapping != null && refDiscrimValues != null)
{
// Add constraint on discriminator in related table
joinConditions.append(" AND (");
for (int i=0;i<refDiscrimValues.length;i++)
{
if (i > 0)
{
joinConditions.append(" OR ");
}
joinConditions.append(getColumnReference(alias, refDiscrimMapping.getDataStoreMapping(0).getDatastoreField()));
joinConditions.append(" = ");
// TODO Cater for numeric discriminator types
// TODO Should we always use single quote for character values ?
joinConditions.append("'" + refDiscrimValues[i] + "'");
}
joinConditions.append("))");
}
else if (refTypeMapping != null)
{
// Add join to ref type mapping table
String refTypeTableName = refTypeMapping.getDataStoreMapping(0).getDatastoreField().getDatastoreContainerObject().toString();
stmt.append(',').append(refTypeTableName).append(" ").append(alias + "_SUB");
int countRefTypeTableFields = refTypeMapping.getNumberOfDatastoreFields();
JavaTypeMapping refIdMapping = refMapping.getDatastoreContainer().getIDMapping();
for (int i=0; i<countRefTypeTableFields; i++)
{
if (i > 0)
{
stmt.append(" AND ");
}
joinConditions.append(
dba.getNonAnsiInnerJoinWhereClause(refTypeMapping.getDataStoreMapping(i).getDatastoreField().toString(),
refIdMapping.getDataStoreMapping(i).getDatastoreField().toString()));
}
}
}
}
}
// INNER JOINs
if (!innerTableMappings.isEmpty())
{
JavaTypeMapping mainIdMapping = mainTable.getIDMapping();
iter = innerTableMappings.entrySet().iterator();
while (iter.hasNext())
{
Map.Entry entry = (Map.Entry)iter.next();
String alias = (String)entry.getKey();
InnerJoinDefinition join = (InnerJoinDefinition)entry.getValue();
if (dba.supportsAnsiJoinSyntax())
{
stmt.append(" INNER JOIN ").append(join.getTable()).append(" ").append(alias);
if (lock && datastoreAdapter.getPlaceWithOptionWithinJoinClauses())
{
stmt.append(" WITH ").append(datastoreAdapter.getSelectWithLockOption());
}
stmt.append(" ON ");
int countReferenceFields = join.getReferenceMapping().getNumberOfDatastoreFields();
for (int i=0; i<countReferenceFields; i++)
{
stmt.append(getColumnReference("THIS", mainIdMapping.getDataStoreMapping(i).getDatastoreField()));
stmt.append(" = ");
stmt.append(getColumnReference(alias, join.getReferenceMapping().getDataStoreMapping(i).getDatastoreField()));
if (i < join.getReferenceMapping().getNumberOfDatastoreFields()-1)
{
stmt.append(" AND ");
}
}
}
else
{
stmt.append(',').append(join.getTable()).append(" ").append(alias);
String[] supertableColumnID = new String[mainTable.getIDMapping().getNumberOfDatastoreFields()];
int countReferenceFields = join.getReferenceMapping().getNumberOfDatastoreFields();
for (int i=0; i<countReferenceFields; i++)
{
supertableColumnID[i] = getColumnReference(alias, join.getReferenceMapping().getDataStoreMapping(i).getDatastoreField());
}
for (int i=0; i<countReferenceFields; i++)
{
if (joinConditions.length() > 0)
{
joinConditions.append(" AND ");
}
joinConditions.append(dba.getNonAnsiInnerJoinWhereClause(mainTableColumnID[i], supertableColumnID[i]));
}
}
}
}
StringBuffer conditionList = new StringBuffer(this.conditionList.toString());
if (joinConditions.length() > 0)
{
if (conditionList.length() > 0)
{
conditionList = joinConditions.append(" AND (").append(conditionList.toString()).append(')');
}
else
{
conditionList = joinConditions;
}
}
// Add the WHERE clause
if (conditionList.length() > 0)
{
stmt.append(" WHERE ").append(conditionList.toString());
}
// Add any locking of the rows
if (lock)
{
if (datastoreAdapter.supportsLockWithSelectForUpdate())
{
stmt.append(" FOR UPDATE");
}
}
return stmt.toString();
}
/**
* Simple definition of a join for use internally.
* Used for inner joins only currently. Could be merged with the
* OuterJoinDefinition below.
* @version $Revision: 1.21 $
*/
class InnerJoinDefinition
{
private DatastoreContainerObject table;
private JavaTypeMapping referenceMapping;
/**
* Constructor
* @param table the joined table
* @param mapping the ID mapping for the joined table
*/
public InnerJoinDefinition(DatastoreContainerObject table, JavaTypeMapping mapping)
{
this.table = table;
this.referenceMapping = mapping;
}
/**
* Accessor for the ID mapping for the joined table
* @return the ID mapping for the joined table
*/
public JavaTypeMapping getReferenceMapping()
{
return referenceMapping;
}
/**
* Accessor for the joined table
* @return the joined table
*/
public DatastoreContainerObject getTable()
{
return table;
}
}
/**
* Simple definition of a join for use internally.
* Used for outer joins only currently, and provides a mapping in the related table
* to join to (from the id of this table), together with an optional discriminator
* mapping in the related table with its valid values of discriminator.
*
* @version $Revision: 1.21 $
*/
class OuterJoinDefinition
{
private JavaTypeMapping referenceMapping;
private JavaTypeMapping refDiscrimMapping;
private JavaTypeMapping refTypeMapping;
private Object[] discrimValues;
/**
* Constructor
* @param ref The mapping of the field in the referenced table to select in the fetch
* @param refDiscrim Mapping for discriminator in the related table
* @param discrimValues Possible values to accept for the discriminator in the related table
*/
public OuterJoinDefinition(JavaTypeMapping ref, JavaTypeMapping refDiscrim, Object[] discrimValues)
{
this.referenceMapping = ref;
this.refDiscrimMapping = refDiscrim;
this.discrimValues = discrimValues;
}
/**
* Constructor to use when we have no discriminator and we outer join to a base table, and need
* to join to a sub table of the base table.
* @param ref The mapping of the field in the referenced table to select in the fetch
* @param refType Mapping to the type of the referenced table (subclass table)
*/
public OuterJoinDefinition(JavaTypeMapping ref, JavaTypeMapping refType)
{
this.referenceMapping = ref;
this.refTypeMapping = refType;
}
/**
* Accessor for the ID mapping for the joined table
* @return the ID mapping for the joined table
*/
public JavaTypeMapping getReferenceMapping()
{
return referenceMapping;
}
/**
* Accessor for the discriminator mapping for the joined table
* @return the discriminator mapping for the joined table
*/
public JavaTypeMapping getReferenceDiscrimMapping()
{
return refDiscrimMapping;
}
/**
* Accessor for possible values to accept for the discriminator in the related table
* @return Possible values to accept for the discriminator in the related table
*/
public Object[] getDiscriminatorValues()
{
return discrimValues;
}
/**
* Accessor for the mapping to the type of the referenced table (subclass table)
* @return the mapping to the type of the referenced table (subclass table)
*/
public JavaTypeMapping getReferenceTypeMapping()
{
return refTypeMapping;
}
}
}