如何在SpringBoot中使用JPA调用MySQL关联多表的查询存储过程

1. 实体类修正 #

你原有实体类的核心问题有两个：

• resultClasses配置错误：存储过程返回的是4个统计字段组成的单结果集，直接映射当前实体即可，不需要关联ProjectsInfo和ClientInfo
• 缺少JPA规范要求的@Id主键注解，所有被@Entity修饰的类必须声明主键
  修正后的代码如下：

package com.panchmeru_studio.entities;

import lombok.Data;
import javax.persistence.*;

@Data
@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(
                name = "GetProjectAndClientOverview.getOverview",
                procedureName = "GetProjectAndClientOverview",
                resultClasses = GetProjectAndClientOverview.class
        )
})
public class GetProjectAndClientOverview {
    // 因为是统计结果实体，无实际业务主键，声明一个临时主键即可
    @Id
    private Long id;

    @Column(name = "projects_done")
    private int projectsDone;

    @Column(name = "happy_clients")
    private int happyClients;

    @Column(name = "real_professionals")
    private int realProfessionals;

    @Column(name = "cups_of_coffee")
    private int cupsOfCoffee;
}

2. Repository层代码 #

创建对应Repository接口继承JpaRepository，使用@Procedure注解绑定存储过程：

package com.panchmeru_studio.repository;

import com.panchmeru_studio.entities.GetProjectAndClientOverview;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Procedure;
import java.util.List;

public interface OverviewRepository extends JpaRepository<GetProjectAndClientOverview, Long> {
    // 绑定@NamedStoredProcedureQuery里配置的name属性
    @Procedure(name = "GetProjectAndClientOverview.getOverview")
    List<GetProjectAndClientOverview> getOverview();
}

因为存储过程返回单行统计结果，你也可以直接返回GetProjectAndClientOverview类型，不需要用List接收

3. Service层代码 #

注入Repository调用方法即可：

package com.panchmeru_studio.service;

import com.panchmeru_studio.entities.GetProjectAndClientOverview;
import com.panchmeru_studio.repository.OverviewRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class OverviewService {
    @Autowired
    private OverviewRepository overviewRepository;

    public GetProjectAndClientOverview getProjectAndClientOverview() {
        // 取返回结果的第一条即可
        return overviewRepository.getOverview().get(0);
    }
}

扩展说明 #

如果不想为统计结果声明带@Id的实体，你也可以直接通过EntityManager调用存储过程，手动映射结果，适合临时统计场景使用。

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