HQL/JPQL in Spring Data JPA

HQL/JPQL in Spring Data JPA | Repository interfaces have provided pre-defined methods to do database operations like findAll(), save(), deleteById(), etc. We can also define our query using the concept of “Custom Query”. This can be implemented using:-

1. @Query: It can be used for both SELECT and non-SELECT (update/delete) operations.
2. findBy: It can be used for only SELECT operations, but not for non-select operations.

Here we will see defining custom query using @Query. Also see:- Custom query method (findBy).

The HQL/JPQL [Hibernate Query Language/JPA Query Language] concept is used to define custom queries that is database independent. We write HQL/JPQL, dialect converts it into SQL queries. SQL queries are database-dependent. So, recomanded to use HQL/JPQL. Even @Query supports Pure SQL queries/Native SQL.

1. SQL: Here we use table name and column names.
2. HQL/JPQL: Here we use class name and variable name.

Therefore to convert SQL query to HQL/JPQL query, replace tableName with className, and columnName with variableName. Examples:-

SQL: select eid, ename from emptab where esal > ?
HQL/JPQL: select empId, empName from Employee where empSal > ?0

1. SQL is case-insensitive but HQL/JPQL is partially case-sensitive. In HQL/JPQL, Java words are case-sensitive (class, variable, package) whereas SQL words are case-insensitive (select, where, from, etc).
2. Do not write the * symbol in HQL/JPQL because HQL/JPQL is Java and * indicates multiply, not all columns. Instead, use alias naming.

SQL: select * from emptab;
HQL/JPQL: select e from Employee e;

3. In HQL/JPQL, while using Model className, the package name is optional.

SQL: select eid, ename from emptab where esal>?

HQL/JPQL:
select empId, empName from com.knowprogram.demo.entity.Employee where empSal > ?0
select empId, empName from Employee where empSal > ?0

4. Parameters are allowed in HQL/JPQL, but we can also use ?0, ?1, ?2 in place of simple “?” symbols.
5. It also supports named parameters. Syntax- :name
6. HQL/JPQL also supports non-select operations like UPDATE and DELETE.

In application.properties file:-

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
   @Id
   private Integer empId;
   private String empName;
   private Double empSal;
}

public interface EmployeeRepository extends JpaRepository<Employee, Integer> { }

@Component
public class EmployeeInsertRunner implements CommandLineRunner {

   @Autowired
   private EmployeeRepository employeeRepository;

   @Override
   public void run(String... args) throws Exception {
      employeeRepository.saveAll(
               List.of(new Employee(10, "A", 2.2), 
                  new Employee(11, "B", 3.2), 
                  new Employee(12, "C", 4.2)
               )
            );
   }
}

Run the application, it will create the table and insert some data.

For Custom Query follow the below steps:-

1. Add one abstract method in the Repository Interface.
2. Provide @Query(“HQL/JPQL”) over the abstract method.
3. Call this method in the Runner class for testing.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

   @Query("SELECT e FROM Employee e")
   List<Employee> getAllEmps();

   @Query("SELECT e.empName FROM Employee e")
   List<String> getAllEmpNames();

   @Query("SELECT e.empId, e.empName FROM Employee e")
   List<Object[]> getAllEmpIdAndNames();

}

@Component
public class EmployeeInsertRunner implements CommandLineRunner {

   @Autowired
   private EmployeeRepository employeeRepository;

   @Override
   public void run(String... args) throws Exception {
      List<Employee> employees = employeeRepository.getAllEmps();
      employees.forEach(System.out::println);

      List<String> empNames = employeeRepository.getAllEmpNames();
      empNames.forEach(System.out::println);

      List<Object[]> list = employeeRepository.getAllEmpIdAndNames();
      list.stream().map(obj -> obj[0] + " - " + obj[1]).forEach(System.out::println);

      Iterator<Object[]> itr = list.iterator();
      while (itr.hasNext()) {
         Object[] object = itr.next();
         System.out.println(object[0] + " - " + object[1]);
      }

      for (Object[] object : list) {
         System.out.println(object[0] + " - " + object[1]);
      }
   }

}

In Select query:-

