package cn.itcast.h3.hql;

import java.util.List;

import org.hibernate.Query;

import org.hibernate.Session;

import cn.itcast.h3.hql.vo.StudentModel;

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

import cn.itcast.h3.util.HibernateUtil;

public class HQLApp {

    //测试简单查询

    void testSimple(){

        Session s = HibernateUtil.getSession();

        //1.HQL

        String hql = "from TeacherModel";

        //2.Query

        Query q = s.createQuery(hql);

        //3.获取查询结果

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

        for(TeacherModel tm:queryList){

            System.out.println(tm);

        }

        s.close();

    }

    //链式格式

    void testLinked(){

        Session s = HibernateUtil.getSession();

        //3.获取查询结果

        List<TeacherModel> queryList = s.createQuery( "from TeacherModel where uuid <=:uuid").setFirstResult(1).setMaxResults(3).setLong("uuid", 3L).list();

        for(TeacherModel tm:queryList){

            System.out.println(tm);

        }

        s.close();

    }

    //别名格式

    void testAlias(){

        Session s = HibernateUtil.getSession();

        //1.HQL

        String hql = "select um.nick from TeacherModel um";

        //2.Query

        Query q = s.createQuery(hql);

        //3.获取查询结果

        List<String> queryList = q.list();

        for(String tm:queryList){

            System.out.println(tm);

        }

        s.close();

    }

    //聚合函数

    void testFunctoin(){

        Session s = HibernateUtil.getSession();

        //1.HQL

        String hql = "select sum(age) from StudentModel";

        //2.Query

        Query q = s.createQuery(hql);

        Long avgAge = (Long) q.uniqueResult();

        System.out.println(avgAge);

        s.close();

    }

    //按条件查询

    void testEntityQuery(){

        Session s = HibernateUtil.getSession();

        //1.HQL

        //编号为4的老师有哪些学生

//        String hql = "from StudentModel where  teacher.uuid= :uuid";

//        //2.Query

//        Query q = s.createQuery(hql);

//        q.setLong("uuid",4L);

        String hql = "from StudentModel where teacher = :teacher";

        Query q = s.createQuery(hql);

        TeacherModel tm = new TeacherModel();

        tm.setUuid(4L);

//        tm.setTeacherName("李若亮");

        //为hql赋值对象参数

        q.setEntity("teacher", tm);

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

        for(StudentModel sm:queryList){

            System.out.println(sm);

        }

    }

    //无条件查询

    void testNoCondQuery(){

        Session s = HibernateUtil.getSession();

        String hql = "from TeacherModel where teacherName = nick";

        Query q = s.createQuery(hql);

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

        for(TeacherModel sm:queryList){

            System.out.println(sm);

        }

    }

    //投影查询

    void testOtherQuery(){

        Session s = HibernateUtil.getSession();

        String hql = "select new TeacherModel(teacherName,nick) from TeacherModel";

        Query q = s.createQuery(hql);

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

        for(TeacherModel sm:queryList){

            System.out.println(sm);

        }

    }

    //投影查询list

    void testOtherQuery2(){

        Session s = HibernateUtil.getSession();

        String hql = "select new list(teacherName,nick) from TeacherModel";

        Query q = s.createQuery(hql);

        List<List<String>> queryList = q.list();

        for(List<String> sm:queryList){

            for(String sa:sm){

                System.out.println(sa);

            }

        }

    }

    //查询分组

    void testGroupQuery(){

        Session s = HibernateUtil.getSession();

        String hql = "select skill,count(skill) from StudentModel group by skill";

        Query q = s.createQuery(hql);

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

        for(Object[] objs:queryList){

            for(Object obj:objs){

                System.out.print(obj+"\t");

            }

            System.out.println();

        }

    }

    //查询分组

    void testObejcts(){

        Session s = HibernateUtil.getSession();

        String hql = "from java.lang.Object";

        Query q = s.createQuery(hql);

        List<Object> queryList = q.list();

        for(Object obj:queryList){

            System.out.println(obj);

        }

    }

    //配置格式的查询

    void testConfQuery(){

        Session s = HibernateUtil.getSession();

        Query q = s.getNamedQuery("getAll");

        q.setString("name", "李若亮");

        List<Object> queryList = q.list();

        for(Object obj:queryList){

            System.out.println(obj);

        }

    }

    public static void main(String[] args) {

        new HQLApp().testConfQuery();

    }

}