hibernate的查询方式
1 oid的查询 get() update() delete() save()
2 对象导航的查询
3 HQL Hibernate query language Hibernate的查询语言.
4 QBC 对象的方式进行操作
5 sql
==================================================================
1 HQL 查询方式基于对象的思路进行查询的,因此条件都是实体里面的变量名作为条件
我们将它划分为二类.
1 字段返回的结果都是object类型的
查询所有
from Customer 或者 select c from Customer as c
==============================================================
事例:
Query query = session.createQuery("select c from Customer as c"); //相当于sql语句.
List<Customer> list = query.list(); //执行查询
for (Customer customer : list) {
System.out.println(customer.getCust_name()+"@"+customer.getCust_id());
}
================================================================================
1.2 查询单个或者多个字段
查询单个字段语句:
select 变量名 form Customer
查询多个字段的语句1 返回值不同而已 .返回的是object
select 变量名1,变量名2 .... from 实体
返回的是 list<object>
查询多个字段的语句2 返回的是查询语句里面new的实体对象.因此我们必须提供有参构造,并且要保证有无参构造
select new Customer(cust_name,cust_id) form Customer
返回的是 list<customer>
查询多个字段3 查询的语句是 select new Map(cust_name as cust_name ,cust_id as cust_id ) form Customer
返回的就是 list<map<string ,object>
方式4 还可以new list（cust_name,cust_id）
返回的是 list<list<object>>
======================================================================= =======
事例:
查询单个字段的
Query query = session.createQuery("select cust_name from Customer ");
@SuppressWarnings("unchecked")
List<Object> list = query.list();
for (Object object : list) {
System.out.println(object);
}
======================================================
查询多个字段的哦
Session session = HibernateUtils.openSession();
Query query = session.createQuery("select cust_name,cust_id from Customer ");
@SuppressWarnings("unchecked")
List<Object[]> list = query.list();
for (Object[] object : list) {
System.out.println(Arrays.toString(object));
}
================================================================
Query query = session.createQuery("select new Customer(cust_id,cust_name) from Customer ");
List<Customer> list = query.list();
for (Customer object : list) {
System.out.println(object.getCust_name());
}
=============================================================================---
需要注意我们再map里面as定义的是map的key.然后我们可以通过Beanuitls进行封装数据到实体对象里面
Query query = session.createQuery("select new Map(cust_id as c,cust_name as d)from Customer ");
List<Map<String , Object>> list = query.list();
for (Map<String, Object> map : list) {
Set<String> set = map.keySet();
for (String string : set) {
//通过key获得value值
System.out.println(string +"@@" +map.get(string));
}
}
================================================================================
Query query = session.createQuery("select new List(cust_id ,cust_name)from Customer ");
List<List<Object>> list = query.list();
for (List<Object> list2 : list) {
System.out.println(list2.get(0)); //这是获得的查询出的cust_id
System.out.println(list2.get(1)); //这是获得的查询出的cust_name
}
===========================================================================
1.3 聚合函数注意9 这里的*可以使用
查询的语句是 select 聚合函数(*) from 实体 注意的是如果是查询比如求和就不能使用*了
还有需要注意的是使用聚合函数返回的只有唯一的一个值因此接收的API是
query.uniqueResult();
================================================================================
//操作
Query query = session.createQuery("select count(*) from Customer ");//使用hql 面向对象
Object uniqueResult = query.uniqueResult();
System.out.println(uniqueResult);
========================================================================
.4 去重函数
查询语句 select distinct 去重的变量名 ,变量名2,.......... from 实体 如果是多个字段返回的就是list集合里面是object的数组哦
案例
Session session = HibernateUtils.openSession();
Query query = session.createQuery("select distinct cust_name from Customer c ");
List<Object> list = query.list();
for (Object object : list) {
System.out.println(object);
}
=====================================================================================
1.5 分组查询
查询的语句是 select 聚合函数 分组的变量名 from 实体 group by 分组的变量名 返回的是list里面的是object的数组
案例 根据名称分组 查看人数
// 通过utils工具类，获得session对象
Session session = HibernateUtils.openSession();
Query query = session.createQuery("select count(*) ,cust_name from Customer c group by cust_name");
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
=======================================================================================================================
条件查询
查询后返回的结果都是实体对象.
2 条件查询(单个条件,) :cust_id 替代了sql语句里面的问号.
需要的api是
query.setParameter(字段名,值)
查询语句是 根据的是实体查询,因此后面的条件是实体的属性(变量名)作为条件
from Customer where cust_id > :cust_id and cust_name= :cust_name
//给参数赋值,因为是根据前面的key去赋值的因此可以没有顺序哦
query .setParameter("cust_id", 99L);//语句里面需要几个就赋值几个哦.
================================================================
事例:
Query query = session.createQuery(" from Customer where cust_id > :cust_id and cust_name= :cust_name ");
//给参数赋值
query .setParameter("cust_id", 99L);
query .setParameter("cust_name", "盼盼1313");
//注意的是条件查询处理返回的结果肯定是实体对象.只是数量的不同而已
List<Customer> list = query.list();
for (Customer customer : list) {
System.out.println(customer);
}
============================================================
排序查询
查询的语句是 不写默认升序的哦
form Customer order by 排序的字段 desc (降序) /asc(升序)
===============================================================
案例:
Query query = session.createQuery(" from Customer order by cust_id ");
List <Customer>list = query.list();
for (Customer customer : list) {
System.out.println(customer.getCust_name()+"@"+customer.getCust_id());
}
====================================================================================
分页查询.
语句是: 查询所有的语句 from Customer
注意的是需要设置startIndex 和pageSize;
query.setFirstResult(1);//相当于 startIndex =1
query.setMaxResults(2);//相当于 pageSize=2
===============================================================================
案例:
Query query = session.createQuery(" from Customer ");
query.setFirstResult(1);
query.setMaxResults(2);
List <Customer>list = query.list();
for (Customer customer : list) {
System.out.println(customer.getCust_name()+"@"+customer.getCust_id());
}
=============================================================
1 QBC 查询方式
我们将它划分为二类.
得到对象 session.creartCriteria
1 字段
1.1 查询所有 session.creartCriteria(实体对象.class)
=============================================================================================
案例是:
/ 获得session对象
Session session = HibernateUtils.openSession();
// 通过session获得查询对象 criteria
Criteria criteria = session.createCriteria(Customer.class); // 括号里面的参数就是查询条件了,表示我们查询的就是这个实体对象.
List<Customer> list = criteria.list(); // 这表示接收返回值.
// 遍历打印结果
for (Customer customer : list) {
System.out.println(customer);
}
}
=====================================================================================================================
1.2 查询单个字段
criteria= session.creartCriteria(实体对象.class)
然后赋值
criteria.setProjection(projections.property(变量名);`
返回值是 list<object>
==================================================================================
案例是
Session session = HibernateUtils.openSession();
// 通过session获得查询对象 criteria
Criteria criteria = session.createCriteria(Customer.class); // 括号里面的参数就是查询条件了,表示我们查询的就是这个实体对象.
// 赋值我们需要查询的实体的变量名(也就是需要查询的字段名)
criteria.setProjection(Projections.property("cust_name"));// Projection 投影的意思.
// 查询后返回的就是Object
List<Object> list = criteria.list(); // 这表示接收返回值.
// 遍历打印结果
for (Object object : list) {
System.out.println(object);
========================================================================================
1.2 查询多个字段
查询的语句是
1 获得查询对象
Criteria criteria = session.createCriteria(Customer.class);
2 通过
ProjectionList list2 = Projections.projectionList(); //固定写法.
3 然后再通过上面得到的集合添加每一个查询的字段
list2.add(Projections.property("cust_name")); //字段1
list2.add(Projections.property("cust_id")); //字段2
4 然后需要建立查询字段的集合和查询对象之间的关系
criteria.setProjection(list2); //固定的写法哦.其实就是将添加单个字段时的参数替换为集合对象哦.
5 返回的结果是list<object[]>
案例是
=====================================================================================================================================
// 通过session获得查询对象 criteria
Criteria criteria = session.createCriteria(Customer.class); // 括号里面的参数就是查询条件了,表示我们查询的就是这个实体对象.
// 赋值我们需要查询的实体的变量名(也就是需要查询的字段名)
ProjectionList list2 = Projections.projectionList();
list2.add(Projections.property("cust_name"));
list2.add(Projections.property("cust_id"));
// 然后将这个集合与对象建立关系.
criteria.setProjection(list2);
// 查询后返回的就是Object
List<Object[]> list = criteria.list(); // 这表示接收返回值.
// 遍历打印结果
for (Object[] object : list) {
System.out.println(Arrays.toString(object));
}
}
===========================================================================================================================================
1.3 聚合函数
查询语句是 1
1 获得查询对象
Criteria criteria = session.createCriteria(Customer.class);
2 添加查询count
criteria.setProjection(Projections.rowCount()); // 这是相当于count(*)
或者
criteria.setProjection(Projections.count("实体的变量名"));
3 接收返回值
=============================================================================================
案例是
// 获得session对象
Session session = HibernateUtils.openSession();
// 通过session对象获得查询的对象
Criteria criteria = session.createCriteria(Customer.class);
// 准备聚合查询的条件
criteria.setProjection(Projections.rowCount()); // 这是相当于count(*)
Object result = criteria.uniqueResult(); // 因为聚合查询出的是唯一的一个结果因此使用此APi这与HQL查询的相同的.
System.out.println(result);
==========================================================================================================================
1.4 去重函数
// 查询之去重查询
public void demo5() {
// 通过工具类获得session对象
Session session = HibernateUtils.getSession();
// 开启事物
Transaction transaction = session.beginTransaction();
// 通过session对象获得criteria对象
Criteria criteria = session.createCriteria(Customer.class);
// 编写去重的条件
criteria.setProjection(Projections.distinct(Projections.property("cust_name")));
List list = criteria.list();
for (Object object : list) {
System.out.println(object);
}
// 事物的提交
transaction.commit();
1.5 分组查询
// 通过工具类获得session对象
Session session = HibernateUtils.getSession();
// 开启事物
Transaction transaction = session.beginTransaction();
// 通过session对象获得criteria对象
Criteria criteria = session.createCriteria(Customer.class);
// 赋值开启分组并且求和
ProjectionList List2 = Projections.projectionList();
List2.add(Projections.groupProperty("cust_name"));
List2.add(Projections.count("cust_name"));
// 将集合与criteria建立关系
criteria.setProjection(List2);
Object result = criteria.uniqueResult();
System.out.println(result);
// 事物的提交
transaction.commit();
}
}
2 条件
条件查询(单个条件,)
// 2.1 条件查询 select * from customer where cust_id > 98
@Test
public void demo7(){
Session session = HibernateUtils.openSession();
Transaction transaction = session.beginTransaction();
//操作
Criteria criteria = session.createCriteria(Customer.class);
criteria.add(Restrictions.gt("cust_id", 98L));
List<Customer> list = criteria.list()
条件查询(单]多个个条件,) 操作的对象是 Restrictions 如果是然后可以拼接or或者and
// 2.1 条件查询
//select * from customer where cust_id > 96 or ( cust_id < 100 and cust_name=盼盼 ) and and and
//criteria.add(Restrictions.gt("cust_id", 96L));
criteria.add(Restrictions.or(
Restrictions.gt("cust_id", 96L),
Restrictions.and(
Restrictions.lt("cust_id", 100L),
Restrictions.eq("cust_name", "盼盼")
)
))
List<Customer> list = criteria.list();
排序查询
Criteria criteria = session.createCriteria(Customer.class);
criteria.addOrder(Order.desc("cust_id"));
分页查询.
Criteria criteria = session.createCriteria(Customer.class)
criteria.setFirstResult(0);
criteria.setMaxResults(2);
非常重要的离线查询哦
应用场景条件查询+分页:
//离线查询 DetachedCriteria 离线对象(有Criteria所有api 但不具有操作数据库能力 因为没有session)
//Criteria 在线对象(有session对象) 直接操作数据库
//DetachedCriteria 的目的是 在前面servlet层 拼接完参数后 到dao层转换成在线对象 即可
//封装一个对象 传给dao层使用即可
//前置对象
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Customer.class); //里面的参数是告诉它需要查询的实体对象
detachedCriteria.add(Restrictions.gt("cust_id", 98L)); //拼接参数
//以后的dao层代码
Session session = HibernateUtils.openSession();
Transaction transaction = session.beginTransaction();
//操作
//将离线对象转换成在线对象即可操作
Criteria criteria = detachedCriteria.getExecutableCriteria(session); //将session对象给离线对象.并且激活在线对象
List<Object> list = criteria.list(); //然后直接操作了哦.
for (Object object : list) {
System.out.println(object);
}
transaction.commit();
session.close();
}