JPA 2.0 Criteria Query with Hibernate

Nadav AzariaApril 3rd, 2013Last Updated: April 3rd, 2013

5 629 4 minutes read

Criteria Query has been introduced in JPA 2.0. With the help of criteria queries you can write your queries in a type-safe way. Before criteria queries, developers had to write queries through the construction of object-based query definitions. There might be chances of incorrect syntax while building the queries. The criteria query API provides feature to create structured and nested queries with compile time safety. Getting into more theory about criteria query may not be a good idea as there are plethora of pages can be found on net for same. Let’s have a simple example to understand query using criteria query. In this post, I used hibernate as vendor with JPA 2.0 to show the example of Criteria Query.

Before going into deep about criteria query, let’s think about database tables and corresponding java classes which can be represented as entities:

Database Tables:

For example we have below two database tables:

State [stateId, stateName]
City [cityId, stateId(FK#), CityName]

We want to fetch list of cities for a particular state where city name should be started from ‘M’ and city list should be in ascending order. If we think about simple native SQL, it would be like below:

Select * from City c, State s where c.stateId = s.stateId and c.stateId = ? and c.cityName like "M%" order by c.cityName

JPA Entities:

Below are the two java classes configured as JPA entities to represent state and city tables. These can be generated using reverse engineering tool as well.

Java entity for STATE table

 import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import java.util.Date;
import java.util.Set;
import javax.persistence.SequenceGenerator;

@Entity
@Table(name="STATE")
@SequenceGenerator(sequenceName="STATE_SEQ",name="STATE_SEQ_GEN")
public class State {
    private Long stateId;
    private String stateName;
    private Set citySet;
    @Id
    @Column(name="stateId")
    @GeneratedValue(generator="STATE_SEQ_GEN",strategy=GenerationType.SEQUENCE)
    public Long getStateId;() {
       return stateId;
    }
    public void setId(long stateId) {
        stateId = stateId;
    }

   @Column(name="stateName")
    public String getStateName() {
        return stateName;
    }
    public void setStateName(String stateName) {
        this.stateName = stateName;
    }
    public void setCitySet(Set citySet)
        this.citySet= citySet;
    }

    @OneToMany(fetch=FetchType.LAZY,cascade=CascadeType.ALL,mappedBy="state")
    public Set getCitySet() {
        return citySet;
    }
}

Java entity for CITY table

 import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name="CITY")
@SequenceGenerator(sequenceName="CITY_SEQ",name="CITY_SEQ_GEN")
public class City {
    private Long cityId;
    private String cityName;
    private State state;

    public void setId(long cityId) {
        this.cityId = cityId;
    }
 @Id
 @Column(name="CityId")
 @GeneratedValue(generator="CITY_SEQ_GEN",strategy=GenerationType.SEQUENCE)
    public Long getCityId() {
        return cityId;
    }
    @Column(name="CityName")
    public void setContent(String cityName) {
        this.cityName = cityName;
    }
    public String getCityName() {
        return cityName;
    }
    public void setState(State state) {
        this.state = state;
    }
   @ManyToOne(fetch =  FetchType.LAZY)
   @JoinColumn(name = "STATEID", nullable = false)
    public state getState() {
        return state;
    }
}

Value Objects (POJO):

Below are the two value objects. With the help of criteria query you can directly map your value objects from result data. You don’t need to write the code to copy the result data from entity classes to your values objects. This is really an exciting feature in criteria query.

 public class StateVO {
    private Long stateId;
    private String stateName;
    private Set cityVOSet;
   // All getter setters
 }
public class CityVO {
    private Long cityId;
    private String cityName;
    private StateVO stateVO;
    public CityVO( Long cityId, String cityName){
                  this.cityId=cicityId;
                  this.cityName=cityName;
    }
    // All getter setters
}

DAO Implementation:

Now this is the time where we can use criteria query to fetch the data. We will expose a method which will take input argument as StateVO and will return list of CityVOs.

  import javax.persistence.EntityManager;
       import javax.persistence.EntityManagerFactory;
       import javax.persistence.Persistence;
       public class StateCityDAOImpl{
       
       public List getCityList(StateVO searchStateVO)  {
         // Get the entity manager instance
         EntityManagerFactory emf = Persistence.createEntityManagerFactory("StateCityService");
         EntityManager entityManager= emf.createEntityManager();
     
         // Get the criteria builder instance from entity manager
        final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
       
       // Create criteria query and pass the value object which needs to be populated as result
        final CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CityVO.class);
     
        // Tell to criteria query which tables/entities you want to fetch
        // To join the City and State tables, we need to write below code
        final Root stateRoot = criteriaQuery.from(State.class);
        final Root cityRoot = criteriaQuery.from(City.class);
   
       // Time to define where clause in terms of Predicates
        // This list will contain all Predicates (where clauses)
        List criteriaList = new ArrayList();
    
       // Note: Ensure that whatever string you are passing in root variables
        // It should be matched with variables' name in entity classes
     
        // [1] where condition: State.StateId = City.StateId
        Predicate predicate1 = criteriaBuilder.equal(
                cityRoot. get("state"). get("stateId"),
                stateRoot.  get("stateId"));
        criteriaList.add(predicate1);

        // [2] where condition: City.StateId = ?
        if (searchStateVO.getStateId() != null) {
           Predicate predicate2= criteriaBuilder.equal(
                    cityRoot. get("state"). get("stateId"),
                    searchStateVO.getStateId());
            criteriaList.add(predicate2);
        }

        // [3] where condition: City.cityName like 'M%'
        Predicate predicate3
                  = criteriaBuilder.like(criteriaBuilder.upper(cityRoot. get("cityName")),
                      "M%");
            criteriaList.add(predicate3);  
     
        // This statement maps your CityVO with result data
        // You have to have a custom constructor in CityVO  (see above) to populate the result data   
        criteriaQuery.select(criteriaBuilder.construct(CityVO.class, cityRoot. get("cityId"),
                cityRoot. get("cityName")));
 
        // Pass the criteria list to the where method of criteria query
        criteriaQuery.where(criteriaBuilder.and(criteriaList.toArray(new Predicate[0])));

        // Order by clause based on city names
        criteriaQuery.orderBy(criteriaBuilder.asc(cityRoot. get("cityName")));

        // Here entity manager will create actual SQL query out of criteria query
        final TypedQuery query = entityManager.createQuery(criteriaQuery);
      
        // This code can be used to define the row range to fetch the result
        /* if (CitySize != 0) {
            query.setFirstResult(startIndex); // starting row index
            query.setMaxResults(endIndex); //  end row index
       }*/
        return query.getResultList();
  }

Now we have CityVO list as answer in your hand. We can access the corresponding city name based on our aforementioned requirement.

Though, writing queries using criteria query may be little bit clumsy but once you get handy, you will enjoy the Criteria Query.

Reference: JPA 2.0 Criteria Query with Hibernate from our JCG partner Narendra Verma at the NS.Infra blog.