001/* 002 * Copyright 2002-2018 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 * https://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 017package org.springframework.jdbc.core.namedparam; 018 019import java.util.ArrayList; 020import java.util.Collection; 021import java.util.HashSet; 022import java.util.Iterator; 023import java.util.List; 024import java.util.Map; 025import java.util.Set; 026 027import org.springframework.dao.InvalidDataAccessApiUsageException; 028import org.springframework.jdbc.core.SqlParameter; 029import org.springframework.jdbc.core.SqlParameterValue; 030import org.springframework.util.Assert; 031 032/** 033 * Helper methods for named parameter parsing. 034 * 035 * <p>Only intended for internal use within Spring's JDBC framework. 036 * 037 * @author Thomas Risberg 038 * @author Juergen Hoeller 039 * @since 2.0 040 */ 041public abstract class NamedParameterUtils { 042 043 /** 044 * Set of characters that qualify as comment or quotes starting characters. 045 */ 046 private static final String[] START_SKIP = new String[] {"'", "\"", "--", "/*"}; 047 048 /** 049 * Set of characters that at are the corresponding comment or quotes ending characters. 050 */ 051 private static final String[] STOP_SKIP = new String[] {"'", "\"", "\n", "*/"}; 052 053 /** 054 * Set of characters that qualify as parameter separators, 055 * indicating that a parameter name in a SQL String has ended. 056 */ 057 private static final char[] PARAMETER_SEPARATORS = 058 new char[] {'"', '\'', ':', '&', ',', ';', '(', ')', '|', '=', '+', '-', '*', '%', '/', '\\', '<', '>', '^'}; 059 060 061 //------------------------------------------------------------------------- 062 // Core methods used by NamedParameterJdbcTemplate and SqlQuery/SqlUpdate 063 //------------------------------------------------------------------------- 064 065 /** 066 * Parse the SQL statement and locate any placeholders or named parameters. 067 * Named parameters are substituted for a JDBC placeholder. 068 * @param sql the SQL statement 069 * @return the parsed statement, represented as ParsedSql instance 070 */ 071 public static ParsedSql parseSqlStatement(final String sql) { 072 Assert.notNull(sql, "SQL must not be null"); 073 074 Set<String> namedParameters = new HashSet<String>(); 075 String sqlToUse = sql; 076 List<ParameterHolder> parameterList = new ArrayList<ParameterHolder>(); 077 078 char[] statement = sql.toCharArray(); 079 int namedParameterCount = 0; 080 int unnamedParameterCount = 0; 081 int totalParameterCount = 0; 082 083 int escapes = 0; 084 int i = 0; 085 while (i < statement.length) { 086 int skipToPosition = i; 087 while (i < statement.length) { 088 skipToPosition = skipCommentsAndQuotes(statement, i); 089 if (i == skipToPosition) { 090 break; 091 } 092 else { 093 i = skipToPosition; 094 } 095 } 096 if (i >= statement.length) { 097 break; 098 } 099 char c = statement[i]; 100 if (c == ':' || c == '&') { 101 int j = i + 1; 102 if (c == ':' && j < statement.length && statement[j] == ':') { 103 // Postgres-style "::" casting operator should be skipped 104 i = i + 2; 105 continue; 106 } 107 String parameter = null; 108 if (c == ':' && j < statement.length && statement[j] == '{') { 109 // :{x} style parameter 110 while (statement[j] != '}') { 111 j++; 112 if (j >= statement.length) { 113 throw new InvalidDataAccessApiUsageException("Non-terminated named parameter declaration " + 114 "at position " + i + " in statement: " + sql); 115 } 116 if (statement[j] == ':' || statement[j] == '{') { 117 throw new InvalidDataAccessApiUsageException("Parameter name contains invalid character '" + 118 statement[j] + "' at position " + i + " in statement: " + sql); 119 } 120 } 121 if (j - i > 2) { 122 parameter = sql.substring(i + 2, j); 123 namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); 124 totalParameterCount = addNamedParameter( 125 parameterList, totalParameterCount, escapes, i, j + 1, parameter); 126 } 127 j++; 128 } 129 else { 130 while (j < statement.length && !isParameterSeparator(statement[j])) { 131 j++; 132 } 133 if (j - i > 1) { 134 parameter = sql.substring(i + 1, j); 135 namedParameterCount = addNewNamedParameter(namedParameters, namedParameterCount, parameter); 136 totalParameterCount = addNamedParameter( 137 parameterList, totalParameterCount, escapes, i, j, parameter); 138 } 139 } 140 i = j - 1; 141 } 142 else { 143 if (c == '\\') { 144 int j = i + 1; 145 if (j < statement.length && statement[j] == ':') { 146 // escaped ":" should be skipped 147 sqlToUse = sqlToUse.substring(0, i - escapes) + sqlToUse.substring(i - escapes + 1); 148 escapes++; 149 i = i + 2; 150 continue; 151 } 152 } 153 if (c == '?') { 154 int j = i + 1; 155 if (j < statement.length && (statement[j] == '?' || statement[j] == '|' || statement[j] == '&')) { 156 // Postgres-style "??", "?|", "?&" operator should be skipped 157 i = i + 2; 158 continue; 159 } 160 unnamedParameterCount++; 161 totalParameterCount++; 162 } 163 } 164 i++; 165 } 166 ParsedSql parsedSql = new ParsedSql(sqlToUse); 167 for (ParameterHolder ph : parameterList) { 168 parsedSql.addNamedParameter(ph.getParameterName(), ph.getStartIndex(), ph.getEndIndex()); 169 } 170 parsedSql.setNamedParameterCount(namedParameterCount); 171 parsedSql.setUnnamedParameterCount(unnamedParameterCount); 172 parsedSql.setTotalParameterCount(totalParameterCount); 173 return parsedSql; 174 } 175 176 private static int addNamedParameter( 177 List<ParameterHolder> parameterList, int totalParameterCount, int escapes, int i, int j, String parameter) { 178 179 parameterList.add(new ParameterHolder(parameter, i - escapes, j - escapes)); 180 totalParameterCount++; 181 return totalParameterCount; 182 } 183 184 private static int addNewNamedParameter(Set<String> namedParameters, int namedParameterCount, String parameter) { 185 if (!namedParameters.contains(parameter)) { 186 namedParameters.add(parameter); 187 namedParameterCount++; 188 } 189 return namedParameterCount; 190 } 191 192 /** 193 * Skip over comments and quoted names present in an SQL statement. 194 * @param statement character array containing SQL statement 195 * @param position current position of statement 196 * @return next position to process after any comments or quotes are skipped 197 */ 198 private static int skipCommentsAndQuotes(char[] statement, int position) { 199 for (int i = 0; i < START_SKIP.length; i++) { 200 if (statement[position] == START_SKIP[i].charAt(0)) { 201 boolean match = true; 202 for (int j = 1; j < START_SKIP[i].length(); j++) { 203 if (statement[position + j] != START_SKIP[i].charAt(j)) { 204 match = false; 205 break; 206 } 207 } 208 if (match) { 209 int offset = START_SKIP[i].length(); 210 for (int m = position + offset; m < statement.length; m++) { 211 if (statement[m] == STOP_SKIP[i].charAt(0)) { 212 boolean endMatch = true; 213 int endPos = m; 214 for (int n = 1; n < STOP_SKIP[i].length(); n++) { 215 if (m + n >= statement.length) { 216 // last comment not closed properly 217 return statement.length; 218 } 219 if (statement[m + n] != STOP_SKIP[i].charAt(n)) { 220 endMatch = false; 221 break; 222 } 223 endPos = m + n; 224 } 225 if (endMatch) { 226 // found character sequence ending comment or quote 227 return endPos + 1; 228 } 229 } 230 } 231 // character sequence ending comment or quote not found 232 return statement.length; 233 } 234 } 235 } 236 return position; 237 } 238 239 /** 240 * Parse the SQL statement and locate any placeholders or named parameters. Named 241 * parameters are substituted for a JDBC placeholder, and any select list is expanded 242 * to the required number of placeholders. Select lists may contain an array of 243 * objects, and in that case the placeholders will be grouped and enclosed with 244 * parentheses. This allows for the use of "expression lists" in the SQL statement 245 * like: <br /><br /> 246 * {@code select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))} 247 * <p>The parameter values passed in are used to determine the number of placeholders to 248 * be used for a select list. Select lists should be limited to 100 or fewer elements. 249 * A larger number of elements is not guaranteed to be supported by the database and 250 * is strictly vendor-dependent. 251 * @param parsedSql the parsed representation of the SQL statement 252 * @param paramSource the source for named parameters 253 * @return the SQL statement with substituted parameters 254 * @see #parseSqlStatement 255 */ 256 public static String substituteNamedParameters(ParsedSql parsedSql, SqlParameterSource paramSource) { 257 String originalSql = parsedSql.getOriginalSql(); 258 List<String> paramNames = parsedSql.getParameterNames(); 259 if (paramNames.isEmpty()) { 260 return originalSql; 261 } 262 StringBuilder actualSql = new StringBuilder(originalSql.length()); 263 int lastIndex = 0; 264 for (int i = 0; i < paramNames.size(); i++) { 265 String paramName = paramNames.get(i); 266 int[] indexes = parsedSql.getParameterIndexes(i); 267 int startIndex = indexes[0]; 268 int endIndex = indexes[1]; 269 actualSql.append(originalSql, lastIndex, startIndex); 270 if (paramSource != null && paramSource.hasValue(paramName)) { 271 Object value = paramSource.getValue(paramName); 272 if (value instanceof SqlParameterValue) { 273 value = ((SqlParameterValue) value).getValue(); 274 } 275 if (value instanceof Collection) { 276 Iterator<?> entryIter = ((Collection<?>) value).iterator(); 277 int k = 0; 278 while (entryIter.hasNext()) { 279 if (k > 0) { 280 actualSql.append(", "); 281 } 282 k++; 283 Object entryItem = entryIter.next(); 284 if (entryItem instanceof Object[]) { 285 Object[] expressionList = (Object[]) entryItem; 286 actualSql.append('('); 287 for (int m = 0; m < expressionList.length; m++) { 288 if (m > 0) { 289 actualSql.append(", "); 290 } 291 actualSql.append('?'); 292 } 293 actualSql.append(')'); 294 } 295 else { 296 actualSql.append('?'); 297 } 298 } 299 } 300 else { 301 actualSql.append('?'); 302 } 303 } 304 else { 305 actualSql.append('?'); 306 } 307 lastIndex = endIndex; 308 } 309 actualSql.append(originalSql, lastIndex, originalSql.length()); 310 return actualSql.toString(); 311 } 312 313 /** 314 * Convert a Map of named parameter values to a corresponding array. 315 * @param parsedSql the parsed SQL statement 316 * @param paramSource the source for named parameters 317 * @param declaredParams the List of declared SqlParameter objects 318 * (may be {@code null}). If specified, the parameter metadata will 319 * be built into the value array in the form of SqlParameterValue objects. 320 * @return the array of values 321 */ 322 public static Object[] buildValueArray( 323 ParsedSql parsedSql, SqlParameterSource paramSource, List<SqlParameter> declaredParams) { 324 325 Object[] paramArray = new Object[parsedSql.getTotalParameterCount()]; 326 if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) { 327 throw new InvalidDataAccessApiUsageException( 328 "Not allowed to mix named and traditional ? placeholders. You have " + 329 parsedSql.getNamedParameterCount() + " named parameter(s) and " + 330 parsedSql.getUnnamedParameterCount() + " traditional placeholder(s) in statement: " + 331 parsedSql.getOriginalSql()); 332 } 333 List<String> paramNames = parsedSql.getParameterNames(); 334 for (int i = 0; i < paramNames.size(); i++) { 335 String paramName = paramNames.get(i); 336 try { 337 Object value = paramSource.getValue(paramName); 338 SqlParameter param = findParameter(declaredParams, paramName, i); 339 paramArray[i] = (param != null ? new SqlParameterValue(param, value) : value); 340 } 341 catch (IllegalArgumentException ex) { 342 throw new InvalidDataAccessApiUsageException( 343 "No value supplied for the SQL parameter '" + paramName + "': " + ex.getMessage()); 344 } 345 } 346 return paramArray; 347 } 348 349 /** 350 * Find a matching parameter in the given list of declared parameters. 351 * @param declaredParams the declared SqlParameter objects 352 * @param paramName the name of the desired parameter 353 * @param paramIndex the index of the desired parameter 354 * @return the declared SqlParameter, or {@code null} if none found 355 */ 356 private static SqlParameter findParameter(List<SqlParameter> declaredParams, String paramName, int paramIndex) { 357 if (declaredParams != null) { 358 // First pass: Look for named parameter match. 359 for (SqlParameter declaredParam : declaredParams) { 360 if (paramName.equals(declaredParam.getName())) { 361 return declaredParam; 362 } 363 } 364 // Second pass: Look for parameter index match. 365 if (paramIndex < declaredParams.size()) { 366 SqlParameter declaredParam = declaredParams.get(paramIndex); 367 // Only accept unnamed parameters for index matches. 368 if (declaredParam.getName() == null) { 369 return declaredParam; 370 } 371 } 372 } 373 return null; 374 } 375 376 /** 377 * Determine whether a parameter name ends at the current position, 378 * that is, whether the given character qualifies as a separator. 379 */ 380 private static boolean isParameterSeparator(char c) { 381 if (Character.isWhitespace(c)) { 382 return true; 383 } 384 for (char separator : PARAMETER_SEPARATORS) { 385 if (c == separator) { 386 return true; 387 } 388 } 389 return false; 390 } 391 392 /** 393 * Convert parameter types from an SqlParameterSource into a corresponding int array. 394 * This is necessary in order to reuse existing methods on JdbcTemplate. 395 * Any named parameter types are placed in the correct position in the 396 * Object array based on the parsed SQL statement info. 397 * @param parsedSql the parsed SQL statement 398 * @param paramSource the source for named parameters 399 */ 400 public static int[] buildSqlTypeArray(ParsedSql parsedSql, SqlParameterSource paramSource) { 401 int[] sqlTypes = new int[parsedSql.getTotalParameterCount()]; 402 List<String> paramNames = parsedSql.getParameterNames(); 403 for (int i = 0; i < paramNames.size(); i++) { 404 String paramName = paramNames.get(i); 405 sqlTypes[i] = paramSource.getSqlType(paramName); 406 } 407 return sqlTypes; 408 } 409 410 /** 411 * Convert parameter declarations from an SqlParameterSource to a corresponding List of SqlParameters. 412 * This is necessary in order to reuse existing methods on JdbcTemplate. 413 * The SqlParameter for a named parameter is placed in the correct position in the 414 * resulting list based on the parsed SQL statement info. 415 * @param parsedSql the parsed SQL statement 416 * @param paramSource the source for named parameters 417 */ 418 public static List<SqlParameter> buildSqlParameterList(ParsedSql parsedSql, SqlParameterSource paramSource) { 419 List<String> paramNames = parsedSql.getParameterNames(); 420 List<SqlParameter> params = new ArrayList<SqlParameter>(paramNames.size()); 421 for (String paramName : paramNames) { 422 params.add(new SqlParameter( 423 paramName, paramSource.getSqlType(paramName), paramSource.getTypeName(paramName))); 424 } 425 return params; 426 } 427 428 429 //------------------------------------------------------------------------- 430 // Convenience methods operating on a plain SQL String 431 //------------------------------------------------------------------------- 432 433 /** 434 * Parse the SQL statement and locate any placeholders or named parameters. 435 * Named parameters are substituted for a JDBC placeholder. 436 * <p>This is a shortcut version of 437 * {@link #parseSqlStatement(String)} in combination with 438 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. 439 * @param sql the SQL statement 440 * @return the actual (parsed) SQL statement 441 */ 442 public static String parseSqlStatementIntoString(String sql) { 443 ParsedSql parsedSql = parseSqlStatement(sql); 444 return substituteNamedParameters(parsedSql, null); 445 } 446 447 /** 448 * Parse the SQL statement and locate any placeholders or named parameters. 449 * Named parameters are substituted for a JDBC placeholder and any select list 450 * is expanded to the required number of placeholders. 451 * <p>This is a shortcut version of 452 * {@link #substituteNamedParameters(ParsedSql, SqlParameterSource)}. 453 * @param sql the SQL statement 454 * @param paramSource the source for named parameters 455 * @return the SQL statement with substituted parameters 456 */ 457 public static String substituteNamedParameters(String sql, SqlParameterSource paramSource) { 458 ParsedSql parsedSql = parseSqlStatement(sql); 459 return substituteNamedParameters(parsedSql, paramSource); 460 } 461 462 /** 463 * Convert a Map of named parameter values to a corresponding array. 464 * <p>This is a shortcut version of 465 * {@link #buildValueArray(ParsedSql, SqlParameterSource, java.util.List)}. 466 * @param sql the SQL statement 467 * @param paramMap the Map of parameters 468 * @return the array of values 469 */ 470 public static Object[] buildValueArray(String sql, Map<String, ?> paramMap) { 471 ParsedSql parsedSql = parseSqlStatement(sql); 472 return buildValueArray(parsedSql, new MapSqlParameterSource(paramMap), null); 473 } 474 475 476 private static class ParameterHolder { 477 478 private final String parameterName; 479 480 private final int startIndex; 481 482 private final int endIndex; 483 484 public ParameterHolder(String parameterName, int startIndex, int endIndex) { 485 this.parameterName = parameterName; 486 this.startIndex = startIndex; 487 this.endIndex = endIndex; 488 } 489 490 public String getParameterName() { 491 return this.parameterName; 492 } 493 494 public int getStartIndex() { 495 return this.startIndex; 496 } 497 498 public int getEndIndex() { 499 return this.endIndex; 500 } 501 } 502 503}