我的一个项目使用了Hibernate3操作Oracle9i数据库,遇到一个很奇怪的问题,即在按某些使用了聚合函数的值的升序排序后,翻页到一定页数(通常是3或5)以后,显示的内容不会变化,即出现不是期望的查询结果.
刚开始仔细排查,找不出原因.后来通过查Hibernate 包中的Oracle9Dialect及OracleDialect的源码,将Dialect换成 OracleDialect后问题解决.
但是,问题虽解决了,根源何在呢?为什么Oracle9却要用到OracleDialect(源码注解中说这是兼容Oracle8i)的才能解决问题呢?!
查看源码,发现两者取得分页查询字符串的方式是有点区别的(在 getlimitString()方法中).
在Oracle9Dialect中:
public
String getLimitString(String sql,
boolean
hasOffset)
{
sql
=
sql.trim();
boolean
isForUpdate
=
false
;
if
( sql.toLowerCase().endsWith(
"
for update
"
) )
{
sql
=
sql.substring(
0
, sql.length()
-
11
);
isForUpdate
=
true
;
}
StringBuffer pagingSelect
=
new
StringBuffer( sql.length()
+
100
);
if
(hasOffset)
{
pagingSelect.append(
"
select * from ( select row_.*, rownum rownum_ from (
"
);
}
else
{
pagingSelect.append(
"
select * from (
"
);
}
pagingSelect.append(sql);
if
(hasOffset)
{
pagingSelect.append(
"
) row_ where rownum <= ?) where rownum_ > ?
"
);
}
else
{
pagingSelect.append(
"
) where rownum <= ?
"
);
}
if
(isForUpdate) pagingSelect.append(
"
for update
"
);
return
pagingSelect.toString();
}
在OracleDialect中:
public
String getLimitString(String sql,
boolean
hasOffset)
{
sql
=
sql.trim();
boolean
isForUpdate
=
false
;
if
( sql.toLowerCase().endsWith(
"
for update
"
) )
{
sql
=
sql.substring(
0
, sql.length()
-
11
);
isForUpdate
=
true
;
}
StringBuffer pagingSelect
=
new
StringBuffer( sql.length()
+
100
);
if
(hasOffset)
{
pagingSelect.append(
"
select * from ( select row_.*, rownum rownum_ from (
"
);
}
else
{
pagingSelect.append(
"
select * from (
"
);
}
pagingSelect.append(sql);
if
(hasOffset)
{
pagingSelect.append(
"
) row_ ) where rownum_ <= ? and rownum_ > ?
"
);
}
else
{
pagingSelect.append(
"
) where rownum <= ?
"
);
}
if
(isForUpdate) pagingSelect.append(
"
for update
"
);
return
pagingSelect.toString();
}
两者的区别主要在于,前者:
row_ where rownum <= ?) where rownum_ > ?
后者:
where rownum_ <= ? and rownum_ > ?
我模拟了我的出问题的查询,使用前者问题重现,使用后者不出问题.
另外是,只在升序排序时才出问题,降序则不会.
我的语句分别如下:
*************************************************************************
第一种:
select
*
from
(
select
rownum row_num ,t.
*
from
(
select
Sum
(b.disp_Count) ,
Sum
(b.click_Count) ,
Sum
(b.total_Price) ,
Sum
(b.return_Cost) ,
avg
(b.rank) , b.sta_Date , b.keyword_Name ,
b.union_Name
from
Bid_Report b
where
(b.sta_Date
=
'
20051129
'
)
group
by
b.sta_Date, b.keyword_Name, b.union_Name
order
by
Sum
(b.click_Count), b.sta_Date
) t
where
rownum
<=
60
)
where
row_num
>
40
(在Hibernate3中,Oracle9Dialect的getLimitString()方法采取类似实现方式)
第二种:
select
*
from
(
select
rownum row_num ,t.
*
from
(
select
Sum
(b.disp_Count) ,
Sum
(b.click_Count) ,
Sum
(b.total_Price) ,
Sum
(b.return_Cost) ,
avg
(b.rank) , b.sta_Date , b.keyword_Name ,
b.union_Name
from
Bid_Report b
where
(b.sta_Date
=
'
20051129
'
)
group
by
b.sta_Date, b.keyword_Name, b.union_Name
order
by
Sum
(b.click_Count), b.sta_Date
) t
)
where
row_num
<=
60
and
row_num
>
40
(在Hibernate3中,OracleDialect的getLimitString()方法采取类似实现方式)
**********************************************************************
现在,问题是:为什么采取后者就可以解决问题了呢?这是不是Oracle9Dialect的一个bug呢?!
哪位高手能给我详析,感激不尽!!!!
posted on 2005-12-30 09:41
南一郎 阅读(1337)
评论(0) 编辑 收藏