View RSS Feed

My Java Tips

Hibernate Queries

Rate this Entry
by , 11-07-2011 at 07:02 PM (1174 Views)
Hibernate is a popular OR (Object Relational) mapping framework. To access database, we have to use Hibernate Query language. I will present the basics in this post that will help you starting query database tables using HQL.


Hibernate queries can be roughly divided into 3 categories:

The queries we general use can be divided up into three main categories :

- Queries using one class
- Queries which several classes so join operation is required
- Queries which cannot be efficiently done using joins

Lets take an example of simplest HQL query:

from Sale sale where sale.date > :startDate
Let me present a query with join operation:

from Cat as cat
inner join cat.mate as mate
left outer join cat.kittens as kitten

A little tricky example follows:

from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens child
left join fetch child.kittens</pre>
Consider the following (simplified) Hibernate mappings:

<class name="Sale" dynamic-update="true" table="t_sale">
<id column="sale_ch_id" name="id" unsaved-value="null"></id> type="string"&gt;
<generator class="uuid.hex">
<property column="sale_d_date" name="date" type="date"></property> not-null="true"/&gt;
<property column="sale_n_price" name="price"></property> type="big_decimal" not-null="true"/&gt;
<many-to-one name="product" class="Product"> column="prod_ch_id".../&gt;
...
</many-to-one></generator></class>

I will continue present HQL tips and trick that will help you in development.



The following query may return multiple objects as an array of type Object[].


select mother, offspr, mate.name
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr

Now suppose, you would like to get a list in the above scenario - use the following query:


select new list(mother, offspr, mate.name)
from DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr

One may need type safe Java object as result.


select new Family(mother, mate, offspr)
from DomesticCat as mother
join mother.mate as mate
left join mother.kittens as offspr

This is the beauty of Hibernate. You can get data fetched from database tables in form of list, objects or arrays. This is useful in complex applications.

Do read the next posts on this very topic.
I will continue exploring Hibernate queries with example.



The example presented below returns the order id, number of items and total value of the order for all unpaid orders for a particular customer and given minimum total value, ordering the results by total value. It uses four inner joins and an (uncorrelated) subselect.

select order.id, sum(price.amount), count(item)
from Order as order
join order.lineItems as item
join item.product as product,
Catalog as catalog
join catalog.prices as price
where order.paid = false
and order.customer = :customer
and price.product = product
and catalog.effectiveDate &lt; sysdate
and catalog.effectiveDate &gt;= all (
select cat.effectiveDate
from Catalog as cat
where cat.effectiveDate &lt; sysdate
)
group by order
having sum(price.amount) &gt; :minAmount
order by sum(price.amount) desc
The above query without subqueries in simplest form is:

select order.id, sum(price.amount), count(item)
from Order as order
join order.lineItems as item
join item.product as product,
Catalog as catalog
join catalog.prices as price
where order.paid = false
and order.customer = :customer
and price.product = product
and catalog = :currentCatalog
group by order
having sum(price.amount) &gt; :minAmount
order by sum(price.amount) desc
The next example counts the number of payments in each status, excluding all payments in the AWAITING_APPROVAL status where the most recent status change was made by the current user.

select count(payment), status.name
from Payment as payment
join payment.currentStatus as status
join payment.statusChanges as statusChange
where payment.status.name &lt;&gt; PaymentStatus.AWAITING_APPROVAL
or (
statusChange.timeStamp = (
select max(change.timeStamp)
from PaymentStatusChange change
where change.payment = payment
)
and statusChange.user &lt;&gt; :currentUser
)
group by status.name, status.sortOrder
order by status.sortOrder.

Submit "Hibernate Queries" to Facebook Submit "Hibernate Queries" to Digg Submit "Hibernate Queries" to del.icio.us Submit "Hibernate Queries" to StumbleUpon Submit "Hibernate Queries" to Google

Tags: None Add / Edit Tags
Categories
Hibernate

Comments