/**
* 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();
}