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.
Using named queries to declare queries for entities is a valid approach and works fine for a small number of queries. As the queries themselves are tied to the Java method that executes them, you can actually bind them directly by using the Spring Data JPA @Query annotation rather than annotating them to the domain class. This frees the domain class from persistence specific information and co-locates the query to the repository interface.

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:
  1. Add a query method to our repository interface.
  2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation.
Let's look at the below examples demonstrates the creating JPQL Queries using @Query annotation.
The following example shows a query created with the @Query annotation:
public interface UserRepository extends JpaRepository<User, Long> {

  @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<User, Long> {

  @Query("select u from User u where u.firstname like %?1")
  List<User> 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 below steps to create a SQL query with the @Query annotation:
  1. Add a query method to our repository interface.
  2. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation’s value attribute.
  3. Set the value of the @Query annotation’s nativeQuery attribute to true.
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);
}

Complete Example

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

Tools and Technologies Used

  • Spring Boot - 2.0.6.RELEASE
  • JDK - 1.8 or later
  • Spring Framework - 5.0.9 RELEASE
  • Spring Data JPA - 2.0.10 RELEASE
  • Maven - 3.2+
  • IDE - Eclipse or Spring Tool Suite (STS)
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>2.0.5.RELEASE</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>1.8</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.
package net.guides.springboot2.springboottestingexamples.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@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 + "]";
    }
}
  • The User class is annotated with @Entity, indicating that it is a JPA entity.
  • The User’s id property is annotated with @Id so that JPA will recognize it as the object’s ID. The id property is also annotated with @GeneratedValue to indicate that the ID should be generated automatically.

Spring Data JPA Repository - UserRepository.java

package net.guides.springboot2.springboottestingexamples.repository;

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 demonstrates @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


The source code of this article available on my GitHub repository - https://github.com/RameshMF/spring-data-jpa-tutorial

Related Posts


Comments