Hibernate- QBC-基本查询

01.环境搭建

02.基本查询

1.方法说明

方法	说明
Restrictions.eq	＝
Restrictions.allEq	利用Map来进行多个等于的限制
Restrictions.gt	＞
Restrictions.ge	＞＝
Restrictions.lt	＜
Restrictions.le	＜＝
Restrictions.between	BETWEEN
Restrictions.like	LIKE
Restrictions.in	in
Restrictions.and	and
Restrictions.or	or
Restrictions.sqlRestriction	用SQL限定查询

2，QBC常用限定方法

Restrictions.eq --> equal,等于.

Restrictions.allEq --> 参数为Map对象,使用key/value进行多个等于的比对,相当于多个Restrictions.eq的效果

Restrictions.gt --> great-than > 大于

Restrictions.ge --> great-equal >= 大于等于

Restrictions.lt --> less-than, < 小于

Restrictions.le --> less-equal <= 小于等于

Restrictions.between --> 对应SQL的between子句

Restrictions.like --> 对应SQL的LIKE子句

Restrictions.in --> 对应SQL的in子句

Restrictions.and --> and 关系

Restrictions.or --> or 关系

Restrictions.isNull --> 判断属性是否为空,为空则返回true

Restrictions.isNotNull --> 与isNull相反

Restrictions.sqlRestriction --> SQL限定的查询

Order.asc --> 根据传入的字段进行升序排序

Order.desc --> 根据传入的字段进行降序排序

MatchMode.EXACT --> 字符串精确匹配.相当于"like 'value'"

MatchMode.ANYWHERE --> 字符串在中间匹配.相当于"like '%value%'"

MatchMode.START --> 字符串在最前面的位置.相当于"like 'value%'"

MatchMode.END --> 字符串在最后面的位置.相当于"like '%value'"

01.基本操作

package com.gordon.test;

import java.util.List;

import org.hibernate.Criteria;

import org.hibernate.Session;

import org.hibernate.Transaction;

import org.hibernate.criterion.Order;

import org.hibernate.criterion.Restrictions;

import org.junit.Test;

import com.gordon.domain.Book;

import com.gordon.utils.HibernateUtil;

/**

 * QBC查询

 * @author Administrator

 *

 */

public class TestQBCDemo1 {

	/**

	 * 基本查询

	 */

	@Test

	public void run1() {

		Session session = HibernateUtil.getCurrentSession();

		Transaction transaction = session.beginTransaction();

		// 穿件criteria查询接口

		Criteria query = session.createCriteria(Book.class);

		query.addOrder(Order.asc("price"));

		query.add(Restrictions.le("price", 40.00));

		List<Book> list = query.list();

		for (Book book : list) {

			System.out.println(book.getName());

		}

		transaction.commit();

	}

}

02.分组查询

/**

 * 分组查询

	Hibernate:

	    select

	        this_.publisher_id as y0_,

	        count(*) as y1_,

	        sum(this_.price) as y2_

	    from

	        t_book this_

	    group by

	        this_.publisher_id

	Hibernate:

	    select

	        publisher0_.id as id1_1_0_,

	        publisher0_.name as name2_1_0_

	    from

	        t_publisher publisher0_

	    where

	        publisher0_.id=?

	出版社：电子工业出版社, 图书数量：3, 总价:155.0

	Hibernate:

	    select

	        publisher0_.id as id1_1_0_,

	        publisher0_.name as name2_1_0_

	    from

	        t_publisher publisher0_

	    where

	        publisher0_.id=?

	出版社：北京大学出版社, 图书数量：2, 总价:67.84

	Hibernate:

	    select

	        publisher0_.id as id1_1_0_,

	        publisher0_.name as name2_1_0_

	    from

	        t_publisher publisher0_

	    where

	        publisher0_.id=?

	出版社：人民邮电出版社, 图书数量：2, 总价:107.2

 */

@Test

public void run2() {

	Session session = HibernateUtil.getCurrentSession();

	Transaction transaction = session.beginTransaction();

	// 穿件criteria查询接口

	Criteria query = session.createCriteria(Book.class);

	ProjectionList pList = Projections.projectionList();

	pList.add(Projections.groupProperty("publisher"));

	pList.add(Projections.rowCount());

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

	query.setProjection(pList);

	Publisher publisher = null;

	Long count = null;

	Double sum = null;

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

	for (Object[] obj : list) {

		publisher = (Publisher) obj[0];

		count = (Long) obj[1];

		sum = (Double) obj[2];

		System.out.println("出版社：" + publisher.getName() + ", 图书数量：" + count + ", 总价:" + sum);

	}

	transaction.commit();

}

03.聚合函数

/**

 * 聚合函数

	Hibernate:

	    select

	        sum(this_.price) as y0_,

	        min(this_.price) as y1_,

	        max(this_.price) as y2_,

	        avg(this_.price) as y3_

	    from

	        t_book this_

	[330.04, 31.0, 70.0, 47.14857142857143]

 */

@Test

public void run3() {

	Session session = HibernateUtil.getCurrentSession();

	Transaction transaction = session.beginTransaction();

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

	ProjectionList projectionList = Projections.projectionList();

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

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

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

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

	criteria.setProjection(projectionList);

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

	for (Object[] objects : list) {

		System.out.println(Arrays.toString(objects));

	}

	transaction.commit();

}