HQL查询详解-优快云博客

本文链接：https://blog.youkuaiyun.com/jinyefei/article/details/78963002

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=?)