七 Hibernate5种查询检索方式,单表&多表

/** * 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查询 */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(); ??} ?????}