本人想通过该篇文章介绍下如何使用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>
<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>
<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>
<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>
<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());
}
相关推荐
Hibernate hql查询语法总结 Hibernate hql查询语法总结
Hibernate HQL基础练习小列子+数据库
Hibernate HQL.txt Hibernate HQL.txt Hibernate HQL.txt
Hibernate中的关联查询实际上生成的是数据库表连接查询的SQL语句
Hibernate HQL查询 分页查询 模糊查询.docxHibernate HQL查询 分页查询 模糊查询.docxHibernate HQL查询 分页查询 模糊查询.docxHibernate HQL查询 分页查询 模糊查询.docxHibernate HQL查询 分页查询 模糊查询....
Hibernate HQL 深度历险 深入讲解Hibernate HQL 查询
Hibernate HQL.docHibernate HQL.docHibernate HQL.doc
hibernate hql基本操作
Hibernate HQL命名参数和Query接口的分页 Hibernate HQL命名参数和Query接口的分页
对hibernate的hql进行了详尽的讲解
hibernate hql各类查询范例
Hibernate HQL 插入，查询，更新
Hibernate HQL查询语句总结.docx 网络下载的
Hibernate HQL 是一个重要的东西 好用 易学 大家都知道 你还没有用吗
HibernateHQL级联注意事项[归类].pdf
一个关于Hibernate-HQL 的文档 内容实际而且实用
hibernate HQL详细教程，简单易学易懂 1.1 HQL基础 2 1.1.1 默认数据库表和数据 2 1.1.2 检索类的所有对象 4 1.1.3 检索类的某几个属性 5 1.1.4 指定别名 5 1.1.5 where条件子句 5 1.1.6 使用distinct过滤掉重复值 6...
hibernate hql语句 hql语言汇总
hibernate HQL 查询教程 管理 Session ThreadLocal 接口的使用
精通HQL.doc精通HQL.doc精通HQL.doc精通HQL.doc精通HQL.doc