Opinion: Redesigning "Query by Criteria" in Hibernate

Discussions

News: Opinion: Redesigning "Query by Criteria" in Hibernate

Bookmark and Share Get thread feed

  1. Opinion: Redesigning "Query by Criteria" in Hibernate (28 messages)

    • Posted by: Dion Almaer
    • Posted on: December 03 2003 11:41 EST
    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
    Reply to this

    Threaded Messages (28)

  2. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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.
    Reply to this Reply to original

  3. re: Why not just add a text-based query?[ Go to top ]

    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.
    Reply to this Reply to original

  4. Because that's what we're trying to get away from?[ Go to top ]

    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...
    Reply to this Reply to original

  5. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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
    Reply to this Reply to original

  6. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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
    Reply to this Reply to original

  7. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    +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
    Reply to this Reply to original

  8. QBC vs other.[ Go to top ]

    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.
    Reply to this Reply to original

  9. Building queries programmatically[ Go to top ]

    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
    Reply to this Reply to original

  10. QBC vs other.[ Go to top ]

    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>
    Reply to this Reply to original

  11. QBC vs other.[ Go to top ]

    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
    Reply to this Reply to original

  12. QBC vs other.[ Go to top ]

    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
    Reply to this Reply to original

  13. A good example of QBC[ Go to top ]

    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.
    Reply to this Reply to original

  14. QBC vs other.[ Go to top ]

    Interesting discussion.

    An expanded take here: http://www.manageability.org/blog/stuff/strings-algebraic-datatypes-quasi-quotes

    Carlos
    Reply to this Reply to original

  15. External queries... JDO 2.0[ Go to top ]

    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
    ---
    Reply to this Reply to original

  16. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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.
    Reply to this Reply to original

  17. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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 :)
    Reply to this Reply to original

  18. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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.
    Reply to this Reply to original

  19. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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 ..
    Reply to this Reply to original

  20. Enjoyment of QBE[ Go to top ]

    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-
    Reply to this Reply to original

  21. SODA[ Go to top ]

    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.
    Reply to this Reply to original

  22. Comparison to Oracle Toplink[ Go to top ]

    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
    Reply to this Reply to original

  23. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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?
    Reply to this Reply to original

  24. use cases[ Go to top ]

    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.
    Reply to this Reply to original

  25. use cases[ Go to top ]

    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
    Reply to this Reply to original

  26. Lazy/eager association fetching[ Go to top ]

    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....
    Reply to this Reply to original

  27. Lazy/eager association fetching[ Go to top ]

    Thx. Absolutely clear now.
    Reply to this Reply to original

  28. Opinion: Redesigning "Query by Criteria" in Hibernate[ Go to top ]

    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?
    Reply to this Reply to original

  29. HQL[ Go to top ]

    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.
    Reply to this Reply to original