I have Client table with ont-to-many with Comment table as the following:
Client table:
Id name status....
Comment table:
Id description created_date client_id
this is what i tried:
@Entity
@Table(name = "client")
public class Client implements Serializable {
/** The comments. */
private Set<Comment> comments = new HashSet<Comment>(0);
@OneToMany(fetch = FetchType.EAGER, mappedBy = "client", cascade = CascadeType.ALL)
@OrderBy(clause = "createdDate")
//@Fetch(FetchMode.SELECT)
public Set<Comment> getComments() {
return comments;
}
}
@Entity
@Table(name = "comment")
public class Comment implements Serializable {
/** The created date. */
private Date createdDate;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CLIENT_ID", nullable = false)
public Client getClient() {
return client;
}
/**
* Sets the client.
*
* @param client
* the new client
*/
public void setClient(Client client) {
this.client = client;
}
}
public List<Client> getLastEditedDateFromComment() {
try {
Calendar cal = Calendar.getInstance();
//cal.add(Calendar.HOUR_OF_DAY, 2);
cal.add(Calendar.DATE, -7);
//.add(Restrictions.le("comments.editedDate", cal.getTime()))
//System.out.println("ClientDAO.getLastEditedDateFromComment()"+cal.getTime());
startOperation();
Criteria criteria = session
.createCriteria(Client.class)
.createAlias("comments", "comments")
.add(Restrictions.eq("status", "Pending"))
.add(Restrictions.le("comments.createdDate", cal.getTime()))
.setProjection(
Projections.distinct(Projections.projectionList()
.add(Projections.groupProperty("clientName"))
.add(Projections.groupProperty("id"))
.add(Projections.groupProperty("status"))
.add(Projections.property("clientName"), "clientName")
.add(Projections.property("id"), "id")
.add(Projections.property("status"), "status")
.add(Projections.max("comments.createdDate"))));
// .setProjection(Projections.max("comments.editedDate"));
// Integer maxAge = (Integer)criteria.uniqueResult();
List<Client> clist = criteria.setResultTransformer(Transformers.aliasToBean(Client.class))
.list();
tx.commit();
return clist;
}
}
the above code generates:
select distinct this_.clientName as y0_, this_.ID as y1_, this_.status as y2_, this_.clientName as y3_, this_.ID as y4_, this_.status as y5_, max(comments1_.createdDate) as y6_ from zyhnavkp.client this_ inner join zyhnavkp.comment comments1_ on this_.ID=comments1_.CLIENT_ID where this_.status=? and comments1_.createdDate<=? group by this_.clientName, this_.ID, this_.status
I need a query to get all clients with the created_date passed 1 week ONLY from comment table where status ='Pending', I'm trying to do this hibernate, any sql query is acceptable :)
Thanks in advance :)
Aucun commentaire:
Enregistrer un commentaire