环境 是 hibernate3.2.5 + oracle10g
hibernate 分页查询 的第一页 和第二页之后的sql 不一样
第一页时是:
select * from (myquery) where rownum<?
第二页及之后页时是:
select * from (select row_.*,rownum rownum_ from (myquery) row_) where rownum_<=? and rownum_>?
由于sql 不相同 可能造成数据库执行计划不同,然后导致数据库记录第一页 和第二页有重复记录
SQL> explain plan for select *
2 from (select b.PRODUCT_ID as col_0_0_,
3 b.PRD_CODE as col_1_0_,
4 b.PRD_NAME as col_2_0_,
5 b.PRD_FINENESS as col_3_0_,
6 a.TRL_SALE_QTY as col_4_0_,
7 a.TRL_REPURCHASE_QTY as col_5_0_,
8 a.TRL_DES_QTY as col_6_0_,
9 a.TRL_ID as col_7_0_,
10 b.PRD_WEIGHT as col_8_0_
11 from TESTA a, TESTB b
12 where b.PRODUCT_ID = a.PRD_PRODUCTID
13 and b.ISDELETE = '1'
14 and b.SUP_ID = '100000'
15 and a.TRS_ID = '6' )
16 where rownum <= 10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 913936710
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 700 | 11 (10)| 00
|* 1 | COUNT STOPKEY | | | | |
|* 2 | HASH JOIN | | 10 | 700 | 11 (10)| 00
|* 3 | TABLE ACCESS FULL| TESTB | 25 | 1200 | 5 (0)| 00
|* 4 | TABLE ACCESS FULL| TESTA | 17 | 374 | 5 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
2 - access("b"."PRODUCT_ID"="a"."PRD_PRODUCTID")
3 - filter("b"."SUP_ID"=100000 AND "b"."ISDELETE"=1)
4 - filter("a"."TRS_ID"=6)
19 rows selected
第二页
SQL> explain plan for select *
2 from (select row_.*, rownum rownum_
3 from (select b.PRODUCT_ID as col_0_0_,
4 b.PRD_CODE as col_1_0_,
5 b.PRD_NAME as col_2_0_,
6 b.PRD_FINENESS as col_3_0_,
7 a.TRL_SALE_QTY as col_4_0_,
8 a.TRL_REPURCHASE_QTY as col_5_0_,
9 a.TRL_DES_QTY as col_6_0_,
10 a.TRL_ID as col_7_0_,
11 b.PRD_WEIGHT as col_8_0_
12 from TESTA a,
13 TESTB b
14 where b.PRODUCT_ID = a.PRD_PRODUCTID
15 and b.ISDELETE = '1'
16 and b.SUP_ID = '100000'
17 and a.TRS_ID = '6') row_)
18 where rownum_ <= 20
19 and rownum_ >10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1793125574
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 27659 | 11 (10)| 0
|* 1 | VIEW | | 17 | 27659 | 11 (10)| 0
| 2 | COUNT | | | | |
|* 3 | HASH JOIN | | 17 | 1190 | 11 (10)| 0
|* 4 | TABLE ACCESS FULL| TESTA | 17 | 374 | 5 (0)| 0
|* 5 | TABLE ACCESS FULL| TESTB | 25 | 1200 | 5 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWNUM_">10 AND "ROWNUM_"<=20)
3 - access("b"."PRODUCT_ID"="a"."PRD_PRODUCTID")
4 - filter("a"."TRS_ID"=6)
5 - filter("b"."SUP_ID"=100000 AND "b"."ISDELETE"=1)
20 rows selected
第一页
SQL> explain plan for select *
2 from (select b.PRODUCT_ID as col_0_0_,
3 b.PRD_CODE as col_1_0_,
4 b.PRD_NAME as col_2_0_,
5 b.PRD_FINENESS as col_3_0_,
6 a.TRL_SALE_QTY as col_4_0_,
7 a.TRL_REPURCHASE_QTY as col_5_0_,
8 a.TRL_DES_QTY as col_6_0_,
9 a.TRL_ID as col_7_0_,
10 b.PRD_WEIGHT as col_8_0_
11 from TESTA a, TESTB b
12 where b.PRODUCT_ID = a.PRD_PRODUCTID
13 and b.ISDELETE = '1'
14 and b.SUP_ID = '100000'
15 and a.TRS_ID = '6' )
16 where rownum <= 10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 913936710
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 700 | 11 (10)| 00
|* 1 | COUNT STOPKEY | | | | |
|* 2 | HASH JOIN | | 10 | 700 | 11 (10)| 00
|* 3 | TABLE ACCESS FULL| TESTB | 25 | 1200 | 5 (0)| 00
|* 4 | TABLE ACCESS FULL| TESTA | 17 | 374 | 5 (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
2 - access("b"."PRODUCT_ID"="a"."PRD_PRODUCTID")
3 - filter("b"."SUP_ID"=100000 AND "b"."ISDELETE"=1)
4 - filter("a"."TRS_ID"=6)
19 rows selected
第二页
SQL> explain plan for select *
2 from (select row_.*, rownum rownum_
3 from (select b.PRODUCT_ID as col_0_0_,
4 b.PRD_CODE as col_1_0_,
5 b.PRD_NAME as col_2_0_,
6 b.PRD_FINENESS as col_3_0_,
7 a.TRL_SALE_QTY as col_4_0_,
8 a.TRL_REPURCHASE_QTY as col_5_0_,
9 a.TRL_DES_QTY as col_6_0_,
10 a.TRL_ID as col_7_0_,
11 b.PRD_WEIGHT as col_8_0_
12 from TESTA a,
13 TESTB b
14 where b.PRODUCT_ID = a.PRD_PRODUCTID
15 and b.ISDELETE = '1'
16 and b.SUP_ID = '100000'
17 and a.TRS_ID = '6') row_)
18 where rownum_ <= 20
19 and rownum_ >10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1793125574
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 27659 | 11 (10)| 0
|* 1 | VIEW | | 17 | 27659 | 11 (10)| 0
| 2 | COUNT | | | | |
|* 3 | HASH JOIN | | 17 | 1190 | 11 (10)| 0
|* 4 | TABLE ACCESS FULL| TESTA | 17 | 374 | 5 (0)| 0
|* 5 | TABLE ACCESS FULL| TESTB | 25 | 1200 | 5 (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ROWNUM_">10 AND "ROWNUM_"<=20)
3 - access("b"."PRODUCT_ID"="a"."PRD_PRODUCTID")
4 - filter("a"."TRS_ID"=6)
5 - filter("b"."SUP_ID"=100000 AND "b"."ISDELETE"=1)
20 rows selected
以上是一个 自然连接的分页查询 ,可以看到,由于hibernate 翻译的sql 不相同,造成执行计划不同,造成查询缺陷
两种解决方案:
方法1、 查询的sql中 增加 order by 唯一键
这个就不多说了 ,sql 加上 order by
方法2、 修改hibernate
a、loader.java 中 修改
private int bindLimitParameters(
final PreparedStatement statement,
final int index,
final RowSelection selection) throws SQLException {
Dialect dialect = getFactory().getDialect();
if ( !dialect.supportsVariableLimit() ) {
return 0;
}
if ( !hasMaxRows( selection ) ) {
throw new AssertionFailure( "no max results set" );
}
int firstRow = getFirstRow( selection );
int lastRow = getMaxOrLimit( selection, dialect );
boolean hasFirstRow = firstRow > 0 && dialect.supportsLimitOffset();
boolean reverse = dialect.bindLimitParametersInReverseOrder();
// if ( hasFirstRow ) { //修改点 始终设置参数
if ( true ) {
statement.setInt( index + ( reverse ? 1 : 0 ), firstRow );
}
statement.setInt( index + ( reverse || !hasFirstRow ? 0 : 1 ), lastRow );
return hasFirstRow ? 2 : 1;
}
b、自定义方言
重写
getLimitString 方法
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("select * from ( select row_.*, rownum rownum_ from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
//pagingSelect.append(" ) where rownum <= ?");修改点
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > ?");
}
else {
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > ?");
}
if ( isForUpdate ) {
pagingSelect.append( " for update" );
}
return pagingSelect.toString();
}
这样 就屏蔽了 第一页 和第二页sql 不同问题,这样同一条sql 数据库的执行计划是一样的,数据就不会出问题了