Hello. In this tutorial, we will explore the @Query in a Spring Boot JPA application.
1. Introduction
The @Query annotation in spring boot is applied at the method level in the JpaRepository interfaces and pertains to a single interface method. The annotation helps the developers to write native sql queries. In this example, we will also be Lombok which is a small library that reduces the boilerplate java code from the project.
2. Spring JPA @Query Example
Let us dive into some practice stuff and I am hoping that you are aware of the spring boot basics.
2.1 Tools Used for Spring boot application and Project Structure
We are using Eclipse Kepler SR2, JDK 8, and Maven. In case you’re confused about where you should create the corresponding files or folder, let us review the project structure of the spring boot application.
Let us start building the application!
3. Creating a Spring Boot application
Below are the steps involved in developing the application.
3.1 Maven Dependency
In the pom.xml file we will define the required dependencies.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://maven.apache.org/POM/4.0.0"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<artifactId>SpringbootQueryExample</artifactId>
<build>
<plugins>
<plugin>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<artifactId>lombok</artifactId>
<groupId>org.projectlombok</groupId>
</exclude>
</excludes>
</configuration>
<groupId>org.springframework.boot</groupId>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<groupId>org.springframework.boot</groupId>
</dependency>
<dependency>
<artifactId>spring-boot-starter-web</artifactId>
<groupId>org.springframework.boot</groupId>
</dependency>
<dependency>
<artifactId>spring-boot-devtools</artifactId>
<groupId>org.springframework.boot</groupId>
<optional>true</optional>
<scope>runtime</scope>
</dependency>
<dependency>
<artifactId>lombok</artifactId>
<groupId>org.projectlombok</groupId>
<optional>true</optional>
</dependency>
<dependency>
<artifactId>spring-boot-starter-test</artifactId>
<groupId>org.springframework.boot</groupId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.github.javafaker</groupId>
<artifactId>javafaker</artifactId>
<version>1.0.2</version>
</dependency>
</dependencies>
<description>Demo project for Spring Boot Query implementation</description>
<groupId>com.tutorial</groupId>
<modelVersion>4.0.0</modelVersion>
<name>SpringbootQueryExample</name>
<parent>
<artifactId>spring-boot-starter-parent</artifactId>
<groupId>org.springframework.boot</groupId>
<relativePath/>
<version>2.5.5</version> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<version>0.0.1-SNAPSHOT</version>
</project>
3.2 Application properties file
Create a properties file in the resources folder and add the following content to it. The file will contain information about the database connectivity, spring jpa, and the h2-console.
application.properties
server.port=9601 # removing trace field from response status exception server.error.include-stacktrace=never spring.application.name=springboot-and-lombok # database settings spring.datasource.username=sa spring.datasource.password= spring.datasource.url=jdbc:h2:mem:query spring.datasource.driverClassName=org.h2.Driver # jpa settings spring.jpa.database-platform=org.hibernate.dialect.H2Dialect spring.jpa.hibernate.ddl-auto=create-drop spring.jpa.properties.hibernate.show_sql=true # h2-console settings # url: http://localhost:9601/h2-console spring.h2.console.enabled=true spring.h2.console.path=/h2-console
3.3 Java Classes
Let us write the important java class(es) involved in this tutorial. The other non-important classes for this tutorial like the model, service, exceptions, dto (data transfer object), and configuration can be downloaded from the Downloads section.
3.3.1 Implementation/Main class
Add the following code to the main class to bootstrap the application from the main method. Always remember, the entry point of the spring boot application is the class containing @SpringBootApplication annotation and the static main method.
SpringbootQueryExampleApplication.java
package com.tutorial;
import lombok.extern.log4j.Log4j2;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@Log4j2
public class SpringbootQueryExampleApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootQueryExampleApplication.class, args);
log.info("application started successfully");
}
}
3.3.2 Repository interface
Add the following code to the repository interface that extends the JpaRepository. The interface methods will be annotated with the @Query annotation to write the explicit jdbc queries.
ProductRepository.java
package com.tutorial.repository;
import com.tutorial.model.Product;
import java.util.List;
import java.util.Optional;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
@Repository
public interface ProductRepository extends JpaRepository<Product, Integer> {
@Query(value = "SELECT * FROM product where name = :name", nativeQuery = true)
Optional<Product> findByName(@Param("name") String name);
@Query(value = "SELECT * FROM product WHERE material = :material", nativeQuery = true)
List<Product> findAllByMaterial(@Param("material") String material);
@Query(value = "SELECT * FROM product ORDER BY price DESC", nativeQuery = true)
List<Product> findAllOrderByPrice();
@Query(value = "SELECT COUNT(*) FROM product", nativeQuery = true)
long count();
@Query(value = "SELECT * FROM product ORDER BY price LIMIT :limit", nativeQuery = true)
List<Product> findTopNByPrice(@Param("limit") int limit);
@Modifying // to tell spring jpa to use executeUpdate as the state of database will be modified
@Query(value = "DELETE FROM product WHERE name = :name", nativeQuery = true)
void delete(@Param("name") String name);
//todo - add other crud methods.
}
3.3.3 Bootstrapping loader
Add the following code to the bootstrapping class which will be responsible to populate the database with some mock data.
ProductsLoader.java
package com.tutorial.boostrap;
import com.github.javafaker.Faker;
import com.tutorial.model.Product;
import com.tutorial.service.ProductService;
import java.util.ArrayList;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;
@Component
public class ProductsLoader implements CommandLineRunner {
@Autowired
Faker faker;
@Autowired
ProductService service;
@Override
public void run(String... args) throws Exception {
List<Product> products = new ArrayList<>();
for (int i = 0; i < 10; i++) {
products.add(create());
}
service.saveAll(products);
}
private Product create() {
return Product.builder()
.name(faker.commerce().productName())
.material(faker.commerce().material())
.price(Double.parseDouble(faker.commerce().price()))
.build();
}
}
3.3.4 Controller class
Add the following code to the controller class. The class will be responsible to handle the incoming HTTP requests, save or get the data from the database.
ProductRestResource.java
package com.tutorial.controller;
import com.tutorial.exception.BadRequest;
import com.tutorial.exception.EntityNotFound;
import com.tutorial.model.Product;
import com.tutorial.requestresponse.ProductDto;
import com.tutorial.service.ProductService;
import java.util.List;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/api/product")
@Log4j2
public class ProductRestResource {
@Autowired
ProductService service;
// http://localhost:9601/api/product/create
/*
{
"name": "Sleek Granite Shoes",
"material": "Rubber",
"price": 16.05
}
*/
@PostMapping("/create")
public ResponseEntity<Integer> create(@RequestBody ProductDto dto) {
final Product p = Product.builder()
.name(dto.getName())
.material(dto.getMaterial())
.price(dto.getPrice())
.build();
return new ResponseEntity<>(service.save(p), HttpStatus.CREATED);
}
// http://localhost:9601/api/product/get?name=Cotton
@GetMapping("/get")
public ResponseEntity<Product> getProduct(@RequestParam("name") String name)
throws BadRequest, EntityNotFound {
validateParam("name", name);
return new ResponseEntity<>(service.findByName(name), HttpStatus.OK);
}
// http://localhost:9601/api/product/getAll?material=Rubber
@GetMapping("/getAll")
public ResponseEntity<List<Product>> getAllByMaterial(@RequestParam("material") String material)
throws BadRequest {
validateParam("material", material);
return new ResponseEntity<>(service.findAllByMaterial(material), HttpStatus.OK);
}
// http://localhost:9601/api/product/getAllByPrice
@GetMapping("/getAllByPrice")
public ResponseEntity<List<Product>> getAllByPrice() {
return new ResponseEntity<>(service.findAllByPrice(), HttpStatus.OK);
}
// http://localhost:9601/api/product/findTopN?limit=5
@GetMapping("/findTopN")
public ResponseEntity<List<Product>> findTopN(@RequestParam(value = "limit",
defaultValue = "3") Integer limit)
throws BadRequest {
return new ResponseEntity<>(service.findTopN(limit), HttpStatus.OK);
}
// http://localhost:9601/api/product/delete?name=Cotton
@DeleteMapping("/delete")
public ResponseEntity<Void> delete(@RequestParam("name") String name)
throws BadRequest, EntityNotFound {
validateParam("name", name);
service.delete(name);
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
private void validateParam(String title, String value) throws BadRequest {
if (StringUtils.isBlank(value)) {
final String err = String.format("%s cannot be null/empty", title);
log.warn(err);
throw new BadRequest(err);
}
}
}
4. Run the Application
To execute the application, right-click on the SpringbootQueryExampleApplication.java class, Run As -> Java Application.
5. Project Demo
To test the application endpoints we will use the postman tool. However, you’re free to use any tool of your choice for interacting with the application endpoints.
Application endpoints
-- create product --
http://localhost:9601/api/product/create
/*
{
"name": "Sleek Granite Shoes",
"material": "Rubber",
"price": 16.05
}
*/
-- get a product by name --
http://localhost:9601/api/product/get?name=Cotton
-- get products by material --
http://localhost:9601/api/product/getAll?material=Rubber
-- get all products order by price --
http://localhost:9601/api/product/getAllByPrice
-- get limited products --
http://localhost:9601/api/product/findTopN?limit=5
-- delete product by name --
http://localhost:9601/api/product/delete?name=Cotton
That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
6. Summary
In this tutorial, we learned the @Query annotation along with a practical implementation of it in a spring application. You can download the sample application as an Eclipse project in the Downloads section.
7. Download the Project
This was an example of @Query annotation implementation in a spring application.
You can download the full source code of this example here: Spring JPA @Query Example
