hibernate框架三
八.HQL查询
什么是HQL
HQL全称 hibernate query language,即hibernate的面向对像方式的查询语言
使用HQL的步骤
- 获取Session对像
- 编写HQL语句
- 创建Query接口,发送HQL语句
- 返回结果
基本查询
3.1查询所学生信息
public List<Student> selectAll(){
//获取session
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
List<Student> list = null;
try {
//编写hql语句
//Student类名,不是表名
String hql="from Student";
//创建Query接口
Query query = session.createQuery(hql);
//得到结果对像,如果是多条记录,请使用List()方法或Iterator()方法
list = query.list();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return list;
}
得到查询结果:集合结构中封装多个学生对像
[model.Student@64702525, model.Student@3449440d]
注意:HQL是面向对像的查询语句,其中查询条件是类的属性名,而不是表的字段
同样,from后是要查询的类名,而不是表名
3.2 绑定参数的查询
按条件查询,如查询姓名叫jack学生
public Student selectByName(String name){
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
Student stu= null;
try {
//拼接字符串的方式麻烦且安全性较差
//String hql="from Student where name = '"+name+"'";
//建议使用参数绑定的方式
String hql = "from Student where name = ?";
Query query = session.createQuery(hql);
//给占位符绑定参数,Hql面对像的,下标从0开始
query.setString(0, name);
//如果确定结果是一条记录,可以使用uniqueResult()方法
stu = (Student) query.uniqueResult();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return stu;
}
以上是按点位符的位置绑定参数,也可以按名称绑定参数,如下
public Student selectByName(String name){
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
Student stu= null;
try {
//命名方式的点位符
String hql = "from Student where name = :myname";
Query query = session.createQuery(hql);
//给占位符myname绑定参数
query.setString("myname", name);
stu = (Student) query.uniqueResult();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return stu;
}
得到的查询结果,是单一的对像
model.Student@78fa83fa
3.3 隐式内连接查询
如按该sql查询 select * from STUDENT s ,CLASS c where s.CID=c.ID and c.NAME=?
查询某个班级中的学生信息
//类中的关联关系表达
public class Student implements Serializable{
private Integer id;
private String name;
private String password;
private Integer age;
private Integer sex;
/*
*使用HQL进行连接查询,对像间关联关系的配置
* 多对一关系
*/
private Clazz clazz;
}
//mapper文件中关联关系表达
<hibernate-mapping package="model" default-lazy="false">
<class name="Student" table="STUDENT">
<id name="id" column="ID">
<generator class="increment"></generator>
</id>
<property name="name" column="NAME"></property>
<property name="password" column="PASSWORD"></property>
<property name="age" column="AGE"></property>
<property name="sex" column="SEX"></property>
<!--多对一关系-->
<many-to-one name="clazz" class="Clazz" column="CID"></many-to-one>
</class>
</hibernate-mapping>
查询方式如下
public List<Student> selectByJoin(String clazzName){
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
List<Student> list= null;
try {
//隐式内连接
//注意:其中的clazz是Student类中的Pojo属性
String hql = "from Student s where s.clazz.name= ?";
Query query = session.createQuery(hql);
query.setString(0, clazzName);
list = query.list();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return list;
}
隐式内联接发送的sql语句
并没有使用Join关键字
Hibernate:
select
student0_.ID as ID0_,
student0_.NAME as NAME0_,
student0_.PASSWORD as PASSWORD0_,
student0_.AGE as AGE0_,
student0_.SEX as SEX0_,
student0_.CID as CID0_
from
STUDENT student0_,
CLASS clazz1_
where
student0_.CID=clazz1_.ID
and clazz1_.NAME=?
查询结果如下,得到是学生对像集合
[model.Student@4fff31e1, model.Student@20a12d8f]
3.4 显式内联接
指显示使用Join关键字
类的关联关系和mapper中的关联关系同上
查询方式如下:
public List<Object> selectByJoin2(String clazzName){
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
List<Object> list= null;
try {
//显式内连接查询
//注意:这里s.clazz是Student类中的属性clazz
String hql = "from Student s inner join s.clazz where s.clazz.name= ?";
Query query = session.createQuery(hql);
query.setString(0, clazzName);
list = query.list();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return list;
}
发送的sql如下,这里显式使用了Join
select
student0_.ID as ID0_0_,
clazz1_.ID as ID1_1_,
student0_.NAME as NAME0_0_,
student0_.PASSWORD as PASSWORD0_0_,
student0_.AGE as AGE0_0_,
student0_.SEX as SEX0_0_,
student0_.CID as CID0_0_,
clazz1_.NAME as NAME1_1_
from
STUDENT student0_
inner join
CLASS clazz1_
on student0_.CID=clazz1_.ID
where
clazz1_.NAME=?
得到结果如下:
[[Ljava.lang.Object;@21d9f7bc, [Ljava.lang.Object;@6d176900]
3.5左外连接查询
查询方式如下:
public List<Object> selectByJoin4(String clazzName){
Session session = DBUtil.findSession();
Transaction tx = session.beginTransaction();
List<Object> list= null;
try {
//使用Left join查询
String hql = "from Student s left join s.clazz with s.clazz.name= ?";
Query query = session.createQuery(hql);
query.setString(0, clazzName);
list = query.list();
tx.commit();
} catch (Exception e) {
e.printStackTrace();
tx.rollback();
}
return list;
}
控制台打印Sql语句如下
select
student0_.ID as ID0_0_,
clazz1_.ID as ID1_1_,
student0_.NAME as NAME0_0_,
student0_.PASSWORD as PASSWORD0_0_,
student0_.AGE as AGE0_0_,
student0_.SEX as SEX0_0_,
student0_.CID as CID0_0_,
clazz1_.NAME as NAME1_1_
from
STUDENT student0_
left outer join
CLASS clazz1_
on student0_.CID=clazz1_.ID
and (clazz1_.NAME=?)