Hibernate Join using criteria and restrictions

Question

I have 2 entities as

PayoutHeader.java

@Entity
public class PayoutHeader extends GenericDomain implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;        
    @Column
    private Integer month;
    @Column
    private Integer year;
    @OneToOne
    private Bank bank;
    @Column
    private Double tdsPercentage;
    @Temporal(javax.persistence.TemporalType.DATE)
    private Date **chequeIssuedDate**;

    @Temporal(javax.persistence.TemporalType.DATE)
    private Date entryDate;

}

PayoutDetails .java

@Entity
public class PayoutDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;        

    @ManyToOne
    private PayoutHeader payoutHeader;

    @Column
    private Double amount;
    @Column
    private String bankName;
    @Temporal(javax.persistence.TemporalType.DATE)
    private Date clearingDate;
    @OneToOne    
    private Advisor advisor;

    @Column
    private Long **advisorId**;
}

I want to write query using Hibernate Criteria like

Select pd.* from PayoutDetails pd, PayoutHeader ph where pd.payoutheaderId = ph.id and pd.advisorId = 1 and and ph.chequeIssuedDate BETWEEN STR_TO_DATE('01-01-2011', '%d-%m-%Y') AND STR_TO_DATE('31-12-2011', '%d-%m-%Y') ";

I have written query like this

public List<PayoutDetails> getPayoutDetails(AdvisorReportForm advisorReportForm) {
        Criteria criteria = getSession().createCriteria(PayoutDetails.class);

        if (advisorReportForm.getAdvisorId() != null && advisorReportForm.getAdvisorId() > 0) {
            criteria.add(Restrictions.eq("advisorId", advisorReportForm.getAdvisorId().toString()));
        }

        criteria.setFetchMode("PayoutHeader", FetchMode.JOIN)
                .add(Restrictions.between("chequeIssuedDate", advisorReportForm.getFromDate(), advisorReportForm.getToDate()));        

        return criteria.list();
    }

But is giving error as

org.hibernate.QueryException: could not resolve property: chequeIssuedDate of: org.commission.domain.payout.PayoutDetails

I think is is trying to find chequeIssuedDate field in PayoutDetails, but this field is in PayoutHeader. How to specify alias during join ?

Don Roby · Accepted Answer · 2012-07-16T11:41:38.577

The criteria.setFetchMode("PayoutHeader", FetchMode.JOIN) just specifies that you want to get the header by a join, and in this case is probably unneeded. It doesn't change which table is used in the restrictions. For that, you probably want to create an additional criteria or an alias, more or less as follows:

public List<PayoutDetails> getPayoutDetails(AdvisorReportForm advisorReportForm) {
        Criteria criteria = getSession().createCriteria(PayoutDetails.class);

        if (advisorReportForm.getAdvisorId() != null && advisorReportForm.getAdvisorId() > 0) {
            criteria.add(Restrictions.eq("advisorId", advisorReportForm.getAdvisorId().toString()));
        }

        criteria.createCriteria("payoutHeader")
                .add(Restrictions.between("chequeIssuedDate", advisorReportForm.getFromDate(), advisorReportForm.getToDate()));        

        return criteria.list();
    }

or (using an alias)

public List<PayoutDetails> getPayoutDetails(AdvisorReportForm advisorReportForm) {
        Criteria criteria = getSession().createCriteria(PayoutDetails.class);

        if (advisorReportForm.getAdvisorId() != null && advisorReportForm.getAdvisorId() > 0) {
            criteria.add(Restrictions.eq("advisorId", advisorReportForm.getAdvisorId().toString()));
        }

        criteria.createAlias("payoutHeader", "header")
                .add(Restrictions.between("header.chequeIssuedDate", advisorReportForm.getFromDate(), advisorReportForm.getToDate()));        

        return criteria.list();
    }

See the Hibernate docs on Criteria Queries for examples of this.

It's also likely not appropriate to convert the advisorId to a string, as it is in fact a Long and probably mapped to a number field in sql.

It's common to also not map something like this advisorId field at all if you map the advisor, and use a restriction based on the advisor field, similarly to the way this deals with the payoutHeader field.

I wouldn't worry about getting all the fields from the header, but it may behave a bit differently if you get the createCriteria version to work.

I have tries First solution given by you, it gives error as "org.hibernate.QueryException: could not resolve property: payoutHeader of: org.commission.domain.payout.PayoutDetails" — Rahul Agrawal, Jul 16 '12 at 11:23
When I am printing generated SQL in select cause it has all columns from both the tables, I need only columns from PayoutDetails table. How to set projection for this ? — Rahul Agrawal, Jul 16 '12 at 11:32
Check that the case matches and try variants - hibernate is definitely case-sensitive. It may also be worth trying the alias version of the query, but it may have the same issues. Also I'm going to add some other notes on the advisor stuff, which shouldn't be causing this problem, but might cause other problems. — Don Roby, Jul 16 '12 at 11:32
Let's say the entity name is "a.b.c.AbcXyz" and Table name is "ABC_XYZ". What value should I use in criteria.createAlias? — manishrw, May 15 '19 at 11:55

Hibernate Join using criteria and restrictions

1 Answers1