多表关联（二）-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);

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