Hibernate查询语言HQL详解
来源:互联网 发布:错过淘宝微信 编辑:程序博客网 时间:2024/06/11 14:51
常用的HQL语句
HQL: Hibernate Query Language.
特点:
1,与SQL相似,SQL中的语法基本上都可以直接使用。
2,SQL查询的是表和表中的列;HQL查询的是对象与对象中的属性。
3,HQL的关键字不区分大小写,类名与属性名是区分大小写的。
4,SELECT可以省略.
1,简单的查询
Employee为实体名而不是数据库中的表名(面向对象特性)
hql = "FROM Employee";hql = "FROM Employee AS e"; // 使用别名hql = "FROM Employee e"; // 使用别名,as关键字可省略
2,带上过滤条件的(可以使用别名):Where
hql = "FROM Employee WHERE id<10";hql = "FROM Employee e WHERE e.id<10";hql = "FROM Employee e WHERE e.id<10 AND e.id>5";
3,带上排序条件的:Order By
hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name";hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name DESC";hql = "FROM Employee e WHERE e.id<10 ORDER BY e.name DESC, id ASC";
4,指定select子句(不可以使用select *)
hql = "SELECT e FROM Employee e"; // 相当于"FROM Employee e"hql = "SELECT e.name FROM Employee e"; // 只查询一个列,返回的集合的元素类型就是这个属性的类型hql = "SELECT e.id,e.name FROM Employee e"; // 查询多个列,返回的集合的元素类型是Object数组hql = "SELECT new Employee(e.id,e.name) FROM Employee e"; // 可以使用new语法,指定把查询出的部分属性封装到对象中
5,执行查询
获得结果(list、uniqueResult、分页 )
Query query = session.createQuery("FROM Employee e WHERE id<3");query.setFirstResult(0);query.setMaxResults(10); // 等同于 limit 0,10两种查询结果list、uniqueResult List list = query.list(); // 查询的结果是一个List集合Employee employee = (Employee) query.uniqueResult();// 查询的结果是唯一的一个结果,当结果有多个,就会抛异常
6,方法链
List list = session.createQuery( "FROM Employee e") .setFirstResult(0) .setMaxResults(10) .list();
7,聚集函数
count(), max(), min(), avg(), sum()
hql = "SELECT COUNT(*) FROM Employee"; // 返回的结果是Long型的hql = "SELECT min(id) FROM Employee"; // 返回的结果是id属性的类型
8,分组: Group By … Having
hql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name";hql = "SELECT e.name,COUNT(e.id) FROM Employee e GROUP BY e.name HAVING count(e.id)>1";hql = "SELECT e.name,COUNT(e.id) FROM Employee e WHERE id<9 GROUP BY e.name HAVING count(e.id)>1";hql = "SELECT e.name,COUNT(e.id) " + "FROM Employee e " + "WHERE id<9 " + "GROUP BY e.name " + "HAVING count(e.id)>1 " + "ORDER BY count(e.id) ASC";hql = "SELECT e.name,COUNT(e.id) AS c " + "FROM Employee e " + "WHERE id<9 " + "GROUP BY e.name " + "HAVING count(e.id)>1 " + // 在having子句中不能使用列别名 "ORDER BY c ASC"; // 在orderby子句中可以使用列别名
9,连接查询
HQL是面向对象的查询
内连接(inner关键字可以省略)
hql = "SELECT e.id,e.name,d.name FROM Employee e JOIN e.department d"; hql = "SELECT e.id,e.name,d.name FROM Employee e INNER JOIN e.department d";
左外连接(outer关键字可以省略)
hql = "SELECT e.id,e.name,d.name FROM Employee e LEFT OUTER JOIN e.department d";
右外连接(outer关键字可以省略)
hql = "SELECT e.id,e.name,d.name FROM Employee e RIGHT JOIN e.department d"; //可以使用更方便的方法 hql = "SELECT e.id,e.name,e.department.name FROM Employee e";
10,查询时使用参数
方式一:使用’?’占位
hql = "FROM Employee e WHERE id BETWEEN ? AND ?"; List list2 = session.createQuery(hql) .setParameter(0, 5)// 设置参数,第1个参数的索引为0。 .setParameter(1, 15) .list();
方式二:使用变量名
hql = "FROM Employee e WHERE id BETWEEN :idMin AND :idMax"; List list3 = session.createQuery(hql) .setParameter("idMax", 15) .setParameter("idMin", 5) .list();// 当参数是集合时,一定要使用setParameterList()设置参数值 hql = "FROM Employee e WHERE id IN (:ids)"; List list4 = session.createQuery(hql) .setParameterList("ids", new Object[] { 1, 2, 3, 5, 8, 100 }) .list();
11,update与delete,不会通知Session缓存
Update
int result = session.createQuery( "UPDATE Employee e SET e.name=? WHERE id>15") .setParameter(0, "无名氏") .executeUpdate(); // 返回int型的结果,表示影响了多少行。
Delete
int result1 = session.createQuery(// "DELETE FROM Employee e WHERE id>15") .executeUpdate(); // 返回int型的结果,表示影响了多少行。
工具类:将HQL语句转换为SQL语句
/*** 将hql 语句转换为sql语句* @param hql 要转换的 hql语句*/public String transHqlToSql(String hql){ SessionFactoryImpl sfi = (SessionFactoryImpl)awMixThrProDao.getSessionD().getSessionFactory(); QueryTranslatorFactory qtf = sfi.getSettings().getQueryTranslatorFactory(); FilterTranslator qt = qtf.createFilterTranslator(hql, hql, null, sfi); qt.compile( null, false ); System. out.println(" hql==>sql: " +qt.getSQLString()); return qt.getSQLString();}
HQL生成工具类
/** * 根据实体构建hql 语句 * @author lxl * @createTime 2013-05 -10 16:00 * @version 1.0 */public class BuildHqlUtil { /** * 根据实体生成对应的hql语句,对象中具有值的属性将成为查询的限制条件部分<br/> * 其中,对于String类型的属性是 like 判断的,以其开头的:like '[value]%' * @param entity * @return */ @SuppressWarnings( "unchecked") public static String buildSelectStr(Object entity) { return buildSelectStr(entity, null); } /** * 根据实体生成有字段限制的 hql语句,对象中具有值的属性将成为查询的限制条件部分<br/> * 其中,对于String类型的属性是 like 判断的,以其开头的:like '[value]%' * @param entity * @param queryFields 数组形式的查询字段:select xxx,xxx ... * @return */ @SuppressWarnings( "unchecked") public static String buildSelectStr(Object entity, String[] queryFields) { if (entity == null) { try { throw new Exception("实体不能为null"); } catch (Exception e) { e.printStackTrace(); return null ; } } Class clazz = entity.getClass(); String hqlStr = ""; try { Field[] fields = clazz.getDeclaredFields(); Field.setAccessible(fields, true); hqlStr = getFieldMsg(fields, queryFields) + getWhereMsg(entity, fields, queryFields); } catch (Exception e) { e.printStackTrace(); } return hqlStr; } /** * 构建查询字段 * @param fields * @param queryFields * @return * @throws Exception */ private static String getFieldMsg(Field[] fields, String[] queryFields) throws Exception { String selectStr = ""; int i, j; if (queryFields != null && queryFields.length != 0) { selectStr = "select "; for (i = 0; i < queryFields.length ; i++) { if (!"" .equals(queryFields[i])) { boolean flag = false; for (j = 0; j < fields.length; j++) { if (queryFields[i].equals(fields[j].getName())) { flag = true; } } if (flag) { selectStr += queryFields[i] + ","; } else { throw new Exception("实体中不存在该字段:" + queryFields[i]); } } } if ("select " .equals(selectStr)) { selectStr = ""; } else { selectStr = selectStr.substring(0, selectStr.length() - 1) + " "; } } return selectStr; } /** * 构建查询条件 * * @param entity * @param fields * @param queryFields * @return * @throws IllegalArgumentException * @throws IllegalAccessException */ private static String getWhereMsg(Object entity, Field[] fields, String[] queryFields) throws IllegalArgumentException, IllegalAccessException { String whereStr = "from " + entity.getClass().getSimpleName() + " where 1=1"; for (int i = 0; i < fields.length; i++) { if (fields[i].get(entity) == null || fields[i].get(entity).toString().trim().length() == 0) { continue; } String value = fields[i].get(entity).toString(); String field = fields[i].getName(); if (isNum(fields[i].getType().getName())) { whereStr += " and " + field + "=" + value; } else { whereStr += " and " + field + " like '" + value + "%'"; } } return whereStr; } /** * 判断是否为Integer,Double,Float等数字类型,注意都是封装类型 * @param fieldName * @return */ private static boolean isNum(String fieldName) { boolean flag = false; if (fieldName.endsWith(".Integer" ) || fieldName.endsWith( ".Double") || fieldName.endsWith( ".Float") || fieldName.endsWith( ".Long") || fieldName.endsWith( ".Number") || fieldName.endsWith( ".BigDecimal")) { flag = true; } return flag; }}
一点注意
关于Hibernate执行带有join的HQL语句:
Query query = session.createQuery("select c from Custom as c join c.orders as ord where ord.name = : name");query.setString("name", "1213233");List list = query.list();for(int i=0;i<list.size();i++){ Custom custom = (Custom)list.get(i);}
1 0
- Hibernate查询语言HQL详解
- Hibernate查询语言:HQL 详解(转载)
- Hibernate查询语言:HQL
- Hibernate 查询语言(HQL)
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言:HQL
- Hibernate查询语言HQL
- HQL: Hibernate查询语言
- HQL: Hibernate查询语言
- Hibernate查询语言HQL
- HQL: Hibernate查询语言
- HQL: Hibernate查询语言
- Android View 给触摸操作提供视觉提示 细致才是王道
- 注释转换 ——C++注释转换为标准C语言注释
- jquery easyui 问题合集
- Android_常见控件之spinner详解
- 讨论Matlab中double,im2double,mat2gray函数区别
- Hibernate查询语言HQL详解
- IOS8 UI: 由 self.view = nil 引起的思考
- Eclipse--theme--color 插件设置
- Direct-X学习笔记--DirectInput
- com.mysql.jdbc.Driver和org.gjt.mm.mysql.Driver区别
- Java中Collection和Collections的区别
- POJ 1061 青蛙的约会(一元线性同余方程)
- ActiveMQ学习笔记(2)——JMS消息模型
- 从源码安装gcc 4.8.2