HQL语句查询

Love The Way You Lie 2022-04-15 03:07 301阅读 0赞

## 单表查询 ##

### 查询一个字段 ###

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();