From SQL to HQL

Most of the development I have done has been with SQL records. Now that CF9 is out I've started playing around with HQL objects and it has been a learning curve but for the best. I struggled and continue to struggle with writing HQL, because my mind still wants to do SQL. I wanted to share what I have learned so far.

Given the db schema below I will show how to write a piece in sql followed by a piece in hql.

There is a PRODUCT table with a many to many relationship with the SIZE table and the PRODUCT table has many to many relationship with the CATEGORY table.

PRODUCT -|-----= PRODUCT_SIZE =------|- SIZE
PRODUCT -|-----= PRODUCT_CATEGORY =-----|- CATEGORY

1. Get all the products
SQL

select * from product

HQL

from Product

2. Get all the products with price equal to $100.
SQL

select * from product where price = '100'

HQL

from Product where price = '100'

3. Get all the products with size_id = 1.
SQL

select * from product 
inner join product_size on product.id = product_size.product_id
where product_size.size_id = 1

HQL

select product
from Product product
join product.sizes size
where size.id = 1

4. Get all the products with size_id = 1 and category_id = 1.
SQL

select * from product 
inner join product_size on product.id = product_size.product_id
inner join product_category on product.id = product_category.product_id
where product_size.size_id = 1
and product_category.category_id = 1

HQL

select product
from Product product
where exists (
from product.categories category
where category.id = 1
)
and exists (
from product.sizes size
where size.id = 1
)

Note: HQL can't solve all queries, a good example is Accounting queries, but it does help with the majority of the light weight queries. I get the line "Well what's the point of using objects if records work fine.". I reply with "There is no point, but if you want to update all your queries with new properties, you will quickly find the point."

Resources used:

http://www.barneyb.com/barneyblog/2010/05/05/embrace-your-hsql/