Hibernate的批量查询
Hibernate的查询大致分为以下三种场景,
1. HQL查询-hibernate Query Language(多表查询,但不复杂时使用)
2. Criteria查询(单表条件查询)
3. 原生SQL查询(复杂的业务查询)
接下来解释三种使用方法:
1.HQL查询-hibernate Query Language(多表查询,但不复杂时使用) Hibernate独家查询语言,属于面向对象的查询语言,HQL语言中不会出现SQL中的表和列,HQL使用的都是JavaBean的类名和属性名。
1.HQL基本查询
(1)查询所有的基本语句
@Test // HQL查询所有数据 public void fun1() { // 1 获得session Session session = HibernateUtil.openSession(); // 2.书写HQL语句 String hql = "from cn.qlq.domain.Customer";// from 类名全路径 // 3.根据hql创建查询对象 Query query = session.createQuery(hql); // 4.根据查询对象获取查询结果 List<Customer> customers = query.list(); System.out.println(customers); }
结果:
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=1, cust_name=XXXXXXXXXX], Customer [cust_id=2, cust_name=联想]]
改进:如果整个项目中只有一个类的名字可以省略包路径,也就是可以只写类名:
@Test // HQL查询所有数据 public void fun1() { // 1 获得session Session session = HibernateUtil.openSession(); // 2.书写HQL语句 // String hql = "from cn.qlq.domain.Customer";// from 类名全路径 String hql = "from Customer";// 如果整个项目中只有这一个类名可以直接写名字 // 3.根据hql创建查询对象 Query query = session.createQuery(hql); // 4.根据查询对象获取查询结果 List<Customer> customers = query.list(); System.out.println(customers); }
(2)根据主键查询单个
@Test // HQL查询单个数据 public void fun2() { // 1 获得session Session session = HibernateUtil.openSession(); // 2.书写HQL语句 // String hql = "from cn.qlq.domain.Customer";// from 类名全路径 String hql = "from Customer where cust_id = 1";// where后面是Customer的属性名称而不是列名 // 3.根据hql创建查询对象 Query query = session.createQuery(hql); // 4.根据查询对象获取查询结果 Customer customer = (Customer) query.uniqueResult(); System.out.println(customer); }
2.HQL条件查询:
(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); }
3.HQL排序与分页查询
分页查询类似于mysql的limit关键字,limit start,pageSize。。。。。。
@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";// :cust_id的名字随便起,只不过习惯性的起做一样 // 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); }
结果:
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_ limit ?, ?
[Customer [cust_id=2, cust_name=新增数据], Customer [cust_id=1, cust_name=程序员111]]
2.Criteria查询(单表条件查询)
criteria查询相比于上面的HQL查询要简单的多。
1.基本查询---查询所有
@Test // 查询所有 public void test1() { // 1 获得session Session session = HibernateUtils.openSession(); // 2.創建criteria进行查询 Criteria criteria = session.createCriteria(Customer.class); List<Customer> list = criteria.list(); System.out.println(list); }
结果:
[Customer [cust_id=1, cust_name=程序员111], Customer [cust_id=2, cust_name=新增数据], Customer [cust_id=3, cust_name=测试名称222], Customer [cust_id=4, cust_name=测试名称222]]
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]
条件列表:
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
3.Hibernate种的原生SQL查询(用于处理一些复杂的业务逻辑)
1.基本查询
1.查询结构是数组集合
@Test // 基本查询--返回数组list public void test1() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); String sql = "select * from cst_customer";// 3.构造Query对象进行查询 SQLQuery sqlQuery = session.createSQLQuery(sql); List<Object[]> list = sqlQuery.list(); for (Object[] o : list) { System.out.println(Arrays.toString(o)); } // 提交事务 tx.commit(); }
结果
[1, 程序员111, null, null, null, null, null, null]
[2, 新增数据, null, null, null, null, null, null]
[3, 测试名称222, null, null, null, null, null, null]
[4, 测试名称222, null, null, null, null, null, null]
2.查询结果是对象集合(重要)
@Test // 基本查询--返回对象list public void test2() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); String sql = "select * from cst_customer";// 3.构造Query对象进行查询 SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.addEntity(Customer.class); List<Customer> list = sqlQuery.list(); System.out.println(list); // 提交事务 tx.commit(); }
结果
[Customer [cust_id=1, cust_name=程序员111], Customer [cust_id=2, cust_name=新增数据], Customer [cust_id=3, cust_name=测试名称222], Customer [cust_id=4, cust_name=测试名称222]]
2.条件查询
@Test // 基本查询--返回对象list public void test3() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); String sql = "select * from cst_customer where cust_id=?";// 3.构造Query对象进行查询 SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.addEntity(Customer.class); sqlQuery.setParameter(0, 1l); Customer cus = (Customer) sqlQuery.uniqueResult(); System.out.println(cus); // 提交事务 tx.commit(); }
结果
Customer [cust_id=1, cust_name=程序员111]
3.聚集函数查询总数
@Test // 基本查询--查询总数 public void test4() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); String sql = "select count(*) from cst_customer";// 3.构造Query对象进行查询 SQLQuery sqlQuery = session.createSQLQuery(sql); BigInteger bg = (BigInteger) sqlQuery.uniqueResult(); System.out.println(bg); // 提交事务 tx.commit(); }
结果
4
4.排序与分页综合查询
@Test // 基本查询--分页查询 public void test5() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); //按cust_id降序排序 String sql = "select * from cst_customer order by cust_id desc limit ?,?";// 3.构造Query对象进行查询 SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.addEntity(Customer.class); /** * 分页查询 取第二页,页大小是二 */ sqlQuery.setParameter(0, 2); sqlQuery.setParameter(1, 2); List<Customer> list = sqlQuery.list(); System.out.println(list); // 提交事务 tx.commit(); }
结果
[Customer [cust_id=2, cust_name=新增数据], Customer [cust_id=1, cust_name=程序员111]]
4.原生SQL映射为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}]
5.hibernate原生SQL进行插入操作
@Test // 原生SQL执行插入操作 public void test6() { // 1.获取与线程绑定的session Session session = HibernateUtils.getCurrentSession(); // 2.开启事务 Transaction tx = session.beginTransaction(); String sql = "insert into user values('xxxx','李四',23)";;// 3.构造Query对象进行插入 SQLQuery sqlQuery = session.createSQLQuery(sql); sqlQuery.executeUpdate(); // 提交事务 tx.commit(); }