Hql语句注意事项总结
(1)使用统计函数作为输出结果
select sum(total),sum(totalup) from AD
where
aid
=?
and fid
=?
and vdatetime
>=?
and vdatetime
<=?
group by aid
(2)使用统计函数作为查询约束条件
select
uid
from
MM
group
by
uid
having
count
(
*
)
>
?
and
min
(m_date)
<
?
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(2)使用统计函数作为查询约束条件
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
(3)使用group by 时有关约束条件的问题
如果约束条件是单个字段,则必须用where,并且where要放在group by之前,如:
select cityname from MI where vdatetime >= ? and vdatetime < ? group by cityname
如果约束条件是统计函数,则要使用having,并且having要放在group by之后,如:(2)
(4)其实,在某些场合,group by可以用distinct代替,如以下两条语句功能相同:
select cityname from MI where vdatetime >= ? and vdatetime < ? group by cityname
select distinct cityname from MI where vdatetime >= ? and vdatetime < ?
2.在count()中使用别名问题
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
在Hibernate3.2中,所有的计数都要用Long,用Integer的话就会有问题.
List list
=
this
.find(
"
select count(*) from MMC as MMC where MMC.uid <> ? and MMC.email = ?
"
,
new
String[]
...
{uid,email}
);
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
if
(list
!=
null
&&!
list.isEmpty())
...
{
long count=Long.parseLong(list.get(0).toString());
if(count>0)
return true;
else
return false;
}
4.关于参数是数组的问题
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0196c3df5ea9e936f21e9932cca91014.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
在实际的工作中,经常遇到这种情况:
(1)要删除一批数据,传过来一个数组,
(2)或者要查询一批数据,查询条件也是传过来一个数组,
这种情况下如何进行处理?
(1)查询数据,查询条件为一个数组,可以有如下两个方法:
<1>直接拼Hql语句
String[] ids
=
...
{"1","2","3"}
;
String str
=
""
;
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
for
(
int
i
=
0
;i
<
ids.length;i
++
)
...
{
str+="'"+ids[i]+"'";
if(i!=(ids.length-1))
str+=",";
}
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
List list;
list
=
appinfoManager.find(
"
from Appinfo where id in (
"
+
str
+
"
)
"
);
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
if
(list
!=
null
)
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
for(int i=0;i<list.size();i++)...{
Appinfo app = (Appinfo)list.get(i);
System.out.println(app.getAppname());
}
}
System.out.println(
"
共有
"
+
list.size()
+
"
条记录
"
);
<2>利用回调函数处理
public
List getApList()
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
return (List)this.getHibernateTemplate().execute(new HibernateCallback() ...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public Object doInHibernate(Session session) throws HibernateException, SQLException ...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
String[] ids = ...{"1","2","3"};
String hql= "from Appinfo where id in (:ids)";
Query query = session.createQuery(hql);
List list = query.setParameterList("ids", ids).list();
return list;
}
});
}
(2)删除数据,参数为数组,利用回调函数
public
void
delInArray()
...
{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
this.getHibernateTemplate().execute(new HibernateCallback() ...{
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
public Object doInHibernate(Session session) throws HibernateException, SQLException ...{
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
String[] ids = ...{"1","2","3"};
String hql= "delete Appinfo where id in (:ids)";
Query query = session.createQuery(hql);
query.setParameterList("ids", ids).executeUpdate();
return null;
}
});
}
(1)要删除一批数据,传过来一个数组,
(2)或者要查询一批数据,查询条件也是传过来一个数组,
这种情况下如何进行处理?
(1)查询数据,查询条件为一个数组,可以有如下两个方法:
<1>直接拼Hql语句
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0196c3df5ea9e936f21e9932cca91014.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/717446ca04a6125dc5b6b54e0fa14ab4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0196c3df5ea9e936f21e9932cca91014.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/717446ca04a6125dc5b6b54e0fa14ab4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/717446ca04a6125dc5b6b54e0fa14ab4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0196c3df5ea9e936f21e9932cca91014.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/1327ab569c1ae82736693a50b8e33378.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/717446ca04a6125dc5b6b54e0fa14ab4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/717446ca04a6125dc5b6b54e0fa14ab4.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/0196c3df5ea9e936f21e9932cca91014.gif)