查询总结、HQL语法、QBC(QueryByCriteria)深入学习

1.查询总结

　　在之前的批量查询练习的时候练习基本五种查询方法的使用:

1.OID查询---根据对象ID进行查询

2.对象属性导航查询: obj.getXXX

3.HQL查询:Query对象查询

4.QBC(QueryByCriteria)：Criteria对象查询

5.原生SQL查询:SQLQuery查询

测试代码如下:

package cn.qlq.query;

import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;
import org.junit.Test;

import cn.qlq.domain.Customer;
import cn.qlq.domain.LinkMan;
import cn.qlq.util.HibernateUtil;

/**
 * 之前的查询总结
 * @author liqiang
 *
 */
public class HibernateQuery {
    @Test
    /**
     * OID查询(get方法)
     */
    public void fun1(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        Customer customer = session.get(Customer.class, 7l);
        System.out.println(customer);
        
        
        tx.commit();
        session.close();
    }
    
    @Test
    /**
     * 属性导航语言(对象.getXXX)
     */
    public void fun2(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        Customer customer = session.get(Customer.class, 7l);
        Set<LinkMan> linkMens = customer.getLinkMens();
        System.out.println(linkMens);
        
        tx.commit();
        session.close();
    }
    
    @Test
    /**
     * HQL查询(Query对象进行查询)
     */
    public void fun3(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        String hql = "from cn.qlq.domain.Customer";//如果整个项目只有一个类名，可以省去包名
        Query query = session.createQuery(hql);
        System.out.println(query.list());
        
        tx.commit();
        session.close();
    }
    
    @Test
    /**
     * Criteria查询(也被叫做QBC---query by criteria)
     */
    public void fun4(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        Criteria criteria = session.createCriteria(Customer.class);
//        criteria.add(Restrictions.ilike("name", "xxx"));//添加过滤条件
        System.out.println(criteria.list());
        
        tx.commit();
        session.close();
    }
    
    @Test
    /**
     * 原生SQL查询(SQLQuery对象进行查询)
     */
    public void fun5(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        String sql = "select * from cst_customer";
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        sqlQuery.addEntity(Customer.class);
        System.out.println(sqlQuery.list());
        
        tx.commit();
        session.close();
    }
}

补充:也可以用query对象将查询结果映射为Map

    @Test
    /**
     * 原生SQL查询(SQLQuery映射查询结果为map)
     */
    public void fun6(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        
        String sql = "select * from cst_customer";
        SQLQuery sqlQuery = session.createSQLQuery(sql);
        sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        System.out.println(sqlQuery.list());
        
        tx.commit();
        session.close();
    }

结果：

[{cust_phone=null, cust_name=测试名称1, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=7}, {cust_phone=null, cust_name=测试名称2, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=8}, {cust_phone=null, cust_name=测试名称3, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=9}, {cust_phone=null, cust_name=测试名称4, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=10}, {cust_phone=null, cust_name=测试名称5, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=11}, {cust_phone=null, cust_name=测试名称6, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=12}, {cust_phone=null, cust_name=测试名称7, cust_mobile=null, cust_linkman=null, cust_industry=null, cust_level=null, cust_source=null, cust_id=13}]

2.HQL深入学习(Hibernate query language)

1.HQL介绍

　　HQL是面向对象的查询语言，它和SQL查询有些类似，但它使用的是类、对象、属性的概念，而没有表和字段的概念，也就是在HQL中如果出现DB中的表和字段名是错误的。在Hibernate的各种检索方式中，官方推荐的是HQL检索，也是使用最广泛的一种查询方式。它具有如下功能:

在查询中设定各种查询条件
支持投影查询，即检索对象的部分属性
支持分页查询
支持分组查询，允许使用group by关键字和having关键字
提供内置聚集函数。如sum、avg、min、max、count等函数
能够调用用户定义的SQL函数
支持子查询，即嵌套查询
支持动态参数绑定

