Dynamic SQL in Java


I am using prepared statement on one of my java calls to the database.

Here's is a sample of the same

 String sql = "Select <table_nam>.<column_name> from  <schema_name>.<table_name>
 where <condition>"
         and <column_name_1> = ? "                        +
        " and <column_name_2> = ? "                     +
        " and <column_name_3> = ? ";
I set the parameters for aboe column as below

stmt.setString( 1, column1 );
stmt.setString( 2, column2 );
stmt.setString( 3, column3 );

column1, column2 and column3 values are passed to the method that has the above code block.


How to make this SQL dynamic ?. If column1, column2 and column3 has particular values (say "X") I DO NOT want to add
in the SQL. For that I have an if statment and populate sql without problem. BUT i have to incorporate many permutations
for setting the statement block, so that it matches with the SQL string. How to do both things i.e generating SQL and setting values (when the values are conditional) in statements.

What is the best possible solution to have the code modular and more robust (avoiding lot of if's)

Who is Participating?
doronbConnect With a Mentor Commented:
1) All of the parameters you've put between '<' and '>' are values used to produce a key to chache the statement.
2) Upon a request to execute the query, you compose the key and check if the statement is already cached.
3) If the statement isn't cached, you create the statement by bulding the SQL String, then create a PreparedStatement from the SQL String.
4) Once the query is cached, you fetch it from the cache and bind the values into their respective places.
5) Execute the PreparedStatement after binding the values and get the ResultSet.

Important Note:  As far as I know, SQL (ANSI-92 compatible) doesn't allow certain values to be bound into PrepareStatements, so parameters like the condition, table-name and column-names would be impossible to bind with stmt.setXXX(...) methods, what I do here is use Java's text formatting to create the SQL String where I can simply pass some parameters as a part of the SQL String (formatted in) and other parameters as bound values for the PreparedStatement.
I usually cache my SQL PreparedStatements according to a key composed from the operation (INSERT/UPDATE/DELETE etc..) and the name/type of parameters.  This way, when I first use the query, I create it and cache it, the 2nd time and everytime after that, I simply fetch the PreparedStatement from my cache, set the parameter values and execute.
Java_ProblemAuthor Commented:
Can u explain on the context of sample sql i gave in my question.
Java_ProblemAuthor Commented:
are these 5 steps really required or there is even better approach.. I will still have "GURU's" share their views and comments (please use some example/template to explain it better)..Thanks
I can hardly imagine any other way to do it given how JDBC and SQL work together.  I've seen solutions that re-created the SQL statement from scratch everytime a query had to be executed, but that's not what I think you want. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.