Hibernate-Criteria学习笔记

「爱情、让人受尽委屈。」 2021-10-18 07:12 433阅读 0赞

# hibernate\_jpa注解 #

> 目前最新版的hibernate，5.2，底层整合了jpa，用[idea的hibernate工具生成实体][idea_hibernate]时，实体包含了注解的配置文件，缺一不可

如，用户类实体，生成之后是这样子的  
![1622529-20190804162258012-455446086.png][]

![1622529-20190804162321165-780651703.png][]

![1622529-20190804162401283-51449520.png][]

cascade属性的可能值有     
    all: 所有情况下均进行关联操作，即save-update和delete。     
    none: 所有情况下均不进行关联操作。这是默认值。      
    save-update: 在执行save/update/saveOrUpdate时进行关联操作。      
    delete: 在执行delete 时进行关联操作。      
    all-delete-orphan: 当一个节点在对象图中成为孤儿节点时，删除该节点。
    比如在一个一对多的关系中，Student包含多个book，当在对象关系中删除一个book时，
    此book即成为孤儿节点。

# hibernate\_Criteria用法『[转][Link 1]』 #

### 外键查询 ###

1.  对于外键本身
    
        public Resume findByUserId(Long id) {
            Criteria cr=getCurrentSession().createCriteria(Resume.class);
            cr.add(Restrictions.eq("user.id",id));
            return (Resume) cr.list().get(0);
        }
    
     
2.  对于外键对象的属性
    
        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用法『[转][Link 2]』 #

<table style="border-color:#000000;border-width:0px;border-style:solid;"> 
 <tbody> 
 <tr> 
 <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 方法</td> 
 <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 说明</td> 
 </tr> 
 <tr> 
 <td>Restrictions.eq</td> 
 <td>＝</td> 
 </tr> 
 <tr> 
 <td>Restrictions.allEq</td> 
 <td>利用Map来进行多个等于的限制</td> 
 </tr> 
 <tr> 
 <td>Restrictions.gt</td> 
 <td>＞</td> 
 </tr> 
 <tr> 
 <td>Restrictions.ge</td> 
 <td>＞＝</td> 
 </tr> 
 <tr> 
 <td>Restrictions.lt</td> 
 <td>＜</td> 
 </tr> 
 <tr> 
 <td>Restrictions.le</td> 
 <td>＜＝</td> 
 </tr> 
 <tr> 
 <td>Restrictions.between</td> 
 <td>BETWEEN</td> 
 </tr> 
 <tr> 
 <td>Restrictions.like</td> 
 <td>LIKE</td> 
 </tr> 
 <tr> 
 <td>Restrictions.in</td> 
 <td>in</td> 
 </tr> 
 <tr> 
 <td>Restrictions.and</td> 
 <td>and</td> 
 </tr> 
 <tr> 
 <td>Restrictions.or</td> 
 <td>or</td> 
 </tr> 
 <tr> 
 <td>Restrictions.isNull</td> 
 <td>判断属性是否为空,为空则返回true</td> 
 </tr> 
 <tr> 
 <td>Restrictions.isNotNull</td> 
 <td>与isNull相反</td> 
 </tr> 
 <tr> 
 <td>Restrictions.sqlRestriction</td> 
 <td>用SQL限定查询</td> 
 </tr> 
 <tr> 
 <td>Order.asc</td> 
 <td>根据传入的字段进行升序排序</td> 
 </tr> 
 <tr> 
 <td>Order.desc</td> 
 <td>根据传入的字段进行降序排序</td> 
 </tr> 
 <tr> 
 <td>MatchMode.EXACT</td> 
 <td>字符串精确匹配.相当于"like&nbsp;'value'"</td> 
 </tr> 
 <tr> 
 <td>MatchMode.ANYWHERE</td> 
 <td>字符串在中间匹配.相当于"like&nbsp;'%value%'"</td> 
 </tr> 
 <tr> 
 <td>MatchMode.START</td> 
 <td>字符串在最前面的位置.相当于"like&nbsp;'value%'"</td> 
 </tr> 
 <tr> 
 <td>MatchMode.END</td> 
 <td>字符串在最后面的位置.相当于"like&nbsp;'%value'"</td> 
 </tr> 
 </tbody> 
</table>

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;
 }

## 查询结果： ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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;
 }

## 查询结果： ##

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

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）存在外键约束；  
> 注：  
> 　　生成实体时，可以双向多对一，一个部门对应多个老师，但电脑吃不消  
> 　　也可以用单向一对一，只对教师进行关联，这个最简单，为使用这个  
> ![1622529-20190804163055706-880477195.png][]
> 
>   
> 要求：给一个部门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;
 }

转载于:https://www.cnblogs.com/HuangJie-sol/p/11017895.html

[idea_hibernate]: https://www.cnblogs.com/HuangJie-sol/p/10959447.html
[1622529-20190804162258012-455446086.png]: /images/20211018/5d4347a81f574b4aa2181c9b10cb976b.png
[1622529-20190804162321165-780651703.png]: /images/20211018/3ba2cbd00a6e4dc7b5c1cb80b145ed23.png
[1622529-20190804162401283-51449520.png]: /images/20211018/9ed0cf74e990479ca7e20d15b90d3a6c.png
[Link 1]: http://zhishichong.com/article/104037
[Link 2]: https://blog.csdn.net/cuiran/article/details/6324083
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211018/01d38b9bce45472eb2426a38e39d61d6.png
[1622529-20190804163055706-880477195.png]: /images/20211018/7c1356b56f0e4f998c04ccf5bb8bb761.png