Java tutorial
package org.apache.torque.util; /* * 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.HashSet; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.torque.Torque; import org.apache.torque.TorqueException; import org.apache.torque.adapter.DB; import org.apache.torque.map.ColumnMap; import org.apache.torque.map.DatabaseMap; import org.apache.torque.util.Criteria.Criterion; /** * 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 535596 2007-05-06 10:47:39Z tfischer $ */ public final class SQLBuilder { /** Logging */ protected static final Log log = LogFactory.getLog(SQLBuilder.class); /** Function Characters */ public static final String[] COLUMN_CHARS = { ".", "*" }; public static final String[] DELIMITERS = { " ", ",", "(", ")", "<", ">" }; /** Private constructor to prevent instantiation. Class contains only static method ans should therefore not be instantiated. */ private SQLBuilder() { } /** * 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) { 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; } /** * Remove a possible schema name from the table name. * * @param table The table name to use * * @return The table name with a possible schema name stripped off */ public static String getUnqualifiedTableName(final String table) { if (table != null) { int dotIndex = table.lastIndexOf("."); // Do we have a dot? if (++dotIndex > 0) // Incrementation allows for better test _and_ substring... { return table.substring(dotIndex); } } return table; } /** * Removes a possible function name or clause from a column name * * @param name The column name, possibly containing a clause * * @return The column name * * @throws TorqueException If the column name was malformed */ private static String removeSQLFunction(final String name) throws TorqueException { // Empty name => return it if (StringUtils.isEmpty(name)) { return name; } // Find Table.Column int dotIndex = name.indexOf('.'); if (dotIndex == -1) { dotIndex = name.indexOf("*"); } if (dotIndex == -1) { throw new TorqueException("removeSQLFunction() : Column name " + name + " does not contain a . or a *"); } String pre = name.substring(0, dotIndex); String post = name.substring(dotIndex + 1, name.length()); int startIndex = StringUtils.lastIndexOfAny(pre, DELIMITERS); int endIndex = StringUtils.indexOfAny(post, DELIMITERS); if (startIndex < 0 && endIndex < 0) { return name; } else { if (endIndex < 0) { endIndex = post.length(); } // if startIndex == -1 the formula is correct return name.substring(startIndex + 1, dotIndex + 1 + endIndex); } } /** * Returns a table name from an identifier. Each identifier is to be qualified as [schema.]table.column. This could also contain * FUNCTION([schema.]table.column). * * @param name The (possible fully qualified) identifier name * * @return the fully qualified table name * * @throws TorqueException If the identifier name was malformed */ public static String getTableName(final String name, final String dbName) throws TorqueException { final String testName = removeSQLFunction(name); if (StringUtils.isEmpty(testName)) { throwMalformedColumnNameException("getTableName", name); } // Everything before the last dot is the table name int rightDotIndex = testName.lastIndexOf('.'); if (rightDotIndex < 0) { if ("*".equals(testName)) { return null; } throwMalformedColumnNameException("getTableName", name); } return getFullTableName(testName.substring(0, rightDotIndex), dbName); } /** * Returns a set of all tables and possible aliases referenced from a criterion. The resulting Set can be directly used to build * a WHERE clause * * @param crit A Criteria object * @param tableCallback A Callback Object * * @return A Set of tables. */ public static Set getTableSet(final Criteria crit, final TableCallback tableCallback) { HashSet tables = new HashSet(); // Loop over all the Criterions for (Iterator it = crit.keySet().iterator(); it.hasNext();) { String key = (String) it.next(); Criteria.Criterion c = crit.getCriterion(key); List tableNames = c.getAllTables(); // Loop over all Tables referenced in this criterion. for (Iterator it2 = tableNames.iterator(); it2.hasNext();) { String name = (String) it2.next(); String aliasName = crit.getTableForAlias(name); // If the tables have an alias, add an "<xxx> AS <yyy> statement" if (StringUtils.isNotEmpty(aliasName)) { String newName = new StringBuffer(name.length() + aliasName.length() + 4).append(aliasName) .append(" AS ").append(name).toString(); name = newName; } tables.add(name); } if (tableCallback != null) { tableCallback.process(tables, key, crit); } } return tables; } /** * Builds a Query clause for Updating and deleting * * @param crit a <code>Criteria</code> value * @param params a <code>List</code> value * @param qc a <code>QueryCallback</code> value * * @return a <code>Query</code> value * * @exception TorqueException if an error occurs */ public static Query buildQueryClause(final Criteria crit, final List params, final QueryCallback qc) throws TorqueException { Query query = new Query(); final String dbName = crit.getDbName(); final DB db = Torque.getDB(dbName); final DatabaseMap dbMap = Torque.getDatabaseMap(dbName); JoinBuilder.processJoins(db, dbMap, crit, query); processModifiers(crit, query); processSelectColumns(crit, query, dbName); processAsColumns(crit, query); processCriterions(db, dbMap, dbName, crit, query, params, qc); processGroupBy(crit, query); processHaving(crit, query); processOrderBy(db, dbMap, crit, query); processLimits(crit, query); if (log.isDebugEnabled()) { log.debug(query.toString()); } return query; } /** * 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 Criteria criteria, final Query query, final String dbName) throws TorqueException { UniqueList selectClause = query.getSelectClause(); UniqueList select = criteria.getSelectColumns(); for (int i = 0; i < select.size(); i++) { String identifier = (String) select.get(i); selectClause.add(identifier); addTableToFromClause(getTableName(identifier, dbName), criteria, query); } } /** * adds the As-columns from the criteria to the query. * * @param criteria the criteria from which the As-columns are taken * @param query the query to which the As-columns should be added */ private static void processAsColumns(final Criteria criteria, final Query query) { UniqueList querySelectClause = query.getSelectClause(); Map criteriaAsColumns = criteria.getAsColumns(); for (Iterator it = criteriaAsColumns.entrySet().iterator(); it.hasNext();) { Map.Entry entry = (Map.Entry) it.next(); String key = (String) entry.getKey(); querySelectClause .add(new StringBuffer().append(entry.getValue()).append(SqlEnum.AS).append(key).toString()); } } /** * adds the Modifiers from the criteria to the query * * @param criteria the criteria from which the Modifiers are taken * @param query the query to which the Modifiers should be added */ private static void processModifiers(final Criteria criteria, final Query query) { UniqueList selectModifiers = query.getSelectModifiers(); UniqueList modifiers = criteria.getSelectModifiers(); for (int i = 0; i < modifiers.size(); i++) { selectModifiers.add(modifiers.get(i)); } } /** * adds the Criterion-objects 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 * @param params the parameters if a prepared statement should be built, or null if a normal statement should be built. * * @throws TorqueException if the Criterion-objects can not be processed */ private static void processCriterions(final DB db, final DatabaseMap dbMap, final String dbName, final Criteria crit, final Query query, final List params, final QueryCallback qc) throws TorqueException { UniqueList whereClause = query.getWhereClause(); for (Iterator it = crit.keySet().iterator(); it.hasNext();) { String key = (String) it.next(); Criteria.Criterion criterion = crit.getCriterion(key); Criteria.Criterion[] someCriteria = criterion.getAttachedCriterion(); String table = null; for (int i = 0; i < someCriteria.length; i++) { String tableName = someCriteria[i].getTable(); // 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(getFullTableName(tableName, dbName), crit, query); table = crit.getTableForAlias(tableName); if (table == null) { table = tableName; } boolean ignoreCase = ((crit.isIgnoreCase() || someCriteria[i].isIgnoreCase()) && (dbMap .getTable(table).getColumn(someCriteria[i].getColumn()).getType() instanceof String)); someCriteria[i].setIgnoreCase(ignoreCase); } criterion.setDB(db); whereClause.add(qc.process(criterion, params)); } } /** * 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 DB db, final DatabaseMap dbMap, final Criteria crit, final Query query) throws TorqueException { UniqueList orderByClause = query.getOrderByClause(); UniqueList selectClause = query.getSelectClause(); UniqueList orderBy = crit.getOrderByColumns(); if (orderBy != null && orderBy.size() > 0) { // Check for each String/Character column and apply // toUpperCase(). for (int i = 0; i < orderBy.size(); i++) { String orderByColumn = (String) orderBy.get(i); String strippedColumnName = removeSQLFunction(orderByColumn); int dotPos = strippedColumnName.lastIndexOf('.'); if (dotPos == -1) { // We are not able to look up the table in the // tableMap, as no table name is given. Simply add // the orderBy and hope the user knows what he is // doing. orderByClause.add(orderByColumn); continue; } String tableName = strippedColumnName.substring(0, dotPos); String table = crit.getTableForAlias(tableName); if (table == null) { table = tableName; } // See if there's a space (between the column list and sort // order in ORDER BY table.column DESC). int spacePos = strippedColumnName.indexOf(' '); String columnName; if (spacePos == -1) { columnName = strippedColumnName.substring(dotPos + 1); } else { columnName = strippedColumnName.substring(dotPos + 1, spacePos); } ColumnMap column = dbMap.getTable(table).getColumn(columnName); // only ignore case in order by for string columns // which do not have a function around them if (column.getType() instanceof String && orderByColumn.indexOf('(') == -1) { // find space pos relative to orderByColumn spacePos = orderByColumn.indexOf(' '); if (spacePos == -1) { orderByClause.add(db.ignoreCaseInOrderBy(orderByColumn)); } else { orderByClause.add(db.ignoreCaseInOrderBy(orderByColumn.substring(0, spacePos)) + orderByColumn.substring(spacePos)); } selectClause.add(db.ignoreCaseInOrderBy(tableName + '.' + columnName)); } else { orderByClause.add(orderByColumn); } } } } /** * 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 Criteria crit, final Query query) throws TorqueException { UniqueList groupByClause = query.getGroupByClause(); UniqueList groupBy = crit.getGroupByColumns(); // need to allow for multiple group bys if (groupBy != null) { for (int i = 0; i < groupBy.size(); i++) { String columnName = (String) groupBy.get(i); String column = (String) crit.getAsColumns().get(columnName); if (column == null) { column = columnName; } if (column.indexOf('.') != -1) { groupByClause.add(column); } else { throwMalformedColumnNameException("group by", column); } } } } /** * 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 { Criteria.Criterion having = crit.getHaving(); if (having != null) { // String groupByString = 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 Criteria crit, final Query query) throws TorqueException { int limit = crit.getLimit(); int offset = crit.getOffset(); if (offset > 0 || limit >= 0) { DB db = Torque.getDB(crit.getDbName()); db.generateLimits(query, offset, limit); } } /** * Throws a TorqueException with the malformed column name error message. The error message looks like this: * * <p><code>Malformed column name in Criteria [criteriaPhrase]: '[columnName]' is not of the form 'table.column'</code> * * @param criteriaPhrase a String, one of "select", "join", or "order by" * @param columnName a String containing the offending column name * * @throws TorqueException Any exceptions caught during processing will be rethrown wrapped into a TorqueException. */ public static void throwMalformedColumnNameException(final String criteriaPhrase, final String columnName) throws TorqueException { StringBuffer sb = new StringBuffer().append("Malformed column name in Criteria ").append(criteriaPhrase) .append(": '").append(StringUtils.isEmpty(columnName) ? "<empty>" : columnName) .append("' is not of the form 'table.column'"); throw new TorqueException(sb.toString()); } /** * 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 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 */ public static String getTableNameForFromClause(final String tableName, final Criteria criteria) { String shortTableName = getUnqualifiedTableName(tableName); // Most of the time, the alias would be for the short name... String aliasName = criteria.getTableForAlias(shortTableName); if (StringUtils.isEmpty(aliasName)) { // But we should also check the FQN... aliasName = criteria.getTableForAlias(tableName); } if (StringUtils.isNotEmpty(aliasName)) { // If the tables have an alias, add an "<xxx> <yyy> statement" // <xxx> AS <yyy> causes problems on oracle return new StringBuffer(tableName.length() + aliasName.length() + 1).append(aliasName).append(" ") .append(tableName).toString(); } return tableName; } /** * Checks if the Tablename tableName is already contained in a from clause. If tableName and the tablenames in fromClause are * generated by getTablenameForFromClause(String, Criteria), (which they usually are), then 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 tableName * is null, true is returned. * * @param fromClause a list containing only elements of type. Query.FromElement * @param tableName the tablename to check * * @return if the Tablename tableName is already contained in a from clause. If tableName is null, true is returned. */ public static boolean fromClauseContainsTableName(final UniqueList fromClause, final String tableName) { if (tableName == null) { // usually this function is called to see if tableName should be // added to the fromClause. As null should not be added, // true is returned. return true; } for (Iterator it = fromClause.iterator(); it.hasNext();) { Query.FromElement fromElement = (Query.FromElement) it.next(); if (tableName.equals(fromElement.getTableName())) { 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 * @param criteria a criteria object to resolve a possible alias * @param query the query where the the tablename should be added to the from clause * * @return the table in the from clause which represents the supplied tableOrAliasName */ private static String addTableToFromClause(final String tableName, final Criteria criteria, Query query) { String tableNameForFromClause = getTableNameForFromClause(tableName, criteria); UniqueList queryFromClause = query.getFromClause(); // it is important that this piece of code is executed AFTER // the joins are processed if (!fromClauseContainsTableName(queryFromClause, tableNameForFromClause)) { Query.FromElement fromElement = new Query.FromElement(tableNameForFromClause, null, null); queryFromClause.add(fromElement); } return tableNameForFromClause; } /** Inner Interface that defines the Callback method for the Table creation loop. */ public interface TableCallback { /** * Callback Method for getTableSet() * * @param tables The current table name * @param key The current criterion key. * @param crit The Criteria used in getTableSet() */ void process(Set tables, String key, Criteria crit); } /** Inner Interface that defines the Callback method for the buildQuery Criterion evaluation */ public interface QueryCallback { /** * The callback for building a query String * * @param criterion The current criterion * @param params The parameter list passed to buildQueryString() * * @return WHERE SQL fragment for this criterion */ String process(Criterion criterion, List params); } }