iBatis分页

最新推荐文章于 2021-02-06 20:17:49 发布

qiuyabing

最新推荐文章于 2021-02-06 20:17:49 发布

阅读量123

点赞数

分类专栏： java 文章标签： iBATIS SQL Web Spring Apache

本文链接：https://blog.csdn.net/qiuyabing/article/details/83940275

版权

java 专栏收录该内容

130 篇文章 0 订阅

订阅专栏

iBatis默认的分页是采用游标滚动的方式来实现的，这种方式在大数据量的情况下便会OOM了，因此一般都采用手写分页SQL语句使用数据库物理分页方式实现，参考了网上很多网友所写的如何实现像hibernate一样使用方言的方式来实现分页功能，基本上千篇一律都是继承com.ibatis.sqlmap.engine.execution.SqlExecutor类然后在spring中进行注入等等，操作复杂编码甚多，方法不可取。
    另外还有些是修改iBatis的jar包来实现，本人觉得这种方法更不可取。
    基于网友们的思想，自己实现了另一种方法，不用修改源码，不用在spring中做任何配置即可实现物理分页功能：
条件
    1、JVM类的加载是通过Class.forName(String cls)来实现，根据这个原理可以自己写一个与com.ibatis.sqlmap.engine.execution.SqlExecutor同名类；
    2、java web类的加载顺序是：首先是web容器的相关类与jar包，然后是web工程下面WEB-INF/classes/下的所有类，最后才是WEB-INF/lib下的所有jar包；
    有了以上的先决条件就好办了，可以在你的项目src目录下建包com.ibatis.sqlmap.engine.execution，然后在此包下建类SqlExecutor，然后把iBatis包下的这个类的源码复制进来后做小小改动，原来的executeQuery方法改成私有、换名，换成什么名称随便，然后新建一个公有的executeQuery方法，分页功能就在这个方法体内实现；
这样一来，web容器首会找到WEB-INF/classes下的com.ibatis.sqlmap.engine.execution.SqlExecutor这个类，因而会忽略掉在ibatis包中的这个类，即实现了自定义的分页功能，又不用去破坏ibatis的包；
    还有一点，也可以将自定义的这个类打成jar包放到lib中去，不过这时就要注意了，jar包的名称一定要在ibatis包的名称之前，也就是说ibatis-2.3.4.726.jar，那么这个jar就可以写成ibatis-2.3.4.725.jar，或者字母在ibatis这几个字母之前，这样才能正确加载自己写的那个类。
贴上代码：
SqlExecutor.java

Java代码

