QBC: Criteria查询
Criteria查询(上)
- QBC:Query By Criteria 标准查询 JPA中Criteria
SQL、HQL 或者各种关键字的语句都不需要书写。全部用方法代替。
Hibernate5.2之前 session.createCriteria(XX);
Hibernate5.2之后 建造者模式 重点
1.基础查询
@Test
public void test01() {
Session session = HibernateUtils.getCurrentSession();
Transaction t= session.beginTransaction();
try {
CriteriaBuilder cBuilder= session.getCriteriaBuilder();
CriteriaQuery<Person> cQuery= cBuilder.createQuery(Person.class);
cQuery.from(Person.class);
List<Person> list = session.createQuery(cQuery).list();
System.out.println(list);
t.commit();
} catch (Exception e) {
if (t!=null) {
t.rollback();
}
}
}
2.条件查询
1.常用条件
/**
* where后跟的条件符:
* > gt() 大于
* >= ge() 大于等于
* < lt() 小于
* <= le() 小于等于
* == equal()
* != notEqual()
*
* path<Number>:Integer,long,short.....
*
*
* between A and B 在A和B之间
* in (......) 在某个集合范围内
* like 模糊匹配
* notlike 不能匹配到的是
* and and() 和
* or or() 或者
* 空 isNull
* isNotNUll
*/
2.相关名称解释
/**
* JPA中的:
* Expression接口: 查询表达式 Type for query expressions. 提供的方法: isNull() isNotNull() in(Object... values)
*
* Path和Predicate是Expression的实现类
* Path:从绑定的类(type)或者集合(collection)中查找简单或者复杂的属性路径。
* 程序:from(Person.class) 查找指定属性 Path<Integer> path = get("属性名"):
*
* Predicate: 同时发生的多个或者单个限制条件
*
* a conjunction(连接词,同时发生) or disjunction(分离) of restrictions. restrictions 限制条件
*/
3.代码示例:
@Test
public void test02() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction = session.beginTransaction();
try {
CriteriaBuilder cBuilder =session.getCriteriaBuilder();
CriteriaQuery<Person> criteriaQuery = cBuilder.createQuery(Person.class);
Root root = criteriaQuery.from(Person.class);
Path<Integer> age = root.get("age");
Predicate pAge= cBuilder.equal(age, 18);
criteriaQuery.where(pAge);
Query<Person> query = session.createQuery(criteriaQuery);
System.out.println(query.list());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
@Test
public void test03() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction = session.beginTransaction();
try {
CriteriaBuilder cBuilder =session.getCriteriaBuilder();
CriteriaQuery<Person> criteriaQuery = cBuilder.createQuery(Person.class);
Root root = criteriaQuery.from(Person.class);
Path<String> name = root.get("name");
Path<Integer> age = root.get("age");
Predicate pAge1= cBuilder.like(name, "%g%");
Predicate pAge2 =cBuilder.isNotNull(age);
Predicate p1= cBuilder.and(pAge1,pAge2);
criteriaQuery.where(p1);
Query<Person> query = session.createQuery(criteriaQuery);
System.out.println(query.list());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
Criteria查询( 下)
1.分页查询:
@Test
public void test02() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
CriteriaBuilder cBuilder= session.getCriteriaBuilder();
CriteriaQuery<Person> cQuery= cBuilder.createQuery(Person.class);
Root root = cQuery.from(Person.class);
Query<Person> query = session.createQuery(cQuery);
query.setFirstResult(0);
query.setMaxResults(2);
System.out.println(query.list());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
2.排序查询:
@Test
public void test01() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
CriteriaBuilder cBuilder= session.getCriteriaBuilder();
CriteriaQuery<Person> cQuery= cBuilder.createQuery(Person.class);
Root root = cQuery.from(Person.class);
Path<String> path= root.get("IDCard");
Order order= cBuilder.desc(path);
cQuery.orderBy(order);
Query<Person> query = session.createQuery(cQuery);
System.out.println(query.list());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
3.聚合函数查询:
@Test
public void test03() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
CriteriaBuilder cBuilder= session.getCriteriaBuilder();
CriteriaQuery<Long> cQuery= cBuilder.createQuery(Long.class);
Root root = cQuery.from(Person.class);
Path<Long> agePath = root.get("age");
Expression<Long> expression = cBuilder.count(agePath);
cQuery.select(expression);
Long sum = session.createQuery(cQuery).uniqueResult();
System.out.println(sum);
transaction.commit();
} catch (Exception e) {
System.out.println(e.getMessage());
if (transaction!=null) {
transaction.rollback();
}
}
}
4.查询多个列:
@Test
public void test04() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
CriteriaBuilder cBuilder= session.getCriteriaBuilder();
CriteriaQuery<Object[]> cQuery= cBuilder.createQuery(Object[].class);
Root root = cQuery.from(Person.class);
Path namePath= root.get("name");
Path addressPath= root.get("address");
cQuery.select(cBuilder.array(namePath,addressPath));
List<Object[]> list = session.createQuery(cQuery).list();
for (int i = 0; i < list.size(); i++) {
Object[] objects = list.get(i);
System.out.println(objects[0]+"==ffff=="+objects[1]);
}
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
5.离线查询:
@Test
public void test05() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
DetachedCriteria detachedCriteria= DetachedCriteria.forClass(Person.class);
detachedCriteria.add(Restrictions.gt("age", 18));
Criteria c = detachedCriteria.getExecutableCriteria(session);
System.out.println(c.list());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}
6.分层思想中Criteria查询
6.1 在线查询:
/**
* 在线查询:
* 完全依赖于Session(包含数据库操作的条件构建以及数据库操作的增删查改)
* 分层思想: dao层只做增删查改
* web层----------------- service层-------------------dao层
* 取参数调用service 业务逻辑(事务) 条件构建和增删查改
*
* 在线查询:service和web层还是以前的写法不需要任何变化。
* dao层书写方式如下:
* @Override
public Person queryPersonById(String id) {
// TODO Auto-generated method stub
Session session = HibernateUtils.getCurrentSession();
//1.CriteriaBuilder
CriteriaBuilder cb = session.getCriteriaBuilder();
//2.CriteriaQuery
CriteriaQuery<Person> cQuery = cb.createQuery(Person.class);
//3.指定查询那个类
Root root = cQuery.from(Person.class);
//构建条件
Path<String> idPath= root.get("IDCard");
Predicate predicate =cb.equal(idPath, id);
cQuery.where(predicate);
//4.执行查询
Person person = session.createQuery(cQuery).uniqueResult();
return person;
}
6.2.离线查询
离线查询:数据库操作的条件构建不需要依赖于session而创建
补充: 离线查询的聚合函数查询:
@Test
public void test06() {
Session session = HibernateUtils.getCurrentSession();
Transaction transaction= session.beginTransaction();
try {
DetachedCriteria detachedCriteria= DetachedCriteria.forClass(Person.class);
Criteria c = detachedCriteria.getExecutableCriteria(session);
c.setProjection(Projections.count("IDCard"));
System.out.println(c.uniqueResult());
transaction.commit();
} catch (Exception e) {
if (transaction!=null) {
transaction.rollback();
}
}
}