多表关联（二）-hql语句查询
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<TeacherInfoBean> 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);