HQL嵌套连接实现与报错排查：获取InventoryReceipts关联InvTran数据

问题背景 #

在Hibernate中使用OneToMany映射实体，需要获取InventoryReceiptsLine结果中嵌套的InvTran数据，希望通过自定义HQL查询生成报表，目标是生成指定嵌套JSON结构（当InventoryReceiptLine存在订单关联时填充invTran字段），但编写的HQL查询抛出「Cannot read field "value" because "s1" is null」错误。

错误信息 #

"Cannot read field "value" because "s1" is null [SELECT IHDR FROM com.emrsinc.patch.models.InventoryReceiptsHDR IHDR left join fetch InventoryReceiptsLine IRL on IRL.receipt_number = IHDR.receipt_number left join fetch InvTran on InvTran.sub_document_no = IRL.receipt_number WHERE IHDR.receipt_number = :receipt]"

相关代码 #

原查询代码 #

@Query(value = "SELECT IHDR " +
            "FROM InventoryReceiptsHDR IHDR " +
            "left join fetch InventoryReceiptsLine IRL on IRL.receipt_number = IHDR.receipt_number " +
            "left join fetch InvTran on InvTran.sub_document_no = IRL.receipt_number " +
            "WHERE IHDR.receipt_number = :receipt")
    InventoryReceiptsHDR getReceipt(@Param("receipt") int receipt);

实体类代码 #

InventoryReceiptsHDR类

@Entity
@Getter
@Table(name = "inventory_receipts_hdr")
public class InventoryReceiptsHDR implements Serializable {

    @Id
    private Integer receipt_number;

    @OneToMany
    @JoinColumn(name = "receipt_number")
    @JsonProperty("items")
    private Set<InventoryReceiptsLine> inventoryReceiptsLineList;

}

InventoryReceiptsLine类

@Entity
@Getter
@Table(name = "inventory_receipts_line")
public class InventoryReceiptsLine implements Serializable {

    @Id
    @JsonBackReference
    private Long line_number;

    private Long receipt_number;
    private Long inv_mast_uid;
    private Long qty_received;
    private String unit_of_measure;

    @OneToOne
    @JoinColumn(name = "inv_mast_uid", insertable = false, updatable = false)
    @JsonUnwrapped
    private InvMast invMast;

    @OneToMany
    @JoinColumns({
            @JoinColumn(name = "inv_mast_uid", referencedColumnName = "inv_mast_uid"),
            @JoinColumn(name="sub_document_no", referencedColumnName = "receipt_number")
    })
    private Set<InvTran> invTran;

}

InvTran类

@Entity
@Getter
@Table(name = "inv_tran")
public class InvTran implements Serializable {

    @Id
    private Integer transaction_number;
    private Integer document_no;
    private Integer qty_allocated;
    private String unit_of_measure;
    @JsonProperty(value = "receipt_id")
    private Integer sub_document_no;
    private Integer inv_mast_uid;

    @OneToOne()
    @JoinColumn(name = "transaction_number", referencedColumnName = "order_no")
    private OeHDR oe;

}

错误原因 #

HQL的join fetch语法必须基于实体间已定义的映射关系，不能像原生SQL那样直接指定on条件。原查询直接对InvTran执行left join fetch，没有通过InventoryReceiptsLine实体中已定义的invTran关联属性进行关联，导致Hibernate无法识别映射关系，进而触发空指针错误。

解决方案 #

修正HQL查询，通过实体间已定义的关联路径执行join fetch：

修正后的查询代码 #

@Query(value = "SELECT DISTINCT IHDR " +
        "FROM InventoryReceiptsHDR IHDR " +
        "left join fetch IHDR.inventoryReceiptsLineList IRL " +
        "left join fetch IRL.invTran " +
        "WHERE IHDR.receipt_number = :receipt")
InventoryReceiptsHDR getReceipt(@Param("receipt") int receipt);

说明 #

1. 关联路径修正：通过IHDR.inventoryReceiptsLineList关联到InventoryReceiptsLine，再通过IRL.invTran关联到InvTran，完全基于实体类中定义的映射关系，符合HQL语法规范。
2. 添加DISTINCT：由于同时fetch两个集合（inventoryReceiptsLineList和invTran）会产生笛卡尔积，导致查询结果重复，添加DISTINCT可确保返回唯一的InventoryReceiptsHDR实例。

目标输出效果 #

修正后查询返回的实体序列化后，将生成符合需求的嵌套JSON结构：

{
    "receipt_number": 5000027,
    "items": [
        {
            "receipt_number": 5000027,
            "inv_mast_uid": 22428,
            "qty_received": 100,
            "unit_of_measure": "EA",
            "item_id": "TRI620-104-706",
            "item_desc": "HSS104 CLAMP 4-8/64\"-7\"",
            "invTran": []
        },
        {
            "receipt_number": 5000027,
            "inv_mast_uid": 13628,
            "qty_received": 200,
            "unit_of_measure": "EA",
            "item_id": "DIXHSS72",
            "item_desc": "ALL STAINLESS WORMGEAR CLAMPS",
            "invTran": []
        },
        {
            "receipt_number": 5000027,
            "inv_mast_uid": 22412,
            "qty_received": 100,
            "unit_of_measure": "EA",
            "item_id": "TRI620-008-706",
            "item_desc": "620-008 CLAMP (HAS 8)",
            "invTran": [
                {
                    "transaction_number": 4245,
                    "document_no": 1000064,
                    "qty_allocated": 51,
                    "unit_of_measure": "EA",
                    "inv_mast_uid": 22412,
                    "oe": null,
                    "receipt_id": 5000027
                }
            ]
        }
    ]
}

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