001: /*
002: * Copyright 2002-2007 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package org.springframework.jdbc.core.namedparam;
018:
019: import java.util.Collection;
020: import java.util.HashSet;
021: import java.util.Iterator;
022: import java.util.List;
023: import java.util.Map;
024: import java.util.Set;
025:
026: import org.springframework.dao.InvalidDataAccessApiUsageException;
027: import org.springframework.jdbc.core.SqlParameter;
028: import org.springframework.jdbc.core.SqlParameterValue;
029: import org.springframework.util.Assert;
030:
031: /**
032: * Helper methods for named parameter parsing.
033: * Only intended for internal use within Spring's JDBC framework.
034: *
035: * @author Thomas Risberg
036: * @author Juergen Hoeller
037: * @since 2.0
038: */
039: public abstract class NamedParameterUtils {
040:
041: /**
042: * Set of characters that qualify as parameter separators,
043: * indicating that a parameter name in a SQL String has ended.
044: */
045: private static final char[] PARAMETER_SEPARATORS = new char[] {
046: '"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+',
047: '-', '*', '%', '/', '\\', '<', '>', '^' };
048:
049: //-------------------------------------------------------------------------
050: // Core methods used by NamedParameterJdbcTemplate and SqlQuery/SqlUpdate
051: //-------------------------------------------------------------------------
052:
053: /**
054: * Parse the SQL statement and locate any placeholders or named parameters.
055: * Named parameters are substituted for a JDBC placeholder.
056: * @param sql the SQL statement
057: * @return the parsed statement, represented as ParsedSql instance
058: */
059: public static ParsedSql parseSqlStatement(String sql) {
060: Assert.notNull(sql, "SQL must not be null");
061:
062: Set namedParameters = new HashSet();
063: ParsedSql parsedSql = new ParsedSql(sql);
064:
065: char[] statement = sql.toCharArray();
066: boolean withinQuotes = false;
067: char currentQuote = '-';
068: int namedParameterCount = 0;
069: int unnamedParameterCount = 0;
070: int totalParameterCount = 0;
071:
072: int i = 0;
073: while (i < statement.length) {
074: char c = statement[i];
075: if (withinQuotes) {
076: if (c == currentQuote) {
077: withinQuotes = false;
078: currentQuote = '-';
079: }
080: } else {
081: if (c == '"' || c == '\'') {
082: withinQuotes = true;
083: currentQuote = c;
084: } else {
085: if (c == ':' || c == '&') {
086: int j = i + 1;
087: while (j < statement.length
088: && !isParameterSeparator(statement[j])) {
089: j++;
090: }
091: if (j - i > 1) {
092: String parameter = sql.substring(i + 1, j);
093: if (!namedParameters.contains(parameter)) {
094: namedParameters.add(parameter);
095: namedParameterCount++;
096: }
097: parsedSql
098: .addNamedParameter(parameter, i, j);
099: totalParameterCount++;
100: }
101: i = j - 1;
102: } else {
103: if (c == '?') {
104: unnamedParameterCount++;
105: totalParameterCount++;
106: }
107: }
108: }
109: }
110: i++;
111: }
112: parsedSql.setNamedParameterCount(namedParameterCount);
113: parsedSql.setUnnamedParameterCount(unnamedParameterCount);
114: parsedSql.setTotalParameterCount(totalParameterCount);
115: return parsedSql;
116: }
117:
118: /**
119: * Parse the SQL statement and locate any placeholders or named parameters.
120: * Named parameters are substituted for a JDBC placeholder and any select list
121: * is expanded to the required number of placeholders.
122: * <p>The parameter values passed in are used to determine the number of
123: * placeholder to be used for a select list. Select lists should be limited
124: * to 100 or fewer elements. A larger number of elements is not guaramteed to
125: * be supported by the database and is strictly vendor-dependent.
126: * @param parsedSql the parsed represenation of the SQL statement
127: * @param paramSource the source for named parameters
128: * @return the SQL statement with substituted parameters
129: * @see #parseSqlStatement
130: */
131: public static String substituteNamedParameters(ParsedSql parsedSql,
132: SqlParameterSource paramSource) {
133: String originalSql = parsedSql.getOriginalSql();
134: StringBuffer actualSql = new StringBuffer();
135: List paramNames = parsedSql.getParameterNames();
136: int lastIndex = 0;
137: for (int i = 0; i < paramNames.size(); i++) {
138: String paramName = (String) paramNames.get(i);
139: int[] indexes = parsedSql.getParameterIndexes(i);
140: int startIndex = indexes[0];
141: int endIndex = indexes[1];
142: actualSql.append(originalSql.substring(lastIndex,
143: startIndex));
144: if (paramSource != null && paramSource.hasValue(paramName)) {
145: Object value = paramSource.getValue(paramName);
146: if (value instanceof Collection) {
147: Collection entries = (Collection) value;
148: for (int k = 0; k < entries.size(); k++) {
149: if (k > 0) {
150: actualSql.append(", ");
151: }
152: actualSql.append("?");
153: }
154: } else {
155: actualSql.append("?");
156: }
157: } else {
158: actualSql.append("?");
159: }
160: lastIndex = endIndex;
161: }
162: actualSql.append(originalSql.substring(lastIndex, originalSql
163: .length()));
164: return actualSql.toString();
165: }
166:
167: /**
168: * Convert a Map of named parameter values to a corresponding array.
169: * @param parsedSql the parsed SQL statement
170: * @param paramSource the source for named parameters
171: * @param declaredParams the List of declared SqlParameter objects
172: * (may be <code>null</code>). If specified, the parameter metadata will
173: * be built into the value array in the form of SqlParameterValue objects.
174: * @return the array of values
175: */
176: public static Object[] buildValueArray(ParsedSql parsedSql,
177: SqlParameterSource paramSource, List declaredParams) {
178: Object[] paramArray = new Object[parsedSql
179: .getTotalParameterCount()];
180: if (parsedSql.getNamedParameterCount() > 0
181: && parsedSql.getUnnamedParameterCount() > 0) {
182: throw new InvalidDataAccessApiUsageException(
183: "You can't mix named and traditional ? placeholders. You have "
184: + parsedSql.getNamedParameterCount()
185: + " named parameter(s) and "
186: + parsedSql.getUnnamedParameterCount()
187: + " traditonal placeholder(s) in ["
188: + parsedSql.getOriginalSql() + "]");
189: }
190: List paramNames = parsedSql.getParameterNames();
191: for (int i = 0; i < paramNames.size(); i++) {
192: String paramName = (String) paramNames.get(i);
193: try {
194: Object value = paramSource.getValue(paramName);
195: SqlParameter param = findParameter(declaredParams,
196: paramName, i);
197: paramArray[i] = (param != null ? new SqlParameterValue(
198: param, value) : value);
199: } catch (IllegalArgumentException ex) {
200: throw new InvalidDataAccessApiUsageException(
201: "No value supplied for the SQL parameter '"
202: + paramName + "': " + ex.getMessage());
203: }
204: }
205: return paramArray;
206: }
207:
208: /**
209: * Find a matching parameter in the given list of declared parameters.
210: * @param declaredParams the declared SqlParameter objects
211: * @param paramName the name of the desired parameter
212: * @param paramIndex the index of the desired parameter
213: * @return the declared SqlParameter, or <code>null</code> if none found
214: */
215: private static SqlParameter findParameter(List declaredParams,
216: String paramName, int paramIndex) {
217: if (declaredParams != null) {
218: // First pass: Look for named parameter match.
219: for (Iterator it = declaredParams.iterator(); it.hasNext();) {
220: SqlParameter declaredParam = (SqlParameter) it.next();
221: if (paramName.equals(declaredParam.getName())) {
222: return declaredParam;
223: }
224: }
225: // Second pass: Look for parameter index match.
226: if (paramIndex < declaredParams.size()) {
227: SqlParameter declaredParam = (SqlParameter) declaredParams
228: .get(paramIndex);
229: // Only accept unnamed parameters for index matches.
230: if (declaredParam.getName() == null) {
231: return declaredParam;
232: }
233: }
234: }
235: return null;
236: }
237:
238: /**
239: * Determine whether a parameter name ends at the current position,
240: * that is, whether the given character qualifies as a separator.
241: */
242: private static boolean isParameterSeparator(char c) {
243: if (Character.isWhitespace(c)) {
244: return true;
245: }
246: for (int i = 0; i < PARAMETER_SEPARATORS.length; i++) {
247: if (c == PARAMETER_SEPARATORS[i]) {
248: return true;
249: }
250: }
251: return false;
252: }
253:
254: /**
255: * Convert a Map of parameter types to a corresponding int array.
256: * This is necessary in order to reuse existing methods on JdbcTemplate.
257: * Any named parameter types are placed in the correct position in the
258: * Object array based on the parsed SQL statement info.
259: * @param parsedSql the parsed SQL statement
260: * @param paramSource the source for named parameters
261: */
262: static int[] buildSqlTypeArray(ParsedSql parsedSql,
263: SqlParameterSource paramSource) {
264: int[] sqlTypes = new int[parsedSql.getTotalParameterCount()];
265: List paramNames = parsedSql.getParameterNames();
266: for (int i = 0; i < paramNames.size(); i++) {
267: String paramName = (String) paramNames.get(i);
268: sqlTypes[i] = paramSource.getSqlType(paramName);
269: }
270: return sqlTypes;
271: }
272:
273: //-------------------------------------------------------------------------
274: // Convenience methods operating on a plain SQL String
275: //-------------------------------------------------------------------------
276:
277: /**
278: * Parse the SQL statement and locate any placeholders or named parameters.
279: * Named parameters are substituted for a JDBC placeholder.
280: * <p>This is a shortcut version of
281: * {@link #parseSqlStatement(String)} in combination with
282: * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
283: * @param sql the SQL statement
284: * @return the actual (parsed) SQL statement
285: */
286: public static String parseSqlStatementIntoString(String sql) {
287: ParsedSql parsedSql = parseSqlStatement(sql);
288: return substituteNamedParameters(parsedSql, null);
289: }
290:
291: /**
292: * Parse the SQL statement and locate any placeholders or named parameters.
293: * Named parameters are substituted for a JDBC placeholder and any select list
294: * is expanded to the required number of placeholders.
295: * <p>This is a shortcut version of
296: * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
297: * @param sql the SQL statement
298: * @param paramSource the source for named parameters
299: * @return the SQL statement with substituted parameters
300: */
301: public static String substituteNamedParameters(String sql,
302: SqlParameterSource paramSource) {
303: ParsedSql parsedSql = parseSqlStatement(sql);
304: return substituteNamedParameters(parsedSql, paramSource);
305: }
306:
307: /**
308: * Convert a Map of named parameter values to a corresponding array.
309: * <p>This is a shortcut version of
310: * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}.
311: * @param sql the SQL statement
312: * @param paramMap the Map of parameters
313: * @return the array of values
314: */
315: public static Object[] buildValueArray(String sql, Map paramMap) {
316: ParsedSql parsedSql = parseSqlStatement(sql);
317: return buildValueArray(parsedSql, new MapSqlParameterSource(
318: paramMap), null);
319: }
320:
321: }
|