什么是hql以及hql和sql的区别

一、hql的定义

Hibernate 查询语言（HQL）是一种面向对象的查询语言，类似于 SQL，但不是去对表和列进行操作，而是面向对象和它们的属性。

二、HQL

1.HQL里面的类名和属性名区分大小写，但关键字不区分大小写 2. ? 从下标0开始计算位置 3. HQL支持:命名参数 4. HQL是面向对象的查询语言

三、SQL

1.SQL里面的表名和列名不区分大小写 2.? 从顺序1开始计算位置 3.SQL不支持:命名参数 4.SQL是面向结构的查询语言

四、处理返回的结果集

4.1 单个对象：通过查询语句得到这个对象的所有属性值 示例代码：

@Test public void list() { String hql="from User"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); //运行这个批量对象 List<User> list = query.list(); for (User user : list) { System.out.println(user); } transaction.commit(); SessionFactoryUtil.closeSession(); }

4.2 Object[]:通过查询语句得到指定的几个属性的值 示例代码：

@Test public void list2() { String hql="select userName,userPwd from User"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); //运行这个批量对象 List<Object[]> list = query.list(); for (Object[] user : list) { System.out.println(Arrays.toString(user)); } transaction.commit(); SessionFactoryUtil.closeSession(); }

4.3 Map 示例代码：

@Test public void list5() { // 第二种hql语句 String hql="select new map(u.userName,u.userPwd) from User u"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); //运行这个批量对象 List<Map<Object, Object>> list = query.list(); for (Map<Object, Object> user : list) { System.out.println(user); } transaction.commit(); SessionFactoryUtil.closeSession(); }

4.4 new 构造方法(attr1,attr2) 示例代码：

@Test public void list6() { String hql="select new User(u.userName,u.userPwd) from User u"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); //运行这个批量对象 List<User> list = query.list(); for (User user : list) { System.out.println(user); } transaction.commit(); SessionFactoryUtil.closeSession(); } /**

五.hql中使用占位符

5.1 ?占位符 从下标0开始计算位置 示例代码：

@Test public void list7() { String hql="select u.userName,u.userPwd from User u where u.userName=?"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); query.setString(0, "zs"); //运行这个批量对象 List<Object[]> list = query.list(); for (Object[] user : list) { System.out.println(Arrays.toString(user)); } transaction.commit(); SessionFactoryUtil.closeSession(); }

5.2 :命名参数 示例代码：

@Test public void list8() { String hql="select u.userName,u.userPwd from User u where u.userName=:uname"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); query.setString("uname", "zs"); //运行这个批量对象 List<Object[]> list = query.list(); for (Object[] user : list) { System.out.println(Arrays.toString(user)); } transaction.commit(); SessionFactoryUtil.closeSession(); }

六.连接查询

示例代码：

@Test public void list9() { String hql="select o.orderNo,oi.quantity from Order o,OrderItem oi " + " where o=oi.order and o.orderId=:orderid"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); query.setInteger("orderid", 3); //运行这个批量对象 List<Object[]> list = query.list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); } transaction.commit(); SessionFactoryUtil.closeSession(); }

七.聚合函数

示例代码：

@Test public void list12() { String hql="select count(*),max(b.price),min(b.price),avg(round(b.price)),sum(b.price) from Book b"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); //运行这个批量对象 List<Object[]> list = query.list(); for (Object[] objects : list) { System.out.println(Arrays.toString(objects)); } transaction.commit(); SessionFactoryUtil.closeSession(); }

八.hql分页

示例代码：

@Test public void list13() { int page=1; int row=3; String hql="from User"; Session session = SessionFactoryUtil.getSession(); Transaction transaction = session.beginTransaction(); //创建批量对象 Query query = session.createQuery(hql); query.setFirstResult((page-1)*row);//设置起始记录下标 query.setMaxResults(row);//设置返回的最大结果集 //运行这个批量对象 List<User> list = query.list(); for (User user : list) { System.out.println(user); } transaction.commit(); SessionFactoryUtil.closeSession(); }