hibernate中DB2数据库的dialect分页问题

qiang

以前的一个项目用到db2, hibernate, 以为hibernate已经帮我们做好分页的一切事情,只要用就行了, 代码大概如下:

/** *//**
* 设置分页, pageNo或pageList<=0时返回所有记录
*
* @param pageNo
* 页数, 从1开始, <=0时返回所有记录
* @param pageList
* 每页记录数, <=0时返回所有记录
* @param query
*/
public static void pagination( int pageNo, int pageList, Query query )
{
if ( pageList > 0 && pageNo > 0 )
{
query.setMaxResults( pageList );
int beginIndex = (pageNo - 1) * pageList;
query.setFirstResult( beginIndex );
}
}

/** *//**
* 设置分页, pageNo或pageList<=0时返回所有记录
*
* @param pageNo
* 页数, 从1开始, <=0时返回所有记录
* @param pageList
* 每页记录数, <=0时返回所有记录
* @param criteria
*/
public static void pagination( int pageNo, int pageList, Criteria criteria )
{
if ( pageList > 0 && pageNo > 0 )
{
criteria.setMaxResults( pageList );
int beginIndex = (pageNo - 1) * pageList;
criteria.setFirstResult( beginIndex );
}
}
一开始, 在hibernate.properties的配置:
hibernate.dialect org.hibernate.dialect.DB2Dialect
hibernate.connection.driver_class COM.ibm.db2.jdbc.net.DB2Driver
一运行就抛出异常, 改成:
hibernate.dialect org.hibernate.dialect.DB2400Dialect
hibernate.connection.driver_class COM.ibm.db2.jdbc.net.DB2Driver
居然ok了, 大家都很高兴, 以为什么都解决了, 也运行了一段时间,感觉有时候性能很差.
后来, 在有分页的地方一页一页的点下去, 点到40页左右(每页15条记录), 页面就卡住了, 检查生成的sql语句, 发现是这样的sql:
select * from T fetch first 600 rows only
我想fetch first 600 rows only大概就是页面卡住的原因了

再看org.hibernate.dialect.DB2Dialect的源码, 发现数据库抛出异常是因为:
1. DB2Dialect生成的sql有两个order by, 应该只保留over()里面的order by
2. 当sql含有select distinct的时候, 要替换表别名为row_, 替换字段名为hibernate生成的字段名别名

把org.hibernate.dialect.DB2Dialect进行修改后, 目前运行还没有发现什么问题.

db2的版本好像7.x, 是不是版本太低, 或jdbc的版本问题呢, 也许hibernate的org.hibernate.dialect.DB2Dialect是没有问题的, 呵呵.
各位老大有没有这方面的经验, 有更好的解决方法请回复, 非常感谢!

修改的DB2Dialect代码如下:

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.log4j.Logger;
import org.hibernate.Hibernate;
import org.hibernate.cfg.Environment;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.function.AnsiTrimEmulationFunction;

