hibernate使用HQL语句进行5种查询

分为：一般查询， 排序查询，分页查询，统计查询，投影查询

package dao;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.mapping.List;
import org.hibernate.query.Query;
import org.junit.Test;

import static org.junit.Assert.*;

/**
 * @Author: David.Xiao
 * @Date: 2018/11/7 16:23
 * @Description: Hql语句特点：不写select *，用实体类名代替表名，用类的字段名代替列名
 */
public class HqlUtilTest {
    /**
     * 查所有
     */
    @Test
    public void queryAllByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("from AccountEntity");
        java.util.List list = query.list();
        for (Object object: list) {
            System.out.println(object);
        }
        tx.commit();
    }

    /**
     * 查一个
     */
    @Test
    public void queryOneByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("from AccountEntity where id = ?1");
//        query.setString(0,"1"); 已废弃
        query.setParameter(1,1);
        java.util.List list = query.list();
        for (Object object: list) {
            System.out.println(object.toString());
        }
        tx.commit();
    }

    /**
     * 排序查询
     */
    @Test
    public void orderQueryByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("from AccountEntity order by id desc");
        java.util.List list = query.list();
        for (Object object: list) {
            System.out.println(object.toString());
        }
        tx.commit();
    }

    /**
     * 分页查询
     * mysql分页关键字为：limit
     * 参数：1 查询的开始记录索引  2 每次查询的条数
     * hibernate setFirstResult   setMaxResult
     */
    @Test
    public void limitQueryByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("from AccountEntity");
        //查询的开始记录索引
        query.setFirstResult(0);
        //每次查询的条数
        query.setMaxResults(2);
        java.util.List list = query.list();
        for (Object object: list) {
            System.out.println(object.toString());
        }
        tx.commit();
    }

    /**
     * 统计查询
     * 即在hql中使用聚合函数：count sum avg max min
     */
    @Test
    public void statisticsQueryByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("select count(*) from AccountEntity");
        java.util.List list = query.list();
//        for (Object object: list) {
//            System.out.println(object.toString());
//        }
        Long count =(Long)query.uniqueResult(); //当返回结果唯一时，可用此方法。若不唯一，则会抛异常。
        tx.commit();
    }

    /**
     * 投影查询:查询指定列，并希望其返回结果是实体类，而不是Object[]
     * 1.查询语句要使用new关键字
     * 2.在实体类中添加对应字段的构造函数，别忘了同时添加默认无参构造函数
     */
    @Test
    public void projectQueryByHql()
    {
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("select new domain.AccountEntity (id,name) from AccountEntity");
        //得到的是Object[]类型的数组
        java.util.List list = query.list();
        for (Object object:list) {
            System.out.println(object.toString());
        }
        tx.commit();
    }
}