运行Hibernate条件时,不会按给定顺序创建HQL查询
并显示异常

  ERROR SqlExceptionHelper:131 - Unknown column 'stsup4_.SUPPLIER_ID' in 'on clause'

生成的HQL不是按预期顺序创建的。
当我在mysql中运行同一个生成的HQL并进行一些更改时,就会得到输出。
Java代码如下
        Session session = connector.getSession();
        Criteria cr = session.createCriteria(ST_PRODUCTMASTER.class, "ST_PRODUCT");
        cr.createCriteria("ST_PRODUCT.ST_PRODUCT_MANUFACTURER_PARENT", "STPRODMAN", JoinType.LEFT_OUTER_JOIN, Restrictions.eq("PROD_MAN_STATUS", "ACTIVE"));
        cr.createCriteria("STPRODMAN.ST_MANUFACTURER", "STMAN", JoinType.LEFT_OUTER_JOIN);
        cr.createCriteria("ST_PRODUCT.ST_PRODUCT_SUPPLIER_PARENT", "STPRODSUP", JoinType.LEFT_OUTER_JOIN, Restrictions.eq("PROD_SUPPLIER_STATUS", "ACTIVE"));
        cr.createCriteria("STPRODSUP.ST_SUPPLIER", "STSUP", JoinType.LEFT_OUTER_JOIN);
        cr.createCriteria("ST_PRODUCT.ST_PRODUCT_RATES", "ZSTPR", JoinType.LEFT_OUTER_JOIN,  Restrictions.eqProperty("ST_SUPPLIER.SUPPLIER_ID", "STSUP.SUPPLIER_ID"));
        cr.createCriteria("ST_PRODUCT.ST_MEDICINE_CATEGORY", "MEDI_CAT", JoinType.LEFT_OUTER_JOIN);
        cr.createCriteria("ST_PRODUCT.ST_TAX_MASTER", "TAX", JoinType.INNER_JOIN);
        cr.createCriteria("ST_PRODUCT.ST_UNIT", "UNIT", JoinType.INNER_JOIN);
        cr.add(Restrictions.eq("ST_PRODUCT.PRODUCT_STATUS", "ACTIVE"));


        ProjectionList p1 = Projections.projectionList();
        p1.add(Projections.property("ST_PRODUCT.PRODUCT_ID"));
        p1.add(Projections.property("ST_PRODUCT.PRODUCT_NAME"));
        p1.add(Projections.property("UNIT.UNIT_DISPLAY_UNIT"));
        p1.add(Projections.property("TAX.TAX_PURCHASE"));
        p1.add(Projections.property("ST_PRODUCT.PRODUCT2_DISCOUNT"));
        p1.add(Projections.property("ST_PRODUCT.PRODUCT2_DIS_AMOUNT"));
        p1.add(Projections.property("ST_MEDICINE_CATEGORY.CAT_CATEGORY_ID"));
        p1.add(Projections.property("MEDI_CAT.CAT_SHORT_NAME"));
        p1.add(Projections.property("UNIT.UNIT_ID"));
        p1.add(Projections.property("TAX.TAX_ID"));
        //MANUFACTURER
        p1.add(Projections.property("STMAN.MAN_ID"));
        p1.add(Projections.property("STPRODMAN.BASE_LEVEL"));
        p1.add(Projections.property("STPRODMAN.FREE_QTY"));
        //SUPPLIER
        p1.add(Projections.property("STSUP.SUPPLIER_ID"));
        p1.add(Projections.property("STPRODSUP.BASE_LEVEL"));
        p1.add(Projections.property("STPRODSUP.FREE_QTY"));

        cr.setProjection(p1);
        List l = cr.list();
        System.out.println("Size items::" + l.size());