/*
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package com.ibatis.sqlmap.engine.execution;
import java.sql.BatchUpdateException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.ibatis.sqlmap.engine.impl.SqlMapClientImpl;
import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;
import com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap;
import com.ibatis.sqlmap.engine.mapping.parameter.ParameterMapping;
import com.ibatis.sqlmap.engine.mapping.result.ResultMap;
import com.ibatis.sqlmap.engine.mapping.result.ResultObjectFactoryUtil;
import com.ibatis.sqlmap.engine.mapping.statement.DefaultRowHandler;
import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.ErrorContext;
import com.ibatis.sqlmap.engine.scope.SessionScope;
import com.ibatis.sqlmap.engine.scope.StatementScope;
/**
* Class responsible for executing the SQL
*/
@SuppressWarnings ("unchecked")
public class SqlExecutor {
private static final Log log = LogFactory.getLog(SqlExecutor.class);
//
// Constants
//
/**
* Constant to let us know not to skip anything
*/
public static final int NO_SKIPPED_RESULTS = 0;
/**
* Constant to let us know to include all records
*/
public static final int NO_MAXIMUM_RESULTS = -999999;
public SqlExecutor() {
log.info("Custom class 'SqlExecutor' Initialization");
}
//
// Public Methods
//
/**
* Execute an update
*
* @param statementScope
* - the request scope
* @param conn
* - the database connection
* @param sql
* - the sql statement to execute
* @param parameters
* - the parameters for the sql statement
* @return - the number of records changed
* @throws SQLException
* - if the update fails
*/
public int executeUpdate(StatementScope statementScope, Connection conn,
String sql, Object[] parameters) throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing update");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
setupResultObjectFactory(statementScope);
int rows = 0;
try {
errorContext
.setMoreInfo("Check the SQL Statement (preparation failed).");
ps = prepareStatement(statementScope.getSession(), conn, sql);
setStatementTimeout(statementScope.getStatement(), ps);
errorContext
.setMoreInfo("Check the parameters (set parameters failed).");
statementScope.getParameterMap().setParameters(statementScope, ps,
parameters);
errorContext.setMoreInfo("Check the statement (update failed).");
ps.execute();
rows = ps.getUpdateCount();
} finally {
closeStatement(statementScope.getSession(), ps);
}
return rows;
}
/**
* Adds a statement to a batch
*
* @param statementScope
* - the request scope
* @param conn
* - the database connection
* @param sql
* - the sql statement
* @param parameters
* - the parameters for the statement
* @throws SQLException
* - if the statement fails
*/
public void addBatch(StatementScope statementScope, Connection conn,
String sql, Object[] parameters) throws SQLException {
Batch batch = (Batch) statementScope.getSession().getBatch();
if (batch == null) {
batch = new Batch();
statementScope.getSession().setBatch(batch);
}
batch.addBatch(statementScope, conn, sql, parameters);
}
/**
* Execute a batch of statements
*
* @param sessionScope
* - the session scope
* @return - the number of rows impacted by the batch
* @throws SQLException
* - if a statement fails
*/
public int executeBatch(SessionScope sessionScope) throws SQLException {
int rows = 0;
Batch batch = (Batch) sessionScope.getBatch();
if (batch != null) {
try {
rows = batch.executeBatch();
} finally {
batch.cleanupBatch(sessionScope);
}
}
return rows;
}
/**
* Execute a batch of statements
*
* @param sessionScope
* - the session scope
* @return - a List of BatchResult objects (may be null if no batch has been
* initiated). There will be one BatchResult object in the list for
* each sub-batch executed
* @throws SQLException
* if a database access error occurs, or the drive does not
* support batch statements
* @throws BatchException
* if the driver throws BatchUpdateException
*/
public List executeBatchDetailed(SessionScope sessionScope)
throws SQLException, BatchException {
List answer = null;
Batch batch = (Batch) sessionScope.getBatch();
if (batch != null) {
try {
answer = batch.executeBatchDetailed();
} finally {
batch.cleanupBatch(sessionScope);
}
}
return answer;
}
/**
* Long form of the method to execute a query
*
* @param statementScope
* - the request scope
* @param conn
* - the database connection
* @param sql
* - the SQL statement to execute
* @param parameters
* - the parameters for the statement
* @param skipResults
* - the number of results to skip
* @param maxResults
* - the maximum number of results to return
* @param callback
* - the row handler for the query
* @throws SQLException
* - if the query fails
*/
//------------------------------- 分页代码重写(start) ------------------------------------//
//重写executeQuery方法，首先判断是否分页查询，分页查询先将分页SQL语句构建，然后执行iBatis默认的查询
public void executeQuery(StatementScope statementScope, Connection conn,
String sql, Object[] parameters, int skipResults, int maxResults,
RowHandlerCallback callback) throws SQLException {
//取数据库产品名称
String dbName = conn.getMetaData().getDatabaseProductName();
int len = sql.length();
//判断是否分页
if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)) {
//根据数据库产品名称取对应的分页SQL语句
sql = Dialect.getLimitString(dbName, sql, skipResults, maxResults);
//分页语句是否存在
if (sql.length() != len) {
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS;
}
}
iBatisExecuteQuery(statementScope, conn, sql, parameters, skipResults,
maxResults, callback);
}
//iBatis包中默认的executeQuery方法
private void iBatisExecuteQuery(StatementScope statementScope,
Connection conn, String sql, Object[] parameters, int skipResults,
int maxResults, RowHandlerCallback callback) throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing query");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
ResultSet rs = null;
setupResultObjectFactory(statementScope);
try {
errorContext
.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = statementScope.getStatement().getResultSetType();
if (rsType != null) {
ps = prepareStatement(statementScope.getSession(), conn, sql,
rsType);
} else {
ps = prepareStatement(statementScope.getSession(), conn, sql);
}
setStatementTimeout(statementScope.getStatement(), ps);
Integer fetchSize = statementScope.getStatement().getFetchSize();
if (fetchSize != null) {
ps.setFetchSize(fetchSize.intValue());
}
errorContext
.setMoreInfo("Check the parameters (set parameters failed).");
statementScope.getParameterMap().setParameters(statementScope, ps,
parameters);
errorContext.setMoreInfo("Check the statement (query failed).");
ps.execute();
errorContext
.setMoreInfo("Check the results (failed to retrieve results).");
// Begin ResultSet Handling
rs = handleMultipleResults(ps, statementScope, skipResults,
maxResults, callback);
// End ResultSet Handling
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(statementScope.getSession(), ps);
}
}
}
//-------------------- 分页代码重写(end) -------------------------------------//
/**
* Execute a stored procedure that updates data
*
* @param statementScope
* - the request scope
* @param conn
* - the database connection
* @param sql
* - the SQL to call the procedure
* @param parameters
* - the parameters for the procedure
* @return - the rows impacted by the procedure
* @throws SQLException
* - if the procedure fails
*/
public int executeUpdateProcedure(StatementScope statementScope,
Connection conn, String sql, Object[] parameters)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing update procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
setupResultObjectFactory(statementScope);
int rows = 0;
try {
errorContext
.setMoreInfo("Check the SQL Statement (preparation failed).");
cs = prepareCall(statementScope.getSession(), conn, sql);
setStatementTimeout(statementScope.getStatement(), cs);
ParameterMap parameterMap = statementScope.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext
.setMoreInfo("Check the output parameters (register output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext
.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(statementScope, cs, parameters);
errorContext
.setMoreInfo("Check the statement (update procedure failed).");
cs.execute();
rows = cs.getUpdateCount();
errorContext
.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(statementScope, cs, mappings, parameters,
null);
} finally {
closeStatement(statementScope.getSession(), cs);
}
return rows;
}
/**
* Execute a stored procedure
*
* @param statementScope
* - the request scope
* @param conn
* - the database connection
* @param sql
* - the sql to call the procedure
* @param parameters
* - the parameters for the procedure
* @param skipResults
* - the number of results to skip
* @param maxResults
* - the maximum number of results to return
* @param callback
* - a row handler for processing the results
* @throws SQLException
* - if the procedure fails
*/
public void executeQueryProcedure(StatementScope statementScope,
Connection conn, String sql, Object[] parameters, int skipResults,
int maxResults, RowHandlerCallback callback) throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
ResultSet rs = null;
setupResultObjectFactory(statementScope);
try {
errorContext
.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = statementScope.getStatement().getResultSetType();
if (rsType != null) {
cs = prepareCall(statementScope.getSession(), conn, sql, rsType);
} else {
cs = prepareCall(statementScope.getSession(), conn, sql);
}
setStatementTimeout(statementScope.getStatement(), cs);
Integer fetchSize = statementScope.getStatement().getFetchSize();
if (fetchSize != null) {
cs.setFetchSize(fetchSize.intValue());
}
ParameterMap parameterMap = statementScope.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext
.setMoreInfo("Check the output parameters (register output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext
.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(statementScope, cs, parameters);
errorContext
.setMoreInfo("Check the statement (update procedure failed).");
cs.execute();
errorContext
.setMoreInfo("Check the results (failed to retrieve results).");
// Begin ResultSet Handling
rs = handleMultipleResults(cs, statementScope, skipResults,
maxResults, callback);
// End ResultSet Handling
errorContext
.setMoreInfo("Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(statementScope, cs, mappings, parameters,
callback);
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(statementScope.getSession(), cs);
}
}
}
private ResultSet handleMultipleResults(PreparedStatement ps,
StatementScope statementScope, int skipResults, int maxResults,
RowHandlerCallback callback) throws SQLException {
ResultSet rs;
rs = getFirstResultSet(statementScope, ps);
if (rs != null) {
handleResults(statementScope, rs, skipResults, maxResults, callback);
}
// Multiple ResultSet handling
if (callback.getRowHandler() instanceof DefaultRowHandler) {
MappedStatement statement = statementScope.getStatement();
DefaultRowHandler defaultRowHandler = ((DefaultRowHandler) callback
.getRowHandler());
if (statement.hasMultipleResultMaps()) {
List multipleResults = new ArrayList();
multipleResults.add(defaultRowHandler.getList());
ResultMap[] resultMaps = statement.getAdditionalResultMaps();
int i = 0;
while (moveToNextResultsSafely(statementScope, ps)) {
if (i >= resultMaps.length)
break;
ResultMap rm = resultMaps[i];
statementScope.setResultMap(rm);
rs = ps.getResultSet();
DefaultRowHandler rh = new DefaultRowHandler();
handleResults(statementScope, rs, skipResults, maxResults,
new RowHandlerCallback(rm, null, rh));
multipleResults.add(rh.getList());
i++;
}
defaultRowHandler.setList(multipleResults);
statementScope.setResultMap(statement.getResultMap());
} else {
while (moveToNextResultsSafely(statementScope, ps))
;
}
}
// End additional ResultSet handling
return rs;
}
private ResultSet getFirstResultSet(StatementScope scope, Statement stmt)
throws SQLException {
ResultSet rs = null;
boolean hasMoreResults = true;
while (hasMoreResults) {
rs = stmt.getResultSet();
if (rs != null) {
break;

qiuyabing

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
iBatis分页

iBatis默认的分页是采用游标滚动的方式来实现的，这种方式在大数据量的情况下便会OOM了，因此一般都采用手写分页SQL语句使用数据库物理分页方式实现，参考了网上很多网友所写的如何实现像hibernate一样使用方言的方式来实现分页功能，基本上千篇一律都是继承com.ibatis.sqlmap.engine.execution.SqlExecutor类然后在spring中进行注入等等，操作复杂编码...
复制链接

扫一扫