package org.apache.torque.sql;
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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.
*/
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
import org.apache.torque.Database;
import org.apache.torque.Torque;
import org.apache.torque.TorqueException;
import org.apache.torque.adapter.Adapter;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.criteria.CriteriaInterface;
import org.apache.torque.criteria.Criterion;
import org.apache.torque.criteria.FromElement;
import org.apache.torque.criteria.PreparedStatementPart;
import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.map.ColumnMap;
import org.apache.torque.map.DatabaseMap;
import org.apache.torque.map.MapHelper;
import org.apache.torque.map.TableMap;
import org.apache.torque.om.ObjectKey;
import org.apache.torque.sql.whereclausebuilder.CurrentDateTimePsPartBuilder;
import org.apache.torque.sql.whereclausebuilder.CustomBuilder;
import org.apache.torque.sql.whereclausebuilder.InBuilder;
import org.apache.torque.sql.whereclausebuilder.LikeBuilder;
import org.apache.torque.sql.whereclausebuilder.NullValueBuilder;
import org.apache.torque.sql.whereclausebuilder.StandardBuilder;
import org.apache.torque.sql.whereclausebuilder.VerbatimSqlConditionBuilder;
import org.apache.torque.sql.whereclausebuilder.WhereClausePsPartBuilder;
import org.apache.torque.util.UniqueColumnList;
import org.apache.torque.util.UniqueList;
/**
* Factored out code that is used to process SQL tables. This code comes
* from BasePeer and is put here to reduce complexity in the BasePeer class.
* You should not use the methods here directly!
*
* @author <a href="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
* @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a>
* @version $Id: SqlBuilder.java 1450486 2013-02-26 22:18:00Z tfischer $
*/
@SuppressWarnings("deprecation")
public final class SqlBuilder
{
/** Logging */
protected static final Log log = LogFactory.getLog(SqlBuilder.class);
/** Delimiters for SQL functions. */
public static final String[] FUNCTION_DELIMITERS
= {" ", ",", "(", ")", "<", ">"};
/** The backslash character*/
private static final char BACKSLASH = '\\';
/**
* The list of WhereClausePsPartBuilders which can build the where clause.
*/
private static List<WhereClausePsPartBuilder> whereClausePsPartBuilders
= new ArrayList<WhereClausePsPartBuilder>();
static
{
whereClausePsPartBuilders.add(new VerbatimSqlConditionBuilder());
whereClausePsPartBuilders.add(new CustomBuilder());
whereClausePsPartBuilders.add(new CurrentDateTimePsPartBuilder());
whereClausePsPartBuilders.add(new NullValueBuilder());
whereClausePsPartBuilders.add(new LikeBuilder());
whereClausePsPartBuilders.add(new InBuilder());
whereClausePsPartBuilders.add(new StandardBuilder());
}
/**
* Private constructor to prevent instantiation.
*
* Class contains only static method and should therefore not be
* instantiated.
*/
private SqlBuilder()
{
// empty
}
/**
* Returns the Builders which are responsible to render single where clause
* conditions. The returned list can be modified in order to change
* the rendered SQL.
*
* @return the current WhereClausePsPartBuilders, not null.
*/
public static List<WhereClausePsPartBuilder> getWhereClausePsPartBuilders()
{
return whereClausePsPartBuilders;
}
/**
* Builds a Query from a criteria.
*
* @param crit the criteria to build the query from, not null.
*
* @return the corresponding query to the criteria.
*
* @exception TorqueException if an error occurs
* @deprecated please use org.apache.torque.criteria.Criteria
* instead of org.apache.torque.util.Criteria.
*/
@Deprecated
public static Query buildQuery(final org.apache.torque.util.Criteria crit)
throws TorqueException
{
Query sqlStatement = new Query();
JoinBuilder.processJoins(crit, sqlStatement);
processModifiers(crit, sqlStatement);
processSelectColumns(crit, sqlStatement);
processAsColumns(crit, sqlStatement);
processCriterions(crit, sqlStatement);
processGroupBy(crit, sqlStatement);
processHaving(crit, sqlStatement);
processOrderBy(crit, sqlStatement);
processLimits(crit, sqlStatement);
return sqlStatement;
}
/**
* Builds a Query from a criteria.
*
* @param crit the criteria to build the query from, not null.
*
* @return the corresponding query to the criteria.
*
* @exception TorqueException if an error occurs
*/
public static Query buildQuery(final Criteria crit)
throws TorqueException
{
Query sqlStatement = new Query();
JoinBuilder.processJoins(crit, sqlStatement);
processModifiers(crit, sqlStatement);
processSelectColumns(crit, sqlStatement);
processAsColumns(crit, sqlStatement);
processCriterions(crit, sqlStatement);
processGroupBy(crit, sqlStatement);
processHaving(crit, sqlStatement);
processOrderBy(crit, sqlStatement);
processLimits(crit, sqlStatement);
processFromElements(crit, sqlStatement);
processForUpdate(crit, sqlStatement);
sqlStatement.setFetchSize(crit.getFetchSize());
return sqlStatement;
}
/**
* Adds the select columns from the criteria to the query.
*
* @param criteria the criteria from which the select columns are taken.
* @param query the query to which the select columns should be added.
*
* @throws TorqueException if the select columns can not be processed.
*/
private static void processSelectColumns(
final CriteriaInterface<?> criteria,
final Query query)
throws TorqueException
{
UniqueList<String> selectClause = query.getSelectClause();
UniqueColumnList selectColumns = criteria.getSelectColumns();
for (Column column : selectColumns)
{
String sqlExpression = column.getSqlExpression();
Column resolvedAlias = criteria.getAsColumns().get(sqlExpression);
if (resolvedAlias != null)
{
// will be handled by processAsColumns
continue;
}
selectClause.add(sqlExpression);
addTableToFromClause(
column,
criteria,
query);
}
}
/**
* Adds the As-columns (Aliases for columns) from the criteria
* to the query's select clause.
*
* @param criteria the criteria from which the As-columns are taken,
* not null.
* @param query the query to which the As-columns should be added,
* not null.
*
* @throws TorqueException if the as columns can not be processed.
*/
private static void processAsColumns(
final CriteriaInterface<?> criteria,
final Query query)
throws TorqueException
{
UniqueList<String> querySelectClause = query.getSelectClause();
Map<String, Column> criteriaAsColumns = criteria.getAsColumns();
for (Map.Entry<String, Column> entry : criteriaAsColumns.entrySet())
{
Column column = entry.getValue();
querySelectClause.add(
column.getSqlExpression()
+ SqlEnum.AS
+ entry.getKey());
addTableToFromClause(
column,
criteria,
query);
}
}
/**
* Adds the select modifiers from the criteria to the query.
*
* @param criteria the criteria from which the Modifiers are taken,
* not null.
* @param query the query to which the Modifiers should be added,
* not null.
*/
private static void processModifiers(
final CriteriaInterface<?> criteria,
final Query query)
{
UniqueList<String> selectModifiers = query.getSelectModifiers();
UniqueList<String> modifiers = criteria.getSelectModifiers();
for (String modifier : modifiers)
{
selectModifiers.add(modifier);
}
}
/**
* Adds the Criterions from the criteria to the query.
*
* @param criteria the criteria from which the Criterion-objects are taken
* @param query the query to which the Criterion-objects should be added.
*
* @throws TorqueException if the Criterion-objects can not be processed
*/
private static void processCriterions(
final Criteria criteria,
final Query query)
throws TorqueException
{
if (criteria.getTopLevelCriterion() == null)
{
return;
}
StringBuilder where = new StringBuilder();
appendCriterion(
criteria.getTopLevelCriterion(),
criteria,
where,
query);
query.getWhereClause().add(where.toString());
}
static void appendCriterion(
Criterion criterion,
CriteriaInterface<?> criteria,
StringBuilder where,
Query query)
throws TorqueException
{
if (criterion.isComposite())
{
where.append('(');
boolean firstPart = true;
for (Criterion part : criterion.getParts())
{
if (!firstPart)
{
where.append(criterion.getConjunction());
}
appendCriterion(
part,
criteria,
where,
query);
firstPart = false;
}
where.append(')');
return;
}
// add the table to the from clause, if it is not already
// contained there
// it is important that this piece of code is executed AFTER
// the joins are processed
addTableToFromClause(
criterion.getLValue(),
criteria,
query);
addTableToFromClause(
criterion.getRValue(),
criteria,
query);
PreparedStatementPart whereClausePartOutput
= processCriterion(criterion, criteria);
where.append(whereClausePartOutput.getSql());
query.getWhereClausePreparedStatementReplacements().addAll(
whereClausePartOutput.getPreparedStatementReplacements());
}
static PreparedStatementPart processCriterion(
Criterion criterion,
CriteriaInterface<?> criteria)
throws TorqueException
{
final String dbName = criteria.getDbName();
final Database database = Torque.getDatabase(dbName);
final Adapter adapter = Torque.getAdapter(dbName);
boolean ignoreCase
= isIgnoreCase(criterion, criteria, database);
WhereClauseExpression whereClausePartInput
= new WhereClauseExpression(
criterion.getLValue(),
criterion.getComparison(),
criterion.getRValue(),
criterion.getSql(),
criterion.getPreparedStatementReplacements());
PreparedStatementPart whereClausePartOutput = null;
for (WhereClausePsPartBuilder builder : whereClausePsPartBuilders)
{
if (builder.isApplicable(whereClausePartInput, adapter))
{
whereClausePartOutput = builder.buildPs(
whereClausePartInput,
ignoreCase,
adapter);
break;
}
}
if (whereClausePartOutput == null)
{
// should not happen as last element in list is standardHandler
// which takes all
throw new RuntimeException("No handler found for whereClausePart "
+ whereClausePartInput);
}
return whereClausePartOutput;
}
/**
* Adds the Criterions from the criteria to the query.
*
* @param criteria the criteria from which the Criterion-objects are taken
* @param query the query to which the Criterion-objects should be added.
*
* @throws TorqueException if the Criterion-objects can not be processed
* @deprecated please use org.apache.torque.criteria.Criteria
* instead of org.apache.torque.util.Criteria.
*/
@Deprecated
private static void processCriterions(
final org.apache.torque.util.Criteria criteria,
final Query query)
throws TorqueException
{
UniqueList<String> whereClause = query.getWhereClause();
for (org.apache.torque.util.Criteria.Criterion criterion
: criteria.values())
{
StringBuilder sb = new StringBuilder();
appendCriterionToPs(
criterion,
criteria,
sb,
query);
whereClause.add(sb.toString());
}
}
/**
* @deprecated please use org.apache.torque.criteria.Criteria
* instead of org.apache.torque.util.Criteria.
*/
@Deprecated
private static void appendCriterionToPs(
org.apache.torque.util.Criteria.Criterion criterion,
org.apache.torque.util.Criteria criteria,
StringBuilder sb,
Query query)
throws TorqueException
{
Column column = criterion.getColumn();
// add the table to the from clause, if it is not already
// contained there
// it is important that this piece of code is executed AFTER
// the joins are processed
addTableToFromClause(
column,
criteria,
query);
boolean ignoreCase
= criteria.isIgnoreCase() || criterion.isIgnoreCase();
final String dbName = criteria.getDbName();
final Adapter adapter = Torque.getAdapter(dbName);
final Database database = Torque.getDatabase(dbName);
{
Column databaseColumn = resolveAliasAndAsColumnAndSchema(
column,
criteria);
ColumnMap columnMap = null;
{
DatabaseMap databaseMap = database.getDatabaseMap();
TableMap tableMap = databaseMap.getTable(
databaseColumn.getTableName());
if (tableMap != null)
{
columnMap = tableMap.getColumn(
databaseColumn.getColumnName());
}
}
if (columnMap != null)
{
// do not use ignoreCase on columns
// which do not contain String values
ignoreCase = ignoreCase
&& columnMap.getType() instanceof String;
}
}
for (int j = 0; j < criterion.getClauses().size(); j++)
{
sb.append('(');
}
String columnName = criterion.getColumn().getSqlExpression();
WhereClauseExpression whereClausePartInput
= new WhereClauseExpression(
columnName,
criterion.getComparison(),
criterion.getValue(),
null,
null);
PreparedStatementPart whereClausePartOutput
= buildPs(
whereClausePartInput,
ignoreCase,
adapter);
sb.append(whereClausePartOutput.getSql());
query.getWhereClausePreparedStatementReplacements().addAll(
whereClausePartOutput.getPreparedStatementReplacements());
for (int i = 0; i < criterion.getClauses().size(); i++)
{
sb.append(criterion.getConjunctions().get(i));
org.apache.torque.util.Criteria.Criterion clause
= criterion.getClauses().get(i);
appendCriterionToPs(
clause,
criteria,
sb,
query);
sb.append(')');
}
}
/**
* adds the OrderBy-Columns from the criteria to the query
* @param criteria the criteria from which the OrderBy-Columns are taken
* @param query the query to which the OrderBy-Columns should be added
* @throws TorqueException if the OrderBy-Columns can not be processed
*/
private static void processOrderBy(
final CriteriaInterface<?> crit,
final Query query)
throws TorqueException
{
UniqueList<String> orderByClause = query.getOrderByClause();
UniqueList<String> selectClause = query.getSelectClause();
UniqueList<OrderBy> orderByList = crit.getOrderByColumns();
// Check for each String/Character column and apply
// toUpperCase().
for (OrderBy orderBy : orderByList)
{
Column column = orderBy.getColumn();
ColumnMap columnMap = MapHelper.getColumnMap(column, crit);
String sqlExpression = column.getSqlExpression();
// Either we are not able to look up the column in the
// databaseMap, then simply use the case in orderBy and
// hope the user knows what he is
// doing.
// Or we only ignore case in order by for string columns
// which do not have a function around them
if (columnMap == null
|| (columnMap.getType() instanceof String
&& sqlExpression.indexOf('(') == -1))
{
if (orderBy.isIgnoreCase() || crit.isIgnoreCase())
{
final Adapter adapter = Torque.getAdapter(crit.getDbName());
orderByClause.add(
adapter.ignoreCaseInOrderBy(sqlExpression)
+ ' ' + orderBy.getOrder());
selectClause.add(
adapter.ignoreCaseInOrderBy(sqlExpression));
}
else
{
orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
if (crit.getAsColumns().get(sqlExpression) == null)
{
selectClause.add(sqlExpression);
}
}
}
else
{
orderByClause.add(sqlExpression + ' ' + orderBy.getOrder());
if (crit.getAsColumns().get(sqlExpression) == null)
{
selectClause.add(sqlExpression);
}
}
addTableToFromClause(
column,
crit,
query);
}
}
/**
* Adds the GroupBy-Columns from the criteria to the query.
*
* @param criteria the criteria from which the GroupBy-Columns are taken.
* @param query the query to which the GroupBy-Columns should be added.
*
* @throws TorqueException if the GroupBy-Columns can not be processed
*/
private static void processGroupBy(
final CriteriaInterface<?> criteria,
final Query query)
throws TorqueException
{
UniqueList<String> groupByClause = query.getGroupByClause();
UniqueList<String> selectClause = query.getSelectClause();
UniqueColumnList groupBy = criteria.getGroupByColumns();
for (Column groupByColumn : groupBy)
{
Column column = criteria.getAsColumns().get(
groupByColumn.getSqlExpression());
if (column == null)
{
column = groupByColumn;
}
groupByClause.add(column.getSqlExpression());
selectClause.add(column.getSqlExpression());
addTableToFromClause(column, criteria, query);
}
}
/**
* adds the Having-Columns from the criteria to the query
* @param criteria the criteria from which the Having-Columns are taken
* @param query the query to which the Having-Columns should be added
* @throws TorqueException if the Having-Columns can not be processed
*/
private static void processHaving(
final Criteria crit,
final Query query)
throws TorqueException
{
Criterion having = crit.getHaving();
if (having != null)
{
query.setHaving(having.toString());
}
}
/**
* adds the Having-Columns from the criteria to the query
* @param criteria the criteria from which the Having-Columns are taken
* @param query the query to which the Having-Columns should be added
* @throws TorqueException if the Having-Columns can not be processed
*/
private static void processHaving(
final org.apache.torque.util.Criteria crit,
final Query query)
throws TorqueException
{
org.apache.torque.util.Criteria.Criterion having = crit.getHaving();
if (having != null)
{
query.setHaving(having.toString());
}
}
/**
* Adds a Limit clause to the query if supported by the database.
*
* @param criteria the criteria from which the Limit and Offset values
* are taken
* @param query the query to which the Limit clause should be added
*
* @throws TorqueException if the Database adapter cannot be obtained
*/
private static void processLimits(
final CriteriaInterface<?> crit,
final Query query)
throws TorqueException
{
int limit = crit.getLimit();
long offset = crit.getOffset();
if (offset > 0 || limit >= 0)
{
Adapter adapter = Torque.getAdapter(crit.getDbName());
adapter.generateLimits(query, offset, limit);
}
}
/**
* Checks the fromElements in the criteria and replaces the automatically
* calculated fromElements in the query by them, if they are filled.
*
* @param criteria the criteria from which the query should be built.
* @param query the query to build.
*/
private static void processFromElements(
final Criteria criteria,
final Query query)
{
if (criteria.getFromElements().isEmpty())
{
log.trace("criteria's from Elements is empty,"
+ " using automatically calculated from clause");
return;
}
query.getFromClause().clear();
query.getFromClause().addAll(criteria.getFromElements());
}
/**
* Adds a possible FOR UPDATE Clause to the query.
*
* @param criteria the criteria from which the query should be built.
* @param query the query to build.
*
* @throws TorqueException if the Database adapter cannot be obtained
*/
private static void processForUpdate(
final Criteria criteria,
final Query query)
throws TorqueException
{
if (criteria.isForUpdate())
{
Adapter adapter = Torque.getAdapter(criteria.getDbName());
query.setForUpdate(adapter.getUpdateLockClause());
}
}
/**
* Returns the tablename which can be added to a From Clause.
* This takes care of any aliases that might be defined.
* For example, if an alias "a" for the table AUTHOR is defined
* in the Criteria criteria, getTableNameForFromClause("a", criteria)
* returns "AUTHOR a".
*
* @param toAddToFromClause the column to extract the table name from,
* or a literal object value.
* @param criteria a criteria object to resolve a possible alias.
*
* @return A prepared statement part containing either the table name
* itself if tableOrAliasName is not an alias,
* or a String of the form "tableName tableOrAliasName"
* if tableOrAliasName is an alias for a table name,
* or a ? with the replacement if toAddToFromClause is not a Column.
*/
static PreparedStatementPart getExpressionForFromClause(
final Object toAddToFromClause,
final CriteriaInterface<?> criteria)
throws TorqueException
{
if (!(toAddToFromClause instanceof Column))
{
// toAddToFromClause is a literal Value
return new PreparedStatementPart("?", toAddToFromClause);
}
Column column = (Column) toAddToFromClause;
Column resolvedColumn
= resolveAliasAndAsColumnAndSchema(column, criteria);
String fullTableName
= resolvedColumn.getFullTableName();
if (!StringUtils.equals(
resolvedColumn.getTableName(),
column.getTableName()))
{
// If the tables have an alias, add an "<xxx> <yyy> statement"
// <xxx> AS <yyy> causes problems on oracle
PreparedStatementPart result = new PreparedStatementPart();
result.getSql()
.append(fullTableName)
.append(" ")
.append(column.getTableName());
return result;
}
Object resolvedAlias = criteria.getAliases().get(
resolvedColumn.getTableName());
if (resolvedAlias != null)
{
if (resolvedAlias instanceof Criteria)
{
Criteria subquery = (Criteria) resolvedAlias;
Query renderedSubquery = SqlBuilder.buildQuery(subquery);
PreparedStatementPart result = new PreparedStatementPart();
result.getSql().append("(")
.append(renderedSubquery.toString())
.append(") ")
.append(resolvedColumn.getTableName());
result.getPreparedStatementReplacements().addAll(
renderedSubquery.getPreparedStatementReplacements());
return result;
}
else
{
throw new TorqueException("Table name "
+ resolvedColumn.getTableName()
+ " resolved to an unhandleable class "
+ resolvedAlias.getClass().getName());
}
}
return new PreparedStatementPart(fullTableName);
}
/**
* Fully qualify a table name with an optional schema reference.
*
* @param table The table name to use.
* If null is passed in, null is returned.
* @param dbName The name of the database to which this tables belongs.
* If null is passed, the default database is used.
*
* @return The table name to use inside the SQL statement.
* If null is passed into this method, null is returned.
* @exception TorqueException if an error occurs
*/
public static String getFullTableName(
final String table,
final String dbName)
throws TorqueException
{
if (table == null)
{
return table;
}
int dotIndex = table.indexOf(".");
if (dotIndex == -1) // No schema given
{
String targetDBName = (dbName == null)
? Torque.getDefaultDB()
: dbName;
String targetSchema = Torque.getSchema(targetDBName);
// If we have a default schema, fully qualify the
// table and return.
if (StringUtils.isNotEmpty(targetSchema))
{
return new StringBuffer()
.append(targetSchema)
.append(".")
.append(table)
.toString();
}
}
return table;
}
/**
* Unqualify a table or column name.
*
* @param name the name to unqualify.
* If null is passed in, null is returned.
*
* @return The unqualified name.
*/
public static String getUnqualifiedName(
final String name,
final String dbName)
throws TorqueException
{
if (name == null)
{
return null;
}
int dotIndex = name.lastIndexOf(".");
if (dotIndex == -1)
{
return name;
}
return name.substring(dotIndex + 1);
}
/**
* Guesses a table name from a criteria by inspecting the first
* column in the criteria.
*
* @param criteria the criteria to guess the table name from.
*
* @return the table name, not null.
*
* @throws TorqueException if the table name cannot be determined.
*/
public static String guessFullTableFromCriteria(Criteria criteria)
throws TorqueException
{
org.apache.torque.criteria.Criterion criterion
= criteria.getTopLevelCriterion();
if (criterion == null)
{
throw new TorqueException("Could not determine table name "
+ " as criteria contains no criterion");
}
while (criterion.isComposite())
{
criterion = criterion.getParts().iterator().next();
}
String tableName = null;
Object lValue = criterion.getLValue();
if (lValue instanceof Column)
{
Column column = (Column) lValue;
tableName = column.getFullTableName();
}
if (tableName == null)
{
throw new TorqueException("Could not determine table name "
+ " as first criterion contains no table name");
}
return tableName;
}
/**
* Returns the table map for a table.
*
* @param tableName the name of the table.
* @param dbName the name of the database, null for the default db.
*
* @return the table map for the table, not null.
*
* @throws TorqueException if the database or table is unknown.
*/
public static TableMap getTableMap(String tableName, String dbName)
throws TorqueException
{
if (dbName == null)
{
dbName = Torque.getDefaultDB();
}
DatabaseMap databaseMap = Torque.getDatabaseMap(dbName);
if (databaseMap == null)
{
throw new TorqueException("Could not find database map"
+ " for database "
+ dbName);
}
String unqualifiedTableName = getUnqualifiedName(tableName, dbName);
TableMap result = databaseMap.getTable(unqualifiedTableName);
if (result == null)
{
throw new TorqueException("Could not find table "
+ tableName
+ " in database map of database "
+ dbName);
}
return result;
}
/**
* Returns the database name of a column.
*
* @param tableName the name of a table or the alias for a table.
* @param criteria a criteria object to resolve a possible alias.
*
* @return either the tablename itself if tableOrAliasName is not an alias,
* or a String of the form "tableName tableOrAliasName"
* if tableOrAliasName is an alias for a table name
*/
static Column resolveAliasAndAsColumnAndSchema(
final Column columnToResolve,
final CriteriaInterface<?> criteria)
throws TorqueException
{
String columnNameToResolve = columnToResolve.getColumnName();
Column resolvedColumn = criteria.getAsColumns().get(columnNameToResolve);
boolean sqlExpressionModified = false;
if (resolvedColumn == null)
{
resolvedColumn = columnToResolve;
}
else
{
sqlExpressionModified = true;
}
String tableNameToResolve = resolvedColumn.getTableName();
Object resolvedAlias = criteria.getAliases().get(tableNameToResolve);
String resolvedTableName;
if (resolvedAlias == null || !(resolvedAlias instanceof String))
{
resolvedTableName = tableNameToResolve;
}
else
{
resolvedTableName = (String) resolvedAlias;
sqlExpressionModified = true;
}
String resolvedSchemaName = resolvedColumn.getSchemaName();
if (resolvedSchemaName == null)
{
final String dbName = criteria.getDbName();
final Database database = Torque.getDatabase(dbName);
resolvedSchemaName = database.getSchema();
}
if (sqlExpressionModified)
{
return new ColumnImpl(
resolvedSchemaName,
resolvedTableName,
resolvedColumn.getColumnName());
}
else
{
return new ColumnImpl(
resolvedSchemaName,
resolvedTableName,
resolvedColumn.getColumnName(),
resolvedColumn.getSqlExpression());
}
}
/**
* Checks if a fromExpression is already contained in a from clause.
* Different aliases for the same table are treated
* as different tables: E.g.
* fromClauseContainsTableName(fromClause, "table_a a") returns false if
* fromClause contains only another alias for table_a ,
* e.g. "table_a aa" and the unaliased tablename "table_a".
* Special case: If fromClause is null or empty, false is returned.
*
* @param fromClause The list to check against.
* @param fromExpression the fromExpression to check, not null.
*
* @return whether the fromExpression is already contained in the from
* clause.
*/
static boolean fromClauseContainsExpression(
final UniqueList<FromElement> fromClause,
final PreparedStatementPart fromExpression)
{
if (fromExpression == null || fromExpression.getSql().length() == 0)
{
return false;
}
String fromExpressionSql = fromExpression.getSql().toString();
for (FromElement fromElement : fromClause)
{
if (fromExpressionSql.equals(fromElement.getFromExpression()))
{
return true;
}
}
return false;
}
/**
* Adds a table to the from clause of a query, if it is not already
* contained there.
*
* @param tableOrAliasName the name of a table
* or the alias for a table. If null, the from clause is left
* unchanged.
* @param criteria a criteria object to resolve a possible alias
* @param query the query where the the table name should be added
* to the from clause
*/
static void addTableToFromClause(
final Object possibleColumn,
final CriteriaInterface<?> criteria,
Query query)
throws TorqueException
{
if (possibleColumn == null)
{
return;
}
if (!(possibleColumn instanceof Column))
{
return;
}
Column column = (Column) possibleColumn;
if (column.getTableName() == null)
{
return;
}
PreparedStatementPart fromClauseExpression = getExpressionForFromClause(
column,
criteria);
UniqueList<FromElement> queryFromClause = query.getFromClause();
// it is important that this piece of code is executed AFTER
// the joins are processed
if (!fromClauseContainsExpression(
queryFromClause,
fromClauseExpression))
{
FromElement fromElement = new FromElement(
fromClauseExpression.getSql().toString(),
null,
null,
fromClauseExpression.getPreparedStatementReplacements());
queryFromClause.add(fromElement);
}
}
/**
* Checks whether ignoreCase is used for this criterion.
* This is the case if ignoreCase is either set on the criterion
* or the criteria and if ignoreCase is applicable for both values.
*
* @param criterion the value to check.
* @param criteria the criteria where the criterion stems from.
* @param database The database to check.
*
* @return Whether to use ignoreCase for the passed criterion.
*
* @throws TorqueException in the case of an error.
*/
static boolean isIgnoreCase(
Criterion criterion,
CriteriaInterface<?> criteria,
Database database)
throws TorqueException
{
boolean ignoreCase
= criteria.isIgnoreCase() || criterion.isIgnoreCase();
ignoreCase = ignoreCase
&& ignoreCaseApplicable(
criterion.getLValue(),
criteria,
database)
&& ignoreCaseApplicable(
criterion.getRValue(),
criteria,
database);
return ignoreCase;
}
/**
* Checks whether ignoreCase is applicable for this column.
* This is not the case if the value is a column and the column type is
* not varchar, or if the object is no column and not a String;
* in all other cases ignoreCase is applicable.
*
* @param value the value to check.
* @param criteria the criteria where the value stems from.
* @param database The database to check.
*
* @return false if ignoreCase is not applicable, true otherwise.
*
* @throws TorqueException in the case of an error.
*/
private static boolean ignoreCaseApplicable(
Object value,
CriteriaInterface<?> criteria,
Database database)
throws TorqueException
{
if (value == null)
{
return true;
}
if (!(value instanceof Column))
{
if (value instanceof String
|| value instanceof Iterable
|| value.getClass().isArray())
{
return true;
}
return false;
}
Column column = (Column) value;
Column databaseColumn = resolveAliasAndAsColumnAndSchema(
column,
criteria);
ColumnMap columnMap = null;
{
DatabaseMap databaseMap = database.getDatabaseMap();
TableMap tableMap = databaseMap.getTable(
databaseColumn.getTableName());
if (tableMap != null)
{
columnMap = tableMap.getColumn(
databaseColumn.getColumnName());
}
}
if (columnMap == null)
{
return true;
}
// do not use ignoreCase on columns
// which do not contain String values
return columnMap.getType() instanceof String;
}
/**
* Builds an element of the where clause of a prepared statement.
*
* @param whereClausePart the part of the where clause to build.
* Can be modified during this method.
* @param ignoreCase If true and columns represent Strings, the appropriate
* function defined for the database will be used to ignore
* differences in case.
* @param adapter The adapter for the database for which the SQL
* should be created, not null.
*
* @deprecated remove when util.Criteria is removed
*/
@Deprecated
private static PreparedStatementPart buildPs(
WhereClauseExpression whereClausePart,
boolean ignoreCase,
Adapter adapter)
throws TorqueException
{
PreparedStatementPart result = new PreparedStatementPart();
// Handle SqlEnum.Custom
if (SqlEnum.CUSTOM == whereClausePart.getOperator())
{
result.getSql().append(whereClausePart.getRValue());
return result;
}
// Handle SqlEnum.CURRENT_DATE and SqlEnum.CURRENT_TIME
if (whereClausePart.getRValue() instanceof SqlEnum)
{
result.getSql().append(whereClausePart.getLValue())
.append(whereClausePart.getOperator())
.append(whereClausePart.getRValue());
return result;
}
// If rValue is an ObjectKey, take the value of that ObjectKey.
if (whereClausePart.getRValue() instanceof ObjectKey)
{
whereClausePart.setRValue(
((ObjectKey) whereClausePart.getRValue()).getValue());
}
/* If rValue is null, check to see if the operator
* is an =, <>, or !=. If so, replace the comparison
* with SqlEnum.ISNULL or SqlEnum.ISNOTNULL.
*/
if (whereClausePart.getRValue() == null)
{
if (whereClausePart.getOperator().equals(SqlEnum.EQUAL))
{
result.getSql().append(whereClausePart.getLValue())
.append(SqlEnum.ISNULL);
return result;
}
if (whereClausePart.getOperator().equals(SqlEnum.NOT_EQUAL)
|| whereClausePart.getOperator().equals(
SqlEnum.ALT_NOT_EQUAL))
{
result.getSql().append(whereClausePart.getLValue())
.append(SqlEnum.ISNOTNULL);
return result;
}
}
// Handle SqlEnum.ISNULL and SqlEnum.ISNOTNULL
if (whereClausePart.getOperator().equals(SqlEnum.ISNULL)
|| whereClausePart.getOperator().equals(SqlEnum.ISNOTNULL))
{
result.getSql().append(whereClausePart.getLValue())
.append(whereClausePart.getOperator());
return result;
}
// handle Subqueries
if (whereClausePart.getRValue() instanceof Criteria)
{
Query subquery = SqlBuilder.buildQuery(
(Criteria) whereClausePart.getRValue());
result.getPreparedStatementReplacements().addAll(
subquery.getPreparedStatementReplacements());
result.getSql().append(whereClausePart.getLValue())
.append(whereClausePart.getOperator())
.append("(").append(subquery.toString()).append(")");
return result;
}
if (whereClausePart.getRValue()
instanceof org.apache.torque.util.Criteria)
{
Query subquery = SqlBuilder.buildQuery(
(org.apache.torque.util.Criteria)
whereClausePart.getRValue());
result.getPreparedStatementReplacements().addAll(
subquery.getPreparedStatementReplacements());
result.getSql().append(whereClausePart.getLValue())
.append(whereClausePart.getOperator())
.append("(").append(subquery.toString()).append(")");
return result;
}
// handle LIKE and similar
if (whereClausePart.getOperator().equals(Criteria.LIKE)
|| whereClausePart.getOperator().equals(Criteria.NOT_LIKE)
|| whereClausePart.getOperator().equals(Criteria.ILIKE)
|| whereClausePart.getOperator().equals(Criteria.NOT_ILIKE))
{
return buildPsLike(whereClausePart, ignoreCase, adapter);
}
// handle IN and similar
if (whereClausePart.getOperator().equals(Criteria.IN)
|| whereClausePart.getOperator().equals(Criteria.NOT_IN))
{
return buildPsIn(whereClausePart, ignoreCase, adapter);
}
// Standard case
result.getPreparedStatementReplacements().add(
whereClausePart.getRValue());
if (ignoreCase
&& whereClausePart.getRValue() instanceof String)
{
result.getSql().append(
adapter.ignoreCase((String) whereClausePart.getLValue()))
.append(whereClausePart.getOperator())
.append(adapter.ignoreCase("?"));
}
else
{
result.getSql().append(whereClausePart.getLValue())
.append(whereClausePart.getOperator())
.append("?");
}
return result;
}
/**
* Takes a WhereClauseExpression with a LIKE operator
* and builds an SQL phrase based on whether wildcards are present
* and the state of theignoreCase flag.
* Multicharacter wildcards % and * may be used
* as well as single character wildcards, _ and ?. These
* characters can be escaped with \.
*
* e.g. criteria = "fre%" -> columnName LIKE 'fre%'
* -> UPPER(columnName) LIKE UPPER('fre%')
* criteria = "50\%" -> columnName = '50%'
*
* @param whereClausePart the part of the where clause to build.
* Can be modified in this method.
* @param ignoreCase If true and columns represent Strings, the appropriate
* function defined for the database will be used to ignore
* differences in case.
* @param adapter The adapter for the database for which the SQL
* should be created, not null.
*
* @return the rendered SQL for the WhereClauseExpression
*
* @deprecated remove when util.Criteria is removed
*/
@Deprecated
static PreparedStatementPart buildPsLike(
WhereClauseExpression whereClausePart,
boolean ignoreCase,
Adapter adapter)
throws TorqueException
{
if (!(whereClausePart.getRValue() instanceof String))
{
throw new TorqueException(
"rValue must be a String for the operator "
+ whereClausePart.getOperator());
}
String value = (String) whereClausePart.getRValue();
// If selection criteria contains wildcards use LIKE otherwise
// use = (equals). Wildcards can be escaped by prepending
// them with \ (backslash). However, if we switch from
// like to equals, we need to remove the escape characters.
// from the wildcards.
// So we need two passes: The first replaces * and ? by % and _,
// and checks whether we switch to equals,
// the second removes escapes if we have switched to equals.
int position = 0;
StringBuffer sb = new StringBuffer();
boolean replaceWithEquals = true;
while (position < value.length())
{
char checkWildcard = value.charAt(position);
switch (checkWildcard)
{
case BACKSLASH:
if (position + 1 >= value.length())
{
// ignore backslashes at end
break;
}
position++;
char escapedChar = value.charAt(position);
if (escapedChar != '*' && escapedChar != '?')
{
sb.append(checkWildcard);
}
// code below copies escaped character into sb
checkWildcard = escapedChar;
break;
case '%':
case '_':
replaceWithEquals = false;
break;
case '*':
replaceWithEquals = false;
checkWildcard = '%';
break;
case '?':
replaceWithEquals = false;
checkWildcard = '_';
break;
default:
break;
}
sb.append(checkWildcard);
position++;
}
value = sb.toString();
if (ignoreCase)
{
if (adapter.useIlike() && !replaceWithEquals)
{
if (SqlEnum.LIKE.equals(whereClausePart.getOperator()))
{
whereClausePart.setOperator(SqlEnum.ILIKE);
}
else if (SqlEnum.NOT_LIKE.equals(whereClausePart.getOperator()))
{
whereClausePart.setOperator(SqlEnum.NOT_ILIKE);
}
}
else
{
// no native case insensitive like is offered by the DB,
// or the LIKE was replaced with equals.
// need to ignore case manually.
whereClausePart.setLValue(
adapter.ignoreCase((String) whereClausePart.getLValue()));
}
}
PreparedStatementPart result = new PreparedStatementPart();
result.getSql().append(whereClausePart.getLValue());
if (replaceWithEquals)
{
if (whereClausePart.getOperator().equals(SqlEnum.NOT_LIKE)
|| whereClausePart.getOperator().equals(SqlEnum.NOT_ILIKE))
{
result.getSql().append(SqlEnum.NOT_EQUAL);
}
else
{
result.getSql().append(SqlEnum.EQUAL);
}
// remove escape backslashes from String
position = 0;
sb = new StringBuffer();
while (position < value.length())
{
char checkWildcard = value.charAt(position);
if (checkWildcard == BACKSLASH
&& position + 1 < value.length())
{
position++;
// code below copies escaped character into sb
checkWildcard = value.charAt(position);
}
sb.append(checkWildcard);
position++;
}
value = sb.toString();
}
else
{
result.getSql().append(whereClausePart.getOperator());
}
String rValueSql = "?";
// handle ignoreCase if necessary
if (ignoreCase && (!(adapter.useIlike()) || replaceWithEquals))
{
rValueSql = adapter.ignoreCase(rValueSql);
}
// handle escape clause if necessary
if (!replaceWithEquals && adapter.useEscapeClauseForLike())
{
rValueSql = rValueSql + SqlEnum.ESCAPE + "'\\'";
}
result.getPreparedStatementReplacements().add(value);
result.getSql().append(rValueSql);
return result;
}
/**
* Takes a columnName and criteria and
* builds a SQL 'IN' expression taking into account the ignoreCase
* flag.
*
* @param whereClausePart the part of the where clause to build.
* Can be modified in this method.
* @param ignoreCase If true and columns represent Strings, the appropriate
* function defined for the database will be used to ignore
* differences in case.
* @param adapter The adapter for the database for which the SQL
* should be created, not null.
*
* @return the built part.
*
* @deprecated remove when util.Criteria is removed
*/
@Deprecated
static PreparedStatementPart buildPsIn(
WhereClauseExpression whereClausePart,
boolean ignoreCase,
Adapter adapter)
{
PreparedStatementPart result = new PreparedStatementPart();
boolean ignoreCaseApplied = false;
List<String> inClause = new ArrayList<String>();
boolean nullContained = false;
if (whereClausePart.getRValue() instanceof Iterable)
{
for (Object listValue : (Iterable<?>) whereClausePart.getRValue())
{
if (listValue == null)
{
nullContained = true;
continue;
}
result.getPreparedStatementReplacements().add(listValue);
if (ignoreCase && listValue instanceof String)
{
inClause.add(adapter.ignoreCase("?"));
ignoreCaseApplied = true;
}
else
{
inClause.add("?");
}
}
}
else if (whereClausePart.getRValue().getClass().isArray())
{
for (Object arrayValue : (Object[]) whereClausePart.getRValue())
{
if (arrayValue == null)
{
nullContained = true;
continue;
}
result.getPreparedStatementReplacements().add(arrayValue);
if (ignoreCase && arrayValue instanceof String)
{
inClause.add(adapter.ignoreCase("?"));
ignoreCaseApplied = true;
}
else
{
inClause.add("?");
}
}
}
else
{
throw new IllegalArgumentException(
"Unknown rValue type "
+ whereClausePart.getRValue().getClass().getName()
+ ". rValue must be an instance of "
+ " Iterable or Array");
}
if (nullContained)
{
result.getSql().append('(');
}
if (ignoreCaseApplied)
{
result.getSql().append(
adapter.ignoreCase((String) whereClausePart.getLValue()));
}
else
{
result.getSql().append(whereClausePart.getLValue());
}
result.getSql().append(whereClausePart.getOperator())
.append('(')
.append(StringUtils.join(inClause.iterator(), ","))
.append(')');
if (nullContained)
{
if (whereClausePart.getOperator() == SqlEnum.IN)
{
result.getSql().append(Criterion.OR)
.append(whereClausePart.getLValue()).append(SqlEnum.ISNULL);
}
else if (whereClausePart.getOperator() == SqlEnum.NOT_IN)
{
result.getSql().append(Criterion.AND)
.append(whereClausePart.getLValue()).append(
SqlEnum.ISNOTNULL);
}
result.getSql().append(')');
}
return result;
}
}