RSS

Criteria查询

06 Apr

Hibernate的三种查询方式

1. HQL(Hibernate Query Language)语句,这是类似SQL语句中的一种对象化查询语句

2. 使用Criteria对象,进行按条件查询(Query By Criteria,简称QBC)和按示例查询(Query By Example,简称QBE)

3. 使用原生的SQL(Native SQL)语句

 

Criteria的基本使用

Criteria criteria = session.createCriteria(GuestBook.class);

List<GuestBook> list = criteria.list();

Criteria接口的核心方法

add()

增加一个代表查询条件的Criteriaon对象

addOrder()

增加一个代表排序的Criterion对象

createAlias()

创建关联查询,为所关联的持久化类建立别名

createCriteria()

在互相关联的持久化类之间建立条件约束

setFirstResult()

设定要获取的第一条记录的位置 (从0开始)

setMaxResults()

设定要获取的记录的最大数量

list()

获取满足查询条件的记录的集合

uniqueResult()

获取满足查询条件的唯一记录

 

对查对查询结果进行排序

Criteria criteria = session.createCriteria(GuestBook.class).add(

Restrictions.like("name", "Xa%")).add(

Restrictions.between("id", new Integer(10000), new Integer(100000)));

//    Criterion crit = Restrictions.eq("name", "Xavier");

//    criteria.add(crit);

criteria.addOrder(Order.desc("id")).addOrder(Order.asc("createdTime"));

//    Order order = Order.desc("id");

//    criteria.addOrder(order);

相当于WHERE子句的条件

 

实现分页显示数据

criteria.setMaxResults(30);

criteria.setFirstResult(10);//setFirstResult()从0开始计数

只查询一条记录

Criteria criteria = session.createCriteria(GuestBook.class);

criteria.setMaxResults(1);

GuestBook gb = (GuestBook)criteria.uniqueResult();

Restrictions类

eq()

等于

Restrictions.eq("price", new Double(300.0))

ne()

不等于

Restrictions.ne("name", "Xavier")

allEq()

参数为一个Map对象,使用key/value进行多个等于条件的对比

Restrictions.allEq(map)

gt()

大于

Restrictions.gt("price", new Double(300.0))

ge()

大于等于

Restrictions.ge("price", new Double(300.0))

lt()

小于

Restrictions.lt("price", new Double(300.0))

le()

小于等于

Restrictions.le("price", new Double(300.0))

between()

对应SQL的between子句

Restrictions.between("id", new Integer(1), new Integer(10))

in()

对应SQL的in子句

Restrictions.in("id", {new Integer(1), new Integer(3)})

like()

对应SQL的like子句

Restrictions.like("name", "X%")

ilike()

对应SQL的like子句,但是匹配的字符串忽略大小写

Restrictions.ilike("name", "Xa%")

isNull()

判断是否空

Restrictions.isNull("title")

isNotNull()

判断是否非空

Restrictions.isNotNull("title")

and()

条件与

Restrictions.and(criterion1)

or()

条件或

Restrictions.or(criterion1)

not()

条件非

Restrinctions.not(criterion1, criterion2)

sqlRestriction()

使用原生SQL语句设定查询条件

Restrictions.sqlRestriction("{alias}.name=?", "Xavier", Hibernate.STRING)

 
条件条件"或"方式组合查询条件

Criteria criteria = session.createCriteria(GuestBook.class);

Criterion criterion1 = Restrictions.or(Restrictions.like("name", "Xa%"),

Restrictions.between("id", new Integer(1), new Integer(10)));

//criteria.add(Restrictions.or(Restrictions.like("name", "Xa%"), Restrictions.between("id", new Integer(1), new Integer(10)));

criteria.add(criterion1);

List<GuestBook> list = criteria.list();

使用原生SQL语句设定查询条件

Criteria criteria = session.createCriteria(GuestBook.class);

//下面的name是Table的字段不是属性值

criteria.add(Restrictions.sqlRestriction("{alias}.NAME=?", "Xavier", Hibernate.STRING));

List<GuestBook> list = criteria.list();

统计函数查询

Criteria criteria = session.createCriteria(Product.class);

