Java tutorial
package org.hxzon.util.db.springjdbc; /* * Copyright 2002-2013 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.Set; /** * Helper methods for named parameter parsing. * * <p>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 = i; while (i < statement.length) { skipToPosition = skipCommentsAndQuotes(statement, i); if (i == skipToPosition) { break; } else { i = skipToPosition; } } if (i >= statement.length) { break; } 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 RuntimeException("Parameter name contains invalid character '" + statement[j] + "' at position " + i + " in statement: " + sql); } } if (j >= statement.length) { throw new RuntimeException("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 * parentheses. This allows for the use of "expression lists" in the SQL statement * like: <br /><br /> * {@code 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 placeholders to * be used for a select list. Select lists should be limited to 100 or fewer elements. * A larger number of elements is not guaranteed to be supported by the database and * is strictly vendor-dependent. * @param parsedSql the parsed representation 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<String> paramNames = parsedSql.getParameterNames(); int lastIndex = 0; for (int i = 0; i < paramNames.size(); i++) { String paramName = paramNames.get(i); int[] indexes = parsedSql.getParameterIndexes(i); int startIndex = indexes[0]; int endIndex = indexes[1]; actualSql.append(originalSql, lastIndex, startIndex); if (paramSource != null && paramSource.hasValue(paramName)) { Object value = paramSource.getValue(paramName); if (value instanceof SqlParameterValue) { value = ((SqlParameterValue) value).getValue(); } 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, 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}). 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 RuntimeException("Not allowed to mix named and traditional ? placeholders. You have " + parsedSql.getNamedParameterCount() + " named parameter(s) and " + parsedSql.getUnnamedParameterCount() + " traditional placeholder(s) in statement: " + 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 RuntimeException( "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} 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) { params.add(new SqlParameter(paramName, paramSource.getSqlType(paramName), paramSource.getTypeName(paramName))); } 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 final String parameterName; private final int startIndex; private final int endIndex; public ParameterHolder(String parameterName, int startIndex, int endIndex) { this.parameterName = parameterName; this.startIndex = startIndex; this.endIndex = endIndex; } public String getParameterName() { return this.parameterName; } public int getStartIndex() { return this.startIndex; } public int getEndIndex() { return this.endIndex; } } }