mybatis 在当前项目中的实际应用及自定义分页的实现

mybatis 在当前项目中的实际应用及自定义分页的实现

项目中分页代码的解耦

实现目标

实现目标，使用spring 提供的分页相关的类，避免代码中直接使用PageHelper

具体实现

创建自定义PageHelper，并使用spring-data-common提供的具体实现类操作分页

maven 中引入相关依赖

<dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
            <version>2.6.2</version>
</dependency>

public class PageHelper {

    //创建分页参数
    public static Pageable createPageable(int page, int size ) {
        return PageRequest.of(page,size);
    }

    //创建分页参数
    public static Pageable createPageable(int page, int size, Sort sort) {
        return PageRequest.of(page,size,sort);
    }

    public static Page createPage(List content, Pageable pageable, long total) {
        return new PageImpl(content,pageable,total);
    }

    public static Page doSelectPage(Pageable pageable,Select select) {
        com.github.pagehelper.Page page = com.github.pagehelper.PageHelper.startPage(pageable.getPageNumber(), pageable.getPageSize()).doSelectPage(() -> select.doSelect());
        return createPage(page,pageable,page.getTotal());
    }


}

public interface Select {

    void doSelect();

}

这样我们在项目中就不直接与pageHelper 打交道了，将代码控制在自定义的 PageHelper 中了。

具体应用

首先我们的controller层面的过滤bean class 统一继承 PagedRequest.class

public class PagedRequest extends Request {

    /**
     * 页号
     */
    private Integer pageNum;

    /**
     * 单页大小(rows)
     */
    private Integer pageSize;

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }
}

到controller层面 因为要调用service，所以转化为 domain对象 我们使用 spring data 提供的 domain Pageable.class

//调用我们自己的工具类创建分页对象
Pageable pageable =
                PagingHelper.createPageable(merchantAuditListReq.getPageNum(), merchantAuditListReq.getPageSize());

将 Pageable 对象和 过滤条件对象传递给service方法

 Page<MerchantAuditListResultModel> page_result = merchantService.getMerchAuditList(pageable, merchantAuditListParamModel);

service 的方法的返回值必须返回 spring data 提供的domain Page 作为返回值

service 层调用具体的查询语句需要使用mybatis 的分页插件，这里提供了一个工具类 PagingHepler.class

//select 方法有两个参数  1 分页数据  2 具体执行的查询方法
Page<MerchantInfoDetailResult> page = PagingHelper.select(pageable, () ->
                custMerchantInfoMapper.getMerchantDetailInfoByExample(merchantInfoParam));

select 方法的具体实现如下，其实还是调用page hepler 的方法来查询分页,获取到数据之后通过 createPage 来创建我们内部流传的Page domain

    public static Page select(Pageable pageable, PagingSelector pagingSelector) {
        com.github.pagehelper.Page page = PageHelper.startPage(pageable.getPageNumber() + 1, pageable.getPageSize())
                .doSelectPage(new PageHelperSelect(pagingSelector));

        return createPage(page.getResult(), pageable, page.getTotal());
    }
 public static Page createPage(List content, Pageable pageable, long total) {
        return new PageImpl(content, pageable, total);
    }

实体类中枚举的更高实现在MyBatis 中的应用

配置

实现自定义枚举类的 TypeHandler，并将枚举转换的TypeHandler 放入mybatis 的config当中.

public class TypeCodeTypeHandler<E extends Enumerable> extends BaseTypeHandler<E> {

	private Class<E> type;
	private final Map<Integer, E> enums;

