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.


<?xml version="1.0" encoding="UTF-8"?>
<project 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">





  <description>Demo project for Spring Boot Query implementation</description>

    <version>2.5.5</version> <!-- lookup parent from repository -->




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.


# removing trace field from response status exception
# database settings
# jpa settings
# h2-console settings
# url: http://localhost:9601/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.


package com.tutorial;

import lombok.extern.log4j.Log4j2;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

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.


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;

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.


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;

public class ProductsLoader implements CommandLineRunner {

  Faker faker;
  ProductService service;

  public void run(String... args) throws Exception {
    List<Product> products = new ArrayList<>();
    for (int i = 0; i < 10; i++) {

  private Product create() {
    return Product.builder()

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.


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;

public class ProductRestResource {

  ProductService service;

  // http://localhost:9601/api/product/create
    "name": "Sleek Granite Shoes",
    "material": "Rubber",
    "price": 16.05
  public ResponseEntity<Integer> create(@RequestBody ProductDto dto) {
    final Product p = Product.builder()
    return new ResponseEntity<>(service.save(p), HttpStatus.CREATED);

  // http://localhost:9601/api/product/get?name=Cotton
  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
  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
  public ResponseEntity<List<Product>> getAllByPrice() {
    return new ResponseEntity<>(service.findAllByPrice(), HttpStatus.OK);

  // http://localhost:9601/api/product/findTopN?limit=5
  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
  public ResponseEntity<Void> delete(@RequestParam("name") String name)
      throws BadRequest, EntityNotFound {
    validateParam("name", 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);
      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 --
"name": "Sleek Granite Shoes",
"material": "Rubber",
"price": 16.05

-- get a product by name -- 

-- get products by material --

-- get all products order by price --

-- get limited products --

-- delete product by name --

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

Want to know how to develop your skillset to become a Java Rockstar?

Join our newsletter to start rocking!

To get you started we give you our best selling eBooks for FREE!


1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design


and many more ....


Receive Java & Developer job alerts in your Area

I have read and agree to the terms & conditions



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).
Notify of

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

Inline Feedbacks
View all comments
Back to top button