Updated June 29, 2023
Introduction to JPQL
An object-oriented query language, JPQL (Java Persistence Query Language), is used in JPA specification to perform several database operations, especially on persistent entities. JPQL operates the Structured Query Language queries using the entity object model as a substitute for a database table. The main aim of JPA is to convert Java Persistence Query Language into Structured Query Language. Thus, it offers developers an easy platform to manage SQL tasks. Even though SQL syntax serves as the foundation, this won’t directly affect the database.
Why do we need JPQL?
Developers use JPQL for its wide features and advantages. It helps create queries against entities stored in a relational database. Moreover, a JPQL query can get and return objects more than table field values. This is one of the main reasons why JPQL is considered object-oriented friendly and easier to use.
How JPQL works?
The key difference between JPQL and SQL is that the former works with classes and objects of Java, whereas the latter works with relational database fields, records, and tables.
There are several clauses used by JPQL similar to SQL; they are:
- SELECT: Retrieve data or information.
- UPDATE: Perform updation of data.
- DELETE: Delete data.
1. SELECT
Similar to SQL, the SELECT query in JPQL is also in the below format:
SELECT…..
FROM……Every query used for retrieving information must include these two mandatory clauses. In some cases, developers may include specific optional clauses as well. It will be in the format as shown below.
Code:
SELECT…..
FROM……
[WHERE...]
[GROUP BY ... [HAVING ...]]
[ORDER BY...]Since these clauses are optional, they are mentioned in the square brackets.
Example:
The below query retrieves all the employee objects from the database:
SELECT e FROM Employee AS eSince SELECT and FROM are compulsory, this shows a minimal query. The FROM clause states one or more identification variables or query variables. It is analogous to loop variables present in programming languages. Each identification variable denotes iteration over database objects. Here, e is a range variable. That is, it describes iteration over every employee object in the DB.
2. UPDATE
Syntax:
UPDATE. . .
SET. . .
[ WHERE. . .]Code:
UPDATE employee SET salary=salary*12/10
UPDATE employee e SET e.salary=e.salary*12/10
UPDATE employee AS e SET e.salary=e.salary*12/10Here, the 3 equivalent queries increase employees’ salaries by 10%. Moreover, the UPDATE clause describes only one range variable for iteration. However, this method does not support multiple variables or JOIN operations. The SET clause describes one or more number of field update expressions.
3. DELETE
Syntax:
DELETE
FROM . . .
[ WHERE. . . ]Code:
DELETE From Employee
DELETE From Employee e
DELETE From Employee AS eIn addition to that, JPQL is considered case insensitive. That is, keywords such as SELECT, UPDATE, etc. can be written in upper case (SELECT, UPDATE) or lower case (select, update). However, in some cases, it is case-sensitive. It can be entity class names and persistent field names etc. In addition to that, string literals are also considered case-sensitive (“ABC” and “abc” are different values).
Examples of JPQL Query
Below are the examples mentioned:
Example #1
Query to retrieve all employees ordered alphabetically
Code:
SELECT e
FROM employee e
ORDER BY e.firstName, e.lastNameExample #2
Query to retrieve the employees that are in the ABC department.
Code:
SELECT e
FROM employee e
WHERE department='ABC'Example #3
Query to delete employees with a salary smaller than 65000.
Code:
DELETE FROM
Employee e
WHERE e.salary < 65000Example #4
Query to group employees by their name’s first letter.
Code:
SELECT SUBSTRING( e.name , 1 , 1 )
FROM employee e
GROUP BY
SUBSTRING( e.name , 1 , 1 )Example #5
Query to find the sum and average salary of employees in the ABC department.
Code:
SELECT SUM(e.salary), AVG(e.salary)
FROM employee e
WHERE department='ABC'Example #6
Query to count all subjects of an employee and returns only the employees with more than one subject.
Code:
SELECT e
FROM Employee e
WHERE (SELECT count(s) FROM Subject s WHERE e MEMBER OF s.subjects ) > 1Methods
It offers two methods that help access database records; they are:
- Query createQuery(String n): This method is used for query interface instance creation for JPQL statement execution.
- Query createNamedQuery(String n): Developers use this method to create a query interface instance for executing named queries.
In addition to that, Query execution can be controlled by the below interface methods.
- int executeUpdate(): You can execute update and delete operations using this method.
- int getFirstResult(): Calling this method will return the first result that the query retrieves.
- int getMaxResults(): When you execute this method, it will retrieve the highest number of results the query can retrieve.
- Query setFirstResult(int startPosition): Calling this method will assign the position of the first result that the query retrieves.
- Query setMaxResults(int maxResult): Calling this method will assign the maximum count of results that the query retrieves.
Advantages
The following are the main advantages of JPQL
- Simple
- Robust
- JPQL can use different types of databases like MySQL, Oracle, etc.
- In code, developers can dynamically build JPQL queries.
- Developers can declare JPQL statically into metadata.
JPQL Features
JPQL is considered an extension of EJB QL ( ), adding the below main features such as: –
- It can carry out joint operations.
- It can update as well as delete data in a huge size.
- It can undergo aggregate function with sorting as well as grouping clauses.
- It is object-oriented
- The result type of single value and multiple values.
- JPQL is a platform-independent query language.
Conclusion
Developers use JPQL to perform several database operations, particularly on persistent entities. This document clearly explains each and every aspect of JPQL efficiently.
Recommended Articles
This is a guide to JPQL. Here we discuss how Entity JavaBeans Query Language works, along with some features, methods, and examples. You can also go through our other related articles to learn more –
