批量查询HQL（查询所有、条件查询、占位符、分页、排序、统计、投影）

HQL(Hibernate Query Language)查询：

1、查询所有学生信息：

public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student";
            Query query= session.createQuery(hql);
            List<Student>list=query.list();
            System.out.println(list);
            transaction.commit();
            session.close();//游离状态
        }

简化写法：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="from Student";
        Query query= session.createQuery(hql);
        List<Student> list=query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }

查询所有表的数据：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="from java.lang.Object";
        Query query= session.createQuery(hql);
        List<Object> list=query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }

 2、条件查询（查询所有的女学生的信息）：

public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student where sex='女'";
            Query query= session.createQuery(hql);
            List<Student>list=query.list();
            System.out.println(list);
            transaction.commit();
            session.close();//游离状态
        }

3、问号占位符的使用：

public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student where studentno=?";
            Query query= session.createQuery(hql);
            query.setInteger(0,937221524);
            Student student= (Student) query.uniqueResult();//获取唯一值
            System.out.println(student);
            transaction.commit();
            session.close();
        }

 public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student where sex=?";
            Query query= session.createQuery(hql);
            //query.setFetchSize(0,"女");
            query.setParameter(0,"女");//通用
            List<Student> list=query.getResultList();
            System.out.println(list);
            transaction.commit();
            session.close();
        }

4、命名占位符：

省去了用数字指明属性位置的麻烦，直接用属性名代替。

public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student where sex=:sex";
            Query query= session.createQuery(hql);
            query.setParameter("sex","女");//通用
            List<Student> list=query.getResultList();
            System.out.println(list);
            transaction.commit();
            session.close();
        }

5、分页查询：

需要指明从哪开始，要取几条数据：

  public static void testSel() {
            Session session = HibernateUtils.openSession();
            Transaction transaction = session.beginTransaction();
            String hql="from pers.zhb.domain.Student where sex=:sex";
            Query query= session.createQuery(hql);
            query.setFirstResult(0);//从0开始
            query.setMaxResults(2);//抓取两条数据
            query.setParameter("sex","女");//通用
            List<Student> list=query.getResultList();
            System.out.println(list);
            transaction.commit();
            session.close();
        }

6、排序查询：

（1）按学号升序排列：

public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="from pers.zhb.domain.Student order by studentno asc";
        Query query= session.createQuery(hql);
        List<Student> list=query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }

（2）分数降序：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="from pers.zhb.domain.Student order by point desc ";
        Query query= session.createQuery(hql);
        List<Student> list=query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }

 7、统计查询：

（1）count：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select count(*) from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        Number number= (Number) query.uniqueResult();
        System.out.println(number);
        transaction.commit();
        session.close();//游离状态
    }

 （2）sum求所有学生的分数和：

  public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select sum(point) from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        Number number= (Number) query.uniqueResult();
        System.out.println(number);
        transaction.commit();
        session.close();//游离状态
    }

 （3）avg求平均分：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select avg(point) from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        Number number= (Number) query.uniqueResult();
        System.out.println(number);
        transaction.commit();
        session.close();//游离状态
    }

 （4）min求最低分：

 public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select min(point) from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        Number number= (Number) query.uniqueResult();
        System.out.println(number);
        transaction.commit();
        session.close();//游离状态
    }

 8、投影：

（1）查询每个学生的名字：

public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select sname from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        List list= query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }

 （2）查询学生的姓名和学号：

创建构造方法（用于存储姓名和学号）：

 public Student(Integer studentno,String sname){
        super();
        this.studentno=studentno;
        this.sname=sname;
    }

public static void testSel() {
        Session session = HibernateUtils.openSession();
        Transaction transaction = session.beginTransaction();
        String hql="select new Student(studentno,sname) from pers.zhb.domain.Student";
        Query query= session.createQuery(hql);
        List<Object[]> list= query.list();
        System.out.println(list);
        transaction.commit();
        session.close();//游离状态
    }