Hibernate（4）hibernate的HQL详解

àì夳堔傛蜴生んèń 2024-02-17 21:45 56阅读 0赞

## 1 为什么需要学习HQL（hibernate query language）？ ##

**面向对象的查询语言**，与SQL不同，HQL中的对象名是区分大小写的（除了Java类和属性，其他部分不区分大小写）；  
**HQL中查的是对象而不是表**，并且支持多态；  
HQL\*\*主要通过Query来操作\*\*`Query q = session.createQuery(hql);`

## 2 Hibernate HQL ##

*  **Hibernate 工具类**  
    [http://blog.csdn.net/u013943420/article/details/70908878\#t1][http_blog.csdn.net_u013943420_article_details_70908878_t1]
 *  **使用的表结构和测试数据**

create table student(
           sid number primary key,
           sname varchar2(45) not null,
           ssex char(2) not null,
           sdept varchar2(10) not null,
           sage number(3),
           saddress varchar2(45)
    );
    
    insert into student values(2004001, '林青霞', 'F', '计算机系', 22, '上海');
    insert into student values(2004002, '刘德华', 'M', '外语系', 23, '南京');
    insert into student values(2004003, '成龙', 'M', '化学系', 21, '山东');
    insert into student values(2004004, '林可心', 'F', '计算机系', 22, '北京');
    insert into student values(2004005, '周华健', 'M', '生物系', 24, '山东');
    insert into student values(2004006, '周润发', 'M', '数学系', 20, '湖北');
    
    create table course(
           cid number primary key,
           cname varchar2(50) not null,
           ccredit number(3)
    );
    
    insert into course values(11, 'java编程', 6);
    insert into course values(21, 'c++课程', 4);
    insert into course values(31, 'oracle', 3);
    insert into course values(41, 'javaEE', 100);
    insert into course values(51, 'linux', 1);
    
    create table studCourse(
           stuCourseId number primary key,
           sid number references student(sid),
           cid number references course(cid),
           grade number not null
    );
    
    insert into studCourse values(stucourse_seq.nextval, 2004001, 11, 90);
    insert into studCourse values(stucourse_seq.nextval, 2004001, 21, 19);
    insert into studCourse values(stucourse_seq.nextval, 2004003, 21, 45);
    insert into studCourse values(stucourse_seq.nextval, 2004004, 41, 99);
    insert into studCourse values(stucourse_seq.nextval, 2004006, 11, 39);
    
    create sequence stucourse_seq
    start with 1
    increment by 1
    minvalue 1 
    nomaxvalue
    nocycle
    nocache

### ①数据库表映射 ###

使用Hibernate工具，自动生成domain对象和映射文件，如果表有主外键的关系，则应当**先映射主表**，**再映射从表**。

### ②检索Student的全部属性 ###

**查询全部属性**：from Student  
**查询部分属性**：select sid, sname, saddress from Student

Session session = null;
    Transaction ts = null;
    
    try {
        session = HibernateUtil.getCurrentSession();
        ts = session.beginTransaction();
    
        // 检索所有学生信息
        List<Student> list = session.createQuery("from Student").list();
    
        for(Student stu: list) {
            System.out.println(stu.getSid() + " " + stu.getSname() + " " + 
                                stu.getSaddress());
        }
        Iterator<Student> it = list.iterator();
    
        while(it.hasNext()) {
            System.out.println(it.next().getSid() + " " + it.next().getSname() + " " + 
                    it.next().getSaddress());
        }
        ts.commit();
    } catch (Exception e) {
        e.printStackTrace();
        if(ts!=null) {
            ts.rollback();
        }
        throw new RuntimeException(e.getMessage());
    } finally {
        if(session!=null && session.isOpen()) {
            session.close();
        }
    }

### ③检索Student的部分属性 ###

检索学生的部分属性信息（**Hibernate建议我们把整个对象的所有属性都查询出来**）

// 核心代码
    List list = session.createQuery("select sid, sname, saddress from Student").list();
    
    for(int i=0; i<list.size(); i++) {
        Object objs[] = (Object[]) list.get(i);
        System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + 
                objs[2].toString());
    }
    //------------------当查询只返回一列数据时----------
    List list = session.createQuery("select count(*) from Student group by sdept").list();
    // list中放置的是一个对象，而不是对象数组
    for(int i=0; i<list.size(); i++) {
        Object objs = list.get(i);
        System.out.println(objs);
    }

