Hebernate批量查询

1、HQL查询-hibernate Query Language(多表查询,但不复杂时使用)

Hibernate独家查询语言,属于面向对象的查询语言

• 基本查询

    //-------------------------------------------
        //1> 书写HQL语句
        //String hql = " from Customer where cust_id = 1 "; 
        String hql = " from Customer "; // 查询所有Customer对象

        //2> 根据HQL语句创建查询对象
        Query query = session.createQuery(hql);

        //3> 根据查询对象获得查询结果集
        List<Customer> list = query.list(); // 返回list结果
        //query.uniqueResult();//接收唯一的查询结果

        System.out.println(list);
        //-------------------------------------------

• 条件查询

  • 1、’ ? ’ 号占位符

          //-------------------------------------------
      //1> 书写HQL语句
      String hql = " from Customer where cust_id = ? "; // 查询所有Customer对象
  
      //2> 根据HQL语句创建查询对象
      Query query = session.createQuery(hql);
  
      //设置参数
      //query.setLong(0, 1l);
      query.setParameter(0, 1l);
  
      //3> 根据查询对象获得查询结果
      Customer c = (Customer) query.uniqueResult();
  
      System.out.println(c);
      //-------------------------------------------

  • 2、命名占位符

  //-------------------------------------------
      //1> 书写HQL语句
      String hql = " from Customer where cust_id = :cust_id "; // 查询所有Customer对象
      //2> 根据HQL语句创建查询对象
      Query query = session.createQuery(hql);
      //设置参数
      query.setParameter("cust_id", 1l);
      //3> 根据查询对象获得查询结果
      Customer c = (Customer) query.uniqueResult();
  
      System.out.println(c);
      //-------------------------------------------

• 分页查询

//-------------------------------------------
        //1> 书写HQL语句
        String hql = " from Customer  "; // 查询所有Customer对象

        //2> 根据HQL语句创建查询对象
        Query query = session.createQuery(hql);

        //设置分页信息 limit ?,?
        query.setFirstResult(1);
        query.setMaxResults(1);

        //3> 根据查询对象获得查询结果
        List<Customer> list =  query.list();

        System.out.println(list);
        //-------------------------------------------

2、Criteria查询(单表条件查询)

Hibernate自创的无语句面向对象查询

• 基本查询

//-------------------------------------------

        //查询所有的Customer对象
        Criteria criteria = session.createCriteria(Customer.class);

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

        System.out.println(list);

//      Customer c = (Customer) criteria.uniqueResult();

        //-------------------------------------------

• 条件查询

  //条件查询
  //HQL语句中,不可能出现任何数据库相关的信息的
  // >                gt
  // >=               ge
  // <                lt
  // <=               le
  // ==               eq
  // !=               ne
  // in               in
  // between and      between
  // like             like
  // is not null      isNotNull
  // is null          isNull
  // or               or
  // and              and
  

//-------------------------------------------
        //创建criteria查询对象
        Criteria criteria = session.createCriteria(Customer.class);
        //添加查询参数 => 查询cust_id为1的Customer对象
        criteria.add(Restrictions.eq("cust_id", 1l));
        //执行查询获得结果
        Customer c = (Customer) criteria.uniqueResult();
        System.out.println(c);
        //-------------------------------------------

• 分页 查询

//-------------------------------------------
        //创建criteria查询对象
        Criteria criteria = session.createCriteria(Customer.class);
        //设置分页信息 limit ?,?
        criteria.setFirstResult(1);
        criteria.setMaxResults(2);
        //执行查询
        List<Customer> list = criteria.list();

        System.out.println(list);
        //-------------------------------------------

• 设置查询总记录数

//-------------------------------------------
        //创建criteria查询对象
        Criteria criteria = session.createCriteria(Customer.class);
        //设置查询的聚合函数 => 总行数
        criteria.setProjection(Projections.rowCount());
        //执行查询
        Long count = (Long) criteria.uniqueResult();

        System.out.println(count);
        //-------------------------------------------

3、原生SQL查询(复杂的业务查询)

• 基本查询

  • 1、返回数组List

    //-------------------------------------------
        //1 书写sql语句
        String sql = "select * from cst_customer";
    
        //2 创建sql查询对象
        SQLQuery query = session.createSQLQuery(sql);
    
        //3 调用方法查询结果
        List<Object[]> list = query.list();
        //query.uniqueResult();
    
        for(Object[] objs : list){
            System.out.println(Arrays.toString(objs));
        }
    
        //-------------------------------------------

  • 2、返回对象List

//-------------------------------------------
        //1 书写sql语句
        String sql = "select * from cst_customer";

        //2 创建sql查询对象
        SQLQuery query = session.createSQLQuery(sql);
        //指定将结果集封装到哪个对象中
        query.addEntity(Customer.class);

        //3 调用方法查询结果
        List<Customer> list = query.list();

        System.out.println(list);
        //-------------------------------------------

• 条件查询

//-------------------------------------------
        //1 书写sql语句
        String sql = "select * from cst_customer where cust_id = ? ";

        //2 创建sql查询对象
        SQLQuery query = session.createSQLQuery(sql);

        query.setParameter(0, 1l);
        //指定将结果集封装到哪个对象中
        query.addEntity(Customer.class);

        //3 调用方法查询结果
        List<Customer> list = query.list();

        System.out.println(list);
        //-------------------------------------------

• 分页查询

//-------------------------------------------
        //1 书写sql语句
        String sql = "select * from cst_customer  limit ?,? ";

        //2 创建sql查询对象
        SQLQuery query = session.createSQLQuery(sql);

        query.setParameter(0, 0);
        query.setParameter(1, 1);
        //指定将结果集封装到哪个对象中
        query.addEntity(Customer.class);

        //3 调用方法查询结果
        List<Customer> list = query.list();

        System.out.println(list);
        //-------------------------------------------