hibernate基础18：HQL

HQL：hibernate query language查询语言，是一种完全面向对象的查询语言。HQL与SQL几乎相同，关键字(不区分大小写)一样，HQL查询的是类名不是表名，输出字段写属性名而不是表中字段名

1、查询所有

@Test
    public void testSelect(){
        String hql = "from User";
        List<User> list =  session.createQuery(hql).list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

2、条件查询

@Test
    public void testSelect(){
        String hql = "from User where age > 25";
        List<User> list =  session.createQuery(hql).list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

@Test
    public void testSelect(){
        String hql = "from User where age > ?";
        List<User> list =  session.createQuery(hql)
                .setInteger(0, 25)
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

@Test
    public void testSelect(){
        String hql = "from User where age > :age";
        List<User> list =  session.createQuery(hql)
                .setInteger("age", 25) 
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

3、查询指定列

3.1、使用object数组接收，输出时根据字段顺序，下标从0开始

@Test
    public void testSelect(){
        String hql = "select name,age from User";
        List<Object[]> list =  session.createQuery(hql)
                .list();
        for (Object[] objs : list){
            System.out.println(objs[0]+"	"+objs[1]);
        }
    }

3.2、实体类添加构造方法，在HQL输出字段时创建对象，即可用实体类接收

//实体类添加构造方法
    public User(String name, int age) {
        super();
        this.name = name;
        this.age = age;
    }

@Test
    public void testSelect(){
        String hql = "select new User(u.name,u.age) from User u";
        List<User> list =  session.createQuery(hql)
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

4、LIKE关键字使用

    @Test
    public void testSelect(){
        String hql = "from User u where name like ?";
        List<User> list =  session.createQuery(hql)
                .setString(0, "张%")
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

5、输出唯一结果使用：uniqueResult，一般多用于登录，返回结果只有一条或NULL，当查询出多条使用此关键字报错

@Test
    public void testSelect(){
        String hql = "from User u where name like ?";
        User u =  (User) session.createQuery(hql)
                .setString(0, "张三")
                .uniqueResult();
        System.out.println(u);
    }

6、聚合函数查询

6.1、统计表记录个数

@Test
    public void testSelect(){
        String hql = "select count(1) from User";
        Long count =  (Long) session.createQuery(hql)
                .uniqueResult();
        System.out.println(count);
    }

7、HQL分页

@Test
    public void testSelect(){
        String hql = "from User";
        List<User> list =  session.createQuery(hql)
                //(currentPage-1)*pageSize (当前页-1)*每页显示条数
                .setFirstResult(0)
                //pageSize 每页显示条数
                .setMaxResults(2)
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

8、HQL排序

@Test
    public void testSelect(){
        //asc 升序，desc 降序
        String hql = "from User order by age asc";
        List<User> list =  session.createQuery(hql)
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

9、IN关键字

    @Test
    public void testSelect(){
        String hql = "from User where id in(:ids)";
        Object[] ids = {2,3};
        List<User> list =  session.createQuery(hql)
                .setParameterList("ids", ids)
                .list();
        for (User u : list){
            System.out.println(u.getName()+"	"+u.getAge());
        }
    }

10、关联查询

@Test
    public void testSelect1(){
        String hql = "from Student s where s.classes.name = ?";
        List<Student> list =  session.createQuery(hql)
                .setString(0, "一年级")
                .list();
        for (Student stu : list){
            System.out.println(stu.getName()+"	"+stu.getAge());
        }
    }