	public TypeCodeTypeHandler(Class<E> type) {
		if (type == null) {
			throw new IllegalArgumentException("type argument cannot be null");
		}
	/// 是一个 Java 反射 API 方法，用于获取指定枚举类的所有枚举常量。
		E[] enumArray = type.getEnumConstants();
		Map<Integer, E> enums = new HashMap<Integer, E>();
		for (E each : enumArray) {
			enums.put(each.getCode(), each);
		}
		this.type = type;
		this.enums = enums;
	}

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, E parameter,
			JdbcType jdbcType) throws SQLException {
		ps.setInt(i, parameter.getCode());
	}

	@Override
	public E getNullableResult(ResultSet rs, String columnName)
			throws SQLException {
		int code = rs.getInt(columnName);
		if (rs.wasNull()) {
			return null;
		} else {
			try {
				return enums.get(code);
			} catch (Exception ex) {
				throw new IllegalArgumentException("Cannot convert " + code
						+ " to " + type.getSimpleName() + " by code value.", ex);
			}
		}
	}

	@Override
	public E getNullableResult(ResultSet rs, int columnIndex)
			throws SQLException {
		int code = rs.getInt(columnIndex);
		if (rs.wasNull()) {
			return null;
		} else {
			try {
				return enums.get(code);
			} catch (Exception ex) {
				throw new IllegalArgumentException("Cannot convert " + code
						+ " to " + type.getSimpleName() + " by code value.", ex);
			}
		}
	}

	@Override
	public E getNullableResult(CallableStatement cs, int columnIndex)
			throws SQLException {
		int code = cs.getInt(columnIndex);
		if (cs.wasNull()) {
			return null;
		} else {
			try {
				return enums.get(code);
			} catch (Exception ex) {
				throw new IllegalArgumentException("Cannot convert " + code
						+ " to " + type.getSimpleName() + " by code value.", ex);
			}
		}
	}
}

public SqlSessionFactory sqlSessionFactory() {
       
        try {
            List<String> packages = Lists.newArrayList();
            packages.add("com.kaffatech.mocha.common.lang.type");
            //创建对应类型的 TypeHandler
            List<TypeHandler> typeHandlers = TypeHandlerUtils.getTypeHandlerByPackages(packages);
            bean.setTypeHandlers(typeHandlers.toArray(new TypeHandler[typeHandlers.size()]));
            return bean.getObject();
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }

使用

枚举类都需要继承自Enumerable 接口

public enum ConsumeTradeStatus implements Enumerable {

    WAIT(0, "待支付"), PROCESSING(2, "支付中"), SUCCESS(3, "支付成功"),FALSE(4,"支付失败"), CLOSE(5, "已失效");

    private Integer code;
    private String description;

    ConsumeTradeStatus(Integer code, String description) {
        this.code = code;
        this.description = description;
    }


    @Override
    public int getCode() {
        return code;
    }

    @Override
    public String getCodeString() {
        return name();
    }

    @Override
    public String getDescription() {
        return description;
    }
}
public interface Enumerable {

	/**
	 * 获取代码
	 * @return
	 */
	int getCode();


	/**
	 * 获取字符型代码(一般情况应为枚举name)
	 * @return
	 */
	String getCodeString();

	/**
	 * 获取描述
	 * @return
	 */
	String getDescription();

}

如果数据库中的字段使用枚举类型，声明为 tinyInt 类型，并在代码生成器那进行属性类型覆盖EntryManualType

 <table tableName="accounting_entry_apply">
            <columnOverride column="entry_manual_type"
                            javaType="com.flashfin.flp.ms.domain.accounting.type.EntryManualType"></columnOverride>
            <columnOverride column="audit_status"
                            javaType="com.flashfin.flp.ms.domain.accounting.type.AuditStatus"></columnOverride>
            <columnOverride column="deleted" javaType="java.lang.Boolean"></columnOverride>
            <columnOverride column="create_time" javaType="java.time.Instant"></columnOverride>
            <columnOverride column="update_time" javaType="java.time.Instant"></columnOverride>
            <columnOverride column="audit_time" javaType="java.time.Instant"></columnOverride>
        </table>

关于这样做的好处

记当前项目中国际化的做法_奋斗的小面包的博客-CSDN博客

扩展

分页原理的实现

常见的分页方式有如下两种

PageHelper.startPage(1, 10);
Page<User> users = (Page<User>) userMapper.selectList("user",null);//也可以是 List<Country> list = countryMapper.selectIf(1);

这种机制的实现原理是在调用PageHelper.start 的时候创建一个Page对象放入threadLocal 中，当mybatis的插件拦截到他时获取threadLocal中的page对象，进行sql的改写，之后讲查询到的结果放入 page 对象之中进行返回，Page是派生自List的，所以这也就是解释了为什么我们使用List 接收或使用Page 进行接收都不会报错的原理。

第二种分页方式如下

Page<Country> page = PageHelper.startPage(1, 10).doSelectPage(new ISelect() {
    @Override
    public void doSelect() {
        countryMapper.selectGroupBy();
    }
});

这又是什么机理呢，其实妙处就在于Page对象的提前暴露，当我们在调用调用start 的时候会创建一个page对象，并将该对象放入ThreadLocal当中，并将Page对象返回，这就给我我们机会提前引用，调用select 的时候会执行具体的dao操作，此时就算在plugin 中删除了ThreadLocal的Page的对象，我们外接依旧是有引用的

源码展示

//pagehelper 中
public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
        Page<E> page = new Page<E>(pageNum, pageSize, count);
        page.setReasonable(reasonable);
        page.setPageSizeZero(pageSizeZero);
        //当已经执行过orderBy的时候
        Page<E> oldPage = getLocalPage();
        if (oldPage != null && oldPage.isOrderByOnly()) {
            page.setOrderBy(oldPage.getOrderBy());
        }
        setLocalPage(page);
        return page;
    }
