JPQL 

Java Persistence Query Language

Agenda

1. What is JPQL
2. Domain model for demo
3. HQL (Hibernate Query Language) to SQL (Structured Query Language)

   • Simple Select

   • JOIN

   • Collection Path Expression

   • Constructor Expression

   • Aggregate Function

   • NamedQuery

4. JPQL Execution Flow
5. HQL Injection
6. JPA 2.1 Overview

Domain Model

Employee

Department

Project

Login

ReleaseNote

*........1

* ........ *

1 ........ 1

Select - I (Simple Select)

HQL:
SELECT e
FROM Employee e

SQL:
SELECT id, name, salary, dept_id, address_id
FROM emp

 Entity: Employee
  Identification variable : e (*)

public class Employee {

    private long id;
    private String name;
    private Address address;
    private Department department;
    private Double salary;
 
    //constructor, getters, setters
}

#2 Select All Employee attributes

HQL:
SELECT e.name
FROM Employee e

SQL:
SELECT name
FROM emp

#1 Select Particular Attribute Name

Select - II (JOIN)

HQL:
FROM Employee e
JOIN e.department

SQL:
SELECT e.id as empId,
e.address_id,
e.dept_id,
e.name as empName,
e.salary, 
FROM table_employee e INNER JOIN table_department department
 ON (e.dept_id = d.id)

HQL:
FROM Employee e
JOIN FETCH e.department

SQL:
SELECT e.id as empId,
e.address_id,
e.dept_id,
e.name as empName,
e.salary,
d.name as deptName,
d.id as deptId,
FROM table_employee e INNER JOIN table_department department
 ON (e.dept_id = d.id)

#4 Eagerly Load Department with Employee

#3 JOIN Department with Employee

Select - II (JOIN cont...)

HQL:
FROM Employee e 
WHERE e.department IS NOT NULL

SQL:
SELECT e.id, e.address_id, e.dept_id, e.name, e.salary 
FROM table_employee e 
WHERE e.dept_id IS NOT NULL

HQL:
SELECT DISTINCT e.department
FROM Employee e
WHERE e.salary >= :salary

SQL: 
SELECT DISTINCT d.id, d.name 
FROM table_employee e
INNER JOIN table_department d ON (e.dept_id = d.id)
WHERE e.salary >= ?

#5 Employes who have department assigned

#6 Department, Filtered by employee salary (Implicit join)

Select - III (Collection in Path expression)

HQL:
FROM Employee e 
WHERE e.projects IS NOT EMPTY

SQL:
SELECT e.id,  e.address_id, e.dept_id, e.name, e.phone_number, e.salary 
FROM table_employee e 
    WHERE
        EXISTS (
            SELECT p.id 
            FROM
            table_employee_project ep,
            table_project p 
            WHERE
                e.id = ep.employee_id AND ep.project_id = p.id
        )

HQL:
SELECT e FROM Employee e,
IN (e.projects) p
WHERE p.projectType = :type

SQL:
SELECT e.id, e.address_id, e.dept_id, e.name, e.phone_number, e.salary
FROM table_employee e 
INNER JOIN table_employee_project ep (ON e.id = ep.employee_id)
INNER JOIN table_project p ON (ep.project_id = p.id)
WHERE p.project_type=?

#7 Employes who have any project assigned

* Has to be distinct

#8 Employes who work in a particular type of project

Select - III (Collection in Path expression cont..)

HQL:
FROM Employee e 
WHERE SIZE(e.projects) >= :projectCount

SQL:
SELECT e.id, e.address_id, e.dept_id,
e.name, e.phone_number, e.salary 
FROM table_employee e 
WHERE
    (
        SELECT
        COUNT(ep.employee_id) 
        FROM table_employee_project ep 
        WHERE e.id = ep.employee_id
    ) >= ?

#9 SIZE () function

MEMBER OF, NOT MEMBER OF

Select - V (Aggregate Functions)

