Hibernate查询方式:
一、OID查询
Hibernate根据对象的OID(主键)进行检索
①、get方法:Customer customer=session.get(Customer.class,1l);
②、load方法:Customer customer=session.load(Customer.class,1l);
二、对象导航检索
Hibernate根据一个已经查询到的对象,获得其关联的对象的一种查询方式。
LinkMan linkMan = session.get(LinkMan.class,1l); Customer customer = linkMan.getCustomer(); Customer customer = session.get(Customer.class,2l); Set<LinkMan> linkMans = customer.getLinkMans();
三、HQL检索
HQL查询:Hibernate Query Language,Hibernate的查询语言,是一种面向对象的方式的查询语言,语法类似SQL。通过session.createQuery(),用于接收一个HQL进行查询方式。
①、简单查询
@Test public void demo() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); //Query query = session.createQuery("from com.hibernate.domain.Customer"); // from Customer(类的全名称,不适表名) Query query = session.createQuery("from Customer "); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
②、别名查询
@Test public void demo2() { 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 public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); //默认asc(升序), 可设置desc(降序) Query query = session.createQuery("from Customer order by cust_id "); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
④、条件查询
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 一、条件参数索引 Query query = session.createQuery("from Customer where cust_name=?1 and cust_id=?0"); query.setParameter(1, "zhangsan"); query.setParameter(0, 4l); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 二、条件参数名 Query query = session.createQuery("from Customer where cust_name=:name and cust_id=:id"); query.setParameter("name", "lisi"); query.setParameter("id", 1l); List<Customer> list = query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
⑤、投影查询
投影查询:查询对象的某个或某些属性
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 单个属性 // List<Object> list = session.createQuery("select c.cust_name from Customer c").list(); // for (Object object : list) { // System.out.println(object); // } // 多个属性 // List<Object[]> list = session.createQuery("select c.cust_id,c.cust_name
from Customer c").list(); // for (Object[] objects : list) { // System.out.println(Arrays.toString(objects)); // } //多个属性,封装到新的对象中 // org.hibernate.PropertyNotFoundException: no appropriate constructor in class:
com.hibernate.domain.Customer // 类中没有适当的构造函数 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(); }
类中没有适当的构造函数解决方案:
public class Customer { public Customer() { } public Customer(Long cust_id, String cust_name) { this.cust_id = cust_id; this.cust_name = cust_name; }
⑥、分页查询
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Query query=session.createQuery("from Customer"); query.setFirstResult(3); query.setMaxResults(2); List<Customer> list=query.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
注意:HQL、QBC两个Demo在实现分页调用setFirstResult和setMaxResults方法一直报java.lang.UnsupportedOperationException: query result offset is not supported的异常
解决方法:修改hibernate方言
<property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
⑦、分组统计查询
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); //聚合函数的使用:count(),max(),min(),avg(),sum() // Object object=session.createQuery("select count(*) from Customer").uniqueResult(); // System.out.println(object); // 分组统计 List<Object[]> list=session.createQuery("select cust_source,count(*) from Customer group by cust_source").list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); } transaction.commit(); }
分组统计加条件:检索统计结果大于2的数据
// 分组统计 List<Object[]> list=session.createQuery("select cust_source,count(*) from Customer group by cust_source
having count(*) > 2").list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); }
⑧、多表查询
1、SQL的多表查询
连接查询
交叉连接:笛卡尔积(查询表的乘积)select * from A,B;
内连接 :inner join (inner 可以省略) 表的交集
隐式内连接:select * from A,B where A.id = B.aid;
显示内连接:select * from A join B on A.id = B.aid;
外连接 :
左外连接:left outer join(outer 可以省略)
select * from A left outer join B on A.id= B.aid;
右外连接:right outer join(outer 可以省略)
select * from A right outer join B on A.id = B.aid;
2、HQL的多表查询
连接查询
交叉连接
内连接:返回值为Object[]数组,
显示内连接
隐式内连接
迫切内连接:相对内连接 inner join fetch关键字,返回值为对象
外连接
左外连接
右外连接
迫切左外连接
@Test public void demo2() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); // 内连接 // List<Object[]> list=session.createQuery("from Customer c inner join c.linkMans").list(); // for (Object[] objects : list) { // System.out.println(Arrays.toString(objects)); // } // 迫切内连接 List<Customer> list = session.createQuery("from Customer c inner join fetch c.linkMans").list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
注意:迫切连接(from Customer c inner join fetch c.linkMans)有可能返回对象重复,建议写成(select distinc c from Customer c inner join fetch c.linkMans)
四、QBC检索
QBC查询:Query By Criteria,条件查询。是一种更加面向对象化的查询的方式。
①、简单查询
@Test public void demo2() { 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.asc("cust_id")); criteria.addOrder(Order.desc("cust_id")); 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.asc("cust_id")); criteria.addOrder(Order.desc("cust_id")); criteria.setFirstResult(3); criteria.setMaxResults(3); List<Customer> list=criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
④、条件查询
@Test public void demo12() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); Criteria criteria = session.createCriteria(Customer.class); /** * = eq * > gt * >= ge * < lt * <= le * <> ne * like * in * and * or */ // and // criteria.add(Restrictions.eq("cust_name", "lisi")); // criteria.add(Restrictions.eq("cust_id", 1l)); // or // criteria.add(Restrictions.or(Restrictions.eq("cust_name", "lisi"), // Restrictions.eq("cust_name", "zhangsan"))); // 多or Disjunction dis = Restrictions.disjunction(); dis.add(Restrictions.eq("cust_name", "lisi")); dis.add(Restrictions.eq("cust_name", "zhangsan")); dis.add(Restrictions.eq("cust_name", "tianqi")); dis.add(Restrictions.eq("cust_name", "zhaoliu")); criteria.add(dis); 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); /** * add() 添加条件 * addOrder() 添加排序 * setProjection() 添加聚合函数和group by having */ criteria.setProjection(Projections.rowCount()); Long num=(Long)criteria.uniqueResult(); System.out.println(num); transaction.commit(); }
⑥、离线查询
@Test public void demo2() { // 创建离线对象DetachedCriteria DetachedCriteria detachedCriteria=DetachedCriteria.forClass(Customer.class); //设置条件 detachedCriteria.add(Restrictions.like("cust_name", "zh%")); Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); //主要:和session建立关系,并创建Criteria对象 Criteria criteria = detachedCriteria.getExecutableCriteria(session); List<Customer> list=criteria.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }
五、SQL检索
①、简单查询结果为Object[]数组
@Test public void demo12() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); SQLQuery sqlQuery=session.createSQLQuery("select * from cst_customer"); List<Object[]> list=sqlQuery.list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); } transaction.commit(); }
②、简单查询结果为对象
@Test public void demo12() { Session session = HibernateUtils.getCurrentSession(); Transaction transaction = session.beginTransaction(); SQLQuery sqlQuery=session.createSQLQuery("select * from cst_customer"); sqlQuery.addEntity(Customer.class); List<Customer> list=sqlQuery.list(); for (Customer customer : list) { System.out.println(customer); } transaction.commit(); }