Code ví dụ Spring Boot Data JPA annotation @Query

Code ví dụ Spring Boot Data JPA annotation @Query

Spring Data @Query

Annotation @Query trong spring data jpa được dùng để khai báo các query cho các method trong interface thừa kế từ Repository.

(Xem lại: Hướng dẫn annotation @Query / NativeQuery trong Spring data)

Code ví dụ

Các công nghệ sử dụng:

Tạo Database

Tạo database spring-data với table customer

CREATE DATABASE  IF NOT EXISTS `spring-data`;
CREATE TABLE `spring-data`.`customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Tạo database spring-data với table customer

Tạo Spring Boot Project

Ở bài này mình tạo Spring Boot Non-Web để hiển thị kết quả luôn trên console cho đơn giản chứ không thông qua trình duyệt web.

(Xem lại: Code ví dụ Spring Boot Non Web)

spring starter project

Code ví dụ Spring Boot Data JPA @Query

Code ví dụ Spring Boot Data JPA @Query

Cấu trúc project

Code ví dụ Spring Boot Data JPA annotation @Query

File application.properties

Config các thông tin kết nối database, hibernate.

## Spring DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/spring-data?useSSL=false
spring.datasource.username=root
spring.datasource.password=admin1234

## ==============JPA / HIBERNATE=================
spring.jpa.show-sql=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect


## ============= LOGGING
logging.pattern.console=

File entity

package stackjava.com.sbdatquery.entities;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "customer")
public class Customer implements Serializable{
  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "id")
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  @Column(name = "name")
  private String name;

  @Column(name = "address")
  private String address;

  // getter - setter

}

File Repository

2 method đầu tiên đều dùng để lấy ra tất cả các đối tượng customer và sắp theo theo thứ tự name giảm dần: trong đó method đầu tiên viết theo chuẩn hsql (truy vấn theo đối tượng) còn method thứ 2 viết theo dạng native sql (truy vấn theo bảng)

Method findByName thì sử dụng kiểu truyền tham số theo thứ tự, còn method findByNameAndAddress thì truyền tham số theo cách sử dụng annotation @Param

package stackjava.com.sbdataquery.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import stackjava.com.sbdataquery.entities.Customer;

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {

  @Query("SELECT e FROM Customer e ORDER BY e.name DESC")
  List<Customer> findAllOrderByNameDesc();

  @Query(value = "SELECT e.* FROM customer e ORDER BY e.name DESC", nativeQuery = true)
  List<Customer> findAllOrderByNameDescNative();

  @Query("SELECT e FROM Customer e WHERE e.name = ?1")
  List<Customer> findByName(String name);

  @Query("SELECT e FROM Customer e WHERE e.name = :name AND e.address = :address")
  List<Customer> findByNameAndAddress(@Param("name") String name, @Param("address") String address);

  @Query("SELECT e FROM Customer e WHERE e.name like ?1")
  List<Customer> findByNameLike(String name);

}

File chạy ứng dụng

package stackjava.com.sbdataquery;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

import stackjava.com.sbdataquery.entities.Customer;
import stackjava.com.sbdataquery.repository.CustomerRepository;

@SpringBootApplication
@EnableJpaRepositories
@ComponentScan("stackjava.com")
public class SpringBootDataQueryApplication implements CommandLineRunner {

  @Autowired
  private CustomerRepository customerRepository;

  public static void main(String[] args) {
    SpringApplication app = new SpringApplication(SpringBootDataQueryApplication.class);
    app.run(args);
  }

  @Override
  public void run(String... args) throws Exception {
    System.out.println("__________Reset and init data________________");
    customerRepository.deleteAll();
    customerRepository.save(new Customer("Dead pool", "Marvel"));
    customerRepository.save(new Customer("Thor", "ragnarok"));
    customerRepository.save(new Customer("Iron Man", "Marvel"));
    customerRepository.save(new Customer("Hulk", "Marvel"));
    customerRepository.save(new Customer("Hawkeye", "Marven"));
    customerRepository.save(new Customer("Thanos", "Titan"));
    customerRepository.save(new Customer("Batman", "DC"));

    System.out.println("__________Demo find all order by name desc________________");
    List<Customer> customers = customerRepository.findAllOrderByNameDesc();
    customers.forEach(customer -> System.out.println(customer));
    
    System.out.println("__________Demo find find by name like 'th'________________");
    List<Customer> listCustomer = customerRepository.findByNameLike("%th%");
    listCustomer.forEach(customer -> System.out.println(customer));
  }

}

Kết quả:

__________Reset and init data________________
__________Demo find all order by name desc________________
Customer [id=2, name=Thor, address=ragnarok]
Customer [id=6, name=Thanos, address=Titan]
Customer [id=3, name=Iron Man, address=Marvel]
Customer [id=4, name=Hulk, address=Marvel]
Customer [id=5, name=Hawkeye, address=Marven]
Customer [id=1, name=Dead pool, address=Marvel]
Customer [id=7, name=Batman, address=DC]
__________Demo find find by name like 'th'________________
Customer [id=2, name=Thor, address=ragnarok]
Customer [id=6, name=Thanos, address=Titan]

 

Okay, Done!

Download code ví dụ trên tại đây.

 

References.

https://docs.spring.io/…/#jpa.query-methods.at-query

stackjava.com