sqlexpression.java

torque服务器源代码

package org.apache.torque.util;

/* ====================================================================
 * The Apache Software License, Version 1.1
 *
 * Copyright (c) 2001-2003 The Apache Software Foundation.  All rights
 * reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 *
 * 1. Redistributions of source code must retain the above copyright
 *    notice, this list of conditions and the following disclaimer.
 *
 * 2. Redistributions in binary form must reproduce the above copyright
 *    notice, this list of conditions and the following disclaimer in
 *    the documentation and/or other materials provided with the
 *    distribution.
 *
 * 3. The end-user documentation included with the redistribution,
 *    if any, must include the following acknowledgment:
 *       "This product includes software developed by the
 *        Apache Software Foundation (http://www.apache.org/)."
 *    Alternately, this acknowledgment may appear in the software itself,
 *    if and wherever such third-party acknowledgments normally appear.
 *
 * 4. The names "Apache" and "Apache Software Foundation" and
 *    "Apache Turbine" must not be used to endorse or promote products
 *    derived from this software without prior written permission. For
 *    written permission, please contact [e-mail].
 *
 * 5. Products derived from this software may not be called "Apache",
 *    "Apache Turbine", nor may "Apache" appear in their name, without
 *    prior written permission of the Apache Software Foundation.
 *
 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 * SUCH DAMAGE.
 * ====================================================================
 *
 * This software consists of voluntary contributions made by many
 * individuals on behalf of the Apache Software Foundation.  For more
 * information on the Apache Software Foundation, please see
 * <http://www.apache.org/>.
 */

import java.lang.reflect.Array;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.torque.TorqueException;
import org.apache.torque.adapter.DB;
import org.apache.torque.om.DateKey;
import org.apache.torque.om.ObjectKey;
import org.apache.torque.om.StringKey;


/**
 * This class represents a part of an SQL query found in the <code>WHERE</code>
 * section.  For example:
 * <pre>
 * table_a.column_a = table_b.column_a
 * column LIKE 'F%'
 * table.column < 3
 * </pre>
 * This class is used primarily by {@link org.apache.torque.util.BasePeer}.
 *
 * @author <a href="mailto:[e-mail]">John D. McNally</a>
 * @author <a href="mailto:[e-mail]">Daniel Rall</a>
 * @author <a href="mailto:[e-mail]">Fedor Karpelevitch</a>
 * @author <a href="mailto:[e-mail]">Martin Poeschl</a>
 * @version $Id: SqlExpression.java,v 1.24 2003/08/27 22:50:11 mpoeschl Exp $
 */
public class SqlExpression
{
    /** escaped single quote */
    private static final char SINGLE_QUOTE = '\'';
    /** escaped backslash */
    private static final char BACKSLASH = '\\';

    /**
     * Used to specify a join on two columns.
     *
     * @param column A column in one of the tables to be joined.
     * @param relatedColumn The column in the other table to be joined.
     * @return A join expression, e.g. UPPER(table_a.column_a) =
     *         UPPER(table_b.column_b).
     */
    public static String buildInnerJoin(String column, String relatedColumn)
    {
        // 'db' can be null because 'ignoreCase' is false.
        return buildInnerJoin(column, relatedColumn, false, null);
    }

    /**
     * Used to specify a join on two columns.
     *
     * @param column A column in one of the tables to be joined.
     * @param relatedColumn The column in the other table to be joined.
     * @param ignoreCase If true and columns represent Strings, the appropriate
     *        function defined for the database will be used to ignore
     *        differences in case.
     * @param db Represents the database in use for vendor-specific functions.
     * @return A join expression, e.g. UPPER(table_a.column_a) =
     *         UPPER(table_b.column_b).
     */
    public static String buildInnerJoin(String column,
                                         String relatedColumn,
                                         boolean ignoreCase,
                                         DB db)
    {
        int addlength = (ignoreCase) ? 25 : 1;
        StringBuffer sb = new StringBuffer(column.length()
                + relatedColumn.length() + addlength);
        buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
        return sb.toString();
    }

    /**
     * Used to specify a join on two columns.
     *
     * @param column A column in one of the tables to be joined.
     * @param relatedColumn The column in the other table to be joined.
     * @param ignoreCase If true and columns represent Strings, the appropriate
     *        function defined for the database will be used to ignore
     *        differences in case.
     * @param db Represents the database in use for vendor-specific functions.
     * @param whereClause A StringBuffer to which the sql expression will be
     *        appended.
     */
    public static void buildInnerJoin(String column,
                                       String relatedColumn,
                                       boolean ignoreCase,
                                       DB db,
                                       StringBuffer whereClause)
    {
        if (ignoreCase)
        {
            whereClause.append(db.ignoreCase(column))
                    .append('=')
                    .append(db.ignoreCase(relatedColumn));
        }
        else
        {
            whereClause.append(column)
                    .append('=')
                    .append(relatedColumn);
        }
    }


