Solved

Dynamic SQL in Java

Posted on 2004-10-12
7
330 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

863 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

19 Experts available now in Live!

Get 1:1 Help Now