Spring Boot对接MySQL多表视图返回React前端查询报错如何解决

问题根因 #

你遇到的报错由以下几个错误共同导致：

• 实体类字段和视图字段不匹配：你在CpySiteAssetView实体类中定义了带@Id注解的id字段，但你创建的MySQL视图的查询字段里根本没有id列，Hibernate执行查询时会自动拼接id字段到SQL中，自然报找不到id字段的错误
• 视图名不匹配：你创建的视图名称是cpysiteasset，但是实体类@Table注解中配置的表名是cpysiteassetview，两边名称不一致
• 视图创建SQL语法错误：你写的CREATE VIEW语句里，cpymaster和sitemaster的JOIN关联条件不完整，还有多余的右括号，视图本身就无法正常创建
• 主键配置错误：视图是只读的，不需要@GeneratedValue自增主键配置，你需要选择视图中能唯一确定一行数据的字段作为主键

修正方案 #

1. 修正视图创建SQL #

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `cpysiteassetview` AS
    SELECT 
        -- 新增id字段作为主键，也可以直接用assetsid作为主键（如果资产id全局唯一）
        ROW_NUMBER() OVER (ORDER BY `sitemaster`.`sitemastercode` , `assets`.`assetsidentifier`) AS `id`,
        `cpymaster`.`cpymasterid` AS `cpymasterid`,
        `cpymaster`.`cpymastercode` AS `cpymastercode`,
        `cpymaster`.`cpymastername` AS `cpymastername`,
        `sitemaster`.`sitemasterid` AS `sitemasterid`,
        `sitemaster`.`sitemastercode` AS `sitemastercode`,
        `sitemaster`.`sitemastername` AS `sitemastername`,
        `assets`.`assetsid` AS `assetsid`,
        `assets`.`assetsidentifier` AS `assetsidentifier`,
        `assets`.`assetsname` AS `assetsname`
    FROM
        `cpymaster`
        -- 补全关联条件，可替换为你实际的关联字段
        JOIN `sitemaster` ON `sitemaster`.`cpymasterid` = `cpymaster`.`cpymasterid`
        JOIN `assets` ON `assets`.`sitemaster_sitemasterid` = `sitemaster`.`sitemasterid`
    ORDER BY `sitemaster`.`sitemastercode` , `assets`.`assetsidentifier`;

2. 修正实体类代码 #

package net.javaguides.springboot.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Immutable;

@Entity
@Immutable
@Table(name = "cpysiteassetview")
public class CpySiteAssetView {
    
    @Id // 移除自增配置，视图为只读无需自增
    private long id;
    
    private int cpymasterid;
    private String cpymastercode; 
    private String cpymastername;
    private int sitemasterid;
    private String sitemastercode; 
    private String sitemastername; 
    private int assetsid;
    private String assetsidentifier; 
    private String assetsname;
    
    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    @Column(name = "cpymasterid")
    public int getCpymasterid() {
        return cpymasterid;
    }
    
    public void setCpymasterid(int cpymasterid) {
        this.cpymasterid = cpymasterid;
    }

    @Column(name = "cpymastercode")
    public String getCpymastercode() {
        return cpymastercode;
    }
    
    public void setCpymastercode(String cpymastercode) {
        this.cpymastercode = cpymastercode;
    }

    @Column(name = "cpymastername")
    public String getCpymastername() {
        return cpymastername;
    }
    
    public void setCpymastername(String cpymastername) {
        this.cpymastername = cpymastername;
    }

    @Column(name = "sitemasterid")
    public int getSitemasterid() {
        return sitemasterid;
    }
    
    public void setSitemasterid(int sitemasterid) {
        this.sitemasterid = sitemasterid;
    }

    @Column(name = "sitemastercode")
    public String getSitemastercode() {
        return sitemastercode;
    }
    
    public void setSitemastercode(String sitemastercode) {
        this.sitemastercode = sitemastercode;
    }

    @Column(name = "sitemastername")
    public String getSitemastername() {
        return sitemastername;
    }
    
    public void setSitemastername(String sitemastername) {
        this.sitemastername = sitemastername;
    }

    @Column(name = "assetsid")
    public int getAssetsid() {
        return assetsid;
    }
    
    public void setAssetsid(int assetsid) {
        this.assetsid = assetsid;
    }

    @Column(name = "assetsidentifier")
    public String getAssetsidentifier() {
        return assetsidentifier;
    }
    
    public void setAssetsidentifier(String assetsidentifier) {
        this.assetsidentifier = assetsidentifier;
    }

    @Column(name = "assetsname")
    public String getAssetsname() {
        return assetsname;
    }
    
    public void setAssetsname(String assetsname) {
        this.assetsname = assetsname;
    }
            
}

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