从Hibernate 3.0.x/3.1.x升级到最新的3.2版之后,3.2版的很多sql函数如count(), sum()的唯一返回值已经从Integer变为Long,如果不升级代码,会得到一个ClassCastException。 | ||||||||
COUNT 返回Long对象 | ||||||||
MAX MIN 返回类型是跟所使用的字段类型有关 | ||||||||
AVG 返回Double | ||||||||
SUM 如使用字段是整形,返回Long(以前是返回BigInteger),如果是浮点类型那么就返回Double. | ||||||||
1. uniqueResult() 唯一的结果。如果查询的结果只包含一个对象,可使用.uniqueResult() | ||||||||
注意:如果查询结果包含多个对象,使用uniqueResult抛出异常 | ||||||||
2. 获取总的记录数 --- 注意:HQL中不要使用count(1) | ||||||||
List<Number> list = session.createQuery("select count(c) from Customer c").list(); | ||||||||
Long count = list.get(0).longValue(); | ||||||||
System.out.println(count); | ||||||||
3. 获取某一列数据的SUM | ||||||||
Number count = session.createQuery("select sum(c.cust_id) from Customer c").uniqueResult(); | ||||||||
System.out.println(.longValue()); | ||||||||
4. 分组的查询 | ||||||||
使用group by 属性 进行分组 | ||||||||
HQL多表查询 | ||||||||
1. 多表的查询进来使用HQL语句进行查询,HQL语句和SQL语句的查询语法比较类似。 | ||||||||
* 内连接查询 | ||||||||
* 显示内连接 | ||||||||
* select * from customers c inner join orders o on c.cid = o.cno; | ||||||||
* 隐式内连接 | ||||||||
* select * from customers c,orders o where c.cid = o.cno; | ||||||||
* 外连接查询 | ||||||||
* 左外连接 | ||||||||
* select * from customers c left join orders o on c.cid = o.cno; | ||||||||
* 右外连接 | ||||||||
* select * from customers c right join orders o on c.cid = o.cno; | ||||||||
2. HQL的多表查询 | ||||||||
* 迫切和非迫切: | ||||||||
* 非迫切返回结果是Object[] | ||||||||
* 迫切连接返回的结果是对象,例如:把客户的信息封装到客户的对象中,把订单的信息封装到客户的Set集合中。 | ||||||||
3. 内连接查询 | ||||||||
* 内连接使用 inner join ,默认返回的是Object数组 | ||||||||
List<Object[]> list = session.createQuery("from Customer c inner join c.linkmans").list(); | ||||||||
for (Object[] objects : list) { | ||||||||
System.out.println(Arrays.toString(objects)); | ||||||||
} | ||||||||
迫切内连接:inner join fetch ,返回的是实体对象 | ||||||||
List<Customer> list = session.createQuery("from Customer c inner join fetch c.linkmans").list(); | ||||||||
Set<Customer> set = new HashSet<Customer>(list); | ||||||||
for (Customer customer : set) { | ||||||||
System.out.println(customer); | ||||||||
} | ||||||||
4. 左外连接查询 | ||||||||
左外连接: 封装成List<Object[]> | ||||||||
迫切左外连接 | ||||||||
List<Customer> list = session.createQuery("from Customer c left join fetch c.linkmans").list(); | ||||||||
Set<Customer> set = new HashSet<Customer>(list); | ||||||||
for (Customer customer : set) { | ||||||||
System.out.println(customer); | ||||||||
} | ||||||||
案例: | ||||||||
public List<Map<String, Object>> getSumList(Date startDate, Date endDate) { | ||||||||
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd 00:00:00"); | ||||||||
SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd 23:59:59"); | ||||||||
String hql="select sum(r.winAmount) as winAmount,sum(r.balance) as balance from ReportMemberFund as r where 1 = 1 "; | ||||||||
if(startDate!=null){ | ||||||||
hql += " and r.statisticsDate >='"+df.format(startDate)+"'"; | ||||||||
} | ||||||||
if(endDate!=null){ | ||||||||
hql += " and r.statisticsDate <='"+df1.format(endDate)+"'"; | ||||||||
} | ||||||||
Query query=getSession().createQuery(hql); | ||||||||
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP); | ||||||||
return query.list(); | ||||||||
} |
“相关推荐”对你有帮助么?
-
非常没帮助
-
没帮助
-
一般
-
有帮助
-
非常有帮助
提交