Replace question mark with respective parameter string

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.

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

I assume you are using Java and I am not a Java-expert so hope this answer can help you a bit.

Normal way (Vulnerable to injection attack)

String depId = "10";
String perStatus = "'Active'";

String query = "select this_.id, this_.name from table this_ where 
this_.depId = %s and this_.perStatus = %s";
String finalQuery = String.format(query, depId, perStatus);

System.out.println(finalQuery);

Safeway (PreparedStatement)

String query = "select this_.id, this_.name from table this_ where this_.depId = ? and this_.perStatus = ?";
PreparedStatement prepStatQuery = con.prepareStatement(query);

prepStatQuery.setInt(1, 10);
prepStatQuery.setString(2, "Active");

ResultSet resSet = prepStatQuery.executeQuery();

You have many options here:

1. Use String.format method and align the parameters in the order you want. Here you will replace your ? with a format specifier.
2. With JDBC you have to PreparedStatement.
3. Or with Spring Data check this link.

I would suggest you to use Stored Procedures in you're Case.
They are easy to create and are stored on you're Database directly.
You're Stored Procedure could look like this.

CREATE PROCEDURE yourProcedureName
	@depId int,
	@perStatus varchar(10),
	AS
	BEGIN
		SELECT this_.id, this_.name from table this_ where this_.depId = @depId and this_.perStatus = @perStatus
END

Here is some example how you could call your Stored Procedure in your Java Code.

public void storedProcedure(Int depId, String perStatus, String connectionUrl){
    Connection con = null;
    PreparedStatement pStatement = null;
    ResultSet rs = null;

    try {
        // Establish the connection
        Connection connection = DriverManager.getConnection(connectionUrl);

        // Prepare the stored procedure call
        String storedProcedure = "{CALL procedureName(?,?)}";
        pStatement = connection.prepareCall(storedProcedure);

        pStatement.setInt(1,depId);
        pStatement.setString(2,perStatus);


        pStatement.execute();

        pStatement.close();
        connection.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    finally {
        if(rs != null) try {
            rs.close();
        }catch (Exception e){}
        if(pStatement!=null) try {
            pStatement.close();
        }catch (Exception e){}
        if(con!=null) try{con.close();} catch (Exception e){}
    }

}