Derived Query Methods with Spring Data JPA

In this post we will talk about different ways to write derived query in Spring Data JPA.

Spring JPA Overview

We know Spring Data JPA brings CRUD implementation out of the box and along with Spring JPA provides you some easy method to write the query based on method name. It provides some Generic Crud operations like save(), findById(), deleteById(), findAll() and so on. There are many such method which comes out of the box and can use without even implementing.

Some simple use-cases can be written with derived query methods by simply defining the method name of the entity class inside the repository interface.

Structure of Derived Query Methods in Spring Data JPA

Derived Query method name has mainly two parts. The first part known as introducer and the second as criteria.

Introducer: Spring JPA supports clause like find, query, read, count and get based on which Spring Data JPA knows what action to take. There are certain expression that too can be used like Distinct to truncate our result set.

Criteria: Spring JPA supports clause like By keyword and is followed by the entity attributes to query on. Different conditions like AND, OR, IS, ISNOT, ISNULL and many others can also be applied.

You might also want to read:

You just need to follow conventions while writing derived methods. As long as your query method name starts with find…By, read…By, query…By, count…By, or get…By and follows the right criteria pattern, Spring Data translates it to create the required JPQL query.

Some of the examples are as follows:

# find posts by name
List<Post> findByName(String name);
# find posts by name or author
List<Post> findByNameOrAuthor(String name, String author);
# find posts by name and author
List<Post> findByNameAndAuthor(String name, String author);

Name or Author in the above example is attribute of the Java persistent class.

Using OR or AND use case in derived query

    List<Post> findByNameOrAuthor(String name, String author);
    List<Post> findByNameAndAuthor(String name, String author);

OR/AND allows to perform query with multiple arguments or attributes.

Null check use case in derived query

    List<Post> findByNameIsNull();
    List<Post> findByNameIsNotNull();

This query checks for null as value in name attribute. Similarly NotNull will retrieve result which are not null.

Is, IsNot, Equals and Not use case in derived query

These are conditional query to check for the exact name or not. By the way Is and Equals has the exact result.

    List<Post> findByNameIs(String name);
    List<Post> findByNameEquals(String name);

    List<Post> findByNameIsNot(String name);
    List<Post> findByNameNot(String name);

OrderBy, StartsWith, Containing use case in derived query

    List<Post> findByNameStartingWith(String prefix);
    List<Post> findByNameStartsWith(String prefix);

    List<Post> findByNameContaining(String infix);

    List<Post> findByNameOrderByAuthor(String name);
    List<Post> findByNameOrderByAuthorAsc(String name);

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Setup Spring Boot JPA Project

Let’s use Spring Starter tool to generate a project with one click. You can add the three dependency we need while generating the project: Spring Web, Spring Data JPA.

Once you download the project, your pom should look like this:

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.7</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.rtech</groupId>
	<artifactId>derivedquery</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>derivedquery</name>
	<description>Demo project for Spring Data JPA Derived Query</description>
	<properties>
		<java.version>11</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>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>

Configure Spring Boot JPA

We need one more dependency for datasource. As i have mentioned above, you can use any datasource you like and for this part we need to add one more dependency depending on the datasource.

Configure H2

To add H2 dependency in pom.xml.

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

long with it, we also need to update application.properties file.

spring.datasource.url=jdbc:h2:mem:your_database_name //i will be using jpadb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto= update
spring.h2.console.enabled=true
# h2-console is default path for H2

spring.datasource.url is used to define database url. “jdbc:h2:mem:[your_database_name]” is how you define the url for H2

spring.datasource.username and spring.datasource.password are the credentials to connect to database.

spring.jpa.hibernate.ddl-auto is used to initialize database. It will create database tables automatically based on our defined model. You can also set this property to following values: none, validate, update, create-drop. For production, it is better to use validate as property.

spring.h2.console.enabled=true will tell Spring to start H2 Database administration tool. We can access this on http://localhost:8080/h2-console

spring.jpa.show-sql=true is to log database transaction on the console.

spring.jpa.properties.hibernate.dialect is to configure Hibernate for JPA implementation. It’s different for MySQL and PostgreSQL.

Define Data Model class for Spring JPA

Since our main objective is to understand Derived query, we will be defining Post class inside package named model.

Post

package com.rtech.derivedquery.model;

import javax.persistence.*;

@Entity
@Table(name = "post")
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Column(name = "name")
    private String name;
    @Column(name = "description")
    private String description;
    @Column(name = "author")
    private String author;


    public Post() {
    }

    public Post(String name, String description, String author) {
        this.name = name;
        this.description = description;
        this.author = author;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String toString(){//overriding the toString() method
        return id+" "+name+" "+description+ " "+ author;
    }
    
}

