首页 > 文章 > Java > java之查询种类、HQL、Criteria、查询优化和练习为客户列表增加查询条件

java之查询种类、HQL、Criteria、查询优化和练习为客户列表增加查询条件

怪我咯原创: 2018-05-18 15:38:561671浏览

一、查询种类

1.oid查询-get

2.对象属性导航查询

3.HQL

4.Criteria

5.原生SQL

二、查询-HQL语法

//学习HQL语法public class Demo {    //基本语法    @Testpublic 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//minpublic 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 {
    
    @Testpublic 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 使用时才加载集合数据.    @Testpublic 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 立即记载集合数据        @Testpublic 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语句)        @Testpublic 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 失效.立即加载.        @Testpublic 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 {
    
    @Testpublic 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 获得sessionSession session = HibernateUtils.getCurrentSession();//2 将离线对象关联到sessionCriteria c = dc.getExecutableCriteria(session);//3 执行查询并返回return c.list();
    }

Dao层代码

以上是java之查询种类、HQL、Criteria、查询优化和练习为客户列表增加查询条件的详细内容。更多信息请关注PHP中文网其他相关文章！

Java sql servlet select Session 对象数据库

声明：

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：使用Java finally语句的一些注意事项下一篇：使用Java中可变长参数的实例

查看更多