This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Dymanic queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dymanic queries" Watch "Dymanic queries" New topic
Author

Dymanic queries

Jorge Ribeiro
Ranch Hand

Joined: Oct 18, 2005
Posts: 40
Hi,

We have a Java EE back-end application using EJBs plus DAOs. We are now going to integrate a Java Web Front-End. The front-end will allow the user to query data, providing numerous query criterias- simple comparisons (= ,<, >, >=, =<, !=), intervals (between X and Y), multiple values (in (A, ..., Z)), etc. That means that the queries we use on the DAOs have to be created dynamically, depending on the criterias provided by the user. It goes without saying that a criteria influences the columns we include in the where clause, the operators and potential joins.

Therefore we need to create a framework that allows us to do this. The FE must pass data in a structured way and the BE must be able to process this data and generate the query according to it. A simple implementation would be to have have an interface QueryCriteria with X different implementations (NormalComparision, In, Between, etc). The FE would pass a Map of <Name, QueryCriteria> to the BE. The BE would take a query template (for example using XSLT or Velocity) and would generate the query dynamically.

But I guess that this is a requirement common to many applications so I was wondering if there is any framework that helps with this- an implementation of the classes needed to represent the query criterias, and/or to define query templates and to generate them during runtime using the query criterias. I know that Hibernate provides the Criteria API, but using Hibernate is not an option for us. Any advice on best practices for this kind of problems is also very welcome.

Many thanks!
Jorge
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Ah, the database anti-pattern, an old friend. I call it an anti-pattern because you're essentially reimplementing a database system inside an application and UI tier. Even when you are successful building such a system, the user often has way too much access to perform queries than will grind your system to a halt.

There's really nothing I know of to help you accomplish this other than building a rather complex query system. I'd suggest you try to avoid the anti-pattern all together and give them limited search capabilities or, the more common solution, give them a list of reports based on fixed (but parameterized) pre-written SQL queries. Often times there's really only a handful of queries the user really wants, so figure out which ones they are and offer it to them as a report.


My Blog: Down Home Country Coding with Scott Selikoff
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Dymanic queries
 
Similar Threads
Generic DAO using Spring
Hibernate Example
Advance Search !!
Hibernate now, or wait until EJB 3.0?
Array of Beans in ActionForm does not retrieve any input