    /**
     * Builds a simple SQL expression.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
     *        !=, LIKE, etc.
     * @return A simple SQL expression, e.g. UPPER(table_a.column_a)
     *         LIKE UPPER('ab%c').
     * @throws TorqueException Any exceptions caught during processing will be
     *         rethrown wrapped into a TorqueException.
     */
    public static String build(String columnName,
                                Object criteria,
                                SqlEnum comparison)
        throws TorqueException
    {
        // 'db' can be null because 'ignoreCase' is null
        return build(columnName, criteria, comparison, false, null);
    }

    /**
     * Builds a simple SQL expression.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
     *        !=, LIKE, etc.
     * @param ignoreCase If true and columns represent Strings, the appropriate
     *        function defined for the database will be used to ignore
     *        differences in case.
     * @param db Represents the database in use, for vendor specific functions.
     * @return A simple sql expression, e.g. UPPER(table_a.column_a)
     *         LIKE UPPER('ab%c').
     * @throws TorqueException Any exceptions caught during processing will be
     *         rethrown wrapped into a TorqueException.
     */
    public static String build(String columnName,
                                Object criteria,
                                SqlEnum comparison,
                                boolean ignoreCase,
                                DB db)
        throws TorqueException
    {
        int addlength = (ignoreCase ? 40 : 20);
        StringBuffer sb = new StringBuffer(columnName.length() + addlength);
        build(columnName, criteria, comparison, ignoreCase, db, sb);
        return sb.toString();
    }

    /**
     * Builds a simple SQL expression.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
     *        !=, LIKE, etc.
     * @param ignoreCase If true and columns represent Strings, the appropriate
     *        function defined for the database will be used to ignore
     *        differences in case.
     * @param db Represents the database in use, for vendor specific functions.
     * @param whereClause A StringBuffer to which the sql expression will be
     *        appended.
     */
    public static void build(String columnName,
                              Object criteria,
                              SqlEnum comparison,
                              boolean ignoreCase,
                              DB db,
                              StringBuffer whereClause)
    {
        // Allow null criteria
        // This will result in queries like
        // insert into table (name, parent) values ('x', null);
        //

        /* Check to see if the criteria is an ObjectKey
         * and if the value of that ObjectKey is null.
         * In that case, criteria should be null.
         */

        if (criteria != null && criteria instanceof ObjectKey)
        {
            if (((ObjectKey) criteria).getValue() == null)
            {
                criteria = null;
            }
        }
        /*  If the criteria is null, check to see comparison
         *  is an =, <>, or !=.  If so, replace the comparison
         *  with the proper IS or IS NOT.
         */

        if (criteria == null)
        {
            criteria = "null";
            if (comparison.equals(Criteria.EQUAL))
            {
                comparison = Criteria.ISNULL;
            }
            else if (comparison.equals(Criteria.NOT_EQUAL))
            {
                comparison = Criteria.ISNOTNULL;
            }
            else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
            {
                comparison = Criteria.ISNOTNULL;
            }
        }
        else
        {
            if (criteria instanceof String || criteria instanceof StringKey)
            {
                criteria = quoteAndEscapeText(criteria.toString(), db);
            }
            else if (criteria instanceof Date)
            {
                Date dt = (Date) criteria;
                criteria = db.getDateString(dt);
            }
            else if (criteria instanceof DateKey)
            {
                Date dt = (Date) ((DateKey) criteria).getValue();
                criteria = db.getDateString(dt);
            }
            else if (criteria instanceof Boolean)
            {
                criteria = db.getBooleanString((Boolean) criteria);
            }
        }

        if (comparison.equals(Criteria.LIKE)
                || comparison.equals(Criteria.NOT_LIKE))
        {
            buildLike(columnName, (String) criteria, comparison,
                       ignoreCase, db, whereClause);
        }
        else if (comparison.equals(Criteria.IN)
                || comparison.equals(Criteria.NOT_IN))
        {
            buildIn(columnName, criteria, comparison,
                     ignoreCase, db, whereClause);
        }
        else
        {
            // Do not put the upper/lower keyword around IS NULL
            //  or IS NOT NULL