EDUCBA

Home Software Development Software Development Tutorials Java Technology Tutorial JPQL

JPQL

Princy Victor
Article byPrincy Victor
Priya Pedamkar
Reviewed byRavi Rathore

Updated June 29, 2023

JPQL

 

 

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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 e

Since 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/10

Here, 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 e

In 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.lastName

Example #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 < 65000

Example #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 ) > 1

Methods

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 –

  1. Timer in Java
  2. StringBuffer Class in Java
  3. Java Transient
  4. Inner Class in Java
quiz