Hibernate提供的Query接口是专门的HQL查询接口，它能够执行各种复杂的HQL查询语句。完整的SQL语句结构如下:

Select ... from... where ... group by ... having...order by ...desc/asc

　　HQL非常类似于SQL。通常情况下如果查询表中的所有记录的时候，查询语句中可省略select关键字，如下:

from Customer

　　如果执行该语句，则会返回应用程序中的所有的Customer对象，需要注意的是Customer是类名而不是对象名，类名区分大小写，from不区分大小写，如果项目中有多个相同的类名需要加包名。

补充:如果希望查询结果映射为map，可以采用:

        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

2.HQL学习

1.基本检索-检索所有

    @Test
    // HQL查询所有数据
    public void fun1() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
//        String hql = "from cn.qlq.domain.Customer";// from 类名全路径
        String hql1 = "from Customer";//整个项目只有一个类名可以省略包名
//        String hql2 = "from java.lang.Object";//写Object的时候会查询项目中的所有类,切勿使用，浪费内存
        // 3.根据hql创建查询对象
//        Query query = session.createQuery(hql);
        Query query = session.createQuery(hql1);
//        Query query = session.createQuery(hql2);
        // 4.根据查询对象获取查询结果
        List<Customer> objs = query.list();
        System.out.println(objs);
    }

结果:

Hibernate: 
    select
        customer0_.cust_id as cust_id1_0_,
        customer0_.cust_name as cust_nam2_0_,
        customer0_.cust_source as cust_sou3_0_,
        customer0_.cust_industry as cust_ind4_0_,
        customer0_.cust_level as cust_lev5_0_,
        customer0_.cust_linkman as cust_lin6_0_,
        customer0_.cust_phone as cust_pho7_0_,
        customer0_.cust_mobile as cust_mob8_0_ 
    from
        cst_customer customer0_
[Customer [cust_id=7, cust_name=测试名称1], Customer [cust_id=8, cust_name=测试名称2], Customer [cust_id=9, cust_name=测试名称3], Customer [cust_id=10, cust_name=测试名称4], Customer [cust_id=11, cust_name=测试名称5], Customer [cust_id=12, cust_name=测试名称6], Customer [cust_id=13, cust_name=测试名称7]]

2.排序

    @Test
    //排序查询
    public void fun2() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "from Customer order by cust_id asc,cust_name desc";//整个项目只有一个类名可以省略包名
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql1);
        // 4.根据查询对象获取查询结果
        List<Customer> objs = query.list();
        System.out.println(objs);
    }

结果:

Hibernate: 
    select
        customer0_.cust_id as cust_id1_0_,
        customer0_.cust_name as cust_nam2_0_,
        customer0_.cust_source as cust_sou3_0_,
        customer0_.cust_industry as cust_ind4_0_,
        customer0_.cust_level as cust_lev5_0_,
        customer0_.cust_linkman as cust_lin6_0_,
        customer0_.cust_phone as cust_pho7_0_,
        customer0_.cust_mobile as cust_mob8_0_ 
    from
        cst_customer customer0_ 
    order by
        customer0_.cust_id asc,
        customer0_.cust_name desc
[Customer [cust_id=7, cust_name=测试名称1], Customer [cust_id=8, cust_name=测试名称2], Customer [cust_id=9, cust_name=测试名称3], Customer [cust_id=10, cust_name=测试名称4], Customer [cust_id=11, cust_name=测试名称5], Customer [cust_id=12, cust_name=测试名称6], Customer [cust_id=13, cust_name=测试名称7]]

3.条件检索

(1)?占位符查询

　　类似于JDBC的占位符，只是hibernate的?下标从0开始，而JDBC的下标从1开始，基本上所有的编程索引都从0开始，唯独JDBC从1开始。。。。

    @Test
    // HQL的?占位符查询
    public void fun3() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
