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 ?
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.