Hibernate中使用HQL

1.HQL是什么?

HQL(Hibernate Query Language)是hibernate专门用于查询数据的语句,有别于SQL,HQL 更接近于面向对象的思维方式。 

2.为什么使用HQL?

使用HQL 可以避免使用JDBC 查询的一些弊端:

不需要再编写繁琐的SQL 语句,针对实体类及其属性进行查询;

查询结果是直接存放在List 中的对象,不需要再次封装;

独立于数据库,对不同的数据库根据Hibernate dialect 属性的配置自动生成不同的SQL 语句执行。

3.HQL的语法

[select/update/delete……] from Entity [where……] [group by……] [having……] [order by……].

【注意】:HQL语句中关键字不区分大小写,但是实体类和对象属性要区分大小写。

4.在JUnit测试类测试HQL

hibernate hql可以校验hive sql吗 hibernate的hql_Test

示例1:

public void hql1() {
        String hql = "select t from TeacherEntity t";
        //创建查询对象
        Query query = session.createQuery(hql);
        //执行查询
        List<Object> list = query.list();
        for (Object obj : list) {
            System.out.println(((TeacherEntity) obj).getTeacherName() + "/" + ((TeacherEntity) obj).getTeacherClass());
        }
    }

    @Test
    public void hql2() {
        String hql = "from TeacherEntity";
        List<TeacherEntity> list = session.createQuery(hql).list();
        for (TeacherEntity t : list) {
            System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
        }
    }

这两个测试的结果是相同的:

(1)在什么情况下,不用写select
当我们查询的是TeacherEntity的所有属性时,我们可以不写select。如:from TeacherEntity;当然也可以写,但格式要发生改变:select t from TeacherEntity (as) t ;括号中的as可写可不写;

(2)在什么情况下,写select
当我们查询的不再是Man的所有属性是,我们必须要写上select。如:我们查询TeacherEntity中的name和class属性时,我们必须这么写:select t.name,t.class from TeacherEntity t;也可以不用别名:select name,class from TeacherEntity;

(3)当查询结果是一个对象,可以用Object接收,也可以用该实体接收。

示例2:

@Test
    public void hql3() {
        String hql = "from TeacherEntity where teacherName=? ";
        List<TeacherEntity> list = session.createQuery(hql).setParameter(0, "陈老师").list();
        for (TeacherEntity t : list) {
            System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
        }
    }

    @Test
    public void hql33() {
        String hql = "from TeacherEntity where teacherName=:name ";
        List<TeacherEntity> list = session.createQuery(hql).setParameter("name", "陈老师").list();
        for (TeacherEntity t : list) {
            System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
        }
    }

HQL中使用占位符:

(1)占位符从下标0开始计算位置;

(2)命名参数的使用如上。

示例3:

@Test
    public void hql4() {
        String hql = "select teacherName,teacherClass from TeacherEntity where teacherClass=?";
        List<Object[]> list = session.createQuery(hql).setParameter(0, "Java1").list();
        for (Object[] objs : list) {
            System.out.println(objs[0].toString() + "/" + objs[1].toString());
        }
    }

    @Test
    public void hql5() {
        String hql = "select new com.project.hibernate.dto.TeacherDto(teacherName,teacherClass) from TeacherEntity where teacherClass=?";
        List<TeacherDto> list = session.createQuery(hql).setParameter(0, "Java1").list();
        for (TeacherDto dto : list) {
            System.out.println(dto.getUserName() + "/" + dto.getUserClass());
        }
    }

    @Test
    public void hql6() {
        String hql = "select new map(teacherName,teacherClass) from TeacherEntity where teacherClass=?";
        List<Map> list = session.createQuery(hql).setParameter(0, "Java1").list();
        for (Map map : list) {
            System.out.println(map.get("0") + "/" + map.get("1"));
        }
    }

当我们查询的对象属性超过一个,可以用对象数组来接收结果;

也可以用一个临时的数据传输对象来接收(dto),该dto中要有对应的构造方法;

还可以用Map接收,得到的结果map键值对中键分别是"0","1","2","3".....,值则是对应的属性。

示例4(模糊查询):

//模糊查询
    @Test
    public void hql8() {
        String hql = "select new map(teacherName,teacherClass) from TeacherEntity where teacherClass like ?";
        List<Map> list = session.createQuery(hql).setParameter(0, "%J%").list();
        for (Map map : list) {
            System.out.println(map.get("0") + "/" + map.get("1"));
        }
    }

示例5(关联查询):

hibernate hql可以校验hive sql吗 hibernate的hql_Test_02

//关联查询
    @Test
    public void hql9() {
        String hql = "select new map(s.studentName,t.teacherName,t.teacherClass) from StudentEntity s left join s.teacher t where t.teacherName=? ";
        List<Map> list = session.createQuery(hql).setParameter(0, "陈老师").list();
        for (Map map : list) {
            System.out.println(map.get("0") + "/" + map.get("1") + "/" + map.get("2"));
        }
    }

因为在实体类中已经映射了学生表外键和老师表主键,所以这里不用像SQL一样写“on s.f_t_id=t.t_id”。

示例6(分页查询):

//分页查询
    @Test
    public void hql10() {
        //当前页码
        int currPage = 4;
        //每页显示条数
        int pageSize = 5;
        String hql = "from TeacherEntity where teacherName like ?";
        List<TeacherEntity> list = session.createQuery(hql).setParameter(0, "%刘老师%").
                setFirstResult((currPage - 1) * pageSize).setMaxResults(pageSize).list();
        for (TeacherEntity t : list) {
            System.out.println(t.getTeacherName());
        }
    }

Query对象提供了简便的分页方法:

setFirstResult(int firstResult)方法 设置起始下标 (等同于limit(a,b)中的a);

setMaxResults(int maxResults)方法 设置最大返回的记录条数(等同于limit(a,b)中的b)。

示例7(结果返回一个对象):

//返回一个对象
    @Test
    public void hql11() {
        String hql = "from TeacherEntity where teacherName=?";
        TeacherEntity t = (TeacherEntity) session.createQuery(hql).setParameter(0, "陈老师").uniqueResult();
        System.out.println(t.getTeacherName() + "/" + t.getTeacherClass());
    }

当确定返回的实例只有一个或者null时 用uniqueResult()方法。

示例7(删除和修改对象):

//删除对象
    @Test
    public void hql12() {
        String hql = "delete from TeacherEntity where teacherName=?";
        int i = session.createQuery(hql).setParameter(0, "刘老师19").executeUpdate();
        System.out.println(i);
    }

    //修改对象
    @Test
    public void hql13() {
        String hql = "update TeacherEntity set teacherName=? where teacherId=?";
        int i = session.createQuery(hql).setParameter(0, "测试修改").setParameter(1, "40288ae670f5d8a30170f5d8a5e90011").executeUpdate();
        System.out.println(i);
    }

Hibernate中使用SQL

@Test
    public void sql() {
        String sql = "select * from sys_teacher";
        NativeQuery query = session.createNativeQuery(sql);
        //把查询出来的数据放入实体中,该实体需要被hibernate管理
        query.addEntity(TeacherEntity.class);
        List<TeacherEntity> list = query.list();
        for (TeacherEntity t : list) {
            System.out.println(t.getTeacherName());
        }
    }

    @Test
    public void sql2() {
        String sql = "select t_name,t_class from sys_teacher";
        List<Object[]> list = session.createNativeQuery(sql).list();
        for (Object[] objs : list) {
            System.out.println(objs[0].toString() + "/" + objs[1].toString());
        }
    }