//        String hql = "from cn.qlq.domain.Customer";// from 类名全路径
        String hql = "from Customer where cust_id = ?";// 如果整个项目中只有这一个类名可以直接写名字
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql);
//        query.setLong(0, 1l);//类似于JDBC的占位符，只是JDBC的占位符下标从0开始，hibernate从1开始
        query.setParameter(0, 1l);//这种写法不用管类型
        // 4.根据查询对象获取查询结果
        Customer customer = (Customer) query.uniqueResult();
        System.out.println(customer);
    }

(2)命令占位符：name格式的查询，固定格式，name随便起，习惯性的起做和条件名字一样

    @Test
    // HQL的命令占位符查询
    public void fun4() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
//        String hql = "from cn.qlq.domain.Customer";// from 类名全路径
        String hql = "from Customer where cust_id = :cust_id";// :cust_id的名字随便起，只不过习惯性的起做一样
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql);
//        query.setLong(0, 1l);//类似于JDBC的占位符，只是JDBC的占位符下标从0开始，hibernate从1开始
        query.setParameter("cust_id",1l);
        // 4.根据查询对象获取查询结果
        Customer customer = (Customer) query.uniqueResult();
        System.out.println(customer);
    }

4.排序查询

@Test
    // HQL分页查询
    public void fun5() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.书写HQL语句
//        String hql = "from cn.qlq.domain.Customer";// from 类名全路径
        String hql = "from Customer order by cust_id desc";// 3.根据hql创建查询对象
        Query query = session.createQuery(hql);
        /**
         * 类似于 limit start,pageSize;
         * 假设页大小是2
         * 页号 起始值  页大小
         * 1   0    2
         * 2   2    2
         */
        //例如取第二页数据
        query.setFirstResult(2);
        query.setMaxResults(2);
        // 4.根据查询对象获取查询结果
         List<Customer> customers = query.list();
        System.out.println(customers);
    }

5.聚合函数的使用:

    @Test
    //聚集函数的使用
    public void fun3() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "select count(*) from Customer";//统计总数
        String hql2 = "select avg(cust_id) from Customer";//平均
        String hql3 = "select sum(cust_id) from Customer";//和
        String hql4 = "select max(cust_id) from Customer";//最大
        String hql5 = "select min(cust_id) from Customer";//最小
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql4);
        // 4.根据查询对象获取查询结果
        Number number = (Number) query.uniqueResult();//Number是所有数字类型的父类
        System.out.println(number);
    }

6.投影检索---查询指定的属性，不查全部

(1)投影单个属性

    @Test
    //投影单个
    public void fun7() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "select cust_name from Customer";//整个项目只有一个类名可以省略包名
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql1);
        // 4.根据查询对象获取查询结果
        List<String> objs = query.list();//那一列是什么类型用什么类型，也可以省去不写
        System.out.println(objs);
    }

结果:

Hibernate: 
    select
        customer0_.cust_name as col_0_0_ 
    from
        cst_customer customer0_
[测试名称1, 测试名称2, 测试名称3, 测试名称4, 测试名称5, 测试名称6, 测试名称7]

(2)投影多列---映射为数组

    @Test
    //投影多列
    public void fun7() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "select cust_id,cust_name from Customer";//整个项目只有一个类名可以省略包名
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql1);
        // 4.根据查询对象获取查询结果
        List<Object[]> objs = query.list();//那一列是什么类型用什么类型，也可以省去不写
        System.out.println(objs);
    }

结果:

Hibernate: 
    select
        customer0_.cust_id as col_0_0_,
        customer0_.cust_name as col_1_0_ 
    from
        cst_customer customer0_
