Gavin and the Hibernate are looking into what makes a great "Query by Criteria" design. Wrapping an OO model on top of SQL is tough, and this piece walks through various design choices. It is always interesting to watch thought progression such as this.
Gavin ends up "considering supporting something like the following in Hibernate":
session.createCriteria(Project.class)
.createDisjunction()
.add( eq("name", "Hibernate") )
.add( like("description", "%ORM%") )
.list();
Do you have any thoughts? Has anyone worked with a great Query by Criteria API that they loved? Are you someone who would rather just do it in SQL?
Read more from Gavin Designing "query by criteria" in the new Hibernate group blog
-
Opinion: Redesigning "Query by Criteria" in Hibernate (28 messages)
- Posted by: Dion Almaer
- Posted on: December 03 2003 11:41 EST
Threaded Messages (28)
- Opinion: Redesigning "Query by Criteria" in Hibernate by Scott Ferguson on December 03 2003 11:50 EST
- re: Why not just add a text-based query? by Jordan Zimmerman on December 03 2003 16:02 EST
- Because that's what we're trying to get away from? by java developer on December 03 2003 10:47 EST
- re: Why not just add a text-based query? by Jordan Zimmerman on December 03 2003 16:02 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Billy Newport on December 03 2003 12:07 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Deepak Singh on December 03 2003 12:11 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Jason Boutwell on December 03 2003 12:43 EST
- QBC vs other. by Gavin King on December 03 2003 12:49 EST
- Building queries programmatically by Christian Schneider on December 03 2003 02:13 EST
- QBC vs other. by Scott Ferguson on December 03 2003 03:01 EST
-
QBC vs other. by Mike Spille on December 03 2003 03:07 EST
-
QBC vs other. by Carlos Perez on December 03 2003 04:10 EST
- A good example of QBC by Raffaele Guidi on December 03 2003 05:26 EST
- QBC vs other. by Carlos Perez on December 04 2003 07:05 EST
-
QBC vs other. by Carlos Perez on December 03 2003 04:10 EST
- External queries... JDO 2.0 by Dion Almaer on December 03 2003 18:37 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by thoff thoff on December 03 2003 12:42 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Konstantin Ignatyev on December 03 2003 13:22 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by John Ferguson Smart on July 09 2005 09:25 EDT
- Opinion: Redesigning "Query by Criteria" in Hibernate by Guglielmo Lichtner on December 03 2003 14:38 EST
- Enjoyment of QBE by denis krizanovic on December 03 2003 18:00 EST
- SODA by Eric Samson on December 04 2003 04:03 EST
- Comparison to Oracle Toplink by atoy so on December 03 2003 21:58 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Dmitriy Kiriy on December 04 2003 04:54 EST
- use cases by Gavin King on December 04 2003 06:09 EST
-
use cases by Dmitriy Kiriy on December 04 2003 07:37 EST
-
Lazy/eager association fetching by Gavin King on December 04 2003 12:52 EST
- Lazy/eager association fetching by Dmitriy Kiriy on December 05 2003 03:30 EST
-
Lazy/eager association fetching by Gavin King on December 04 2003 12:52 EST
-
use cases by Dmitriy Kiriy on December 04 2003 07:37 EST
- use cases by Gavin King on December 04 2003 06:09 EST
- Opinion: Redesigning "Query by Criteria" in Hibernate by Gopal Pillai on December 04 2003 12:12 EST
- HQL by Sebastian Petzelberger on August 12 2005 09:18 EDT
-
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Scott Ferguson
- Posted on: December 03 2003 11:50 EST
- in response to Dion Almaer
Why not just add a text-based query?
<pre>
session.createCriteria(Project.class)
.restrict("name = 'Hibernate' AND description LIKE %ORM%");
</pre>
I've never liked APIs which expose the expression objects or expression-component methods. Just leave it as a text expression. -
re: Why not just add a text-based query?[ Go to top ]
- Posted by: Jordan Zimmerman
- Posted on: December 03 2003 16:02 EST
- in response to Scott Ferguson
Because a potential benefit of an O/R framework is to hide vendor differences. There are many differences between, for example, MS SQL and Oracle SQL. If all the differences can be hidden under the hood in the O/R framework, vendor independence would be realized. -
Because that's what we're trying to get away from?[ Go to top ]
- Posted by: java developer
- Posted on: December 03 2003 22:47 EST
- in response to Jordan Zimmerman
THe last hting I want ot see is what amounts to "script" in the middle of the query, such as "where X = Y", which is really just SQL or even "X=blah"- that's scripting. we dont' say "x=blah" we say "equals(x)" but once we start thinking like that , we get away from considering just the operators avialable to us on the keyboard - < > + * = and into thinking about other, more fine-grained and useful operators that free us of WHERE clauses... -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Billy Newport
- Posted on: December 03 2003 12:07 EST
- in response to Dion Almaer
I like the text version also, use it like a prepared statement, receive back a handle for the 'compiled text' and execute it later.
It's easier to read/write/learn, it's intuitive to the vast majority of people and it's as fast as these other alternatives once 'prepared'.
Billy -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Deepak Singh
- Posted on: December 03 2003 12:11 EST
- in response to Dion Almaer
I like the idea of making the Query in the XML decriptor and then refering the query by Id or something in the code. This way the same query can be modified easily as well as re-used.
Thanks
Deepak -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Jason Boutwell
- Posted on: December 03 2003 12:43 EST
- in response to Deepak Singh
+1.
Big fan of this method.
>I like the idea of making the Query in the XML decriptor and then refering the >query by Id or something in the code. This way the same query can be modified >easily as well as re-used.
>Thanks
>Deepak -
QBC vs other.[ Go to top ]
- Posted by: Gavin King
- Posted on: December 03 2003 12:49 EST
- in response to Deepak Singh
Why not just add a text-based query? <<
Hibernate has had a sophisticated query /language/ for a long time now. This is an alternative, for the times when its more appropriate. Here is my followup post on why you need *both* options.
http://blog.hibernate.org/cgi-bin/blosxom.cgi/2003/12/03#qbcvslang
QBC shines for queries that are built programmatically.
>> I like the idea of making the Query in the XML decriptor and then refering the query by Id or something in the code. This way the same query can be modified easily as well as re-used. <<
Yes, I like this one too, and Hibernate lets you externalize both HQL and native SQL queries to the mapping document. However, this -certainly- doesn't help for queries that must be built programmatically. -
Building queries programmatically[ Go to top ]
- Posted by: Christian Schneider
- Posted on: December 03 2003 14:13 EST
- in response to Gavin King
well, what I've used is some meta representation of the most common SQL elements like classes for
- Query having:
- Table
- having Columns
- Join
- also having Columns
- WhereClause
- having Statements
- OrderByClause
- having Statements
etc.
so with that you can do things like: query.addTable(..) and query.addWhereClause(..) to construct an object graph of the query you're building programmatically.
Finally you can use the GoF Visitor pattern to traverse this object graph building your query language string which you can throw at the usual SQL executors within your system (a database facade or whatever).
Best Regards,
Chris -
QBC vs other.[ Go to top ]
- Posted by: Scott Ferguson
- Posted on: December 03 2003 15:01 EST
- in response to Gavin King
Hibernate has had a sophisticated query /language/ for a long time now.
Yes, which is why it should be taken advantage of.
> This is an alternative, for the times when its more appropriate. Here is my followup post on why you need *both* options.
If you want a programmatic query generator, then make a query generator. But it should generate hSQL, not some new API.
The Criteria interface is currently a mix of two concepts: a programmatic query generator and a partial implementation of the Query interface.
You can make the two concepts orthogonal by leaving Query stuff to Query and programmatic query generation to Criteria. Criteria would then return a hSQL string.
Your example would then be something like:
<pre>
session.list(Criteria.create(Person.class)
.add(Example.create(elvis))
.generateSQL());
</pre> -
QBC vs other.[ Go to top ]
- Posted by: Mike Spille
- Posted on: December 03 2003 15:07 EST
- in response to Gavin King
Gavin: QBC shines for queries that are built programmatically.
In that case the examples you're using, and the approaches you're considering, seem to show that you're focusing on the wrong thing. Your examples show you stringing big queries along in large chains and a strong emphasis on making the entire chain easily readable/understandable by developers. If you're focusing on programmatic query building, then there aren't going to be big chains of clauses strung together as a developer would type them out in SQL - instead the "query" will be built up piecemeal over time. If you consider that sort of usage, the really troublesome part is adding clauses _over time_ with the right precedence levels.
-Mike -
QBC vs other.[ Go to top ]
- Posted by: Carlos Perez
- Posted on: December 03 2003 16:10 EST
- in response to Mike Spille
You know there a couple of ways to skin this cat:
http://www.manageability.org/blog/archive/20030317%23taxonomy_of_meta_programming/view
The pros and cons of each are really interesting.
String representation:
Extremely flexible however enough rope to hang yourself with. Checking only at runtime. Need to build a parser.
Algebraic Datatype representation:
Static type checking, harder to make a mistake. However extremely verbose. Harder also to build translators since API is not uniform.
Quasi Quote representation:
More of a hybrid of the two above. WYSIWIG with a back door for programatic manipulation.
My quick take, the first and last options are best. The middle one is questionable.
Carlos -
A good example of QBC[ Go to top ]
- Posted by: Raffaele Guidi
- Posted on: December 03 2003 17:26 EST
- in response to Carlos Perez
I think cayenne offers A good example of QBC (AbstractQuery, SelectQuery, matchExp, matchLikeExp, matchJoinExp, etc). I find it very usable (also because I'm not a sql fan) and, as I'm now porting all my code to hibernate (easier to use, cluster and tune up), I'm thinking to write a set of QBC classes to produce OQL queries. Too bad my contract doesn't allow me to publish my work under a free license. Anyhow, I think this could be the best way to do it. -
QBC vs other.[ Go to top ]
- Posted by: Carlos Perez
- Posted on: December 04 2003 07:05 EST
- in response to Carlos Perez
Interesting discussion.
An expanded take here: http://www.manageability.org/blog/stuff/strings-algebraic-datatypes-quasi-quotes
Carlos -
External queries... JDO 2.0[ Go to top ]
- Posted by: Dion Almaer
- Posted on: December 03 2003 18:37 EST
- in response to Deepak Singh
Deepak -
The JDO expert group agrees with this (Gavin himself is playing an important role in that group).
We have planned a 'Named Query' feature which would allow you to do just this.
We talked about this feature the JDO 2.0 Kickoff Meeting article.
---
Named queries was another item that people were looking forward to having. In code, you could executeNamedQuery("FIND_TOP_TEN"), and have the query itself be externalized (in an xml file, properties file, in JNDI, etc). Eric Samson (of LIBeLIS) talked about named queries, and the idea of use cases
--- -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: thoff thoff
- Posted on: December 03 2003 12:42 EST
- in response to Dion Almaer
Factories are better than constructors anyway because
you can have an abstract API and slip in different
implementations. Constructors are good at initializing
an object, not selecting what object to create. -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Konstantin Ignatyev
- Posted on: December 03 2003 13:22 EST
- in response to Dion Almaer
This API seems to be very convenient for constructing dynamic queries on the fly which depend on user's input. I have use similar concept in my HQLBuillder class but will evaluate the API as a replacement for my "proprietary" solution :) -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: John Ferguson Smart
- Posted on: July 09 2005 09:25 EDT
- in response to Konstantin Ignatyev
I agree that both are useful. I find the Hibernate Criteria API well adapted for multi-criteria search functionalities (see my blog on this subject for more details). In other circumstances, they can be cumbersome, it's a question of using the right tool for the right job. -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Guglielmo Lichtner
- Posted on: December 03 2003 14:38 EST
- in response to Dion Almaer
What's being discussed here is something like building the sql abstract syntax tree by hand:
new Criteria(Project.class)
.add( new Equals("name", "Hibernate") )
.add( new Like("description", "%ORM%") )
.execute();
I guess the benefits of this are that you get some type checking: more constraints means fewer bugs. However, another way to achieve the same thing is to keep on using text (HQL or whatever it's called) and just build a static checker that can check against a database. This might even exist already - I don't know.
On another note, I think a straightforward replacement for text query languages is a small api that supports relational algebra. The main ideas involved are
a) a relation (i.e. list of tuples)
b) projection (stripping out columns in a tuple, I think)
c) cartesian product of relations
d) selection (I don't know the name of this, but the point should be obvious)
Example:
Relation r1, r2, r3, r4;
r1 = RelationFactory.get("R1");
r2 = RelationFactory.get("R2");
r3 = r1.times(r2);
r4 = r3.project("Column1, Column2");
Tuple t;
while ((t = r4.nextTuple()) != null) {
//do something
}
I am not sure if it's useful but it seems kind of fun. I guess since it's really a level below SQL it's not so easy to implement. It might be possible to do it at runtime by tracking how the relation was assembled .. -
Enjoyment of QBE[ Go to top ]
- Posted by: denis krizanovic
- Posted on: December 03 2003 18:00 EST
- in response to Dion Almaer
Hey,
The most enjoyable QBE I've ever used is in a product called db4o. You basically construct an object with the fields you want to use as criteria and send it to the engine. The engine returns a collection containing objects that look like this. But, you say, how do I do query using relational operators? Well, there's this thing called SODA which does some magic, tho I've never used it.
Anyway, what I guess I'm really trying to say is that the dude who's behind db4o has spent a lot of time thinking about qbe. Has written an oo qbe interface called SODA, and that you should probably hook up with him and have a chat.
A forum supporting the product is at http://groups.yahoo.com/group/db4o_unmoderated/messagesearch?query=query%20by%20example
and the guys name is Carl Rosenberger and the site is www.db4o.com
regards,
dk- -
SODA[ Go to top ]
- Posted by: Eric Samson
- Posted on: December 04 2003 04:03 EST
- in response to denis krizanovic
Here is the URL for S.O.D.A:
http://sodaquery.sourceforge.net
We at LIBeLIS have written a 100% Java fast and light database (code name FileDB). It is a non-relational database, as the goal is to have very good response times with a very low memory footprint.
It is not an ODBMS too, it is rather a minimal, and highly configurable storage component.
It could be easily embedded within a Java application when there is no need for SQL support. We also have a version for mobile devices. It could also be wrapped by any higher-level persistence mechanisms.
FileDB is using SODA as the basic interface to its internal query engine, as we wanted to avoid the cost of statement parsing.
SODA is nice because it is a first attempt to standardize these things.
Cons: no join, no sub-queries, no param, sometimes too complex, maybe not enough generic.
But all these things can be improved in the future (for instance we support already sub-queries and params in our implementation).
Best Regards, Eric. -
Comparison to Oracle Toplink[ Go to top ]
- Posted by: atoy so
- Posted on: December 03 2003 21:58 EST
- in response to Dion Almaer
Has anyone here used Oracle Toplink? The proposed Query by Criteria design looks similar to the Toplink Session Query which is the default mechanism of querying the database. Here is an example:
ExpressionBuilder b = new ExpressionBuilder();
Expression e1 = b.get("attribute").equal(value1);
Expression e2 = b.anyOf("oneToMany").lessThanEqual(value2);
Expression e3 = b.getAllowingNone("aggregateObject").anyOfAllowingNull("deepProperty").equal(value3);
Expression exp = e1.and( e2.or(e3));
Target t = (Target) session.executeQuery(Target.class, exp);
What I don't like about this technique is that the attributes are evaluated at runtime and produce more exceptions. However, this is the only way to query using complex criteria (like, <, >, between, in, etc.).
Another option is to use Toplink's Query By Example. Its advantage is in using type checking, so there's no chance of misspellings. The result is matched with the declared attributes of the sample instance. However, only one-to-one mappings can be queried on. Here is an example:
ReadObjectQuery query = new ReadObjectQuery();
Employee employee = new Employee();
employee.setFirstName("Bob");
employee.setLastName("Smith");
query.setExampleObject(employee);
Employee result = (Employee) session.executeQuery(query);
(Information taken from Toplink API docs)
Walts -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Dmitriy Kiriy
- Posted on: December 04 2003 04:54 EST
- in response to Dion Almaer
I want to talk not about QBC (which is result limitation) but about another type of limitation - fetch groups.
<quote> (From the JDO 2.0 kick off meeting)
Use Cases or Fetch Groups
This is a fairly open idea right now, however the idea is that a developer can setup different "use cases", which can encapsulate performance characteristics. For example, if you are in one use case, use a particular fetch group (as you know that in this case you will need X, Y, and Z), a certain locking strategy, etc. This could be really powerful, so look out for this concept in the future!
</quote>
So ,as I understand I will can write things like
<code>
List/*<User>*/ queryByCriteria(User.class,..., new String[] {"user.userGroup.*", "user.userSettings.pagesSettings.*"});
</code>
When probably this things will appear in Hibernate? Gavin, may be you answer? -
use cases[ Go to top ]
- Posted by: Gavin King
- Posted on: December 04 2003 06:09 EST
- in response to Dmitriy Kiriy
I am not especially a fan of the "use cases" concept. To me it seems to move stuff into metadata that belongs more naturally in code. The metadata should define stuff that cuts across many usecases, not stuff that is specific to one or two usecases. But this is my predjudice, I suppose. I despise XML metadata hell and prefer to write Java code. If you prefer to write XML, this approach would suit you better.
>> For example, if you are in one use case, use a particular fetch group (as you know that in this case you will need X, Y, and Z), a certain locking strategy, etc. <
Hibernate prefers that you specify this stuff in the Java code and in the query language.
Anyway, in time, I could perhaps be convinced otherwise. Patrick Linskey has told we a couple of nice things about the usecase concept that had me a little bit interested. -
use cases[ Go to top ]
- Posted by: Dmitriy Kiriy
- Posted on: December 04 2003 07:37 EST
- in response to Gavin King
Gavin
As soon, as I understand, nobody want "move stuff into metadata that belongs more naturally in code". No. We ALLREADY have a metadata in Hibernate configuration files. What I am saying, is when I write something like "user.userSettings", Hibernate generate only SQL for retrive this relationship and not others, or insure that cached "user" object have collection of "userSettings" filled -
Lazy/eager association fetching[ Go to top ]
- Posted by: Gavin King
- Posted on: December 04 2003 12:52 EST
- in response to Dmitriy Kiriy
What I am saying, is when I write something like "user.userSettings", Hibernate generate only SQL for retrive this relationship and not others, or insure that cached "user" object have collection of "userSettings" filled<
But Hibernate can already do this at either the code or metadata level...
In metadata:
<set name="userSettings" outer-join="true" ....
or:
<set name="userSettings" lazy="true" ...
Then override it in code:
session.createQuery("from User u left join fetch u.userSettings").list();
session.createCriteria(User.class)
.setFetchMode("userSettings", FetchMode.EAGER)
.list();
Do you /really/ also need:
<set name="userSettings" ...>
<fetch-mode use-case="foo" outer-join="true"/>
<fetch-mode use-case="bar" outer-join="false"/>
session.setUseCase("foo");
or whatever?
I dunno .... I can't see much added value personally.... -
Lazy/eager association fetching[ Go to top ]
- Posted by: Dmitriy Kiriy
- Posted on: December 05 2003 03:30 EST
- in response to Gavin King
Thx. Absolutely clear now. -
Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]
- Posted by: Gopal Pillai
- Posted on: December 04 2003 12:12 EST
- in response to Dion Almaer
Whatever API design we agree/disagree with (like createDisjunction, and() , add(), etc), I think we need to look at the property value we specify in the query (e.g "name" or "description") will cause problems in maintaining the project. When the Object changes (removing one property or changing the method name) will not show until its deployed.
Is there any alternative to this? -
HQL[ Go to top ]
- Posted by: Sebastian Petzelberger
- Posted on: August 12 2005 09:18 EDT
- in response to Dion Almaer
You forgot to mention Hibernate Query Language.
If Criteria is not good, you can use HQL. If you realy need, you can use native SQL.