//Page 中

 public <E> Page<E> doSelectPage(ISelect select) {
        select.doSelect();
        return (Page<E>) this;
    }

说了这么久，分页插件的大致机理已经了解了，多说无益我们直接实现一下自定义的分页插件吧

实现自定义分页 插件

@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)

public class PageHelper implements Interceptor {
    public static ThreadLocal<Pageable> pageInfos = new ThreadLocal<>();

    public static void startPage(int pageNum,int pageSize) {
        pageInfos.set(PageRequest.of(pageNum,pageSize));
    }

    public static Pageable getPage() {
        return pageInfos.get();
    }

    public static void remove() {
        pageInfos.remove();
    }


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        try {

            MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
            SqlSource sqlSource = mappedStatement.getSqlSource();
            BoundSql boundSql;
            if (invocation.getArgs().length == 6) {
                boundSql = (BoundSql) invocation.getArgs()[5];
            } else {
                boundSql = mappedStatement.getBoundSql(invocation.getArgs()[1]);
            }

            Object parameterObject = invocation.getArgs()[1];
            //判读是否需要分页,判断的逻辑就是PageHelper 中是否有page信息
            Pageable page = PageHelper.getPage();
            if (page != null) {
                //查询总的total
                String sql = boundSql.getSql();
                String prefix = "Select count(0) ";
                int from = sql.indexOf("from");
                String countSql = prefix + sql.substring(from, sql.length());
                Executor executor = (Executor) invocation.getTarget();

                ResultMap resultMap = new ResultMap.Builder(mappedStatement.getConfiguration(), mappedStatement.getId() + "count", Integer.class, Collections.EMPTY_LIST, true).build();

                StaticSqlSource countBoundSql = new StaticSqlSource(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings());

                MappedStatement countMappedStatement = new MappedStatement.Builder(mappedStatement.getConfiguration(), mappedStatement.getId() + "count", countBoundSql, mappedStatement.getSqlCommandType())
                        .cache(mappedStatement.getCache())
                        .databaseId(mappedStatement.getDatabaseId())
                        .fetchSize(mappedStatement.getFetchSize())
                        .statementType(mappedStatement.getStatementType())
                        .resultMaps(Arrays.asList(resultMap)).build();

                List<Object> query = executor.query(countMappedStatement,parameterObject,(RowBounds)invocation.getArgs()[2],(ResultHandler)invocation.getArgs()[3]);
                int total = (int) query.get(0);
                //分页插叙
                int pageSize = page.getPageSize();
                int pageNumber = page.getPageNumber();

                String selectSql = boundSql.getSql();
                selectSql = selectSql + " " + "limit " + (pageNumber - 1) * pageSize + "," + pageSize;

                BoundSql selectBoundSql = new BoundSql(mappedStatement.getConfiguration(), selectSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
                //进行查询操作
                Object proceed = executor.query(mappedStatement,parameterObject,(RowBounds)invocation.getArgs()[2],(ResultHandler)invocation.getArgs()[3],(CacheKey) invocation.getArgs()[4],selectBoundSql);
                Page ret = new Page(pageNumber, pageSize);
                ret.setTotal(total);
                ret.addAll((Collection) proceed);
                return ret;
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            //清除分页信息
            PageHelper.remove();
        }
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target,this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

@Configuration
public class PageHelperConfig implements InitializingBean {
    @Autowired
    private SqlSessionFactory sqlSessionFactory;


    public void afterPropertiesSet() throws Exception {
        PageHelper interceptor = new PageHelper();
        interceptor.setProperties(null);
        sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
    }
}

ok ok ok 目前先写到这里，本文档持续更新ing