当前位置：首页 > news >正文

Spring Boot 3.3 【五】Spring Boot 整合JPA-原生SQL支持

news 来源：原创 2024/9/20 2:50:06

在使用JPA（Java Persistence API）时，虽然它主要通过对象而不是SQL语句来操作数据库，但在处理复杂查询时，我们有时仍需要直接使用SQL。这可以通过 @Query 注解在 Repository 接口中实现。以下是一个使用Spring Data JPA和原生SQL来处理复杂数据库查询的实例。

一、查询用户及其订单信息

假设我们有两个表：users 和 orders，其中 orders 表通过 user_id 字段与 users 表关联。

二、数据库表结构

users
- id INT
- name VARCHAR
- email VARCHAR
orders
- id INT
- user_id INT
- order_date DATE
- amount DECIMAL

三、创建JPA实体

1. User 实体类

package com.jsglxx.demo.entity;import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;@Entity  
public class User {  @Id  @GeneratedValue(strategy = GenerationType.IDENTITY)  private Long id;  private String name;  private String email;  public User() {super();}// getters and setters  public Long getId() {return id;}public void setId(Long id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public User(Long id, String name, String email) {super();this.id = id;this.name = name;this.email = email;}}

2. Orders 实体类

注意：order 是 Mysql 排序的保留关键字，不能使用。

package com.jsglxx.demo.entity;import java.math.BigDecimal;
import java.util.Date;import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;@Entity  
public class Orders {  @Id  @GeneratedValue(strategy = GenerationType.IDENTITY)  private Long id;  private Long userId;  private Date orderDate;  private BigDecimal amount;  public Orders() {super();}// getters and setters  public Long getId() {return id;}public void setId(Long id) {this.id = id;}public Long getUserId() {return userId;}public void setUserId(Long userId) {this.userId = userId;}public Date getOrderDate() {return orderDate;}public void setOrderDate(Date orderDate) {this.orderDate = orderDate;}public BigDecimal getAmount() {return amount;}public void setAmount(BigDecimal amount) {this.amount = amount;}public Orders(Long id, Long userId, Date orderDate, BigDecimal 			amount) {super();this.id = id;this.userId = userId;this.orderDate = orderDate;this.amount = amount;}}

四、创建 Repository 接口

package com.jsglxx.demo.repository;import java.util.List;import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;import com.jsglxx.demo.entity.User;public interface UserRepository extends JpaRepository<User, Long> {  @Query(value = "SELECT u.*, o.* FROM user u INNER JOIN orders o ON u.id = o.user_id WHERE u.name = ?1", nativeQuery = true)  List<Object[]> findUserWithOrdersByName(String name);  }

返回类型 List<Object[]> 中的 Object[] 表示每行结果是一个对象数组，其中包含了 users 和 orders 表中对应列的值。
对于更复杂的查询，考虑使用 DTO（Data Transfer Object） 来映射查询结果，使代码更加清晰。

五、 Service 调用

package com.jsglxx.demo.service;import java.util.List;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import com.jsglxx.demo.repository.UserRepository;@Service  
public class UserService {  @Autowired  private UserRepository userRepository;  public List<Object[]> getUserWithOrdersByName(String name) { return userRepository.findUserWithOrdersByName(name);  }  
}

六、 Controller 调用

@RestController  
@RequestMapping("/users")  
public class UserController {  @Autowiredprivate UserService userService;//根据用户姓名查询订单信息@GetMapping("/{name}")public List<Object[]> getUserWithOrdersByName(@PathVariable String name){return this.userService.getUserWithOrdersByName(name);}
}