单表查询:
Hibernate是DAO层技术,对数据的使用,查询是最为重要的。Hibernate的查询技术非常强大,支持原始SQL语句查询,支持QBC查询及Hibernate特有的HQL查询。
HQL,Hibernate Query Language,Hibernate查询语言,它与SQL非常相似。但,HQL是面向对象的查询语言,而SQL是面向二维表的。HQL查询语句中使用的是类名与属性名,而SQL语句使用的是表名与字段名。
QBC,Query By Criteria,标准查询,一种比HQL更为面向对象的查询方法。
对应API:
(1) Query接口:
Hibernate进行HQL查询的接口,支持动态绑定参数的功能。使用Session对象的createQuery方法可获取Query对象。
Query query = session.createQuery(hql);
(2)SQLQuery接口:
Hibernate进行SQL原生查询的接口,支持动态绑定参数的功能,是Query接口的子接口。使用Session对象的createSQLQuery()方法可获取SQLQuery对象。
SQLQuery sqlQuery = session.createSQLQuery(sql);
其查询出的结果对象默认为Object,当然,若结果为List,则其元素为Object。使用SQLQuery的addEntity(Xxx.class)方法,可以将其结果泛型设定为指定类型。
(3) Criteria接口:
Criteria,标准、准则,Hibernate进行Criteria查询的接口,与Query接口无关。使用Session对象的createCriteria()方法可获取Criteria对象。
Criteria criteria = session.createCriteria(Xxx.class);
具体查询代码:
(1) 查询所有:
//查询所有
@Test
public void test01_SQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String sql = "select tid,tname,tage,tscore from t_student";
List<Student> list = session.createSQLQuery(sql).addEntity(Student.class).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test01_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
//sql中出现的是表名与字段名,hql中出现的是类名与属性名
String hql = "from Student";
List<Student> list = session.createQuery(hql).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test01_QBC() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
List<Student> list = session.createCriteria(Student.class).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(2)对查询结果排序:
//查询结果查询
@Test
public void test02_SQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String sql = "select tid,tname,tage,tscore from t_student order by tage desc";
List<Student> list = session.createSQLQuery(sql).addEntity(Student.class).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test02_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student order by age desc";
List<Student> list = session.createQuery(hql).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test02_QBC() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
List<Student> list = session.createCriteria(Student.class).addOrder(Order.desc("age")).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(3) 动态参数绑定查询:
//动态参数绑定查询
@Test
public void test03_1() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where age > ? and score < ?";
List<Student> list = session.createQuery(hql)
.setInteger(0, 20)
.setDouble(1, 94)
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test03_2() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where age > :myage and score < :myscore";
List<Student> list = session.createQuery(hql)
.setInteger("myage", 20)
.setDouble("myscore", 94)
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test03_3() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where age > ? and score < ?";
List<Student> list = session.createSQLQuery(hql)
.setParameter(0, 20)
.setParameter(1, 94.0) //这里必须是94.0
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test03_4() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where age > :myage and score < :myscore";
List<Student> list = session.createQuery(hql)
.setParameter("myage", 20)
.setParameter("myscore", 94.0) //这里必须是94.0
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(4) 分页查询:
//分页查询
@Test
public void test04_SQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String sql = "select * from t_student limit ?,?";
List<Student> list = session.createSQLQuery(sql)
.addEntity(Student.class)
.setInteger(0, 4)
.setInteger(1, 3)
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
} @Test
public void test04_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student";
List<Student> list = session.createQuery(hql)
.setFirstResult(4)
.setMaxResults(3)
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(5) 模糊查询:
//模糊查询
@Test
public void test05_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where name like :myname";
List<Student> list = session.createQuery(hql)
.setString("myname", "%n%")
.list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(6) 唯一性查询:
//唯一性查询
@Test
public void test06_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student where id=:myid";
Student student = (Student) session.createQuery(hql)
.setInteger("myid", 3)
.uniqueResult();
System.out.println(student);
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(7) 聚合函数查询:
//聚合函数查询
@Test
public void test07_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "select count(id) from Student";
Long count = (Long) session.createQuery(hql).uniqueResult();
System.out.println(count);
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(8) 投影查询:
//投影查询
@Test
public void test08_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "select new Student(name, age) from Student";
List<Student> list = session.createQuery(hql).list();
for (Student student : list) {
System.out.println(student);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(9) 分组查询:
//分组查询
@Test
public void test09_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
String hql = "from Student group by age";
List<Integer> list = session.createQuery(hql).list(); //每个年龄段的第一个人
System.out.println(list); String hql2 = "select age from Student group by age";
List<Integer> list2 = session.createQuery(hql2).list(); //共有几个年龄段
System.out.println(list2); String hql3 = "select age from Student group by age having count(age) > 1";
List<Integer> list3 = session.createQuery(hql3).list(); //人数多于1人的年龄段
System.out.println(list3);
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
(10) iterator和N+1问题的解决:
@Test
public void test10_3() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
//第一次查询
String hql = "from Student";
List<Student> list = session.createQuery(hql).list();
for (Student student : list) {
System.out.println(student);
}
//第二次查询
String hql2 = "from Student";
Iterator<Student> it2 = session.createQuery(hql2).iterate();
while (it2.hasNext()) {
Student student2 = it2.next();
System.out.println(student2);
}
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
解释:list()方法对所有Student表的每条数据进行一次详情查询,但是第二次再用list()查询时不会从Session缓存中读取数据;
而iterator()方法首先对Student表的主键进行查询(对主键的查询比详情效率高,因为数据库中存在索引),再根据主键依次对Student表中的每条数据进行逐个查询,总共查询了N+1次,但是第二次再用iterator()查询时,只会做主键查询,然后根据主键从Session缓存中读取数据,而不会进行详情查询。
所以,如果要进行多次查询时,第一次使用list()查询,之后每次都使用iterator()查询。
(11) 命名查询(好处:可以在配置文件中修改查询语句):
//命名查询
@Test
public void test11_HQL() {
//1. 获取Session
Session session = HbnUtils.getSession();
try {
//2. 开启事务
session.beginTransaction();
//3. 操作
Student student = (Student) session.getNamedQuery("selectById")
.setInteger("myid", 3)
.uniqueResult();
System.out.println(student);
//4. 事务提交
session.getTransaction().commit();
} catch (Exception e) {
e.printStackTrace();
//5. 事务回滚
session.getTransaction().rollback();
}
}
对应映射文件为:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> <hibernate-mapping>
<!-- 类到表的映射,属性到字段的映射 -->
<class name="com.tongji.beans.Student" table="t_student">
<id name="id" column="tid">
<generator class="native"/>
</id>
<property name="name" column="tname"/>
<property name="age" column="tage"/>
<property name="score" column="tscore"/>
</class>
<query name="selectById">from Student where id=:myid</query>
</hibernate-mapping>