JPQL

Introduction to JPQL

In order to perform several database operations especially on persistent entities, an object-oriented query language known as JPQL (Java Persistence Query Language) is used in JPA specification. As a substitute for a database table, JPQL operates the Structured Query Language queries by using the entity object model. 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 this is established based on SQL syntax, the database won’t be affected directly.

Why do we need JPQL?

JPQL is used by developers for its wide features and advantages. It helps in creating queries against entities that are stored in a relational database. Moreover, a JPQL query can get and return objects more than field values from tables. This is one of the main reasons where JPQL is considered as object-oriented friendly as well as easier to use.

How JPQL works?

The key difference between JPQL and SQL is that 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……

These two clauses are compulsory in every query that is used for retrieving information. In some cases, certain optional clauses are also included. 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 the programming languages. Each identification variable denotes iteration over database objects. Here, e is a range variable. That is, it describes iteration over every employee objects 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 the salary of employees by 10%. Moreover, the UPDATE clause describes only one range variable for iteration. However, multiple variables, as well as JOIN, are not supported in this. 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 a case insensitive. That is, keywords such as SELECT, UPDATE, etc. can be written either 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 fields names etc. In addition to that, string literals are also considered as case sensitive (“ABC” and “abc” are different values).

Examples on 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 that have 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 of JPQL

JPQL offers two methods that are helpful in accessing database records, They are:

• Query createQuery(String n): This method is used for query interface instance creation that is used for JPQL statement execution.
• Query createNamedQuery(String n): This method is used for query interface instance creation that is used for named queries execution.

In addition to that, Query execution can be controlled by the below interface methods.

• int executeUpdate(): Update and delete operations will be executed by using this method.
• int getFirstResult(): The first result that the query retrieves will be returned on calling this method.
• int getMaxResults(): The maximum count of results that the query retrieves will be returned on calling this method.
• Query setFirstResult(int startPosition): The position of the first result that the query retrieves will be assigned to calling this method.
• Query setMaxResults(int maxResult): The maximum count of results that the query retrieves will be assigned on calling this method.

Advantages

The following are the main advantages of JPQL

• Simple
• Robust
• JPQL is able to use different types of databases like MySQL, Oracle, etc.
• In code, JPQL queries can be dynamically built.
• JPQL can be declared statically into metadata.

JPQL Features

JPQL is considered as an extension of EJB QL ( ) adding the below main features such as: –

• JPQL is able to carry out joint operations.
• It can update as well as delete data in a huge size.
• JPQL can undergo aggregate function with sorting as well as grouping clauses.
• JPQL is object-oriented
• The result type of single value and multiple values.
• JPQL is a platform-independent query language.

Conclusion

JPQL is used to perform several database operations, especially on persistent entities. This document clearly explains each and every aspect of JPQL in an efficient manner.

Recommended Articles

This is a guide to JPQL. Here we discuss how Entity JavaBeans Query Language works, along with some features, methods and different 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