[[Ljava.lang.Object;@29bd3793, [Ljava.lang.Object;@458ba94d, [Ljava.lang.Object;@541821e6, [Ljava.lang.Object;@7c59ae2b, [Ljava.lang.Object;@63b9c8d4, [Ljava.lang.Object;@3a300972, [Ljava.lang.Object;@27bc3936]

(3)将投影映射为对象:(构造方式查询)

在原来的类中创建需要投影的属性的构造方法，同时需要显示的创建无参构造函数:

测试代码:

    @Test
    //投影多列---映射为JavaBean
    public void fun7() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "select new Customer(cust_id,cust_name) from Customer";//整个项目只有一个类名可以省略包名
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql1);
        // 4.根据查询对象获取查询结果
        List<Customer> objs = query.list();//那一列是什么类型用什么类型，也可以省去不写
        System.out.println(objs);
    }

结果

Hibernate: 
    select
        customer0_.cust_id as col_0_0_,
        customer0_.cust_name as col_1_0_ 
    from
        cst_customer customer0_
[Customer [cust_id=7, cust_name=测试名称1], Customer [cust_id=8, cust_name=测试名称2], Customer [cust_id=9, cust_name=测试名称3], Customer [cust_id=10, cust_name=测试名称4], Customer [cust_id=11, cust_name=测试名称5], Customer [cust_id=12, cust_name=测试名称6], Customer [cust_id=13, cust_name=测试名称7]]

7.查询结果映射为map

    @Test
    public void fun8() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.书写HQL语句
        String hql1 = "select cust_id,cust_name from Customer";//整个项目只有一个类名可以省略包名
        // 3.根据hql创建查询对象
        Query query = session.createQuery(hql1);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        // 4.根据查询对象获取查询结果
        List<Map<String,Object>> objs = query.list();//那一列是什么类型用什么类型，也可以省去不写
        System.out.println(objs);
    }

结果：

[{1=测试名称1, 0=7}, {1=测试名称2, 0=8}, {1=测试名称3, 0=9}, {1=测试名称4, 0=10}, {1=测试名称5, 0=11}, {1=测试名称6, 0=12}, {1=测试名称7, 0=13}]

3.HQL多表查询

　　多表查询分为交叉连接(笛卡尔积)、内连接(隐士内连接、显示内连接)，外链接（左外连接和右外连接），测试语句如下:

package cn.qlq.HQL;


import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.junit.Test;

import cn.qlq.domain.Customer;
import cn.qlq.util.HibernateUtil;

//学习HQL语法(不常用) - 多表查询语法
public class Demo2 {
    //回顾-原生SQL
    // 交叉连接-笛卡尔积(避免)
//        select * from A,B 
    // 内连接
//        |-隐式内连接
//            select * from A,B  where b.aid = a.id
//        |-显式内连接
//            select * from A inner join B on b.aid = a.id
    // 外连接
//        |- 左外
//            select * from A left [outer] join B on b.aid = a.id
//        |- 右外
//            select * from A right [outer] join B on b.aid = a.id
//---------------------------------------------------------------------
//HQL的多表查询
        //内连接(迫切)
        //外连接
//            |-左外(迫切)
//            |-右外(迫切)
    
    @Test
    //HQL 内连接 => 将连接的两端对象分别返回.放到数组中.
    public void fun0(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from Customer c inner join c.linkMens ";
        
        Query query = session.createQuery(hql);
        
        List<Object[]> list = query.list();
        
        for(Object[] arr : list){
            System.out.println(Arrays.toString(arr));
        }
        //----------------------------------------------------
        tx.commit();
        session.close();
        
    }
    
    @Test
    //HQL 内连接 => 带查询条件，查询cust_id为9的数据
    public void fun1(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from Customer c inner join c.linkMens  where cust_id = 9l";
        
        Query query = session.createQuery(hql);
        
        List<Object[]> list = query.list();
        
        for(Object[] arr : list){
            System.out.println(Arrays.toString(arr));
        }
        //----------------------------------------------------
        tx.commit();
        session.close();
        
    }
    
    @Test
    //HQL 迫切内连接 => 帮我们进行封装.返回值就是一个对象
    public void fun2(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from Customer c inner join fetch c.linkMens ";
        
        Query query = session.createQuery(hql);
        
        List<Customer> list = query.list();
        
        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();
        
    }
    
    @Test
    //HQL 左外连接 => 将连接的两端对象分别返回.放到数组中.
    public void fun3(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from Customer c left join c.linkMens ";
        
        Query query = session.createQuery(hql);
        
        List<Object[]> list = query.list();
        
        for(Object[] arr : list){
            System.out.println(Arrays.toString(arr));
        }
        //----------------------------------------------------
        tx.commit();
        session.close();
        
    }
    @Test
    //HQL 右外连接 => 将连接的两端对象分别返回.放到数组中.
    public void fun4(){
        Session session = HibernateUtil.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from Customer c right join c.linkMens ";
        
        Query query = session.createQuery(hql);
        
        List<Object[]> list = query.list();
        
        for(Object[] arr : list){
            System.out.println(Arrays.toString(arr));
        }
        //----------------------------------------------------
        tx.commit();
        session.close();
        
    }
    
}

3.QBC深入学习

1.在线Criteria(由Session创建)

　　QBC（Query By Criteria）是Hibernate创提供的另一种检索对象的方式，它主要由Criteria接口、Criterion接口和Expression类组成。Criteria是HibernateAPI中的一个查询接口，它需要由session进行创建(由session创建的Criteria称为在线Criteria对象,查询哪个实体将哪个实体的class字节码对象传给session)。Criterion是Criteria的查询条件，在Criteria中提供了add(Criterion c)方法来添加查询条件。使用QBC检索对象的示例代码如下:

        Criteria criteria = session.createCriteria(Customer.class);
        criteria.add(Restrictions.eq("cust_id", 1l));
        Customer customer = (Customer) criteria.uniqueResult();

1.基本的查询所有

    @Test
    // 查询所有
    public void test1() {
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        List<Customer> list = criteria.list();
        System.out.println(list);
    }

2. 条件查询单个:

    @Test
    // 根据ID查询单个,条件查询
    public void test2() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.add(Restrictions.eq("cust_id", 1l));
        Customer customer = (Customer) criteria.uniqueResult();
        System.out.println(customer);
    }

结果:

Customer [cust_id=1, cust_name=程序员111]

条件列表:

补充:QBC也可以传SQL条件，例如:

    @Test
    // 根据ID查询单个,条件查询
    public void test2() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.add(Restrictions.sqlRestriction("cust_id = 1"));
        criteria.add(Restrictions.sqlRestriction("cust_name = '张三'"));
        Customer customer = (Customer) criteria.uniqueResult();
        System.out.println(customer);
    }

结果:

user创建对象
Hibernate: 
    select
        this_.cust_id as cust_id1_0_0_,
        this_.cust_name as cust_nam2_0_0_,
        this_.cust_source as cust_sou3_0_0_,
        this_.cust_industry as cust_ind4_0_0_,
        this_.isPerson as isPerson5_0_0_,
        this_.cust_level as cust_lev6_0_0_,
        this_.cust_linkman as cust_lin7_0_0_,
        this_.cust_phone as cust_pho8_0_0_,
        this_.cust_mobile as cust_mob9_0_0_ 
    from
        cst_customer this_ 
    where
        cust_id = 1 
        and cust_name = '张三'
Customer [cust_id=1, cust_name=张三]

3.分页查询:

    @Test
    // 分页查询
    public void test3() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        /**
         * 类似于 limit start,pageSize;
         * 假设页大小是2
         * 页号 起始值  页大小
         * 1   0    2
         * 2   2    2
         */
        criteria.setFirstResult(2);
        criteria.setMaxResults(2);
        List<Customer> list = criteria.list();
        System.out.println(list);
    }

结果:

[Customer [cust_id=3, cust_name=测试名称222], Customer [cust_id=4, cust_name=测试名称222]]

4.排序分组使用

    @Test
    // 排序和分组
    public void test5() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.排序
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.addOrder(Order.desc("cust_id"));
        List<Customer> list = criteria.list();
        System.out.println(list);
        System.out.println();
        // 2.分组
        Criteria criteria1 = session.createCriteria(Customer.class);
        criteria1.setProjection(Projections.groupProperty("cust_name"));
        List<Customer> list1 = criteria1.list();
        System.out.println(list1);
    }

