sqlserver2005的分页优化
创始人
2024-12-20 08:22:47
0次


一、hibernate分页 hibernate对MsSql的伪分页
分页是web项目中比不可少的一个功能，数据量大的时候不能全部展示必然要用到分页技术。相信大家对hibernate中的分页都不陌生：
Java代码 
public Query setMaxResults(int maxResults);  
public Query setFirstResult(int firstResult); 
Java代码 
public Query setMaxResults(int maxResults); 
public Query setFirstResult(int firstResult); 

只要调用了这两个方法并设置好参数，hibernate自动分页完全屏蔽了底层分页技术，这也是众多开发者喜欢hibernate的原因之一。
项目开发中遇到一个奇怪的问题。数据库采用的是Sql Server 2005，也设置了上面两个参数，可是每次发送到数据库端的SQL语句都是select top ....语句。即便是查询第10w条，也只有一个select top 语句，不免引起对hibernate实现sql server分页的怀疑。hibernate针对不同数据库实现的分页方法封装在对应数据库的方言里，通过getLimitString方法转化成对应数据库的分页算法。
以常见的Mysql数据库的方言MySQLDialect为例：
Java代码 
public String getLimitString(String sql, boolean hasOffset) {  
        return new StringBuffer( sql.length() + 20 )  
                .append( sql )  
                .append( hasOffset ? " limit ?, ?" : " limit ?" )  
                .toString();  
    } 
Java代码 
public String getLimitString(String sql, boolean hasOffset) { 
        return new StringBuffer( sql.length() + 20 ) 
                .append( sql ) 
                .append( hasOffset ? " limit ?, ?" : " limit ?" ) 
                .toString(); 
    } 

采用了大家熟悉的的limit进行分页。
数据库的方言Oracle9iDialect：
Java代码 
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 <= ?");  
        } 
Java代码 
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 <= ?"); 
        } 

利用Oracle的rownum 结合三层嵌套查询完成分页。这个三层是Oracle最经典高效的分页算法。
可是针对Sql Server的方言SQLServerDialect：
Java代码 
public String getLimitString(String querySelect, int offset, int limit) {  
        if ( offset > 0 ) {  
            throw new UnsupportedOperationException( "query result offset is not supported" );  
        }  
        return new StringBuffer( querySelect.length() + 8 )  
                .append( querySelect )  
                .insert( getAfterSelectInsertPoint( querySelect ), " top " + limit )  
                .toString();  
    } 
Java代码 
public String getLimitString(String querySelect, int offset, int limit) { 
        if ( offset > 0 ) { 
            throw new UnsupportedOperationException( "query result offset is not supported" ); 
        } 
        return new StringBuffer( querySelect.length() + 8 ) 
                .append( querySelect ) 
                .insert( getAfterSelectInsertPoint( querySelect ), " top " + limit ) 
                .toString(); 
    } 

揉揉眼睛、再揉揉，没错，只出现了一个top语句。这就意味着如果查询第10w页的数据，需要把前10w页数据全部提取出来。hibernate针对sql server的分页是伪分页，所以随着数据量日益增加用户抱怨系统速度慢，程序员抱怨hibernate性能低，dba抱怨开发人员sql功底太浅。
不知道hibernate开发组，出于什么目前或情况没有真正提供sql server的分页技术，那我们自己来实现。
方言类：
Java代码 
public class SQLServer2005Dialect extends SQLServerDialect {  
 
    /** 
     *  
     * 是否需要绑定limit参数? 
     *  
     * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数 
     */ 
 
    private ThreadLocal supportsVariableLimit = new ThreadLocal();  
    public SQLServer2005Dialect() {  
        registerFunction("bitand", new BitAndFunction());  
        registerFunction("bitxor", new BitXorFunction());  
        registerFunction("bitor", new BitOrFunction());  
        setSupportsVariableLimit(false);  
    }  
 
    /** 
     *  
     *

 
     * 设置是否先绑定limit参数。 
     *

 
     *  
     * @param first 
     */ 
 
    private void setSupportsVariableLimit(boolean first) {  
        this.supportsVariableLimit.set(Boolean.valueOf(first));  
    }  
 
