Spring Query API

[scottr]

Hi All,

I have been thinking for some time about what I believe would be a useful extension to the Spring framework, specifically to the DAO and ORM areas, in the form of a generic, OO query API.

I find that the different competing ORMs have some sort of OO based query API, a good example of which (and which I often use) is Hibernate's Criteria API. But there are a number of problems I have with Hibernate's Criteria API, being:

• a) Not portable between ORMs (only works with Hibernate)
  b) Tied to a hibernate Session. You cannot just create a Criteria out-of-session, populate it, then re-use it later in a different session.

I think a generic Spring query API, similar to Hibernate's Criteria API, would be extremely useful. The features it should have would be:

• a) Object Oriented. A query exists as a type-safe object, rather than as a language-specific query string,
  b) Possess common ORM/dao query features - including the following:
  [list:d7ba361e6a] i) Selection set,
  ii) Conditions/filters,
  iii) Fetch sets (eg. for pre-fetching lazy relationships),
  iv) Grouping,
  v) Ordering,
  vi) Result format (ie. List vs Unique result object)

b) ORM independent. Query objects should exist independently of the ORM chosen to implement a DAO, and be re-useable between different ORMs (jdbc, Hibernate, jdo, ojb, etc),
c) Disconnedted from data-access layer. In other words, the query could be created anywhere, in the View or in the service layer, with no dependence on a jdbc connection/hibernate session/etc, in order to be created or exist.
d) Configurable via xml - to allow defining queries in spring xml context files,
e) Helper translators for all supported ORMs - to allow a query to be transalated into a Hibernate query (Criteria), JDO query, OJB, jdbc, etc,[/list:u:d7ba361e6a]
Some of the perceived benefits of such an API would be:

• a) Allow for the removal a lot of repetitive DAO code, or even allow just using a generic DAO for all service implementations,
  b) Allow for dynamic queries that could be, for example, driven by human input.
  c) Allow for persistence of query objects (eg. user on a website defines preferred filters, saves them for later re-use),
  etc.

Does anyone have any thoughts/comments on this?

regards,
Scott Russell

[tentacle]

Scott,

Rod Johnson thinks Spring should only encapsulate the ugly details of persistence but certainly not all of it. I do agree with him this would not be one of Spring's core tasks.

Myself I'm somewhere in the middle between it's-a-good-thing and it's-a-bad-thing. I guess it depends on which way you look at it. Before I continue let me explain I see your proposal as a complete encapsulation of querying functionality for any persistence layer behind a common API.

I've recently created a query language for a customer that uses a model based EJB environment. This environment already had a lot of code generation so adding another few templates wasn't an issue. The requirement was to convert this universal query language to either SQL, EJB-QL or HQL.

The language is object oriented. The problem we were facing was that we had to translate bean names to table names in case of SQL. So we required meta data to do the conversion. This was not an issue since the meta data was generated based on the model. For EJB-QL and HQL this was much less of an issue since they're object oriented as well. We just carbon copied the property names and derived the entity bean name for EJB-QL.

It because of the meta data I haven't started a similar project yet. Basically you would have to set up the meta data that says which object maps to which table, which property maps to which field. The conversion to SQL has not yet been implemented but we were thinking we could determine the field type based on JDBC meta data.

Another issue for SQL is the conversion of the result set to objects. This is a feature that was already in place, it's basically a closure that maps result set fields to bean properties. These closures were also generated based on the model.

This is just a small compilation of issues that I've encountered. I would like to hear other comments, maybe we could reach a common understanding of what requirements are important and start working on it.

[scottr]

Scott,

Rod Johnson thinks Spring should only encapsulate the ugly details of persistence but certainly not all of it. I do agree with him this would not be one of Spring's core tasks.

Myself I'm somewhere in the middle between it's-a-good-thing and it's-a-bad-thing. I guess it depends on which way you look at it. Before I continue let me explain I see your proposal as a complete encapsulation of querying functionality for any persistence layer behind a common API.

I've recently created a query language for a customer that uses a model based EJB environment. This environment already had a lot of code generation so adding another few templates wasn't an issue. The requirement was to convert this universal query language to either SQL, EJB-QL or HQL.

The language is object oriented. The problem we were facing was that we had to translate bean names to table names in case of SQL. So we required meta data to do the conversion. This was not an issue since the meta data was generated based on the model. For EJB-QL and HQL this was much less of an issue since they're object oriented as well. We just carbon copied the property names and derived the entity bean name for EJB-QL.

It because of the meta data I haven't started a similar project yet. Basically you would have to set up the meta data that says which object maps to which table, which property maps to which field. The conversion to SQL has not yet been implemented but we were thinking we could determine the field type based on JDBC meta data.

Another issue for SQL is the conversion of the result set to objects. This is a feature that was already in place, it's basically a closure that maps result set fields to bean properties. These closures were also generated based on the model.

This is just a small compilation of issues that I've encountered. I would like to hear other comments, maybe we could reach a common understanding of what requirements are important and start working on it.

Thanks for the useful comments.

I take your point that a Spring query API could end up re-inventing the wheel, and retreading the work already covered by existing ORMs. This is most certainly NOT what I would intend. Additionally, I was not so much thinking of a Spring-specific query language (like HQL, SQL, EBJ-QL), which would require a parser and have no type safety.

I'm more thinking of a generic query object model, defining query objects, condition object types (eg. equals, less-than, range). Such a query object model would be a lightweight API that would merely serve to allow programming layers outside the data-access layer, to dynamically build queries and define the extent (in terms of breadth and object-graph depth) of the data required.

