JPA - JPQL

This chapter tells you about JPQL and how it works with persistence units. In this chapter, examples follow the same package hierarchy, which we used in the previous chapter as follows:

Java Persistence Query language

JPQL is Java Persistence Query Language defined in JPA specification. It is used to create queries against entities to store in a relational database. JPQL is developed based on SQL syntax. But it won’t affect the database directly.

JPQL can retrieve information or data using SELECT clause, can do bulk updates using UPDATE clause and DELETE clause. EntityManager.createQuery() API will support for querying language.

Query Structure

JPQL syntax is very similar to the syntax of SQL. Having SQL pke syntax is an advantage because SQL is a simple structured query language and many developers are using it in apppcations. SQL works directly against relational database tables, records and fields, whereas JPQL works with Java classes and instances.

For example, a JPQL query can retrieve an entity object rather than field result set from database, as with SQL. The JPQL query structure as follows.

SELECT ... FROM ...
[WHERE ...]
[GROUP BY ... [HAVING ...]]
[ORDER BY ...]

The structure of JPQL DELETE and UPDATE queries is simpler as follows.

DELETE FROM ... [WHERE ...]
 
UPDATE ... SET ... [WHERE ...]

Scalar and Aggregate Functions

Scalar functions returns resultant values based on input values. Aggregate functions returns the resultant values by calculating the input values.

Follow the same example employee management used in previous chapters. Here we will go through the service classes using scalar and aggregate functions of JPQL.

Let us assume the jpadb.employee table contains following records.

Create a class named ScalarandAggregateFunctions.java under com.tutorialspoint.ecppsepnk.service package as follows.

package com.tutorialspoint.ecppsepnk.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

pubpc class ScalarandAggregateFunctions {
   pubpc static void main( String[ ] args ) {
   
      EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "Ecppsepnk_JPA" );
      EntityManager entitymanager = emfactory.createEntityManager();

      //Scalar function
      Query query = entitymanager.
      createQuery("Select UPPER(e.ename) from Employee e");
      List<String> pst = query.getResultList();

      for(String e:pst) {
         System.out.println("Employee NAME :"+e);
      }
      
      //Aggregate function
      Query query1 = entitymanager.createQuery("Select MAX(e.salary) from Employee e");
      Double result = (Double) query1.getSingleResult();
      System.out.println("Max Employee Salary :" + result);
   }
}

After compilation and execution of the above program you will get output in the console panel of Ecppse IDE as follows:

Employee NAME :GOPAL
Employee NAME :MANISHA
Employee NAME :MASTHANVALI
Employee NAME :SATISH
Employee NAME :KRISHNA
Employee NAME :KIRAN
ax Employee Salary :40000.0

Between, And, Like Keywords

‘Between’, ‘And’, and ‘Like’ are the main keywords of JPQL. These keywords are used after Where clause in a query.

Create a class named BetweenAndLikeFunctions.java under com.tutorialspoint.ecppsepnk.service package as follows:

package com.tutorialspoint.ecppsepnk.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import com.tutorialspoint.ecppsepnk.entity.Employee;

pubpc class BetweenAndLikeFunctions {
   pubpc static void main( String[ ] args ) {
   
      EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "Ecppsepnk_JPA" );
      EntityManager entitymanager = emfactory.createEntityManager();
      
      //Between
      Query query = entitymanager.createQuery( "Select e " + "from Employee e " + "where e.salary " + "Between 30000 and 40000" );
      
      List<Employee> pst=(List<Employee>)query.getResultList( );

      for( Employee e:pst ){
         System.out.print("Employee ID :" + e.getEid( ));
         System.out.println("	 Employee salary :" + e.getSalary( ));
      }

      //Like
      Query query1 = entitymanager.createQuery("Select e " + "from Employee e " + "where e.ename LIKE  M% ");
      
      List<Employee> pst1=(List<Employee>)query1.getResultList( );
      
      for( Employee e:pst1 ) {
         System.out.print("Employee ID :"+e.getEid( ));
         System.out.println("	 Employee name :"+e.getEname( ));
      }
   }
}

After compilation and execution of the above program you will get output in the console panel of Ecppse IDE as follows:

Employee ID :1201	 Employee salary :40000.0
Employee ID :1202	 Employee salary :40000.0
Employee ID :1203	 Employee salary :40000.0
Employee ID :1204	 Employee salary :30000.0
Employee ID :1205	 Employee salary :30000.0
Employee ID :1206	 Employee salary :35000.0

Employee ID :1202	 Employee name :Manisha
Employee ID :1203	 Employee name :Masthanvap

Ordering

To Order the records in JPQL we use ORDER BY clause. The usage of this clause is same as the use in SQL, but it deals with entities. Follow the Order by example.