错误SqlExceptionHelper:131-“on子句”中的未知列“stsup4\u.SUPPLIER\u ID”
这是Hibernate生成的SQL
    select
    this_.PRODUCT_ID as y0_,
    this_.PRODUCT_NAME as y1_,
    unit8_.UNIT_DISPLAY_UNIT as y2_,
    tax7_.TAX_PURCHASE as y3_,
    this_.PRODUCT2_DISCOUNT as y4_,
    this_.PRODUCT2_DIS_AMOUNT as y5_,
    this_.CAT_CATEGORY_ID as y6_,
    medi_cat6_.CAT_SHORT_NAME as y7_,
    unit8_.UNIT_ID as y8_,
    tax7_.TAX_ID as y9_,
    stman2_.MAN_ID as y10_,
    stprodman1_.BASE_LEVEL as y11_,
    stprodman1_.FREE_QTY as y12_,
    stsup4_.SUPPLIER_ID as y13_,
    stprodsup3_.BASE_LEVEL as y14_,
    stprodsup3_.FREE_QTY as y15_
from
    ST_PRODUCTMASTER this_
left outer join
    ST_MEDICINE_CATEGORY medi_cat6_
        on this_.CAT_CATEGORY_ID=medi_cat6_.CAT_CATEGORY_ID
left outer join
    ST_PRODUCT_MANUFACTURER stprodman1_
        on this_.PRODUCT_ID=stprodman1_.PRODUCT_ID
        and (
            stprodman1_.PROD_MAN_STATUS=?
        )
left outer join
    ST_MANUFACTURER stman2_
        on stprodman1_.MAN_ID=stman2_.MAN_ID
left outer join
    ST_PRODUCT_RATES zstpr5_
        on this_.PRODUCT_ID=zstpr5_.PRODUCT_ID
        and (
            zstpr5_.SUPPLIER_ID=stsup4_.SUPPLIER_ID
        )
left outer join
    ST_PRODUCT_SUPPLIER stprodsup3_
        on this_.PRODUCT_ID=stprodsup3_.PRODUCT_ID
        and (
            stprodsup3_.PROD_SUPPLIER_STATUS=?
        )
left outer join
    ST_SUPPLIER stsup4_
        on stprodsup3_.SUPPLIER_ID=stsup4_.SUPPLIER_ID
inner join
    ST_TAX_MASTER tax7_
        on this_.TAX_ID=tax7_.TAX_ID
inner join
    ST_UNIT unit8_
        on this_.UNIT_ID=unit8_.UNIT_ID
where
    (
        this_.PRODUCT_DELETED <> 'DELETED'
    )
    and this_.PRODUCT_STATUS=?

在stsup4之前生成zstp5_
因此zstp5_u.SUPPLIER_uid=stsup4_u.SUPPLIER_uid
无法访问
 left outer join
    ST_PRODUCT_RATES zstpr5_
        on this_.PRODUCT_ID=zstpr5_.PRODUCT_ID
        and (
            zstpr5_.SUPPLIER_ID=stsup4_.SUPPLIER_ID
        )
left outer join
    ST_PRODUCT_SUPPLIER stprodsup3_
        on this_.PRODUCT_ID=stprodsup3_.PRODUCT_ID
        and (
            stprodsup3_.PROD_SUPPLIER_STATUS=?
        )
left outer join
    ST_SUPPLIER stsup4_
        on stprodsup3_.SUPPLIER_ID=stsup4_.SUPPLIER_ID

实际预期订单为
left outer join
    ST_PRODUCT_SUPPLIER stprodsup3_
        on this_.PRODUCT_ID=stprodsup3_.PRODUCT_ID
        and (
            stprodsup3_.PROD_SUPPLIER_STATUS=?
        )
left outer join
    ST_SUPPLIER stsup4_
        on stprodsup3_.SUPPLIER_ID=stsup4_.SUPPLIER_ID
left outer join
    ST_PRODUCT_RATES zstpr5_
        on this_.PRODUCT_ID=zstpr5_.PRODUCT_ID
        and (
            zstpr5_.SUPPLIER_ID=stsup4_.SUPPLIER_ID
        )

Hibernate正在生成一个表名为的数字,
1,2,3_
即使在创建SQL时,不维护这个顺序,
它甚至不是按照我们给出的顺序创造的。

最佳答案

由于您还没有发布映射文件,我将解释Hibernate如何对连接进行排序。
Hibernate使用映射xml文件来确定连接的顺序,因此如果该表中有一个关系(一对多或任何其他标记),只需将其移到另一个表的标记之上。
你可以阅读更多关于它的here

10-07 19:04
查看更多