hibernate使用criteria进行子查询并分页

最新推荐文章于 2022-11-30 17:33:38 发布

weixin_30755709

最新推荐文章于 2022-11-30 17:33:38 发布

阅读量119

点赞数

文章标签： java

原文链接：http://www.cnblogs.com/HuangJie-sol/p/11017895.html

版权

背景：

有一张活动表（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 ?

View Code

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