结果：

[Customer [cust_id=4, cust_name=测试名称222], Customer [cust_id=3, cust_name=测试名称222], Customer [cust_id=2, cust_name=新增数据], Customer [cust_id=1, cust_name=程序员111]]

[新增数据, 测试名称222, 程序员111]

5.聚集函数查询总数

    @Test
    // 查询总数（聚集函数的使用）
    public void test4() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.聚集函数查询总数
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.setProjection(Projections.rowCount());
        Long count = (Long) criteria.uniqueResult();
        System.out.println(count);
        // 3.聚集函数查询总数第二种方法
        Criteria criteria1 = session.createCriteria(Customer.class);
        criteria1.setProjection(Projections.count("cust_id"));
        Long count1 = (Long) criteria.uniqueResult();
        System.out.println(count1);
        // 4.聚集函数查询几个不同的姓名
        Criteria criteria2 = session.createCriteria(Customer.class);
        criteria2.setProjection(Projections.countDistinct("cust_name"));
        Long count2 = (Long) criteria2.uniqueResult();
        System.out.println(count2);
    }

结果:

4
4
3

6. Criteria 实现or语句查询

    @Test
    // 根据ID查询单个,条件查询
    public void test22() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.add(Restrictions.or(Restrictions.eq("cust_name", "李四1"), Restrictions.eq("cust_name", "李四2")));
        criteria.add(Restrictions.isNotNull("cust_name"));
        List list = criteria.list();
        System.out.println(list);
    }

