Hibernate-Criteria学习笔记
hibernate_jpa注解
目前最新版的hibernate，5.2，底层整合了jpa，用idea的hibernate工具生成实体时，实体包含了注解的配置文件，缺一不可
如，用户类实体，生成之后是这样子的
cascade属性的可能值有
all: 所有情况下均进行关联操作，即save-update和delete。
none: 所有情况下均不进行关联操作。这是默认值。
save-update: 在执行save/update/saveOrUpdate时进行关联操作。
delete: 在执行delete 时进行关联操作。
all-delete-orphan: 当一个节点在对象图中成为孤儿节点时，删除该节点。
比如在一个一对多的关系中，Student包含多个book，当在对象关系中删除一个book时，
此book即成为孤儿节点。
hibernate_Criteria用法『转』
外键查询
- 对于外键本身
public Resume findByUserId(Long id) { Criteria cr=getCurrentSession().createCriteria(Resume.class); cr.add(Restrictions.eq("user.id",id)); return (Resume) cr.list().get(0); }
- 对于外键对象的属性
public Resume findByAttr(Attr attr) { Criteria cr=getCurrentSession().createCriteria(Resume.class); cr.createAlias("user","u"); cr.add(Restrictions.eq("u.attr",attr)); return cr.list(); }
hibernate_Restrictions用法『转』
| 方法 | 说明 |
| Restrictions.eq | ＝ |
| Restrictions.allEq | 利用Map来进行多个等于的限制 |
| Restrictions.gt | ＞ |
| Restrictions.ge | ＞＝ |
| Restrictions.lt | ＜ |
| Restrictions.le | ＜＝ |
| Restrictions.between | BETWEEN |
| Restrictions.like | LIKE |
| Restrictions.in | in |
| Restrictions.and | and |
| Restrictions.or | or |
| Restrictions.isNull | 判断属性是否为空,为空则返回true |
| Restrictions.isNotNull | 与isNull相反 |
| Restrictions.sqlRestriction | 用SQL限定查询 |
| Order.asc | 根据传入的字段进行升序排序 |
| Order.desc | 根据传入的字段进行降序排序 |
| MatchMode.EXACT | 字符串精确匹配.相当于"like 'value'" |
| MatchMode.ANYWHERE | 字符串在中间匹配.相当于"like '%value%'" |
| MatchMode.START | 字符串在最前面的位置.相当于"like 'value%'" |
| MatchMode.END | 字符串在最后面的位置.相当于"like '%value'" |
Disjunction：逻辑或【or】
Conjunction：逻辑与【and】
一、进行子查询并分页
背景：
有一张活动表（volunteer_activity）
和一张活动报名表（volunteer_sign_up）–存放志愿者活动报名记录
要求：查出志愿者参加的活动和未参加的活动
代码：
/** * 查询已加入的活动 * @param key * @param setPageSize * @return */ @Override public PageBean findAllVolunteerJoinActivity(String key, PageBean<VolunteerActivityEntity> setPageSize, String userId) { Session session = sessionFactory.openSession(); /** * hibernate 利用子查询实现 exists 功能 */ Criteria criteria = session.createCriteria(VolunteerActivityEntity.class, "activity"); DetachedCriteria detachedCriteria = DetachedCriteria.forClass(VolunteerSignUpEntity.class,"signUp"); detachedCriteria.add(Restrictions.eq("volunteerId", userId)); detachedCriteria.add(Property.forName("activity.activityId").eqProperty("signUp.activityId")); criteria.add(Subqueries.exists(detachedCriteria.setProjection(Projections.property("signUp.signUpId"))));//这里改为notExists就是查询未参加的活动了 if (key != null && !key.equals("")) { //搜索 List list = criteria.add( Restrictions.or( Restrictions.or(Restrictions.like("activityCode", key, MatchMode.ANYWHERE)), Restrictions.or(Restrictions.like("activityTitle", key, MatchMode.ANYWHERE)), Restrictions.or(Restrictions.like("activityContent", key, MatchMode.ANYWHERE)), Restrictions.or(Restrictions.like("activityLeader", key, MatchMode.ANYWHERE)))) .setFirstResult((setPageSize.getCurrPage() - 1) * setPageSize.getPageSize() ) .setMaxResults((setPageSize.getCurrPage() - 1) * setPageSize.getPageSize() + setPageSize.getPageSize()).list(); setPageSize.setRows(list); } else { setPageSize.setRows(criteria.setFirstResult((setPageSize.getCurrPage() - 1) * setPageSize.getPageSize()) .setMaxResults((setPageSize.getCurrPage() - 1) * setPageSize.getPageSize() + setPageSize.getPageSize()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list()); } session.close(); return setPageSize; }
查询结果：
Hibernate: select this_.activity_id as activity1_3_0_, this_.activity_code as activity2_3_0_, this_.activity_content as activity3_3_0_, this_.activity_end_time as activity4_3_0_, this_.activity_leader as activity5_3_0_, this_.activity_people_num as activity6_3_0_, this_.activity_sign_end_time as activity7_3_0_, this_.activity_sign_start_time as activity8_3_0_, this_.activity_start_time as activity9_3_0_, this_.activity_title as activit10_3_0_ from volunteer_activity this_ where exists ( select signUp_.sign_up_id as y0_ from volunteer_sign_up signUp_ where signUp_.volunteer_id=? and this_.activity_id=signUp_.activity_id ) limit ?
二、利用逻辑对象进行多条件查询
背景：
有一张竞赛表（competition）
要求：给一个部门id，查出本部门的竞赛，并模糊参训
代码：
@Override public PageBean findByDept(Integer deptId, String key, PageBean<CompetitionEntity> pageBean) { Session session = sessionFactory.openSession(); Criteria criteria = session.createCriteria(CompetitionEntity.class); //TODO 伪外键查询--首要条件 criteria.add(Restrictions.eq("deptId", deptId)); if (key != null && !key.equals("")) { //搜索--实现：where deptId=? and (xxx like ? or xxx like ?...) Disjunction dis = Restrictions.disjunction();//多个or可以拼接 dis.add(Restrictions.like("itemName", key, MatchMode.ANYWHERE)); List list = criteria.add(dis) .setFirstResult((pageBean.getCurrPage() - 1) * pageBean.getPageSize() ) .setMaxResults(pageBean.getPageSize()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); pageBean.setRows(list); } else { pageBean.setRows( criteria.setFirstResult((pageBean.getCurrPage() - 1) * pageBean.getPageSize()) .setMaxResults(pageBean.getPageSize()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list()); } pageBean.setTotal(Math.toIntExact((Long) session.createCriteria(CompetitionEntity.class).add(Restrictions.eq("deptId", deptId)) .setProjection(Projections.rowCount()).uniqueResult())); session.close(); return pageBean; }
查询结果：
Hibernate: select this_.item_id as item_id1_2_0_, this_.awardee as awardee2_2_0_, this_.dept_id as dept_id3_2_0_, this_.dept_name as dept_nam4_2_0_, this_.item_name as item_nam5_2_0_, this_.match_id as match_id6_2_0_, this_.match_name as match_na7_2_0_, this_.prize_grade as prize_gr8_2_0_, this_.prize_img as prize_im9_2_0_, this_.prize_level as prize_l10_2_0_, this_.prize_time as prize_t11_2_0_, this_.teacher_id as teacher12_2_0_, this_.teacher_name as teacher13_2_0_ from competition this_ where this_.dept_id=? and ( this_.item_name like ? ) limit ?
三、外键查询
背景：
有一张教师表（sys_user）,与部门表（dept）存在外键约束；
注：
生成实体时，可以双向多对一，一个部门对应多个老师，但电脑吃不消
也可以用单向一对一，只对教师进行关联，这个最简单，为使用这个
要求：给一个部门id，查出本部门的竞赛，并模糊参训
代码：
@Override public PageBean findByDept(Integer deptId, String key, PageBean<TeacherEntity> pageBean) { Session session = sessionFactory.openSession(); Criteria criteria = session.createCriteria(TeacherEntity.class); Criteria criteriaForCount = session.createCriteria(TeacherEntity.class);//用于获取数据库记录数 //TODO 外键查询 criteria.createAlias("dept", "dept"). add(Restrictions.eq("dept.deptId", deptId)); criteriaForCount.createAlias("dept", "dept"). add(Restrictions.eq("dept.deptId", deptId)); if (key != null && !key.equals("")) { //搜索--实现：where deptId=? and (xxx like ? or xxx like ?...) Disjunction dis = Restrictions.disjunction();//多个or可以拼接 dis.add(Restrictions.like("teacherCode", key, MatchMode.ANYWHERE)); dis.add(Restrictions.like("teacherName", key, MatchMode.ANYWHERE)); List list = criteria.add(dis) .setFirstResult((pageBean.getCurrPage() - 1) * pageBean.getPageSize() ) .setMaxResults(pageBean.getPageSize()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list(); pageBean.setRows(list); } else { pageBean.setRows(criteria.setFirstResult((pageBean.getCurrPage() - 1) * pageBean.getPageSize()) .setMaxResults(pageBean.getPageSize()).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list()); } pageBean.setTotal(Math.toIntExact((Long) criteriaForCount.setProjection(Projections.rowCount()).uniqueResult())); session.close(); return pageBean; }
