介绍hibernate常用查询方法
用到的表
CREATE TABLE `department` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
CREATE TABLE `employee` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
`department_id` int(11) DEFAULT NULL,
`country_id` int(11) NOT NULL,
`phone` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK75C8D6AED43F9198` (`department_id`),
CONSTRAINT `FK75C8D6AED43F9198` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 查询department所有内容
String sql = "from Department t";
Query q = session.createQuery(sql);
List<Department> departments = q.list();
hibernate可以将查询结果自动映射到pojo中
- 占位符的使用
String sql = "from Department t where t.name=:name";
Query qq = session.createQuery(sql);
List<Department> departments = qq.setParameter("name", "技术部").list();
或者
String sql = "from Department t where t.name=?";
Query qq = session.createQuery(sql);
List<Department> departments = qq.setParameter(0, "技术部").list();
使用主键获取实体
hibernate可以使用session.load()或者session.get()方法获取主键对应的实体.
两者的区别是:使用load如果没有实体,抛出异常,使用get没有实体返回nullhql返回数组
String sql = "select t,(select count(*) from t.employees es where es.departmentId=t.id and es.salary>10) as cc from Department t group by t";
Query qq = session.createQuery(sql);
List<Object[]> list = qq.list();
- hql返回map
String sql = "select new Map(t,(select count(*) from t.employees es where es.departmentId=t.id and es.salary>=10) as cc) from Department t group by t";
List<Map<String, Object>> map = session.createQuery(sql).list();
对于有别名的列直接使用别名获取
map.get("cc")
无别名的列则使用所在的位置获取
map.get("0")
