基本HQL语句学习

最新推荐文章于 2021-05-30 21:22:17 发布

会飞行的猪

最新推荐文章于 2021-05-30 21:22:17 发布

阅读量895

点赞数 1

分类专栏： Hibenate 文章标签： HQL QBC

本文链接：https://blog.csdn.net/u011249702/article/details/51260047

版权

Hibenate 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

HQL语句是hibernate中比较常见的查询语句，语句灵活和普通的SQL类似，按照查询的需求可以分为一下几种：

1.1 简单HQL 
    1.1.1 简单查询
        hql = "FROM Employee"
        hql = "FROM Employee e " //使用别名

    1.1.2 使用where过滤条件
        hql = "FROM Employee where id > 2 and ... "
        hql = "FROM Employee e where e.id > 2 and ... "

    1.1.3 使用order by
        hql = "FROM Employee where id > 2 and ... order by id DESC "
        hql = "FROM Employee e where e.id > 2 and ... order by e.id DESC " 

    1.1.4 指定的select语句不能用*代替，应该使用对象
        hql = "SELECT e FROM Employee e" // 可以省略
        hql = "SELECT e.name FROM Employee e"

    1.1.6 分页
        Query query = session.createQuery(hql);
        query.setFirstResult(0);
        query.setMaxResults(10);



1.2 单列返回原始类型
    hql = "SELECT e.id FROM Employee e";

1.3 指定多列查询返回使用对象数组
    hql = "SELECT new Employee(e.id,e.name) FROM Employee e"; // 必须添加一个构造方法，不过默认的构造器要记得添加，否则"FROM Employee"失效

1.4 聚集函数
    hql = "SELECT COUNT(*) FROM Employee";
    hql = "SELECT max(id) FROM Employee";
    Number count = (Number) session.createQuery(hql).uniqueResult(); 

1.5 Group By，Having
    hql = "SELECT e.id,e.name,count(*) FROM Employee e where id < 10 group by e.name having count(*) > 1";
    List list = query.list(); 

1.6 带占位符的查询
    hql = "SELECT e FROM Employee e where e.id = ?";
    Query query = session.createQuery(hql).setParameter(1, ...);

1.7 使用变量名
    hql = "SELECT e FROM Employee e where e.id :ID ";
    Query query = session.createQuery(hql).setParameter("ID", ...);

1.8 集合参数
    hql = "SELECT e FROM Employee e where e.id in (:ids) ";
    Query query = session.createQuery(hql).setParameterList("ids", new Object[]{...});

1.9 定义命名的查询
    Query query = session.createQuery("queryRange");

        <query name="queryRange">
        <![CDATA[  HQL: FROM ..... ]]>  
        </query>

note： 值得注意的是这边类名一般是全限定名，可以在hbm中写入自动导入包，就可以使用简单名
    <hibernate-mapping package="com.heying.k_hql_query" auto-import="true">

介绍QBC(query by criteria)：

        // 创建Criteria
        Criteria criteria = session.createCriteria(Employee.class);
        // Where 条件
        criteria.add(Restrictions.ge("id", ""));
        criteria.add(Restrictions.le("id", ""));
        criteria.add(Restrictions.ne("id", ""));
        // Order by
        criteria.addOrder(Order.desc("name"));
        criteria.addOrder(Order.asc(""));