xbyang18
- 浏览: 16502 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
三里小龙：
值得借鉴，收藏下
缓存JAVA -
xbyang18：
<input type="text" ...
mysql 随机选择一条记录 -
hanxiangyu89：
怎么下载不了？？？？
学习ibatis的一些资料
对IBatis分页的改进,使ibatis支持hibernate式的物理分页
关键字: ibatis spring 分页 物理分页
公司的大部分项目都开始使用IBatis作为O/R Mapping了,但是在使用的过程中也发现了很多不方便和存在争议的地方,其中一个不方便的地方就是分页,目前的处理方式都是在sqlMap中写针对特定数据库的物理分页Sql语句,对于oracle数据库都是在分页的sql语句里面加上三层嵌套的sql语句,想了很多办法,都没能很好的避免这个问题, 无意间在javaeye上看到了《使ibatis支持hibernate式的物理分页》这篇文章,可点进去已经被删除了,幸好google了一下有很多人已经收藏了,这里转载一下,以便再找不到了.
转载地址:http://www.blogjava.net/libin2722/articles/192504.html
一直以来ibatis的分页都是通过滚动ResultSet实现的，应该算是逻辑分页吧。逻辑分页虽然能很干净地独立于特定数据库，但效率在多数情况下不及特定数据库支持的物理分页，而hibernate的分页则是直接组装sql，充分利用了特定数据库的分页机制，效率相对较高。本文讲述的就是如何在不重新编译ibatis源码的前提下，为ibatis引入hibernate式的物理分页机制。
基本思路就是找到ibatis执行sql的地方，截获sql并重新组装sql。通过分析ibatis源码知道，最终负责执行sql的类是 com.ibatis.sqlmap.engine.execution.SqlExecutor，此类没有实现任何接口，这多少有点遗憾，因为接口是相对稳定契约，非大的版本更新，接口一般是不会变的，而类就相对易变一些，所以这里的代码只能保证对当前版本（2.1.7）的ibatis有效。下面是 SqlExecutor执行查询的方法：
Java代码 1./**
2. * Long form of the method to execute a query
3. *
4. * @param request - the request scope
5. * @param conn - the database connection
6. * @param sql - the SQL statement to execute
7. * @param parameters - the parameters for the statement
8. * @param skipResults - the number of results to skip
9. * @param maxResults - the maximum number of results to return
10. * @param callback - the row handler for the query
11. *
12. * @throws SQLException - if the query fails
13. */
14. public void executeQuery(RequestScope request, Connection conn, String sql, Object[] parameters,
15. int skipResults, int maxResults, RowHandlerCallback callback)
16. throws SQLException {
17. ErrorContext errorContext = request.getErrorContext();
18. errorContext.setActivity("executing query");
19. errorContext.setObjectId(sql);
20.
21. PreparedStatement ps = null;
22. ResultSet rs = null;
23.
24. try {
25. errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
26.
27. Integer rsType = request.getStatement().getResultSetType();
28. if (rsType != null) {
29. ps = conn.prepareStatement(sql, rsType.intValue(), ResultSet.CONCUR_READ_ONLY);
30. } else {
31. ps = conn.prepareStatement(sql);
32. }
33.
34. Integer fetchSize = request.getStatement().getFetchSize();
35. if (fetchSize != null) {
36. ps.setFetchSize(fetchSize.intValue());
37. }
38.
39. errorContext.setMoreInfo("Check the parameters (set parameters failed).");
40. request.getParameterMap().setParameters(request, ps, parameters);
41.
42. errorContext.setMoreInfo("Check the statement (query failed).");
43.
44. ps.execute();
45. rs = getFirstResultSet(ps);
46.
47. if (rs != null) {
48. errorContext.setMoreInfo("Check the results (failed to retrieve results).");
49. handleResults(request, rs, skipResults, maxResults, callback);
50. }
51.
52. // clear out remaining results
53. while (ps.getMoreResults());
54.
55. } finally {
56. try {
57. closeResultSet(rs);
58. } finally {
59. closeStatement(ps);
60. }
61. }
62.
63. }
/**
* Long form of the method to execute a query
*
* @param request - 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
*/
public void executeQuery(RequestScope request, Connection conn, String sql, Object[] parameters,
int skipResults, int maxResults, RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = request.getStatement().getResultSetType();
if (rsType != null) {
ps = conn.prepareStatement(sql, rsType.intValue(), ResultSet.CONCUR_READ_ONLY);
} else {
ps = conn.prepareStatement(sql);
}
Integer fetchSize = request.getStatement().getFetchSize();
if (fetchSize != null) {
ps.setFetchSize(fetchSize.intValue());
}
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
request.getParameterMap().setParameters(request, ps, parameters);
errorContext.setMoreInfo("Check the statement (query failed).");
ps.execute();
rs = getFirstResultSet(ps);
if (rs != null) {
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
// clear out remaining results
while (ps.getMoreResults());
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(ps);
}
}
}
其中handleResults(request, rs, skipResults, maxResults, callback)一句用于处理分页，其实此时查询已经执行完毕，可以不必关心handleResults方法，但为清楚起见，下面来看看 handleResults的实现：
Java代码 1.private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
2. try {
3. request.setResultSet(rs);
4. ResultMap resultMap = request.getResultMap();
5. if (resultMap != null) {
6. // Skip Results
7. if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
8. if (skipResults > 0) {
9. rs.absolute(skipResults);
10. }
11. } else {
12. for (int i = 0; i < skipResults; i++) {
13. if (!rs.next()) {
14. break;
15. }
16. }
17. }
18.
19. // Get Results
20. int resultsFetched = 0;
21. while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {
22. Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs);
23. callback.handleResultObject(request, columnValues, rs);
24. resultsFetched++;
25. }
26. }
27. } finally {
28. request.setResultSet(null);
29. }
30. }
private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
try {
request.setResultSet(rs);
ResultMap resultMap = request.getResultMap();
if (resultMap != null) {
// Skip Results
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (skipResults > 0) {
rs.absolute(skipResults);
}
} else {
for (int i = 0; i < skipResults; i++) {
if (!rs.next()) {
break;
}
}
}
// Get Results
int resultsFetched = 0;
while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {
Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs);
callback.handleResultObject(request, columnValues, rs);
resultsFetched++;
}
}
} finally {
request.setResultSet(null);
}
}
此处优先使用的是ResultSet的absolute方法定位记录，是否支持absolute取决于具体数据库驱动，但一般当前版本的数据库都支持该方法，如果不支持则逐条跳过前面的记录。由此可以看出如果数据库支持absolute，则ibatis内置的分页策略与特定数据库的物理分页效率差距就在于物理分页查询与不分页查询在数据库中的执行效率的差距了。因为查询执行后读取数据前数据库并未把结果全部返回到内存，所以本身在存储占用上应该差距不大，如果都使用索引，估计执行速度也差不太多。
继续我们的话题。其实只要在executeQuery执行前组装sql，然后将其传给 executeQuery，并告诉handleResults我们不需要逻辑分页即可。拦截executeQuery可以采用aop动态实现，也可直接继承SqlExecutor覆盖executeQuery来静态地实现，相比之下后者要简单许多，而且由于SqlExecutor没有实现任何接口，比较易变，动态拦截反到增加了维护的工作量，所以我们下面来覆盖executeQuery：
Java代码 1.package com.aladdin.dao.ibatis.ext;
2.
3.import java.sql.Connection;
4.import java.sql.SQLException;
5.
6.import org.apache.commons.logging.Log;
7.import org.apache.commons.logging.LogFactory;
8.
9.import com.aladdin.dao.dialect.Dialect;
10.import com.ibatis.sqlmap.engine.execution.SqlExecutor;
11.import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
12.import com.ibatis.sqlmap.engine.scope.RequestScope;
13.
14.public class LimitSqlExecutor extends SqlExecutor {
15.
16. private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
17.
18. private Dialect dialect;
19.
20. private boolean enableLimit = true;
21.
22. public Dialect getDialect() {
23. return dialect;
24. }
25.
26. public void setDialect(Dialect dialect) {
27. this.dialect = dialect;
28. }
29.
30. public boolean isEnableLimit() {
31. return enableLimit;
32. }
33.
34. public void setEnableLimit(boolean enableLimit) {
35. this.enableLimit = enableLimit;
36. }
37.
38. @Override
39. public void executeQuery(RequestScope request, Connection conn, String sql,
40. Object[] parameters, int skipResults, int maxResults,
41. RowHandlerCallback callback) throws SQLException {
42. if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)
43. && supportsLimit()) {
44. sql = dialect.getLimitString(sql, skipResults, maxResults);
45. if(logger.isDebugEnabled()){
46. logger.debug(sql);
47. }
48. skipResults = NO_SKIPPED_RESULTS;
49. maxResults = NO_MAXIMUM_RESULTS;
50. }
51. super.executeQuery(request, conn, sql, parameters, skipResults,
52. maxResults, callback);
53. }
54.
55. public boolean supportsLimit() {
56. if (enableLimit && dialect != null) {
57. return dialect.supportsLimit();
58. }
59. return false;
60. }
61.
62.}
package com.aladdin.dao.ibatis.ext;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.aladdin.dao.dialect.Dialect;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.RequestScope;
public class LimitSqlExecutor extends SqlExecutor {
private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
private Dialect dialect;
private boolean enableLimit = true;
public Dialect getDialect() {
return dialect;
}
public void setDialect(Dialect dialect) {
this.dialect = dialect;
}
public boolean isEnableLimit() {
return enableLimit;
}
public void setEnableLimit(boolean enableLimit) {
this.enableLimit = enableLimit;
}
@Override
public void executeQuery(RequestScope request, Connection conn, String sql,
Object[] parameters, int skipResults, int maxResults,
RowHandlerCallback callback) throws SQLException {
if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)
&& supportsLimit()) {
sql = dialect.getLimitString(sql, skipResults, maxResults);
if(logger.isDebugEnabled()){
logger.debug(sql);
}
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS;
}
super.executeQuery(request, conn, sql, parameters, skipResults,
maxResults, callback);
}
public boolean supportsLimit() {
if (enableLimit && dialect != null) {
return dialect.supportsLimit();
}
return false;
}
} 其中：
Java代码 1.skipResults = NO_SKIPPED_RESULTS;
2.maxResults = NO_MAXIMUM_RESULTS;
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS; 告诉handleResults不分页（我们组装的sql已经使查询结果是分页后的结果了），此处引入了类似hibenate中的数据库方言接口Dialect，其代码如下：
Java代码 1.package com.aladdin.dao.dialect;
2.
3.public interface Dialect {
4.
5. public boolean supportsLimit();
6.
7. public String getLimitString(String sql, boolean hasOffset);
8.
9. public String getLimitString(String sql, int offset, int limit);
10.}
package com.aladdin.dao.dialect;
public interface Dialect {
public boolean supportsLimit();
public String getLimitString(String sql, boolean hasOffset);
public String getLimitString(String sql, int offset, int limit);
}
下面为Dialect接口的MySQL实现：
Java代码 1.package com.aladdin.dao.dialect;
2.
3.public class MySQLDialect implements Dialect {
4.
5. protected static final String SQL_END_DELIMITER = ";";
6.
7. public String getLimitString(String sql, boolean hasOffset) {
8. return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
9. hasOffset ? " limit ?,?" : " limit ?")
10. .append(SQL_END_DELIMITER).toString();
11. }
12.
13. public String getLimitString(String sql, int offset, int limit) {
14. sql = trim(sql);
15. StringBuffer sb = new StringBuffer(sql.length() + 20);
16. sb.append(sql);
17. if (offset > 0) {
18. sb.append(" limit ").append(offset).append(',').append(limit)
19. .append(SQL_END_DELIMITER);
20. } else {
21. sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
22. }
23. return sb.toString();
24. }
25.
26. public boolean supportsLimit() {
27. return true;
28. }
29.
30. private String trim(String sql) {
31. sql = sql.trim();
32. if (sql.endsWith(SQL_END_DELIMITER)) {
33. sql = sql.substring(0, sql.length() - 1
34. - SQL_END_DELIMITER.length());
35. }
36. return sql;
37. }
38.
39.}
package com.aladdin.dao.dialect;
public class MySQLDialect implements Dialect {
protected static final String SQL_END_DELIMITER = ";";
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
hasOffset ? " limit ?,?" : " limit ?")
.append(SQL_END_DELIMITER).toString();
}
public String getLimitString(String sql, int offset, int limit) {
sql = trim(sql);
StringBuffer sb = new StringBuffer(sql.length() + 20);
sb.append(sql);
if (offset > 0) {
sb.append(" limit ").append(offset).append(',').append(limit)
.append(SQL_END_DELIMITER);
} else {
sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
}
return sb.toString();
}
public boolean supportsLimit() {
return true;
}
private String trim(String sql) {
sql = sql.trim();
if (sql.endsWith(SQL_END_DELIMITER)) {
sql = sql.substring(0, sql.length() - 1
- SQL_END_DELIMITER.length());
}
return sql;
}
} 接下来的工作就是把LimitSqlExecutor注入ibatis中。我们是通过spring来使用ibatis的，所以在我们的dao基类中执行注入，代码如下：
Java代码 1.package com.aladdin.dao.ibatis;
2.
3.import java.io.Serializable;
4.import java.util.List;
5.
6.import org.springframework.orm.ObjectRetrievalFailureException;
7.import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
8.
9.import com.aladdin.dao.ibatis.ext.LimitSqlExecutor;
10.import com.aladdin.domain.BaseObject;
11.import com.aladdin.util.ReflectUtil;
12.import com.ibatis.sqlmap.client.SqlMapClient;
13.import com.ibatis.sqlmap.engine.execution.SqlExecutor;
14.import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
15.
16.public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {
17.
18. private SqlExecutor sqlExecutor;
19.
20. public SqlExecutor getSqlExecutor() {
21. return sqlExecutor;
22. }
23.
24. public void setSqlExecutor(SqlExecutor sqlExecutor) {
25. this.sqlExecutor = sqlExecutor;
26. }
27.
28. public void setEnableLimit(boolean enableLimit) {
29. if (sqlExecutor instanceof LimitSqlExecutor) {
30. ((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
31. }
32. }
33.
34. public void initialize() throws Exception {
35. if (sqlExecutor != null) {
36. SqlMapClient sqlMapClient = getSqlMapClientTemplate()
37. .getSqlMapClient();
38. if (sqlMapClient instanceof ExtendedSqlMapClient) {
39. ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
40. .getDelegate(), "sqlExecutor", SqlExecutor.class,
41. sqlExecutor);
42. }
43. }
44. }
45.
46. ...
47.
48.}
package com.aladdin.dao.ibatis;
import java.io.Serializable;
import java.util.List;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.aladdin.dao.ibatis.ext.LimitSqlExecutor;
import com.aladdin.domain.BaseObject;
import com.aladdin.util.ReflectUtil;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {
private SqlExecutor sqlExecutor;
public SqlExecutor getSqlExecutor() {
return sqlExecutor;
}
public void setSqlExecutor(SqlExecutor sqlExecutor) {
this.sqlExecutor = sqlExecutor;
}
public void setEnableLimit(boolean enableLimit) {
if (sqlExecutor instanceof LimitSqlExecutor) {
((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
}
}
public void initialize() throws Exception {
if (sqlExecutor != null) {
SqlMapClient sqlMapClient = getSqlMapClientTemplate()
.getSqlMapClient();
if (sqlMapClient instanceof ExtendedSqlMapClient) {
ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
.getDelegate(), "sqlExecutor", SqlExecutor.class,
sqlExecutor);
}
}
}
...
} 其中的initialize方法执行注入，稍后会看到此方法在spring Beans 配置中指定为init-method。由于sqlExecutor是 com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient的私有成员，且没有公开的set方法，所以此处通过反射绕过java的访问控制，下面是ReflectUtil的实现代码：
Java代码 1.package com.aladdin.util;
2.
3.import java.lang.reflect.Field;
4.import java.lang.reflect.Method;
5.import java.lang.reflect.Modifier;
6.
7.import org.apache.commons.logging.Log;
8.import org.apache.commons.logging.LogFactory;
9.
10.public class ReflectUtil {
11.
12. private static final Log logger = LogFactory.getLog(ReflectUtil.class);
13.
14. public static void setFieldValue(Object target, String fname, Class ftype,
15. Object fvalue) {
16. if (target == null
17. || fname == null
18. || "".equals(fname)
19. || (fvalue != null && !ftype.isAssignableFrom(fvalue.getClass()))) {
20. return;
21. }
22. Class clazz = target.getClass();
23. try {
24. Method method = clazz.getDeclaredMethod("set"
25. + Character.toUpperCase(fname.charAt(0))
26. + fname.substring(1), ftype);
27. if (!Modifier.isPublic(method.getModifiers())) {
28. method.setAccessible(true);
29. }
30. method.invoke(target, fvalue);
31.
32. } catch (Exception me) {
33. if (logger.isDebugEnabled()) {
34. logger.debug(me);
35. }
36. try {
37. Field field = clazz.getDeclaredField(fname);
38. if (!Modifier.isPublic(field.getModifiers())) {
39. field.setAccessible(true);
40. }
41. field.set(target, fvalue);
42. } catch (Exception fe) {
43. if (logger.isDebugEnabled()) {
44. logger.debug(fe);
45. }
46. }
47. }
48. }
49.}
package com.aladdin.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class ReflectUtil {
private static final Log logger = LogFactory.getLog(ReflectUtil.class);
public static void setFieldValue(Object target, String fname, Class ftype,
Object fvalue) {
if (target == null
|| fname == null
|| "".equals(fname)
|| (fvalue != null && !ftype.isAssignableFrom(fvalue.getClass()))) {
return;
}
Class clazz = target.getClass();
try {
Method method = clazz.getDeclaredMethod("set"
+ Character.toUpperCase(fname.charAt(0))
+ fname.substring(1), ftype);
if (!Modifier.isPublic(method.getModifiers())) {
method.setAccessible(true);
}
method.invoke(target, fvalue);
} catch (Exception me) {
if (logger.isDebugEnabled()) {
logger.debug(me);
}
try {
Field field = clazz.getDeclaredField(fname);
if (!Modifier.isPublic(field.getModifiers())) {
field.setAccessible(true);
}
field.set(target, fvalue);
} catch (Exception fe) {
if (logger.isDebugEnabled()) {
logger.debug(fe);
}
}
}
}
}
到此剩下的就是通过Spring将sqlExecutor注入BaseDaoiBatis中了，下面是Spring Beans配置文件：
Xml代码 1.<?xml version="1.0" encoding="UTF-8"?>
2.<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
3. "http://www.springframework.org/dtd/spring-beans.dtd">
4.
5.<beans>
6. <!-- Transaction manager for a single JDBC DataSource -->
7. <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
8. <property name="dataSource">
9. <ref bean="dataSource" />
10. </property>
11. </bean>
12.
13. <!-- SqlMap setup for iBATIS Database Layer -->
14. <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
15. <property name="configLocation">
16. <value>classpath:/com/aladdin/dao/ibatis/sql-map-config.xml</value>
17. </property>
18. <property name="dataSource">
19. <ref bean="dataSource" />
20. </property>
21. </bean>
22.
23. <bean id="sqlExecutor" class="com.aladdin.dao.ibatis.ext.LimitSqlExecutor">
24. <property name="dialect">
25. <bean class="com.aladdin.dao.dialect.MySQLDialect" />
26. </property>
27. </bean>
28.
29. <bean id="baseDao" abstract="true" class="com.aladdin.dao.ibatis.BaseDaoiBatis" init-method="initialize">
30. <property name="dataSource">
31. <ref bean="dataSource" />
32. </property>
33. <property name="sqlMapClient">
34. <ref bean="sqlMapClient" />
35. </property>
36. <property name="sqlExecutor">
37. <ref bean="sqlExecutor" />
38. </property>
39. </bean>
40.
41. <bean id="userDao" class="com.aladdin.dao.ibatis.UserDaoiBatis" parent="baseDao" />
42.
43. <bean id="roleDao" class="com.aladdin.dao.ibatis.RoleDaoiBatis" parent="baseDao" />
44.
45. <bean id="resourceDao" class="com.aladdin.dao.ibatis.ResourceDaoiBatis" parent="baseDao" />
46.
47.</beans>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- Transaction manager for a single JDBC DataSource -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<!-- SqlMap setup for iBATIS Database Layer -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>classpath:/com/aladdin/dao/ibatis/sql-map-config.xml</value>
</property>
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<bean id="sqlExecutor" class="com.aladdin.dao.ibatis.ext.LimitSqlExecutor">
<property name="dialect">
<bean class="com.aladdin.dao.dialect.MySQLDialect" />
</property>
</bean>
<bean id="baseDao" abstract="true" class="com.aladdin.dao.ibatis.BaseDaoiBatis" init-method="initialize">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="sqlMapClient">
<ref bean="sqlMapClient" />
</property>
<property name="sqlExecutor">
<ref bean="sqlExecutor" />
</property>
</bean>
<bean id="userDao" class="com.aladdin.dao.ibatis.UserDaoiBatis" parent="baseDao" />
<bean id="roleDao" class="com.aladdin.dao.ibatis.RoleDaoiBatis" parent="baseDao" />
<bean id="resourceDao" class="com.aladdin.dao.ibatis.ResourceDaoiBatis" parent="baseDao" />
</beans>
此后就可以通过调用org.springframework.orm.ibatis.SqlMapClientTemplate的
public List queryForList(final String statementName, final Object parameterObject, final int skipResults, final int maxResults) throws DataAccessException
或
public PaginatedList queryForPaginatedList(final String statementName, final Object parameterObject, final int pageSize) throws DataAccessException
得到分页结果了。建议使用第一个方法，第二个方法返回的是PaginatedList，虽然使用简单，但是其获得指定页的数据是跨过我们的dao直接访问ibatis的，不方便统一管理
关键字: ibatis spring 分页 物理分页
公司的大部分项目都开始使用IBatis作为O/R Mapping了,但是在使用的过程中也发现了很多不方便和存在争议的地方,其中一个不方便的地方就是分页,目前的处理方式都是在sqlMap中写针对特定数据库的物理分页Sql语句,对于oracle数据库都是在分页的sql语句里面加上三层嵌套的sql语句,想了很多办法,都没能很好的避免这个问题, 无意间在javaeye上看到了《使ibatis支持hibernate式的物理分页》这篇文章,可点进去已经被删除了,幸好google了一下有很多人已经收藏了,这里转载一下,以便再找不到了.
转载地址:http://www.blogjava.net/libin2722/articles/192504.html
一直以来ibatis的分页都是通过滚动ResultSet实现的，应该算是逻辑分页吧。逻辑分页虽然能很干净地独立于特定数据库，但效率在多数情况下不及特定数据库支持的物理分页，而hibernate的分页则是直接组装sql，充分利用了特定数据库的分页机制，效率相对较高。本文讲述的就是如何在不重新编译ibatis源码的前提下，为ibatis引入hibernate式的物理分页机制。
基本思路就是找到ibatis执行sql的地方，截获sql并重新组装sql。通过分析ibatis源码知道，最终负责执行sql的类是 com.ibatis.sqlmap.engine.execution.SqlExecutor，此类没有实现任何接口，这多少有点遗憾，因为接口是相对稳定契约，非大的版本更新，接口一般是不会变的，而类就相对易变一些，所以这里的代码只能保证对当前版本（2.1.7）的ibatis有效。下面是 SqlExecutor执行查询的方法：
Java代码 1./**
2. * Long form of the method to execute a query
3. *
4. * @param request - the request scope
5. * @param conn - the database connection
6. * @param sql - the SQL statement to execute
7. * @param parameters - the parameters for the statement
8. * @param skipResults - the number of results to skip
9. * @param maxResults - the maximum number of results to return
10. * @param callback - the row handler for the query
11. *
12. * @throws SQLException - if the query fails
13. */
14. public void executeQuery(RequestScope request, Connection conn, String sql, Object[] parameters,
15. int skipResults, int maxResults, RowHandlerCallback callback)
16. throws SQLException {
17. ErrorContext errorContext = request.getErrorContext();
18. errorContext.setActivity("executing query");
19. errorContext.setObjectId(sql);
20.
21. PreparedStatement ps = null;
22. ResultSet rs = null;
23.
24. try {
25. errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
26.
27. Integer rsType = request.getStatement().getResultSetType();
28. if (rsType != null) {
29. ps = conn.prepareStatement(sql, rsType.intValue(), ResultSet.CONCUR_READ_ONLY);
30. } else {
31. ps = conn.prepareStatement(sql);
32. }
33.
34. Integer fetchSize = request.getStatement().getFetchSize();
35. if (fetchSize != null) {
36. ps.setFetchSize(fetchSize.intValue());
37. }
38.
39. errorContext.setMoreInfo("Check the parameters (set parameters failed).");
40. request.getParameterMap().setParameters(request, ps, parameters);
41.
42. errorContext.setMoreInfo("Check the statement (query failed).");
43.
44. ps.execute();
45. rs = getFirstResultSet(ps);
46.
47. if (rs != null) {
48. errorContext.setMoreInfo("Check the results (failed to retrieve results).");
49. handleResults(request, rs, skipResults, maxResults, callback);
50. }
51.
52. // clear out remaining results
53. while (ps.getMoreResults());
54.
55. } finally {
56. try {
57. closeResultSet(rs);
58. } finally {
59. closeStatement(ps);
60. }
61. }
62.
63. }
/**
* Long form of the method to execute a query
*
* @param request - 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
*/
public void executeQuery(RequestScope request, Connection conn, String sql, Object[] parameters,
int skipResults, int maxResults, RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = request.getErrorContext();
errorContext.setActivity("executing query");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
ResultSet rs = null;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = request.getStatement().getResultSetType();
if (rsType != null) {
ps = conn.prepareStatement(sql, rsType.intValue(), ResultSet.CONCUR_READ_ONLY);
} else {
ps = conn.prepareStatement(sql);
}
Integer fetchSize = request.getStatement().getFetchSize();
if (fetchSize != null) {
ps.setFetchSize(fetchSize.intValue());
}
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
request.getParameterMap().setParameters(request, ps, parameters);
errorContext.setMoreInfo("Check the statement (query failed).");
ps.execute();
rs = getFirstResultSet(ps);
if (rs != null) {
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
handleResults(request, rs, skipResults, maxResults, callback);
}
// clear out remaining results
while (ps.getMoreResults());
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(ps);
}
}
}
其中handleResults(request, rs, skipResults, maxResults, callback)一句用于处理分页，其实此时查询已经执行完毕，可以不必关心handleResults方法，但为清楚起见，下面来看看 handleResults的实现：
Java代码 1.private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
2. try {
3. request.setResultSet(rs);
4. ResultMap resultMap = request.getResultMap();
5. if (resultMap != null) {
6. // Skip Results
7. if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
8. if (skipResults > 0) {
9. rs.absolute(skipResults);
10. }
11. } else {
12. for (int i = 0; i < skipResults; i++) {
13. if (!rs.next()) {
14. break;
15. }
16. }
17. }
18.
19. // Get Results
20. int resultsFetched = 0;
21. while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {
22. Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs);
23. callback.handleResultObject(request, columnValues, rs);
24. resultsFetched++;
25. }
26. }
27. } finally {
28. request.setResultSet(null);
29. }
30. }
private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
try {
request.setResultSet(rs);
ResultMap resultMap = request.getResultMap();
if (resultMap != null) {
// Skip Results
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (skipResults > 0) {
rs.absolute(skipResults);
}
} else {
for (int i = 0; i < skipResults; i++) {
if (!rs.next()) {
break;
}
}
}
// Get Results
int resultsFetched = 0;
while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {
Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs);
callback.handleResultObject(request, columnValues, rs);
resultsFetched++;
}
}
} finally {
request.setResultSet(null);
}
}
此处优先使用的是ResultSet的absolute方法定位记录，是否支持absolute取决于具体数据库驱动，但一般当前版本的数据库都支持该方法，如果不支持则逐条跳过前面的记录。由此可以看出如果数据库支持absolute，则ibatis内置的分页策略与特定数据库的物理分页效率差距就在于物理分页查询与不分页查询在数据库中的执行效率的差距了。因为查询执行后读取数据前数据库并未把结果全部返回到内存，所以本身在存储占用上应该差距不大，如果都使用索引，估计执行速度也差不太多。
继续我们的话题。其实只要在executeQuery执行前组装sql，然后将其传给 executeQuery，并告诉handleResults我们不需要逻辑分页即可。拦截executeQuery可以采用aop动态实现，也可直接继承SqlExecutor覆盖executeQuery来静态地实现，相比之下后者要简单许多，而且由于SqlExecutor没有实现任何接口，比较易变，动态拦截反到增加了维护的工作量，所以我们下面来覆盖executeQuery：
Java代码 1.package com.aladdin.dao.ibatis.ext;
2.
3.import java.sql.Connection;
4.import java.sql.SQLException;
5.
6.import org.apache.commons.logging.Log;
7.import org.apache.commons.logging.LogFactory;
8.
9.import com.aladdin.dao.dialect.Dialect;
10.import com.ibatis.sqlmap.engine.execution.SqlExecutor;
11.import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
12.import com.ibatis.sqlmap.engine.scope.RequestScope;
13.
14.public class LimitSqlExecutor extends SqlExecutor {
15.
16. private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
17.
18. private Dialect dialect;
19.
20. private boolean enableLimit = true;
21.
22. public Dialect getDialect() {
23. return dialect;
24. }
25.
26. public void setDialect(Dialect dialect) {
27. this.dialect = dialect;
28. }
29.
30. public boolean isEnableLimit() {
31. return enableLimit;
32. }
33.
34. public void setEnableLimit(boolean enableLimit) {
35. this.enableLimit = enableLimit;
36. }
37.
38. @Override
39. public void executeQuery(RequestScope request, Connection conn, String sql,
40. Object[] parameters, int skipResults, int maxResults,
41. RowHandlerCallback callback) throws SQLException {
42. if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)
43. && supportsLimit()) {
44. sql = dialect.getLimitString(sql, skipResults, maxResults);
45. if(logger.isDebugEnabled()){
46. logger.debug(sql);
47. }
48. skipResults = NO_SKIPPED_RESULTS;
49. maxResults = NO_MAXIMUM_RESULTS;
50. }
51. super.executeQuery(request, conn, sql, parameters, skipResults,
52. maxResults, callback);
53. }
54.
55. public boolean supportsLimit() {
56. if (enableLimit && dialect != null) {
57. return dialect.supportsLimit();
58. }
59. return false;
60. }
61.
62.}
package com.aladdin.dao.ibatis.ext;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.aladdin.dao.dialect.Dialect;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.RequestScope;
public class LimitSqlExecutor extends SqlExecutor {
private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
private Dialect dialect;
private boolean enableLimit = true;
public Dialect getDialect() {
return dialect;
}
public void setDialect(Dialect dialect) {
this.dialect = dialect;
}
public boolean isEnableLimit() {
return enableLimit;
}
public void setEnableLimit(boolean enableLimit) {
this.enableLimit = enableLimit;
}
@Override
public void executeQuery(RequestScope request, Connection conn, String sql,
Object[] parameters, int skipResults, int maxResults,
RowHandlerCallback callback) throws SQLException {
if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)
&& supportsLimit()) {
sql = dialect.getLimitString(sql, skipResults, maxResults);
if(logger.isDebugEnabled()){
logger.debug(sql);
}
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS;
}
super.executeQuery(request, conn, sql, parameters, skipResults,
maxResults, callback);
}
public boolean supportsLimit() {
if (enableLimit && dialect != null) {
return dialect.supportsLimit();
}
return false;
}
} 其中：
Java代码 1.skipResults = NO_SKIPPED_RESULTS;
2.maxResults = NO_MAXIMUM_RESULTS;
skipResults = NO_SKIPPED_RESULTS;
maxResults = NO_MAXIMUM_RESULTS; 告诉handleResults不分页（我们组装的sql已经使查询结果是分页后的结果了），此处引入了类似hibenate中的数据库方言接口Dialect，其代码如下：
Java代码 1.package com.aladdin.dao.dialect;
2.
3.public interface Dialect {
4.
5. public boolean supportsLimit();
6.
7. public String getLimitString(String sql, boolean hasOffset);
8.
9. public String getLimitString(String sql, int offset, int limit);
10.}
package com.aladdin.dao.dialect;
public interface Dialect {
public boolean supportsLimit();
public String getLimitString(String sql, boolean hasOffset);
public String getLimitString(String sql, int offset, int limit);
}
下面为Dialect接口的MySQL实现：
Java代码 1.package com.aladdin.dao.dialect;
2.
3.public class MySQLDialect implements Dialect {
4.
5. protected static final String SQL_END_DELIMITER = ";";
6.
7. public String getLimitString(String sql, boolean hasOffset) {
8. return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
9. hasOffset ? " limit ?,?" : " limit ?")
10. .append(SQL_END_DELIMITER).toString();
11. }
12.
13. public String getLimitString(String sql, int offset, int limit) {
14. sql = trim(sql);
15. StringBuffer sb = new StringBuffer(sql.length() + 20);
16. sb.append(sql);
17. if (offset > 0) {
18. sb.append(" limit ").append(offset).append(',').append(limit)
19. .append(SQL_END_DELIMITER);
20. } else {
21. sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
22. }
23. return sb.toString();
24. }
25.
26. public boolean supportsLimit() {
27. return true;
28. }
29.
30. private String trim(String sql) {
31. sql = sql.trim();
32. if (sql.endsWith(SQL_END_DELIMITER)) {
33. sql = sql.substring(0, sql.length() - 1
34. - SQL_END_DELIMITER.length());
35. }
36. return sql;
37. }
38.
39.}
package com.aladdin.dao.dialect;
public class MySQLDialect implements Dialect {
protected static final String SQL_END_DELIMITER = ";";
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer(sql.length() + 20).append(trim(sql)).append(
hasOffset ? " limit ?,?" : " limit ?")
.append(SQL_END_DELIMITER).toString();
}
public String getLimitString(String sql, int offset, int limit) {
sql = trim(sql);
StringBuffer sb = new StringBuffer(sql.length() + 20);
sb.append(sql);
if (offset > 0) {
sb.append(" limit ").append(offset).append(',').append(limit)
.append(SQL_END_DELIMITER);
} else {
sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);
}
return sb.toString();
}
public boolean supportsLimit() {
return true;
}
private String trim(String sql) {
sql = sql.trim();
if (sql.endsWith(SQL_END_DELIMITER)) {
sql = sql.substring(0, sql.length() - 1
- SQL_END_DELIMITER.length());
}
return sql;
}
} 接下来的工作就是把LimitSqlExecutor注入ibatis中。我们是通过spring来使用ibatis的，所以在我们的dao基类中执行注入，代码如下：
Java代码 1.package com.aladdin.dao.ibatis;
2.
3.import java.io.Serializable;
4.import java.util.List;
5.
6.import org.springframework.orm.ObjectRetrievalFailureException;
7.import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
8.
9.import com.aladdin.dao.ibatis.ext.LimitSqlExecutor;
10.import com.aladdin.domain.BaseObject;
11.import com.aladdin.util.ReflectUtil;
12.import com.ibatis.sqlmap.client.SqlMapClient;
13.import com.ibatis.sqlmap.engine.execution.SqlExecutor;
14.import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
15.
16.public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {
17.
18. private SqlExecutor sqlExecutor;
19.
20. public SqlExecutor getSqlExecutor() {
21. return sqlExecutor;
22. }
23.
24. public void setSqlExecutor(SqlExecutor sqlExecutor) {
25. this.sqlExecutor = sqlExecutor;
26. }
27.
28. public void setEnableLimit(boolean enableLimit) {
29. if (sqlExecutor instanceof LimitSqlExecutor) {
30. ((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
31. }
32. }
33.
34. public void initialize() throws Exception {
35. if (sqlExecutor != null) {
36. SqlMapClient sqlMapClient = getSqlMapClientTemplate()
37. .getSqlMapClient();
38. if (sqlMapClient instanceof ExtendedSqlMapClient) {
39. ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
40. .getDelegate(), "sqlExecutor", SqlExecutor.class,
41. sqlExecutor);
42. }
43. }
44. }
45.
46. ...
47.
48.}
package com.aladdin.dao.ibatis;
import java.io.Serializable;
import java.util.List;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import com.aladdin.dao.ibatis.ext.LimitSqlExecutor;
import com.aladdin.domain.BaseObject;
import com.aladdin.util.ReflectUtil;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;
public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {
private SqlExecutor sqlExecutor;
public SqlExecutor getSqlExecutor() {
return sqlExecutor;
}
public void setSqlExecutor(SqlExecutor sqlExecutor) {
this.sqlExecutor = sqlExecutor;
}
public void setEnableLimit(boolean enableLimit) {
if (sqlExecutor instanceof LimitSqlExecutor) {
((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
}
}
public void initialize() throws Exception {
if (sqlExecutor != null) {
SqlMapClient sqlMapClient = getSqlMapClientTemplate()
.getSqlMapClient();
if (sqlMapClient instanceof ExtendedSqlMapClient) {
ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
.getDelegate(), "sqlExecutor", SqlExecutor.class,
sqlExecutor);
}
}
}
...
} 其中的initialize方法执行注入，稍后会看到此方法在spring Beans 配置中指定为init-method。由于sqlExecutor是 com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient的私有成员，且没有公开的set方法，所以此处通过反射绕过java的访问控制，下面是ReflectUtil的实现代码：
Java代码 1.package com.aladdin.util;
2.
3.import java.lang.reflect.Field;
4.import java.lang.reflect.Method;
5.import java.lang.reflect.Modifier;
6.
7.import org.apache.commons.logging.Log;
8.import org.apache.commons.logging.LogFactory;
9.
10.public class ReflectUtil {
11.
12. private static final Log logger = LogFactory.getLog(ReflectUtil.class);
13.
14. public static void setFieldValue(Object target, String fname, Class ftype,
15. Object fvalue) {
16. if (target == null
17. || fname == null
18. || "".equals(fname)
19. || (fvalue != null && !ftype.isAssignableFrom(fvalue.getClass()))) {
20. return;
21. }
22. Class clazz = target.getClass();
23. try {
24. Method method = clazz.getDeclaredMethod("set"
25. + Character.toUpperCase(fname.charAt(0))
26. + fname.substring(1), ftype);
27. if (!Modifier.isPublic(method.getModifiers())) {
28. method.setAccessible(true);
29. }
30. method.invoke(target, fvalue);
31.
32. } catch (Exception me) {
33. if (logger.isDebugEnabled()) {
34. logger.debug(me);
35. }
36. try {
37. Field field = clazz.getDeclaredField(fname);
38. if (!Modifier.isPublic(field.getModifiers())) {
39. field.setAccessible(true);
40. }
41. field.set(target, fvalue);
42. } catch (Exception fe) {
43. if (logger.isDebugEnabled()) {
44. logger.debug(fe);
45. }
46. }
47. }
48. }
49.}
package com.aladdin.util;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class ReflectUtil {
private static final Log logger = LogFactory.getLog(ReflectUtil.class);
public static void setFieldValue(Object target, String fname, Class ftype,
Object fvalue) {
if (target == null
|| fname == null
|| "".equals(fname)
|| (fvalue != null && !ftype.isAssignableFrom(fvalue.getClass()))) {
return;
}
Class clazz = target.getClass();
try {
Method method = clazz.getDeclaredMethod("set"
+ Character.toUpperCase(fname.charAt(0))
+ fname.substring(1), ftype);
if (!Modifier.isPublic(method.getModifiers())) {
method.setAccessible(true);
}
method.invoke(target, fvalue);
} catch (Exception me) {
if (logger.isDebugEnabled()) {
logger.debug(me);
}
try {
Field field = clazz.getDeclaredField(fname);
if (!Modifier.isPublic(field.getModifiers())) {
field.setAccessible(true);
}
field.set(target, fvalue);
} catch (Exception fe) {
if (logger.isDebugEnabled()) {
logger.debug(fe);
}
}
}
}
}
到此剩下的就是通过Spring将sqlExecutor注入BaseDaoiBatis中了，下面是Spring Beans配置文件：
Xml代码 1.<?xml version="1.0" encoding="UTF-8"?>
2.<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
3. "http://www.springframework.org/dtd/spring-beans.dtd">
4.
5.<beans>
6. <!-- Transaction manager for a single JDBC DataSource -->
7. <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
8. <property name="dataSource">
9. <ref bean="dataSource" />
10. </property>
11. </bean>
12.
13. <!-- SqlMap setup for iBATIS Database Layer -->
14. <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
15. <property name="configLocation">
16. <value>classpath:/com/aladdin/dao/ibatis/sql-map-config.xml</value>
17. </property>
18. <property name="dataSource">
19. <ref bean="dataSource" />
20. </property>
21. </bean>
22.
23. <bean id="sqlExecutor" class="com.aladdin.dao.ibatis.ext.LimitSqlExecutor">
24. <property name="dialect">
25. <bean class="com.aladdin.dao.dialect.MySQLDialect" />
26. </property>
27. </bean>
28.
29. <bean id="baseDao" abstract="true" class="com.aladdin.dao.ibatis.BaseDaoiBatis" init-method="initialize">
30. <property name="dataSource">
31. <ref bean="dataSource" />
32. </property>
33. <property name="sqlMapClient">
34. <ref bean="sqlMapClient" />
35. </property>
36. <property name="sqlExecutor">
37. <ref bean="sqlExecutor" />
38. </property>
39. </bean>
40.
41. <bean id="userDao" class="com.aladdin.dao.ibatis.UserDaoiBatis" parent="baseDao" />
42.
43. <bean id="roleDao" class="com.aladdin.dao.ibatis.RoleDaoiBatis" parent="baseDao" />
44.
45. <bean id="resourceDao" class="com.aladdin.dao.ibatis.ResourceDaoiBatis" parent="baseDao" />
46.
47.</beans>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
"http://www.springframework.org/dtd/spring-beans.dtd">
<beans>
<!-- Transaction manager for a single JDBC DataSource -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<!-- SqlMap setup for iBATIS Database Layer -->
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocation">
<value>classpath:/com/aladdin/dao/ibatis/sql-map-config.xml</value>
</property>
<property name="dataSource">
<ref bean="dataSource" />
</property>
</bean>
<bean id="sqlExecutor" class="com.aladdin.dao.ibatis.ext.LimitSqlExecutor">
<property name="dialect">
<bean class="com.aladdin.dao.dialect.MySQLDialect" />
</property>
</bean>
<bean id="baseDao" abstract="true" class="com.aladdin.dao.ibatis.BaseDaoiBatis" init-method="initialize">
<property name="dataSource">
<ref bean="dataSource" />
</property>
<property name="sqlMapClient">
<ref bean="sqlMapClient" />
</property>
<property name="sqlExecutor">
<ref bean="sqlExecutor" />
</property>
</bean>
<bean id="userDao" class="com.aladdin.dao.ibatis.UserDaoiBatis" parent="baseDao" />
<bean id="roleDao" class="com.aladdin.dao.ibatis.RoleDaoiBatis" parent="baseDao" />
<bean id="resourceDao" class="com.aladdin.dao.ibatis.ResourceDaoiBatis" parent="baseDao" />
</beans>
此后就可以通过调用org.springframework.orm.ibatis.SqlMapClientTemplate的
public List queryForList(final String statementName, final Object parameterObject, final int skipResults, final int maxResults) throws DataAccessException
或
public PaginatedList queryForPaginatedList(final String statementName, final Object parameterObject, final int pageSize) throws DataAccessException
得到分页结果了。建议使用第一个方法，第二个方法返回的是PaginatedList，虽然使用简单，但是其获得指定页的数据是跨过我们的dao直接访问ibatis的，不方便统一管理
分享到：
发表评论
相关推荐
总之，通过以上方法，我们可以实现在不修改iBatis源码的前提下，让iBatis支持类似Hibernate的物理分页，提高查询效率，尤其在大数据量的场景下，这种优化尤为关键。不过，需要注意的是，不同的数据库系统对物理分页...
本文讲述的就是如何在不重新编译ibatis源码的前提下，为ibatis引入hibernate式的物理分页机制。 基本思路就是找到ibatis执行sql的地方，截获sql并重新组装sql。通过分析ibatis源码知道，最终负责执行sql的类是 ...
SSH2+IBATIS框架是Java开发中常用的一套企业级应用开发框架组合，它将Spring、Hibernate、Struts2和iBatis等组件融合在一起，以提高开发效率和实现松耦合。以下是对这些框架及其整合方式的详细说明： **Spring框架*...
本项目是一个基于SpringMVC、Spring、Hibernate和jQuery的完整分页应用，涵盖了后端服务、数据持久化、前端交互等多个重要技术领域。下面将详细解释这些技术及其在项目中的应用。 1. SpringMVC：SpringMVC是Spring...
### MySQL、JDBC详解及与iBatis对比 ...通过对MySQL的基本操作、JDBC的工作原理以及iBatis与JDBC的对比分析，我们可以更全面地理解数据库编程的关键概念和技术要点。希望本文能够帮助开发者们更加熟练地掌握这些技能。
除了基本的查询，iBatis还支持动态SQL、批量操作、分页等功能，这些都是在实际项目中非常实用的能力。例如，动态SQL允许开发者根据不同的条件构建SQL语句，而批量操作则可以提高数据处理的效率。 ### 四、总结 ...
Ibatis的核心理念是“SQL就是SQL”，它并不像Hibernate那样尝试完全对象化数据库，而是将数据访问逻辑保留在SQL语句中，通过XML配置文件或注解来绑定SQL与Java对象。这使得开发人员可以充分利用SQL的灵活性，同时...
- **配置简单**：最新的iBatis版本（例如2.0）改进了XML配置文件，使其更加直观易懂，即使是没有深入学习过iBatis的新手也能够快速理解配置文件的结构和意义。 #### 三、环境搭建与基本配置 1. **安装iBatis**：...
与其他数据库持久层框架如JDO、Hibernate相比，Ibatis的最大优势在于其简洁性和易学性。只需掌握JavaBean、XML和SQL的基础知识，开发者就能有效地利用Ibatis发挥出SQL的强大功能。 #### 安装与配置 - **JAR文件和...
与Hibernate相比，Ibatis更注重SQL的自由度，适合对SQL有深度需求的项目。而Hibernate则以全自动化为特点，通过ORM机制简化了数据库操作，但在大数据量和复杂查询时可能效率较低。选择Ibatis还是Hibernate，取决于...
在Ibatis中，可以通过动态SQL来实现排序（ORDER BY）和分页（LIMIT 或 ROWNUM），而在控制器层，Spring MVC 可以处理请求参数，传递分页和排序条件到服务层，从而返回对应的查询结果。 "新增、修改、删除"是CRUD...
在IT行业中，分页是数据库查询的一个重要特性，特别是在数据量庞大的情况下，它能帮助...在 chap_ibatis 压缩包中，可能包含了相关的Ibatis分页源码示例，你可以进一步研究其中的细节，加深对Ibatis分页机制的理解。
- 对于那些对性能有较高要求的应用场景，iBatis 的灵活性和可控性使其成为更好的选择。 **5.2 复杂查询** - 当应用中包含大量复杂的 SQL 查询时，iBatis 的手动 SQL 控制能够更好地满足需求。 **5.3 简单查询** - ...
Ibatis是由Apache软件基金会维护的一个开源项目，它的核心功能是将SQL语句与Java代码分离，提供灵活的数据访问层，使数据库操作更加简单、高效。 在视频教程中，你可能会学到以下关键知识点： 1. **Ibatis简介**：...
iBatis是一个轻量级的Java持久层框架，它与Hibernate相比，更加灵活，适合于对数据库操作有自定义需求的项目。在本资源中，"iBatis条件查询"着重展示了如何根据业务需求定制SQL语句进行数据检索，尤其在不涉及复杂...
此外，由于Hibernate的自动管理特性，对于某些特定的数据库优化操作，如分页查询、批处理更新等，可能不如iBatis那么直接和高效。 其次，iBatis的学习曲线相对较平缓，因为它允许开发者保留对SQL的直接控制。而...
虽然与Hibernate相比，iBATIS可能在自动化程度和缓存管理上略显不足，但对于那些需要对SQL查询有精细化控制的应用场景，iBATIS无疑是更好的选择。开发者应根据项目的具体需求和团队的技术栈来决定采用哪种框架，以...
8. 分页查询：iBatis支持通过设置参数实现分页查询，这对大数据量的展示非常有用。 9. 缓存机制：iBatis提供了本地缓存和二级缓存机制，可以有效减少数据库访问，提高系统性能。开发者可以根据需求选择启用并配置...
【标题】：“利用Mybatis的动态SQL实现物理分页” 【描述】：本文主要探讨了在实际项目中如何利用Mybatis的动态SQL功能来解决大数据量下的物理分页问题，以避免内存溢出。 【标签】：“SQL 数据库 数据处理 参考...