Replace question mark with respective parameter string

Question

I have a sql query like below and I want to replace the question mark with respective binded parameters passed. How will I construct this String

select this_.id, this_.name from table this_ where this_.depId = ? and this_.perStatus = ?

I want to replace the question mark like below

select this_.id, this_.name from table this_ where this_.depId = 10 and this_.perStatus = 'Active'

How do I replace all the question marks with respective parameters. Any help appreciated.

Answer 1

If we agree that this should never be used to construct a Statement but only for debugging messages, you can do it like

static final Pattern COMPARISON = Pattern.compile("((\\w+)\\s*[<>=]+\\s*)\\?");

static String debugExpression(String actualQuery, Map<String,Object> values) {
    return COMPARISON.matcher(actualQuery)
        .replaceAll(mr -> mr.group(1) + forValue(values.get(mr.group(2))));
}

private static String forValue(Object o) {
    return o instanceof Number || o instanceof Boolean? o.toString():
        o == null? " *** key not in map ***":
        "'" + Matcher.quoteReplacement(o.toString()) + "'";
}

This searches for occurrences of “name  comparison operator  question mark” and replaces the question mark with a representation of the value found in the map for “name”.

If you prefer to enforce the “name” to be prepended by this_. you can use the following pattern instead:

static final Pattern COMPARISON
    = Pattern.compile("(?<=this_\\.)((\\w+)\\s*[<>=]+\\s*)\\?");

If we test it with

String query = "select this_.id, this_.name from table this_ where this_.depId = ? and this_.perStatus = ?";
System.out.println(debugExpression(query, Map.of("depId", 10, "perStatus", "Active")));

It will print

select this_.id, this_.name from table this_ where this_.depId = 10 and this_.perStatus = 'Active'

This will not replace other question marks, like the (?, ? …) of an update or insert statement, as there’s no context to derive the right key from.

---

The code above uses Java 9 features. If you need Java 8 compatibility, the replacement logic needs to be implemented manually:

static String debugExpression(String actualQuery, Map<String,Object> values) {

  Matcher m = COMPARISON.matcher(actualQuery);
  if(!m.find()) return actualQuery;

  StringBuilder sb = new StringBuilder(actualQuery.length() + 100);
  int start = 0;

  do {
    sb.append(actualQuery, start, m.end(1));
    start = m.end();
    Object o = values.get(m.group(2));

    if(o == null) sb.append(" *** key not in map ***");
    else if(o instanceof Number || o instanceof Boolean) sb.append(o);
    else sb.append("'").append(o).append("'");

  } while(m.find());

  return sb.append(actualQuery, start, actualQuery.length()).toString();
}