JPA QL

Importance of Query Language

 

From searching to sorting, analytic  and business intelligence, efficiently moving data from the database to the application and presenting it to the user is a regular part of enterprise development. Doing so requires the ability to issue bulk queries against the database and interpret the results for the application.

Object Query Language

•  Developer want the query results to be converted to entities so that the query results are used directly by application logic.
• Domain model are abstraction of the physical model (tables) in ORM, so Object Query Language like  JPA QL are abstraction over the native SQL

JPA QL – Features

•Single and multiple value result types

•Aggregate functions, with sorting and grouping clauses

•A more natural join syntax, including support for both inner and outer joins

•Conditional expressions involving subqueries

•Update and delete queries for bulk data changes

Result projection into non-persistent classes

JPA QL

•Supports two methods for expressing queries

–Query Language

–Criteria API

And also, we can use native SQL queries

JPA QL in its simplest form

•SQL

–Select EMP_ID, EMP_NAME from EMPLOYEE

•JPA QL

–from Employee

–Select e from Employee e(uses an alias mostly useful in joins)

Select e.empName from Employee e 

Querying Employee Objects

@PersistenceContext

EntityManager em;

List<Employees> findAllEmployees()

{

   Query query = newEm.createQuery("from Employee");

  return query.getResultList();

}

Note: employees is a collection of Employee Entity Objects

Expression in JPA QL

•from Employee emp where emp.empAge between 25 and 30

•from Employee emp where emp.empAge > 26

•from Employee emp where emp.deptName in (‘BIDW', ‘JAVA')

•from Employee e where e.deptName like ‘J%‘

•from Employee e where e.deptName not like ‘J%‘

•from Employee e where ( e.empAge / 20) > 0

•from Employee e

  where (e.empName like 'K%' and e.empId = 109 )  or e.empAge in (25, 30 )

Ordering Query Results

•from User u order by u.username

•from User u order by u.username desc

  Distinct Rows

•select distinct item.description from Item item

Parameter Binding (Prepared Statements)

•Building queries using String concatenation is buggy and difficult to maintain.

–"from Item i where i.description like '" + search + "'";

•A cleaner approach to building queries is to use parameter binding.

•JPA Supports 2 types

–Named Parameter Binding – Preferred Approach

  "from Item item where item.description like :search";

  Query q = em.createQuery(queryString)

  .setParameter("search", searchString);

–Positional Parameter Binding – Not Recommended

   "from Item item where item.description like ?1 and item.date > ?2";

  Query q = em.createQuery(queryString)

  .setParameter(1, searchString)

  .setParameter(2, minDate, TemporalType.DATE);

Pagination using JPA QL

•What is Pagination?

•Approaches

–Fetch complete data from the table and store in session and subsequently retrieve from session. (No query change)

–Fetch data as required from the database. (Query needs to contain the info like number of records to fetch and from which record number for each page).

•JPA QL support

  Query query =  newEm.createQuery("from Employee");

     query.setFirstResult(1);

     query.setMaxResults(5);

     List employees =

            query.getResultList();

Named Queries

•JPA QL queries embedded within Java Code makes the code unreadable and reduces maintainability

•Externalizing queries as metadata using annotations helps resolve the above issues.

•Two Steps

–Externalize the query in your Entity class using @NamedQuery and give it a name

@NamedQueries({

@NamedQuery(

name = "findEmployees",

query = "from Employee")})

–Refer to the query by its name from the Java code.

  Query query =

  em.createNamedQuery(" findEmployees")

Note: Query names are global and hence must be unique.

Aggregate Functions

•count(), min(), max(), sum() and avg()

  select count(i) from Item I

•Using group by

  select u.lastname, count(u) from User u

  group by u.lastname

When aggregate functions are used the query.getResultList() method returns a list of Object arrays with the values in order as specified in the select clause

  Criteria API

•Constructing queries using programming API.

•Simple Example

–SELECT e FROM Employee e WHERE e.name = 'John Smith'

–Equivalent query constructed using the Criteria API

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp) .where(cb.equal(emp.get("name"), "John Smith"));