Spring Boot Data JPA @Query tutorial

Spring Boot Data JPA @Query tutorial shows how to create custom queries with Data JPA @Query.

Spring is a popular Java application framework for creating enterprise applications. Spring Boot is an evolution of Spring framework which helps create stand-alone, production-grade Spring based applications with minimal effort.

Spring Data JPA

Spring Data JPA facilitates the implementation of JPA based repositories. It enhances support for JPA based data access layers. It makes it easier to build Spring-powered applications that use data access technologies. Spring Data JPA is a part of the larger Spring Data family.

Spring Data JPA @Query

The @Query annotation declares finder queries directly on repository methods. While similar @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.

Spring Boot Data JPA @Query example

The following application is a simple Spring Boot web application, which uses Data JPA @Query to create a custom query.

$ tree
.
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── zetcode
    │   │           ├── Application.java
    │   │           ├── bean
    │   │           │   └── City.java
    │   │           ├── controller
    │   │           │   └── MyController.java
    │   │           ├── repository
    │   │           │   └── CityRepository.java
    │   │           └── service
    │   │               ├── CityService.java
    │   │               └── ICityService.java
    │   └── resources
    │       ├── application.properties
    │       ├── data-h2.sql
    │       ├── schema-h2.sql
    │       ├── static
    │       │   └── index.html
    │       └── templates
    │           └── showCities.ftl
    └── test
        └── java

This is the project structure.

pom.xml
<?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>com.zetcode</groupId>
    <artifactId>SpringBootCustomDataJpaQuery</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.10.RELEASE</version>
    </parent>
    
    <dependencies>
        
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
       
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

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

    </dependencies>     
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin> 
        </plugins>
    </build>
    
</project>

The Maven POM file contains dependencies for H2 database, Freemarker template engine, and Spring Boot Data JPA.

application.properties
server.port=8086
server.contextPath=/myapp

spring.main.banner-mode=off
spring.datasource.platform=h2
logging.level.org.hibernate.SQL=DEBUG
spring.jpa.show-sql=true 
spring.jpa.hibernate.ddl-auto=none

In the application.properties file we write various configuration settings of a Spring Boot application. The server.port sets the server port and the server.contextPath sets the context path (application name). After these settings, we access the application at localhost:8086/myapp/. With the spring.main.banner-mode property we turn off the Spring banner.

The spring.datasource.platform sets the vendor name of the database. It is used in the initialization scripts. The spring.jpa.show-sql enables logging of SQL statements. Finally, the spring.jpa.hibernate.ddl-auto disables the automatic creation of schemas from entities.

City.java
package com.zetcode.bean;

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "cities")
public class City {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;
    private int population;

    public City() {
    }

    public City(String name, int population) {
        this.name = name;
        this.population = population;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 79 * hash + Objects.hashCode(this.id);
        hash = 79 * hash + Objects.hashCode(this.name);
        hash = 79 * hash + this.population;
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final City other = (City) obj;
        if (this.population != other.population) {
            return false;
        }
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return Objects.equals(this.id, other.id);
    }
    
    @Override
    public String toString() {
        
        StringBuilder builder = new StringBuilder();
        builder.append("City{id=").append(id).append(", name=")
                .append(name).append(", population=")
                .append(population).append("}");
        
        return builder.toString();
    }
}

This is the City entity. Each entity must have at least two annotations defined: @Entity and @Id.

