Fetching association with Hibernate Criteria Query

What is Criteria Query?

Why do we need this?

When is this used?



Here are the answers.... with examples

We have an online shopping application where the user can place an order. This application provides a JSP with search criteria like ShopName,PublisherName,BookPriceRange and availability(One can add their own search criterias) .Inorder to fetch the results our query should be formed in such a way that the parameters should be added run time(as the user selects can select only shopname sometimes both shopname and publisher).Now the criteria queries comes in hand. Based on the user selection we will do necessary validation and build up the SQL query dynamically. I think you got the answers.Now we will drive directly into an example.

Lets think about what the search results should contain.. Bookname,PublisherName,ShopAddress,Cost of the Book. In this application we have three pojo's and 3 corresponding tables and they are
1.Shop(Association type with book is one to many )
2.Book(Association type with publisher is one to many )
3.Publisher
So as per above, the shop class contains set of books and book class contains publisher id. Now lets see how will we build the query

1.Criteria criteria = session.createCriteria(Shop.class,"shop")
2. .createCriteria("Books","book")
3. .add(Restrictions.eq("shop.Name",shopname))
4. .add(Restrictions.between("book.price",minPrice,maxPrice))

lets understand the above 4 lines.Before explaining them, assume shopname,minPrice,MaxPrice are the parameters passed from the presentation layer i.e the user entered values in the search criteria.
First line is as usual it says hibernate to fetch all the data from Shop table.
Second line makes a join with the book table.

Question:Why do we need to join shop and book
Answer : valid doubt, since the Shop is already having an association mapping called books to Book class. whenever hibernate queries for Shop it fetches the data from book table also. But it depends on whether your association has lazy=false or not.Normally we prefer lazy=true for associations so in that case we have to do one more select by giving Shop.getBooks().
Alternatively, here in our online shopping application the user enters the book name, so based on this criteria we want to limit the number of books being fetched.This is the reason we are joining with Book class.

Third and Fourth lines are restrictions i.e. our query criteria;s in where condition of SQL queries.

Now lets go further and see what happens if the user has entered publisher name in the search criteria.In that case we need to join Publisher table.

Note that I am adding lines to the same above query

1.Criteria criteria = session.createCriteria(Shop.class,"shop")
2. .createCriteria("Books","book")
3. .add(Restrictions.eq("shop.Name",shopname))
4. .add(Restrictions.between("book.price",minPrice,maxPrice));
5. if(publishername!= null)
6. criteria.createCriteria("Publisher","publisher")
7. .add(Restrictions.eq("publisher.Name",publishername)) ;

Fifth line checks whether the user entered any publishername if there is any then we need to Join publisher table thats what we are doing in second line and seventh line is a restriction on publisher name.

Note: restrictions are nothing but the condition we put after "where" in sql query.

What happens if i say criteria.list() ?
It fetches all the data from the three tables shop,Book,Publisher..

answer is no

why . if you can go back and see the query once again our first criteria query is on the entity and the other 2 criterias are on the association mappings.So the result contains data from Shop table
But our main aim is to fetch data from all the three tables here comes the usage of projections.

Question: what are Projections in hibernate and how are they used.
Answer: i will explain with an example in our shop table suppose if i only want shopname then my query would be formed like this

select shopname from shop instead of select * from shop

this is what a projection do it basically fetches the selected columns from the table.

Coming to our dicussion how can we use projections here. take the same old query and set the projection as bellow.


criteria.setProjection(Projections.projectionList().add(Projections.property("shop.name"),"shopname").add(Projections.property("book.name"),"BookName"))


Here i have created a list of projections for the selected columns(column names should prefix with table alias specified in criteria query if you are confused look into our first criteria query once).


This gives the list of Object[] array.


In order to transform this array to a map with key as alias please refer my earlier post.
http://prasadmarrapu.blogspot.com/2008/07/result-transformers-in-hibernate.html

Want to see tutorials on more Technologies and latest updates go to
http://prasad.marrapu.googlepages.com