jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes Dymanic queries 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 "Dymanic queries" Watch "Dymanic queries" New topic

Dymanic queries

Jorge Ribeiro
Ranch Hand

Joined: Oct 18, 2005
Posts: 40

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!
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

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.

[OCA 8 Book] [Blog]
I agree. Here's the link: http://aspose.com/file-tools
subject: Dymanic queries
It's not a secret anymore!