Spring Data JPA @Query Example - Tutorial

Hello guys, if you are learning Spring Data JPA framework or using Spring Data JPA in your project and are not sure how to use @Query annotation then you have come to the right place. Earlier, I have shared the best Spring Data JPA courses and Spring Data JPA Interview questions and in this article, I am going to show you how to use @Query annotation from Spring Data JPA to create dynamic SQL queries. Spring is a popular Java application framework for creating enterprise applications and Spring Boot is an evolution of the Spring framework which helps to create standalone applications in a minimal effort. In this tutorial, we are going to discuss how to create queries using Spring Data JPA with an example. So let’s have a look with a Spring data JPA @Query example.

By the way, basic knowledge of Spring Data JPA is required, if you don't know Spring Data JPA is a project under the Spring framework, much like Spring Kafka which enhances the support for JPA-based data access layers and by using spring-powered applications much easier to develop.

It's not the replacement of Hibernate or any other ORM tool or JPA implementations like Eclipse TopLink but it provides useful abstraction like Repository to work with JPA easily and create more cleaner code. If used correctly Spring Data JPA can boost developer productivity.   

Spring Data JPA @Query example

Spring JPA @Query allows you to create dynamic queries. So there is no need to write the standard JPA queries and allows more options to query the database. In this example, we are going to create one project including the JPA @Query.

The Spring boot flow architecture is shown in the below picture

Common CRUD methods can easily create Spring JPA queries. But when comes to enterprise applications that are created using the Spring framework, need to execute complex queries against the database. 

 In this type of situation, you need to move with the Spring Data JPA queries with the annotation of @Query. In this post, we will use the student entity and will Lombok to generate code for the Student entity.

In order to store the values, and embedded H2 database was used. In this project, used Spring data JPA, Lombok, H2 dependencies, and spring boot dev tools as shown in the following pom.xml file.

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

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-devtools</artifactId>
  <scope>runtime</scope>
  <optional>true</optional>
</dependency>

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


<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>

Next, we should configure the running port of the application and also the h2 database configuration properties in order to connect to it.

spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:cmpe172
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=username
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

The code of the Student entity is given below. Only three attributes are used here name, id, name, age, and studentAddress.

@Entity(name = "Student")
@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private int id;

  @Column(unique = true)
  private String id;
  private String name;
  private String age;
}

From the above Student entity, @Entity(name = "Student") specifies that the class is an entity mapped to a table named Student. So the H2 database will be able to recognize this and create a Student table.

How to use @Query in Spring Data JPA Application? Example

Here, the repository interface extends the CrudRepository, there are in-built JPA queries in Spring and if you want to query from a table without a scope, then better use the @Query annotation to create the custom query.

public interface StudentRepository extends CrudRepository < Student, Integer > {

  @Query("SELECT s FROM Student s")
  List < Student > findAllStudents();

}

In the above code example, the findAllStudents() method is annotated with the @Query. So this annotation takes a custom query as a string. So within this query, it is responsible to provide all the student's records from the student table.

In order to test the code, using a simple method which is simply can insert some data into the database and get the inserted records back.

@DataJpaTest
class StudentRepositoryTest {
    @Autowired
    private StudentRepository studentRepository;
    private Student st1, st2, st3;
    private List < Student > studentList;
    @BeforeEach
    void setUp() {
        Student st1 = Student.builder()
            .name("Megan Alex")
            .age(15)
            .build();
        Student st2 = Student.builder()
            .name("Emma Watson")
            .age(16)
            .build();
        Student st3 = Student.builder()
            .name("Jessica R.")
            .age(12)
            .build();
        studentRepository.save(st1);
        studentRepository.save(st2);
        studentRepository.save(st3);
    }
    @Test
    void findAllStudents() {
        List < Student > result = studentRepository.findAllStudents();
        assertThat(result, is(notNullValue()));
    }
}

So code in the above used the integration test. The findAllStudents() test method calls the studentRepository.findAllStudents(); query method which is the StudentRepository and gets all the records in the student table.

1. Sorting with @Query - Example

Spring JPA can automatically generate the ORDER_BY using the Sort parameter. This is mostly used on the occasions that you might want to order the query in ascending order or descending order. The code used to perform the sorting is this,

@Query("select s from Student s where s.age = ?15")
List<Student> findStudentByAgeAndSorted(int age, Sort sort);

So in the above code, the Sort parameter is responsible for sort the Students according to Sort properties implemented in the client code.

2. @Query Example with Named Parameters

Spring JPA is responsible for handling named parameters in Native SQL. So in there, can pass parameters to queries and retrieve the data according to it.

@Query(value = "SELECT * FROM Student WHERE age = :age 
                 and name= :name", nativeQuery = true)
Book findStudentByAgeAndStudentNameNative(@Param("age") int age,
                      @Param("name") String name);

3. Native SQL Select @Query with Index Parameters - Example

For this kind of query, you need to have set the native query flag to true. So the code to use native SQL is as below,

@Query(value = "SELECT * FROM Student WHERE age = ?15 
                and name = ? Megan Alex ", nativeQuery = true)
Book findStudentByAgeAndNameNative(String age, String studentName);

When you want to develop Enterprise applications with the spring framework, better to move with Spring native queries.

That's all about how to use @Query annotation in Spring Data JPA. It's one of the most powerful and flexible approaches to accomplish your read operations is with native queries. They let you use all of your database's functionality, and Spring Data JPA takes care of practically all of the boilerplate work. 

But using them takes more effort than a derived query, and they provide a few limitations compared to a custom JPQL query. The most notable ones are:

You must give a count query to use pagination for your query result. You can achieve this by setting the @Query annotation's countQuery attribute.

Native queries do not support dynamic sorting. You must add the ORDER BY clause in your query if you wish to receive your query results in a specified order.

Your native query statement is not adjusted to your database's SQL dialect by Spring Data JPA or your persistence provider. As a result, you must confirm that your SQL statement is supported by all of your supported DBMS.

Other Spring Framework articles you may like to explore 

• 15 Spring Data JPA Interview Questions with Answers
• Top 10 Courses to Learn Microservices with Spring Boot
• 13 Spring Boot Actuator Questions for interviews
• How to limit the number of concurrent active sessions in Java web app
• How to implement LDAP authentication in the Active directory
• Top 15 Microservice Interview Questions with Answers
• 20 Spring Boot Testing Interview Questions with Answers
• 10 Advanced Spring Boot Courses for Experienced Developers
• 15 Spring Cloud Interview Questions with Answers
• How to get ServletContext object in Spring controller
• Difference between @RestController and @Controller in Spring MVC?
• How Spring MVC works internally?
• What is the use of DispatcherServlet in Spring MVC?
• 10 Best Spring Framework Courses for Beginners
• 20+ Spring Boot Interview Questions for Java developers
• How to enable Spring security in a Java web application?
• 17 Spring AOP Interview Questions with Answers

Thanks for reading this article so far. If you find this Spring Data JPA tutorial and @Query example useful, please share them with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P. S. - If you are new to Spring Framework and looking for free online courses to learn Core Spring, Spring MVC, and Spring Boot then you can also checkout this list of free Spring Framework courses for Java developers.