criteria.setProjection(Projections.max("price");

Double maxValue = (Double) criteria.uniqueResult();

统计函数组合查询条件

Criteria criteria = session.createCriteria(Product.class);

criteria.setProjection(Projections.rowCount());

criteria.add(Restrictions.gt("price", new Double(40.0)));

Integer rowCount = (Integer)criteria.uniqueResult();

获取多个统计函数的值

Criteria criteria = session.createCriteria(Product.class);

ProjectionList proList = Projections.projectionList();

proList.add(Projections.max("price");

proList.add(Projections.min("price");

proList.add(Projections.avg("price");

proList.add(Projections.sum("price");

proList.add(Projections.count("price"));

proList.add(Projections.countDistinct("price"));

proList.add(Projections.rowCount());

criteria.setProjection(proList);

Object values [] = (Object[])criteria.uniqueResult();

分组查询

Criteria criteria = session.createCriteria(Product.class);

criteria.setProjection(Projections.groupProperty("price"));

List<Double> list = criteria.list();

分组统计

Criteria criteria = session.createCriteria(Product.class);

ProjectionList proList = Projections.projectionList();

proList.add(Projections.max("price");

proList.add(Projections.min("price");

proList.add(Projections.avg("price");

proList.add(Projections.sum("price");

proList.add(Projections.count("price"));

proList.add(Projections.countDistinct("price"));

proList.add(Projections.rowCount());

proList.add(Projections.groupProperty("name"));

criteria.setProjection(proList);

List<Object[]> list = criteria.list();

给分组统计结果指定别名

Criteria criteria = session.createCriteria(Product.class);

criteria.setProjection(Projections.alias(Projections.groupProperty("price"), "p"));

//或者通过下面的方式

criteria.setProjection(Projections.groupProperty("price").as("p"));

criteria.addOrder(Order.desc("p"));

List<Double> list = criteria.list();

 

投影查询 – 只查询指定表的指定字段,注意投影查询的数据集类型

Criteria criteria = session.createCriteria(Product.class);

criteria.setProjection(Property.forName("name"));

criteria.setProjection(Property.forName("price"));

criteria.add(Restrictions.gt("price", new Double(40.0)));

List<Object []> list = criteria.list();

使用Property类的forName()方法实现分组统计

Criteria criteria = session.createCriteria(Product.class);

ProjectionList proList = Projections.projectionList();

proList.add(Property.forName("price").max());

proList.add(Property.forName("price").min());

proList.add(Property.forName("price").avg());

proList.add(Property.forName("category.id").group().as("c"));

criteria.setProjection(proList);

criteria.addOrder(Order.desc("c"));

List<Object[]> list = criteria.list();

使用DetachedCriteria对象实现离线查询

//在表现层,构造DetachedCriteria对象,保存用户的动态的查询条件

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(GuestBook.class);

detachedCriteria.add(Restrictions.like("name", "Xa%")).add(Restrictions.between("id", new Integer(1), new Integer(10)));

detachedCriteria.addOrder(Order.desc("id"));

//在业务逻辑层,DetachedCriteria对象与当前的Session对象进行绑定

//获取查询结果

Session session = HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();

Transaction tx = session.beginTransaction();

Criteria criteria = detachedCriteria.getExecutableCriteria(session);

List<GuestBook> list = criteria.list();

tx.commit();

 

根据示例对象进行查询

GuestBook gb = new GuestBook();

gb.setName("Xavier");

Session session = HibernateSessionFactoryUtil.getSessionFactory().getCurrentSession();

Transaction tx = session.beginTransaction();

Criteria criteria = session.createCriteria(GuestBook.class);

criteria.add(Example.create(gb));

List <GuestBook> list = criteria.list();

tx.commit();

CriteriaTest .java

package hibernate.example.test;

import java.util.List;

import org.hibernate.Criteria;

import org.hibernate.Session;

import org.hibernate.cfg.Configuration;

//import org.hibernate.criterion.Criterion;

import org.hibernate.criterion.Order;

import org.hibernate.criterion.Restrictions;

import hibernate.example.vo.GuestBook;

public class CriteriaTest {

 

public static void main(String[] args) {

CriteriaTest test = new CriteriaTest();

//        test.list();

test.sortResult();

}

@SuppressWarnings("unchecked")

public void list(){

Session session = new Configuration().configure().buildSessionFactory().getCurrentSession();

session.beginTransaction();

Criteria criteria = session.createCriteria(GuestBook.class);

List<GuestBook> list = criteria.list();

for(GuestBook gb: list){

System.out.println("ID: " + gb.getId());

System.out.println("Name: " + gb.getName());           

}

session.getTransaction().commit();

}

@SuppressWarnings("unchecked")

public void sortResult(){

Session session = new Configuration().configure().buildSessionFactory().getCurrentSession();

session.beginTransaction();

Criteria criteria = session.createCriteria(GuestBook.class).add(

Restrictions.like("name", "Xa%")).add(

Restrictions.between("id", new Integer(10000), new Integer(100000)));

//        Criterion crit = Restrictions.eq("name", "Xavier");

//        criteria.add(crit);

criteria.addOrder(Order.desc("id")).addOrder(Order.asc("createdTime"));

//        Order order = Order.desc("id");

//        criteria.addOrder(order);

List<GuestBook> list = criteria.list();

for(GuestBook gb: list){

System.out.println("ID: " + gb.getId());

System.out.println("Name: " + gb.getName());           

}

session.getTransaction().commit();

}

}

Advertisements
 
Leave a comment

Posted by on 04/06/2011 in HIBERNATE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: