Spring Boot JPA查询MySQL视图仅返回重复首行数据问题求助

问题原因 #

该问题由JPA实体主键设置错误导致：
JPA规范要求每个实体必须有唯一的主键标识，持久化上下文（一级缓存）会以主键为key缓存已经查询到的实体对象。你将非唯一的customerid设为了实体的唯一主键，而视图中同一个customerid对应多条数据，此时JPA会认为这些行对应同一个实体，直接复用第一次查询到的对象，不会读取后续行的其他字段值，最终就出现了同customerid下所有行都重复首条数据的现象。
普通表单一主键每行唯一，不会触发该缓存复用逻辑，所以读取正常。

解决方案 #

方案1：使用复合主键映射（无需修改视图） #

当视图没有单字段唯一标识时，可使用多字段组合的复合主键，具体改造步骤如下：

1. 新建复合主键类，需实现Serializable接口，并重写equals和hashCode方法：

import java.io.Serializable;
import java.sql.Timestamp;
import java.util.Objects;

public class ViewMonthlyExpenseId implements Serializable {
    private Long customerId;
    private Timestamp createdDateTime;
    private Float priceGross;

    public ViewMonthlyExpenseId() {}

    public ViewMonthlyExpenseId(Long customerId, Timestamp createdDateTime, Float priceGross) {
        this.customerId = customerId;
        this.createdDateTime = createdDateTime;
        this.priceGross = priceGross;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        ViewMonthlyExpenseId that = (ViewMonthlyExpenseId) o;
        return Objects.equals(customerId, that.customerId) && Objects.equals(createdDateTime, that.createdDateTime) && Objects.equals(priceGross, that.priceGross);
    }

    @Override
    public int hashCode() {
        return Objects.hash(customerId, createdDateTime, priceGross);
    }
}

2. 改造原视图实体类，绑定复合主键：

import javax.persistence.*;
import java.sql.Timestamp;

@Entity
@Immutable
@Table(name="v_monthlyexpense")
@IdClass(ViewMonthlyExpenseId.class) // 绑定复合主键类
public class View_MonthlyExpense {

    @Id // 标记为组成主键的字段
    @Column(name="customerid", nullable = false)
    private Long customerId;

    @Id // 标记为组成主键的字段
    @Column(name="createddatetime")
    private Timestamp createdDateTime;

    @Id // 标记为组成主键的字段
    @Column(name="pricegross")
    private float priceGross;

    protected View_MonthlyExpense() {
    }

    public View_MonthlyExpense(Long customerId, Timestamp createdDateTime, float priceGross) {
        this.customerId = customerId;
        this.createdDateTime = createdDateTime;
        this.priceGross = priceGross;
    }

    // 原有getter、toString方法保持不变
    public Long getCustomerId() {
        return customerId;
    }

    public Timestamp getCreatedDateTime() {
        return createdDateTime;
    }

    public float getPriceGross() {
        return priceGross;
    }

    @Override
    public String toString() {
        return customerId + ". " + createdDateTime + " - " + priceGross + " USD";
    }
}

3. 改造Repository接口，替换主键类型：

import org.springframework.data.repository.CrudRepository;
import java.util.List;

public interface ExpenseRepository2 extends CrudRepository<View_MonthlyExpense, ViewMonthlyExpenseId> {
    List<View_MonthlyExpense> findAll();
}

方案2：给视图增加伪主键（代码改造量更小） #

修改视图创建语句，新增自增行号作为唯一单字段主键：

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_monthlyexpense` AS 
select 
  (@rowid := @rowid + 1) AS id, -- 新增自增伪主键
  distinct `_v`.`customerid` AS `customerid`,
  `_o`.`createddatetime` AS `createddatetime`,
  `_o`.`pricegross` AS `pricegross` 
from ((`c_visit` `_v` join `c_visit_order` `_vo` on(`_vo`.`visitId` = `_v`.`id`)) join `c_order` `_o` on(`_o`.`id` = `_vo`.`orderId`)),
(SELECT @rowid := 0) AS t;

之后实体类只需要将新增的id字段设为唯一主键即可，无需使用复合主键。

改造完成后重新运行即可正常读取视图的所有行数据。

内容的提问来源于stack exchange，提问作者MTMunich