HQL

HQL - HibernateD Query Language

Subset of original Hibernate HQL is supported.

HQL is similar to SQL. Entity names used similar to SQL tables, property names - similar to SQL table columns. HQL field and entity references can be longer than table.field in SQL - e.g. u.customer.address.zip

Only SELECTs are supported in HibernateD

SELECT clause can omitted - first entity in FROM clause will be selected in this case.

In SELECT you can specify either single entity alias or one or more primitive properties.

Aggregate functions and expressions cannot be used for SELECT clause properties in current implementation (only primitive fields or entity aliases can be specified).

Entities are returned as Objects, while individual properties - as Variant[] rows.

INNER JOIN and LEFT JOIN are supported.

FETCH keyword can be added after JOIN keyword to specify that joined entity should be fetched in the same query.

Parameter names should be prefixed by colon. E.g. :Name

WHERE clause is supported with following limitation: functions are not supported.

ORDER BY clause is supported.

Sample queries:

FROM Person WHERE id = :Id AND moreInfo.evenMore.flags > 0
FROM User WHERE id in (1, 2, (3 - 1 * 25) / 2, 4 + :Id, 5)
FROM User WHERE roles.id = 1
FROM Role WHERE users.id = 1
FROM User WHERE customer.id = 1
SELECT a2 FROM User AS a1 INNER JOIN FETCH a1.roles AS a2 WHERE a1.id = 1
SELECT a2 FROM Customer AS a1 JOIN a1.users AS a2 WHERE a1.id = 1
SELECT a FROM User AS a WHERE id = :Id AND name != :skipName OR name IS NULL  AND a.flags IS NOT NULL ORDER BY name, a.flags DESC
SELECT a FROM User AS a WHERE ((id = :Id) OR (name LIKE 'a%' AND flags = (-5 + 7))) AND name != :skipName AND flags BETWEEN 2*2 AND 42/5 ORDER BY name, a.flags DESC
SELECT a FROM Person AS a LEFT JOIN a.moreInfo as b LEFT JOIN b.evenMore c WHERE a.id = :Id AND b.flags > 0 AND c.flags > 0
SELECT a FROM Person a WHERE a.id = :Id AND a.moreInfo.evenMore.flags > 0
FROM User AS u WHERE id = :Id and (u.name like '%test%' or flags=44)
SELECT c.name, c.address.zip FROM Customer AS c WHERE id = :Id