Hibernate-Criteria查询（2）动态查询、排序、分页、连接查询、投影查询、聚合函数

package junit.test;

import static org.junit.Assert.*;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import hibch06.dao.HibernateSessionFactory;
import hibch06.entity.Dept;
import hibch06.entity.Emp;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;



public class Hibch06Test2 {
    /*
     * 动态查询
     * 职位：经理
     * 工资大于2000
     * 入职时间：1981-4-1到1981-5-1
     */
    @Test
    public void test() throws ParseException {
        EmpCondition empCondition=new EmpCondition();
        empCondition.setJob("MANAGER");
        empCondition.setSal(2000D);
        empCondition.setHireDateStart(new SimpleDateFormat("yyyy-MM-dd").parse("1981-4-1"));
        empCondition.setHireDateEnd(new SimpleDateFormat("yyyy-MM-dd").parse("1981-5-1"));

        Session session=HibernateSessionFactory.getSession();
        Criteria criteria=session.createCriteria(Emp.class);
        //动态添加查询条件
        if(empCondition.getJob()!=null){
            criteria.add(Restrictions.ilike("job", empCondition.getJob(),MatchMode.ANYWHERE));
        }
        if(empCondition.getSal()!=null){
            criteria.add(Restrictions.gt("sal", empCondition.getSal()));
        }
        if(empCondition.getHireDateStart()!=null){
            criteria.add(Restrictions.ge("hiredate", empCondition.getHireDateStart()));
        }
        if(empCondition.getHireDateEnd()!=null){
            criteria.add(Restrictions.le("hiredate", empCondition.getHireDateEnd()));
        }


        List<Emp> list=criteria.list();
        for (Emp emp : list) {
            System.out.println(emp.getEname());
        }
    }
    /*
     * 排序
     */
    @Test
    public void test2(){
        Session session=HibernateSessionFactory.getSession();
        List<Emp> list=session.createCriteria(Emp.class)
                .addOrder(Order.desc("sal"))
                .addOrder(Order.asc("empno"))
                .list();
        for (Emp emp : list) {
            System.out.println(emp.getEmpno()+" "+emp.getEname()+" "+emp.getSal());
        }

    }

    /*
     * 分页
     */
    @Test
    public void test3(){
        Session session=HibernateSessionFactory.getSession();
        List<Emp> list=session.createCriteria(Emp.class)
                .setFirstResult((3-1)*5)
                .setMaxResults(5)
                .list();
        for (Emp emp : list) {
            System.out.println(emp.getEmpno()+" "+emp.getEname()+" "+emp.getSal());
        }
    }

    /*
     * 连接查询
     * 查询财务部中姓名含“a”的员工(方法一)
     */
    @Test
    public void test4(){
        Session session=HibernateSessionFactory.getSession();
        List<Emp> list=session.createCriteria(Emp.class)
                .add(Restrictions.ilike("ename", "a",MatchMode.ANYWHERE))
                .createCriteria("dept")
                .add(Restrictions.eq("dname", "ACCOUNTING"))
                .list();
        for (Emp emp : list) {
            System.out.println(emp.getEname());
        }

    }

    /*
     * 连接查询
     * 查询财务部中姓名含“a”的员工(方法二)
     */
    @Test
    public void test5(){
        Session session=HibernateSessionFactory.getSession();
        List<Emp> list=session.createCriteria(Emp.class,"e")
                .createAlias("dept", "d")
                .add(Restrictions.ilike("e.ename", "a",MatchMode.ANYWHERE))
                .add(Restrictions.eq("d.dname", "ACCOUNTING"))
                .list();
        for (Emp emp : list) {
            System.out.println(emp.getEname());
        }

    }

    /*
     * 投影查询 返回的结果类型是Object[]
     */
    @Test
    public void test6(){
        Session session=HibernateSessionFactory.getSession();
        List<Object[]> list=session.createCriteria(Emp.class)
        .setProjection(Projections.projectionList()
                .add(Property.forName("empno"))
                .add(Property.forName("ename"))
                .add(Property.forName("sal")))
                .list();
        for (Object[] o : list) {
            System.out.println(o[0]+" "+o[1]+" "+o[2]);
        }
    }

    /*
     * 统计各个部门的平均工资，最高工资，最低工资 
     */
    @Test
    public void test7(){
        Session session=HibernateSessionFactory.getSession();
        List<Object[]> list=session.createCriteria(Emp.class,"e")
                .createAlias("dept", "d")
                .setProjection(Projections.projectionList()
                        .add(Projections.groupProperty("d.dname"))//按部门名称分组
                        .add(Projections.avg("e.sal"))
                        .add(Projections.max("e.sal"))
                        .add(Projections.min("e.sal")))
                        .list();
        for (Object[] o : list) {
            System.out.println(o[0]+" "+o[1]+" "+o[2]+" "+o[3]);
        }       

    }
}