Hibernate分页和投影查询

uniqueResult()方法

public Long obtainRowCount(double salary) {
        String hql = "select count(empNo) from Emp emp where emp.salary>=:salary";
        return (Long) this.getCurrentSession().createQuery(hql).setParameter("salary", salary).uniqueResult();
    }

Hibernate:
select
count(emp0_.empNo) as col_0_0_
from
project.Emp emp0_
where
emp0_.salary>=?

分页查询

 public List<Emp> findByPage(int pageNo,int pageSize)
    {
        if (pageNo>=1&&pageSize>=0) {
            return this.getCurrentSession().createQuery("from Emp order by empNo")
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .list();
        }
        else {
            return null;
        }
    }

Hibernate:
select
emp0_.empNo as empNo1_,
emp0_.empName as empName1_,
emp0_.job as job1_,
emp0_.salary as salary1_
from
project.Emp emp0_
order by
emp0_.empNo limit ?

 int totalPages=(int)((count%pageSize==0)?(count/pageSize):(count/pageSize+1));

投影查询

• 每条查询结果仅包含一个结果列

public List<String> selectEmp()
    {
       return this.getCurrentSession().createQuery("select emp.empName from Emp  as emp ").list();
    }

Hibernate:
select
emp0_.empName as col_0_0_
from
project.Emp emp0_

• 每条查询结果包含不止一个结果列

public List<Object[]> selectEmp()
    {
       return this.getCurrentSession().createQuery("select emp.empName ,emp.job from Emp  as emp ").list();
    }

Hibernate:
select
emp0_.empName as col_0_0_,
emp0_.job as col_1_0_
from
project.Emp emp0_

• 每个查询结果通过构造方法封装成对象（需要有对应的构造方法）

public List<Emp> selectEmp()
    {
       return this.getCurrentSession().createQuery("select new Emp(empNo,empName,job,salary) from Emp ").list();
    }

Hibernate:
select
emp0_.empNo as col_0_0_,
emp0_.empName as col_1_0_,
emp0_.job as col_2_0_,
emp0_.salary as col_3_0_
from
project.Emp emp0_

总结
查询中用到表达式和聚合函数得到计算列的特殊结果或者查询的目的是为了展示，不需要保持持久化维持数据，都应该用投影查询减少开销。