Hibernate Query Language

A query language from the HiberNate O-R mapper project with some interesting properties:

Although it seems tied to Java for the time being, some of its ideas can perhaps be applied to revamping, wrapping, or replacing the SQL standard.

Here's an example from the Hibernate docs:

[EditHint: insert description of what it actually does in English - under construction]

HQL:

 select cust
 from Product prod,
    Store store
    inner join store.customers cust
 where prod.name = 'widget'
    and store.location.name in ( 'Melbourne', 'Sydney' )
    and prod = all elements(cust.currentOrder.lineItems)

Versus allegedly equivalent SQL:

 SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
 FROM customers cust,
    stores store,
    locations loc,
    store_customers sc,
    product prod
 WHERE prod.name = 'widget'
    AND store.loc_id = loc.id
    AND loc.name IN ( 'Melbourne', 'Sydney' )
    AND sc.store_id = store.id
    AND sc.cust_id = cust.id
    AND prod.id = ALL(
        SELECT item.prod_id
        FROM line_items item, orders o
        WHERE item.order_id = o.id
            AND cust.current_order = o.id
    )

(SQL 92 does support INNER JOIN, and some dialects support automatic joining between tables where key constraints are defined - i.e. store.loc_id = loc.id would not be required. This would bring the two statements to be very close - the HQL example above is hiding implementation as the table mapping. It could also be achieved through use of a database view (again, pre-defined mapping). Not to say this is a bad thing - it saves on repeatedly defining the same relationships in multiple statements).

{I see some other ways that could possibly shorten the above SQL, although it may lengthen others. One example is generally not sufficient enough to compare.}

HQL does not actually require a select clause for mapped entities "from Product" is a completely valid query which would presumably return instances of the Product class (you could map the product entity to any Java type but the class name is customary).

Another important feature of HQL and Hibernate in general is the abillity to use bind parameters. Bind parameters allow you to reliably avoid the problem of SqlInjection.

  Query q = sess.createQuery(
			"from DomesticCat cat where cat.name = :name");
  q.setString("name", "Fritz");
  Iterator cats = q.iterate();

(Almost all existing flavours of SQL have supported bind variables for the last decade. It is the treatment of SQL as concatenated strings that is the main cause of SqlInjection - not to mention performance issues).


Questions


See also: SqlFlaws, RelationalLanguage, QueryWrapperLanguageOverSql


EditText of this page (last edited January 13, 2008) or FindPage with title or text search