在Hibernate中提供了很多种的查询的方式。Hibernate共提供了五种查询方式
1、Hibernate的查询方式:OID查询
- OID检索:Hibernate根据对象的OID(主键)进行检索
1.1 使用get方法
Customer customer = session.get(Customer.class,1l)
1.2 使用load方法
Customer customer = session.load(Customer.class,1l);
2、Hibernate的查询方式:对象导航检索
对象导航检索:Hibernate根据一个已经查询到的对象,获得其关联的对象的一种查询方式。
LinkMan linkMan = session.get(LinkMan.class,1l);
Customer customer = linkMan.getCustomer();
Customer customer = session.get(Customer.class,2l);
Set<LinkMan> linkMans = customer.getLinkMans();
3、Hibernate的查询方式:HQL检索
HQL查询:Hibernate Query Language,Hibernate的查询语言,是一种面向对象的方式的查询语言,语法类似SQL。通过session.createQuery(),用于接收一个HQL进行查询方式。
下面是一个例子,已一对多为例,表使用的是一对多中的两张表(前文中有)
3.1 初始化数据
/**
* 初始化数据,(手动执行三次,两个名字都不相同)
*/
public void demo1() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 创建一个客户
Customer customer = new Customer();
customer.setCust_name("小李");
for (int i = 1; i <= 10; i++) {
LinkMan linkMan = new LinkMan();
linkMan.setLkm_name("王东" + i);
linkMan.setCustomer(customer);
customer.getLinkMans().add(linkMan);
session.save(linkMan);
}
session.save(customer);
tx.commit();
}
3.2 HQL 简单查询
/**
* HQL的简单查询
*/
public void demo2() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 简单的查询
Query query = session.createQuery("from Customer");
List<Customer> list = query.list();
// sql中支持*号的写法:select * from cst_customer; 但是在HQL中不支持*号的写法。
/*
* Query query = session.createQuery("select * from Customer");// 报错
* List<Customer> list = query.list();
*/
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
3.3 HQL 别名查询
/**
* 别名查询
*/
public void demo3() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 别名的查询
/*
* Query query = session.createQuery("from Customer c"); List<Customer>
* list = query.list();
*/
Query query = session.createQuery("select c from Customer c");
List<Customer> list = query.list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
3.4 HQL 排序查询
/**
* 排序查询
*/
public void demo4() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 排序的查询
// 默认情况
// List<Customer> list = session.createQuery("from Customer order by
// cust_id").list();
// 设置降序排序 升序使用asc 降序使用desc
List<Customer> list = session.createQuery("from Customer order by cust_id desc").list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
3.5 HQL 条件查询
/**
* 条件查询
*/
public void demo5() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 条件的查询
// 一、按位置绑定:根据参数的位置进行绑定。
// 一个条件
/*
* Query query = session.createQuery("from Customer where cust_name = ?"
* ); query.setParameter(0, "李兵"); List<Customer> list = query.list();
*/
// 多个条件
/*
* Query query = session.createQuery(
* "from Customer where cust_source = ? and cust_name like ?");
* query.setParameter(0, "小广告"); query.setParameter(1, "李%");
* List<Customer> list = query.list();
*/
// 二、按名称绑定
Query query = session.createQuery("from Customer where cust_source = :aaa and cust_name like :bbb");
// 设置参数:
query.setParameter("aaa", "朋友推荐");
query.setParameter("bbb", "李%");
List<Customer> list = query.list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
3.6 HQL 投影查询
- 投影查询:查询对象的某个或某些属性。
/**
* 投影查询
*/
public void demo6() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = 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_name,c.cust_source from Customer c").list(); for
* (Object[] objects : list) {
* System.out.println(Arrays.toString(objects)); }
*/
//要提供构造方法,查几个属性,就提供特定的构造方法
List<Customer> list = session.createQuery("select new Customer(cust_name,cust_source) from Customer").list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
3.7 HQL 分页查询
/**
* 分页查询
*/
public void demo7() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = 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);
}
tx.commit();
}
3.7 HQL 分组统计查询
/**
* 分组统计查询
*/
public void demo8() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = 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));
}
tx.commit();
}
3.8 HQL 多表查询
-
sql的多表查询
- 连接查询
-
交叉连接:笛卡尔积
select * from A,B;
-
内连接:inner join(inner 可以省略)
两个表的交集- 隐式内连接:
//在sql语句中看不见inner join select * from A,B where A.id = B.aid;
- 显示内连接:
//在sql语句中能看见inner join select * from A inner join B on A.id = B.aid;
-
外连接
- 左外连接
//left outer join(outer 可以省略),查询左边表的全部信息,和两张表的公共部分 select * from A left outer join B on A.id= B.aid;
- 右外连接
//light outer join(outer 可以省略) select * from A right outer join B on A.id = B.aid;
-
- 连接查询
-
HQL的多表查询
- 连接查询
- 交叉连接
- 内连接
- 显示内连接
- 隐式内连接
- 迫切内连接
- 连接
- 左外连接
- 右外连接
- 迫切左外连接
- 连接查询
/**
* HQL的多表查询
*/
public void demo9() {
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// SQL:SELECT * FROM cst_customer c INNER JOIN cst_linkman l ON
// c.cust_id = l.lkm_cust_id;
// HQL:内连接 from Customer c inner join c.linkMans
/*
* List<Object[]> list = session.createQuery(
* "from Customer c inner join c.linkMans").list(); for (Object[]
* objects : list) { System.out.println(Arrays.toString(objects)); }
*/
// HQL:迫切内连接 其实就在普通的内连接inner join后添加一个关键字fetch. from Customer c inner
// join fetch c.linkMans
List<Customer> list = session.createQuery("select distinct c from Customer c inner join fetch c.linkMans")
.list();// 通知hibernate,将另一个对象的数据封装到该对象中
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
4、Hibernate的查询方式:QBC检索
- QBC查询:Query By Criteria,条件查询。是一种更加面向对象化的查询的方式。
4.1 简单查询
/**
* 简单的查询
*/
public void demo1(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 获得Criteria的对象
Criteria criteria = session.createCriteria(Customer.class);
List<Customer> list = criteria.list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
4.2 排序查询
/**
* 排序查询
*/
public void demo2(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = 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);
}
tx.commit();
}
4.3 分页查询
/**
* 分页查询
*/
public void demo3(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 分页查询
Criteria criteria = session.createCriteria(LinkMan.class);
criteria.setFirstResult(10);
criteria.setMaxResults(10);
List<LinkMan> list = criteria.list();
for (LinkMan linkMan : list) {
System.out.println(linkMan);
}
tx.commit();
}
4.4 条件查询
/**
* 条件查询
*/
public void demo4(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = session.beginTransaction();
// 条件查询
Criteria criteria = session.createCriteria(Customer.class);
// 设置条件:
/**
* = eq
* > gt
* >= ge
* < lt
* <= le
* <> ne
* like
* in
* and
* or
*/
criteria.add(Restrictions.eq("cust_source", "小广告"));
// criteria.add(Restrictions.or(Restrictions.like("cust_name", "李%")));
criteria.add(Restrictions.like("cust_name", "李%"));
List<Customer> list = criteria.list();
for (Customer customer : list) {
System.out.println(customer);
}
tx.commit();
}
4.5 统计查询
/**
* 统计查询
*/
public void demo5(){
Session session = HibernateUtils.getCurrentSession();
Transaction tx = 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);
tx.commit();
}
4.6 离线条件查询(SSH)—DetachedCriteria
/**
* 离线条件查询
*/
public void demo6(){
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();
}
}