sqlexpression.java

torque服务器源代码

            if (comparison.equals(Criteria.ISNULL)
                    || comparison.equals(Criteria.ISNOTNULL))
            {
                whereClause.append(columnName)
                        .append(comparison);
            }
            else
            {
                String columnValue = criteria.toString();
                if (ignoreCase && db != null)
                {
                    columnName = db.ignoreCase(columnName);
                    columnValue = db.ignoreCase(columnValue);
                }
                whereClause.append(columnName)
                        .append(comparison)
                        .append(columnValue);
            }
        }
    }

    /**
     * Takes a columnName and criteria and builds an SQL phrase based
     * on whether wildcards are present and the state of the
     * ignoreCase 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 columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison Whether to do a LIKE or a NOT LIKE
     * @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 An SQL expression.
     */
    static String buildLike(String columnName,
                             String criteria,
                             SqlEnum comparison,
                             boolean ignoreCase,
                             DB db)
    {
        StringBuffer whereClause = new StringBuffer();
        buildLike(columnName, criteria, comparison, ignoreCase, db,
                   whereClause);
        return whereClause.toString();
    }

    /**
     * Takes a columnName and criteria and builds an SQL phrase based
     * on whether wildcards are present and the state of the
     * ignoreCase 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 columnName A column name.
     * @param criteria The value to compare the column against.
     * @param comparison Whether to do a LIKE or a NOT LIKE
     * @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.
     */
    static void buildLike(String columnName,
                           String criteria,
                           SqlEnum comparison,
                           boolean ignoreCase,
                           DB db,
                           StringBuffer whereClause)
    {
        // If selection is case insensitive use SQL UPPER() function
        // on column name.
        if (ignoreCase)
        {
            columnName = db.ignoreCase(columnName);
        }
        whereClause.append(columnName);

        // If selection criteria contains wildcards use LIKE otherwise
        // use = (equals).  Wildcards can be escaped by prepending
        // them with \ (backslash).
        String equalsOrLike = " = ";
        int position = 0;
        StringBuffer sb = new StringBuffer();
        while (position < criteria.length())
        {
            char checkWildcard = criteria.charAt(position);

            switch (checkWildcard)
            {
            case BACKSLASH:
                // Determine whether to skip over next character.
                switch (criteria.charAt(position + 1))
                {
                case '%':
                case '_':
                case '*':
                case '?':
                case BACKSLASH:
                    position++;
                    break;
                }
                break;
            case '%':
            case '_':
                equalsOrLike = comparison.toString();
                break;
            case '*':
                equalsOrLike = comparison.toString();
                checkWildcard = '%';
                break;
            case '?':
                equalsOrLike = comparison.toString();
                checkWildcard = '_';
                break;
            }

            sb.append(checkWildcard);
            position++;
        }
        whereClause.append(equalsOrLike);

        // If selection is case insensitive use SQL UPPER() function
        // on criteria.
        String clauseItem = sb.toString();
        if (ignoreCase)
        {
            clauseItem = db.ignoreCase(clauseItem);
        }
        whereClause.append(clauseItem);
    }

    /**
     * Takes a columnName and criteria (which must be an array) and
     * builds a SQL 'IN' expression taking into account the ignoreCase
     * flag.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison Either " IN " or " NOT IN ".
     * @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 An SQL expression.
     */
    static String buildIn(String columnName,
                          Object criteria,
                          SqlEnum comparison,
                          boolean ignoreCase,
                          DB db)
    {
        StringBuffer whereClause = new StringBuffer();
        buildIn(columnName, criteria, comparison,
                ignoreCase, db, whereClause);
        return whereClause.toString();
    }

    /**
     * Takes a columnName and criteria (which must be an array) and
     * builds a SQL 'IN' expression taking into account the ignoreCase
     * flag.
     *
     * @param columnName A column.
     * @param criteria The value to compare the column against.
     * @param comparison Either " IN " or " NOT IN ".
     * @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.
     */
    static void buildIn(String columnName,
                        Object criteria,
                        SqlEnum comparison,
                        boolean ignoreCase,
                        DB db,
                        StringBuffer whereClause)
    {
        if (ignoreCase)
        {
            whereClause.append(db.ignoreCase(columnName));
        }
        else
        {
            whereClause.append(columnName);
        }

        whereClause.append(comparison);
        HashSet inClause = new HashSet();
        if (criteria instanceof List)
        {
            Iterator iter = ((List) criteria).iterator();
            while (iter.hasNext())
            {
                Object value = iter.next();

                // The method processInValue() quotes the string
                // and/or wraps it in UPPER().
                inClause.add(processInValue(value, ignoreCase, db));
            }
        }
        else
        {
            // Assume array.
            for (int i = 0; i < Array.getLength(criteria); i++)
            {
                Object value = Array.get(criteria, i);

                // The method processInValue() quotes the string
                // and/or wraps it in UPPER().
                inClause.add(processInValue(value, ignoreCase, db));
            }
        }
        whereClause.append('(')
                   .append(StringUtils.join(inClause.iterator(), ","))
                   .append(')');
    }

    /**
     * Creates an appropriate string for an 'IN' clause from an
     * object.  Adds quoting and/or UPPER() as appropriate.  This is
     * broken out into a seperate method as it is used in two places
     * in buildIn, depending on whether an array or List is being
     * looped over.
     *
     * @param value The value to process.
     * @param ignoreCase Coerce the value suitably for ignoring case.
     * @param db Represents the database in use for vendor specific functions.
     * @return Processed value as String.
     */
    static String processInValue(Object value,
                                 boolean ignoreCase,
                                 DB db)
    {
        String ret = null;
        if (value instanceof String)
        {
            ret = quoteAndEscapeText((String) value, db);
        }
        else
        {
            ret = value.toString();
        }
        if (ignoreCase)
        {
            ret = db.ignoreCase(ret);
        }
        return ret;
    }

    /**
     * Quotes and escapes raw text for placement in a SQL expression.
     * For simplicity, the text is assumed to be neither quoted nor
     * escaped.
     *
     * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
     * @param db the db
     * @return Quoted and escaped text.
     */
    public static String quoteAndEscapeText(String rawText, DB db)
    {
        StringBuffer buf = new StringBuffer((int) (rawText.length() * 1.1));

        // Some databases do not need escaping.
        String escapeString = new String();
        if (db != null && !db.escapeText())
        {
            escapeString = String.valueOf(BACKSLASH);
        }
        else
        {
            escapeString = String.valueOf(BACKSLASH)
                    + String.valueOf(BACKSLASH);
        }

        char[] data = rawText.toCharArray();
        buf.append(SINGLE_QUOTE);
        for (int i = 0; i < data.length; i++)
        {
            switch (data[i])
            {
            case SINGLE_QUOTE:
                buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
                break;
            case BACKSLASH:
                buf.append(escapeString);
                break;
            default:
                buf.append(data[i]);
            }
        }
        buf.append(SINGLE_QUOTE);

        return buf.toString();
    }
}