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 Dymanic queries Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 3697
    
    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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dymanic queries
 
Similar Threads
Advance Search !!
Hibernate Example
Array of Beans in ActionForm does not retrieve any input
Hibernate now, or wait until EJB 3.0?
Generic DAO using Spring