### ④uniqueResult ###

uniqueResult:返回结果**顶多有一个对象时，可以使用*\*uniqueResult()得到结果；但是，***如果结果是多条，使用该方法就会抛出异常。\*

Student stu = (Student) session.createQuery("from Student where sid='2004003'").uniqueResult();
    if(stu!=null) {
        System.out.println(stu.getSid() + " " + stu.getSname() + " " + stu.getSaddress());
    }

### ⑤过滤重复值distinct 及 between…and 和 in 与 not in的使用 ###

// distinct
    List list = session.createQuery("select distinct sage, ssex from Student").list();
    // between...and
    List list = session.createQuery("select distinct sage, ssex from Student where sage between 20 and 22").list();
    // in 和 not in
    List list = session.createQuery("select sage, ssex, sname from Student where sdept in ('计算机系', '外语系')").list();
    
    for(int i=0; i<list.size(); i++) {
        Object objs[] = (Object[]) list.get(i);
        System.out.println(objs[0].toString() + " " + objs[1].toString());
    }

### ⑥分组查询group by 与 order by 及 having ###

List list = session.createQuery("select avg(sage), sdept from Student group by sdept").list();
    
    List list = session.createQuery("select count(*), sdept from Student group by sdept having count(*) > 1").list();
    // 女生多于1人的系
    List list = session.createQuery("select count(*), sdept from Student where ssex='F' group by sdept having count(*) > 1").list();

### ⑦其他常用查询 ###

**查询选修11号课程的最低分和最高分**

List list = session.createQuery("select 11, max(grade), min(grade) from Studcourse where course.cid = 11").list();
    
    for(int i=0; i<list.size(); i++) {
        
        Object objs[] = (Object[]) list.get(i);
        System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + 
                objs[2].toString());
    }

**显示各科考试不及格学生的姓名，科目和分数**

List list = session.createQuery("select student.sname, course.cname, grade from Studcourse where grade < 60").list();
    
    for(int i=0; i<list.size(); i++) {
        
        Object objs[] = (Object[]) list.get(i);
        System.out.println(objs[0].toString() + " " + objs[1].toString() + " " + 
                objs[2].toString());
    }

**显示各个科目不及格学生数量**

List list = session.createQuery("select course.cname, count(*) from Studcourse where grade < 60 group by course.cname").list();
    
    for(int i=0; i<list.size(); i++) {
        
        Object objs[] = (Object[]) list.get(i);
        System.out.println(objs[0].toString() + " " + objs[1].toString());
    }

### ⑧分页查询 ###

setFirstResult(i) 从第几个开始取  
setMaxResults(j) 最多取出多少个  
**按照年龄从小到大取出1~3个学生**

List<Student> list = session.createQuery("from Student order by sage").setFirstResult(2).setMaxResults(3).list();
    
    for(Student stu: list) {
        System.out.println(stu.getSid() + " " + stu.getSname() + " " + 
                            stu.getSage());
    }

**分页显示所有学生信息**

private static void showResultByPage(int pageSize) {
        // 设置分页变量
        int pageNow = 1;
        int pageCount = 1;
        int rowCount = 1;
        Session session = null;
        Transaction ts = null;
    
        try {
            session = HibernateUtil.getCurrentSession();
            ts = session.beginTransaction();
    
            rowCount = Integer.parseInt(session.createQuery("select count(*) from Student").uniqueResult().toString());
            pageCount = (rowCount - 1)/pageSize + 1;
    
            for(int i=1; i<=pageCount; i++) {
                System.out.println("******第"+i+"页**********");
                List<Student> list = session.createQuery("from Student").setFirstResult((i-1)*pageSize).setMaxResults(pageSize).list();
                for(Student stu: list) {
                    System.out.println(stu.getSid() + " " + stu.getSname() + " " + 
                                        stu.getSage());
                }
            }
    
            ts.commit();
        } catch (Exception e) {
            e.printStackTrace();
            if(ts!=null) {
                ts.rollback();
            }
            throw new RuntimeException(e.getMessage());
        } finally {
            if(session!=null && session.isOpen()) {
                session.close();
            }
        }
    }

### ⑨参数绑定 ###

setParameter();  
**使用参数绑定：**  
\- 可读性好  
\- 性能提高  
\- 防止sql注入  
**参数绑定有两种形式：**  
**？** ：这种方式使用位置绑定；  
**:xxx**：这种方式采用参数名绑定；  
如果两种方式同时使用，参数名绑定只能放在位置绑定之后

