hibernate中的批量查询

总共5种查询：

• oid查询-get
• 对象属性导航查询
• HQL查询
• criteria查询
• 原生SQL查询

一、HQL查询(多表查询,但不复杂时使用)

HQL：hibernate Query Language，hibernate独家查询语言,属于面向对象的查询语言

注意：

• 基本和SQL相似，只是表名和列名改为了类名和属性名。
• 聚合函数、排序、部分列查询也和SQL基本一样。
• 查询结果为数字，但是不知道是long、float、int...等等时，可以用Number类型，Number是所有数值类型的父类。

 

1、Query对象API

获得query对象：

Query query = session.createQuery(hql);

 获得单个查询结果

Customer customer = (Customer) query.uniqueResult();

获得多个查询结果

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

设置占位符值

query.setLong(0, 1l);

万能设置占位符方法，下标从0开始

query.setParameter(0, 1l);

设置分页参数

query.setFirstResult(1);
query.setMaxResults(2);

2、基本批量查询

//批量查询
//查询中不是表名，而是完整类名，如果没有重复类名，可以省略包名
@Test
public void fun1() {
	Session session = HibernateUtils.openSession();
	//开启事务
	Transaction tx = session.beginTransaction();
		//----------------------------------------------------------------
	//1书写hql语句
	String hql = " from cn.yuchao.domain.Customer";
	//2根据hql语句获得查询对象
	Query query = session.createQuery(hql);
	//3根据查询对象获得查询结果
	List<Customer> list = query.list();
		
	System.out.println(list);
		//----------------------------------------------------------------
	tx.commit();
	session.close();
}

3、条件查询

//条件查询
//where后面的不是列名，是类的属性名
@Test
public void fun2() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//---------------------------------------------------------
	//1书写hql语句
	String hql = "from Customer where cust_id = 1";
	//2通过hql语句获得查询对象
	Query query = session.createQuery(hql);
	//3通过查询对象获得查询结果
	Customer customer = (Customer) query.uniqueResult();
		
	System.out.println(customer);
		
	//---------------------------------------------------------
	tx.commit();
	session.clear();
}

4、问号占位符

//条件查询
//问号占位符
@Test
public void fun3() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//---------------------------------------------------------
	//1、书写hql语句
	String hql = "from Customer where cust_id = ?";
	//2、通过hql语句获得查询对象
	Query query = session.createQuery(hql);
	//3、设置占位符的值,hibernate的占位符下标是从0开始的
	//query.setLong(0, 1l);
	query.setParameter(0, 1l);
	//4、通过查询对象获得查询结果
	Customer customer = (Customer) query.uniqueResult();
	
	System.out.println(customer);
	//---------------------------------------------------------
	tx.commit();
	session.close();
}

5、命名占位符

//条件查询
//命名占位符
@Test
public void fun4() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------------
	//1、书写hql语句
	String hql = "from Customer where cust_id = :cust_id";
	//2、通过hql语句获得查询对象
	Query query = session.createQuery(hql);
	//3、设置占位符的值
	query.setParameter("cust_id", 2l);
	//4、通过查询对象获得查询结果
	Customer customer = (Customer) query.uniqueResult();
	
	System.out.println(customer);
	//-----------------------------------------------------
	tx.commit();
	session.close();
}

6、分页查询

//分页查询
@Test
public void fun5() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------------
	//1、书写hql语句
	String hql = "from Customer";
	//2、通过hql语句获得查询对象
	Query query = session.createQuery(hql);
	//3、设置分页需要的两个值
	query.setFirstResult(1);
	query.setMaxResults(2);
	//4、通过查询对象获得查询结果
	List<Customer> list = query.list();
		
	System.out.println(list);
	//-----------------------------------------------------
	tx.commit();
	session.close();
}

7、起别名

@Test
public void text() {
	Session session = HibernateUtils.openSession();
	Transaction ts = session.beginTransaction();
	//---------------------------------------------
	//String hql = "from Customer";
	//String hql = "from Customer c";
	String hql = "select c from Customer c";
	Query query = session.createQuery(hql);
	List<Customer> list = query.list();
	System.out.println(list);
	//---------------------------------------------
	ts.commit();
	session.close();
}

8、投影查询

@Test
public void text2() {
	Session session = HibernateUtils.openSession();
	Transaction ts = session.beginTransaction();
	//---------------------------------------------
	//String hql = "select cust_id,cust_name from Customer ";
	//List<Object[]> list = query.list();
		
	//new Customer()是构造函数，需要写，注意要加上空参
	String hql = "select new Customer(cust_id,cust_name) from Customer ";
	Query query = session.createQuery(hql);
	List list = query.list();
	System.out.println(list);
	//---------------------------------------------
	ts.commit();
	session.close();
}

9、多表查询

注意：

• hql相比SQL不用写条件语句（where）
• “迫切”比起寻常的也就是把查询结果封装成了对象集合List<Customer>，普通的就是一条记录封装成一个含两个对象的数组，整个查询结果就是一个数组集合List<Object[]>
• hql中使用distinct是因为有重复的数据

1）原生SQL多表回顾

交叉连接（笛卡尔积）：select * from A,B;

内连接

  |-隐式内连接 select * from A,B where A.aid = B.bid;

  |-显示内连接 select * from A inner join B on A.aid = B.bid;

