深入研究Hibernate之四：HQL查询

先列出实体类：

package org.yanglg.model;

import java.util.HashSet;
import java.util.Set;

public class Classroom {
	private int id;
	private String name;
	private int grade;

	private Set<Student> stus;
	private Special spe;

	public Special getSpe() {
		return spe;
	}

	public void setSpe(Special spe) {
		this.spe = spe;
	}

	public Classroom() {
		stus = new HashSet<Student>();
	}

	public void addStudent(Student stu) {
		stus.add(stu);
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public int getGrade() {
		return grade;
	}

	public void setGrade(int grade) {
		this.grade = grade;
	}

	public Set<Student> getStus() {
		return stus;
	}

	public void setStus(Set<Student> stus) {
		this.stus = stus;
	}

}

package org.yanglg.model;

import java.util.HashSet;
import java.util.Set;

public class Special {
	private int id;
	private String name;
	private Set<Classroom> clas;

	public Special() {

		clas = new HashSet<Classroom>();

	}

	
	
	public void addClassroom(Classroom cla) {
		clas.add(cla);
	}

	public Set<Classroom> getClas() {
		return clas;
	}

	public void setClas(Set<Classroom> clas) {
		this.clas = clas;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

}

package org.yanglg.model;


/**
 * 学生实体
 * 
 * @author yanglg
 * 
 */
public class Student {
	private int id;
	private String name;
	private String stuno;
	private String sex;
	private Classroom cla;


	public String getSex() {
		return sex;
	}


	public void setSex(String sex) {
		this.sex = sex;
	}


	


	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}


	public String getName() {
		return name;
	}


	public void setName(String name) {
		this.name = name;
	}


	public Classroom getCla() {
		return cla;
	}


	public void setCla(Classroom cla) {
		this.cla = cla;
	}


	public String getStuno() {
		return stuno;
	}


	public void setStuno(String stuno) {
		this.stuno = stuno;
	}


}

package org.yanglg.model;

public class StudentInfo {
	private int id;
	private String stuName;
	private String sex;
	private String claName;

	@Override
	public String toString() {
		return "StudentInfo [id=" + id + ", stuName=" + stuName + ", sex="
				+ sex + ", claName=" + claName + "]";
	}

