Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic SQL in Java

Posted on 2004-10-12
7
Medium Priority
?
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

670 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