外连接

  |-左外连接 select * from A left [outer] join B on A.aid = B.bid;

  |-右外连接 select * from A right[outer] join B on A.aid = B.bid;

2）HQL多表连接

内连接（迫切） select [distinct] from Customer c inner join [fetch] c.linkMans

外连接（迫切）

  |-左外连接 select [distinct] c form Customer c left [outer] join [fetch] c.linkMans;

  |-右外连接 select [distinct] c form Customer c right[outer] join [fetch] c.linkMans;

二、Criteria查询(单表条件查询)QBC

Hibernate自创的无语句面向对象查询

1、Criteria对象API

创建criteria对象

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

获得单个对象

Customer customer = (Customer) criteria.uniqueResult();

获得多个对象集合

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

条件查询

criteria.add(Restrictions.eq("cust_id", 1l));
criteria.add(Restrictions.idEq(1l));

设置集合函数

criteria.setProjection(Projections.rowCount());

设置分页参数

criteria.setFirstResult(0);
criteria.setMaxResults(2);

设置排序

//criteria.addOrder(Order.desc("cust_id"));
criteria.addOrder(Order.asc("cust_id"));

2、基本查询

//基础查询
@Test
public void fun1() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------
	//1获得criteria对象
	Criteria criteria = session.createCriteria(Customer.class);
	//2通过criteria对象获得对象集合
	List<Customer> list = criteria.list();
		
	System.out.println(list);
	//-----------------------------------------------
	tx.commit();
	session.close();
}

3、条件查询

//条件查询
//HQL语句中,不可能出现任何数据库相关的信息的
// > 				gt
// >=				ge
// <				lt
// <=				le
// ==				eq
// !=				ne
// in				in
// between and		between
// like 			like
// is not null 		isNotNull
// is null			isNull
// or				or
// and				and
@Test
public void fun2() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------
	//1获得criteria对象
	Criteria criteria = session.createCriteria(Customer.class);
	//2设置条件
	criteria.add(Restrictions.eq("cust_id", 1l));
	//3通过criteria对象获得对象集合
	Customer customer = (Customer) criteria.uniqueResult();
		
	System.out.println(customer);
	//-----------------------------------------------
	tx.commit();
	session.close();
}

4、分页查询

//分页查询
@Test
public void fun3() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------
	//1获得criteria对象
	Criteria criteria = session.createCriteria(Customer.class);
	//2设置分页条件
	criteria.setFirstResult(0);
	criteria.setMaxResults(2);
	//3通过criteria对象获得对象集合
	List<Customer> list = criteria.list();
		
	System.out.println(list);
	//-----------------------------------------------
	tx.commit();
	session.close();
}

5、聚合函数

//聚合函数——查询总记录数
@Test
public void fun4() {
	Session session = HibernateUtils.openSession();
	Transaction tx = session.beginTransaction();
	//-----------------------------------------------
	//1获得criteria对象
	Criteria criteria = session.createCriteria(Customer.class);
	//2设置聚合函数
	criteria.setProjection(Projections.rowCount());
	//3通过criteria对象获得记录数
	Long count = (Long) criteria.uniqueResult();
		
	System.out.println(count);
	//-----------------------------------------------
	tx.commit();
	session.close();
}

6、排序查询

@Test
public void test() {
	Session session = HibernateUtils.openSession();
	Transaction ts = session.beginTransaction();
	//-------------------------------------------
	Criteria criteria = session.createCriteria(Customer.class);
	//criteria.addOrder(Order.desc("cust_id"));
	criteria.addOrder(Order.asc("cust_id"));
	List list = criteria.list();
	System.out.println(list);
	//-------------------------------------------
	ts.commit();
	session.close();
}

7、离线查询

       离线查询就是可以不通过session获得criteria，可以先封装好criteria，然后再和session关联。这么做的好处是在web层就可以封装好条件，然后传递到service/Dao层，避免了不同的查询条件数据类型不同，导致使用不同的方法接受参数。

@Test
public void test2() {
	//假设这里是service/web层
	DetachedCriteria dc = DetachedCriteria.forClass(Customer.class);
	dc.add(Restrictions.idEq(1l));
	//---------------------------------------------
	Session session = HibernateUtils.openSession();
	Transaction ts = session.beginTransaction();
	//---------------------------------------------
	//把criteria关联session
	Criteria criteria = dc.getExecutableCriteria(session);
		
	Customer customer = (Customer) criteria.uniqueResult();
		
	System.out.println(customer);
		
	//---------------------------------------------
	ts.commit();
	session.close();
}

三、原生SQL查询(复杂的业务查询)

@Test
public void fun1() {
	Session session = HibernateUtils.openSession();
	//开始事务
	Transaction tx = session.beginTransaction();
	//---------------------------------------------------------
	String sql = "select * from customer where cust_id = ?";
	SQLQuery query = session.createSQLQuery(sql);
	//设置查询后封装的对象
	query.addEntity(Customer.class);
	query.setParameter(0, 1);
	List<Customer> list = query.list();
	
	for (Customer customer : list) {
		System.out.println(customer.toString());
	}
		
	//接受参数为object[]的集合
	/*List<Object[]> list = query.list();
	for (Object[] objects : list) {
		System.out.println(Arrays.toString(objects));
	}*/

	//---------------------------------------------------------
	tx.commit();
	session.close();
}