Hibernate中hql三种方式实现分页关联查询

1.用户实体类   User.class

package com.system.user.entity.guard;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import org.hibernate.annotations.Cascade;
import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

import javax.persistence.*;
import java.util.Date;
import java.util.List;
import java.util.Set;

/**
 * @author f
 * @version 1.0
 * @Title: 用户管理
 * @Package
 */
@Data
@Entity
@Table(name = "sys_user")
public class User  {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", columnDefinition = "bigint NOT NULL COMMENT '自增主键'")
    private Long id;
    /**
     * ----用户名称---
     **/
    @Column(name = "name", columnDefinition = "varchar(64) NULL COMMENT '用户名称'")
    private String name;
    /**
     * ----登录账号---
     **/
    @Column(name = "login_name", columnDefinition = "varchar(64) NULL COMMENT '登录账号'")
    private String loginName;
    /**
     * ----登录密码---
     **/
    @Column(name = "pass_word", columnDefinition = "varchar(64) NULL COMMENT '登录密码'")
    private String passWord;

    /**
     * ----状态---
     **/
    @Column(name = "state", columnDefinition = "int NULL COMMENT '用户状态'")
    private Integer state;
     /**
     * ----创建时间---
     **/
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @Column(name = "create_time", columnDefinition = "datetime NULL ")
    protected Date createTime;
    /**
     * ----创建人ID---
     **/
    @Column(name = "create_id", columnDefinition = "bigint NULL ")
    protected Long createId;
    /**
     * ----创建人---
     **/
    @Transient
    protected String createName;


    public User() {
    }

    public User(Long id,String name, String loginName, Integer state, String remark, Date createTime, Long createId, String createName) {
        this.id = id;
        this.name = name;
        this.loginName = loginName;
        this.state = state;
        this.remark = remark;
        this.createTime = createTime;
        this.createId = createId;
        this.createName = createName;
    }
}

2.用户Dao   UserDao.class

package com.system.user.dao;
import com.base.database.hibernate.BaseDao;
import com.base.mvc.LayuiPage;
import com.system.user.entity.guard.User;
import com.system.user.entity.guard.UserCondition;
import org.hibernate.Query;
import org.hibernate.type.DateType;
import org.hibernate.type.IntegerType;
import org.hibernate.type.LongType;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class UserDao  {
	 @Autowired
    public BaseDao baseDao;

    /**
     * HQL分页列表关联查询查询方法
     *
     * @param obj:实体类
     * @param condition:查询条件及分页
     * @return
     */
    public LayuiPage<User> pageUserList(User obj, UserCondition condition) {
        String totalHql = "select count(1) ";
        String leftHql = "select new  User(us.id,us.name,us.loginName,us.state," +
                "us.remark, us.createTime,us.createId,a.name)";
        String hql = " from User us, User a  where  us.createId = a.id  ";
        Map<String, Object> param = new HashMap<>();
        // 用户名称
        if (!StringUtils.isEmpty(obj.getName())) {
            hql += " and us.name like :name ";
            param.put("name", "%" + obj.getName().trim() + "%");
        }
        //获取总条数
        Long total = baseDao.countByHql(totalHql + hql, param);
        //添加排序
        hql += " order by us.createTime desc";
        List<User> list = baseDao.findByHql(leftHql + hql, param, condition.getPageNum(), condition.getPageTotal());
        LayuiPage<User> ret = new LayuiPage<User>(total, list);
        return ret;
    }

    /**
     * SQL分页列表关联查询查询方法
     *
     * @param obj:实体类
     * @param condition:查询条件及分页
     * @return
     */
    public LayuiPage<User> pageUserListSql(User obj, UserCondition condition) {

        String totalSql = "select count(1) ";
        String leftSql = "SELECT us.id as id,us.name as name,us.login_name as loginName,us.state as state," +
                "us.remark as remark, us.create_time as createTime,us.create_id as createId,a.name as createName ";
        String sql = " from sys_user us " +
                "LEFT JOIN sys_user a ON us.create_id=a.id where 1=1 ";
        Map<String, Object> param = new HashMap<>();
        // 用户名称
        if (!StringUtils.isEmpty(obj.getName())) {
            sql += " and us.name like :name ";
            param.put("name", "%" + obj.getName().trim() + "%");
        }
        //获取总条数
        Long total = baseDao.countBySql(totalSql + sql, param);
        //添加排序
        sql += " order by us.create_time desc";
        Query query = baseDao.getCurrentSession().createSQLQuery(leftSql + sql)
                .addScalar("id", new LongType())
                .addScalar("name", new StringType())
                .addScalar("loginName", new StringType())
                .addScalar("state", new IntegerType())
                .addScalar("remark", new StringType())
                .addScalar("createTime", new DateType())
                .addScalar("createId", new LongType())
                .addScalar("createName", new StringType())
                .setFirstResult((condition.getPageNum() - 1) * condition.getPageTotal())
                .setMaxResults(condition.getPageTotal())
                .setResultTransformer(Transformers.aliasToBean(User.class));
        if (!param.isEmpty()) {
            for (String key : param.keySet()) {
                query.setParameter(key, param.get(key));
            }
        }
        List<User> list = query.list();
        LayuiPage<User> ret = new LayuiPage<User>(total, list);
        return ret;
    }
    /**
     * SQL分页列表关联查询查询方法 
     *
     * @param obj:实体类
     * @param condition:查询条件及分页
     * @return
     */
    public LayuiPage<Map<String, Object>> pageUserListSqlMap(User obj, UserCondition condition) {

        String leftSql = "SELECT us.id as id,us.name as name,us.login_name as loginName,us.state as state," +
                "us.remark as remark, us.create_time as createTime,us.create_id as createId,a.name as createName ";
        String sql = " from sys_user us " +
                "LEFT JOIN sys_user a ON us.create_id=a.id where 1=1 ";
        Map<String, Object> param = new HashMap<>();
        // 用户名称
        if (!StringUtils.isEmpty(obj.getName())) {
            sql += " and us.name like :name ";
            param.put("name", "%" + obj.getName().trim() + "%");
        }
        String totalSql = "select count(*) " + sql;
        //获取总条数
        Long total = baseDao.countBySql(totalSql, param);
        //添加排序
        sql += " order by us.create_time desc";
        List<Map<String, Object>> list = baseDao.findBySql(leftSql + sql, param, condition.getPageNum(), condition.getPageTotal());
        LayuiPage<Map<String, Object>> ret = new LayuiPage<Map<String, Object>>(total, list);
        return ret;
    }
}