List<Student> list = session.createQuery("from Student where sage>? and saddress=:address")
            .setString(0, "20")
            .setString("address", "山东").list();
    for(Student stu: list) {
        System.out.println(stu.getSid() + " " + stu.getSname() + " " + 
                            stu.getSage());
    }

### ⑩ 在映射文件中得到hql语句 ###

可以从映射文件中得到hql，执行查询语句，这样可以更加灵活，在某些情况下，可以考虑使用：比如在Student.hbm.xml中**配置：**

<query name="myquerytest">
        from Student where sage>26
    </query>

**使用方式：**

List<Student> list = session.getNamedQuery("myquerytest").list();

### ⑪子查询、多表查询 ###

### ⑫Criteria 使用 ###

Session session = HibernateUtil.getCurrentSession();
    Criteria cri = session.createCriteria(Student.class);
    cri.add(Restrictions.gt("sage", (Integer)10));
    
    List<Student> list = cri.list();
    for(Student stu: list) {
        System.out.println(stu.getSname() + " " + stu.getSage()+ " " + 
                            stu.getSaddress());
    }

## 3 Hibernate工具类扩展 ##

public final class HibernateUtil {
        private static SessionFactory sessionFactory = null;
        // 线程局部模式
        private static ThreadLocal<Session> threadLoacal = new ThreadLocal<Session>();
        private HibernateUtil() {}
        static {
            sessionFactory = new Configuration().configure().buildSessionFactory();
        }
        // 获取全新的session
        public static Session openSession() {
            return sessionFactory.openSession();
        }
        // 获取和线程关联的session
        public static Session getCurrentSession() {
            Session session = threadLoacal.get();
            // 判断是否得到
            if(session==null) {
                session = sessionFactory.openSession();
                // 将session放入threadLocal
                threadLoacal.set(session);
            }
            return session;
        }
    
        /// 分页
        public static List executeQueryByPage(String hql, String[] paras, int pageSize, int pageNow) {
            Session session = null;
            List list = null;
            try {
                session = openSession();
    
                Query query = session.createQuery(hql);
    
                if(paras!=null && paras.length>0) {
                    for(int i=0; i<paras.length; i++) {
                        query.setString(i, paras[i]);
                    }
                }
    
                /// 
                query.setFirstResult((pageNow-1)*pageSize).setMaxResults(pageSize);
    
                list = query.list();
    
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            } finally {
                if(session!=null && session.isOpen()) {
                    session.close();
                }
            }
    
            return list;
        }
    
        /// 查询接口
        public static List executeQuery(String hql, String[] paras) {
            Session session = null;
            List list = null;
            try {
                session = openSession();
    
                Query query = session.createQuery(hql);
    
                if(paras!=null && paras.length>0) {
                    for(int i=0; i<paras.length; i++) {
                        query.setString(i, paras[i]);
                    }
                }
                list = query.list();
    
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            } finally {
                if(session!=null && session.isOpen()) {
                    session.close();
                }
            }
    
            return list;
        }
        /// 修改和删除 批量sql
        public static void executeUpdate(String hql, String[] paras) {
            Session session = null;
            Transaction ts = null;
            try {
                session = openSession();
                ts = session.beginTransaction();
                Query query = session.createQuery(hql);
    
                if(paras!=null && paras.length>0) {
                    for(int i=0; i<paras.length; i++) {
                        query.setString(i, paras[i]);
                    }
                }
    
                query.executeUpdate();
    
                ts.commit();
            } catch (Exception e) {
                if(ts!=null) {
                    ts.rollback();
                }
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            } finally {
                if(session!=null && session.isOpen()) {
                    session.close();
                }
            }
        }
    
        // 添加
        public static void save(Object obj) {
            Session session = null;
            Transaction ts = null;
            try {
                session = openSession();
                ts = session.beginTransaction();
                session.save(obj);
                ts.commit();
            } catch (Exception e) {
                if(ts!=null) {
                    ts.rollback();
                }
                e.printStackTrace();
                throw new RuntimeException(e.getMessage());
            } finally {
                if(session!=null && session.isOpen()) {
                    session.close();
                }
            }
        }
    }

[http_blog.csdn.net_u013943420_article_details_70908878_t1]: http://blog.csdn.net/u013943420/article/details/70908878#t1