Hibernate使用SQL查询和HQL查询的区别

第一点:查询返回的对象不同

• SQL查询返回array数组
• HQL查询返回bean对象

第二点:查询语法不同

• SQL查询是基于数据库
• HQL查询是基于bean对象

public class HibernateSqlOrHql {

    static Session session;
    static Transaction tx;
    {
        session = HibernateSessionFactory.getSession();
        tx = session.beginTransaction();
    }

    @After
    public void afterTest() {
        tx.commit();
        HibernateSessionFactory.closeSession();
    }

    // sql增
    public void addDeptBySql(int dept_id, String dept_name) {
        String sql = "insert into dept(dept_id,dept_name)  values(:dept_id,:dept_name)";
        Query q = session.createSQLQuery(sql);
        System.out.println(q.getQueryString());
        q.setInteger("dept_id", dept_id);
        q.setString("dept_name", dept_name);
        q.executeUpdate();
    }

    // sql增测试
    @Test
    public void addDeptBySqlTest() {
        new HibernateSqlOrHql().addDeptBySql(1, "人事部");
        new HibernateSqlOrHql().addDeptBySql(2, "娱乐部");
        new HibernateSqlOrHql().addDeptBySql(3, "开发部");
        new HibernateSqlOrHql().addDeptBySql(4, "游戏部");
        System.out.println("成功添加");
    }

    // sql查询
    public static List<Object[]> findDeptByName(String dept_name) {
        Query q = session.createSQLQuery(
                "select dept_id,dept_name from dept d where d.dept_name like :dept_name  ");
        q.setString("dept_name", "%" + dept_name + "%");
        List<Object[]> arr = q.list();
        return arr;
    }

    // sql查询测试
    @Test
    public void TestFindDeptByName() {
        List<Object[]> list = findDeptByName("部");
        for (Object[] arr : list) {
            System.out.println(arr[0] + "--" + arr[1]);
        }

    }
    
    // sql语句查询
    public static List<Object[]> sqlSelect(String sql) {
        List<Object[]> list = new ArrayList<Object[]>();
        Query query = session.createSQLQuery(sql);
        list = query.list();
        return list;
    }

    // sql语句查询测试
    @Test
    public void sqlSelectTest() {
        List<Object[]> list = sqlSelect(
                "select dept_id,dept_name from dept where dept_name like '%部%'");
        for (Object[] arr : list) {
            System.out.println(arr[0] + "--" + arr[1]);
        }
    }

    // hql查询
    public static List<Dept> hqlSelectByName(String dept_name) {
        List<Dept> depts = new ArrayList<Dept>();
        Query q = session.createQuery(
                "from Dept dept where dept.dept_name like :dept_name");
        q.setString("dept_name", "%" + dept_name + "%");
        depts = q.list();
        return depts;
    }

    // hql查询测试
    @Test
    public void hqlSelectByName() {
        List<Dept> depts = hqlSelectByName("部");
        for (Dept dept : depts) {
            System.out.println(dept.getDept_id() + "--" + dept.getDept_name());
        }
    }

}