含复合键的实体一对多映射配置及ORA-00942错误排查求助

配置含复合键的MrsConHist与MrsConHistCar实体一对多关联时，调用MrsConHist.getMrsConHistCars()触发ORA-00942:表或视图不存在错误。已核查实体类与映射关系未发现异常，按业务逻辑该方法应返回至少6条数据。

系统环境：hibernate-core 6.6、jakarta.persistence 3.1.0、Oracle 19.6、Java 11

实体类代码 #

父实体：MrsConHist.java 和 MrsConHistPK.java #

@Table(name = "MRS_CON_HIST")
@Entity
public class MrsConHist implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    private MrsConHistPK comp_id;
    
    @OneToMany(mappedBy="mrsConHist",orphanRemoval =true,fetch= FetchType.LAZY)//tried EAGER
    private Set<MrsConHistCar> mrsConHistCars = new HashSet<MrsConHistCar>(0);

    public Set<MrsConHistCar> getMrsConHistCars() {
    return this.mrsConHistCars;
    }
    public void setMrsConHistCars(Set<MrsConHistCar> mrsConHistCars) {
    this.mrsConHistCars = mrsConHistCars;
    }
    //Other get and set, and constructors
}

@Embeddable
public class MrsConHistPK  implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    @Column(name = "CON_NO",insertable=false, updatable=false)
    private String conNo;
    @Column(name = "DASH_NO",insertable=false, updatable=false)
    private Integer dashNo;
    @Column(name = "DATE_INSRV_REV",insertable=false, updatable=false)
    private Date dateInsrvRev;
    @Column(name = "DATETIME_INSRV",insertable=false, updatable=false)
    private Date datetimeInsrv;

    // Constructors, Get, Set, and @Override functions    
}

子实体：MrsConHistCar.java 和 MrsConHistCarPK.java #

@Table(name = "MRS_CON_HIST_CAR")
@Entity
public class MrsConHistCar  implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    private MrsConHistCarPK comp_id;

    @ManyToOne(fetch = FetchType.LAZY ) // tried EAGER
    @JoinColumns({
     @JoinColumn(name="CON_NO", nullable=false),
     @JoinColumn(name = "DASH_NO", nullable=false),
     @JoinColumn(name = "DATE_INSRV_TREV", nullable=false),
     @JoinColumn(name = "DATETIME_INSRV", nullable=false)
    })
     private MrsConHist mrsConHist;    
    // Constructors, Get, Set, and @Override functions  
}


@Embeddable
public class MrsConHistCarPK implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    @Column(name = "CON_NO", insertable=false, updatable=false)
    private String conNo;
    @Column(name = "DASH_NO", insertable=false, updatable=false)
    private Integer dashNo;
    @Column(name = "DATE_INSRV_REV", insertable=false, updatable=false)
    private java.sql.Date dateInsrvRev;
    @Column(name = "DATETIME_INSRV", insertable=false, updatable=false)
    private Date datetimeInsrv;
    @Column(name = "CAR_NO")
    private String carNo;
    // Constructors, Get, Set, and @Override functions 
}

数据库查询代码 #

mrsConHist= DAOFactory.getMarisConsistDao().retrieve_MrsConHist( incdOccrDt, conNo,  dashNo, true ); 

报错信息 #

调用Set<MrsConHistCar> cars = mrsConHist.getMrsConHistCars();时触发错误：

15:23:48.230 DEBUG JDBC exception executing SQL [select mchc1_0.CON_NO,mchc1_0.DASH_NO,mchc1_0.DATE_INSRV_TREV,mchc1_0.DATETIME_INSRV,mchc1_0.CAR_NO,mchc1_0.DATE_INSRV_REV,mchc1_0.CAR_ORDER,mchc1_0.CAR_ORIENTATION,mchc1_0.CUM_HRS,mchc1_0.CUM_MIN,mchc1_0.DATETIME_LAST_UPD,mchc1_0.DATETIME_MAINT_UPD,mchc1_0.END_CAR_HRS,mchc1_0.END_CAR_MIN,mchc1_0.TERM_LAST_UPD,mchc1_0.TERM_MAINT_UPD
from MRS_CON_HIST_CAR mchc1_0
where (mchc1_0.CON_NO,mchc1_0.DASH_NO,mchc1_0.DATE_INSRV_TREV,mchc1_0.DATETIME_INSRV) in ((?,?,?,?))] [ORA-00942: table or view does not exist] [n/a]

排查与解决步骤 #

1. 修正列名拼写错误
   从报错SQL可见，Hibernate生成的查询使用了DATE_INSRV_TREV列，但父实体主键类MrsConHistPK、子实体主键类MrsConHistCarPK对应的列均为DATE_INSRV_REV，此处存在明显拼写错误（TREV vs REV），导致Oracle解析时误报表/视图不存在。

2. 更新关联注解
   将MrsConHistCar类中@JoinColumns里的DATE_INSRV_TREV改为DATE_INSRV_REV，与数据库列名、主键类定义保持一致：

   @ManyToOne(fetch = FetchType.LAZY )
   @JoinColumns({
    @JoinColumn(name="CON_NO", nullable=false),
    @JoinColumn(name = "DASH_NO", nullable=false),
    @JoinColumn(name = "DATE_INSRV_REV", nullable=false), // 修正拼写
    @JoinColumn(name = "DATETIME_INSRV", nullable=false)
   })
    private MrsConHist mrsConHist;
   

3. 验证关联完整性
   确保子实体@JoinColumns中所有列与父实体主键列一一对应，列名、数据类型完全匹配。同时确认数据库中MRS_CON_HIST_CAR表存在这些列，且当前数据库用户拥有该表的查询权限。

4. 检查Hibernate schema配置
   若表属于特定schema，需在@Table注解中指定schema属性，例如：

   @Table(name = "MRS_CON_HIST_CAR", schema = "YOUR_SCHEMA_NAME")
   

   或在Hibernate配置文件中设置hibernate.default_schema为对应schema。

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