Solved

Dynamic SQL in Java

Posted on 2004-10-12
7
329 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
Comment Utility
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
Comment Utility
Can u explain on the context of sample sql i gave in my question.
0
 
LVL 9

Accepted Solution

by:
doronb earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
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…
This video teaches viewers about errors in exception handling.

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now