public List<TourScheduleWithClientCount> searchByDateWithCount(Date from, Date to) {
String sql = "SELECT NEW com.package.TourScheduleWithClientCount(ts,COUNT(ta.clientId)) FROM TourSchedule ts LEFT JOIN ts.tourAppliedList ta WHERE "
+ (to != null ? "ts.fromDate <= :to AND " : "")
+ (from != null ? "ts.fromDate >= :from AND " : "")
+ " ts.dFlg = 0 GROUP BY ts.id ORDER BY ts.fromDate DESC";
Query query = em.createQuery(sql);
if (from != null) {
query.setParameter("from", from);
}
if (to != null) {
query.setParameter("to", to);
}
return query.getResultList();
}
public class TourScheduleWithClientCount {
public TourScheduleWithClientCount() {
}
public TourScheduleWithClientCount(TourSchedule tourSchedule, Long count) {
super();
this.schedule = tourSchedule;
this.numberOfClients = count;
}
private TourSchedule schedule;
private Long numberOfClients;
public TourSchedule getSchedule() {
return schedule;
}
public void setSchedule(TourSchedule schedule) {
this.schedule = schedule;
}
public Long getNumberOfClients() {
return numberOfClients;
}
public void setNumberOfClients(Long numberOfClients) {
this.numberOfClients = numberOfClients;
}
}
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#JOIN