Querying Entities using JPQL and the Criteria API

When i started using EJB2.0 the only way to query the database is to use EJB QL(EJB Query Language) later on with the introduction of JavaEE 5.0 the EJB QL is Replaced by JPQL (Java Persistence Query Language). Now in today’s world, There are two primary Java EE technologies for querying a database: Java Persistence Query Language (JPQL) and the Criteria API. JPQL is similar in appearance and usage to SQL while the Criteria API provides a more type-safe and object-oriented entity querying capability.JPQL is based on the Hibernate Query Language (HQL) and is a subset of HQL. It is portable and allows queries to be executed against different data stores. This is in contrast to the use of a native query where the query is expressed in the native SQL of the data store.Another older, yet still viable technique is the Java database classes and interfaces found in the java.sql package.

Now i want to share some comparison of JPQL and CriteriaAPI:

JPQL:
String param = "Pav%"; Query query = em.createQuery( "select e from Employee e where e.firstName like :param"); query.setParameter("param", param); List<Employee> employees = query.getResultList(); for (Employee emp : employees){ System.out.println(emp.getFirstName()); }

Equivalent with Criteria API:
String param = "Pav%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Employee> query = cb.createQuery(Employee.class);

        Root<Employee> emp = query.from(Employee.class);
        query.where(cb.like(emp.<String>get("firstName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<Employee> tq = em.createQuery(query);
        tq.setParameter("param", param);
        List<Employee> employees = tq.getResultList();

        for (Employee employee : employees){
            System.out.println(employee.getFirstName());
        }

Example of criteria API using count:
In JPQL

String param = "2000";

        Query query = em.createQuery(
             "select count(e) from Employee e where e.sal = :empSal");
        query.setParameter("empSal", param);
        Long count1 = (Long)query.getSingleResult();

        System.out.println("count : "+count1);

Equivalent with Criteria API

String param = "2000";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Long> query = cb.createQuery(Long.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(cb.count(emp));
        query.where(cb.equal(emp.<String>get("sal"),
                             cb.parameter(String.class,"empSal")));

        TypedQuery<Long> tq = em.createQuery(query);
        tq.setParameter("empSal", param);

        Long count = tq.getSingleResult();

        System.out.println("count : "+count);

Example of criteria using distinct
In JPQL

String param = "dev%";

        Query query = em.createQuery(
             "select distinct e.disignation from Employee e where e.designation like :param");
        query.setParameter("param", param);
        List<String> desg= query.getResultList();

        for (String desgs : desg){
            System.out.println(desgs);
        }

Equivalent with Criteria API

String param = "dev%";

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<String> query = cb.createQuery(String.class);

        Root<Employee> emp = query.from(Employee.class);
        query.select(emp.<String>get("designation"));
        query.distinct(true);

        query.where(cb.like(music.<String>get("artisteName"),
                            cb.parameter(String.class, "param")));

        TypedQuery<String> tq = em.createQuery(query);
        tq.setParameter("param", param);

        List<String> designations = tq.getResultList();


        for (String desgs : designations){
            System.out.println(desgs);
        }

Happy Coding :)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s