结果: (这种写法是一个 or ,相当于 where xxx=xx and ( xxx = xxx or xxxx=xxxx or ....) )

user创建对象
Hibernate:

select
this_.cust_id as cust_id1_0_0_,
this_.cust_name as cust_nam2_0_0_,
this_.cust_source as cust_sou3_0_0_,
this_.cust_industry as cust_ind4_0_0_,
this_.isPerson as isPerson5_0_0_,
this_.cust_level as cust_lev6_0_0_,
this_.cust_linkman as cust_lin7_0_0_,
this_.cust_phone as cust_pho8_0_0_,
this_.cust_mobile as cust_mob9_0_0_ 
from
cst_customer this_ 
where
(
this_.cust_name=? 
or this_.cust_name=?
) 
and this_.cust_name is not null

[Customer [cust_id=3, cust_name=李四1], Customer [cust_id=4, cust_name=李四2]]

第二种写法:(建议这种---多个or的时候清晰)

    @Test
    // 根据ID查询单个,条件查询
    public void test222() {
        // 1 获得session
        Session session = HibernateUtils.openSession();
        // 2.創建criteria进行查询
        Criteria criteria = session.createCriteria(Customer.class);
        criteria.add(Restrictions.isNotNull("cust_name"));

        Disjunction disjunction = Restrictions.disjunction();
        disjunction.add(Restrictions.eq("cust_name", "李四1"));
        disjunction.add(Restrictions.eq("cust_name", "李四2"));
        criteria.add(disjunction);

        List list = criteria.list();
        System.out.println(list);
    }

结果:

user创建对象
Hibernate: 
    select
        this_.cust_id as cust_id1_0_0_,
        this_.cust_name as cust_nam2_0_0_,
        this_.cust_source as cust_sou3_0_0_,
        this_.cust_industry as cust_ind4_0_0_,
        this_.isPerson as isPerson5_0_0_,
        this_.cust_level as cust_lev6_0_0_,
        this_.cust_linkman as cust_lin7_0_0_,
        this_.cust_phone as cust_pho8_0_0_,
        this_.cust_mobile as cust_mob9_0_0_ 
    from
        cst_customer this_ 
    where
        this_.cust_name is not null 
        and (
            this_.cust_name=? 
            or this_.cust_name=?
        )
[Customer [cust_id=3, cust_name=李四1], Customer [cust_id=4, cust_name=李四2]]

所以当只需要一个or条件时可以用Restrictions.or();当用到多个or条件时，就需要用到Restrictions.disjunction();

