HQL语句详解

最新推荐文章于 2020-09-09 10:10:27 发布

极乐码农

最新推荐文章于 2020-09-09 10:10:27 发布

阅读量906

点赞数

文章标签： mysql java hibernate

本文链接：https://blog.csdn.net/weixin_43216437/article/details/104427399

版权

1、创建表

    @Test
    public void testAdd() {
        Session session = HibernateUtils.getSession();
        Transaction ts = session.beginTransaction();
        try {
            for (int i = 0; i <1000; i++) {
                User user = new User();
                if (i % 2 == 0) {
                    user.setUserName("周志强");
                    user.setGender(1);
                    user.setBirthday(new Date(1997-1990,06-1,07));
                    user.setAddress("天水");
                } else {
                    user.setUserName("张豆豆");
                    user.setGender(0);
                    user.setBirthday(new Date(2002-1990,06-1,03));
                    user.setAddress("重庆");
                }
                if (i%10 ==0) {
                    session.flush();
                }
                session.flush();
                session.save(user);
            }
            ts.commit();
        } catch (Exception e) {
            e.printStackTrace();
            ts.rollback();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2、hql语句

2.1hql基本查询

hql语句中所以表名和字段名名
类名属性名

2.1.1(查询表中所有属性) String hql = “from User”;

    @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "from User";
            Query query = session.createQuery(hql);
            List<User> userList = query.list();
            for (User user:userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.2(查询表中单个属性)String hql = “select u.userName from User u”;

@Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u.userName from User u";
            Query query = session.createQuery(hql);
            List<String> nameList = query.list();
            for (String name:nameList) {
                System.out.println(name);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.3(查询表中多个属性) String hql = “select u.userName,u.gender from User u”;

    @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u.userName,u.gender from User u";
            Query query = session.createQuery(hql);
            List<Object[]> arrayList = query.list();
            for (Object[] array :arrayList) {
                System.out.println("姓名:"+array[0]+"：性别:"+array[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.4(hql分页查询重点)

//设置开始行号
//从前台传递过来的是页码的pageNo 公式(pageNo-1)*pageSize
query.setFirstResult(0);
//设置每页的数量（pageSize）
query.setMaxResults(10);

翻页计算公式(每页的开始行号)： (pageNo-1)*pageSize
sql语句：select *from 表 limit (pageNo-1)*pageSize ,pageSize
该sql语句第一个参数开始行号，第二个参数每页的记录数

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "from User";
            Query query = session.createQuery(hql);
            //设置开始行号
            //从前台传递过来的是页码的pageNo 公式(pageNo-1)*pageSize
            query.setFirstResult(0);
            //设置每页的数量（pageSize）
            query.setMaxResults(10);
            List<User> userList = query.list();
            for (User user:userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.5(限定查询：有条件的查询) String hql = “select u.userName from User u where u.gender=?”;

//设置第一个缺少的参数和jdbc不同预编译索引是从0开始的不是从1开始的切记切记

    @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u.userName from User u where u.gender=?";
            Query query = session.createQuery(hql);
            //设置第一个缺少的参数 和jdbc不同  参数是从0开始的不是从1开始的
            query.setParameter(0, 0);
            List<String> userList = query.list();
            for (String user:userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.6(统计查询)String hql = “select count(u.userId) from User u”;

Object o = query.uniqueResult();
String hql = “select max(u.userId) from User u”;
String hql = “select min(u.userId) from User u”;
String hql = “select sum(u.userId) from User u”;
String hql = “select avg(u.userId) from User u”;

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select count(u.userId) from User u";
            Query query = session.createQuery(hql);
            Object o = query.uniqueResult();
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.7（分组统计查询）

String hql = “select avg(u.salary),u.gender from User u group by u.gender having avg(u.salary)>6000”;
having：条件筛选
sql语句一定写对程序出错75% 可能是sql语句写错先检查sql语句。

   @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select avg(u.salary),u.gender  from User u group by u.gender having avg(u.salary)>6000";
            Query query = session.createQuery(hql);
            List<Object[]> arrayList =query.list();
            for (Object[] array:arrayList) {
                System.out.println("性别" + array[1] + "：平均薪水：" + array[0]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

             运行结果：
             性别0:：平均薪水：5500.0
             性别1:平均薪水：9990.0

2.1.8(投影查询)

先创建一个接收查询结果的bean(必须给定它的有参构造器才能用它来接收sql执行的结果

package model;

public class EmpBean {
    private double avgSalary;
    private Integer gender;

    public EmpBean(double avgSalary, Integer gender) {
        this.avgSalary = avgSalary;
        this.gender = gender;
    }

    public double getAvgSalary() {
        return avgSalary;
    }

    public void setAvgSalary(Integer avgSalary) {
        this.avgSalary = avgSalary;
    }

    @Override
    public String toString() {
        return "EmpBean{" +
                "avgSalary=" + avgSalary +
                ", gender=" + gender +
                '}';
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }
}

//我们可以创建一个业务bean，在bean中提供有参数的构造器来返回sql执行的值。
创建这个对象的语法在select 后直接new 这个对象，
也就是创建有参构造器对象区别是里面的实参是填入sql执行后的值。
顺序必须和构造器里的顺序一致，不然会报错。
查询后的返回结果就是这个对象，相当于用sql语句new出了这个对象。

  @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //我们可以创建一个业务bean，在bean中提供有参数的构造器来返回sql执行的值。创建这个对象的语法在select 后直接new 这个对象
           String hql="select new model.EmpBean(avg(u.salary),u.gender) from User u group by gender";
            Query query = session.createQuery(hql);
            List<EmpBean> empBeans =query.list();
            for (EmpBean bean:empBeans) {
                System.out.println("性别" + bean.getGender() + "：平均薪水：" + bean.getAvgSalary());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }
}

使用投影查询的好处是避免使用数组进行接收，数据更加集中。遍历也更加方便。一般都用这种而不用普通的分组查询。

2.1.9(排序查询)

       String hql = "select u from User u order by u.salary asc"; 按照工资升序排列
       asc:升序
       desc:降序

@Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u from User u order by u.salary asc";
            Query query = session.createQuery(hql);
            List<User> userList = query.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.1.9(模糊查询重点)

  String hql = "select u from User u where u.userName like  '%张%'"
  模糊语句用  ’‘ 括在里面

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u from User u where u.userName like  '%张%'";
            Query query = session.createQuery(hql);
            List<User> userList = query.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

            String hql = "select u from User u where u.userName like  '%志_'";     查询倒数第二个是志的数据

  @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String hql = "select u from User u where u.userName like  '%志_'";
            Query query = session.createQuery(hql);
            List<User> userList = query.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

2.10 在配置文件中写sql语句（推荐使用）

        名字是固定的  参数设置最好使用  :名字  这样的格式  尽量不要使用 问号   ？ query写在class下面  不要写在class里面

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="model">
    <class name="model.User" table="t_user">
        <id name="userId" column="user_id">
            <generator class="native"/>
        </id>
        <property name="userName" column="user_name"/>
        <property name="gender"/>
        <property name="birthday"/>
        <property name="address"/>
        <property name="salary"/>
    </class>
    <query name="getUserAll">
        <![CDATA[
                  from User u where u.salary>:salary
        ]]>
    </query>
    
</hibernate-mapping>

测试类

      Query query = session.getNamedQuery("getUserAll"); //获得sql语句的
        query.setParameter("salary", 5000);//设置参数
        List<User> userList = query.list();//执行  
        都用的同一个query

@Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Query query = session.getNamedQuery("getUserAll");
            query.setParameter("salary", 5000);
            List<User> userList = query.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3、QBC查询

3.1(QBC基本查询)

   //创建qbc查询接口的实现类
        Criteria criteria = session.createCriteria(User.class);
        //查询User类对象的列表
        List<User> userList  = criteria.list();

3.1.1 基本查询(查询全量数据)

@Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //创建qbc查询接口的实现类
            Criteria criteria = session.createCriteria(User.class);
            //查询User类对象的列表
            List<User> userList  = criteria.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.1.2 基本查询(查询单个属性)

     //创建qbc查询接口的实现类
        Criteria c = session.createCriteria(User.class);
        //设置要具体查询的列
        c.setProjection(Projections.property("userName"));
        List<String> nameList  = c.list();
        也可以分步骤写
        Criteria c = session.createCriteria(User.class);
        PropertyProjection userName = Projections.property("userName");
        c.setProjection(userName);
        List<String> nameList  = c.list();
        不过建议连在一起写

@Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //创建qbc查询接口的实现类
            Criteria c = session.createCriteria(User.class);
            //设置要具体查询的列
            c.setProjection(Projections.property("userName"));
            List<String> nameList  = c.list();
            for (String name : nameList) {
                System.out.println(name);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.1.2 基本查询(查询多个属性)

    Criteria c = session.createCriteria(User.class);
        //创建要查询的具体的属性
        PropertyProjection userName = Projections.property("userName");
        PropertyProjection gender = Projections.property("gender");
        //创建要查询的列的集合
        ProjectionList projectionList = Projections.projectionList();
        //把要查询的列加入到集合中
        projectionList.add(userName);
        projectionList.add(gender);
        //把要查询的属性集合 设置到projection中
        c.setProjection(projectionList);

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            //创建要查询的具体的属性
            PropertyProjection userName = Projections.property("userName");
            PropertyProjection gender = Projections.property("gender");
            //创建要查询的列的集合
            ProjectionList projectionList = Projections.projectionList();
            //把要查询的列加入到集合中
            projectionList.add(userName);
            projectionList.add(gender);
            //把要查询的属性集合 设置到projection中
            c.setProjection(projectionList);
            List<Object[]> objectsList  = c.list();
            for (Object[] objArray : objectsList) {
                System.out.println("姓名:"+objArray[0]+"：性别:"+objArray[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.2(QBC分组统计查询)

3.2.1QBC查询(函数使用 Count函数是统计某一属性有多少行)

    //设置要查询的项
          //查询总数的函数
        Projection rowCount = Projections.rowCount();
        //查询平均值的函数
        AggregateProjection salary = Projections.avg("salary");
        //查询总和的函数
           AggregateProjection salary = Projections.sum("salary");
           //查询最大值的函数
            AggregateProjection salary = Projections.max("salary");
            //查询最小值的函数
            AggregateProjection salary = Projections.min("salary");
        c.setProjection(rowCount);

        @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            AggregateProjection salary = Projections.count("gender");
            AggregateProjection salary1 = Projections.avg("salary");
            AggregateProjection salary2 = Projections.sum("salary");
            AggregateProjection salary3 = Projections.max("salary");
            AggregateProjection salary4 = Projections.min("salary");
            c.setProjection(salary);
            Object o = c.uniqueResult();
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.2.2QBC查询(分组统计查询:通过性别分组查询出最高薪水)

       Criteria c = session.createCriteria(User.class);
        ProjectionList pL = Projections.projectionList();
        //设置要查询的属性
        PropertyProjection gender = Projections.property("gender");
        AggregateProjection salary = Projections.max("salary");
        //根据性别进行分组
        PropertyProjection gender1 = Projections.groupProperty("gender");
        pL.add(gender);
        pL.add(salary);
        pL.add(gender1);
        c.setProjection(pL);

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            ProjectionList pL = Projections.projectionList();
            //设置要查询的属性
            PropertyProjection gender = Projections.property("gender");
            AggregateProjection salary = Projections.max("salary");
            //根据性别进行分组
            PropertyProjection gender1 = Projections.groupProperty("gender");
            pL.add(gender);
            pL.add(salary);
            pL.add(gender1);
            c.setProjection(pL);
            List<Object[]> objectsList = c.list();
            for (Object[] objects : objectsList) {
                System.out.println("性别:" + objects[0] + "：最高薪水:" + objects[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.4(QBC 限定排序分页查询)

       //分组排序
       SELECT gender,salary FROM t_user GROUP BY gender ORDER BY salary ASC;
       //分页查询
       SELECT *FROM  t_user LIMIT 100,10; 
       //limit后第一个参数开始id,第二个参数每页有多少个

3.4.1(排序查询)

    //设置排序字段·
        c.addOrder(Order.desc("salary"));

  @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            //设置要查询的属性
            PropertyProjection username = Projections.property("userName");
            PropertyProjection gender = Projections.property("gender");
            ProjectionList pL = Projections.projectionList();
            pL.add(username);
            pL.add(gender);
            c.setProjection(pL);
            //设置排序字段·
            c.addOrder(Order.desc("salary"));
            List<Object[]> objectsList = c.list();
            for (Object[] objects : objectsList) {
                System.out.println("姓名:" + objects[0] + "：性别" + objects[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.4.2(分页查询)

           //设置分页查询
        c.setFirstResult(1);
        c.setMaxResults(10);

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            //设置要查询的属性
            PropertyProjection username = Projections.property("userName");
            PropertyProjection gender = Projections.property("gender");
            ProjectionList pL = Projections.projectionList();
            pL.add(username);
            pL.add(gender);
            c.setProjection(pL);
            //设置分页查询
            c.setFirstResult(1);
            c.setMaxResults(10);
            List<Object[]> objectsList = c.list();
            for (Object[] objects : objectsList) {
                System.out.println("姓名:" + objects[0] + "：性别" + objects[1]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.4.2(限定查询)

        //创建QBC查询接口
        Criteria c = session.createCriteria(User.class);
        //创建查询条件
        SimpleExpression eq = Restrictions.eq("userName", "周志强");
        //把查询条件设置给QBC查询接口
        c.add(eq);
        List<User> userList = c.list();
         eq:等于
         gt：大于
         lt:小于
         ge:大于等于
         le：小于等于

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            Criteria c = session.createCriteria(User.class);
            SimpleExpression id = Restrictions.eq("userId", 1);
            c.add(id);
            List<User> userList = c.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.4.3(限定查询关于in的用法)

         //原始sql语句
         SELECT *FROM t_user u WHERE u.user_id IN(1,2,3);
         //创建QBC查询接口
        Criteria c = session.createCriteria(User.class);
        Integer[] userIds=new Integer[]{1,2,3,4};
        //设置in
        Criterion userId = Restrictions.in("userId", userIds);
        //将查询条件加入到查询接口中
        c.add(userId);

 @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //创建QBC查询接口
            Criteria c = session.createCriteria(User.class);
            Integer[] userIds=new Integer[]{1,2,3,4};
            //设置in
            Criterion userId = Restrictions.in("userId", userIds);
            //将查询条件加入到查询接口中
            c.add(userId);
            List<User> userList = c.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.4.4(限定查询关于and 和 or的用法)

             //原始sql语句
             SELECT *FROM t_user u WHERE u.salary>=5000 AND u.gender=0;

     @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //创建QBC查询接口
            Criteria c = session.createCriteria(User.class);
            Integer[] userIds=new Integer[]{1,2,3,4};
            //设置查询条件工资>=5000   and 性别=0
            LogicalExpression and = Restrictions.and(Restrictions.ge("salary", 5000), Restrictions.eq("gender", 0));
            //将查询条件加入到查询接口中
            c.add(and);
            List<User> userList = c.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

3.5(QBC 模糊查询)

        //原始sql语句
        SELECT *from t_user u WHERE u.user_name like '%志_';

  @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            //创建QBC查询接口
            Criteria c = session.createCriteria(User.class);
            Integer[] userIds=new Integer[]{1,2,3,4};
            //设置查询条件 姓名倒数第二个是志的人
            SimpleExpression userName = Restrictions.like("userName", "%志_");
            //将查询条件加入到查询接口中
            c.add(userName);
            List<User> userList = c.list();
            for (User user : userList) {
                System.out.println(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

4、hibernate中也可以使用普通sql

          String sql="select count(*) from t_user";
        SQLQuery query = session.createSQLQuery(sql);
        Object o = query.uniqueResult();
        System.out.println(o);

  @Test
    public void testQuery() {
        Session session = HibernateUtils.getSession();
        try {
            String sql="select count(*) from t_user";
            SQLQuery query = session.createSQLQuery(sql);
            Object o = query.uniqueResult();
            System.out.println(o);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            HibernateUtils.closeResource(session);
        }
    }

极乐码农

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
HQL语句详解

1、创建表 @Test public void testAdd() { Session session = HibernateUtils.getSession(); Transaction ts = session.beginTransaction(); try { for (int i = 0; i <...
复制链接

扫一扫