SqlStringUtility.java

import java.util.Collection;
import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.ConcurrentSkipListMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlStringUtility {

    /**
     * Extract Named Parameters (no duplicates)
     *
     * Example usage:
     *
     *   String sql = "SELECT fb.* FROM foobar fb WHERE foo=:foo AND bar=:bar";
     *   Map<String, Object> parameterValues = new HashMap<String, Object>();
     *   parameterValues.put("foo", "Value of foo");
     *   parameterValues.put("bar", "Value of bar");
     *
     *   Query query = entityManager.createNativeQuery(sql);
     *   Set<String> parameters = SqlStringUtility.extractNamedParameters(sql);
     *   if (!parameters.isEmpty())
     *       for (String parameter : parameters)
     *           query.setParameter(parameter, parameterValues.get(parameter));
     * 
     * @param sql String containing a SQL statement from which to extract named parameters.
     */
    public static Set<String> extractNamedParameters(String sql) {
        Pattern pattern = Pattern.compile(":([\\p{L}\\p{N}]+)");
        Matcher matcher = pattern.matcher(sql);
        Set<String> parameters = new HashSet<String>();
        int i = 0;
        while (matcher.find(i)) {
            parameters.add(matcher.group(1));
            i = matcher.end();
        }
        return parameters;
    }

    /**
     * Build Insert Statement
     *
     * Example usage:
     *
     *   ConcurrentSkipListMap<String, String> fields = new ConcurrentSkipListMap<String, String>();
     *   fields.put("id", ":id");
     *   fields.put("name", ":name");
     *   fields.put("status", "'active'");
     *   String sql = SqlStringUtility.buildInsertStatement("users", fields);
     *
     * The above code would generate the following sql String value:
     *
     *   "INSERT INTO users (id,name,status) VALUES (:id,:name,'active')"
     * 
     * @param table Name of database table to INSERT record into.
     * @param fields Map with column names as keys and parameter names or static values as values.
     */
    public static String buildInsertStatement(String table, ConcurrentSkipListMap<String, String> fields) {
        StringBuilder sql = new StringBuilder("INSERT INTO ");
        sql.append(table);
        sql.append("(");
        appendCommaSeparatedStrings(sql, fields.keySet());
        sql.append(") VALUES(");
        appendCommaSeparatedStrings(sql, fields.values());
        sql.append(")");

        return sql.toString();
    }

    /**
     * Append Comma-Separated Strings
     * 
     * @param sb StringBuilder object to append strings to.
     * @param strings Collection of String objects to append.
     */
    private static void appendCommaSeparatedStrings(StringBuilder sb, Collection<String> strings) {
        for (String string : strings)
            sb.append(string).append(",");
        sb.setLength(sb.length() - 1); // drop last comma
    }

}