	public StudentInfo(int id, String stuName, String sex, String claName) {
		super();
		this.id = id;
		this.stuName = stuName;
		this.sex = sex;
		this.claName = claName;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStuName() {
		return stuName;
	}

	public void setStuName(String stuName) {
		this.stuName = stuName;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getClaName() {
		return claName;
	}

	public void setClaName(String claName) {
		this.claName = claName;
	}

}

package org.yanglg.model;

public class StudentInfo2 {
	private int id;
	private String stuName;
	private String sex;
	private String claName;

	public StudentInfo2(int id, String stuName, String sex, String claName,
			String speName) {
		super();
		this.id = id;
		this.stuName = stuName;
		this.sex = sex;
		this.claName = claName;
		this.speName = speName;
	}

	private String speName;

	public String getSpeName() {
		return speName;
	}

	@Override
	public String toString() {
		return "StudentInfo2 [id=" + id + ", stuName=" + stuName + ", sex="
				+ sex + ", claName=" + claName + ", speName=" + speName + "]";
	}

	public void setSpeName(String speName) {
		this.speName = speName;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getStuName() {
		return stuName;
	}

	public void setStuName(String stuName) {
		this.stuName = stuName;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getClaName() {
		return claName;
	}

	public void setClaName(String claName) {
		this.claName = claName;
	}

}

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.yanglg.model">
	<class name="Classroom" table="t_cla">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name" />
		<property name="grade" />

		<set name="stus" lazy="extra">
			<key column="c_id" />
			<one-to-many class="Student" />
		</set>

		<many-to-one name="spe" class="Special" column="spe_id" />


	</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.yanglg.model">
	<class name="Special" table="t_spe">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name" />

		<set name="clas" lazy="extra">
			<key column="spe_id" />
			<one-to-many class="Classroom" />
		</set>


	</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.yanglg.model">
	<class name="Student" table="t_stu">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name" />
		<property name="sex" />

		<property name="stuno" />

		<many-to-one name="cla" class="Classroom" column="c_id" />

	</class>
</hibernate-mapping>

package org.yanglg.test;

import java.util.List;
import java.util.Map;
import java.util.Random;

import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.Test;
import org.yanglg.model.Classroom;
import org.yanglg.model.Special;
import org.yanglg.model.Student;
import org.yanglg.model.StudentInfo;
import org.yanglg.model.StudentInfo2;
import org.yanglg.utils.HibernateUtils;
import org.yanglg.utils.NameGenerateTool;

@SuppressWarnings("unchecked")
public class HQLTest {
	@Test
	public void testSchemaExport() {
		Configuration conf = new Configuration().configure();

		SchemaExport se = new SchemaExport(conf);

		se.create(true, true);
	}

	@Test
	public void testAddSpeial() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			session.beginTransaction();

			String[] str = new String[] { "网络工程", "软件工程", "计算机科学与技术", "护士",
					"财务管理", "新闻", "Java" };

			Random r = new Random();

			for (String ele : str) {

				Special spc = new Special();
				spc.setName(ele);
				session.save(spc);

				for (int i = 1; i <= r.nextInt(4) + 2; i++) {
					Classroom c = new Classroom();
					c.setName((r.nextBoolean() ? "本科" : "专科") + i + "班");
					c.setGrade(r.nextInt(4) + 1);
					c.setSpe(spc);

					session.save(c);

					for (int k = 30; k <= 30 + r.nextInt(40); k++) {
						Student s = new Student();

						Map<String, String> info = NameGenerateTool.genName();

						s.setName(info.get("name"));
						s.setSex(info.get("sex"));
						s.setStuno(String.valueOf(Math.random()));

						s.setCla(c);

						session.save(s);
					}

				}

			}

			session.getTransaction().commit();
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL1() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Classroom> list = session.createQuery("from Classroom").list();

			for (Classroom ele : list) {

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

				System.out.println(ele.getStus().size());

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL2() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Classroom> list = session
					.createQuery("from Classroom c where c.grade = ?")
					.setParameter(0, 1).list();

			for (Classroom ele : list) {
				System.out.println(ele.getName() + "," + ele.getGrade());
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL3() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Classroom> list = session
					.createQuery("from Classroom c where c.grade = :grade")
					.setParameter("grade", 1).list();

			for (Classroom ele : list) {
				System.out.println(ele.getName() + "," + ele.getGrade());
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL4() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Classroom> list = session
					.createQuery(
							"select c from Classroom c where c.grade in :gradeArr and c.id > :id order by grade")
					.setParameterList("gradeArr", new Integer[] { 1, 2 })
					.setParameter("id", 100).list();

			for (Classroom ele : list) {
				System.out.println(ele.getName() + "," + ele.getGrade() + ","
						+ ele.getId());
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL5() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Object[]> list = session.createQuery(
					"select s.sex,count(*) from Student s group by s.sex")
					.list();

			for (Object[] ele : list) {
				System.out.println(ele[0] + "," + ele[1]);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL6() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<Object[]> list = session
					.createQuery(
							"select stu.id,stu.name,stu.sex,cla.name from Student stu left join stu.cla cla where cla.id =2")
					.list();

			for (Object[] ele : list) {
				System.out.println(ele[0]);
				System.out.println(ele[1]);
				System.out.println(ele[2]);
				System.out.println(ele[3]);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}
	@Test
	public void testHQL7() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<StudentInfo> list = session
					.createQuery(
							"select new org.yanglg.model.StudentInfo(stu.id,stu.name,stu.sex,cla.name) from Student stu left join stu.cla cla where cla.id =2")
					.list();

			for (StudentInfo ele : list) {
				System.out.println(ele);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL8() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<StudentInfo2> list = session
					.createQuery(
							"select new org.yanglg.model.StudentInfo2(stu.id,stu.name,stu.sex,cla.name,spe.name) "
									+ "from Student stu left join stu.cla cla left join cla.spe spe")
					.list();

			for (StudentInfo2 ele : list) {
				System.out.println(ele);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

	@Test
	public void testHQL9() {

		Session session = null;
		try {
			session = HibernateUtils.openSession();

			List<StudentInfo2> list = session
					.createQuery(
							"select new org.yanglg.model.StudentInfo2(stu.id,stu.name,stu.sex,cla.name,spe.name) "
									+ "from Student stu left join stu.cla cla left join cla.spe spe order by stu.id,stu.sex")
					.setFirstResult(0).setMaxResults(10).list();

			for (StudentInfo2 ele : list) {
				System.out.println(ele);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			HibernateUtils.closeSession(session);
		}

	}

}

综上：得到如下结论

1、testSchemaExport用来导出表结构

2、testAddSpeial用来导入数据

3、testHQL1：HQL不用表名，直接查询的是对象

4、testHQL2：用别名的方式来限定查询条件

5、testHQL3：用：grade的方式，设置

6、testHQL4：可设置一个List

7、testHQL5：聚合时返回的为List<Object[]>

8、testHQL6：可使用Join，但貌似没有on关键字

9、testHQL7：可到处新建的类，但必须加上完整包名，并且有此构造函数

10、testHQL8：三个表来join

11、testHQL9：分页，setFirstResult，setMaxResults