/** *//**
* An SQL dialect for DB2.
*
* @author Gavin King
*/
public class DB2Dialect
extends Dialect
{
private static final Logger log = Logger.getLogger( DB2Dialect.class );

public DB2Dialect()
{
super();
registerColumnType( Types.BIT, "smallint" );
registerColumnType( Types.BIGINT, "bigint" );
registerColumnType( Types.SMALLINT, "smallint" );
registerColumnType( Types.TINYINT, "smallint" );
registerColumnType( Types.INTEGER, "integer" );
registerColumnType( Types.CHAR, "char(1)" );
registerColumnType( Types.VARCHAR, "varchar($l)" );
registerColumnType( Types.FLOAT, "float" );
registerColumnType( Types.DOUBLE, "double" );
registerColumnType( Types.DATE, "date" );
registerColumnType( Types.TIME, "time" );
registerColumnType( Types.TIMESTAMP, "timestamp" );
registerColumnType( Types.VARBINARY, "varchar($l) for bit data" );
registerColumnType( Types.NUMERIC, "numeric($p,$s)" );
registerColumnType( Types.BLOB, "blob($l)" );
registerColumnType( Types.CLOB, "clob($l)" );

registerFunction( "abs", new StandardSQLFunction( "abs" ) );
registerFunction( "absval", new StandardSQLFunction( "absval" ) );
registerFunction( "sign", new StandardSQLFunction( "sign", Hibernate.INTEGER ) );

registerFunction( "ceiling", new StandardSQLFunction( "ceiling" ) );
registerFunction( "ceil", new StandardSQLFunction( "ceil" ) );
registerFunction( "floor", new StandardSQLFunction( "floor" ) );
registerFunction( "round", new StandardSQLFunction( "round" ) );

registerFunction( "acos", new StandardSQLFunction( "acos", Hibernate.DOUBLE ) );
registerFunction( "asin", new StandardSQLFunction( "asin", Hibernate.DOUBLE ) );
registerFunction( "atan", new StandardSQLFunction( "atan", Hibernate.DOUBLE ) );
registerFunction( "cos", new StandardSQLFunction( "cos", Hibernate.DOUBLE ) );
registerFunction( "cot", new StandardSQLFunction( "cot", Hibernate.DOUBLE ) );
registerFunction( "degrees", new StandardSQLFunction( "degrees", Hibernate.DOUBLE ) );
registerFunction( "exp", new StandardSQLFunction( "exp", Hibernate.DOUBLE ) );
registerFunction( "float", new StandardSQLFunction( "float", Hibernate.DOUBLE ) );
registerFunction( "hex", new StandardSQLFunction( "hex", Hibernate.STRING ) );
registerFunction( "ln", new StandardSQLFunction( "ln", Hibernate.DOUBLE ) );
registerFunction( "log", new StandardSQLFunction( "log", Hibernate.DOUBLE ) );
registerFunction( "log10", new StandardSQLFunction( "log10", Hibernate.DOUBLE ) );
registerFunction( "radians", new StandardSQLFunction( "radians", Hibernate.DOUBLE ) );
registerFunction( "rand", new NoArgSQLFunction( "rand", Hibernate.DOUBLE ) );
registerFunction( "sin", new StandardSQLFunction( "sin", Hibernate.DOUBLE ) );
registerFunction( "soundex", new StandardSQLFunction( "soundex", Hibernate.STRING ) );
registerFunction( "sqrt", new StandardSQLFunction( "sqrt", Hibernate.DOUBLE ) );
registerFunction( "stddev", new StandardSQLFunction( "stddev", Hibernate.DOUBLE ) );
registerFunction( "tan", new StandardSQLFunction( "tan", Hibernate.DOUBLE ) );
registerFunction( "variance", new StandardSQLFunction( "variance", Hibernate.DOUBLE ) );

registerFunction( "julian_day", new StandardSQLFunction( "julian_day", Hibernate.INTEGER ) );
registerFunction( "microsecond", new StandardSQLFunction( "microsecond", Hibernate.INTEGER ) );
registerFunction( "midnight_seconds", new StandardSQLFunction( "midnight_seconds", Hibernate.INTEGER ) );
registerFunction( "minute", new StandardSQLFunction( "minute", Hibernate.INTEGER ) );
registerFunction( "month", new StandardSQLFunction( "month", Hibernate.INTEGER ) );
registerFunction( "monthname", new StandardSQLFunction( "monthname", Hibernate.STRING ) );
registerFunction( "quarter", new StandardSQLFunction( "quarter", Hibernate.INTEGER ) );
registerFunction( "hour", new StandardSQLFunction( "hour", Hibernate.INTEGER ) );
registerFunction( "second", new StandardSQLFunction( "second", Hibernate.INTEGER ) );
registerFunction( "current_date", new NoArgSQLFunction( "current date", Hibernate.DATE, false ) );
registerFunction( "date", new StandardSQLFunction( "date", Hibernate.DATE ) );
registerFunction( "day", new StandardSQLFunction( "day", Hibernate.INTEGER ) );
registerFunction( "dayname", new StandardSQLFunction( "dayname", Hibernate.STRING ) );
registerFunction( "dayofweek", new StandardSQLFunction( "dayofweek", Hibernate.INTEGER ) );
registerFunction( "dayofweek_iso", new StandardSQLFunction( "dayofweek_iso", Hibernate.INTEGER ) );
registerFunction( "dayofyear", new StandardSQLFunction( "dayofyear", Hibernate.INTEGER ) );
registerFunction( "days", new StandardSQLFunction( "days", Hibernate.LONG ) );
registerFunction( "current_time", new NoArgSQLFunction( "current time", Hibernate.TIME, false ) );
registerFunction( "time", new StandardSQLFunction( "time", Hibernate.TIME ) );
registerFunction( "current_timestamp", new NoArgSQLFunction( "current timestamp", Hibernate.TIMESTAMP, false ) );
registerFunction( "timestamp", new StandardSQLFunction( "timestamp", Hibernate.TIMESTAMP ) );
registerFunction( "timestamp_iso", new StandardSQLFunction( "timestamp_iso", Hibernate.TIMESTAMP ) );
registerFunction( "week", new StandardSQLFunction( "week", Hibernate.INTEGER ) );
registerFunction( "week_iso", new StandardSQLFunction( "week_iso", Hibernate.INTEGER ) );
registerFunction( "year", new StandardSQLFunction( "year", Hibernate.INTEGER ) );

registerFunction( "double", new StandardSQLFunction( "double", Hibernate.DOUBLE ) );
registerFunction( "varchar", new StandardSQLFunction( "varchar", Hibernate.STRING ) );
registerFunction( "real", new StandardSQLFunction( "real", Hibernate.FLOAT ) );
registerFunction( "bigint", new StandardSQLFunction( "bigint", Hibernate.LONG ) );
registerFunction( "char", new StandardSQLFunction( "char", Hibernate.CHARACTER ) );
registerFunction( "integer", new StandardSQLFunction( "integer", Hibernate.INTEGER ) );
registerFunction( "smallint", new StandardSQLFunction( "smallint", Hibernate.SHORT ) );

registerFunction( "digits", new StandardSQLFunction( "digits", Hibernate.STRING ) );
registerFunction( "chr", new StandardSQLFunction( "chr", Hibernate.CHARACTER ) );
registerFunction( "upper", new StandardSQLFunction( "upper" ) );
registerFunction( "lower", new StandardSQLFunction( "lower" ) );
registerFunction( "ucase", new StandardSQLFunction( "ucase" ) );
registerFunction( "lcase", new StandardSQLFunction( "lcase" ) );
registerFunction( "length", new StandardSQLFunction( "length", Hibernate.LONG ) );
registerFunction( "ltrim", new StandardSQLFunction( "ltrim" ) );
registerFunction( "rtrim", new StandardSQLFunction( "rtrim" ) );
registerFunction( "substr", new StandardSQLFunction( "substr", Hibernate.STRING ) );
registerFunction( "posstr", new StandardSQLFunction( "posstr", Hibernate.INTEGER ) );

registerFunction( "substring", new StandardSQLFunction( "substr", Hibernate.STRING ) );
registerFunction( "bit_length", new SQLFunctionTemplate( Hibernate.INTEGER, "length(?1)*8" ) );
registerFunction( "trim", new AnsiTrimEmulationFunction() );
registerFunction( "concat", new VarArgsSQLFunction( Hibernate.STRING, "", "||", "" ) );
registerFunction( "str", new SQLFunctionTemplate( Hibernate.STRING, "rtrim(char(?1))" ) );

registerKeyword( "current" );
registerKeyword( "date" );
registerKeyword( "time" );
registerKeyword( "timestamp" );
registerKeyword( "fetch" );
registerKeyword( "first" );
registerKeyword( "rows" );
registerKeyword( "only" );

getDefaultProperties().setProperty( Environment.STATEMENT_BATCH_SIZE, NO_BATCH );
}

public String getLowercaseFunction()
{
return "lcase";
}

public String getAddColumnString()
{
return "add column";
}

public boolean dropConstraints()
{
return false;
}

public boolean supportsIdentityColumns()
{
return true;
}

public String getIdentitySelectString()
{
return "values identity_val_local()";
}

public String getIdentityColumnString()
{
return "generated by default as identity"; // not null (start with 1)
// is implicit
}

public String getIdentityInsertString()
{
return "default";
}

public String getSequenceNextValString( String sequenceName )
{
return "values nextval for " + sequenceName;
}

public String getCreateSequenceString( String sequenceName )
{
return "create sequence " + sequenceName;
}

public String getDropSequenceString( String sequenceName )
{
return "drop sequence " + sequenceName + " restrict";
}

public boolean supportsSequences()
{
return true;
}

public String getQuerySequencesString()
{
return "select seqname from sysibm.syssequences";
}

public boolean supportsLimit()
{
return true;
}

/**//*
* public String getLimitString(String sql, boolean hasOffset) { StringBuffer
* rownumber = new StringBuffer(50) .append(" rownumber() over("); int
* orderByIndex = sql.toLowerCase().indexOf("order by"); if (orderByIndex>0)
* rownumber.append( sql.substring(orderByIndex) ); rownumber.append(") as
* row_,"); StringBuffer pagingSelect = new StringBuffer( sql.length()+100 )
* .append("select * from ( ") .append(sql) .insert(
* getAfterSelectInsertPoint(sql)+16, rownumber.toString() ) .append(" ) as
* temp_ where row_ "); if (hasOffset) { pagingSelect.append("between ?+1 and
* ?"); } else { pagingSelect.append("<= ?"); } return
* pagingSelect.toString(); }
*/

/** *//**
* Render the <tt>rownumber() over ( . ) as rownumber_,</tt> bit, that
* goes in the select list
*/
private String getRowNumber( String sql )
{
StringBuffer rownumber = new StringBuffer( 50 )
.append( "rownumber() over(" );

int orderByIndex = sql.toLowerCase().indexOf( "order by" );

if ( orderByIndex > 0 )
{
String orderBy = sql.substring( orderByIndex ); //order by bid0_.bid_id desc, item.item_id asc
if ( hasDistinct( sql ) )
{
for ( int fromIndex = 0; ; )
{// 替换 表别名 为 row_
int dotIx = orderBy.indexOf( ".", fromIndex );
if ( dotIx == -1 ) break;
int spaceIx = orderBy.substring( fromIndex, dotIx ).lastIndexOf( " " );
String tableAlias = orderBy.substring( spaceIx + 1, dotIx );
orderBy = orderBy.replaceAll( tableAlias, "row_" );
fromIndex = dotIx+1;
}
for ( int fromIndex = 0; ; )
{// 替换 字段名 为 hibernate生成的 字段名别名
int dotIx = orderBy.indexOf( ".", fromIndex );
if ( dotIx == -1 ) break;
int first1 = orderBy.indexOf( " ", dotIx );//第一个空格
int first2 = orderBy.indexOf( ",", dotIx );//第一个逗号
int endIndex = getEndIndex( first1, first2 );
String col = null;
if ( endIndex == -1 )
col = orderBy.substring( dotIx+1 );
else
col = orderBy.substring( dotIx+1, endIndex );

//bid0_.bid_id as bid1_4_,
String beforeStr = "." + col + " as ";
int pos = sql.indexOf( beforeStr );
if ( pos > -1 )
{
pos += beforeStr.length();
first1 = sql.indexOf( " ", pos );//第一个空格
first2 = sql.indexOf( ",", pos );//第一个逗号
endIndex = getEndIndex( first1, first2 );
String colAlias = null;
if ( endIndex == -1 )
colAlias = sql.substring( pos );
else
colAlias = sql.substring( pos, endIndex );
orderBy = orderBy.replaceAll( col, colAlias );
}
fromIndex = dotIx+1;
}
rownumber.append( orderBy );
}
else
rownumber.append( orderBy );
}

rownumber.append( ") as rownumber_," );

return rownumber.toString();
}

private int getEndIndex( int first1, int first2 )
{
if ( first1 == -1 && first2 == -1 )
return -1;
else if ( first1 > -1 && first2 > -1 )
return Math.min( first1, first2 );
else
return first1 + first2 + 1;
}

public String getLimitString( String sql, boolean hasOffset )
{
if ( log.isDebugEnabled() )
log.debug( "sql = " + sql );

int startOfSelect = sql.toLowerCase().indexOf( "select" );

StringBuffer pagingSelect = new StringBuffer( sql.length() + 100 ).append(
sql.substring( 0, startOfSelect ) ) // add the comment
.append( "select * from ( select " ) // nest the main query in an outer
// select
.append( getRowNumber( sql ) ); // add the rownnumber bit into the outer
// query select list

int orderByIndex = sql.toLowerCase().indexOf( "order by" );
if ( hasDistinct( sql ) )
{
pagingSelect.append( " row_.* from ( " ); // add another (inner) nested
// select

// add the main query
if ( orderByIndex > 0 ) // whithout ordey by
pagingSelect.append( sql.substring( startOfSelect, orderByIndex ) );
else
pagingSelect.append( sql.substring( startOfSelect ) );

pagingSelect.append( " ) as row_" ); // close off the inner nested select
}
else
{// add the main query
if ( orderByIndex > 0 ) // whithout ordey by
pagingSelect.append( sql.substring( startOfSelect + 6, orderByIndex ) );
else
pagingSelect.append( sql.substring( startOfSelect + 6 ) );
}

pagingSelect.append( " ) as temp_ where rownumber_ " );

// add the restriction to the outer select
if ( hasOffset )
{
pagingSelect.append( "between ?+1 and ?" );
}
else
{
pagingSelect.append( "<= ?" );
}

if ( log.isDebugEnabled() )
log.debug( "pagingSelectSQL = " + pagingSelect.toString() );

return pagingSelect.toString();
}

private static boolean hasDistinct( String sql )
{
return sql.toLowerCase().indexOf( "select distinct" ) >= 0;
}

public String getForUpdateString()
{
return " for read only with rs";
}

public boolean useMaxForLimit()
{
return true;
}

public boolean supportsOuterJoinForUpdate()
{
return false;
}

public boolean supportsNotNullUnique()
{
return false;
}

public String getSelectClauseNullString( int sqlType )
{
String literal;
switch ( sqlType )
{
case Types.VARCHAR:
case Types.CHAR:
literal = "'x'";
break;
case Types.DATE:
literal = "'2000-1-1'";
break;
case Types.TIMESTAMP:
literal = "'2000-1-1 00:00:00'";
break;
case Types.TIME:
literal = "'00:00:00'";
break;
default:
literal = "0";
}
return "nullif(" + literal + ',' + literal + ')';
}

public static void main( String[] args )
{
System.out.println( new DB2Dialect().getLimitString(
"/*foo*/ select * from foos", true ) );
System.out.println( new DB2Dialect().getLimitString(
"/*foo*/ select distinct * from foos", true ) );
System.out.println( new DB2Dialect().getLimitString(
"/*foo*/ select * from foos foo order by foo.bar, foo.baz", true ) );
System.out.println( new DB2Dialect().getLimitString(
"/*foo*/ select distinct * from foos foo order by foo.bar, foo.baz",
true ) );
String sql = "select distinct bid0_.bid_id as bid1_4_, bid0_.downloadFilePath as download2_4_, bid0_.agency as agency4_, bid0_.updateTime as updateTime4_, bid0_.effectTime as effectTime4_, bid0_.explainText as explainT6_4_, bid0_.name as name4_, bid0_.people as people4_, bid0_.state as state4_, bid0_.report as report4_, bid0_.bid_project_id as bid11_4_, bid0_.company_account_id as company12_4_, bid0_.area_id as area13_4_ from bid bid0_ inner join bid_allow_member allowmembe1_ on bid0_.bid_id=allowmembe1_.bid_id where allowmembe1_.department_id=1381 and bid0_.area_id=5 and bid0_.state>0 order by bid0_.bid_id desc";
System.out.println( new DB2Dialect().getLimitString( sql, true ) );
}

public boolean supportsUnionAll()
{
return true;
}

public int registerResultSetOutParameter( CallableStatement statement, int col )
throws SQLException
{
return col;
}

public ResultSet getResultSet( CallableStatement ps )
throws SQLException
{
boolean isResultSet = ps.execute();
// This assumes you will want to ignore any update counts
while ( !isResultSet && ps.getUpdateCount() != -1 )
{
isResultSet = ps.getMoreResults();
}
ResultSet rs = ps.getResultSet();
// You may still have other ResultSets or update counts left to process here
// but you can't do it now or the ResultSet you just got will be closed
return rs;
}

public boolean supportsCommentOn()
{
return true;
}

public boolean supportsTemporaryTables()
{
return true;
}

public String getCreateTemporaryTableString()
{
return "declare global temporary table";
}

public String getCreateTemporaryTablePostfix()
{
return "not logged";
}

public String generateTemporaryTableName( String baseTableName )
{
return "session." + super.generateTemporaryTableName( baseTableName );
}

public boolean supportsCurrentTimestampSelection()
{
return true;
}

public String getCurrentTimestampSelectString()
{
return "values current timestamp";
}

public boolean isCurrentTimestampSelectStringCallable()
{
return false;
}

public boolean supportsParametersInInsertSelect()
{
// DB2 known to not support parameters within the select
// clause of an SQL INSERT SELECT statement
return false;
}

public String getCurrentTimestampSQLFunctionName()
{
return "sysdate";
}
}

wwxx

进来看看 学习学习