Search This Blog

Wednesday, 3 April 2013

criteria.setProjection()

In previous posts we have seen how to use the Criteria interface. We have also been using the setProjection method when needed to select properties or objects from the executed query.
Consider the below query that only retrieves the id of all entities that match a particular string format in their name property.
public static void testSelectId() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.like("name", "entity%"));
    criteria.setProjection(Projections.id());
    List<Integer> ids = criteria.list(); 
    System.out.println(ids);
    session.close();
}
The result of the query is :
select
    this_.ID as y0_ 
from
    ENTITY this_  
where
    this_.NAME like ?
[2, 3, 4, 5]
The Projections.id() method adds the identifier columns of the table to the select clause.It internally returns an instance of IdentifierProjection.
What if we need multiple columns ?
public static void testSelectUsingProperties() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.like("name", "entity%"));
    criteria.setProjection(Projections.projectionList()
            .add(Projections.property("name"))
            .add(Projections.property("master")));
    List<Object[]> rows = criteria.list(); 
    for (Object[] row : rows) {
        System.out.println(row[0] + " and " + row[1]);
    }
    session.close();
}
The above code involves the creation of a ProjectionList instance. The class implements the Projection interface and internally has an array list to hold multiple projections. We simple need to add a projection instance for every column that we need to load. The Projections.property() method takes the name of the property and creates a PropertyProjection instance. The criteria now returns a list of object array just like HQL. The output of the method is as below:
Hibernate: 
    /* criteria query */ 
    select
        this_.NAME as y0_,
        this_.MASTER_ID as y1_ 
    from
        ENTITY this_ 
    where
        this_.NAME like ?
Hibernate: 
    /* load com.model.Master */ 
    select
        master0_.ID as ID1_0_,
        master0_.DATA as DATA1_0_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID=?
entity1 and [Master] : ( id 1 , data : master No 1 )]
entity2 and [Master] : ( id 1 , data : master No 1 )]
Hibernate: 
    /* load com.model.Master */ 
    select
        master0_.ID as ID1_0_,
        master0_.DATA as DATA1_0_ 
    from
        ENTITY_MASTER master0_ 
    where
        master0_.ID=?
entity100 and [Master] : ( id 2 , data : master No 2 )]
entity102 and [Master] : ( id 2 , data : master No 2 )]
As we have loaded the Master instance lazily and using a select strategy, the session executes a database fetch from within the for loop, every time it encounters an unloaded Master instance. What if we need to add aliases to the projection ?
criteria.setProjection(Projections.projectionList()
    .add(Projections.property("name").as("n"))
    .add(Projections.property("master").as("m")));
The PropertyProjection class has an as() method to which we can pass the alias.

2 comments:

  1. Thank you very much.
    I have been looking for the example of working with setProjection() as property extractor,
    and your example showed it ideally

    ReplyDelete
  2. Thank you very much! It's the best explanation of Projection.

    ReplyDelete