In this post we will talk about different ways to write custom 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. However, in some of the case these queries might not be enough and we might need to write custom query.
In this post, we will look into how to use @Query annotation to execute custom queries for both SQL and JPQL queries.
@Query annotation
The method is annotated with @Query annotation and the queries is passed as a value to be executed. @Query annotation gives full flexibility to execute query as required without relying of naming convention of method.
JPQL
Also know as Java Persistence query language. It is more preferred as it allows you to define query based on JPA entity and it is database agnostic. The query is parse by Spring Data JPA before it submits it to database. There is some drawback for using JPQL query. It only supports a subset of SQL standard and not a fit for complex query.
You might also want to read:
public interface PostRepository extends JpaRepository<Post, Integer> {
@Query("FROM Post WHERE name = ?1")
List<Post> findByName(String name);
@Query("SELECT a FROM Post a WHERE Name = ?1 AND Author = ?2")
List<Author> findByNameAndAuthor(String name, String author);
}
With JPQL query you can write query treating table columns as attributes of the class. So, basically the query seems like it is executing on the Java persistent class.
Sorting by using JPQL Query
There are two ways to achieve this. One is by using ORDER BY Clause and the next by Sort in the method argument. With Sort object in the method, hibernate will add ORDER BY clause in the query.
public interface PostRepository extends JpaRepository<Post, Integer> {
@Query("FROM Post WHERE Name = ?1 ORDER BY Author ASC")
List<Post> findByNameOrderByAuthor(String name);
}
Native Queries
We can write native queries inside @Query annotation as well. The query is executed by Hibernate or EclipseLink as for JPQL.The query is kind of similar to JPQL queries, however, we should tell Spring JPA that we want the query to be executed as native query and that is done by setting the nativeQuery property as true.
The native query is not parsed by Spring JPA but is rather sent to database directly. So, the query is written as if the query is executed directly on database.
@Query(value = "SELECT * FROM post", nativeQuery = true)
List<Post> findAllPost();
@Query(value = "SELECT * FROM post WHERE name = :name", nativeQuery = true)
List<Post> findCustomPostsByName(@Param("name") String name);
Spring Data JPA replaces the value of each positional parameter with the value passed as argument in the methods. This is Named Parameters representation of queries. Named parameter starts with :name and the value is defined inside method argument
Setup Spring Boot JPA Project
Let’s use Spring Starter tool to generate a project with one click. You can add the dependencies 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.fullstack.coder</groupId>
<artifactId>custom-query</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>custom-query</name>
<description>Demo project for Spring Boot Custome 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>mysql</groupId>
<artifactId>mysql-connector-java</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>
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 will be using SQL as data source.
Configure SQL
long with it, we also need to update application.properties file.
spring.datasource.url= jdbc:mysql://localhost:3306/your_database_name?useSSL=false
spring.datasource.username= your_username
spring.datasource.password= your_password
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto= update
Define Data Model class for Spring JPA
Since our main objective is to understand Custom Query, we will be defining Post class inside package named model.
Post
package com.fullstack.coder.custom.query.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 Custom Query
Inside the repository package, let’s create one interface that extends JpaRepository; PostRepository inside repository package.
PostRepository
package com.fullstack.coder.custom.query.repository;
import com.fullstack.coder.custom.query.model.Post;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface PostRepository extends JpaRepository<Post, Integer> {
@Query("FROM Post WHERE name = ?1")
List<Post> findByPostName(String name);
@Query("SELECT a FROM Post a WHERE name = ?1 AND author = ?2")
List<Post> findByPostNameAndAuthor(String name, String author);
@Query("FROM Post WHERE name = ?1 ORDER BY name ASC")
List<Post> findByPostNameOrderByName(String name);
@Query("SELECT p FROM Post p WHERE p.name LIKE %?1%")
List<Post> findByNameLike(String name);
@Query(value = "SELECT * FROM post", nativeQuery = true)
List<Post> findAllPost();
@Query(value = "SELECT * FROM post WHERE name = :name", nativeQuery = true)
List<Post> findCustomPostsByName(@Param("name") String name);
}
Define Rest Controller for Custom Query
package com.fullstack.coder.custom.query.controller;
import com.fullstack.coder.custom.query.model.Post;
import com.fullstack.coder.custom.query.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> getCustomQueryPost() {
postRepository.findByPostName("Post1").
forEach(p -> System.out.println(p.toString()));
System.out.println();
postRepository.findByPostNameAndAuthor("Post1","author1")
.forEach(p -> System.out.println(p.toString()));
System.out.println();
postRepository.findByPostNameOrderByName("Post1").
forEach(p -> System.out.println(p.toString()));
System.out.println();
postRepository.findCustomPostsByName("Post1").
forEach(p -> System.out.println(p.toString()));
System.out.println();
postRepository.findAllPost().
forEach(p -> System.out.println(p.toString()));
System.out.println();
postRepository.findByNameLike("Post1").
forEach(p -> System.out.println(p.toString()));
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 write our own custom query to connect to database instead of out of box queries.