HQL查询语句

HQL查询

hql基本查询
单属性和多属性查询

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

将多个查询分装成对象

List<Province> list1 = session.createQuery("select new Province(id,name,code) from Province").list();
        for (int i = 0;i<list1.size();i++) {
            //System.out.println(p.getName()+" "+p.getCode());
            System.out.println(list1.get(i)+"========");
        }
       System.out.println(list1);

别名使用

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

查询所有列（不能使用*）
使用别名和使用from

List<Province> list = session.createQuery("select p from Province p").list();
        for(Province province:list) {
            System.out.println(province);
        }

          List<Province> list = session.createQuery("from Province").list();
        for(Province province:list) {
            System.out.println(province);
        }

占位符? 从0开始
使用setParameter和命名查询询，命名查询–设置条件参数的名称 以冒号开头后更名称 设置参数时 只需指定名
setInterger(0,4)跟setParameter(0,4)效果一样

List<Province> list = session.createQuery("from Province p where id<?")
                .setParameter(0,4).list();
        for(Province province:list) {
            System.out.println(province);
        }
        List<Province> list = session.createQuery("from Province p where id<:id")
                .setParameter("id",4).list();
        for(Province province:list) {
            System.out.println(province);
        }

分页查询

 /*
       setFirstResult():开始从这个下标开始，不包括这个下标
       setMaxResults():设置每页记录数pageSize
       */
       /* List<Province> list = session.createQuery("from Province p")
                .setFirstResult(3).setMaxResults(9).list();
        for(Province province:list) {
            System.out.println(province);
        }*/

统计查询

 Number count =  (Number) session.createQuery("select max(p.id) from Province p").uniqueResult();
            System.out.println("总数："+count.byteValue());

分组查询

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

排序
desc降序
asc升序

List<Province> list = session.createQuery("from Province order by id desc").list();
        for (Province province:list) {
            System.out.println(province);
        }

连接查询
内连接

String hql = "from City c where c.province.name=:name";
        hql = "select c from City c join c.province d where d.name=:name";
        hql = "select c from City c inner join c.province d where d.name=:name";
        List<City> list = session.createQuery(hql).setString("name","河南省").list();
        for (City c:list
             ) {
            System.out.println(c);
        }

左外连接

 String hql ="select p.name,c.name from City c left outer join c.province p";
        Query query = session.createQuery(hql);
        List<Object[]> list = query.list();
        for (Object[] o:list
             ) {
            System.out.println(o[0]+"  "+o[1]);

        }

本地查询 sql查询

String sql="select Name,Price from BOOK";
List<Object[]> list =session.createSQLQuery(sql)
.list();
for(Object[] b:list){
System.out.println(b[0]+"-"+b[1]);
}