File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Long concatenated SQL stmts Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Long concatenated SQL stmts" Watch "Long concatenated SQL stmts" New topic

Long concatenated SQL stmts

Jack Adams

Joined: Jun 08, 2004
Posts: 25
We have clients that like searching and creating reports on many different fields.
So, arbiraty example,a STUDENT, might be searched using:
born after, before, between
major, minor
etc, etc
The sql stmt is usually just concatenated with each additional field
SQL = SQL + (another search criteria)

It works, and we have made the process "standard", but it really seems backwards.
1. Can never really test completely (so many choice combinations)
2. Stmts will never be compiled (-always- changing, not prepared)
3. Not very readable (vs prepared stmt that can be made a constant string)

? Does hibernate (o/r) really solve this problem
? How do you handle this
? Do you limit users choices ever (since not all fields will have index on them)
Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
Hibernate is also more good for static queries.
You can use prepared statements for dynamic queries also .It is only that you keep on adding serach criteria on the String Buffer and finally convert it to string and make it a prepared statement
In the same way you can set the filter fields of prepared statment also
Code can be complex involiving lot of if else conditions but it is workable

SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Hibernate does have a more elegant solution; you can use Criteria, which avoids the sort of string manipulation you tend to see building dynamic SQL.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
I agree. Here's the link:
subject: Long concatenated SQL stmts
It's not a secret anymore!