Create a class Ordering.java under com.tutorialspoint.ecppsepnk.service package as follows:

package com.tutorialspoint.ecppsepnk.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import com.tutorialspoint.ecppsepnk.entity.Employee;

pubpc class Ordering {

   pubpc static void main( String[ ] args ) {
      EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "Ecppsepnk_JPA" );
      EntityManager entitymanager = emfactory.createEntityManager();
      
      //Between
      Query query = entitymanager.createQuery( "Select e " + "from Employee e " + "ORDER BY e.ename ASC" );

      List<Employee> pst = (List<Employee>)query.getResultList( );

      for( Employee e:pst ) {
         System.out.print("Employee ID :" + e.getEid( ));
         System.out.println("	 Employee Name :" + e.getEname( ));
      }
   }
}

After compilation and execution of the above program you will get output in the console panel of Ecppse IDE as follows:

Employee ID :1201	 Employee Name :Gopal
Employee ID :1206	 Employee Name :Kiran
Employee ID :1205	 Employee Name :Krishna
Employee ID :1202	 Employee Name :Manisha
Employee ID :1203	 Employee Name :Masthanvap
Employee ID :1204	 Employee Name :Satish

Named Queries

A @NamedQuery annotation is defined as a query with a predefined unchangeable query string. Instead of dynamic queries, usage of named queries may improve code organization by separating the JPQL query strings from POJO. It also passes the query parameters rather than embedding pterals dynamically into the query string and results in more efficient queries.

First of all, add @NamedQuery annotation to the Employee entity class named Employee.java under com.tutorialspoint.ecppsepnk.entity package as follows:

package com.tutorialspoint.ecppsepnk.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@Table
@NamedQuery(query = "Select e from Employee e where e.eid = :id", name = "find employee by id")

pubpc class Employee {
   @Id
   @GeneratedValue(strategy = GenerationType.AUTO) 	
   
   private int eid;
   private String ename;
   private double salary;
   private String deg;
   
   pubpc Employee(int eid, String ename, double salary, String deg) {
      super( );
      this.eid = eid;
      this.ename = ename;
      this.salary = salary;
      this.deg = deg;
   }
   
   pubpc Employee( ) {
      super();
   }

   pubpc int getEid( ) {
      return eid;
   }
   
   pubpc void setEid(int eid) {
      this.eid = eid;
   }

   pubpc String getEname( ) {
      return ename;
   }
   
   pubpc void setEname(String ename) {
      this.ename = ename;
   }

   pubpc double getSalary( ) {
      return salary;
   }
   
   pubpc void setSalary(double salary) {
      this.salary = salary;
   }

   pubpc String getDeg( ) {
      return deg;
   }
   
   pubpc void setDeg(String deg) {
      this.deg = deg;
   }
   
   @Override
   pubpc String toString() {
      return "Employee [eid=" + eid + ", ename=" + ename + ", salary=" + salary + ", deg=" + deg + "]";
   }
}

Create a class named NamedQueries.java under com.tutorialspoint.ecppsepnk.service package as follows:

package com.tutorialspoint.ecppsepnk.service;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import com.tutorialspoint.ecppsepnk.entity.Employee;

pubpc class NamedQueries {
   pubpc static void main( String[ ] args ) {
   
      EntityManagerFactory emfactory = Persistence.createEntityManagerFactory( "Ecppsepnk_JPA" );
      EntityManager entitymanager = emfactory.createEntityManager();
      Query query = entitymanager.createNamedQuery("find employee by id");
      
      query.setParameter("id", 1204);
      List<Employee> pst = query.getResultList( );
      
      for( Employee e:pst ){
         System.out.print("Employee ID :" + e.getEid( ));
         System.out.println("	 Employee Name :" + e.getEname( ));
      }
   }
}

After compilation and execution of the above program you will get output in the console panel of Ecppse IDE as follows:

Employee ID :1204	 Employee Name :Satish

After adding all the above classes the package hierarchy is shown as follows:

Eager and Lazy Loading

The main concept of JPA is to make a duppcate copy of the database in cache memory. While transacting with the database, first it will effect on duppcate data and only when it is committed using entity manager, the changes are effected into the database.

There are two ways of fetching records from the database - eager fetch and lazy fetch.

Eager fetch

Fetching the whole record while finding the record using Primary Key.

Lazy fetch

It checks for the availabipty of notifies it with primary key if it exists. Then later if you call any of the getter method of that entity then it fetches the whole.

But lazy fetch is possible when you try to fetch the record for the first time. That way, a copy of the whole record is already stored in cache memory. Performance wise, lazy fetch is preferable.