Home » Java » Enterprise Java » JPA 2.0 Criteria Query with Hibernate

About Nadav Azaria

JPA 2.0 Criteria Query with Hibernate

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:

    1.   State [stateId, stateName]
    2.   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.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

 

4 comments

  1. Dominik Weidenfeld

    Why cant we just pass the City Object in the CriteriaQuery but have to create the ValueObjects?
    This: “final CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(City.class);”
    instead of this: “final CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(CityVO.class);”

  2. jiaah to many code
    JPQL or HQL is still preferred

  3. Hello, Im using a criteriaQuery to fetch some records from a table usin filters, But i have a couple of many to many annotations, so it retrieve the complete unit. that doesnt happens when I use a query with entityManager.createQuery(“select p from Table p”).

    somebody knows why this happen?

  4. This tutorial is a bit off base since one of the biggest reasons to even go thru the trouble of creating Criteria Query is to have full compiler support for Entity properties, which is completely negated by this piece: cityRoot. get(“cityName”). If the column name changes one will get a runtime error.

    But thank you! Very nicely put together.

Leave a Reply

Your email address will not be published. Required fields are marked *

*


seven + 2 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Want to take your Java Skills to the next level?
Grab our programming books for FREE!
  • Save time by leveraging our field-tested solutions to common problems.
  • The books cover a wide range of topics, from JPA and JUnit, to JMeter and Android.
  • Each book comes as a standalone guide (with source code provided), so that you use it as reference.
Last Step ...

Where should we send the free eBooks?

Good Work!
To download the books, please verify your email address by following the instructions found on the email we just sent you.