@Entity
@Table(name = "CITIES")
public class City {

The @Entity annotation specifies that the class is an entity and is mapped to a database table. The @Table annotation specifies the name of the database table to be used for mapping.

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

The @Id annotation specifies the primary key of an entity and the @GeneratedValue provides for the specification of generation strategies for the values of primary keys.

schema-h2.sql
CREATE TABLE cities(id INT PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(100), population INT);

When the application is started, the schema-h2.sql script is executed. It creates a new database table.

data-h2.sql
INSERT INTO cities(name, population) VALUES('Bratislava', 432000);
INSERT INTO cities(name, population) VALUES('Budapest', 1759000);
INSERT INTO cities(name, population) VALUES('Prague', 1280000);
INSERT INTO cities(name, population) VALUES('Warsaw', 1748000);
INSERT INTO cities(name, population) VALUES('Los Angeles', 3971000);
INSERT INTO cities(name, population) VALUES('New York', 8550000);
INSERT INTO cities(name, population) VALUES('Edinburgh', 464000);
INSERT INTO cities(name, population) VALUES('Suzhou', 4327066);
INSERT INTO cities(name, population) VALUES('Zhengzhou', 4122087);
INSERT INTO cities(name, population) VALUES('Berlin', 3671000);

Later, the data-h2.sql file is executed. It fills the table with data.

CityRepository.java
package com.zetcode.repository;

import com.zetcode.bean.City;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CityRepository extends CrudRepository<City, Long> {

    @Query("select c from City c where c.name like %?1")
    List<City> findByNameEndsWith(String chars);
}

By extending from the Spring CrudRepository, we will have some methods for our data repository implemented. In addition, we create a custom findByNameEndsWith() method.

@Query("select c from City c where c.name like %?1")
List<City> findByNameEndsWith(String chars);

The @Query annotation contains the custom JPQL querty. It returns cities whose names end in the provided characters.

ICityService.java
package com.zetcode.service;

import com.zetcode.bean.City;
import java.util.List;

public interface ICityService {

    public List<City> findByNameEndsWith(String name);
}

ICityService provides a contract method to get all cities ending in certain characters.

CityService.java
package com.zetcode.service;

import com.zetcode.bean.City;
import com.zetcode.repository.CityRepository;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class CityService implements ICityService {

    @Autowired
    private CityRepository repository;

    @Override
    public List<City> findByNameEndsWith(String name) {

        List<City> cities = (List<City>) repository.findByNameEndsWith(name);
        return cities;
    }
}

CityService contains the implementation of the findByNameEndsWith() method. We use the repository to retrieve data from the database.

@Autowired
private CityRepository repository;

CityRepository is injected.

List<City> cities = (List<City>) repository.findByNameEndsWith(name);

The findByNameEndsWith() method of the repository returns the list of cities ending in certain characters.

MyController.java
package com.zetcode.controller;

import com.zetcode.bean.City;
import com.zetcode.service.ICityService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;

@Controller
public class MyController {
    
    @Autowired
    ICityService cityService;
    
    @GetMapping("/showCitiesEnding")
    public String findCitiesNameEndsWith(Model model, @RequestParam String name) {
        
        List<City> cities = (List<City>) cityService.findByNameEndsWith(name);
        
        model.addAttribute("cities", cities);
        
        return "showCities";
    }
}

The MyController class is annotated with @Controller.

@Autowired
private ICityService cityService;

We inject a ICityService into the countryService field.

@GetMapping("/showCitiesEnding")
public String findCitiesNameEndsWith(Model model, @RequestParam String name) {
    
    List<City> cities = (List<City>) cityService.findByNameEndsWith(name);
    
    model.addAttribute("cities", cities);
    
    return "showCities";
}

We map a request with the showCitiesEnding path to the controller's findCitiesNameEndsWith() method. The model gains a list of matching cities and the processing is sent to the showCities.ftl Freemarker template file.

showCities.ftl
<!DOCTYPE html>
<html>
    <head>
        <title>Cities</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        </head>
    <body>
        <h2>List of cities</h2>
        
        <table>
            <tr>
                <th>Id</th>  
                <th>Name</th>  
                <th>Population</th>
            </tr>        

            <#list cities as city>
                <tr>
                    <td>${city.id}</td> 
                    <td>${city.name}</td> 
                    <td>${city.population}</td>
                </tr>
            </#list>        
        </table>                
    </body>
</html>

In the showCities.ftl template file, we display the data in an HTML table.

index.html
<!DOCTYPE html>
<html>
    <head>
        <title>Home page</title>
        <meta charset="UTF-8"/>
        <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
    </head>
    <body>
        <a href="showCitiesEnding?name=ou">Show cities ending in ou</a>
    </body>
</html>

In the index.html there is a link to show cities ending in ou characters.

Application.java
package com.zetcode;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

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

The Application sets up the Spring Boot application. The @SpringBootApplication enables auto-configuration and component scanning.

$ mvn spring-boot:run 

After the application is run, we can navigate to localhost:8086/myapp/.

Result of Spring Data JPA @Query
Figure: Result of Spring Data JPA @Query

In this tutorial, we have showed how to use Spring Data JPA @Query annotation to create a custom JPQL query. You might also be interested in the related tutorials: