【JAVAEE学习笔记】hibernate04：查询种类、HQL、Criteria、查询优化和练习为客户列表增加查询条件

一、查询种类

1.oid查询-get

2.对象属性导航查询

3.HQL

4.Criteria

5.原生SQL

二、查询-HQL语法

//学习HQL语法
public class Demo {

    //基本语法
    @Test
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql = " from  cn.itcast.domain.Customer ";//完整写法
        String hql2 = " from  Customer "; //简单写法
        String hql3 = " from java.lang.Object "; 

        Query query = session.createQuery(hql3);

        List list = query.list();

        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //排序
    public void fun2(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql1 = " from  cn.itcast.domain.Customer order by cust_id asc ";//完整写法
        String hql2 = " from  cn.itcast.domain.Customer order by cust_id desc ";//完整写法

        Query query = session.createQuery(hql2);

        List list = query.list();

        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //条件查询
    public void fun3(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql1 = " from  cn.itcast.domain.Customer where cust_id =? ";//完整写法
        String hql2 = " from  cn.itcast.domain.Customer where cust_id = :id ";//完整写法

        Query query = session.createQuery(hql2);

//        query.setParameter(0, 2l);
        query.setParameter("id", 2l);

        List list = query.list();

        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //分页查询
    public void fun4(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql1 = " from  cn.itcast.domain.Customer  ";//完整写法

        Query query = session.createQuery(hql1);

        //limit ?,?
        // (当前页数-1)*每页条数
        query.setFirstResult(2);
        query.setMaxResults(2);

        List list = query.list();

        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //统计查询
    //count    计数
    //sum     求和
    //avg    平均数
    //max
    //min
    public void fun5(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql1 = " select count(*) from  cn.itcast.domain.Customer  ";//完整写法
        String hql2 = " select sum(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
        String hql3 = " select avg(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
        String hql4 = " select max(cust_id) from  cn.itcast.domain.Customer  ";//完整写法
        String hql5 = " select min(cust_id) from  cn.itcast.domain.Customer  ";//完整写法

        Query query = session.createQuery(hql5);

        Number number  = (Number) query.uniqueResult();

        System.out.println(number);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //投影查询
    public void fun6(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        String hql1 = " select cust_name from  cn.itcast.domain.Customer  ";
        String hql2 = " select cust_name,cust_id from  cn.itcast.domain.Customer  ";
        String hql3 = " select new Customer(cust_id,cust_name) from  cn.itcast.domain.Customer  ";

        Query query = session.createQuery(hql3);

        List list = query.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }
}

学习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 fun1(){
        Session session = HibernateUtils.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 迫切内连接 => 帮我们进行封装.返回值就是一个对象
    public void fun2(){
        Session session = HibernateUtils.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 = HibernateUtils.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 = HibernateUtils.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();

    }

}

学习HQL语法(不常用) - 多表查询语法

三、查询-Criteria语法

public class Demo {

    @Test
    //基本语法
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Criteria c = session.createCriteria(Customer.class);

        List<Customer> list = c.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //条件语法
    public void fun2(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Criteria c = session.createCriteria(Customer.class);

//        c.add(Restrictions.idEq(2l));
        c.add(Restrictions.eq("cust_id",2l));

        List<Customer> list = c.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //分页语法 - 与HQL一样
    public void fun3(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Criteria c = session.createCriteria(Customer.class);
        //limit ?,?
        c.setFirstResult(0);
        c.setMaxResults(2);

        List<Customer> list = c.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //排序语法
    public void fun4(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Criteria c = session.createCriteria(Customer.class);

        c.addOrder(Order.asc("cust_id"));
        //c.addOrder(Order.desc("cust_id"));

        List<Customer> list = c.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    //统计语法
    public void fun5(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Criteria c = session.createCriteria(Customer.class);

        //设置查询目标
        c.setProjection(Projections.rowCount());

        List list = c.list();

        System.out.println(list);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

}

学习Criteria语法

　　非离线

　　

　　离线

　　

public class Demo2 {

    @Test
    public void fun1(){
        //Service/web层
        DetachedCriteria dc  = DetachedCriteria.forClass(Customer.class);

        dc.add(Restrictions.idEq(6l));//拼装条件(全部与普通Criteria一致)

        //----------------------------------------------------
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------
        Criteria c = dc.getExecutableCriteria(session);

        List list = c.list();

        System.out.println(list);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

}

学习离线Criteria

四、查询优化

　　类级别查询

　　　　get方法:没有任何策略.调用即立即查询数据库加载数据.

　　　　load方法: 应用类级别的加载策略

public class Demo {

    @Test
    // get方法 : 立即加载.执行方法时立即发送sql语句查询结果
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Customer c = session.get(Customer.class, 2l);

        System.out.println(c);
        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    @Test
    // load方法(默认):是在执行时,不发送任何sql语句.返回一个对象.使用该对象时,才执行查询.
    // 延迟加载: 仅仅获得没有使用.不会查询.在使用时才进行查询.
    // 是否对类进行延迟加载: 可以通过在class元素上配置lazy属性来控制.
        //lazy:true  加载时,不查询.使用时才查询b
        //lazy:false 加载时立即查询.
    public void fun2(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Customer c = session.load(Customer.class, 2l);

        //----------------------------------------------------
        tx.commit();
        session.close();
        System.out.println(c);

    }

}

懒加载|延迟加载

public class Demo {

    //集合级别的关联
    //fetch:select 单表查询
    //lazy:true 使用时才加载集合数据.
    @Test
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        Customer c = session.get(Customer.class, 2l);

        Set<LinkMan> linkMens = c.getLinkMens();//关联级别

        System.out.println(linkMens);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

    //集合级别的关联
        //fetch:select 单表查询
        //lazy:false 立即记载集合数据
        @Test
        public void fun2(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            Customer c = session.get(Customer.class, 2l);

            Set<LinkMan> linkMens = c.getLinkMens();//关联级别

            System.out.println(linkMens);

            //----------------------------------------------------
            tx.commit();
            session.close();

        }
        //集合级别的关联
        //fetch:select 单表查询
        //lazy:extra 极其懒惰.与懒加载效果基本一致. 如果只获得集合的size.只查询集合的size(count语句)
        @Test
        public void fun3(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            Customer c = session.get(Customer.class, 2l);

            Set<LinkMan> linkMens = c.getLinkMens();//关联级别

            System.out.println(linkMens.size());

            System.out.println(linkMens);

            //----------------------------------------------------
            tx.commit();
            session.close();

        }
        //集合级别的关联
        //fetch:join    多表查询
        //lazy:true|false|extra 失效.立即加载.
        @Test
        public void fun4(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            Customer c = session.get(Customer.class, 2l);

            Set<LinkMan> linkMens = c.getLinkMens();//关联级别

            System.out.println(linkMens.size());

            System.out.println(linkMens);

            //----------------------------------------------------
            tx.commit();
            session.close();

        }

        @Test
        //fetch: subselect 子查询
        //lazy: true 懒加载
        public void fun5(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            String  hql = "from Customer";

            Query query = session.createQuery(hql);

            List<Customer> list = query.list();

            for(Customer c:list){
                System.out.println(c);
                System.out.println(c.getLinkMens().size());
                System.out.println(c.getLinkMens());
            }

            //----------------------------------------------------
            tx.commit();
            session.close();

        }
        @Test
        //fetch: subselect 子查询
        //lazy: false 立即加载
        public void fun6(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            String  hql = "from Customer";

            Query query = session.createQuery(hql);

            List<Customer> list = query.list();

            for(Customer c:list){
                System.out.println(c);
                System.out.println(c.getLinkMens().size());
                System.out.println(c.getLinkMens());
            }

            //----------------------------------------------------
            tx.commit();
            session.close();

        }
        @Test
        //fetch: subselect 子查询
        //lazy: extra 极其懒惰
        public void fun7(){
            Session session = HibernateUtils.openSession();
            Transaction tx = session.beginTransaction();
            //----------------------------------------------------

            String  hql = "from Customer";

            Query query = session.createQuery(hql);

            List<Customer> list = query.list();

            for(Customer c:list){
                System.out.println(c);
                System.out.println(c.getLinkMens().size());
                System.out.println(c.getLinkMens());
            }

            //----------------------------------------------------
            tx.commit();
            session.close();

        }

}

关联级别 延迟加载 & 抓取策略（客户再获取多个联系人）

public class Demo2 {

    @Test
    //fetch:select    单表查询
    //lazy:proxy
        //customer-true 懒加载
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        LinkMan lm = session.get(LinkMan.class, 3l);

        Customer customer = lm.getCustomer();

        System.out.println(customer);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }
    @Test
    //fetch:join    多表
    //lazy: 失效
    public void fun3(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        LinkMan lm = session.get(LinkMan.class, 3l);

        Customer customer = lm.getCustomer();

        System.out.println(customer);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }
    @Test
    //fetch:select    单表查询
    //lazy:proxy
        //customer-false 立即加载
    public void fun2(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        LinkMan lm = session.get(LinkMan.class, 3l);

        Customer customer = lm.getCustomer();

        System.out.println(customer);

        //----------------------------------------------------
        tx.commit();
        session.close();

    }
}

关联级别 延迟加载 & 抓取策略（通过联系人获得客户）

　　结论:为了提高效率.fetch的选择上应选择select. lazy的取值应选择 true. 全部使用默认值.

　　no-session问题解决: 扩大session的作用范围.

　　

public class Demo {

    @Test
    public void fun1(){
        Session session = HibernateUtils.openSession();
        Transaction tx = session.beginTransaction();
        //----------------------------------------------------

        String hql = "from Customer ";
        Query query = session.createQuery(hql);
        List<Customer> list = query.list();

        for(Customer c:list){
            System.out.println(c.getLinkMens());
        }

        //----------------------------------------------------
        tx.commit();
        session.close();

    }

}

抓取数量

五、练习:为客户列表增加查询条件

在上一次笔记的代码基础上，在显示客户列表之前，加入筛选的条件:

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //1获得查询条件
        String cust_name = request.getParameter("cust_name");
        //2判断查询条件是否不为空
        DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);

        if(cust_name!=null && !"".equals(cust_name)){
            dc.add(Restrictions.like("cust_name", "%"+cust_name+"%"));
        }
            //不为空=>添加条件
        //3 调用Service查询所有客户
        List<Customer> list = cs.getAll(dc);
        //4 将客户列表放入request域
        request.setAttribute("list", list);
        //5 转发到list.jsp显示
        request.getRequestDispatcher("/jsp/customer/list.jsp").forward(request, response);

    }

Servlet层代码

    public List<Customer> getAll(DetachedCriteria dc) {
        Session session =  HibernateUtils.getCurrentSession();
        //打开事务
        Transaction tx = session.beginTransaction();

        List<Customer> list = customerDao.getAll(dc);

        //关闭事务
        tx.commit();
        return list;
    }

Service层代码

    public List<Customer> getAll(DetachedCriteria dc) {
        //1 获得session
                Session session = HibernateUtils.getCurrentSession();
        //2 将离线对象关联到session
                Criteria c = dc.getExecutableCriteria(session);
        //3 执行查询并返回
        return c.list();
    }

Dao层代码