package Test;

import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;


import org.hibernate.Criteria;
import org.hibernate.Session;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;

import entity.Dept;
import entity.Emp;
import entity.QueryCondition;

import util.HibernateSessionFactory;

//criteria查询
public class TestCriteria {

    @Test
    public void Test1(){
        Session session =HibernateSessionFactory.getSession();
        try {
            Criteria criteria = session.createCriteria(Emp.class);
            Iterator<Emp> iter = criteria.list().iterator();
            while(iter.hasNext()){
                Emp emp=iter.next();
                System.out.println(emp.getEname());
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
    }
    /*    职位是工程师，如：job = ‘engineer’
    ?工资大于2000元，如：salary > 2000
    ?入职时间在2006年12月31日至2008年12月31日之间
    模糊查询的时候可以使用
    criteria动态查询
    */
    @Test
    public void Test2(){
        Session session =HibernateSessionFactory.getSession();
        try {
            Criteria criteria = session.createCriteria(Emp.class);
            QueryCondition queryCondition=new QueryCondition();
            queryCondition.setStartdate(new SimpleDateFormat("yyyy-MM-dd").parse("1980-12-17"));
            queryCondition.setEnddate(new SimpleDateFormat("yyyy-MM-dd").parse("1987-12-32"));
            Emp emp =new Emp();
            emp.setSal(2000D);
            //emp.setJob("SALESMAN");
            if(queryCondition.getStartdate()!=null){
                criteria.add(Restrictions.gt("hiredate",queryCondition.getStartdate() ));
            }
            if(queryCondition.getEnddate()!=null){
                criteria.add(Restrictions.lt("hiredate",queryCondition.getEnddate() ));
            }
            if(emp.getSal()!=null){
                criteria.add(Restrictions.gt("sal", 2000D));
            }
            if(emp.getJob()!=null){
                criteria.add(Restrictions.eq("job", "SALESMAN"));
            }
            
            List<Emp> emplist = criteria.list();
            for (Emp emp1 : emplist) {
                System.out.println(emp1.getJob()+"\t"+emp1.getSal()+"\t"+emp1.getHiredate());
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
    }
    
    /*    职位是工程师，如：job = ‘engineer’
    ?工资大于2000元，如：salary > 2000
    ?入职时间在2006年12月31日至2008年12月31日之间
    模糊查询的时候可以使用,加上分页
    */
    @Test
    public void Test3(){
        Session session =HibernateSessionFactory.getSession();
        try {
            Criteria criteria = session.createCriteria(Emp.class);
            QueryCondition queryCondition=new QueryCondition();
            queryCondition.setStartdate(new SimpleDateFormat("yyyy-MM-dd").parse("1980-12-17"));
            queryCondition.setEnddate(new SimpleDateFormat("yyyy-MM-dd").parse("1987-12-32"));
            Emp emp =new Emp();
            emp.setSal(2000D);
            //emp.setJob("SALESMAN");
            if(queryCondition.getStartdate()!=null){
                criteria.add(Restrictions.gt("hiredate",queryCondition.getStartdate() ));
            }
            if(queryCondition.getEnddate()!=null){
                criteria.add(Restrictions.lt("hiredate",queryCondition.getEnddate() ));
            }
            if(emp.getSal()!=null){
                criteria.add(Restrictions.gt("sal", 2000D));
            }
            if(emp.getJob()!=null){
                criteria.add(Restrictions.eq("job", "SALESMAN"));
            }
            //设置每页显示多少条
            int pageSize=2;
            int pageNo=2;//当前第几页
            //得出总页数
            int totalCount =(Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();
            int totalPage = totalCount%pageSize==0?totalCount/pageSize:totalCount/pageSize+1;
            criteria.setProjection(null);
            criteria.setFirstResult(pageNo);//第几页的查询
            criteria.setMaxResults(pageSize);//最多显示几条
            
            List<Emp> emplist = criteria.list();
            for (Emp emp1 : emplist) {
                System.out.println(emp1.getJob()+"\t"+emp1.getSal()+"\t"+emp1.getHiredate());
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
    }
    
    /*criteria排序
     * 按员工工资降序排列
     * */
    @Test
    public void test4(){
        Session session =HibernateSessionFactory.getSession();
        try {
            Criteria criteria = session.createCriteria(Emp.class,"e");
            criteria.add(Restrictions.gt("sal", 2000D));
            //criteria.addOrder(Order.asc("sal"));//按工资升序
            criteria.addOrder(Order.desc("empno"));//按工号降序
            List<Emp> emplist = criteria.list();
            for (Emp emp : emplist) {
                System.out.println(emp.getEmpno()+"\t"+emp.getEname()+"\t"+emp.getSal());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*查询工资最高的员工*/
    @Test
    public void test5(){
        Session session =HibernateSessionFactory.getSession();
        try {
            Object obj=session.createCriteria(Emp.class,"e")
                    .add(Restrictions.isNotEmpty("e.sal"))
                    .addOrder(Order.desc("e.sal"))
                    .setMaxResults(1)
                    .uniqueResult();
            
            System.out.println(obj.toString());
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*联合查询
     * 
     * MatchMode.START：字符串在最前面的位置.相当于"like ‘key%‘"
MatchMode.END：字符串在最后面的位置.相当于"like ‘%key‘"
MatchMode.ANYWHERE：字符串在中间匹配.相当于"like ‘%key%‘"
MatchMode.EXACT：字符串精确匹配.相当于"like ‘key‘"
     * */
    @SuppressWarnings("unchecked")
    @Test
    public void test6(){
        Session session =HibernateSessionFactory.getSession();
        try {
            List<Emp> emplist=session.createCriteria(Emp.class,"e")
                    .add(Restrictions.ilike("e.ename", "A",MatchMode.ANYWHERE))
                    .createAlias("dept", "d").add(Restrictions.eq("d.dname", "SALES")).list();
            
            for (Emp emp : emplist) {
                System.out.println(emp.getEname()+"\t"+emp.getDept().getDname());
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 查询部门名称
     * 投影查询：属性查询
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test7(){
        Session session =HibernateSessionFactory.getSession();
        try {
            List<String> deptlist = session.createCriteria(Dept.class).setProjection(Property.forName("dname")).list();
            
            for (String string : deptlist) {
                System.out.println(string);
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 查询部门名称
     * 投影查询：查询员工姓名、工资和部门
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test8(){
        Session session =HibernateSessionFactory.getSession();
        try {
            List<Object[]> deptlist = session.createCriteria(Emp.class,"e")
                    .createAlias("e.dept", "d")
                    .setProjection(Projections.projectionList()
                            .add(Property.forName("e.ename"))
                            .add(Property.forName("e.sal"))
                            .add(Property.forName("d.dname"))).list();
            System.out.println(deptlist.size());
            for (Object[] obj : deptlist) {
                System.out.println(obj[0] + "，"+obj[1]+","+obj[2]);
            }
            
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 统计各个部门的平均工资、最高工资、最低工资
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test9(){
        Session session =HibernateSessionFactory.getSession();
        try {
            List<Object[]> deptlist = session.createCriteria(Emp.class,"e")
                    .createAlias("e.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();
            System.out.println("各部门平均工资、最高工资、最低工资是：============");
            for (Object[] obj : deptlist) {
                
                System.out.println(obj[0] + "，"+obj[1]+","+obj[2]);
            }
            
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 使用DetachedCriteria查询财务部的姓名包括“a”的员工
     * DetachedCriteria 和 Criteria 功能类似
?Criteria是由Session对象创建的，
DetachedCriteria创建时不需要Session对象
?可以把DetachedCriteria作为方法参数传递来构造查
询条件
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test10(){
        Session session =HibernateSessionFactory.getSession();
        try {
            DetachedCriteria detachedCriteria=DetachedCriteria.forClass(Emp.class,"e")
                    .createAlias("e.dept", "d")
                    .add(Restrictions.eq("d.dname", "SALES"))
                    .add(Restrictions.ilike("e.ename", "a",MatchMode.ANYWHERE));
            List<Emp> list = detachedCriteria.getExecutableCriteria(session).list();
            System.out.println("使用DetachedCriteria查询财务部的姓名包括“a”的员工==============");
            for (Emp emp : list) {
                System.out.println(emp.getEname()+"\t"+emp.getDept().getDname());
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     * 使用DetachedCriteria作为查询条件，查询工资高于平均工资的员工
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test11(){
        Session session =HibernateSessionFactory.getSession();
        try {
            DetachedCriteria avgsal=DetachedCriteria.forClass(Emp.class,"e")
                    .setProjection(Projections.avg("e.sal"));
            List<Emp> list = session.createCriteria(Emp.class,"e")
            .createAlias("e.dept", "d")
            .add(Property.forName("e.sal").gt(avgsal)).list();
            for (Emp emp : list) {
                System.out.println(emp.getEname()+"\t"+emp.getSal());
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}