package cn.itcast.h3.query.hql;

import java.util.List;

import org.hibernate.Criteria;

import org.hibernate.Session;

import org.hibernate.criterion.DetachedCriteria;

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 cn.itcast.h3.query.hql.vo.StudentModel;

import cn.itcast.h3.query.hql.vo.TeacherModel;

import cn.itcast.util.HibernateUtil;

//Criteria查询(QBC)

public class CriteriaApp {

    // 简单查询

    void testSimpleCriteria() {

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(TeacherModel.class);

        List<TeacherModel> queryList = c.list();

        for (TeacherModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    // 查询排序

    void testCriteriaOrder() {

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(StudentModel.class);

        c.addOrder(Order.asc("age"));

        c.addOrder(Order.desc("studentName"));

        List<StudentModel> queryList = c.list();

        for (StudentModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    // 按条件查询

    void testCriteriaCondition() {

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(StudentModel.class);

        // 从上向下，全部默认为并且关系

        // c.add(Restrictions.like("studentName", "%虎%"));

        // c.add(Restrictions.gt("age", 22));

        // 使用关系描述条件

        // c.add(Restrictions.or(Restrictions.like("studentName", "%虎%"),

        // Restrictions.lt("age", 22)));

        // c.add(

        // Restrictions.and(

        // Restrictions.eq("aa", 123),

        // Restrictions.and(

        // Restrictions.lt("aa", 232),

        // Restrictions.eq("", 123)

        // )

        // )

        // );

        List<StudentModel> queryList = c.list();

        for (StudentModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    // 链式风格

    void testCriteriaLink() {

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(StudentModel.class);

        c.add(Restrictions.like("studentName", "%虎%")).add(    Restrictions.gt("age", 22));

        List<StudentModel> queryList = c.list();

        for (StudentModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    // 投影风格

    void testCriteriaShadow() {

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(StudentModel.class);

//        投影单个字段

//        c.setProjection(Property.forName("age"));

//        List<Integer> queryList = c.list();

//        for (Integer age : queryList) {

//            System.out.println(age);

//        }

//        投影多个字段

        c.setProjection(

            Projections.projectionList()

                .add(Property.forName("studentName"))

                .add(Property.forName("age"))

            );

        List<Object[]> queryList =c.list();

        for(Object[] objs:queryList){

            for(Object obj:objs){

                System.out.println(obj);

            }

            System.out.println("----------------");

        }

        s.close();

    }

    //多表关联查询

    void testMulQuery(){

        Session s = HibernateUtil.getSession();

        Criteria c = s.createCriteria(StudentModel.class);

        c.createAlias("teacher", "t");

        c.add(Restrictions.eq("t.teacherName", "李若亮"));

        List<StudentModel> queryList = c.list();

        for (StudentModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    //离线查询DetachedCriteria

    void testDetachedCriteria(){

        DetachedCriteria dc = DetachedCriteria.forClass(StudentModel.class);

        dc.createAlias("teacher", "t");

        dc.add(Restrictions.eq("t.teacherName", "雅典娜"));

        //..一路传递到数据层

        Session s = HibernateUtil.getSession();

        Criteria c = dc.getExecutableCriteria(s);

        List<StudentModel> queryList = c.list();

        for (StudentModel tm : queryList) {

            System.out.println(tm);

        }

        s.close();

    }

    public static void main(String[] args) {

        new CriteriaApp().testDetachedCriteria();

    }

}