Hibernate Hql

package org.zyq.test;


import java.util.Iterator;
import java.util.List;


import org.junit.Test;
import org.zyq.hibernate.hql.Classes;
import org.zyq.hibernate.hql.Student;


/**
 * Hibernate HQL查询测试
 * 
 * @author zyq
 */
public class TestHql extends TestBase {


@Test
public void testInit() throws Exception {
for (int i = 0; i < 10; i++) {
Classes classes = new Classes();
classes.setName("班级" + i);
session.save(classes);
for (int j = 0; j < 10; j++) {
Student student = new Student();
student.setName("班级" + i + "的学生" + j);
student.setCreateTime(randomDate("2013-01-01", "2014-11-01"));
// 在内存中建立由student指向classes的引用
student.setClasses(classes);
session.save(student);
}
}


for (int i = 0; i < 5; i++) {
Classes classes = new Classes();
classes.setName("无学生班级" + i);
session.save(classes);
}


for (int i = 0; i < 10; i++) {
Student student = new Student();
student.setName("无业游民" + i);
session.save(student);
}
}


/**
* 单一属性查询，返回结果集属性列表，元素类型和实体类中相应的属性类型一致
* 
* @throws Exception
*/
@Test
public void testSimplePropertyQuery1() throws Exception {
List students = session.createQuery("select name from Student").list();
for (Iterator<String> iter = students.iterator(); iter.hasNext();) {
System.out.println(iter.next());
}
}


/**
* 多个属性查询
* 
* @throws Exception
*/
@Test
public void testSimplePropertyQuery2() throws Exception {
List students = session.createQuery(
"select name , createTime from Student").list();
for (Iterator<Object[]> iter = students.iterator(); iter.hasNext();) {
Object[] obj = iter.next();
System.out.println(obj[0] + "-" + obj[1]);
}
}


/**
* 返回Student对象
* 
* @throws Exception
*/
@Test
public void testSimplePropertyQuery3() throws Exception {
// public Student(String name, Date createTime) {}--需要存在这个构造器
List students = session.createQuery(
"select new Student(name , createTime) from Student").list();
for (Iterator<Student> iter = students.iterator(); iter.hasNext();) {
Student obj = iter.next();
System.out.println(obj.getName() + "-" + obj.getCreateTime());
}
}


// ---------实体对象查询【重要】-----------
/*
* N + 1问题，在默认情况下，使用query.iterate查询，有可以能出现N+1问题 所谓的N+1是在查询的时候发出了N+1条sql语句 1:
* 首先发出一条查询对象id列表的sql N: 根据id列表到缓存中查询，如果缓存中不存在与之匹配的数据，那么会根据id发出相应的sql语句
* list和iterate的区别？ list每次都会发出sql语句，list会向缓存中放入数据，而不利用缓存中的数据
* iterate：在默认情况下iterate利用缓存数据，但如果缓存中不存在数据有可以能出现N+1问题
*/
@Test
public void testSimpleObjectQueryTest1() throws Exception {
List students = session.createQuery(" from Student as s ").list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleObjectQueryTest2() throws Exception {
/**
* 出现N+1现象 1：发出查询id列表的sql * Hibernate: select student0_.id as col_0_0_
* from t_student student0_ N：在依次发出根据id查询Student对象的sql * Hibernate:
* select student0_.id as id1_0_, student0_.name as name1_0_, *
* student0_.createTime as createTime1_0_, student0_.classesid as
* classesid1_0_ * from t_student student0_ where student0_.id=?
*/
Iterator iter = session.createQuery(" from Student as s ").iterate();
while (iter.hasNext()) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleObjectQueryTest3() throws Exception {
session.createQuery(" from Student as s ").list();


// 不会出现N+1现象
// Hibernate:
// select
// student0_.id as id11_,
// student0_.name as name11_,
// student0_.createTime as createTime11_,
// student0_.classes_id as classes4_11_
// from
// hql_student student0_
// Hibernate:
// select
// student0_.id as col_0_0_
// from
// hql_student student0_
Iterator iter = session.createQuery(" from Student as s ").iterate();
while (iter.hasNext()) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleObjectQueryTest4() throws Exception {
session.createQuery(" from Student as s ").list();
// Hibernate:
// select
// student0_.id as id11_,
// student0_.name as name11_,
// student0_.createTime as createTime11_,
// student0_.classes_id as classes4_11_
// from
// hql_student student0_
// Hibernate:
// select
// student0_.id as id11_,
// student0_.name as name11_,
// student0_.createTime as createTime11_,
// student0_.classes_id as classes4_11_
// from
// hql_student student0_
// 会发出两次查询，在默认情况下list每次都会向数据库发出查询对象的sql，除非配置查询缓存，所以下面的list操作
// 虽然在一级缓存中已经有了对象数据，但list默认情况下不会利用缓存，
// 而再次发出sql 默认情况下，list会向缓存中放入数据，但不会利用数据
List students = session.createQuery(" from Student as s ").list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


// ------------------3、条件查询【重要】------------
// 可以采用拼字符串的方式传递参数
// * 可以采用 ？来传递参数（索引从0开始）
// * 可以采用 :参数名 来传递参数
// * 如果传递多个参数，可以采用setParamterList方法
// * 在hql中可以使用数据库的函数，如：date_format
@Test
public void testSimpleConditionQueryTest() {
// 拼接字符串
List students = session.createQuery(
" from Student as s where name like '%1%' ").list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleConditionQueryTest2() {
// 设置占位符？,参数的索引从0开始,传递的参数值，不用单引号引起来,不能使用like '?'
List students = session.createQuery(
" from Student as s where name like ? ").setParameter(0, "%1%")
.list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleConditionQueryTest3() {
// 使用 ：参数名称 的方式传递参数值(冒号+参数名)
List students = session.createQuery(
" from Student as s where name like :filterName ")
.setParameter("filterName", "%1%").list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleConditionQueryTest4() {
// 支持in，需要使用setParameterList进行参数传递
List students = session.createQuery(
" from Student as s where id in (:myids) ").setParameterList(
"myids", new Object[] { 1L, 2L, 3L, 4L, 5L }).list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName());
}
}


@Test
public void testSimpleConditionQueryTest5() {
// 支持in，需要使用setParameterList进行参数传递
List students = session
.createQuery(
" from Student as s where date_format(s.createTime, '%Y-%m')=? ")
.setParameter(0, "2014-02").list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName() + "-"
+ student.getCreateTime());
}
}


// ------------4、hibernate也支持直接使用sql进行查询------------
@Test
public void testSqlQuery() throws Exception {
List students = session.createSQLQuery("select * from hql_student")
.list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
// Student student = (Student)iter.next();
// -----------不能直接转成student对象
// System.out.println(student.getName()+"-"+student.getCreateTime());
Object[] obj = (Object[]) iter.next(); // ---------只能使用这种方式获取数据信息
System.out.println(obj[0] + "," + obj[1]);
}
}


// -------------------------分页查询【重要】--------
/**
* 分页查询
* 
* @throws Exception
*/
@Test
public void testPageQueryTest() throws Exception {
List students = session.createQuery("from Student").setFirstResult(1)
.setMaxResults(5).list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Student student = (Student) iter.next();
System.out.println(student.getName() + "-"
+ student.getCreateTime());
}


}


// -------------------------对象导航查询查询【重要】--------
/**
* 对象导航查询查询
* 
* @throws Exception
*/
@Test
public void testObjectNavQuery() throws Exception {
List students = session.createQuery(
"select s.name from Student s where s.classes.name like '%1%'")
.list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
System.out.println(iter.next());
}
}


// ------------9、连接查询【重要】-----------------
@Test
public void testJoinQuery() throws Exception {
// inner join , left join , out join
List students = session.createQuery(
" select c.name, s.name from Student s inner join s.classes c ")
.list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Object[] obj = (Object[]) iter.next();
System.out.println(obj[0] + "," + obj[1]);
}
}


// -----------------统计查询【重要】------------------
@Test
public void testStatQueryTest() throws Exception {
Long count = (Long) session.createQuery("select count(*) from Student")
.uniqueResult();
System.out.println(count);
}


@Test
public void testStatQueryTest2() throws Exception {
List students = session.createQuery(
"select c.name, count(s.id) from Student s join s.classes c group by c.name order by c.name")
.list();
for (Iterator iter = students.iterator(); iter.hasNext();) {
Object[] obj = (Object[]) iter.next();
System.out.println(obj[0] + ", " + obj[1]);
}
}


}