Regular expression to replace a value in query parameter in java

Question

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.

take a look at preparedStatement. docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html — Bowie
– Bowie, Commented Dec 24, 2013 at 7:42

Sachin · Accepted Answer · 2013-12-24 07:46:31Z

1

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

answered Dec 24, 2013 at 7:46

Sachin

3,5944 gold badges25 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sachin Over a year ago

As OP said he already built the query. He just want to know how to execute this query. So I have provided it :)

Sureshkumar Panneerselvan Over a year ago

I think he need to build the query with values. So if we see the build code, we can suggest how to set the values.

Sachin Over a year ago

The OP says," I am writing one application to search for specific fields in the form. I am using an API to generate the query based on the selected fields. The api returns the generated query in following format." So I don't think so he want to create query. What he wants to replace ? with proper value

Sachin · Accepted Answer · 2013-12-24 07:54:25Z

1

You can use PreparedStatement to execute such queries that contains ?

PreparedStatement ps = null;
ResultSet rs = null;

String sQuery = "SELECT T0."ID" AS "COL0" FROM STUDENT_TABLE T0 WHERE ( ( ( T0."STUDENT_NAME" = ? ) AND ( T0."GRADE" = ? ) ) AND ( T0."STUDENT_NO" LIKE ? ) )";

ps = objConnection.prepareStatement(sQuery);
ps.setString(1, valueOf_T0."STUDENT_NAME");
ps.setString(2, valueOf_T0."GRADE");
ps.setString(3, valueOf_T0."STUDENT_NO");
rs = ps.executeQuery();

As you have data values and you know the type of database fields, so that you can set values in setter methods according to your data type like setInt(), setDouble(),etc.

Read more about : http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

Thanks

edited Dec 24, 2013 at 7:54

Sachin

3,5944 gold badges25 silver badges45 bronze badges

answered Dec 24, 2013 at 7:51

Neelam Sharma

2,8574 gold badges37 silver badges76 bronze badges

Collectives™ on Stack Overflow

Regular expression to replace a value in query parameter in java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related