/** * 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(String sql) { Set<String> namedParameters = new HashSet<String>(); ParsedSql parsedSql = new ParsedSql(sql); char[] statement = sql.toCharArray(); int namedParameterCount = 0; int unnamedParameterCount = 0; int totalParameterCount = 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; } while (j < statement.length && !isParameterSeparator(statement[j])) { j++; } if (j - i > 1) { String parameter = sql.substring(i + 1, j); if (!namedParameters.contains(parameter)) { namedParameters.add(parameter); namedParameterCount++; } parsedSql.addNamedParameter(parameter, i, j); totalParameterCount++; } i = j - 1; } else { if (c == '?') { unnamedParameterCount++; totalParameterCount++; } } i++; } parsedSql.setNamedParameterCount(namedParameterCount); parsedSql.setUnnamedParameterCount(unnamedParameterCount); parsedSql.setTotalParameterCount(totalParameterCount); return parsedSql; }
/** * Convert a Map of named parameter values to a corresponding array. * * @param parsedSql the parsed SQL statement * @param paramSource the source for named parameters * @return the array of values */ public static Object[] buildValueArray(ParsedSql parsedSql, Map<String, Object> paramSource) throws SQLException { Object[] valueArray = new Object[parsedSql.getTotalParameterCount()]; if (parsedSql.getNamedParameterCount() > 0 && parsedSql.getUnnamedParameterCount() > 0) { throw new SQLException("You can't mix named and traditional ? placeholders."); } List<String> paramNames = parsedSql.getParameterNames(); for (int i = 0; i < paramNames.size(); i++) { String paramName = paramNames.get(i); valueArray[i] = paramSource.get(paramName); } return valueArray; }
/** * Parse the SQL statement and locate any placeholders or named parameters. * * @param parsedSql the parsed represenation of the SQL statement * @param paramSource the source for named parameters * @return the SQL statement with substituted parameters */ public static String substituteNamedParameters(ParsedSql parsedSql, Map 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.containsKey(paramName)) { Object value = paramSource.get(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(); }