I am writing one application to search for specific fields in the form and display the corresponding query to the user (user can save the executed query and load the query later). I am using an API to generate the query based on the selected fields. The API returns the generated query in following format.
Generated query:
SELECT T0."id" AS "COL0"
FROM student_table T0
WHERE ( ( ( T0."student_name" = ? )
AND ( T0."grade" = ? ) )
AND ( T0."student_no" LIKE ? ) )
Expected query:
SELECT T0."id" AS "COL0"
FROM student_table T0
WHERE ( ( ( T0."student_name" = 'John' )
AND ( T0."grade" = 'A' ) )
AND ( T0."student_no" LIKE '12%' ) )
Now I have to build proper query from above query. Since I have the field values in the from, I want to replace the "?" with corresponding values.
Is it possible to replace the '?' with corresponding field values using java string replaceAll method ? can we use the concept of back references to replace the question marks with corresponding values ? If not possible with regex please suggest a better approach to solve the problem.
I think prepared statement wont help much since I need to automate this process for hundreds of fields which are unordered and it is a very costly operation also. (correct me If I am wrong)
I am actually using my company specific platform API, I am adding criteria's and query columns to the query definition and it returns the executed query in the above mentioned format. User should be able to save the Expected query, I should display both the query and result of query in UI.
Try to execute query in following way
String insertTableSQL = "SELECT T0.\"ID\" AS \"COL0\" FROM STUDENT_TABLE T0 WHERE ( ( ( T0.\"STUDENT_NAME\" = ? ) AND ( T0.\"GRADE\" = ? ) ) AND ( T0.\"STUDENT_NO\" LIKE ? ) )";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setString(1, "John");
preparedStatement.setString(2, "A");
preparedStatement.setString(3, "12%");
preparedStatement.execute();