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. Select lists may contain
122: * an array of objects and in that case the placeholders will be grouped and
123: * enclosed with parantheses. This allows for the use of "expression lists" in
124: * the SQL statement like:<br/>
125: * select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))
126: * <p>The parameter values passed in are used to determine the number of
127: * placeholder to be used for a select list. Select lists should be limited
128: * to 100 or fewer elements. A larger number of elements is not guaramteed to
129: * be supported by the database and is strictly vendor-dependent.
130: * @param parsedSql the parsed represenation of the SQL statement
131: * @param paramSource the source for named parameters
132: * @return the SQL statement with substituted parameters
133: * @see #parseSqlStatement
134: */
135: public static String substituteNamedParameters(ParsedSql parsedSql,
136: SqlParameterSource paramSource) {
137: String originalSql = parsedSql.getOriginalSql();
138: StringBuffer actualSql = new StringBuffer();
139: List paramNames = parsedSql.getParameterNames();
140: int lastIndex = 0;
141: for (int i = 0; i < paramNames.size(); i++) {
142: String paramName = (String) paramNames.get(i);
143: int[] indexes = parsedSql.getParameterIndexes(i);
144: int startIndex = indexes[0];
145: int endIndex = indexes[1];
146: actualSql.append(originalSql.substring(lastIndex,
147: startIndex));
148: if (paramSource != null && paramSource.hasValue(paramName)) {
149: Object value = paramSource.getValue(paramName);
150: if (value instanceof Collection) {
151: Iterator entryIter = ((Collection) value)
152: .iterator();
153: int k = 0;
154: while (entryIter.hasNext()) {
155: if (k > 0) {
156: actualSql.append(", ");
157: }
158: k++;
159: Object entryItem = entryIter.next();
160: if (entryItem instanceof Object[]) {
161: Object[] expressionList = (Object[]) entryItem;
162: actualSql.append("(");
163: for (int m = 0; m < expressionList.length; m++) {
164: if (m > 0) {
165: actualSql.append(", ");
166: }
167: actualSql.append("?");
168: }
169: actualSql.append(")");
170: } else {
171: actualSql.append("?");
172: }
173: }
174: } else {
175: actualSql.append("?");
176: }
177: } else {
178: actualSql.append("?");
179: }
180: lastIndex = endIndex;
181: }
182: actualSql.append(originalSql.substring(lastIndex, originalSql
183: .length()));
184: return actualSql.toString();
185: }
186:
187: /**
188: * Convert a Map of named parameter values to a corresponding array.
189: * @param parsedSql the parsed SQL statement
190: * @param paramSource the source for named parameters
191: * @param declaredParams the List of declared SqlParameter objects
192: * (may be <code>null</code>). If specified, the parameter metadata will
193: * be built into the value array in the form of SqlParameterValue objects.
194: * @return the array of values
195: */
196: public static Object[] buildValueArray(ParsedSql parsedSql,
197: SqlParameterSource paramSource, List declaredParams) {
198: Object[] paramArray = new Object[parsedSql
199: .getTotalParameterCount()];
200: if (parsedSql.getNamedParameterCount() > 0
201: && parsedSql.getUnnamedParameterCount() > 0) {
202: throw new InvalidDataAccessApiUsageException(
203: "You can't mix named and traditional ? placeholders. You have "
204: + parsedSql.getNamedParameterCount()
205: + " named parameter(s) and "
206: + parsedSql.getUnnamedParameterCount()
207: + " traditonal placeholder(s) in ["
208: + parsedSql.getOriginalSql() + "]");
209: }
210: List paramNames = parsedSql.getParameterNames();
211: for (int i = 0; i < paramNames.size(); i++) {
212: String paramName = (String) paramNames.get(i);
213: try {
214: Object value = paramSource.getValue(paramName);
215: SqlParameter param = findParameter(declaredParams,
216: paramName, i);
217: paramArray[i] = (param != null ? new SqlParameterValue(
218: param, value) : value);
219: } catch (IllegalArgumentException ex) {
220: throw new InvalidDataAccessApiUsageException(
221: "No value supplied for the SQL parameter '"
222: + paramName + "': " + ex.getMessage());
223: }
224: }
225: return paramArray;
226: }
227:
228: /**
229: * Find a matching parameter in the given list of declared parameters.
230: * @param declaredParams the declared SqlParameter objects
231: * @param paramName the name of the desired parameter
232: * @param paramIndex the index of the desired parameter
233: * @return the declared SqlParameter, or <code>null</code> if none found
234: */
235: private static SqlParameter findParameter(List declaredParams,
236: String paramName, int paramIndex) {
237: if (declaredParams != null) {
238: // First pass: Look for named parameter match.
239: for (Iterator it = declaredParams.iterator(); it.hasNext();) {
240: SqlParameter declaredParam = (SqlParameter) it.next();
241: if (paramName.equals(declaredParam.getName())) {
242: return declaredParam;
243: }
244: }
245: // Second pass: Look for parameter index match.
246: if (paramIndex < declaredParams.size()) {
247: SqlParameter declaredParam = (SqlParameter) declaredParams
248: .get(paramIndex);
249: // Only accept unnamed parameters for index matches.
250: if (declaredParam.getName() == null) {
251: return declaredParam;
252: }
253: }
254: }
255: return null;
256: }
257:
258: /**
259: * Determine whether a parameter name ends at the current position,
260: * that is, whether the given character qualifies as a separator.
261: */
262: private static boolean isParameterSeparator(char c) {
263: if (Character.isWhitespace(c)) {
264: return true;
265: }
266: for (int i = 0; i < PARAMETER_SEPARATORS.length; i++) {
267: if (c == PARAMETER_SEPARATORS[i]) {
268: return true;
269: }
270: }
271: return false;
272: }
273:
274: /**
275: * Convert a Map of parameter types to a corresponding int array.
276: * This is necessary in order to reuse existing methods on JdbcTemplate.
277: * Any named parameter types are placed in the correct position in the
278: * Object array based on the parsed SQL statement info.
279: * @param parsedSql the parsed SQL statement
280: * @param paramSource the source for named parameters
281: */
282: public static int[] buildSqlTypeArray(ParsedSql parsedSql,
283: SqlParameterSource paramSource) {
284: int[] sqlTypes = new int[parsedSql.getTotalParameterCount()];
285: List paramNames = parsedSql.getParameterNames();
286: for (int i = 0; i < paramNames.size(); i++) {
287: String paramName = (String) paramNames.get(i);
288: sqlTypes[i] = paramSource.getSqlType(paramName);
289: }
290: return sqlTypes;
291: }
292:
293: //-------------------------------------------------------------------------
294: // Convenience methods operating on a plain SQL String
295: //-------------------------------------------------------------------------
296:
297: /**
298: * Parse the SQL statement and locate any placeholders or named parameters.
299: * Named parameters are substituted for a JDBC placeholder.
300: * <p>This is a shortcut version of
301: * {@link #parseSqlStatement(String)} in combination with
302: * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
303: * @param sql the SQL statement
304: * @return the actual (parsed) SQL statement
305: */
306: public static String parseSqlStatementIntoString(String sql) {
307: ParsedSql parsedSql = parseSqlStatement(sql);
308: return substituteNamedParameters(parsedSql, null);
309: }
310:
311: /**
312: * Parse the SQL statement and locate any placeholders or named parameters.
313: * Named parameters are substituted for a JDBC placeholder and any select list
314: * is expanded to the required number of placeholders.
315: * <p>This is a shortcut version of
316: * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}.
317: * @param sql the SQL statement
318: * @param paramSource the source for named parameters
319: * @return the SQL statement with substituted parameters
320: */
321: public static String substituteNamedParameters(String sql,
322: SqlParameterSource paramSource) {
323: ParsedSql parsedSql = parseSqlStatement(sql);
324: return substituteNamedParameters(parsedSql, paramSource);
325: }
326:
327: /**
328: * Convert a Map of named parameter values to a corresponding array.
329: * <p>This is a shortcut version of
330: * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}.
331: * @param sql the SQL statement
332: * @param paramMap the Map of parameters
333: * @return the array of values
334: */
335: public static Object[] buildValueArray(String sql, Map paramMap) {
336: ParsedSql parsedSql = parseSqlStatement(sql);
337: return buildValueArray(parsedSql, new MapSqlParameterSource(
338: paramMap), null);
339: }
340:
341: }
|