1. All columns => List<T>
2. One Column => List<Data Type>
3. Multiple Column => List<Object[]>

Passing Parameters to Query

We can pass parameters either through positional parameters or named parameters.

1. Positional parameters denoted as ?position. Position number starts from one(1). In old versions (Spring Data JPA 2.x) it used to start from zero.

... where column operator ?1 .. column operator ?2 .. column operator ?3

2. Named parameters denoted as :name.

• If our query returns single row data then List is not required we can use return type as T, data type, Object[ ], but we should use Object (later cast to Object[ ]).
• In the case of positional parameters, ‘param name‘ can be any name but data type and position order must match.

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

   // using parameters
   @Query("SELECT e FROM Employee e WhERE e.empSal < ?1")
   List<Employee> getAllEmpsBySal(Double empSal);

   // List not required if query returns one row data
   @Query("SELECT e FROM Employee e WHERE e.empId = ?1")
   Employee getOneEmpsById(Integer empId);

   // we can use Object in place of Object[] for return type, later down cast
   @Query("SELECT e.empName, e.empSal FROM Employee e WHERE e.empId = ?1 OR e.empName = ?2")
   Object getOneEmpNameSalByIdOrName(Integer eid, String ename);
   // parameter name can be any name

   @Query("SELECT e FROM Employee e ORDER BY e.empName DESC")
   List<Employee> getAllEmpsSorted();

}

@Component
public class EmployeeInsertRunner implements CommandLineRunner {

   @Autowired
   private EmployeeRepository employeeRepository;

   @Override
   public void run(String... args) throws Exception {
      List<Employee> list = employeeRepository.getAllEmpsBySal(120.0);
      list.forEach(System.out::println);

      Employee emp = employeeRepository.getOneEmpsById(10);
      System.out.println(emp);

      Object employee = employeeRepository.getOneEmpNameSalByIdOrName(10, "A");
      System.out.println(employee);
      Object[] obj = (Object[]) employee;
      System.out.println(obj[0] + " - " + obj[1]);

      employeeRepository.getAllEmpsSorted().forEach(System.out::println);
   }
}

If the Query gets modified with a parameter, then it may affect position numbers in the query and the params order in the method. Example:-

// old query
select * from emptab where esal>?1 and eid<?2
List<T> m1(Double a, Integer b);

// modified query
select * from emptab where esal>?1 or ename=?2 and eid<?3
List<T> m1(Double a, String m, Integer b)

These modifications will create problems, therefore instead of using numbers we can use names i.e. ‘named parameters’. Syntax- :name

// positional parameter
@Query("SELECT e FROM Employee WHERE e.empId = ?1")
List<Employee> m1(Integer id);

// named parameter
@Query("SELECT e FROM Employee WHERE e.empId= :eid")
List<Employee> m1(Integer eid); 
// parameter name and named parameter must match

// positional parameter
@Query("SELECT e FROM Employee WHERE e.empId = ?1 and e.empName = ?2")
List m1(String name, Integer id);

// named parameter
@Query("SELECT e FROM Employee WHERE e.empId = :eid and e.empName = :ename")
List m1(Integer eid, String ename);

While defining the method, we don’t need to follow the order for passing param names but names must match with named params. The below query is also valid.

@Query("SELECT e FROM Employee WHERE e.empId = :eid and e.empName = :ename")
List m1(String ename, Integer eid);

Example in Repository:-

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {

   @Query("SELECT e FROM Employee e WHERE e.empName=:ename or e.empSal>=:esal")
   List<Employee> getEmpsByNamedParam(Double esal, String ename);

   // in operator takes multiple values
   @Query("SELECT e FROM Employee e WHERE e.empId in (:eids)")
   List<Employee> getEmpsBySelectedIds(List<Integer> eids);
}

@Component
public class EmployeeInsertRunner implements CommandLineRunner {

   @Autowired
   private EmployeeRepository employeeRepository;

   @Override
   public void run(String... args) throws Exception {
      employeeRepository.getEmpsByNamedParam(3.2, "A")
         .forEach(System.out::println);

      employeeRepository.getEmpsBySelectedIds(
         List.of(10, 55, 36, 12)
      ).forEach(System.out::println);
   }
}

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search