kelefa  
大千世界中，唯一缺乏的就是人类的注意力。
日历
<2010年9月>
日一二三四五六
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789
统计
  • 随笔 - 11
  • 文章 - 0
  • 评论 - 28
  • 引用 - 0

导航

常用链接

留言簿(2)

随笔分类

随笔档案

搜索

  •  

最新评论

阅读排行榜

评论排行榜

 
hibernate中DB2数据库的dialect分页问题
不知道是自己对hibernate不了解,用的不对, 还是db2版本问题, 以前的一个项目用到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";
    }
}
posted on 2006-12-27 11:17 杨杰荣 阅读(3890) 评论(3)  编辑  收藏 所属分类: webwork,hibernate
评论:
  • # re: hibernate中DB2数据库的dialect分页问题  s Posted @ 2006-12-27 14:25
    fdsfsdf  回复  更多评论   

  • # re: hibernate中DB2数据库的dialect分页问题  Kevi Chen Posted @ 2007-08-27 11:35
    good! DB2 V8.2 依旧， DB2Dialect 有问题  回复  更多评论   

  • # re: hibernate中DB2数据库的dialect分页问题  Joice Posted @ 2010-09-07 22:00
    不幸被你言中，DB2 V7.X 已出来10年有余，我还在为前辈的bug而焦头烂额！！！  回复  更多评论   


只有注册用户登录后才能发表评论。
网站导航:
 
 
Copyright © 杨杰荣 Powered by: 博客园 模板提供：沪江博客