    /** 
     *  
     *

 
     * 获取sql中select子句位置。 
     *

 
     *  
     * @param sql 
     *  
     * @return int 
     */ 
    protected static int getSqlAfterSelectInsertPoint(String sql) {  
        int selectIndex = sql.toLowerCase().indexOf("select");  
 
        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");  
 
        return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6);  
    }  
 
    public boolean supportsLimitOffset() {  
        return true;  
    }  
 
    /* 
     * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true， 
     *  
     * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值) 
     */ 
    public boolean supportsVariableLimit() {  
        return ((Boolean) this.supportsVariableLimit.get()).booleanValue();  
    }  
 
    public boolean useMaxForLimit() {  
        return true;  
    }  
    /** 
     * 首页top，以后用ROW_NUMBER 
     */ 
    public String getLimitString(String query, int offset, int limit) {  
        setSupportsVariableLimit(offset > 0);  
 
        if (offset == 0) {  
            return new StringBuffer(query.length() + 8).append(query).insert(  
                    getSqlAfterSelectInsertPoint(query), " top " + limit)  
                    .toString();  
        }  
 
        return getLimitString(query, offset > 0);  
    }  
      
    public String getLimitString(String sql, boolean hasOffset) {  
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");  
 
        if (orderByIndex <= 0) {  
            throw new UnsupportedOperationException(  
                    "must specify 'order by' statement to support limit operation with offset in sql server 2005");  
        }  
 
        String sqlOrderBy = sql.substring(orderByIndex + 8);  
 
        String sqlRemoveOrderBy = sql.substring(0, orderByIndex);  
 
        int insertPoint = getSqlAfterSelectInsertPoint(sql);  
        return new StringBuffer(sql.length() + 100)  
                .append("with tempPagination as(")  
                .append(sqlRemoveOrderBy)  
                .insert(  
                        insertPoint + 23,  
                        " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy  
                                + ") as RowNumber,")  
                .append(  
                        ") select * from tempPagination where RowNumber>?  and RowNumber<=?")  
                .toString();  
    }  
} 
Java代码 
public class SQLServer2005Dialect extends SQLServerDialect { 
 
    /**
     * 
     * 是否需要绑定limit参数?
     * 
     * 在SQL Server中使用top时不能使用参数表示top条数,而使用ROW_NUMBER()则需要提供limit参数
     */ 
 
    private ThreadLocal supportsVariableLimit = new ThreadLocal(); 
    public SQLServer2005Dialect() { 
        registerFunction("bitand", new BitAndFunction()); 
        registerFunction("bitxor", new BitXorFunction()); 
        registerFunction("bitor", new BitOrFunction()); 
        setSupportsVariableLimit(false); 
    } 
 
    /**
     * 
     *


     * 设置是否先绑定limit参数。
     *


     * 
     * @param first
     */ 
 
    private void setSupportsVariableLimit(boolean first) { 
        this.supportsVariableLimit.set(Boolean.valueOf(first)); 
    } 
 
    /**
     * 
     *


     * 获取sql中select子句位置。
     *


     * 
     * @param sql
     * 
     * @return int
     */ 
    protected static int getSqlAfterSelectInsertPoint(String sql) { 
        int selectIndex = sql.toLowerCase().indexOf("select"); 
 
        int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct"); 
 
        return selectIndex + ((selectDistinctIndex == selectIndex) ? 15 : 6); 
    } 
 
    public boolean supportsLimitOffset() { 
        return true; 
    } 
 
    /*
     * Hibernate在获得Limit String(已添加了limit子句)后，如果此方法返回true，
     * 
     * 则会添加额外的参数值(ROW_NUMBER()范围)(策略可能是这样：有offset设置两个参数值，没有设置一个参数值)
     */ 
    public boolean supportsVariableLimit() { 
        return ((Boolean) this.supportsVariableLimit.get()).booleanValue(); 
    } 
 
    public boolean useMaxForLimit() { 
        return true; 
    } 
    /**
     * 首页top，以后用ROW_NUMBER
     */ 
    public String getLimitString(String query, int offset, int limit) { 
        setSupportsVariableLimit(offset > 0); 
 
        if (offset == 0) { 
            return new StringBuffer(query.length() + 8).append(query).insert( 
                    getSqlAfterSelectInsertPoint(query), " top " + limit) 
                    .toString(); 
        } 
 
        return getLimitString(query, offset > 0); 
    } 
     
    public String getLimitString(String sql, boolean hasOffset) { 
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by"); 
 
        if (orderByIndex <= 0) { 
            throw new UnsupportedOperationException( 
                    "must specify 'order by' statement to support limit operation with offset in sql server 2005"); 
        } 
 
        String sqlOrderBy = sql.substring(orderByIndex + 8); 
 
        String sqlRemoveOrderBy = sql.substring(0, orderByIndex); 
 
        int insertPoint = getSqlAfterSelectInsertPoint(sql); 
        return new StringBuffer(sql.length() + 100) 
                .append("with tempPagination as(") 
                .append(sqlRemoveOrderBy) 
                .insert( 
                        insertPoint + 23, 
                        " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy 
                                + ") as RowNumber,") 
                .append( 
                        ") select * from tempPagination where RowNumber>?  and RowNumber<=?") 
                .toString(); 
    } 
} 

函数：
Java代码 
public class BitAndFunction implements SQLFunction {  
    public Type getReturnType(Type type, Mapping mapping) {  
        return Hibernate.INTEGER;  
    }  
 
