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 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)
Cấu trúc project
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.
