openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/SQLBuffer.java

/*
 * 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.
 */
package org.apache.openjpa.jdbc.sql;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Objects;

import org.apache.openjpa.jdbc.identifier.DBIdentifier;
import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration;
import org.apache.openjpa.jdbc.kernel.exps.CollectionParam;
import org.apache.openjpa.jdbc.kernel.exps.Val;
import org.apache.openjpa.jdbc.schema.Column;
import org.apache.openjpa.jdbc.schema.Sequence;
import org.apache.openjpa.jdbc.schema.Table;
import org.apache.openjpa.kernel.Filters;
import org.apache.openjpa.kernel.exps.Parameter;


/**
 * Buffer for SQL statements that can be used to create
 * java.sql.PreparedStatements.
 * This buffer holds the SQL statement parameters and their corresponding
 * columns. The parameters introduced by the runtime system are distinguished
 * from the parameters set by the user.
 *
 * @author Marc Prud'hommeaux
 * @author Abe White
 * @author Pinaki Poddar
 *
 * @since 0.2.4
 */
public final class SQLBuffer
    implements Serializable, Cloneable {

    
    private static final long serialVersionUID = 1L;

    private static final String PARAMETER_TOKEN = "?";

    private final DBDictionary _dict;
    private final StringBuilder _sql = new StringBuilder();
    private List _subsels = null;
    private List _params = null;
    private List _cols = null;

    // Even element refers to an index of the _params list
    // Odd element refers to the user parameter
    private List _userIndex = null;
    private List _userParams = null;

    /**
     * Default constructor.
     */
    public SQLBuffer(DBDictionary dict) {
        _dict = dict;
    }

    /**
     * Copy constructor.
     */
    public SQLBuffer(SQLBuffer buf) {
        _dict = buf._dict;
        append(buf);
    }

    /**
     * Perform a shallow clone of this SQL Buffer.
     */
    @Override
    public Object clone() {
        return new SQLBuffer(this);
    }

    /**
     * Return true if the buffer is emtpy.
     */
    public boolean isEmpty() {
        return _sql.length() == 0;
    }

    /**
     * Append all SQL and parameters of the given buffer.
     */
    public SQLBuffer append(SQLBuffer buf) {
        append(buf, _sql.length(), (_params == null) ? 0 : _params.size(), true, false);
        return this;
    }

    /**
     * Append parameters only if the given buffer at the given positions.
    */
    public SQLBuffer appendParamOnly(SQLBuffer buf) {
        append(buf, _sql.length(), (_params == null) ? 0 : _params.size(), true, true);
        return this;
    }

    /**
     * Append parameters and/or SQL of the given buffer at the given positions.
     */
    private void append(SQLBuffer buf, int sqlIndex, int paramIndex,
        boolean subsels, boolean paramOnly) {
        if (subsels) {
            // only allow appending of buffers with subselects, not insertion
            if (_subsels != null && !_subsels.isEmpty()
                && sqlIndex != _sql.length())
                throw new IllegalStateException();
            if (buf._subsels != null && !buf._subsels.isEmpty()) {
                if (sqlIndex != _sql.length())
                    throw new IllegalStateException();
                if (_subsels == null)
                    _subsels = new ArrayList(buf._subsels.size());
                for (int i = 0; i < buf._subsels.size(); i++)
                    _subsels.add(((Subselect) buf._subsels.get(i)).
                        clone(sqlIndex, paramIndex));
            }
        }

        if (!paramOnly) {
            if (sqlIndex == _sql.length())
                _sql.append(buf._sql.toString());
            else
                _sql.insert(sqlIndex, buf._sql.toString());
        }

        if (buf._params != null) {
            if (_params == null)
                _params = new ArrayList();
            if (_cols == null && buf._cols != null) {
                _cols = new ArrayList();
                while (_cols.size() < _params.size())
                    _cols.add(null);
            }

            if (paramIndex == _params.size()) {
                _params.addAll(buf._params);
                if (buf._userParams != null) {
                    if (_userParams == null)
                        _userParams = new ArrayList();
                   _userParams.addAll(paramIndex, buf._userParams);
                }
                if (buf._userIndex != null) {
                    if (_userIndex == null)
                        _userIndex = new ArrayList();
                    _userIndex.addAll(buf._userIndex);
                }
                if (buf._cols != null)
                    _cols.addAll(buf._cols);
                else if (_cols != null)
                    while (_cols.size() < _params.size())
                        _cols.add(null);
            } else {
                _params.addAll(paramIndex, buf._params);
                if ( buf._userParams != null) {
                    if (_userParams == null)
                        _userParams = new ArrayList();
                    _userParams.addAll(paramIndex, buf._userParams);
                }
                if (buf._userIndex != null) {
                     if (_userIndex == null)
                         _userIndex = new ArrayList();
                     _userIndex.addAll(buf._userIndex);
                }
                if (buf._cols != null)
                    _cols.addAll(paramIndex, buf._cols);
                else if (_cols != null)
                    while (_cols.size() < _params.size())
                        _cols.add(paramIndex, null);
            }
        }

        if (_userIndex != null) {
            // fix up user parameter index(s)
            for (int i = 0; i < _userIndex.size(); i+=2) {
            	Object param = _userIndex.get(i+1);

            	Object previousParam = (i > 0) ? _userIndex.get(i-1) : null;
            	if ( param != previousParam) {
            		_userIndex.set(i, _userParams.indexOf(param));
            	}else{
            		//if there are multiple parameters for the in clause or the combined PK field,
            		//we got duplicate param objects in _userParams list.
            		//In order to find the right index, we have to skip params that's checked already.
            		int previousUserIndex = (Integer)_userIndex.get(i-2);
            		int userParamindex = 0;

                	for(Object next : _userParams){
                        if (next == param && userParamindex > previousUserIndex) {
                            _userIndex.set(i, userParamindex);
                            break;
                      }
                      userParamindex++;
                	}
            	}
            }
        }
    }

    public SQLBuffer append(DBIdentifier name) {
        _sql.append(_dict.toDBName(name));
        return this;
    }

    public SQLBuffer append(Table table) {
        _sql.append(_dict.getFullName(table, false));
        return this;
    }

    public SQLBuffer append(Sequence seq) {
        _sql.append(_dict.getFullName(seq));
        return this;
    }

    public SQLBuffer append(Column col) {
        _sql.append(_dict.getColumnDBName(col));
        return this;
    }

    public SQLBuffer append(String s) {
        _sql.append(s);
        return this;
    }

    /**
     * Append a subselect. This delays resolution of the select SQL.
     */
    public SQLBuffer append(Select sel, JDBCFetchConfiguration fetch) {
        return append(sel, fetch, false);
    }

    /**
     * Append a subselect count. This delays resolution of the select SQL.
     */
    public SQLBuffer appendCount(Select sel, JDBCFetchConfiguration fetch) {
        return append(sel, fetch, true);
    }

    /**
     * Append a subselect. This delays resolution of the select SQL.
     */
    private SQLBuffer append(Select sel, JDBCFetchConfiguration fetch,
        boolean count) {
        _sql.append("(");
        Subselect sub = new Subselect();
        sub.select = sel;
        sub.fetch = fetch;
        sub.count = count;
        sub.sqlIndex = _sql.length();
        sub.paramIndex = (_params == null) ? 0 : _params.size();
        _sql.append(")");

        if (_subsels == null)
            _subsels = new ArrayList(2);
        _subsels.add(sub);
        return this;
    }

    /**
     * Replace a subselect.
     */
    public boolean replace(Select old, Select sel) {
        if (_subsels == null)
            return false;
        Subselect sub;
        for (int i = 0; i < _subsels.size(); i++) {
            sub = (Subselect) _subsels.get(i);
            if (sub.select == old) {
                sub.select = sel;
                return true;
            }
        }
        return false;
    }

    /**
     * Append a parameter value.
     */
    public SQLBuffer appendValue(Object o) {
        return appendValue(o, null);
    }

    /**
     * Append a system inserted parameter value for a specific column.
     */
    public SQLBuffer appendValue(Object o, Column col) {
        return appendValue(o, col, null);
    }

    /**
     * Append a user parameter value for a specific column. User parameters
     * are marked as opposed to the parameters inserted by the internal runtime
     * system. This helps to reuse the buffer by reparmeterizing it with new
     * set of user parameters while the 'internal' parameters remain unchanged.
     *
     * @param userParam if non-null, designates a 'user' parameter.
     */
    public SQLBuffer appendValue(Object o, Column col, Parameter userParam) {
        return appendValue(o, col, userParam, true);
    }

    public SQLBuffer appendValue(Object o, Column col, Parameter userParam, boolean useParamToken) {
        if (o == null)
            _sql.append("NULL");
        else if (o instanceof Raw)
            _sql.append(o.toString());
        else {
            Class<?> type = Filters.wrap(o.getClass());
            if (useParamToken || !validParamLiteralType(type)) {
                _sql.append(PARAMETER_TOKEN);

                // initialize param and col lists; we hold off on col list until
                // we get the first non-null col
                if (_params == null)
                    _params = new ArrayList();
                if (_userParams == null)
                    _userParams = new ArrayList();
                if (col != null && _cols == null) {
                    _cols = new ArrayList();
                    while (_cols.size() < _params.size())
                        _cols.add(null);
                }

                _params.add(o);
                if (userParam != null) {
                    Object param = userParam;
                    if (userParam instanceof CollectionParam)
                        param = ((CollectionParam) userParam).clone();
                    _userParams.add(param);
                    if (_userIndex == null)
                        _userIndex = new ArrayList();
                    int index = _params.size()-1;
                    _userIndex.add(index);
                    _userIndex.add(param);
                }
                else
                    _userParams.add(o);
                if (_cols != null)
                    _cols.add(col);
            } else {
                if (type == String.class) {
                    _sql.append("'" + o.toString().replace("'", "''") + "'");

                } else if ( type == Character.class ) {
                    if (_dict.storeCharsAsNumbers) {
                        _sql.append(Integer.toString((Character) o));
                    } else {
                        _sql.append("'" + o.toString().replace("'", "''") + "'");
                    }
                } else if (type == Boolean.class) {
                    Boolean b = (Boolean) o;
                    // We store B(b)ooleans as ints. Convert
                    _sql.append(_dict.getBooleanRepresentation().getRepresentation(b));
                } else {
                    _sql.append(o.toString());
                }
            }
        }
        return this;
    }

    private boolean validParamLiteralType(Class<?> type) {
        boolean ret = type == String.class
                || type == Integer.class
                || type == Character.class
                || type == Boolean.class
                || type == Short.class
                || type == Long.class
                || type == Byte.class;
        return ret;
    }

    /**
     * Return the list of parameter values.
     */
    public List getParameters() {
        return (_params == null) ? Collections.EMPTY_LIST : _params;
    }

    /**
     * Get the user parameter positions in the list of parameters. The odd
     * element of the returned list contains an integer index that refers
     * to the position in the {@link #getParameters()} list. The even element
     * of the returned list refers to the user parameter key.
     * This structure is preferred over a normal map because a user parameter
     * may occur more than one in the parameters.
     */
    public List getUserParameters() {
        if (_userIndex == null)
            return Collections.EMPTY_LIST;
        return _userIndex;
    }

    /**
     * Return the SQL for this buffer.
     */
    public String getSQL() {
        return getSQL(false);
    }

    /**
     * Returns the SQL for this buffer.
     *
     * @param replaceParams if true, then replace parameters with the
     * actual parameter values
     */
    public String getSQL(boolean replaceParams) {
        resolveSubselects();
        String sql = _sql.toString();
        if (!replaceParams || _params == null || _params.isEmpty())
            return sql;

        StringBuilder buf = new StringBuilder();
        Iterator pi = _params.iterator();
        for (int i = 0; i < sql.length(); i++) {
            if (sql.charAt(i) != '?') {
                buf.append(sql.charAt(i));
                continue;
            }

            Object param = pi.hasNext() ? pi.next() : null;
            if (param == null)
                buf.append("NULL");
            else if (param instanceof Number || param instanceof Boolean)
                buf.append(param);
            else if (param instanceof String || param instanceof Character)
                buf.append("'").append(param).append("'");
            else
                buf.append("?");
        }
        return buf.toString();
    }

    /**
     * Resolve our delayed subselects.
     */
    private void resolveSubselects() {
        if (_subsels == null || _subsels.isEmpty())
            return;

        // add subsels backwards so that the stored insertion points of
        // later subsels remain valid
        Subselect sub;
        SQLBuffer buf;
        for (int i = _subsels.size() - 1; i >= 0; i--) {
            sub = (Subselect) _subsels.get(i);
            if (sub.count)
                buf = sub.select.toSelectCount();
            else
                buf = sub.select.toSelect(false, sub.fetch);
            buf.resolveSubselects();
            append(buf, sub.sqlIndex, sub.paramIndex, false, false);
        }
        _subsels.clear();
    }

    /**
     * Create and populate the parameters of a prepared statement using
     * the SQL in this buffer.
     */
    public PreparedStatement prepareStatement(Connection conn)
        throws SQLException {
        return prepareStatement(conn, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    }

    /**
     * Create and populate the parameters of a prepared statement using
     * the SQL in this buffer.
     */
    public PreparedStatement prepareStatement(Connection conn, int rsType,
        int rsConcur)
        throws SQLException {
        return prepareStatement(conn, null, rsType, rsConcur);
    }

    /**
     * Create and populate the parameters of a prepred statement using the
     * SQL in this buffer and the given fetch configuration.
     */
    public PreparedStatement prepareStatement(Connection conn,
        JDBCFetchConfiguration fetch, int rsType, int rsConcur)
        throws SQLException {
        if (rsType == -1 && fetch == null)
            rsType = ResultSet.TYPE_FORWARD_ONLY;
        else if (rsType == -1)
            rsType = fetch.getResultSetType();
        if (rsConcur == -1)
            rsConcur = ResultSet.CONCUR_READ_ONLY;

        PreparedStatement stmnt;
        if (rsType == ResultSet.TYPE_FORWARD_ONLY
            && rsConcur == ResultSet.CONCUR_READ_ONLY)
            stmnt = conn.prepareStatement(getSQL());
        else
            stmnt = conn.prepareStatement(getSQL(), rsType, rsConcur);
        try {
            setParameters(stmnt);
            if (fetch != null) {
                if (fetch.getFetchBatchSize() > 0)
                    stmnt.setFetchSize(
                        _dict.getBatchFetchSize(fetch.getFetchBatchSize()));
                if (rsType != ResultSet.TYPE_FORWARD_ONLY
                    && fetch.getFetchDirection() != ResultSet.FETCH_FORWARD)
                    stmnt.setFetchDirection(fetch.getFetchDirection());
            }
            return stmnt;
        } catch (SQLException se) {
            try {
                stmnt.close();
            } catch (SQLException se2) {
            }
            throw se;
        }
    }

    /**
     * Create and populate the parameters of a prepared statement using
     * the SQL in this buffer.
     */
    public CallableStatement prepareCall(Connection conn)
        throws SQLException {
        return prepareCall(conn, ResultSet.TYPE_FORWARD_ONLY,
            ResultSet.CONCUR_READ_ONLY);
    }

    /**
     * Create and populate the parameters of a prepared statement using
     * the SQL in this buffer.
     */
    public CallableStatement prepareCall(Connection conn, int rsType,
        int rsConcur)
        throws SQLException {
        return prepareCall(conn, null, rsType, rsConcur);
    }

    /**
     * Create and populate the parameters of a prepred statement using the
     * SQL in this buffer and the given fetch configuration.
     */
    public CallableStatement prepareCall(Connection conn,
        JDBCFetchConfiguration fetch, int rsType, int rsConcur)
        throws SQLException {
        if (rsType == -1 && fetch == null)
            rsType = ResultSet.TYPE_FORWARD_ONLY;
        else if (rsType == -1)
            rsType = fetch.getResultSetType();
        if (rsConcur == -1)
            rsConcur = ResultSet.CONCUR_READ_ONLY;

        CallableStatement stmnt;
        if (rsType == ResultSet.TYPE_FORWARD_ONLY
            && rsConcur == ResultSet.CONCUR_READ_ONLY)
            stmnt = conn.prepareCall(getSQL());
        else
            stmnt = conn.prepareCall(getSQL(), rsType, rsConcur);
        try {
            setParameters(stmnt);
            if (fetch != null) {
                if (fetch.getFetchBatchSize() > 0)
                    stmnt.setFetchSize(
                        _dict.getBatchFetchSize(fetch.getFetchBatchSize()));
                if (rsType != ResultSet.TYPE_FORWARD_ONLY
                    && fetch.getFetchDirection() != ResultSet.FETCH_FORWARD)
                    stmnt.setFetchDirection(fetch.getFetchDirection());
            }
            return stmnt;
        } catch (SQLException se) {
            try {
                stmnt.close();
            } catch (SQLException se2) {
            }
            throw se;
        }
    }

    /**
     * Populate the parameters of an existing PreparedStatement
     * with values from this buffer.
     */
    public void setParameters(PreparedStatement ps)
        throws SQLException {
        if (_params == null)
            return;

        Column col;
        for (int i = 0; i < _params.size(); i++) {
            col = (_cols == null) ? null : (Column) _cols.get(i);
            _dict.setUnknown(ps, i + 1, _params.get(i), col);
        }
    }

    @Override
    public int hashCode() {
        int hash = _sql.hashCode();
        return (_params == null) ? hash : hash ^ _params.hashCode();
    }

    /**
     * Compare internal SQL without resolving subselects or stringifying
     * parameters.
     */
    public boolean sqlEquals(String sql) {
        return _sql.toString().equals(sql);
    }

    @Override
    public boolean equals(Object other) {
        if (other == this)
            return true;
        if (!(other instanceof SQLBuffer))
            return false;

        SQLBuffer buf = (SQLBuffer) other;
        return _sql.equals(buf._sql)
            && Objects.equals(_params, buf._params);
    }

    /**
     * Replace SQL '?' with CAST string if required by DB platform
     * @param oper
     * @param val
     */
    public void addCastForParam(String oper, Val val) {
        if (_sql.charAt(_sql.length() - 1) == '?') {
            String castString = _dict.addCastAsType(oper, val);
            if (castString != null)
                _sql.replace(_sql.length() - 1, _sql.length(), castString);
        }
    }

    /**
     * Replace current buffer string with the new string
     *
     * @param start replace start position
     * @param end replace end position
     * @param newString
     */
    public void replaceSqlString(int start, int end, String newString) {
        _sql.replace(start, end, newString);
    }

    /**
     * Represents a subselect.
     */
    private static class Subselect {

        public Select select;
        public JDBCFetchConfiguration fetch;
        public boolean count;
        public int sqlIndex;
        public int paramIndex;

        public Subselect clone(int sqlIndex, int paramIndex) {
            if (sqlIndex == 0 && paramIndex == 0)
                return this;

            Subselect sub = new Subselect();
            sub.select = select;
            sub.fetch = fetch;
            sub.count = count;
            sub.sqlIndex = this.sqlIndex + sqlIndex;
            sub.paramIndex = this.paramIndex + paramIndex;
            return sub;
        }
    }

    public void setParameters(List params) {
        _params = params;
    }

    public List getColumns() {
        return _cols;
    }
}