• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Dymanic queries

 
Jorge Ribeiro
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic