最近做项目,有多个表关联查询。
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();
}
}