HQL语句查询

单表查询

查询一个字段

List<String> list0 = session.createQuery("select name from Province ").list();

 

查询部分字段

通过对象数组接收

 List<Object[]> list = session.createQuery("select id,name,code from Province ").list();
        for(int i=0;i<list.size();i++){
            Object[] obj = list.get(i);
            System.out.println(obj[0]+ ":  "+obj[1] + "   "  + obj[2]);
        }

 

通过对象接收

List<Province> list1 = session.createQuery("select new Province(id,name,code) from Province ").list();
        for(Province province:list1){
            System.out.println(province.getName()+"  "+province.getCode());
        }

查询unique

 

Number number = (Number) session.createQuery("select count(id) from Province ").uniqueResult();
        System.out.println(number);

查询根据某个字段排序

 List<City> cities = session.createQuery("select id,name,code from City order by code desc,name asc").list();

条件查询

查询id>30的所有province

List<Province> provinceList = session.createQuery("select p from Province p where id > ?").setInteger(0,30).list();

多条件查询

String hql = "select p from Province p where id > ? and id < ? ";
        Query query = session.createQuery(hql);
        query.setParameter(0,20).setParameter(1,30);
        List<Province> provinceList1 = query.list();

命名参数占位符

 String hql1 = "select p from Province p where id > :i1 and id < :i2 and code = :code ";
        List<Province> provinceList2 = session.createQuery(hql1).setParameter("i1",5).setParameter("i2",30).setParameter("code","410000").list();

分页查询

List<Province> provinceList3 = session.createQuery("from Province ").setFirstResult(9).setMaxResults(4).list();

 

多表关联查询

 

统计各个省对应的市总数

group by 统计函数
select 后面跟的列  只能是groupby分组的列 +  统计函数

 

List<Object[]> list = session.createQuery("select  c.province.name,count(c.id) from City c group by c.province.name").list();
        for (Object[] objects :list){
            System.out.println(objects[0]  +"  "+objects[1]);
        }

查询 市名称，对应的省名称  使用join关联

List<Object[]> list1 = session.createQuery("select c.name,p.name from City c join c.province as p").list();


        List<Object[]> list2 = session.createQuery("select c.name,p.name from City c inner join c.province as p").list();

        //左外连接
        List<Object[]> list3 = session.createQuery("select c.name,p.name from City c left outer join c.province as p").list();

        //右外连接
        List<Object[]> list4 = session.createQuery("select c.name,p.name from City c right outer join c.province as p").list();

 

使用原生sql查询  地区名，市名，省名称

List<Object[]> list = session.createSQLQuery("select a.name as aname,c.name as cname,p.name as pname from area a join city c on c.code=a.citycode join province p on c.provincecode=p.code").list();