    public boolean hasArguments() {  
        return true;  
    }  
 
    public boolean hasParenthesesIfNoArguments() {  
        return true;  
    }  
 
    public String render(List args, SessionFactoryImplementor factory)  
            throws QueryException {  
        if (args.size() != 2) {  
            throw new IllegalArgumentException(  
                    "BitAndFunction requires 2 arguments!");  
        }  
        return args.get(0).toString() + " & " + args.get(1).toString();  
    }  
} 
Java代码 
public class BitAndFunction implements SQLFunction { 
    public Type getReturnType(Type type, Mapping mapping) { 
        return Hibernate.INTEGER; 
    } 
 
    public boolean hasArguments() { 
        return true; 
    } 
 
    public boolean hasParenthesesIfNoArguments() { 
        return true; 
    } 
 
    public String render(List args, SessionFactoryImplementor factory) 
            throws QueryException { 
        if (args.size() != 2) { 
            throw new IllegalArgumentException( 
                    "BitAndFunction requires 2 arguments!"); 
        } 
        return args.get(0).toString() + " & " + args.get(1).toString(); 
    } 
} 
 
Java代码 
public class BitOrFunction implements SQLFunction {  
    public Type getReturnType(Type type, Mapping mapping) {  
        return Hibernate.INTEGER;  
    }  
    public boolean hasArguments() {  
        return true;  
    }  
    public boolean hasParenthesesIfNoArguments() {  
        return true;  
    }  
    public String render(List args, SessionFactoryImplementor factory)  
            throws QueryException {  
        if (args.size() != 2) {  
            throw new IllegalArgumentException(  
                    "BitOrFunction requires 2 arguments!");  
        }  
        return args.get(0).toString() + " | " + args.get(1).toString();  
    }  
} 
Java代码 
public class BitOrFunction implements SQLFunction { 
    public Type getReturnType(Type type, Mapping mapping) { 
        return Hibernate.INTEGER; 
    } 
    public boolean hasArguments() { 
        return true; 
    } 
    public boolean hasParenthesesIfNoArguments() { 
        return true; 
    } 
    public String render(List args, SessionFactoryImplementor factory) 
            throws QueryException { 
        if (args.size() != 2) { 
            throw new IllegalArgumentException( 
                    "BitOrFunction requires 2 arguments!"); 
        } 
        return args.get(0).toString() + " | " + args.get(1).toString(); 
    } 
} 
 
Java代码 
public Type getReturnType(Type type, Mapping mapping) {  
        return Hibernate.INTEGER;  
    }  
    public boolean hasArguments() {  
        return true;  
    }  
    public boolean hasParenthesesIfNoArguments() {  
        return true;  
    }  
    public String render(List args, SessionFactoryImplementor factory)  
            throws QueryException {  
        if (args.size() != 2) {  
            throw new IllegalArgumentException(  
                    "BitXorFunction requires 2 arguments!");  
        }  
        return args.get(0).toString() + " ^ " + args.get(1).toString();  
    } 


上一篇：怎么解析wxPython修改文本框颜色过程

下一篇：Python阶乘求和的代码怎么写

相关内容

热门资讯

空调连续工作多久需要停机休息 空调连续工作的时间取决于多个因素，包括空调的型号、功率、使用环境和维护情况等。一般来说，空调可以连续...
空调哪个符号是除湿的 空调除湿功能通常用一个水滴形状的符号表示。不同品牌和型号的空调遥控器可能会有所差异，但一般来说，除湿...
格力空调多长时间加一次氟利昂 1、一般情况下，空调对氟利昂的使用量是非常小的，所以在空调使用了四年左右的时候再考虑加也没问题。2、...
空调用多长时间需要加氟一次 一般情况下是五年。当出现以下这几种情况时，空调才需要加氟。1、空调的室内机和室外机各有两个接口，当接...
格调生活·C标思享荟：卡萨帝在... 9月12日的郑州龙湖金融岛，夜风轻拂，灯光璀璨。“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑...
品牌价值破千亿、高端份额持续领... 9月12日，“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑州站”在郑州龙湖金融岛举办。2026...
空调主板坏了修800还是换新的 空调主板坏了是修还是换新，需要考虑多个因素，以下是一些建议：1. 维修成本：如果维修费用较低，比如 ...
空调开多久需要休息多长时间 问题：空调开多久需要休息多长时间回答：1、在使用空调的过程中，一般空调开7~8个小时左右需要休息2~...
中央空调多长时间清洗一次 1、一般空调开机前清洗一次即可，空调开机中间时段再清洗一次，空调关机时清洗一次，这样比较合理。2、空...
中央空调氟利昂多久加一次 看空调制热和制冷程度，很多人的观点认为氟利昂必须一年放一次，其实这个观点是错误的，理解上面有点偏差，...