HQL:
SELECT e.id, e.name, COUNT (p), COUNT(DISTINCT p.projectType)
FROM Employee e
JOIN e.projects p
GROUP BY e.id, e.name

AVG, COUNT, MAX, MIN, and SUM

SQL: 
SELECT e.id, e.name, COUNT(p.id) projects, COUNT(DISTINCT p.project_type) projectTypes 
FROM table_employee e 
INNER JOIN table_employee_project ep ON (e.id = ep.employee_id) 
INNER JOIN table_project p ON (ep.project_id = p.id) 
GROUP BY e.id, e.name

HQL:
SELECT AVG(e.salary) FROM Employee e

SQL:
 SELECT * FROM (
   SELECT AVG(e.salary) FROM table_employee e
  ) 

#10 COUNT, AVG

Select - IV (Constructor Expression)

HQL:
SELECT NEW net.therap.command.EmployeeRowCmd(e.name, e.department.name, e.phoneNumber)
FROM Employee e

public class EmployeeRowCmd implements Serializable {

   private static final long serialVersionUID = 1L;

    private String name;
    private String departmentName;
    private String phoneNumber;

    public EmployeeRowCmd(String name, String departmentName, String phoneNumber) {
        this.name = name;
        this.departmentName = departmentName;
        this.phoneNumber = phoneNumber;
    }

    // getters and setters
}

SQL: 
SELECT e.name empName, d.name as deptName, e.phone_number 
FROM table_employee e, table_department d 
WHERE e.dept_id = d.id

#11 Select Subset

Select - VI (Named Queries)

@Entity
@Table(name = "table_employee")
@NamedQueries({
        @NamedQuery(name = "Employee.findAll",
                query = "SELECT e FROM Employee e"),
        @NamedQuery(name = "Employee.findByName",
                query = "SELECT e FROM Employee e WHERE e.name = :name"),
})
public class Employee implements Serializable {

    @Id
    @GeneratedValue(generator = "tmpEmployeeSeq", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "tmpEmployeeSeq", sequenceName = "tmp_employee_seq", allocationSize = 1)
    private long id;

    private String name;

    .....
    .....
}

• Pre compiled and validated at app start-up time
• Scoped to the persistence unit

• Enforce the best practice of using query parameters 

Query Parsing Abstract Flow

• Generate HQLQueryPlanKey
• Check in QueryPlanCache (MRU cache)
• Transform HQL into the AST using ANTLR (ANother Tool for Language)
• Transform HQL AST to SQL AST
• SQL AST to native query : SqlGenerator
• ParameterParser
  -- ordinalParameterList
  -- namedParameterMap
• Generate Plan - > HQLQueryPlan

Hibernate Versions and Release

3.6.10.Final in Application

4.3.0.Final in Demo

5.2.5.Final Latest (November 2016)

1. Hibernate 6.x
2. Hibernate 7.x (Jandex)

Road Map

HQL Injection

DEMO

JPA 2.1 (Since Hibernate 4.3.0.Final)

• Attribute Converter
• Constructor Result Mappings
• Programmatic Named Queries
• Named Entity Graph

JPA 2.1 : Attribute Converter

Convert between the database and the Java representation

• Entity classes
• Mapped superclasses
• Embeddable classes

Work with attributes of

• Id attributes
• Version
• Relationship attributes  
• Temporal / Enumerated

Doesn't work with - 

DEMO

JPA 2.1 : Constructor Result Mapping

• Specifies the mapping of the result of a native SQL query​

DEMO

JPA 2.1 : Named Query Programmatically

1. Create javax.persistence.Query
2. Unique name within the persistence unit
3. EntityManagerFactoryImpl#addNamedQuery

Steps -

JPA 2.1 : Named Entity Graph

The primary key and version fields always fetched

• Fetch graph ~ specified attributes FetchType.EAGER others lazy.
• Load graph ~  not specified attributes will keep their default fetch type.

DEMO

Thank you