Spring Data JPA @Query Annotation Example

By Atul Rai | Last Updated: March 27, 2020 Previous       Next

On this page, we’ll learn to write custom queries using Spring Data JPA @Query annotation. @Query annotation supports both JPQL as well as the Native query. The uses of @Query annotation are to execute the complex SQL queries and retrieve the records from the database.

P.S Tested with Spring Boot 2.2.5.RELEASE and MySQL database.

What is JPQL?

JPQL stands for Java Persistence Query Language. JPQL uses the entity object model instead of database tables to define a query. It is a platform-independent object-oriented query language defined as part of the Java Persistence API (JPA) specification.

JPQL is not database specific query. It is the responsibility of the ORM engine to create SQL compatible with the database.

See the below method where we used the JPQL with @Query annotation.

/**
* It return the list of first name of customers
*/
@Query(value = "SELECT c.firstName FROM Customer c")
List<Customer> getCustomerFirstName();

Here, Customer represents the name of entity class and firstName is the member variable of that entity.

What is a Native query?

In simple words, Native query is specific to the database (engine). All the columns and tables name must be the same as in the database. We can also write the database-specific functions or keywords.

/**
* It return the list of first name of all customers also.
*/
@Query(value = "SELECT c.first_name FROM customer c", nativeQuery = true)
List<String> getCustomerFirstNameNative();

Here, customer represents the table name and first_name is the column name of that table.

Dependency Required

The required dependency is spring-boot-starter-data-jpa and others related to the project or application.

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

Entity

Create the entity class along with member variable. In my case Customer is an entity as given below:

package org.websparrow.entity;

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

@Entity
public class Customer {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	private String firstName;
	private String lastName;
	private Long mobile;
	private String country;
	
	// Generate Getters and Setters...
}

1. JPQL

In this section, we’ll see some example of @Query annotation in which we are going to use JPQL to fetch the records from the database. All these methods declared in the CustomerRepository interface.

package org.websparrow.repository;

import org.websparrow.entity.Customer;
@Repository
public interface CustomerRepository extends CrudRepository<Customer, Integer> {

}

1.1 To fetch/retrieve the list of all customers:

@Query(value = "SELECT c FROM Customer c")
List<Customer> getCustomer();

1.2 It returns the list of the first name of customers:

@Query(value = "SELECT c.firstName FROM Customer c")
List<String> getCustomerFirstName();

Remember: Customer represents the name of entity class and firstName is the member variable of that entity.

1.3 It returns the list of the first name of customers whose country is the USA:

@Query(value = "SELECT c.firstName FROM Customer c WHERE c.country='USA'")
List<String> getCustomerFirstNameWhereCountry();

1.4 It returns the list of customers whose country name passed by user:

@Query(value = "SELECT c FROM Customer c WHERE c.country= ?1")
List<Customer> getCustomerWhereCountry(String country);

Alternatively, we can also use the @Param annotation to pass the country value:

@Query(value = "SELECT c FROM Customer c WHERE c.country= :country")
List<Customer> getCustomerWhereCountry1(@Param("country") String country);

1.5 (OR clause) It returns the list of customers whose country or the last name passed by user:

@Query(value = "SELECT c FROM Customer c WHERE c.country= :country OR c.lastName= :lastName")
List<Customer> getCustomerWhereCountryOrLastName(
		@Param("country") String country,
		@Param("lastName") String lastName);

Remember: Customer represents the name of entity class and country and lastName is the member variable of that entity.

Alternate way;

@Query(value = "SELECT c FROM Customer c WHERE c.country= ?1 OR c.lastName= ?2")
List<Customer> getCustomerWhereCountryOrLastName1(String country,
		String lastName);

1.6 Bind directly to DTO–  Yes we can also bind the values directly to DTO returned by the JPQL query. For example, the entity (table) has 5 member variables (columns) and we have a requirement of only 2 member variables (columns), in this case, create the new DTO and bind the result as shown below:

@Query(value = "SELECT new org.websparrow.dto.CustomerCustomData(c.id, c.country)"
		+ " FROM Customer c")
List<CustomerCustomData> getCustomerIdAndCountry();

