/*
* Copyright 2002-2011 the original author or authors.
*
* Licensed 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.springframework.jdbc.core.namedparam;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.util.Assert;
/**
* Helper methods for named parameter parsing.
* Only intended for internal use within Spring's JDBC framework.
*
* @author Thomas Risberg
* @author Juergen Hoeller
* @since 2.0
*/
public abstract class NamedParameterUtils {
/**
* Set of characters that qualify as parameter separators,
* indicating that a parameter name in a SQL String has ended.
*/
private static final char[] PARAMETER_SEPARATORS =
new char[] {'"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+', '-', '*', '%', '/', '\\', '<', '>', '^'};
/**
* Set of characters that qualify as comment or quotes starting characters.
*/
private static final String[] START_SKIP =
new String[] {"'", "\"", "--", "/*"};
/**
* Set of characters that at are the corresponding comment or quotes ending characters.
*/
private static final String[] STOP_SKIP =
new String[] {"'", "\"", "\n", "*/"};
//-------------------------------------------------------------------------
// Core methods used by NamedParameterJdbcTemplate and SqlQuery/SqlUpdate
//-------------------------------------------------------------------------
/**
* Parse the SQL statement and locate any placeholders or named parameters.
* Named parameters are substituted for a JDBC placeholder.
* @param sql the SQL statement
* @return the parsed statement, represented as ParsedSql instance
*/
public static ParsedSql parseSqlStatement(final String sql) {
Assert.notNull(sql, "SQL must not be null");
Set<String> namedParameters = new HashSet<String>();
String sqlToUse = sql;
List<ParameterHolder> parameterList = new ArrayList<ParameterHolder>();
char[] statement = sql.toCharArray();
int namedParameterCount = 0;
int unnamedParameterCount = 0;
int totalParameterCount = 0;
int escapes = 0;
int i = 0;
while (i < statement.length) {
int skipToPosition = skipCommentsAndQuotes(statement, i);
if (i != skipToPosition) {
if (skipToPosition >= statement.length) {
break;
}
i = skipToPosition;
}
char c = statement[i];
if (c == ':' || c == '&') {
int j = i + 1;
if (j < statement.length && statement[j] == ':' && c == ':') {
// Postgres-style "::" casting operator - to be skipped.
i = i + 2;
continue;
}
String parameter = null;
if (j < statement.length && c == ':' && statement[j] == '{') {
// :{x} style parameter
while (j < statement.length && !('}' == statement[j])) {
j++;
if (':' == statement[j] || '{' == statement[j]) {
throw new InvalidDataAccessApiUsageException("Parameter name contains invalid character '" + statement[j] + "' at position " + i + " in statement " + sql);
}
}
if (j >= statement.length) {
throw new InvalidDataAccessApiUsageException("Non-terminated named parameter declaration at position " + i + " in statement " + sql);
}
if (j - i > 3) {
parameter = sql.substring(i + 2, j);
namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter);
totalParameterCount = addNamedParameter(parameterList, totalParameterCount, escapes, i, j + 1, parameter);
}
j++;
}
else {
while (j < statement.length && !isParameterSeparator(statement[j])) {
j++;
}
if (j - i > 1) {
parameter = sql.substring(i + 1, j);
namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter);
totalParameterCount = addNamedParameter(parameterList, totalParameterCount, escapes, i, j, parameter);
}
}
i = j - 1;
}
else {
if (c == '\\') {
int j = i + 1;
if (j < statement.length && statement[j] == ':') {
// this is an escaped : and should be skipped
sqlToUse = sqlToUse.substring(0, i - escapes) + sqlToUse.substring(i - escapes + 1);
escapes++;
i = i + 2;
continue;
}
}
if (c == '?') {
unnamedParameterCount++;
totalParameterCount++;
}
}
i++;
}
ParsedSql parsedSql = new ParsedSql(sqlToUse);
for (ParameterHolder ph : parameterList) {
parsedSql.addNamedParameter(ph.getParameterName(), ph.getStartIndex(), ph.getEndIndex());
}
parsedSql.setNamedParameterCount(namedParameterCount);
parsedSql.setUnnamedParameterCount(unnamedParameterCount);
parsedSql.setTotalParameterCount(totalParameterCount);
return parsedSql;
}
private static int addNamedParameter(List<ParameterHolder> parameterList, int totalParameterCount, int escapes, int i, int j,
String parameter) {
parameterList.add(new ParameterHolder(parameter, i - escapes, j - escapes));
totalParameterCount++;
return totalParameterCount;
}
private static int addNewNamedParameter(Set<String> namedParameters, int namedParameterCount, String parameter) {
if (!namedParameters.contains(parameter)) {
namedParameters.add(parameter);
namedParameterCount++;
}
return namedParameterCount;
}
/**
* Skip over comments and quoted names present in an SQL statement
* @param statement character array containing SQL statement
* @param position current position of statement
* @return next position to process after any comments or quotes are skipped
*/
private static int skipCommentsAndQuotes(char[] statement, int position) {
for (int i = 0; i < START_SKIP.length; i++) {
if (statement[position] == START_SKIP[i].charAt(0)) {
boolean match = true;
for (int j = 1; j < START_SKIP[i].length(); j++) {
if (!(statement[position + j] == START_SKIP[i].charAt(j))) {
match = false;
break;
}
}
if (match) {
int offset = START_SKIP[i].length();
for (int m = position + offset; m < statement.length; m++) {
if (statement[m] == STOP_SKIP[i].charAt(0)) {
boolean endMatch = true;
int endPos = m;
for (int n = 1; n < STOP_SKIP[i].length(); n++) {
if (m + n >= statement.length) {
// last comment not closed properly
return statement.length;
}
if (!(statement[m + n] == STOP_SKIP[i].charAt(n))) {
endMatch = false;
break;
}
endPos = m + n;
}
if (endMatch) {
// found character sequence ending comment or quote
return endPos + 1;
}
}
}
// character sequence ending comment or quote not found
return statement.length;
}
}
}
return position;
}
/**
* Parse the SQL statement and locate any placeholders or named parameters.
* Named parameters are substituted for a JDBC placeholder and any select list
* is expanded to the required number of placeholders. Select lists may contain
* an array of objects and in that case the placeholders will be grouped and
* enclosed with parantheses. This allows for the use of "expression lists" in
* the SQL statement like:<br/>
* select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))
* <p>The parameter values passed in are used to determine the number of
* placeholder to be used for a select list. Select lists should be limited
* to 100 or fewer elements. A larger number of elements is not guaramteed to
* be supported by the database and is strictly vendor-dependent.
* @param parsedSql the parsed represenation of the SQL statement
* @param paramSource the source for named parameters
* @return the SQL statement with substituted parameters
* @see #parseSqlStatement
*/
public static String substituteNamedParameters(ParsedSql parsedSql, SqlParameterSource paramSource) {
String originalSql = parsedSql.getOriginalSql();
StringBuilder actualSql = new StringBuilder();
List paramNames = parsedSql.getParameterNames();
int lastIndex = 0;
for (int i = 0; i < paramNames.size(); i++) {
String paramName = (String) paramNames.get(i);
int[] indexes = parsedSql.getParameterIndexes(i);
int startIndex = indexes[0];
int endIndex = indexes[1];
actualSql.append(originalSql.substring(lastIndex, startIndex));
if (paramSource != null && paramSource.hasValue(paramName)) {
Object value = paramSource.getValue(paramName);
if (value instanceof Collection) {
Iterator entryIter = ((Collection) value).iterator();
int k = 0;
while (entryIter.hasNext()) {
if (k > 0) {
actualSql.append(", ");
}
k++;
Object entryItem = entryIter.next();
if (entryItem instanceof Object[]) {
Object[] expressionList = (Object[]) entryItem;
actualSql.append("(");
for (int m = 0; m < expressionList.length; m++) {
if (m > 0) {
actualSql.append(", ");
}
actualSql.append("?");
}
actualSql.append(")");
}
else {
actualSql.append("?");
}
}
}
else {
actualSql.append("?");
}
}
else {
actualSql.append("?");
}
lastIndex = endIndex;
}
actualSql.append(originalSql.substring(lastIndex, originalSql.length()));
return actualSql.toString();
}
/**
* Convert a Map of named parameter values to a corresponding array.
* @param parsedSql the parsed SQL statement
* @param paramSource the source for named parameters
* @param declaredParams the List of declared SqlParameter objects
* (may be <code>null</code>). If specified, the parameter metadata will
* be built into the value array in the form of SqlParameterValue objects.
* @return the array of values
*/
public static Object[] buildValueArray(
ParsedSql parsedSql, SqlParameterSource paramSource, List<SqlParameter> declaredParams) {
Object[] paramArray = new Object[parsedSql.getTotalParameterCount()];
if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) {
throw new InvalidDataAccessApiUsageException(
"You can't mix named and traditional ? placeholders. You have " +
parsedSql.getNamedParameterCount() + " named parameter(s) and " +
parsedSql.getUnnamedParameterCount() + " traditonal placeholder(s) in [" +
parsedSql.getOriginalSql() + "]");
}
List<String> paramNames = parsedSql.getParameterNames();
for (int i = 0; i < paramNames.size(); i++) {
String paramName = paramNames.get(i);
try {
Object value = paramSource.getValue(paramName);
SqlParameter param = findParameter(declaredParams, paramName, i);
paramArray[i] = (param != null ? new SqlParameterValue(param, value) : value);
}
catch (IllegalArgumentException ex) {
throw new InvalidDataAccessApiUsageException(
"No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage());
}
}
return paramArray;
}
/**
* Find a matching parameter in the given list of declared parameters.
* @param declaredParams the declared SqlParameter objects
* @param paramName the name of the desired parameter
* @param paramIndex the index of the desired parameter
* @return the declared SqlParameter, or <code>null</code> if none found
*/
private static SqlParameter findParameter(List<SqlParameter> declaredParams, String paramName, int paramIndex) {
if (declaredParams != null) {
// First pass: Look for named parameter match.
for (SqlParameter declaredParam : declaredParams) {
if (paramName.equals(declaredParam.getName())) {
return declaredParam;
}
}
// Second pass: Look for parameter index match.
if (paramIndex < declaredParams.size()) {
SqlParameter declaredParam = declaredParams.get(paramIndex);
// Only accept unnamed parameters for index matches.
if (declaredParam.getName() == null) {
return declaredParam;
}
}
}
return null;
}
/**
* Determine whether a parameter name ends at the current position,
* that is, whether the given character qualifies as a separator.
*/
private static boolean isParameterSeparator(char c) {
if (Character.isWhitespace(c)) {
return true;
}
for (char separator : PARAMETER_SEPARATORS) {
if (c == separator) {
return true;
}
}
return false;
}
/**
* Convert parameter types from an SqlParameterSource into a corresponding int array.
* This is necessary in order to reuse existing methods on JdbcTemplate.
* Any named parameter types are placed in the correct position in the
* Object array based on the parsed SQL statement info.
* @param parsedSql the parsed SQL statement
* @param paramSource the source for named parameters
*/
public static int[] buildSqlTypeArray(ParsedSql parsedSql, SqlParameterSource paramSource) {
int[] sqlTypes = new int[parsedSql.getTotalParameterCount()];
List<String> paramNames = parsedSql.getParameterNames();
for (int i = 0; i < paramNames.size(); i++) {
String paramName = paramNames.get(i);
sqlTypes[i] = paramSource.getSqlType(paramName);
}
return sqlTypes;
}
/**
* Convert parameter declarations from an SqlParameterSource to a corresponding List of SqlParameters.
* This is necessary in order to reuse existing methods on JdbcTemplate.
* The SqlParameter for a named parameter is placed in the correct position in the
* resulting list based on the parsed SQL statement info.
* @param parsedSql the parsed SQL statement
* @param paramSource the source for named parameters
*/
public static List<SqlParameter> buildSqlParameterList(ParsedSql parsedSql, SqlParameterSource paramSource) {
List<String> paramNames = parsedSql.getParameterNames();
List<SqlParameter> params = new LinkedList<SqlParameter>();
for (String paramName : paramNames) {
SqlParameter param = new SqlParameter(
paramName,
paramSource.getSqlType(paramName),
paramSource.getTypeName(paramName));
params.add(param);
}
return params;
}
//-------------------------------------------------------------------------
// Convenience methods operating on a plain SQL String
//-------------------------------------------------------------------------
/**
* Parse the SQL statement and locate any placeholders or named parameters.
* Named parameters are substituted for a JDBC placeholder.
* <p>This is a shortcut version of
* {@link #parseSqlStatement(String)} in combination with
* {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
* @param sql the SQL statement
* @return the actual (parsed) SQL statement
*/
public static String parseSqlStatementIntoString(String sql) {
ParsedSql parsedSql = parseSqlStatement(sql);
return substituteNamedParameters(parsedSql, null);
}
/**
* Parse the SQL statement and locate any placeholders or named parameters.
* Named parameters are substituted for a JDBC placeholder and any select list
* is expanded to the required number of placeholders.
* <p>This is a shortcut version of
* {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
* @param sql the SQL statement
* @param paramSource the source for named parameters
* @return the SQL statement with substituted parameters
*/
public static String substituteNamedParameters(String sql, SqlParameterSource paramSource) {
ParsedSql parsedSql = parseSqlStatement(sql);
return substituteNamedParameters(parsedSql, paramSource);
}
/**
* Convert a Map of named parameter values to a corresponding array.
* <p>This is a shortcut version of
* {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}.
* @param sql the SQL statement
* @param paramMap the Map of parameters
* @return the array of values
*/
public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) {
ParsedSql parsedSql = parseSqlStatement(sql);
return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null);
}
private static class ParameterHolder {
private String parameterName;
private int startIndex;
private int endIndex;
public ParameterHolder(String parameterName, int startIndex, int endIndex) {
super();
this.parameterName = parameterName;
this.startIndex = startIndex;
this.endIndex = endIndex;
}
public String getParameterName() {
return parameterName;
}
public int getStartIndex() {
return startIndex;
}
public int getEndIndex() {
return endIndex;
}
}
}