CODEWITHSUNDEEP
sql
ESri
ESri

Reputation: 11

Create a SQL select query dynamically at runtime

I have a requirement where I need to write a SQL select query with dynamic columns at runtime. There are few mandatory and optional fields in the DB. I want to generate the query at runtime based on the values present in the incoming request.

Ex: DB has colms A,B,C,D,E
scenario 1: request has only value for A, query should be generated as

select * from table where A='<somevalue>'

scenario 2 . Request has value for A and D , query should be generated as

select * from table where A='<somevalue>' and D='<somevalue>'

Currently it is being handled using java to create string for not null values and appending it to select statement to form the final query.

Ex:

if (A!=null) 
  String query_a='<somevalue>'
else 
  query_a=""

and then appending <query_a> <query_b> to form final query

Is there a better way to achieve this?

Upvotes: 0

Views: 1104

Answers (2)

O. Jones
O. Jones

Reputation: 108706

You may want to consider doing this construction of queries in Java, creating an appropriate Prepared Statement for each case.

List<String> clauses = new ArrayList<String>();
List<String> params = new ArrayList<String>();
clauses.add("1=1");
if (you_want_to_search_on_column_a) {
   clauses.Add("column_a = ?");
   params.Add(value_to_search_on_column_a);
}
if (you_want_to_search_on_column_b) {
   clauses.Add("column_b = ?");
   params.Add(value_to_search_on_column_b);
}
String queryString = "SELECT * FROM table WHERE " + String.join(" AND ", clauses);
PreparedStatement stmt = con.prepareStatement(queryString);
for (int i = 0; i < params.size; i++ ) {
   stmt.setString(i+1, params.get(i));
}
ResultSet rs = stmt.executeQuery();

This way you'll present queries with exactly the filtering criteria you need, and so give MySQL's query planner the best possible shot at optimizing each one.

If you were working with Oracle or SQL server it would be worth your trouble to keep a cache of PreparedStatement objects, but that's not so with MySQL.

Upvotes: 0

MertG
MertG

Reputation: 763

In your SQL script, you can better parametrize like below. I've assume that you are using parameters.

**It's basically sql idea.

SELECT * 
FROM table
WHERE (
   (@p1 IS NULL OR columnA = @p1)
OR (@p2 IS NULL OR columnB = @p2)
OR (@p3 IS NULL OR columnC = @p3)
OR (@p4 IS NULL OR columnD = @p4)
)

Upvotes: 2

Related Questions