And the DTO looks like:

Make sure the DTO must have a constructor accepting all internal variables as parameters.

package org.websparrow.dto;

public class CustomerCustomData {

	private Integer id;
	private String country;

	public CustomerCustomData(Integer id, String country) {
		super();
		this.id = id;
		this.country = country;
	}
}

new org.websparrow.dto.CustomerCustomData(c.id, c.country)– This is where the magic happens. The JPQL query creates a new CustomerCustomData by the use of the complete package name and the parameterized constructor created before. The only thing left is to map the necessary Customer variables(c.id, c.country) onto the DTO.

1.7 (JOINs)  In JPQL, we can fetch the data from two or more entities (tables). In shorts, JPQL supports the JOIN clause too. For example, we want only those customer’s records who ordered something.

@Query(value = "SELECT c FROM Customer c INNER JOIN CustomerOrder co ON c.id=co.customerId")
List<Customer> getCustomerWhoOrdered();

Here is the CutomerOrder is an entity that maintains the orders and each order belongs to a particular customer.

package org.websparrow.entity;

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

@Entity
public class CustomerOrder {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	private String name;
	private String description;
	private String orderStatus;
	private Integer customerId;

	// Generate Getters and Setters...
}

Remember: Customer and CustomerOrder both are the names of the entity class.

1.8 (JOIN with WHERE clause) Similarly, we can also add the “WHERE” clause with “JOIN” in JPQL.

@Query(value = "SELECT c FROM Customer c INNER JOIN CustomerOrder co "
		+ " ON c.id=co.customerId WHERE co.orderStatus='Rejected'")
List<Customer> getCustomerWhoOrderedRejected();

1.9 JPQL also support the UPDATE and DELETE in addition to retrieving objects (SELECT queries) with @Query annotation.

/**
* Update customer country where id is something(e.g 9)
 */
@Transactional
@Modifying
@Query(value = "UPDATE Customer c SET c.country='Spain' WHERE c.id= 9")
int updateCustomerCountry();


/**
* Delete customer records whose id is something(e.g 6)
*/
@Transactional
@Modifying
@Query(value = "DELETE FROM Customer c WHERE c.id= 6")
void deleteCustomer();

Why @Transactional and @Modifying?

Whenever you are trying to modify a record in the database, you have to mark it @Transactional as well as @Modifying, which instruct Spring that it can modify existing records.

2. Native Query

@Query annotation also supports the native (database-specific) queries. To make it enable in @Query annotation, we have to set attribute nativeQuery = true, by default it’s false. It will instruct Spring Data JPA that query is specific/compactible to the database used in the application.

In this case, the ORM engine will not convert the query, it directly executes the query. The other advantage of using Native query we can use the database-specific functions, keywords, etc in the query.

For example, our database is MySQL and I want to fetch only 10 records out of 1000 from a table, then the SQL query will be:

SELECT * FROM customer LIMIT 10;

If the database is Oracle then, the query will be I guess:

SELECT * FROM customer ROWNUM <= 3;

Let’s see how it actually looks like.

1.1 To fetch/retrieve the list of all customers:

@Query(value = "SELECT * FROM customer", nativeQuery = true)
List<Customer> getCustomerNative();

1.2 It returns the list of the first name of customers:

@Query(value = "SELECT c.first_name FROM customer c", nativeQuery = true)
List<String> getCustomerFirstNameNative();

Remember: customer represents the name of the table and first_name is the column name of the table.

1.3 It returns the list of the first name of customers whose country is the USA:

@Query(value = "SELECT c.first_name FROM customer c WHERE c.country='USA'", nativeQuery = true)
List<String> getCustomerFirstNameWhereCountryNative();

1.4 It returns the only 10 records of customers:

@Query(value = "SELECT * FROM customer c LIMIT 10", nativeQuery = true)
List<Customer> getCustomerWhereCountryNative();

Similarly, you can try others with the native query.

References

1. Spring Data JPA Derived Query Methods Example
2. Spring Data CrudRepository Interface Example
3. Using @Query- Spring Data JPA
4. Java Persistence Query Language- Wiki