Java query types, HQL, Criteria, query optimization and exercises to add query conditions to customer lists

1. Query types

1.oid query-get

2. Object attribute navigation query

##3.HQL

4.Criteria

5. Native SQL

2. Query-HQL syntax

//学习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();
        
    }
}

Learn HQL syntax

#

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();
        
    }
    
}

Learn HQL syntax (not commonly used) - multi-table query syntax

3. Query-Criteria syntax

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();
        
    }
    
    
}

Learn Criteria grammar

Non-offline

Offline

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();
        
    }
    
}

Learning offline Criteria

4. Query Optimization Class-level query

Get method: There is no strategy. When called, the database is immediately queried to load data.

load method: Apply class-level loading strategy

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);
        
    }
    
}

Lazy loading|Lazy loading

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();
            
        }
    
}

Association level lazy loading & crawling strategy (customer gets multiple contacts again)

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();
        
    }
}

Association level lazy loading & fetching strategy (obtaining customers through contacts)

Conclusion: In order to improve efficiency, select should be selected for fetch. The value of lazy should be selected for true. Use all default values.

　No-session problem solving: Expand the scope of 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();
        
    }
    
    
}

Number of crawls

5. Exercise: Add query conditions to the customer list In the last note Based on the code, before displaying the customer list, add the filtering conditions:

    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 layer code

    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 layer code

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

Dao layer code

The above is the detailed content of Java query types, HQL, Criteria, query optimization and exercises to add query conditions to customer lists. For more information, please follow other related articles on the PHP Chinese website!