A query language from the HiberNate O-R mapper project with some interesting properties:
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