Hibernate(十一)HQL查询

HQL：Hibernate Query Language

HQL有几个特点：

• 与SQL相似，SQL中的语法基本上都可以直接使用;
  
• SQL查询的是表和表中的列；HQL查询的是对象与对象中的属性;
  
• HQL的关键字不区分大小写，类名与属性名区分大小写;
  
• select可以省略。

package test.hibernate.hbmHQL;

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

public class Department {

	private Integer id;
	private String name;
	private Set<Employee> employees = new HashSet<Employee>();

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

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

	public Set<Employee> getEmployees() {
		return employees;
	}

	public void setEmployees(Set<Employee> employees) {
		this.employees = employees;
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return "[employee:id=" + id + ",name=" + name + "]";
	}

}

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="test.hibernate.hbmHQL">
	<class name="Employee" table="employee_hql">
		<id name="id" type="integer" column="id">
			<generator class="native" />
		</id>
		<property name="name" />
		<many-to-one name="department" class="Department" column="departmentId"></many-to-one>
	</class>
	<!-- <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN ? 
		AND ? </query> -->
	<!-- <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN :idMin 
		AND :idMax </query> -->
	<query name="queryByIdRange">
		<!--这里<小于号会被看成特殊字符而报错，用<![CDATA[...]]>包起来表示没有特殊字符 -->
		<![CDATA[FROM Employee e WHERE e.id >= :idMin AND e.id <= :idMax]]>
	</query>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="test.hibernate.hbmHQL">
	<class name="Department" table="department_hql">
		<id name="id" type="integer" column="id">
			<generator class="native" />
		</id>
		<property name="name" />
		<set name="employees" cascade="all">
			<key column="departmentId"></key>
			<one-to-many class="Employee" />
		</set>
	</class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="test.hibernate.hbmHQL">
	<class name="Employee" table="employee_hql">
		<id name="id" type="integer" column="id">
			<generator class="native" />
		</id>
		<property name="name" />
		<many-to-one name="department" class="Department" column="departmentId"></many-to-one>
	</class>
	<!-- <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN ? 
		AND ? </query> -->
	<!-- <query name="queryByIdRange"> FROM Employee e WHERE e.id BETWEEN :idMin 
		AND :idMax </query> -->
	<query name="queryByIdRange">
		<!--这里<小于号会被看成特殊字符而报错，用<![CDATA[...]]>包起来表示没有特殊字符 -->
		<![CDATA[FROM Employee e WHERE e.id >= :idMin AND e.id <= :idMax]]>
	</query>
</hibernate-mapping>

package test.hibernate.hbmHQL;

import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.junit.Test;

public class App {

	private static SessionFactory sessionFactory = new Configuration()//
			.configure()//
			.addClass(Department.class)// 添加Hibernate实体类（加载对应的映射文件）
			.addClass(Employee.class)//
			.buildSessionFactory();

