JPA原生查询未生效及多标识符行异常问题咨询

一、让自定义原生查询生效的步骤 #

1. 修正原生查询的表名大小写 #

你的Expense实体通过@Table(name = "expense")指定数据库表名为小写的expense，但原生查询中写的是from Expense e，在区分大小写的数据库（如PostgreSQL）中会找不到表，导致JPA自动 fallback 到默认查询。需将表名改为与数据库一致的小写：

@Query(nativeQuery = true, value = "select e.expenseid, e.expensedetails, e.amount, e.createddate,e.comment, c.*, p.* from expense e inner join category c on e.categoryid = c.categoryid inner join payment p on e.paymenttypeid = p.paymenttypeid where c.categorytype = 'Expense'")
List<Expense> findAllRecords();

2. 确保Repository接口配置正确 #

• 确认方法所在接口继承自JpaRepository<Expense, Integer>（或Spring Data JPA的其他标准Repository接口）。
• 确认@Query注解导入的是org.springframework.data.jpa.repository.Query，而非其他包的同名注解。
• 避免方法名触发Spring Data JPA的查询推导冲突：可将findAllRecords改为findAllExpenseRecords这类更明确的名称，减少潜在的默认查询覆盖问题。

3. 配置结果集映射（处理关联对象） #

原生查询返回了多表字段，JPA需要明确如何映射到Expense的关联属性（categoryExpense、paymentExpense），可通过@SqlResultSetMapping定义映射规则：

1. 在Expense实体类上添加映射配置：

@Getter @Setter @NoArgsConstructor
@Entity
@Table(name = "expense")
@SqlResultSetMapping(
    name = "ExpenseWithCategoryAndPayment",
    entities = {
        @EntityResult(entityClass = Expense.class, fields = {
            @FieldResult(name = "expenseid", column = "expenseid"),
            @FieldResult(name = "expensedetails", column = "expensedetails"),
            @FieldResult(name = "amount", column = "amount"),
            @FieldResult(name = "comment", column = "comment"),
            @FieldResult(name = "createddate", column = "createddate")
        }),
        @EntityResult(entityClass = Category.class, fields = {
            @FieldResult(name = "categoryid", column = "categoryid"),
            @FieldResult(name = "categoryname", column = "categoryname"),
            @FieldResult(name = "categorytype", column = "categorytype")
        }),
        @EntityResult(entityClass = Payment.class, fields = {
            @FieldResult(name = "paymenttypeid", column = "paymenttypeid"),
            @FieldResult(name = "paymenttype", column = "paymenttype")
        })
    }
)
public class Expense {
    // 原有实体属性...
}

2. 在@Query注解中指定使用该映射：

@Query(nativeQuery = true, value = "select e.expenseid, e.expensedetails, e.amount, e.createddate,e.comment, c.categoryid, c.categoryname, c.categorytype, p.paymenttypeid, p.paymenttype from expense e inner join category c on e.categoryid = c.categoryid inner join payment p on e.paymenttypeid = p.paymenttypeid where c.categorytype = 'Expense'", resultSetMapping = "ExpenseWithCategoryAndPayment")
List<Expense> findAllExpenseRecords();

二、解决More than one row with the given identifier was found异常 #

这个异常的核心原因是关联关系配置与数据库实际数据不匹配：
你的Expense实体对Category和Payment使用了@OneToOne注解，但实际业务中，一个分类/支付方式会被多个支出记录引用，这符合多对一的关联逻辑，而非一对一。

修改关联注解 #

将@OneToOne改为@ManyToOne，保留原有的@JoinColumn配置：

@Getter @Setter @NoArgsConstructor
@Entity
@Table(name = "expense")
public class Expense {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int expenseid;

    @Column(nullable = true)
    private String expensedetails;

    private Double amount;

    // 改为多对一关联
    @ManyToOne(fetch = FetchType.EAGER)
    @JsonManagedReference(value = "expense")
    @JoinColumn(name="categoryid")
    private Category categoryExpense;

    private String comment;

    private Date createddate;

    // 改为多对一关联
    @ManyToOne(fetch = FetchType.EAGER)
    @JsonManagedReference(value = "expense")
    @JoinColumn(name="paymenttypeid")
    private Payment paymentExpense;
}

修改后，JPA加载支出记录时，即使多个记录关联同一个分类或支付方式，也不会因主键重复抛出异常，同时能正确映射关联对象。

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