hibernate 子查询，施用离线Criteria对象实现

hibernate 子查询，使用离线Criteria对象实现

最近做项目，有多个表关联查询。

sql语句

 sql=" select * from s_weektask wt where wt.week_id in(select
 w.week_id from s_week w inner join s_month m on m.month_id =
 w.month_id and w.month_id= 8 ) and wt.by_assessman_id=2;";

hibernate代码

DetachedCriteria monthDetachedCriteria = DetachedCriteria.forClass(SMonth.class, "m")
				.setProjection(Property.forName("monthId"))
				.add(Restrictions.eqProperty("w.SMonth.monthId", "m.monthId"));
		
		Criteria weekCriteria = super.getSession()
		.createCriteria(SWeek.class, "w");
		weekCriteria.add(Restrictions.eq("w.SMonth.monthId", monthId));
		weekCriteria.add(Property.forName("w.SMonth.monthId").in(monthDetachedCriteria));
		List<SWeek> weekList = weekCriteria.list();
		
		Criteria weektaskCriteria = super.getSession()
		.createCriteria(SWeektask.class, "wt");
		weektaskCriteria.add(Restrictions.eq("wt.byAssessmanId", userId));
		weektaskCriteria.add(Property.forName("wt.SWeek").in(weekList));
		List<SWeektask> weektaskList = weektaskCriteria.list();

时间段查询，加模糊查询

@SuppressWarnings("unchecked")
	public List<SMonthTaskrecord> queryMonthTaskrecordList(String startDate,
			String endDate, String keyStr) throws Exception {
		
		SimpleDateFormat df = new SimpleDateFormat("yyyy年MM月");
		Date sd = df.parse(startDate);
		Date ed = df.parse(endDate);
		Criteria userCriteria = super.getSession().createCriteria(SUser.class,
				"u");
		userCriteria.add(Restrictions.like("u.userName", "%" + keyStr + "%"));
		List<SUser> userList = userCriteria.list();
		List<Integer> uidList = new ArrayList<Integer>();
		for (int i = 0; i < userList.size(); i++) {
			uidList.add(userList.get(i).getUserId());
		}

		Criteria monthTaskrecordCriteria = super.getSession().createCriteria(
				SMonthTaskrecord.class, "mt");
		
		if (sd != null) { // 查询制定时间之后的记录
			monthTaskrecordCriteria.add(Restrictions.ge("createTime", sd));
		}
		if (ed != null) { // 查询指定时间之前的记录
			monthTaskrecordCriteria.add(Restrictions.le("createTime", ed));
		}
		if (uidList.size()>0) {
			monthTaskrecordCriteria.add(Property.forName("mt.byAssessmanId").in(
					uidList));
		}else {
			monthTaskrecordCriteria.add(Property.forName("mt.byAssessmanId").eq(0));
		}
		

		List<SMonthTaskrecord> monthTaskrecord = monthTaskrecordCriteria.list();
		return monthTaskrecord;
	}

 

 ****************************另外的举例*************************************

/**
	 * 离线Criteria
	 */
	@SuppressWarnings("unchecked")
	public static void two() {
		// 没有session也可以创建DetachedCriteria
		DetachedCriteria c1 = DetachedCriteria.forClass(Employee.class);
		// 用离线的Criteria处理子查询
		DetachedCriteria c2 = DetachedCriteria.forClass(Employee.class);
		c2.setProjection(Property.forName("salary").avg()); // 添加属性的平均值投影
		DetachedCriteria c3 = DetachedCriteria.forClass(Employee.class);
		c3.setProjection(Projections.distinct(Property.forName("dept.deptId"))); // 统计部门不重复数据

		Session session = HibernateUtil.getUtil().getSession();
		Transaction tran = session.beginTransaction();
		System.out.println("++++++++++++++++++++++++++++++++++++++++");
		try {
			List<Employee> emplist = c1.getExecutableCriteria(session).list();
			for (Employee employee : emplist) {
				System.out.println(employee);
			}
			System.out.println("++++++++++++++++++++++++++++++++++++++++");
			Criteria criteria = session.createCriteria(Employee.class).add(
					Property.forName("salary").lt(c2));
			emplist = criteria.list();
			for (Employee employee : emplist) {
				System.out.println(employee);
			}
			System.out.println("++++++++++++++++++++++++++++++++++++++++");
			criteria = session.createCriteria(Dept.class).add(
					Property.forName("deptId").notIn(c3));
			List<Dept> deptlist = criteria.list();
			for (Dept dept : deptlist) {
				System.out.println(dept);
			}
			System.out.println("++++++++++++++++++++++++++++++++++++++++");
			tran.commit();
		} catch (Exception e) {
			tran.rollback();
			e.printStackTrace();
		}
	}