java hql 多表查询语句,多表关联（二）-hql语句查询

class="java" name="code">

String hql="select new com.sinosoft.education.prototype.TeacherInfoBean(" +

"ti.teacherId," +

"ti.teacherName," +

"(select min(pt.teacSchoolStartTime) from ti.sysEduProjectTeachers pt where pt.teacSchoolEndTime >:endtime and pt.teacSchoolStartTime <=:endtime)," +

"(select min(pt.teacSchoolEndTime) from ti.sysEduProjectTeachers pt where pt.teacSchoolEndTime >:endtime and pt.teacSchoolStartTime <=:endtime)," +

"isnull((select avg(pl.lessonTeacherPoint) from ti.sysEduProjectLessons pl ),0.0)" +

") " +

"from SysTeaTeactherInfo ti left join ti.sysTeaProfFields pf " +

"where concat(',',trim(str(pf.fieldId)),',',pf.fieldFatherStr) like "+"'%,"+fieldId.toString()+",%'" ;

List teacherIds=this.findByNamedParam(hql,new String[]{"endtime"},new Object[]{DateUtil.parse("2013-11-29")});

for(TeacherInfoBean id:teacherIds)

{

System.out.println(id.getTeacherName());

System.out.println(id.getTeacherId());

System.out.println(id.getAvg());

System.out.println(DateUtil.toString(id.getComeTime()));

System.out.println(DateUtil.toString(id.getEndTime()));

}

String countHql="select count(*)"+

"from SysTeaTeactherInfo ti " +

"left join ti.sysTeaProfFields pf " +

"left join ti.sysEduProjectTeachers pt " +

"where concat(',',trim(str(pf.fieldId)),',',pf.fieldFatherStr) like "+"'%,"+fieldId.toString()+",%' " +

"and (pt.teacSchoolEndTime >=:endtime)or(pt.teacSchoolEndTime <= :endtime)" ;

System.out.println("=======================================");

return this.findByHqlOnPage(hql, page, countPerPage, new String[]{"endtime"},new Object[]{DateUtil.parse("2013-11-29")},countHql);

同时需要自定义teacherbean 且bean中的属性必须和hql语句中的元素顺序相同，否则可能会出现传值错误