Spring Data JPA - Creating Database Queries using @Query Annotation

The previous part of this tutorial described how we can create database queries from the method names of our query methods. This blog post describes how to create database queries using the @Query annotation.

Well, the method names query generation strategy has the following weaknesses:

  1. The features of the method name parser determine what kind of queries we can create. If the method name parser doesn’t support the required keyword, we cannot use this strategy.
  2. The method names of complex query methods are long and ugly.
  3. There is no support for dynamic queries.
In this post, we can avoid those weaknesses by using the @Query annotation.

Creating Query Methods using @Query Annotation

We can configure the invoked database query by annotating the query method with the @Query annotation. It supports both JPQL and SQL queries, and the query that is specified by using the @Query annotation precedes all other query generation strategies.

Let’s find out how we can create both JPQL and SQL queries with the @Query annotation.

Creating JPQL Queries

We can create a JPQL query with the @Query annotation by following these steps:

Let's look at the below examples that demonstrate the creation of JPQL Queries using @Query annotation.

The following example shows a query created with the @Query annotation:


public interface UserRepository extends JpaRepository {

    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);
}

Using Advanced LIKE Expressions - The query execution mechanism for manually defined queries created with @Query allows the definition of advanced LIKE expressions inside the query definition, as shown in the following example: 


public interface UserRepository extends JpaRepository {

    @Query("select u from User u where u.firstname like %?1")
        List findByFirstnameEndsWith(String firstname);
}

In the preceding example, the LIKE delimiter character (%) is recognized, and the query is transformed into a valid JPQL query (removing the %). Upon query execution, the parameter passed to the method call gets augmented with the previously recognized LIKE pattern.

Creating SQL Queries

The @Query annotation allows for running native queries by setting the nativeQuery flag to true.

Let's follow the below steps to create a SQL query with the @Query annotation:


import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboottestingexamples.model.User;

/**
 * UserRepository demonstrates the method name query generation.
 * 
 * @author Ramesh Fadatare
 *
 */
@Repository
public interface UserRepository extends JpaRepository {

    @Query(value = "select * from users where first_name like %?1", nativeQuery = true)
    List findByFirstnameEndsWith(String firstname);

    @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
    User findByEmailAddress(String emailAddress);
}

Complete Example

Let's develop a complete example to demonstrate the usage of @Query annotation using the Spring Boot application which quickly bootstraps with autoconfiguration.

Tools and Technologies Used

There are many ways to create a Spring Boot application. The simplest way is to use Spring Initializr at http://start.spring.io/, which is an online Spring Boot application generator.

The pom.xml File


<?xml version="1.0" encoding="UTF-8"?>
<project
    xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>net.guides.springboot2</groupId>
    <artifactId>springboot-testing-examples</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>springboot-testing-examples</name>
    <description>Demo project for Spring Boot</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.0.4</version>
        <relativePath/>
        <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

JPA Entity - User.java

In this example, we store User objects, annotated as a JPA entity.


import java.util.Date;

import jakarta.persistence.*;

@Entity
@Table(name = "users")
public class User {
    private long id;
    private String firstname;
    private String lastname;
    private Date startDate;
    private String emailAddress;
    private int age;
    private int active;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getActive() {
        return active;
    }

    public void setActive(int active) {
        this.active = active;
    }

    public String getEmailAddress() {
        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", firstname=" + firstname + ", lastname=" + lastname + ", startDate=" + startDate +
            ", emailAddress=" + emailAddress + ", age=" + age + ", active=" + active + "]";
    }
}

Spring Data JPA Repository - UserRepository.java


import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboottestingexamples.model.User;

/**
 * UserRepository demonstrates the method name query generation.
 * 
 * @author Ramesh Fadatare
 *
 */
@Repository
public interface UserRepository extends JpaRepository < User, Long > {
    @Query("select u from User u where u.emailAddress = ?1")
    User findByEmailAddress(String emailAddress);

    @Query("select u from User u where u.firstname like %?1")
    List < User > findByFirstnameEndsWith(String firstname);
}

Let's create a SQL query with the @Query annotation:


import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import net.guides.springboot2.springboottestingexamples.model.User;

/**
 * UserRepository demonstrates the method name query generation.
 * 
 * @author Ramesh Fadatare
 *
 */
@Repository
public interface UserRepository extends JpaRepository < User, Long > {

    @Query(value = "select * from users where first_name like %?1", nativeQuery = true)
    List < User > findByFirstnameEndsWith(String firstname);

    @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)
    User findByEmailAddress(String emailAddress);
}

Create an Application class

Here you create an Application class with all the components.


package net.guides.springboot2.springboottestingexamples;

import java.util.Date;
import java.util.List;

import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import net.guides.springboot2.springboottestingexamples.model.User;
import net.guides.springboot2.springboottestingexamples.repository.UserRepository;

@SpringBootApplication
public class Application implements CommandLineRunner {

    @Autowired
    private UserRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(String...args) throws Exception {
        User user = new User();
        user.setActive(1);
        user.setAge(28);
        user.setEmailAddress("ramesh24@gmail.com");
        user.setFirstname("Ramesh");
        user.setLastname("Fadatare");
        user.setStartDate(new Date());
        user = userRepository.save(user);

        System.out.println("-------------------------------------:: " + user.getId());

        System.out.println(" ---------------@Query ---------------------");
        System.out.println("--------------findByEmailAddress -----------------");

        User user2 = userRepository.findByEmailAddress("ramesh24@gmail.com");
        System.out.println(user2.toString());

        System.out.println(" ---------------@Query ---------------------");
        System.out.println("--------------findByLastname -----------------");

        List < User > user3 = userRepository.findByFirstnameEndsWith("Ramesh");
        System.out.println(user3.get(0).toString());
    }
}

Note that we have used UserRepository methods to demonstrate @Query annotation.

Running Application

We are using Maven so we can run the application using ./mvnw spring-boot:run. Or you can build the JAR file with ./mvnw clean package. Then you can run the JAR file:

java -jar target/spring-data-jpa-example-0.1.0.jar

Output