There is no need to define meta-data because each ORM already does that. All that would be required would be for an ORM-specific DAO to translate a query object into its own preferred query model. For this it would be sufficient to define a translator for each ORM. Eg. a hibernate translator could convert a generic Spring query object into a hibernate Criteria object.

It is when using plain jdbc and no ORM that such an API would get tricky, potentially requiring meta-data. Perhaps that is one area that would either be too difficult for such a query API, or alternatively would just require programmer intervention defining their own property<=>column, bean<=>table translation (similar to how custom RowMapper-s are defined), within the data-access layer.

So I guess the issue for me at least, is not to invent a Spring ORM and re-invent the wheel, but rather to come up with a generic ORM-independent query and condition object model, that is lightweight, and can naturally exist outside the data-access layer (to allow views for example to build user-defined queries). And such a model would merely be a disconnected wrapper over the existing query facilities of existing ORMs.

regards,
Scott

[trisberg]

Scott,

Are you looking for something like Squiggle SQL Builder ( http://joe.truemesh.com/squiggle/ )?

I did play around with it and added some extensions to it a while ago. The project itself seems to have been forgotten (I guess Joe is busy with other projects) but you could probably use it to get ideas. It's pretty easy to dynamicly generate sql using it.

[scottr]

Scott,

Are you looking for something like Squiggle SQL Builder ( http://joe.truemesh.com/squiggle/ )?

I did play around with it and added some extensions to it a while ago. The project itself seems to have been forgotten (I guess Joe is busy with other projects) but you could probably use it to get ideas. It's pretty easy to dynamicly generate sql using it.

There are similarities but my focus was more on ORM integration than sql.

regards,
Scott

[manifoldronin]

How about The jakarta commons functors package? It has the full predicate model defined in a lightweight manner. All we need is the "binding" implementations for various ORM's. And being part of jakarta-commons, it's practically standardized.

[scottr]

How about The jakarta commons functors package? It has the full predicate model defined in a lightweight manner. All we need is the "binding" implementations for various ORM's. And being part of jakarta-commons, it's practically standardized.

Looks interesting. Actually, the spring sandbox has a similar API for closures and predicates, and I would think that makes a good basis for a query API. Just needs to be fleshed out with more of the features I listed above (eg. fetching preferences, grouping, ordering, etc), and of course the translation to different ORMs.

regards,
Scott

[jnsunkersett]

Hi,

I landed on this forum topic searching for a generic DAO implementation.

Does Spring have any helper objects in that can assist me?

My requirement is very similar ...a generic query api ...to be explicit...:

Logic coded in my business tier works with what I call Domain objects and whenever it requires data it will call upon a Data-Provider in terms of Domain Objects.

The Data-Provider will encapsulate a database.
1. It will receive a request for data in terms of the business/ domain object,
2. internally lookup the data (using some api either JDBC/ Hibernate-ORM or similar or DAO pattern) and
3. Using some thing (what I call a Data-Mapper) will map data received from the database to the domain objects and return.

The reason behind this - my application would be using a database, whose schema (table-design) may vary by locale. By having the business tier communicate in terms of domain objects I can keep it independant of the schema and would only have to implement locale (or schema) specific data-mappers and dao.

For this I had a look at Martin Fowlers patterns :: QueryObject, DataMapper, Meta-DataMapping but implementation is quite complex

PS: I only need to support the 'R' out of CRUD (only SELECT queries with different ORDERBY, WHERE, ...)

Any comments?

~g1

[jayakrishnapalla]

Hi,

I landed on this forum topic searching for a generic DAO implementation.

Does Spring have any helper objects in that can assist me?

My requirement is very similar ...a generic query api ...to be explicit...:

Logic coded in my business tier works with what I call Domain objects and whenever it requires data it will call upon a Data-Provider in terms of Domain Objects.

The Data-Provider will encapsulate a database.
1. It will receive a request for data in terms of the business/ domain object,
2. internally lookup the data (using some api either JDBC/ Hibernate-ORM or similar or DAO pattern) and
3. Using some thing (what I call a Data-Mapper) will map data received from the database to the domain objects and return.

The reason behind this - my application would be using a database, whose schema (table-design) may vary by locale. By having the business tier communicate in terms of domain objects I can keep it independant of the schema and would only have to implement locale (or schema) specific data-mappers and dao.

For this I had a look at Martin Fowlers patterns :: QueryObject, DataMapper, Meta-DataMapping but implementation is quite complex

PS: I only need to support the 'R' out of CRUD (only SELECT queries with different ORDERBY, WHERE, ...)

Any comments?

~g1

Hi ,
we can use factory related design pattern to make our dao more generic:
/ Abstract class DAO Factory
public abstract class DAOFactory {

// List of DAO types supported by the factory
public static final int CLOUDSCAPE = 1;
public static final int ORACLE = 2;
public static final int SYBASE = 3;
...

// There will be a method for each DAO that can be
// created. The concrete factories will have to
// implement these methods.
public abstract CustomerDAO getCustomerDAO();
public abstract AccountDAO getAccountDAO();
public abstract OrderDAO getOrderDAO();
...

public static DAOFactory getDAOFactory(
int whichFactory) {

switch (whichFactory) {
case CLOUDSCAPE:
return new CloudscapeDAOFactory();
case ORACLE :
return new OracleDAOFactory();
case SYBASE :
return new SybaseDAOFactory();
...
default :
return null;
}
}
}