@Entity

This annotation tells Spring JPA that the class is a persistent Java class and the table has to be mapped on to the database.

@Column

Table is supposed to have different columns and this column is mapped to attributes name in Java persistent class. @Column annotation tells Spring JPA that the attribute is a column of the associated table. With name property column name can be given too.

@Table

The annotation maps the class name to a table name in datasource. “name” property defines table name.

@Id

A table needs to have a unique key as an attribute based on which you can perform query for uniqueness. @Id annotation tells that the column is a primary key. The primary key can be generated by different ways. @GeneratedValue defines how the primary key will be generated.

Create Repository Interface for Spring JPA Derived Query

Inside the repository package, let’s create one interface that extends JpaRepository; PostRepository inside repository package.

PostRepository

package com.rtech.derivedquery.repository;


import com.rtech.derivedquery.model.Post;
import org.springframework.data.jpa.repository.JpaRepository;

import javax.transaction.Transactional;
import java.util.List;

public interface PostRepository extends JpaRepository<Post, Integer> {

    List<Post> findByName(String name);
    List<Post> findDistinctByName(String name);
    List<Post> findByNameOrAuthor(String name, String author);
    List<Post> findByNameAndAuthor(String name, String author);


    List<Post> findByNameIs(String name);
    // OR
    List<Post> findByNameEquals(String name);

    List<Post> findByNameIsNot(String name);
    // OR
    List<Post> findByNameNot(String name);


    List<Post> findByNameIsNull();
    List<Post> findByNameIsNotNull();


    List<Post> findByNameStartingWith(String prefix);
    List<Post> findByNameStartsWith(String prefix);


    List<Post> findByNameContaining(String infix);

    List<Post> findByNameOrderByAuthor(String name);
    List<Post> findByNameOrderByAuthorAsc(String name);

    List<Post> findByNameOrderByAuthorDesc(String name);

    @Transactional
    void deleteByName(String name);

}

Define Rest Controller for Derived Query

package com.rtech.derivedquery.controller;


import com.rtech.derivedquery.model.Post;
import com.rtech.derivedquery.repository.PostRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.Optional;

@RestController
public class PostController {

    @Autowired
    PostRepository postRepository;


    @GetMapping("/post/{id}")
    public ResponseEntity<Post> getPostById(@PathVariable("id") int id) {
        Optional<Post> post = postRepository.findById(id);
        if(post.isEmpty()) {
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
        return new ResponseEntity<>(post.get(), HttpStatus.OK);
    }

    @PostMapping("/post")
    public ResponseEntity<Post> createPost(@RequestBody Post post) {
        Post resPost = postRepository.save(new Post(post.getName(), post.getDescription(), post.getAuthor()));
        return new ResponseEntity<>(resPost, HttpStatus.CREATED);
    }

    @GetMapping("/post/derived")
    public ResponseEntity<HttpStatus> getDerivedPost() {
        postRepository.findByName("Post1").forEach(p->System.out.println(p.toString()));
        postRepository.findByNameIs("Post1").forEach(p->System.out.println(p.toString()));
        postRepository.findByNameEquals("Post1").forEach(p->System.out.println(p.toString()));

        postRepository.findByNameIsNot("Post1").forEach(p->System.out.println(p.toString()));
        postRepository.findByNameNot("Post1").forEach(p->System.out.println(p.toString()));

        postRepository.findByNameOrAuthor("","author1").forEach(p->System.out.println(p.toString()));
        postRepository.findByNameAndAuthor("Post","author1").forEach(p->System.out.println(p.toString()));

        postRepository.findByNameIsNull().forEach(p->System.out.println(p.toString()));
        postRepository.findByNameIsNotNull().forEach(p->System.out.println(p.toString()));

        postRepository.findByNameStartingWith("P").forEach(p->System.out.println(p.toString()));
        postRepository.findByNameStartsWith("Po").forEach(p->System.out.println(p.toString()));

        postRepository.findByNameContaining("s").forEach(p->System.out.println(p.toString()));

        postRepository.findByNameOrderByAuthor("Post1").forEach(p->System.out.println(p.toString()));

        postRepository.deleteByName("Post1");
        return new ResponseEntity<>(HttpStatus.OK);
    }

    @DeleteMapping("/post/{id}")
    public ResponseEntity<HttpStatus> deletePostById(@PathVariable("id") int id) {
        postRepository.deleteById(id);
        return new ResponseEntity(HttpStatus.NO_CONTENT);
    }

}

Conclusion

Spring JPA is amazing as it provides many out of box functionalities. In this post we saw how we can simply use attribute name of class to generate a query method.