package Test;

import java.util.ArrayList;

import java.util.List;

import org.hibernate.Criteria;

import org.hibernate.Session;

import org.hibernate.criterion.Criterion;

import org.hibernate.criterion.MatchMode;

import org.hibernate.criterion.Restrictions;

import org.junit.Test;

import com.sun.org.apache.regexp.internal.recompile;

import entity.Dept;

import entity.Emp;

import util.HibernateSessionFactory;

public class TestCriteria {

    /*

    通过criteria查询部门信息

    */

    @Test

    public void test1(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Dept.class);

        List<Dept> list=criteria.list();

        System.out.println("通过criteria查询部门信息");

        for (Dept dept : list) {

            System.out.println(dept.getDname());

        }

    }

    /**

     * 查询位置在二楼的部门

     */

    @Test

    public void test2(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Dept.class);

                //.add(Restrictions.eq("loc", "二楼"));

        Criterion criterion = Restrictions.eq("loc", "二楼");

        criteria.add(criterion);

        List<Dept> list=criteria.list();

        System.out.println("查询位置在二楼的部门");

        for (Dept dept : list) {

            System.out.println(dept.getDname()+"\t"+dept.getLoc());

        }

    }

    /**

     * 查询工资高于2000元的员工

     */

    @Test

    public void test3(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                .add(Restrictions.gt("sal", 2000D));

        List<Emp> list=criteria.list();

        System.out.println("查询工资高于2000元的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getSal());

        }

    }

    /**

     * 查询不属于任何部门的员工

     */

    @Test

    public void test4(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                .add(Restrictions.isNull("dept"));

        List<Emp> list=criteria.list();

        System.out.println("查询不属于任何部门的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getSal());

        }

    }

    /**

     * 查询职位是“MANAGER”的员工

     */

    @Test

    public void test5(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                .add(Restrictions.eq("job", "MANAGER"));

        List<Emp> list=criteria.list();

        System.out.println("查询职位是“MANAGER”的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getJob());

        }

    }

    /**

     * 查询职位是“MANAGER”或“CLERK”的员工

     */

    @Test

    public void test6(){

        Session session =HibernateSessionFactory.getSession();

        List<String> joblist=new ArrayList<String>();

        joblist.add("MANAGER");

        joblist.add("CLERK");

        Criteria criteria = session.createCriteria(Emp.class)

                .add(Restrictions.in("job", joblist));

        List<Emp> list=criteria.list();

        System.out.println("查询职位是“MANAGER”或“CLERK”的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getJob());

        }

    }

    /**

     * 查询工资在2000到5000元之间的员工

     */

    @Test

    public void test7(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                //.add(Restrictions.ge("sal", 2000D))

                //.add(Restrictions.le("sal", 5000D));

                .add(Restrictions.between("sal", 2000D, 5000D));

        List<Emp> list=criteria.list();

        System.out.println("查询工资在2000到5000元之间的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getSal());

        }

    }

    /**

     * 查询姓名中包括 “s”的员工

     */

    @Test

    public void test8(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                //.add(Restrictions.like("ename", "%s%").ignoreCase());//忽略大小写

                .add(Restrictions.like("ename", "s", MatchMode.ANYWHERE).ignoreCase());

        List<Emp> list=criteria.list();

        System.out.println("查询姓名中包括 “s”的员工");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getSal());

        }

    }

    /**

     * 查询职位是“analyser”或“accountant”的员工

     */

    @Test

    public void test9(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Emp.class)

                //两个or

                //.add(Restrictions.or(Restrictions.eq("job", "CLERK"),

                        //Restrictions.eq("job", "SALESMAN")));

                //三个

                .add(Restrictions.disjunction()

                        .add(Restrictions.eq("job", "SALESMAN"))

                        .add(Restrictions.eq("job", "CLERK"))

                        .add(Restrictions.eq("job","MANAGER"))

                        );

        List<Emp> list=criteria.list();

        System.out.println("查询职位是“analyser”或“accountant”的员工--几种方法");

        for (Emp emp : list) {

            System.out.println(emp.getEname()+"\t"+emp.getSal()+"\t"+emp.getJob());

        }

    }

    /**

     * 查询没有员工的部门

     */

    @Test

    public void test10(){

        Session session =HibernateSessionFactory.getSession();

        Criteria criteria = session.createCriteria(Dept.class)

                .add(Restrictions.isEmpty("emps"));

        List<Dept> list=criteria.list();

        System.out.println(" 查询没有员工的部门");

        for (Dept dept : list) {

            System.out.println(dept.getDname());

        }

    }

}