2.离线Criteria(DetachedCriteria--可以凭空创建)

补充:

　　Spring 的框架提供了getHibernateTemplate().findByCriteria(detachedCriteria) 方法可以很方便地根据DetachedCriteria来返回查询结果。

1.DetachedCriteria介绍以及简单用法

　　应用场景一般是动态条件查询，我们可以在web层或者service层根据业务创建DetachedCriteria对象并封装动态条件传到dao层，dao层根据DetachedCriteria对象获取在线Criteria查询数据库,实现解耦。使用步骤大概如下:

(1)创建DetachedCriteria对象的方式有两种:

        //第一种方法:传入class
        DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);//查询哪个对象将哪个对象的字节码传进去
        //第二种方法:传入全类名(必须携带包名)
        DetachedCriteria dc = DetachedCriteria.forEntityName("cn.qlq.domain.Customer");//

(2)DetachedCriteria对象封装查询条件与Criteria对象一样:

        dc.add(Restrictions.idEq(7l));

(3)通过DetachedCriteria构造在线Criiteria对象方法:(需要Session环境)

        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.通过DetachedCriteria构造Criteria对象
        Criteria criteria = dc.getExecutableCriteria(session);//获取在线criteria对象

(4)通过Criteria进行查询:（与原来一样）

        //1.查询集合
//        List<Customer> list = criteria.list();
        //2.查询单个对象
        Object uniqueResult = criteria.uniqueResult();

2.在线Criteria与线下Criteria(DetachedCriteria)区别:

在线Criteria的查询图解如下:

离线Criteria图解如下:

线下Criteria测试代码:

    @Test
    public void test1() { 
        /*******************S  Web层或者service层  **************************/
        //0.构造DetachedCriteria对象封装查询条件
        //第一种方法:传入class
//        DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);//查询哪个对象将哪个对象的字节码传进去
        //第二种方法:传入全类名(必须携带包名)
        DetachedCriteria dc = DetachedCriteria.forEntityName("cn.qlq.domain.Customer");//
        dc.add(Restrictions.idEq(7l));
        /*******************E  Web层或者service层  **************************/
        
        /********************S Dao层************************************/
        // 1 获得session
        Session session = HibernateUtil.openSession();
        // 2.通过DetachedCriteria构造Criteria对象
        Criteria criteria = dc.getExecutableCriteria(session);//获取在线criteria对象
        List<Customer> list = criteria.list();
        System.out.println(list);
        /********************E Dao层************************************/
    }

结果:

Hibernate: 
    select
        this_.cust_id as cust_id1_0_0_,
        this_.cust_name as cust_nam2_0_0_,
        this_.cust_source as cust_sou3_0_0_,
        this_.cust_industry as cust_ind4_0_0_,
        this_.cust_level as cust_lev5_0_0_,
        this_.cust_linkman as cust_lin6_0_0_,
        this_.cust_phone as cust_pho7_0_0_,
        this_.cust_mobile as cust_mob8_0_0_ 
    from
        cst_customer this_ 
    where
        this_.cust_id = ?
[Customer [cust_id=7, cust_name=测试名称1]]

user创建对象Hibernate: select this_.cust_id as cust_id1_0_0_, this_.cust_name as cust_nam2_0_0_, this_.cust_source as cust_sou3_0_0_, this_.cust_industry as cust_ind4_0_0_, this_.isPerson as isPerson5_0_0_, this_.cust_level as cust_lev6_0_0_, this_.cust_linkman as cust_lin7_0_0_, this_.cust_phone as cust_pho8_0_0_, this_.cust_mobile as cust_mob9_0_0_ from cst_customer this_ where cust_id = 1 and cust_name = '张三'Customer [cust_id=1, cust_name=张三]

posted @ 2018-07-29 19:26 QiaoZhi 阅读(1218) 评论(0) 编辑收藏举报

刷新页面返回顶部

Qiao_Zhi

有远大抱负的人不可忽略眼前的工作!!!