aspose file tools*
The moose likes JDBC and the fly likes Long concatenated SQL stmts Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Long concatenated SQL stmts" Watch "Long concatenated SQL stmts" New topic
Author

Long concatenated SQL stmts

Jack Adams
Greenhorn

Joined: Jun 08, 2004
Posts: 24
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
gpa
honors,
ss#,
birthdate,
lastname
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
Bartender

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: http://aspose.com/file-tools
 
subject: Long concatenated SQL stmts
 
Similar Threads
PreparedStatement v/s Statement
JSP Dedugging
Updating Access Db **Please Help**
benefit of Statement over PreparedStatement?
Dynamic column selection in prepared statement?