Hibernate5种查询检索方式,单表&多表
Hibernate中提供了5种查询方式:
- OID查询
- 对象导航查询
- HQL检索
- QBC检索
- SQL检索
OID检索:
Hibernate根据对象的oid(表中主键)
- 使用get方法 Customer customer = new Customer(Customer.class , 1l);
- 使用load方法
对象导航检索:
Hibernate根据一个已经查询到的对象,获得其关联对象的一种查询方式
LinkMan linkman = session.get(LinkMan.class , 1l);
linkman,getCustomer();
HQL检索:Hibernate Query Language
一种面向对象的查询语言,session.createQuery(hql);
- HQL的简单查询
- HQL的排序查询
- HQL的条件查询
- HQL的投影查询 : 查询对象的某个或某些属性
- HQL的分组统计查询
- HQL的分页查询
/** * HQL查询方式测试类 */ public class demo1 { @Test /** * 初始化数据 */ public void demo1() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Customer customer = new Customer(); customer.setCust_name("啥几把"); for (int i = 0; i < 10; i++) { LinkMan linkMan = new LinkMan(); linkMan.setLkm_name("啊实打" + i); linkMan.setCustomer(customer); ; session.save(linkMan); } session.save(customer); transaction.commit(); } @Test /** * HQL的简单查询,sql支持*,HQL不支持*,但是聚合函数里面可以用,比如count(*) */ public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Query query = session.createQuery("from Customer"); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * HQL的别名查询 */ public void demo3() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Query query = session.createQuery("select c from Customer c"); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * HQL的排序查询,升序asc,降序desc */ public void demo4() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // HQL的排序查询 List<Customer> list = session.createQuery("from Customer order by cust_id desc ").list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * HQL的条件查询,按位置绑定,按名称绑定 */ public void demo5() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 按位置绑定 /* * Query query = session. * createQuery("from Customer where cust_name like ? and cust_id = ? "); * query.setParameter(0, "李兵"); query.setParameter(1, 1l); */ // 按名称绑定 Query query = session.createQuery("from Customer where cust_name like :aaa and cust_id = :bbb "); query.setParameter("aaa", "李%"); query.setParameter("bbb", 1l); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * HQL的投影查询,查询对象的某个或者某些属性 */ public void demo6() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 投影查询,单个属性 // List<Object> list = session.createQuery("select c.cust_name from // Customer c ").list(); // 投影查询,多个属性,封装到数组 /* * List<Object[]> list = * session.createQuery("select c.cust_name,c.cust_id from Customer c "). * list(); for (Object[] objects : list) { * System.out.println(Arrays.toString(objects)); } */ // 投影查询,多个属性,封装到对象(需要提供构造) List<Customer> list = session.createQuery("select new Customer(cust_id,cust_name) from Customer ").list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * HQL的分页查询 */ public void demo7() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 分页查询,单个属性 Query query = session.createQuery("from LinkMan"); query.setFirstResult(20); query.setMaxResults(10); List<LinkMan> list = query.list(); for (LinkMan linkMan : list) { System.out.println(linkMan); } transaction.commit(); } @Test /** * HQL的分组统计查询,聚合函数:count(),min(),avg(),max() */ public void demo8() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // HQL的分组统计查询,得到唯一结果用uniqueResult,否则用list /* * Object object = * session.createQuery("select count(*) from LinkMan").uniqueResult(); * System.out.println(object); */ // 分组统计:按来源,且统计大于1的 List<Object[]> list = session .createQuery("select cust_source,count(*) from Customer group by cust_source having count(*) > 1") .list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); } transaction.commit();
} }
QBC查询:
Query By Criteria:条件查询,更加面向对象的查询方式,并不是所有方式都能用
- 简单查询
- 排序查询
- 分页查询
- 条件查询
- 统计查询
- 离线条件查询 DetachedCriteria(SSH整合经常用到这个模版),离线:脱离Session使用,适合条件多的查询,免于拼接JDBC语句
/** *QBC查询 */ public class HibernateDemo2 { @Test /** * 简单查询 */ public void demo1(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); List<Customer> list = criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * 排序查询 */ public void demo2(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); criteria.addOrder(Order.desc("cust_id")); List<Customer> list = criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * 分页查询 */ public void demo3(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(LinkMan.class); criteria.setFirstResult(10); criteria.setMaxResults(5); List<LinkMan> list = criteria.list(); for (LinkMan linkMan : list) { System.out.println(linkMan); } transaction.commit(); } @Test /** * 条件查询 */ public void demo4(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); /** * 设置条件: * = eq * > gt * >= gq * < lt * <= le * <> ne * like * in * and * or */ criteria.add(Restrictions.eq("cust_source", "小广告")); criteria.add(Restrictions.like("cust_name", "李%")); List<Customer> list = criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * 条件查询 */ public void demo5(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); /** * 设置条件: * = eq * > gt * >= gq * < lt * <= le * <> ne * like * in * and * or */ criteria.add(Restrictions.eq("cust_source", "小广告")); criteria.add(Restrictions.like("cust_name", "李%")); List<Customer> list = criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } @Test /** * 统计查询 */ public void demo6(){ Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); /* * add 普通条件,where后面的 * addOrder :排序 * setProjection :聚合函数,group by having */ criteria.setProjection(Projections.rowCount()); Long num = (Long) criteria.uniqueResult(); System.out.println(num); transaction.commit(); } @Test /** * 离线条件查询 */ public void demo7(){ DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class); detachedCriteria.add(Restrictions.like("cust_name", "李%")); Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = detachedCriteria.getExecutableCriteria(session); List<Customer> list = criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); } }
多表查询:
SQL的多表查询:
连接查询:
交叉链接:得到笛卡尔乘积,不常用。select * from t1 , t2;
内链接: Inner Join(Inner 可以省略,看到Join则是内连接,查到的是两个表公共的部分,交集)
- 隐式内连接(看不到Inner Join,但是和InnerJoin查到的结果一样) select * from t1 , t2 where t1.id = t2.id;
- 显式内连接(能看到Inner Join) select * from t1 inner join t2 on t1.id = t2.id;
外链接:左表以及两表公共部分,左表的数据全都查询出来。或者右表。
- 左外连接(left outer join outer可以省略)SELECT * FROM cst_customer c1 LEFT OUTER JOIN cst_linkman c2 ON c1.cust_id = c2.lkm_cust_id;
- 右外连接
HQL多表查询:
连接查询:
交叉链接:得到笛卡尔乘积,不常用。select * from t1 , t2;
内链接: Inner Join(Inner 可以省略,看到Join则是内连接,查到的是两个表公共的部分,交集)
- 隐式内连接(看不到Inner Join,但是和InnerJoin查到的结果一样) select * from t1 , t2 where t1.id = t2.id;
- 显式内连接(能看到Inner Join) session.createQuery("from Customer c inner join c.linkMans")
- 迫切内连接:HQL迫切内连接:session.createQuery("select distinct c from Customer c inner join fetch c.linkMans") fetch:通知Hibernate把后面对象装到前面对象的集合里
外链接:左表以及两表公共部分,左表的数据全都查询出来。或者右表。
- 左外连接
- 迫切左外连接:
- 右外连接
@Test /** * HQL的多表查询 */ public void demo9() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); /* //HQL普通内连接 List<Object[]> list = session.createQuery("from Customer c inner join c.linkMans").list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); }*/ //HQL迫切内连接:在普通内连接后面添加一个关键字fetch List<Customer> list = session.createQuery("select distinct c from Customer c inner join fetch c.linkMans").list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
SQL查询:
@Test /** * SQL查询 */ public void demo1() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); SQLQuery query = session.createSQLQuery("select * from cst_customer"); /*List<Object[]> list = query.list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); }*/ //封装到实体 query.addEntity(Customer.class); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }