iabtis分页请教

Question

@刘家华 你好，想跟你请教个问题：您好，我看到您的博文，能否发一个实现的ibatis分页改造的一些类给我邮箱？谢谢，lg0104@sina.com

因为我水平有限，看不太明白您的那些简单核心代码的截图。我目前就是想封装下查询的时候想您这样可以查询出总记录数，和记录list

Answer 1

代码中getSqlWithPagerLimit是oracle的例子

Answer 2

回复
确认一下最佳答案，给点分吧

Answer 3

回复
怎么给分？还是不太会用。。。/(ㄒoㄒ)/~~

Answer 4

回复
应该很简单吧，实在不知道可以问问
@红薯

Answer 5

@刘家华 红薯是哪位？我现在的问题是： mappedStatement.setSql(proxy);这个方法没有，还有就是： public String handle(String sql, Object[] params) throws Throwable { String nsql = sqlHandler.handle(sql, params); ...

Answer 6

import com.ibatis.sqlmap.engine.impl.SqlMapClientImpl;
import com.ibatis.sqlmap.engine.mapping.sql.Sql;
import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;
import com.ibatis.sqlmap.engine.scope.StatementScope;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.Resource;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.SQLException;
import java.util.List;

public class BaseDao {

    private static final Log log = LogFactory.getLog(BaseDao.class);
    private SqlMapClientImpl sqlMapClient;
    private JdbcTemplate jdbcTemplate;

    @Resource(name = "sqlMapClient")
    public final void setSqlMapClientImpl(SqlMapClientImpl sqlMapClient) {
        this.sqlMapClient = sqlMapClient;
        this.jdbcTemplate = new JdbcTemplate(sqlMapClient.getDataSource(), true);
    }

    /**
     * 为SQL添加分页包装
     *
     * @param sql
     * @param pageForm
     * @return
     */
    private String getSqlWithPagerLimit(final String sql, final PageForm pageForm) {
        final int start = pageForm.getPage() * pageForm.getPageSize();
        final int end = (pageForm.getPage() + 1) * pageForm.getPageSize();
        final String sidx = pageForm.getSidx();
        final String sord = pageForm.getSord();
        final StringBuilder _sql = new StringBuilder().append(sql);
        //排序
        if (StringUtils.isNotBlank(sidx) && StringUtils.isNotBlank(sord)) {
            _sql.insert(0, "select * from (").append(") order by ").append(sidx).append(" ").append(sord);
        }
        //分页
        _sql.insert(0, "select * from (select row_.*, rownum row_num_ from (");
        _sql.append(") row_ where rownum<=").append(end).append(") where row_num_ > ").append(start);
        log.debug("分页： " + _sql);
        return _sql.toString();
    }

    /**
     * 提供查询前对sql处理的功能
     *
     * @param statementId
     * @param param
     * @param sqlHandler sql处理器
     * @return
     */
    protected List queryWithSqlHandler(final String statementId, final Object param, final SqlHandler sqlHandler) {
        if (sqlHandler != null) {
            final MappedStatement mappedStatement = sqlMapClient.getMappedStatement(statementId);
            final Sql dySql = mappedStatement.getSql();
            if (Proxy.isProxyClass(dySql.getClass())) {
                log.debug("该Sql对象已经是代理对象，设置新的sql处理器。");
                ((SqlProxyHandler) Proxy.getInvocationHandler(dySql)).setSqlHandler(sqlHandler);
            } else {
                log.debug("创建Sql的代理对象！");
                final SqlProxyHandler sqlProxyHandler = new SqlProxyHandler(dySql, sqlHandler);
                final Class sqlClass = dySql.getClass();
                final Sql proxy = (Sql) Proxy.newProxyInstance(sqlClass.getClassLoader(), sqlClass.getInterfaces(), sqlProxyHandler);
                mappedStatement.setSql(proxy);
            }
        }
        try {
            return sqlMapClient.queryForList(statementId, param);
        } catch (SQLException ex) {
            throw new RuntimeException("查询失败", ex);
        }
    }

    protected IPage findPageWithSqlHandler(String statementId, Object param, final PageForm pageForm, final SqlHandler sqlHandler) {
        final int[] total = new int[1];
        List list = queryWithSqlHandler(statementId, param, new SqlHandler() {
            @Override
            public String handle(String sql, Object[] params) throws Throwable {
                String nsql = sqlHandler.handle(sql, params);
                //查询总记录数
                total[0] = jdbcTemplate.queryForInt("select count(1) as RECORDS from (" + nsql + ")", params);
                return getSqlWithPagerLimit(nsql, pageForm);
            }
        });
        return new Page(list, total[0], pageForm.getPageSize(), pageForm.getPage());
    }

    private static final class SqlProxyHandler implements InvocationHandler {

        private final Sql sql;
        private final ThreadLocal<SqlHandler> sqlHandler = new ThreadLocal();

        public SqlProxyHandler(Sql sql, SqlHandler handler) {
            this.sql = sql;
            setSqlHandler(handler);
        }

        public Sql getSql() {
            return sql;
        }

        public void setSqlHandler(SqlHandler handler) {
            this.sqlHandler.set(handler);
        }

        public SqlHandler getSqlHandler() {
            return sqlHandler.get();
        }

        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            Object result = method.invoke(getSql(), args);
            if ("getSql".equals(method.getName()) && getSqlHandler() != null) {
                log.debug("原SQL： " + result);
                final StatementScope statementScope = (StatementScope) args[0];
                final Object[] params = statementScope.getParameterMap().getParameterObjectValues(statementScope, args[1]);
                result = getSqlHandler().handle((String) result, params);
                log.debug("处理后： " + result);
                setSqlHandler(null);//执行完成后清除线程局部变量，下次调用需要设置新值，否则不拦截getSql方法
            }
            return result;
        }
    }

    protected static interface SqlHandler {

        /**
         * 处理sql语句
         *
         * @param sql ibatis生成的sql语句，其中参数用?号占位
         * @param params sql对应的参数
         * @return
         * @throws Throwable
         */
        String handle(String sql, Object[] params) throws Throwable;
    }
}