Hibernate HQL

最新推荐文章于 2025-12-19 15:39:34 发布

原创最新推荐文章于 2025-12-19 15:39:34 发布 · 160 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

Hibernate+Ibatis 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了Hibernate HQL查询语言的基本用法，包括单表查询、多表关联查询、子查询及动态查询等，并提供了丰富的示例代码。

本人想通过该篇文章介绍下如何使用Hibernate HQL语句，基本上SQL语句的查询都能在HQL中找到对

应的实现。

创建了几个表，这几个表是用于HQL演示用。t_user是一个单独的表，没有与其他表进行关联。

company和employee这两个表有一个一对多的关联关系

user和role是一个多对多的关联关系，中间表为user_role

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

CREATE TABLE `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK4AFD4ACE19AF9E2B` (`company_id`),
  CONSTRAINT `FK4AFD4ACE19AF9E2B` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

CREATE TABLE `user_role` (
  `userId` int(11) DEFAULT NULL,
  `roleId` int(11) DEFAULT NULL,
  KEY `FK143BF46AF9C1C096` (`roleId`),
  KEY `FK143BF46AFF171600` (`userId`),
  CONSTRAINT `FK143BF46AF9C1C096` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`),
  CONSTRAINT `FK143BF46AFF171600` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对应的Hibernate映射文件

Person.hbm.xml

<hibernate-mapping package="com.guchao.hibernate.entity">
	<class name="Person" table="t_user">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name" column="username" type="string"></property>
		<property name="age" column="age" type="integer"></property>
		<property name="birthday" column="birthday" type="date"></property>
	</class>

</hibernate-mapping>

Company.hbm.xml

<hibernate-mapping package="com.guchao.hibernate.entity">
	<class name="Company">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name"></property>
	</class>

</hibernate-mapping>

Employee.hbm.xml

<hibernate-mapping package="com.guchao.hibernate.entity">
	<class name="Employee">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name"></property>
		<many-to-one name="company" column="company_id"></many-to-one>
	</class>

</hibernate-mapping>

User.hbm.xml

<hibernate-mapping package="com.guchao.hibernate.entity">
	<class name="User" table="user">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name"></property>
		<set name="roles" table="user_role">
			<key column="userId"></key>
			<many-to-many class="Role" column="roleId"></many-to-many>
		</set>
	</class>

</hibernate-mapping>

Role.hbm.xml

<hibernate-mapping package="com.guchao.hibernate.entity">
	<class name="Role" table="role">
		<id name="id">
			<generator class="native"></generator>
		</id>
		<property name="name"></property>
	</class>

</hibernate-mapping>

单表HQL

排序order by fieldName desc/asc

Query query = session.createQuery("from Person p order by p.name asc");
		List<Person> list = query.list();

总数count(*)

Query query = session.createQuery("select count(*) from Person p");
		long count = (Long)query.uniqueResult();

范围 between ... and ... > <

Query query = session.createQuery("from Person p where p.id >= ? and p.id <= ?");
		query.setParameter(0, 2).setParameter(1, 5);
		List<Person> list = query.list();

Query query = session.createQuery("from Person p where p.id between ? and ?");
		query.setParameter(0, 2).setParameter(1, 5);
		List<Person> list = query.list();

in，not in

Query query = session.createQuery("from Person p where p.id not in(2,3,5,6)");
		List<Person> list = query.list();

max min avg sum

Query query = session.createQuery("select max(p.age), min(p.age), avg(p.age), sum(p.age) from Person p");
		Object[] o = (Object[])query.uniqueResult();

分组group by having..

Query query = session.createQuery("select p.age,count(*) from Person p group by p.age having count(*) >= 1");
		for(Object o : query.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "|" + arr[1]);
		}

null, not null

Query query = session.createQuery("from Person p where p.birthday is null");
		List<Person> list = query.list();

empty

Query query = session.createQuery("from Person p where p.birthday is empty");
		List<Person> list = query.list();

like %代表0个或多个，_代表1个

Query query = session.createQuery("from Person p where p.name like 'guchao%'");
		List<Person> list = query.list();

Query query = session.createQuery("from Person p where p.name like 'guchao_'");
		List<Person> list = query.list();

算术函数绝对值abs（绝对值），sqrt（平方根），mod（取余数）

Query q = session.createQuery("select abs(p.id)," +
				 "sqrt(p.id)," +
				 "mod(p.id, 2)" +
				 " from Person p ");
		for(Object o : q.list()){
			Object[] arr = (Object[])o;
			System.out.println(arr[0]+","+arr[1]+","+arr[2]);
		}

函数lower，upper，trim，cancat，length

Query q = session.createQuery("select lower(p.name)," +
									 "upper(p.name)," +
									 "trim(p.name)," +
									 "concat(p.name, '***')," +
									 "length(p.name)" +
									 " from Person p");

		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + "-" + arr[1] + "-" + arr[2] + "-" + arr[3] + "-" + arr[4]);
		}

此外HQL中还集成了这几个对象current_date, current_time, current_timestamp

Query q = session.createQuery("select current_date, current_time, current_timestamp, p.id from Person p");
		
		for(Object o : q.list()) {
			Object[] arr = (Object[])o;
			System.out.println(arr[0] + " | " + arr[1] + " | " + arr[2] + " | " + arr[3]);
		}

多表HQL

关联查询

//		Query q = session.createQuery("select e.name,e.company.name from Employee e");
//		Query q = session.createQuery("select e.name,c.name from Employee e join e.company c");
		Query q = session.createQuery("select e.name,c.name from Employee e join e.company c where c.name = 'lenovo' and e.name = 'liuhaibao'");
		for(Object o :q.list()){
			Object[] arr = (Object[])o;
			System.out.println(arr[0]+","+arr[1]);
		}

子查询

Query q = session.createQuery("from Person p where p.id < (select avg(p.id) from Person p)");
		List<Person> list = q.list();
		for(Person p : list){
			System.out.println(p.getId()+","+p.getName());
		}

VO（Value Object），查询结果来自多个表，把查询的信息封装成一个VO

VO类

package com.guchao.hibernate.entity;

public class CompanyEmployeeVO {

	private String companyName;
	
	private String employeeName;

	public CompanyEmployeeVO() {}
	public CompanyEmployeeVO(String companyName, String employeeName) {
		super();
		this.companyName = companyName;
		this.employeeName = employeeName;
	}

	public String getCompanyName() {
		return companyName;
	}

	public void setCompanyName(String companyName) {
		this.companyName = companyName;
	}

	public String getEmployeeName() {
		return employeeName;
	}

	public void setEmployeeName(String employeeName) {
		this.employeeName = employeeName;
	}
}

Query q = session.createQuery("select new com.guchao.hibernate.entity.CompanyEmployeeVO(e.company.name,e.name) from Employee e");
		List<CompanyEmployeeVO> list = q.list();
		for(CompanyEmployeeVO vo :list){
			System.out.println("company name="+vo.getCompanyName()+",employee name="+vo.getEmployeeName());
		}

多条件动态单表查询

由多个查询条件组成的复杂查询，有些查询条件会使用，有些查询条件不会使用。JDBC中使用SQL拼接的方式，Hibernate也是类似的方式。

DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Person.class); 
		String sql=" 1= 1";
		String username = "";
		String age = "28";
		if(username != null && !"".equals(username.trim())){
			sql += " and username = '"+username +"'";
		}
		if(age != null && !"".equals(age.trim())){
			sql += " and age = "+age;
		}
		detachedCriteria.add(Restrictions.sqlRestriction(sql));     
	      
		Criteria criteria = detachedCriteria.getExecutableCriteria(session);  
		List<Person> list = criteria.list();
		for(Person p : list){
			System.out.println(p.getId()+","+p.getName());
		}