本人想通过该篇文章介绍下如何使用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());
}