ibatis实现分页查询

最近在做老项目改造，分享一个之前写的ibatis（这里特指ibatis3.0之前的版本）分页插件。

其实也算不上插件，因为这个已经是hack-in的写法。大致原理就是替换ibatis的SqlExecutor，然后通过重写executeQuery方法，对分页做特殊处理。

代码是基于ibatis-sqlmap-2.3.4.726-sources写的（https://git.coding.net/lichmama/ibatis-pagination.git）核心文件就5个：

 

使用起来也很简单，给个结合spring的例子来看：

sqlmap文件：

<sqlMap namespace="userDAO">
    <!-- 省略其他配置... -->

    <select id="getUsersByParam" parameterClass="map" resultMap="UserMap">
        select * from users
        <dynamic prepend="where">
            <isNotEmpty prepend="and" property="gender">
                gender = #gender#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="status">
                status = #status#
            </isNotEmpty>
            <isNotEmpty prepend="and" property="username">
                username like concat('%', #gender#, '%')
            </isNotEmpty>
        </dynamic>
        order by id asc
    </select>
</sqlMap>

 

IBatisBaseDao.java：

public abstract class IBatisBaseDAO<E, K> extends SqlMapClientDaoSupport {
    private static PagedSqlExecutor pagedSqlExecutor;

    @Override
    protected void initDao() throws Exception {
        super.initDao();
        synchronized (IBatisBaseDAO.class) {
            if (pagedSqlExecutor == null) {
                synchronized (IBatisBaseDAO.class) {
                    pagedSqlExecutor = SpringContextHolder.getBean(PagedSqlExecutor.class);
                    ((SqlMapClientImpl) getSqlMapClient()).getDelegate().setSqlExecutor(pagedSqlExecutor);
                }
            }
        }
    }

    public abstract String getNamespace();
    
    public String getSqlId(String statementName) {
        return getNamespace() + "." + statementName;
    }

    public Page queryForPage(String statementName, Object paramObject, Page page) {
        try {
            List list = getSqlMapClient().queryForList(getSqlId(statementName), paramObject, page.getBeginPos(),
                    page.getPageSize());
            page.setTotalRows(pagedSqlExecutor.getRowsCount());
            page.setResultList(list);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return page;
    }
}

 

UserDAOImpl.java：

public class UserDAOImpl extends IBatisBaseDAO<User, Integer> implements IUserDAO {

    @Override
    public String getNamespace() {
        return "userDAO";
    }

    // 调用queryForPage实现分页查询
    public Page getUsersPaged(Map paramObject, int currentPage, int pageSize) {
        Page page = new Page();
        page.setCurrentPage(currentPage);
        page.setPageSize(pageSize);
        return queryForPage("getUsersByParam", paramObject, page);
    }
}

 

关键spring配置：

    <bean id="pagedSqlExecutor" class="com.ibatis.pagination.PagedSqlExecutor">
        <property name="pagination">
            <!-- 跟数据库类型选择:mysql/oracle -->
            <bean class="com.ibatis.pagination.impl.MySQLPagination" />
        </property>
    </bean>
    
    <bean id="userDAO" class="com.lichmama.dao.impl.UserDAOImpl">
        <property name="sqlMapClient" ref="sqlMapClient" />
    </bean>