JPQL
Samuel Vijay
Agenda
Introduction
JPQL vs SQL
JPQL Functions
JPQL Statements
Clauses
JPQL 2
Introduction
• The Java Persistence API specifies a query language that allow to define
queries over entities and their persistent state.
• JPQL is an extension of EJB QL.
• More robust flexible and object-oriented than SQL.
• The persistence engine parse the query string, transform the JPQL to the
native SQL before executing it.
JPQL 3
JPQL vs SQL
JPQL SQL
It is operates on classes and objects SQL operates on table properties,
available in Java workspace such as rows and columns in the
database space
Using JPQL the entity fields are Using SQL the columns of a table
selected are selected
JPQL operates on Java classes and SQL operates on table properties
objects
JPQL statements can be executed There is no need of such
after they coverted into SQL conversions
JPQL 4
Conversion of JPQL Query into a simple executable SQL query
JPQL
Query
JPQL
Query
Processor
SQL
Query
JPQL 5
JPQL Functions
JPQL provides some built-in functions that are used by JPQL statements.
It can be used with WHERE or HAVING clause.
Functions available in JPQL are:
▪ String functions
▪ Arithmetic functions
▪ Temporal functions
JPQL 6
String Functions
Used in SELECT clause in JPQL.
Filters the results returned by the query.
Performs string manipulations.
Following functions are available in JPQL
CONCAT(), SUBSTRING(), TRIM(), LOWER(), UPPER(), LENGTH(), LOCATE()
JPQL 7
❖ CONCAT() – Used to add two strings.
CONCAT(String, String)
❖ SUBSTRING() – Returns a substring of a specified length from parent string.
SUBSTRING(String, Position,
Length)
❖ TRIM() – Trims the whitespace in the string.
TRIM([[LEADING|TRAILING|BOTH] char) FROM ] (String)
❖ LOWER() – Returns string in lowercase
LOWER(String)
JPQL 8
❖ UPPER() – Returns the value in upper case.
UPPER(String)
❖ LENGTH() – Returns the length of the given string.
LENGTH(String)
❖ LOCATE() – Searches the position of one string within another.
LOCATE(searchString, stringToBeSearched
[initialPosition])
JPQL 9
Arithmetic Functions
Used to manipulate data for generating analysis reports.
Used in WHERE or HAVING clause in JPQL.
Following functions are available in JPQL
ABS(), SQRT(), MOD(), SIZE()
JPQL 10
❖ ABS() – Returns the absolute value of the expression.
ABS(number)
❖ SQRT() – Returns the square root of the expression.
SQRT(double)
❖ MOD() – Returns the modulus of the operation.
MOD(int, int)
❖ SIZE() – Returns the number of items in a collection.
SIZE(collection)
JPQL 11
Temporal Functions
JPQL provides certain time–related functions, known as temporal functions.
It is used to obtain the current time, date, or timestamp.
These functions are translated into database-specific SQL functions and the
requested values are retrieved from the database.
Following are the temporal functions:
• CURRENT_TIME() – Returns the current time.
• CURRENT_DATE() – Returns the current date.
• CURRENT_TIMESTAMP() – Returns the current timestamp.
JPQL 12
JPQL Statements
JPQL includes SQL statements to represent the query language.
Three types of statements are available in JPQL.
• SELECT Statement
• UPDATE Statement
• DELETE Statement
JPQL 13
SELECT Statement
The SELECT statement is used to access entity-related data from the java
workspace.
An entity is defined as collection of data, it can be also referred as class or
objects.
Syntax for SELECT
select_clause statement
from_clause is given below:
[where_clause] [groupby_clause] [having_clause]
[orderby_clause]
Code snippet
SELECT for SELECT
e FROM Employeestatement:
e WHERE e.department =: department
JPQL 14
UPDATE Statement
The UPDATE statement is used to update the records of a table.
The UPDATE statement in JQPL is same as SQL.
Syntax for UPDATE statement is given below:
UPDATE entityName indentifierVariable SET single_value_path_expression1=
value1,….
Code snippet for UPDATE statement:
UPDATE Employee e SET e.salary =:newSalary WHERE e.department =:
department
JPQL 15
DELETE Statement
The DELETE statement is used to delete records of a table.
Here, only one entity can be deleted by using the DELETE statement. The
WHERE clause restricts the number of entities that are affected by DELETE
Syntax for DELETE statement is given below:
DELETE entityName identifierVariable WHERE where_clause
Code snippet for UPDATE statement:
DELETE FROM Employee e WHERE e.department =: department
JPQL 16
CLAUSES
A clause refers to specific parts of a JPQL query that serve various functions
There are several key clauses in JPQL like SELECT, FROM, WHERE, HAVING,
ORDER BY.
❑ SELECT Clause: SELECT clause is used to retrieve the result of a query.
SELECT e.empName1, e.empName2 FROM Employee e
The SELECT clause can also the aggregate functions. Some of the aggregate
functions are
AVG(), COUNT(), MAX(),MIN(), SUM().
JPQL 17
❑ FROM Clause: The FROM clause specifies the entity or entities from which you
want to retrieve data. It defines the source of the data for your query.
SELECT e FROM Employee e
❑ WHERE Clause: The output of the SELECT has no limits. So we use WHERE
clause to filter or limit the result of the SELECT clause
SELECT e FROM Employee e WHERE e.empID >
100
❑ ORDER BY Clause: The ORDER BY clause is used to order the values of the data
and objects retrieved by the SELECT clause.
SELECT e FROM Employee e ORDER BY
e.lastName ASC
JPQL 18
❑ HAVING Clause: The HAVING clause is used in conjunction with the GROUP BY
clause to filter grouped data based on aggregate function.
SELECT e.department,AVG(e.salary) FROM Employee e GROUP BY
e.department HAVING AVG(e.salary) > 60000
❑ GROUP BY Clause: The GROUP BY clause is uded to group query results based
on one or more attributes.It use some aggregate functiond like COUNT,SUM, AVG,
etc.
SELECT e.department, COUNT(e) FROM Employee e GROUP BY e.department
JPQL 19
Thank you
