iabtis分页请教

李__光 发布于 2015/06/16 09:36
阅读 134
收藏 1

解读下一代网络:算力网络正从理想照进现实!>>>

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

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

加载中
0
微凉的风
微凉的风
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;
    }
}



李__光
@刘家华 楼主能加QQ么?
李__光
@刘家华 红薯是哪位?我现在的问题是: mappedStatement.setSql(proxy);这个方法没有,还有就是: public String handle(String sql, Object[] params) throws Throwable { String nsql = sqlHandler.handle(sql, params); ...
微凉的风
微凉的风
回复 @李__光 : 应该很简单吧,实在不知道可以问问 @红薯
李__光
回复 @刘家华 : 怎么给分?还是不太会用。。。/(ㄒoㄒ)/~~
微凉的风
微凉的风
回复 @李__光 : 确认一下最佳答案,给点分吧
下一页
0
微凉的风
微凉的风

代码中getSqlWithPagerLimit是oracle的例子

返回顶部
顶部