Java Persistence Query Language (JPQL) tutorial

As a Java developer working with databases, you know how important it is, to effectively interact with your data.
That’s where Java(also knows as Jakarta) Persistence Query Language (JPQL) comes in – a powerful query language that allows you to define searches against your database.
In this tutorial, you’ll learn the basics of JPQL, including its syntax, structure, and features.
You’ll also learn how to write simple and complex queries, navigate relationships between entities, and use conditional expressions to filter your results.
By the end of this tutorial, you’ll be able to write efficient JPQL queries that help you get the most out of your database.

What is Java Persistence?

Before we start learning about JPQL, let’s start with the basics.
Java Persistence is a way to store and retrieve data in a database using Java. Think of it like a bridge between your Java application and a database.
You know how you can store data in variables and objects in your Java program?
Persistence is about storing that data in a more permanent place, like a database, so it can be retrieved and used later.

What is ORM?

ORM is a technique that helps connect your Java objects (like classes and instances) to tables in a database.
This way, you can work with Java objects in your code, and the ORM tool takes care of storing and retrieving the data from the database.
Popular ORM tools for Java include Hibernate and EclipseLink.

What are Entities?

In Java Persistence, an entity is a special kind of Java class that represents a table in a database.
Think of an entity as a blueprint for a table.
You define an entity class in your Java code, and the ORM tool uses it to create a corresponding table in the database.
For example, if you have a User entity class, the ORM tool might create a USERS table in the database with columns like ID, NAME, and EMAIL.
Entities are the core of Java Persistence, and they help you work with data in a more object-oriented way.

What is JPQL?

Now, finally, let’s come to Java Persistence Query Language (JPQL).
JPQL is a query language that allows you to retrieve and manipulate data in a database using Java Persistence.
It’s similar to SQL, but it’s specifically designed for working with entities and Java objects.
With JPQL, you can write queries that navigate through entities(java classes) and their relationships, making it easier to fetch and update data in the database.
JPQL is a part of the Java Persistence API (JPA) specification, which provides a standard way of accessing and managing data in a database.

Why do we need JPQL?

So, why do we need a special query language like JPQL? Can’t we just use SQL?
Well, SQL is great for working directly with databases, but it’s not ideal for working with Java objects and entities.
JPQL provides a more object-oriented way of querying data, which makes it easier to work with entities and their relationships.
Additionally, JPQL is database-agnostic, meaning you can write queries that work with different databases, like MySQL, Oracle, or PostgreSQL, without worrying about the underlying database syntax.

Basic Concepts and Syntax

There are some basic elements that make up a JPQL query.
These elements include the SELECT clause, which specifies the data you want to retrieve;
the FROM clause, which defines the entity or entities you’re querying;
and the WHERE clause, which filters the results based on conditions you specify.

Another critical aspect of JPQL queries is the ability to use various clauses to refine your results.
For example, you can use the GROUP BY clause to group results based on specific criteria, or the HAVING clause to filter groups based on conditions.
Additionally, the ORDER BY clause allows you to sort your results in a specific order.

Examples of JPQL Queries

Consider the following query

SELECT e FROM Employee e WHERE e.salary > 50000

This query retrieves all employees with a salary greater than 50,000.
Note that the Employee in this query is an entity class rather than a database table name in SQL.

In this example, the SELECT clause specifies that we want to retrieve employees, the FROM clause defines the Employee entity, and the WHERE clause filters the results based on the salary condition.

Using Entity Identifiers

In JPQL (Java Persistence Query Language), entity identifiers (also known as primary keys) are used for uniquely identifying each entity instance in a database.
Entity identifiers are essential for operations like fetching, updating, or deleting specific records.

They are usually mapped to a field in a Java class that has @Id annotation as shown below

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;

@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Integer id;

}

In this example:

• The @Id annotation specifies the id field as the primary key.
• The @GeneratedValue annotation with GenerationType.IDENTITY indicates that the identifier value is generated by the database.

If you have an Order entity with an @Id annotation on the id field, you can use the id identifier to query orders with a specific ID

SELECT o FROM Order o WHERE o.id = 1

Path Expressions

Suppose you have an Order entity having reference to a customer entity, which in turn has fields such as name, address etc.

Below are the two example entities

public class Order {

  String orderId;
  Customer customer;
}

public class Customer {

  String name;
  String address;
}

Let’s say you want to know the customer name from an order id.
For that you need to navigate from Order entity to Customer entity.

This is where path expressions are used.
A path expression is a way to navigate through an entity’s attributes and relationships to access specific data.
You can think of it as a dot notation, where you specify the entity’s attribute or relationship followed by a dot and then the next attribute or relationship, and so on.

A JPQL query to get the customer name from order id would be

SELECT o.customer.name FROM Order o WHERE o.orderId = '1234'

Similarly, if Order has a collection of items, where each item is of type OrderItem class, you can navigate to OrderItem using a path expression like order.orderItems.

Conditional Expressions

Conditional expressions enable you to filter data based on specific conditions, making your queries more targeted and efficient.

In JPQL, you can use these expressions to filter data in the WHERE clause.
Conditional expressions are important in JPQL queries, as they allow you to specify conditions that must be met for the data to be retrieved.

Using Logical Operators (e.g., AND, OR, NOT)

JPQL supports three logical operators: AND, OR, and NOT.

For instance, you can use the AND operator to retrieve data that meets multiple conditions. For example:

SELECT e FROM Employee e WHERE e.salary > 50000 AND e.department = 'Sales'

This query retrieves all employees with a salary greater than 50,000 and who work in the Sales department.

Using Comparison Operators (e.g., =, <, >, <=, >=)

For comparing values, JPQL provides several comparison operators.
These operators allow you to specify conditions based on equality, inequality, and ranges.

Comparison Operators in JPQL

Operator	Description
=	Equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to

With these operators, you can construct conditional expressions that filter data based on specific conditions. For example:

SELECT e FROM Employee e WHERE e.age >= 25 AND e.age <= 35

This query retrieves all employees whose age is between 25 and 35 (inclusive).

Aggregate Functions

For any data analysis, you need to perform calculations on groups of data.
Aggregate functions in JPQL enable you to do just that.
These functions allow you to perform operations like averaging, summing, and counting on a set of data.

For instance, you might want to calculate the average salary of all employees in a department.

Below would be the JPQL query that uses AVG() aggregate function

SELECT AVG(e.salary) FROM Employee e WHERE e.department = 'Sales'`

This query calculates the average salary of all employees in the Sales department.
You can use other aggregate functions like SUM, MAX and MIN to perform different calculations.

Subqueries in JPQL

JPQL allows you to use subqueries to nest one query within another. This enables you to perform complex queries that involve multiple operations.

For instance, you might want to find the maximum salary of employees in each department.

SELECT e.department, MAX(e.salary) 
       FROM Employee e 
       WHERE e.salary > (SELECT AVG(salary) 
       FROM Employee) 
       GROUP BY e.department`

This query finds the maximum salary of employees in each department, but only includes departments where the average salary is higher than the overall average salary.

Summing up

In this comprehensive JPQL tutorial, we covered the basics of Java Persistence Query Language, from its syntax and structure to advanced concepts like entity references, path expressions, and conditional expressions.
You’ve learned how to write simple and complex JPQL queries, using clauses like SELECT, FROM, WHERE, and ORDER BY.
You’ve also seen examples of using entity identifiers, navigation in your queries.
With this knowledge, you’re ready to take your Java development skills to the next level by effectively querying and managing your database using JPQL.