	@Test
	public void testSave() throws Exception {
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		// --------------------------------------------

		// 构建对象
		for (int i = 1; i <= 10; i++) {
			Department department = new Department();
			department.setName("部门_" + i);
			session.save(department);
		}
		for (int i = 1; i <= 20; i++) {
			Employee employee = new Employee();
			employee.setName("员工_" + i);
			employee.setDepartment(null);
			session.save(employee);
		}

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

	// 获取
	@Test
	public void testHQL() throws Exception {
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		// ===========================================
		// 获取数据
		String hql = null;
		// hql = "FROM Employee";//HQL中查询select可以省略，但不能用select *
		// hql = "FROM Employee AS e";
		// hql = "FROM Employee e";//使用别名，as关键字可以省略
		// hql = "FROM Employee e WHERE e.id < 30 AND e.id > 25";
		// hql = "FROM Employee e WHERE e.id < 30 ORDER BY e.id DESC";
		// 有多个排序条件，先按前面的排
		// hql =
		// "FROM Employee e WHERE e.id < 30 ORDER BY e.id DESC,e.name ASC";
		// hql = "select e.name FROM Employee e";
		// hql = "select e FROM Employee e";//相当与"FROM Employee e"
		// hql = "select e.id,e.name FROM Employee e";//返回一个Object数组
		// hql = "select new Employee(e.id,e.name) FROM Employee e";
		// // 返回对象数组,要求类中有id、name两个参数的构造函数
		// // List list = session.createQuery(hql).list();
		Query query = session.createQuery("FROM Employee WHERE id=200");
		// // 分页
		query.setFirstResult(0);// 从第一条记录开始查询，与具体的id无关
		query.setMaxResults(5);
		Employee employee = (Employee) query.uniqueResult();
		System.out.println(employee);// 如果下标越界，查询的结果返回null
		// List list = query.list();

		// 方法链的形式
		// List list = session.createQuery(//
		// "select e.id,e.name FROM Employee e")//
		// .setFirstResult(0)//
		// .setMaxResults(10)//
		// .list();//

		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		//
		// }
		// ==========================================
		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testHQL2() throws Exception {
		Session session = sessionFactory.openSession();
		session.beginTransaction();
		// ===========================================
		String hql = null;

		// 聚集函数：count()、max()、min()、avg()、sum()
		// hql="SELECT COUNT(*) FROM Employee";
		// Long result=(Long)session.createQuery(hql).uniqueResult();
		// System.out.println(result);

		// hql = "SELECT MAX(id) FROM Employee";
		// Number result = (Number) session.createQuery(hql).uniqueResult();
		// System.out.println(result.getClass());// 返回结果类型
		// System.out.println(result.intValue());
		// System.out.println(result.longValue());

		// 分组group by、order by、having
		// hql =
		// "SELECT e.name,COUNT(e.id) FROM Employee e WHERE id <= 30 GROUP BY name";
		// 过滤，只显示数量大于2的组(不能用where代替，因为分组在查询之后)
		// hql="SELECT e.name,COUNT(e.id) FROM Employee e WHERE id <= 30 GROUP BY name HAVING COUNT(e.id) > 2";
		// hql = "SELECT e.name,COUNT(e.id) AS c " + //
		// "FROM Employee e " + //
		// "WHERE id <= 30 " + //
		// "GROUP BY e.name " + //
		// "HAVING COUNT(e.id) = 2 " + // having子句不能使用列别名
		// "ORDER BY c ASC";// order by 子句可以使用列别名
		// List list = session.createQuery(hql).list();
		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		// }

		// hql =
		// "SELECT e.id,e.name,d.name FROM Employee e JOIN e.department d";
		// hql =
		// "SELECT e.id,e.name,d.name FROM Employee e INNER JOIN e.department d";
		// 左外连接，OUTER关键字可以省略
		// hql =
		// "SELECT e.id,e.name,d.name FROM Employee e LEFT OUTER JOIN e.department d";
		// hql =
		// "SELECT e.id,e.name,d.name FROM Employee e RIGHT JOIN e.department d";
		// hql = "SELECT e.id,e.name,e.department.name FROM Employee e";
		// List list = session.createQuery(hql).list();
		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		// }

		// 使用参数查询
		// 方式一：使用?占位
		// hql = "FROM Employee WHERE id=?";
		// List list = session.createQuery(hql)//
		// .setParameter(0, 23)//
		// .list();

		// hql = "FROM Employee WHERE id BETWEEN ? AND ?";
		// List list = session.createQuery(hql)//
		// .setParameter(0, 22)// 设置参数，第一个参数的索引为0
		// .setParameter(1, 30)//
		// .list();
		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		// }
		// 方式二：使用变量名
		// hql = "FROM Employee e WHERE id BETWEEN :idMin AND :idMax";
		// List list = session.createQuery(hql)//
		// .setParameter("idMin", 22)//
		// .setParameter("idMax", 30)//
		// .list();
		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		// }
		// 当变量是集合时，可以使用IN，参数设置用ParameterList
		// hql = "FROM Employee WHERE id IN (:ids)";
		// List list = session.createQuery(hql)//
		// .setParameterList("ids", new Object[] { 23, 26, 27, 28, 30 })//
		// .list();

		// 使用命名查询
		// Query query = session.getNamedQuery("queryByIdRange");
		// query.setParameter(0, 25);
		// query.setParameter(1, 30);
		// List list = query.list();

		// Query query = session.getNamedQuery("queryByIdRange");
		// query.setParameter("idMin", 25);
		// query.setParameter("idMax", 30);
		// List list = query.list();

		/*
		 *  update与delete,不会通知Session缓存，如要获取数据库
		 *  更新后的数据可以用refresh
		 */
		// int result = session.createQuery(//
		// "UPDATE Employee e SET e.name=? WHERE id < 30")
		// .setParameter(0, "张小姐")//
		// .executeUpdate();
		int result = session.createQuery(//
				"DELETE Employee e WHERE id < ?")// delete子句里from可以省掉
				.setParameter(0, 30)//
				.executeUpdate();
		System.out.println("result=" + result);

		// for (Object obj : list) {
		// if (obj.getClass().isArray()) {
		// System.out.println(Arrays.toString((Object[]) obj));
		// } else {
		// System.out.println(obj);
		// }
		// }
		// ===========================================
		session.getTransaction().commit();
		session.close();
	}
}

部分截图

内连接

左外连接

右外连接

Hibernate(十一)HQL查询

原文：http://blog.csdn.net/lindonglian/article/details/46975391