Cannot access JPA joined Entity with HQL

Question

I have 2 ORM classes one is named OpExAlloc and the other is called VesselManComp.

The first one has a @ManyToOne relationship with the the second one not on its primary key but on custom columns.

This is the code in the OpExAlloc class

@Fetch(FetchMode.JOIN)
    @JoinColumns({
            @JoinColumn(name = "companyId", referencedColumnName = "companyId",  insertable = false, updatable = false),
            @JoinColumn(name = "shipCode", referencedColumnName = "code", insertable = false, updatable = false) })
    @ManyToOne(fetch = FetchType.EAGER)
    private VesselManComp vesselManComp;

    public VesselManComp getVesselManComp() {
        return this.vesselManComp;
    }
    public void setVesselManComp(VesselManComp vesselManComp) {
        this.vesselManComp = vesselManComp;
    }

and this is the HQL (that used to work in older version of Hibernate and XML configuration instead on annotations)

    String qs = " select ca.id.docId, ca.id.itemId, ca.id.shipCode, ca.account, "
            + " sum(isnull(ca.quantity, 0)) as quantity, "
            + " sum(isnull(ca.directAmtLocal,0) + isnull(ca.indirectAmtLocal,0) + isnull(ca.nationalTaxLocal,0)) as amountLocal, "
            + " sum(ca.directAmtUsd + ca.indirectAmtUsd + isnull(ca.nationalTaxUsd, 0)) as amountUSD, "
            + " min(ca.id.allocDate) as minDate, "
            + " max(ca.id.allocDate) as maxDate "
            + " from OpExAlloc"
            + " as ca "
            + " where ca.id.companyId = :companyId "
            + " and ca.id.ssId = :sourceSystemId "
            + " and ca.id.type in (:documentTypes) "
            + " and (ca.opexTypeId is null or ca.opexTypeId not in (14, 15)) "
            + " and ca.vesselManComp.code is not null ";

The error that I get is that

java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: code of: com.companyname.orm.sqlserver.aether.dbo.OpExAlloc [ select ca.id.docId, ca.id.itemId, ca.id.shipCode, ca.account,  sum(isnull(ca.quantity, 0)) as quantity,  sum(isnull(ca.directAmtLocal,0) + isnull(ca.indirectAmtLocal,0) + isnull(ca.nationalTaxLocal,0)) as amountLocal,  sum(ca.directAmtUsd + ca.indirectAmtUsd + isnull(ca.nationalTaxUsd, 0)) as amountUSD,  min(ca.id.allocDate) as minDate,  max(ca.id.allocDate) as maxDate  from com.companyname.orm.sqlserver.aether.dbo.OpExAlloc as ca  where ca.id.companyId = :companyId  and ca.id.ssId = :sourceSystemId  and ca.id.type in (:documentTypes)  and (ca.opexTypeId is null or ca.opexTypeId not in (14, 15))  and ca.vesselManComp.code is not null  and ca.id.allocDate >= :allocationDateFrom  and ca.id.allocDate < :allocationDateTo  group by ca.id.docId, ca.id.itemId, ca.id.shipCode, ca.account ]
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)

and the weird fact is that Hibernate looks for "code" property in OpExAlloc object instead of the VesselManComp object.

If I change the query to add a join fetch on the nested object

    String qs = " select ca.id.docId, ca.id.itemId, ca.id.shipCode, ca.account, "
            + " sum(isnull(ca.quantity, 0)) as quantity, "
            + " sum(isnull(ca.directAmtLocal,0) + isnull(ca.indirectAmtLocal,0) + isnull(ca.nationalTaxLocal,0)) as amountLocal, "
            + " sum(ca.directAmtUsd + ca.indirectAmtUsd + isnull(ca.nationalTaxUsd, 0)) as amountUSD, "
            + " min(ca.id.allocDate) as minDate, "
            + " max(ca.id.allocDate) as maxDate "
            + " from OpExAlloc"
            + " as ca "
            + " join fetch ca.vesselManComp vmc "
            + " where ca.id.companyId = :companyId "
            + " and ca.id.ssId = :sourceSystemId "
            + " and ca.id.type in (:documentTypes) "
            + " and (ca.opexTypeId is null or ca.opexTypeId not in (14, 15)) "
            + " and vmc.code is not null ";

I get a Null Pointer Exception

java.lang.NullPointerException
    at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:424)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3921)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3707)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3585)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:720)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:576)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)

Can someone please explain what I am doing wrong? This is a migration of our project the old one using Hibernate 3 with XML configuration for the classes and the query was working just fine.

The new one uses hibernate 5.2 with annotation configuration for the classes.

Best Regards and thank you, George

Both the column name in the database and the property name for the VesselManComp table/orm entity are "code" all lowercase — gbougiakas, Nov 08 '17 at 15:12
This may be stupid remark since not so familiar with HQL: in your HQL is _ca.vesselManComp.code is not null_ and in Exception is _vesselManComp.code is not null_ other fields still referenced by _ca._. Should it be so ? — pirho, Nov 08 '17 at 16:30
my original post had vesselManComp.code and then I edited and added the ca. prefix and did not edit the exception as well. anyway either with ca. or not the same error occurs. I just edited the exception to reflect the code. — gbougiakas, Nov 08 '17 at 16:41

score 0 · Accepted Answer · answered Nov 10 '17 at 10:25

It is possible that your @ManyToOne annotations are completely disregarded. I noticed that the annotations are positioned on the field. What is the access type that is used in the specific class hierarchy (OpExAlloc class)? Could you post the whole class? By default the access type of a class hierarchy is defined by the position of the @Id or @EmbeddedId annotations. If these annotations are on the getter, then the @ManyToOne annotations should be on the getter as well. There is a relevant post Where to put hibernate annotations?

Thanks for the feedback. You are absolutely correct the annotations were completely ignored when they were put above the property/field because the id annotations were put on top of the getter. Now I need to find out why the JOIN annotations are wrong. — gbougiakas, Nov 10 '17 at 12:55

Cannot access JPA joined Entity with HQL

1 Answers1