Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL in Java

Posted on 2004-10-12
7
Medium Priority
?
346 Views
Last Modified: 2010-03-31
Folks,

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.

Question:

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)

0
Comment
Question by:Java_Problem
  • 3
  • 2
7 Comments
 
LVL 9

Expert Comment

by:doronb
ID: 12288910
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.
0
 

Author Comment

by:Java_Problem
ID: 12289850
Can u explain on the context of sample sql i gave in my question.
0
 
LVL 9

Accepted Solution

by:
doronb earned 500 total points
ID: 12290354
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.
0
 

Author Comment

by:Java_Problem
ID: 12290842
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
0
 
LVL 9

Expert Comment

by:doronb
ID: 12302705
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. :)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question