spring

Spring JPA @Query Example

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.

spring jpa query - project structure
Fig. 1: Project structure

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.

spring jpa query - run the app
Fig. 2: Run the 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.

Download
You can download the full source code of this example here: Spring JPA @Query Example

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button