JPA JPQL

JPQL (Java Persistence Query Language) is a powerful query language in JPA that allows you to write queries using the entity model rather than the database tables. Below are examples of both typed and untyped JPQL queries.

Example 1: Fetch all employees

String jpql = "SELECT e FROM Employee e";
Query query = entityManager.createQuery(jpql);
List employees = query.getResultList();

• Explanation:
  • This query selects all Employee entities from the database.
  • The result is returned as a raw List because the query is untyped.

Example 2: Fetch all employees (Typed)

String jpql = "SELECT e FROM Employee e";
TypedQuery<Employee> query = entityManager.createQuery(jpql, Employee.class);
List<Employee> employees = query.getResultList();

• Explanation:
  • Similar to the untyped query, but the TypedQuery interface is used to specify the type of the result (Employee).
  • This provides type safety, ensuring that the result list contains only Employee objects.

Example 3: Fetch employees by department (Untyped)

String jpql = "SELECT e FROM Employee e WHERE e.department.name = :deptName";
Query query = entityManager.createQuery(jpql);
query.setParameter("deptName", "Sales");
List employees = query.getResultList();

• Explanation:
  • This query selects all Employee entities that belong to a department with the name “Sales”.
  • The parameter :deptName is set using setParameter.

Example 4: Fetch employees by department (Typed)

String jpql = "SELECT e FROM Employee e WHERE e.department.name = :deptName";
TypedQuery<Employee> query = entityManager.createQuery(jpql, Employee.class);
query.setParameter("deptName", "Sales");
List<Employee> employees = query.getResultList();

• Explanation:
  • The same query as above but using a TypedQuery for type safety.
  • The result is a list of Employee objects.

Example 5: Count the number of employees

String jpql = "SELECT COUNT(e) FROM Employee e";
Query query = entityManager.createQuery(jpql);
Long count = (Long) query.getSingleResult();

• Explanation:
  • This query counts the number of Employee entities in the database.
  • The result is cast to Long because the query returns a single result.

Example 6: Calculate average salary (Typed)

String jpql = "SELECT AVG(e.salary) FROM Employee e";
TypedQuery<Double> query = entityManager.createQuery(jpql, Double.class);
Double averageSalary = query.getSingleResult();

• Explanation:
  • This query calculates the average salary of all Employee entities.
  • The result is a single Double value.

Example 7: Fetch employees and their departments (Untyped)

String jpql = "SELECT e.name, d.name FROM Employee e JOIN e.department d";
Query query = entityManager.createQuery(jpql);
List<Object[]> results = query.getResultList();
for (Object[] result : results) {
    String employeeName = (String) result[0];
    String departmentName = (String) result[1];
    // Process results
}

• Explanation:
  • This query joins Employee entities with their corresponding Department entities and selects the names of both.
  • The result is a list of Object[], where each element in the array corresponds to the selected fields.

Example 8: Fetch employees and their departments (Typed)

String jpql = "SELECT new com.example.EmployeeDepartmentDTO(e.name, d.name) FROM Employee e JOIN e.department d";
TypedQuery<EmployeeDepartmentDTO> query = entityManager.createQuery(jpql, EmployeeDepartmentDTO.class);
List<EmployeeDepartmentDTO> results = query.getResultList();

• Explanation:
  • This query also joins Employee entities with their Department entities but uses a constructor expression to map the result to a EmployeeDepartmentDTO class.
  • The result is a list of EmployeeDepartmentDTO objects, providing a more structured and typed result.

Example 9: Fetch employees by named query (Untyped)

@NamedQuery(name = "Employee.findByName", query = "SELECT e FROM Employee e WHERE e.name = :name")

// Later in code
Query query = entityManager.createNamedQuery("Employee.findByName");
query.setParameter("name", "Alice");
List employees = query.getResultList();

• Explanation:
  • The @NamedQuery annotation defines a named query at the entity level.
  • The query is executed using createNamedQuery.

Example 10: Fetch employees by named query (Typed)

@NamedQuery(name = "Employee.findByName", query = "SELECT e FROM Employee e WHERE e.name = :name")

// Later in code
TypedQuery<Employee> query = entityManager.createNamedQuery("Employee.findByName", Employee.class);
query.setParameter("name", "Alice");
List<Employee> employees = query.getResultList();

• Explanation:
  • Similar to the untyped named query, but using a TypedQuery to ensure the result is a list of Employee objects.

• Typed Queries provide type safety and help avoid casting errors. They are generally preferred when you know the type of the results.
• Untyped Queries are more flexible but require casting the results to the appropriate type, which can lead to runtime errors if not handled carefully.

JPQL is a powerful way to interact with the database using a more object-oriented approach, and these examples cover some common use cases you might encounter.

For more advanced JPQL features and query patterns, refer to the JPQL documentation.

In JPQL, a constructor expression using the new keyword allows you to create and return objects of a specific class directly from the query results. This is particularly useful when you want to retrieve a subset of data from an entity and encapsulate it in a Data Transfer Object (DTO) or a custom class.

Let’s say we have an Employee entity, and we want to retrieve a list of employees’ names and their departments’ names. Instead of returning the entire Employee and Department objects, we can use a constructor expression to return a list of EmployeeDepartmentDTO objects.

First, create a DTO class that matches the fields you want to return from the query.

public class EmployeeDepartmentDTO {

    private String employeeName;
    private String departmentName;

    // Constructor
    public EmployeeDepartmentDTO(String employeeName, String departmentName) {
        this.employeeName = employeeName;
        this.departmentName = departmentName;
    }

    // Getters and setters (optional)
    public String getEmployeeName() {
        return employeeName;
    }

    public String getDepartmentName() {
        return departmentName;
    }

    // toString method for easy display
    @Override
    public String toString() {
        return "EmployeeDepartmentDTO{" +
               "employeeName='" + employeeName + '\'' +
               ", departmentName='" + departmentName + '\'' +
               '}';
    }
}

Now, write a JPQL query using the new keyword to create instances of EmployeeDepartmentDTO from the query results.

String jpql = "SELECT new com.example.EmployeeDepartmentDTO(e.name, d.name) " +
              "FROM Employee e " +
              "JOIN e.department d";

TypedQuery<EmployeeDepartmentDTO> query = entityManager.createQuery(jpql, EmployeeDepartmentDTO.class);
List<EmployeeDepartmentDTO> results = query.getResultList();

Finally, process or display the results.

for (EmployeeDepartmentDTO dto : results) {
    System.out.println(dto);
}

• JPQL Query:

  • The new keyword is used in the JPQL query to indicate that a new object of the specified class (EmployeeDepartmentDTO) should be created for each row returned by the query.
  • The query selects the employee’s name (e.name) and the department’s name (d.name) and passes them as arguments to the EmployeeDepartmentDTO constructor.

• Typed Query:

  • We use TypedQuery<EmployeeDepartmentDTO> to ensure that the query returns a list of EmployeeDepartmentDTO objects.

• Result Processing:

  • The result is a list of EmployeeDepartmentDTO objects, which can be iterated over and processed as needed.

If there are employees in the database with their respective departments, the output might look like this:

EmployeeDepartmentDTO{employeeName='Alice', departmentName='IT'}
EmployeeDepartmentDTO{employeeName='Bob', departmentName='HR'}
EmployeeDepartmentDTO{employeeName='Charlie', departmentName='Finance'}

• Constructor expressions using new in JPQL are useful for returning non-entity objects like DTOs directly from queries.
• This approach reduces the amount of data retrieved from the database to only what is needed and allows for better encapsulation and separation of concerns in your application code.