File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes sql query based on variable number of input parameters Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql query based on variable number of input parameters" Watch "sql query based on variable number of input parameters" New topic
Author

sql query based on variable number of input parameters

abhishek pendkay
Ranch Hand

Joined: Jan 01, 2007
Posts: 184
Hi all
I have a form with 8 fields and based on the values entered in them I have have to fetch the records from the DataBase. Now the problem is out of the 8 fields the user may fill any number of fields and that too in any order for example the user may fill fields 1,4 and 6 or he may fill 1 and 7 or he may fill all of them (of course he has to fill at least one field)... Now how will I write a query which will work for any number and order of input parameters? and also because this query will be used in reporting(iReport) I am not allowed to write any code with it , it has to be a SQL query. Any ideas...
[ August 29, 2008: Message edited by: abhishek pendkay ]

The significant problems we face cannot be solved by the same level of thinking which created them – Einstein
SCJP 1.5, SCWCD, SCBCD in the making
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38349
    
  23
You have really been dropped in it; you have 2^8-1 = 255 possible combinations of vacant fields .

What format are those fields in? Can you use "like '%'" when there is a vacant field? Can you use "is null" or "is not null" when there is a vacant field?
Don't know any more, sorry.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38349
    
  23
Select the fields actually filled in into a temporary table or a view??

Still not sure.

I actually think you might stand a better chance of a helpful answer on the JDBC forum, so I shall try moving you there.
Paul Yule
Ranch Hand

Joined: May 12, 2008
Posts: 229
I do not think I quite understand the requirements...you can't use code to generate the SQL string because that would seriously be a pain to try and cover every single combination of query.

So no :



I've never used that technology =(.You are not able to do this because of limitations on the reports side of things?
[ August 29, 2008: Message edited by: Paul Yule ]
Shawn Montague
Greenhorn

Joined: Aug 06, 2008
Posts: 25
Is this MySQL or SQL Server? If it is SQL Server, you could write a stored procedure and handle the params in the sproc.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38349
    
  23
MySQL will happily handle stored procedures too.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1124

In my opinion, this question has nothing to do with JDBC.
However Campbell decided to move it to here from its original location: Oracle OAS Forum
I guess, Paul and Shawn, that answers your question as to which database is being used.
Abhishek wrote:

I am not allowed to write any code with it , it has to be a SQL query

So pardon my ignorance, Paul, but how does java help Abhishek?

Abhishek, you can use Oracle's NVL function, for example:

If VAR1 has a value, then the condition becomes:
COL1 = VAR1
Otherwise the condition becomes
COL1 = COL1
which is always true.

Good Luck,
Avi.
Hemavathy Ramamoorthy Gopal
Greenhorn

Joined: May 13, 2008
Posts: 17
Hi,

I think using ' OR ' operator in select statement we can fetch the records.
Performance need to be checked based the availability of data.

Eg : WHERE Input1 = 'value1' or isnull(Input1,'') = ''
AND Input2 = 'value2' or isnull(Input2,'') = ''


Regards,
Hema_RG
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38349
    
  23
Sorry if people think I have moved the thread inappropriately. I think both the last solutions would probably work, and as they say in London, "You pays your money and you takes your choice." ;)
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38349
    
  23
Actually you need to make sure when testing for null that it is the input you check, not the value in the table.
abhishek pendkay
Ranch Hand

Joined: Jan 01, 2007
Posts: 184
Thanks everyone for your reply..
and yes I got my answer Avi Abrami's solution works, I could actually do it using the nvl function so thanks for that Avi

Campbell
Sorry if people think I have moved the thread inappropriately

No you were not wrong at all in moving my post to this forum...Thanks Campbell :thumb:
[ September 01, 2008: Message edited by: abhishek pendkay ]
Jason Pirkey
Greenhorn

Joined: Aug 06, 2008
Posts: 9
Avi,

COL1 will not equal COL1 if COL1 is NULL. Since NULL is a state of being instead of the absence of a value or something stupid like that. So if there is a chance of any of your columns being NULL you could get unintended results.

Hems code will really mess you up without placing the left and right parenthesis around each of the statements. Since each OR statement is like providing an additional WHERE clause.

Like:


If there is a chance of one of your columns being NULL you could try something like this:


When I do something like this I make the NVL value something which can't exist in the database. For example I would NVL a VARCHAR2(1) field to '~~' since if you NVLed it to '~' and '~' could be in that column you could get unintended results.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I'm jumping in a little late here but some comments...
  • Never use stored procedures directly with java code, this is definitely a case where they *are not* warranted
  • You can construct the SQL string dynamically and still use PreparedStatements. There's no requirement the SQL string has to be hard-coded ahead of time. It's not complicated to construct a query statement here, you can just have 8 lines such as "if(x.isAvailable()) query += "NAME = ?", then set the PreparedStatement obect similarly.
  • Never use NVL in a JDBC SQL query. Its non-standard for all databases and not really needed in JDBC. Putting NVL in queries equivalent to putting business logic in SQL since you're making decisions about input that should be made in java.


  • Hope that helps.
    -Scott


    My Blog: Down Home Country Coding with Scott Selikoff
    Jason Pirkey
    Greenhorn

    Joined: Aug 06, 2008
    Posts: 9
    NVL is a standard function for an Oracle database and is commonly used. This post originally came from Oracle OAS Forum where Abhishek was needing to write a Plain Jane SQL query without using any Java since iReports allows the entry of Plain Jane SQL Querys.
    Scott Selikoff
    Saloon Keeper

    Joined: Oct 23, 2005
    Posts: 3704
        
        5

    The syntax for NVL is not standard in every database, meaning using it would make all of your queries Oracle-only. E en if it was, it makes more sense to perform 'embedded' logic in Java (a much more robust language) than in SQL. The whole point of helper functions like NVL is that SQL doesn't have much in the way of if/then/else statements, whereas Java has plenty of room for it. Lastly, better to push more complicated logic on the Java side then bother the database server with it.

    But if you want to write non-portable code that unnecessarily taxes the database and contains business logic in the SQL statements that could just as easily be in java, by all means use NVL.
    [ September 30, 2008: Message edited by: Scott Selikoff ]
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: sql query based on variable number of input parameters