3
开发一个MyBatis通用Mapper的轮子 - 失足成万古风流人物
source link: https://www.cnblogs.com/blogtimes/p/16994250.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
开发一个MyBatis通用Mapper的轮子
程序猿为什么如此执着于造轮子？MyBatis-Plus如此强大的工具流行这么多年了，我为啥还在重复造这样的轮子？
1、公司的技术规范不允许使用MyBatis-Plus，咱也不知道什么原因；
3、以前使用SpringDataJpa惯了，今年第一次用MyBatis，必须把它打造成我想要的样子；
6、MyBatis-Plus好像不支持联合主键；
7、还有一些其它的需求，比如对字典字段自动翻译：字典可能来自枚举、字典表、Redis......
10、通用数据权限控制；
11、如果不造此轮子，就没有这篇文章。
以上12点原因，便是造这个轮子的理由。实际上，轮子不重要，重要的是掌握轮子的原理，取其精华，去其糟粕。也欢迎大家拍砖，请轻拍，数学能力被谁拍坏了谁来陪。
通用Mapper起码应该包含以下功能：
4、批量增
5、批量删
6、只更新指定字段
7、分页查询查当前页
8、分页查询查总数
9、字典字段翻译
10、数据权限控制
大概长下面这个样子：
public interface BaseMapper<T,K> {
int insert(T t);
int batchInsert(List<T> entity);
int deleteById(K id);
int deleteBatchIds(Collection<K> ids);
int updateById(T entity);
int updateSelectiveById(T entity);
T selectById(K id);
List<T> selectBatchIds(Collection<K> ids);
List<T> selectAll();
List<T> selectPage(PageRequest<T> pageRequest);
Long selectCount(T entity);
}
三、实现原理
1、基于MyBatis3提供的SqlProvider构建动态Sql
例如如下代码：
@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(String name);
class UserSqlBuilder {
public static String buildGetUsersByName(final String name) {
return new SQL(){{
SELECT("*");
FROM("users");
if (name != null) {
WHERE("name like #{value} || '%'");
}
ORDER_BY("id");
}}.toString();
}
}
2、基于自定义注解，为实体和数据库表建立对应关系
例如如下代码：
@Table("user")
public class User {
@Id(auto = true)
@Column(value = "id")
private Long id;
@Column(value = "name", filterOperator = FilterOperator.LIKE)
@OrderBy(orderPriority = 0)
private String name;
@OrderBy(order = Order.DESC, orderPriority = 1)
private Integer age;
private String email;
@Transient
private String test;
}
基于以上两个原理，当方法被调用时，我们便可构建出相应的动态Sql，从而实现该通用Mapper。
四、代码实现
1、自定义注解
1）@Table
了解Jpa的朋友一定很熟悉，这个就是为实体指定表名，实体不加这个注解就认为实体名与表名一致：
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
//表名，不指定则使用实体类名
String value() default "";
}
2）@Column
指定完表名，该指定列名了，同样的如果字段不指定则认为字段名与表列名一致：
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Column {
//对应数据库列名
String value() default "";
//查询时的过滤类型
FilterOperator filterOperator() default FilterOperator.EQ;
//是否查询，select是否带上该字段
boolean selectable() default true;
//是否插入，insert是否带上该字段
boolean insertable() default true;
//是否更新，update是否带上该字段
boolean updatable() default true;
}
3）@Id
这个注解就是为了表明该字段是否是数据库主键。当然，这个注解可以与@Column合并，但为了更清晰，我还是决定单独使用这个注解。并且，也方便后期扩展。
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Id {
//主键是否自动生成
boolean auto() default false;
}
4）@OrderBy
这个注解来标明查询时的排序字段，同时考虑如果排序字段有多个，可定义优先级：
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface OrderBy {
//排序
Order order() default Order.ASC;
//多个排序字段先后顺序
int orderPriority() default 0;
}
5）@Transient
考虑实体中有些字段在数据库中不存在的情况。使用这个注解来标注这样的字段：
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Transient {
}
2、几个pojo，用来保存实体对应的信息
1）TableInfo，表示一个实体对应的数据库表信息
public class TableInfo {
//表对应的实体类型
private Class<?> entityClass;
//表名
private String tableName;
//列
private List<ColumnInfo> columns;
//是否联合主键
private boolean isUnionId;
}
2）ColumnInfo，表示实体中的一个字段对应的数据库表字段信息
public class ColumnInfo {
//对应的java类型
private Class<?> fieldClass;
private Field field;
private FilterOperator filterOperator;
//数据库列
private String column;
//是否主键
private boolean isPrimaryKey;
//主键填充方式
private boolean isPrimaryKeyAuto;
//排序
private Order orderBy;
private int orderByPriority;
//是否参与insert
private boolean insertable;
//是否参与update
private boolean updatable;
//是否参与select
private boolean selectable;
}
以上只需要注意一点，如何判断一个实体是否是联合主键。这里用的比较粗暴的方法，如果有多个字段加了@Id，那么认为是联合主键。
3、定义开头说的BaseMapper
这个BaseMapper的定义模仿了SpringDataJpa，它需要两个泛型，T表示实体类型，K表示主键类型。
一般情况下K为简单数据类型，比如Long，String；
联合主键情况下，K为自定义的一个复杂数据类型，具体使用方法见文章最后章节。
public interface BaseMapper<T,K> {
@InsertProvider(type = SqlProvider.class,method = "insert")
@Options(useGeneratedKeys = true, keyProperty = "id",keyColumn = "id")
int insert(T t);
@InsertProvider(type = SqlProvider.class,method = "batchInsert")
int batchInsert(@Param("list") List<T> entity);
@DeleteProvider(type = SqlProvider.class,method = "deleteById")
int deleteById(@Param("id") K id);
@DeleteProvider(type = SqlProvider.class,method = "deleteBatchIds")
int deleteBatchIds(@Param("ids") Collection<K> ids);
@UpdateProvider(type = SqlProvider.class,method = "updateById")
int updateById(T entity);
@UpdateProvider(type = SqlProvider.class,method = "updateSelectiveById")
int updateSelectiveById(T entity);
@SelectProvider(type = SqlProvider.class,method = "selectById")
T selectById(@Param("id") K id);
@SelectProvider(type = SqlProvider.class,method = "selectBatchIds")
List<T> selectBatchIds(@Param("ids") Collection<K> ids);
@SelectProvider(type = SqlProvider.class,method = "selectAll")
List<T> selectAll();
@SelectProvider(type = SqlProvider.class,method = "selectPage")
List<T> selectPage(PageRequest<T> pageRequest);
@SelectProvider(type = SqlProvider.class,method = "selectCount")
Long selectCount(T entity);
}
4、SqlProvider
public class SqlProvider<T> {
private static Logger logger = LoggerFactory.getLogger(SqlProvider.class);
private static Map<Class<?>, TableInfo> tableCache = new ConcurrentHashMap<>();
public String insert(T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
String intoColumns = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isInsertable)
.map(ColumnInfo::getColumn)
.collect(Collectors.joining(","));
String values = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isInsertable)
.map(ColumnInfo::variable)
.collect(Collectors.joining(","));
String sql = new SQL()
.INSERT_INTO(tableName)
.INTO_COLUMNS(intoColumns)
.INTO_VALUES(values).toString();
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String batchInsert(@Param("list" ) List<?> entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
String intoColumns = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isInsertable)
.map(ColumnInfo::getColumn)
.collect(Collectors.joining(","));
String values = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isInsertable)
.map(column->column.variableWithPrefix("item"))
.collect(Collectors.joining(","));
String sql = new SQL()
.INSERT_INTO(tableName)
.INTO_COLUMNS(intoColumns).toString();
sql += " values ";
sql += "<foreach collection=\"list\" item=\"item\" separator=\",\">" +
" <trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">" +
" " + values +
" </trim>" +
"</foreach>";
sql = "<script>"+sql+"</script>";
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String deleteById(@Param("id") T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
String[] where = null;
if (tableInfo.isUnionId()){
where = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = #{id."+columnInfo.getField().getName()+"}")
.toArray(String[]::new);
}else {
where = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = #{id}")
.toArray(String[]::new);
}
String sql = new SQL()
.DELETE_FROM(tableName)
.WHERE(where)
.toString();
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String deleteBatchIds(@Param("ids") Collection<?> entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
if (tableInfo.isUnionId()){
String[] where = new String[entity.size()];
for (int i = 0; i < entity.size(); i++){
List<String> list = new ArrayList<>();
String s = "%s=#{ids[%d].%s}";
for (ColumnInfo columnInfo:tableInfo.getColumns()){
if (columnInfo.isPrimaryKey()){
list.add(String.format(s,columnInfo.getColumn(),i,columnInfo.getField().getName()));
}
}
where[i] = "("+StringUtils.join(list," and ")+")";
}
String sql = "delete from %s where %s ";
sql = String.format(sql,tableName,StringUtils.join(where," or "));
logger.info("sql->{},params->{}",sql,entity);
return sql;
}else {
String idName = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.findFirst()
.get()
.getColumn();
String sql = "DELETE FROM %s WHERE %s IN (%s) ";
String[] arr = new String[entity.size()];
for (int i = 0; i < entity.size(); i++){
arr[i] = "#{ids["+i+"]}";
}
sql = String.format(sql,tableName,idName,StringUtils.join(arr,","));
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
}
public String updateById(T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
String[] where = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = "+columnInfo.variable())
.toArray(String[]::new);
String sql = new SQL().UPDATE(tableName).SET(tableInfo.updateSetColumn()).WHERE(where).toString();
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String updateSelectiveById(T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
String[] where = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = "+columnInfo.variable())
.toArray(String[]::new);
String sql = new SQL().UPDATE(tableName).SET(tableInfo.updateSetSelectiveColumn(entity)).WHERE(where).toString();
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String selectById(@Param("id")T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String[] where = null;
if (tableInfo.isUnionId()){
where = tableInfo.getColumns().stream().filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = #{id."+columnInfo.getField().getName()+"}")
.toArray(String[]::new);
}else {
where = tableInfo.getColumns().stream().filter(ColumnInfo::isPrimaryKey)
.map(columnInfo -> columnInfo.getColumn()+" = #{id}")
.toArray(String[]::new);
}
String sql = new SQL()
.SELECT(tableInfo.selectColumnAsProperty())
.FROM(tableInfo.getTableName())
.WHERE(where)
.toString();
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
public String selectBatchIds(@Param("ids")Collection<?> entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
String tableName = tableInfo.getTableName();
if (tableInfo.isUnionId()){
String[] where = new String[entity.size()];
for (int i = 0; i < entity.size(); i++){
List<String> list = new ArrayList<>();
String s = "%s=#{ids[%d].%s}";
for (ColumnInfo columnInfo:tableInfo.getColumns()){
if (columnInfo.isPrimaryKey()){
list.add(String.format(s,columnInfo.getColumn(),i,columnInfo.getField().getName()));
}
}
where[i] = "("+StringUtils.join(list," and ")+")";
}
String sql = "select %s from %s where %s";
sql = String.format(sql,tableInfo.selectColumnAsProperty(),tableInfo.getTableName(),StringUtils.join(where," or "));
logger.info("sql->{},params->{}",sql,entity);
return sql;
}else {
String idName = tableInfo.getColumns()
.stream()
.filter(ColumnInfo::isPrimaryKey)
.findFirst()
.get()
.getColumn();
String sql = "select %s from %s where %s in (%s) ";
String[] arr = new String[entity.size()];
for (int i = 0; i < entity.size(); i++){
arr[i] = "#{ids["+i+"]}";
}
sql = String.format(sql,tableInfo.selectColumnAsProperty(),tableName,idName,StringUtils.join(arr,","));
logger.info("sql->{},params->{}",sql,entity);
return sql;
}
}
public String selectAll(T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
SQL sql = new SQL()
.SELECT(tableInfo.selectColumnAsProperty())
.FROM(tableInfo.getTableName());
String orderBy = tableInfo.orderByColumn();
if (StringUtils.isNotEmpty(orderBy)){
sql.ORDER_BY(orderBy);
}
return sql.toString();
}
public String selectPage(PageRequest<T> entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
SQL sql = new SQL()
.SELECT(tableInfo.selectColumnAsProperty())
.FROM(tableInfo.getTableName());
String[] where = tableInfo.getColumns().stream()
.filter(column -> {
Field field = column.getField();
T bean = entity.getPageParams();
Object value = Util.getFieldValue(bean, field);
if (value == null) {
return false;
}
return StringUtils.isNotEmpty(value.toString());
})
.map(column -> {
String param = " #{pageParams." + column.getField().getName()+"}";
if (column.getFilterOperator() == FilterOperator.LIKE){
param = "concat('%', "+param+", '%')";
}
if (column.getFilterOperator() == FilterOperator.LEFTLIKE){
param = "concat("+param+", '%')";
}
if (column.getFilterOperator() == FilterOperator.RIGHTLIKE){
param = "concat('%', "+param+")";
}
return column.getColumn()+column.filterOperator()+param;
})
.toArray(String[]::new);
sql.WHERE(where);
if (StringUtils.isNotEmpty(entity.getOrder())){
ColumnInfo columnInfo = tableInfo.getColumns().stream()
.filter(columnInfo1 -> columnInfo1.getField().getName().equalsIgnoreCase(entity.getOrder()))
.findFirst().orElse(null);
if (columnInfo != null){
String direction = entity.getOrderDirection();
direction = (StringUtils.isEmpty(direction) || direction.equalsIgnoreCase("asc"))?" asc ":" desc ";
sql.ORDER_BY(columnInfo.getColumn() + direction);
}
}else {
String orderBy = tableInfo.orderByColumn();
if (StringUtils.isNotEmpty(orderBy)){
sql.ORDER_BY(orderBy);
}
}
sql.OFFSET("#{offset}").LIMIT("#{pageSize}");
String s = sql.toString();
logger.info("sql->{},params->{}",s,entity);
return s;
}
public String selectCount(T entity, ProviderContext context){
TableInfo tableInfo = getTableInfo(context);
SQL sql = new SQL()
.SELECT("count(1)")
.FROM(tableInfo.getTableName());
String[] where = tableInfo.getColumns().stream()
.filter(column -> {
Field field = column.getField();
Object value = Util.getFieldValue(entity, field);
if (value == null) {
return false;
}
return StringUtils.isNotEmpty(value.toString());
})
.map(column -> {
String param = " #{" + column.getField().getName()+"}";
if (column.getFilterOperator() == FilterOperator.LIKE){
param = "concat('%', "+param+", '%')";
}
if (column.getFilterOperator() == FilterOperator.LEFTLIKE){
param = "concat("+param+", '%')";
}
if (column.getFilterOperator() == FilterOperator.RIGHTLIKE){
param = "concat('%', "+param+")";
}
return column.getColumn()+column.filterOperator()+param;
})
.toArray(String[]::new);
sql.WHERE(where);
String s = sql.toString();
logger.info("sql->{},params->{}",s,entity);
return s;
}
private TableInfo getTableInfo(ProviderContext context){
Class<?> clz = getEntityType(context);
return tableCache.computeIfAbsent(context.getMapperType(), t-> Util.tableInfo(clz));
}
private Class<?> getEntityType(ProviderContext context) {
return Stream.of(context.getMapperType().getGenericInterfaces())
.filter(ParameterizedType.class::isInstance)
.map(ParameterizedType.class::cast)
.filter(type -> type.getRawType() == BaseMapper.class)
.findFirst()
.map(type -> type.getActualTypeArguments()[0])
.filter(Class.class::isInstance)
.map(Class.class::cast)
.orElseThrow(() -> new IllegalStateException("未找到BaseMapper的泛型类 " + context.getMapperType().getName() + "."));
}
}
5、实体类转TableInfo
public static TableInfo tableInfo(Class<?> entityClass) {
TableInfo info = new TableInfo();
info.setEntityClass(entityClass);
Table table = entityClass.getAnnotation(Table.class);
String tableName = entityClass.getSimpleName();
if (table != null && StringUtils.isNotEmpty(table.value())){
tableName = table.value();
}
info.setTableName(tableName);
Field[] allFields = getFields(entityClass);
Field[] fields = Stream.of(allFields)
//过滤@Transient注解的field
.filter(field -> !field.isAnnotationPresent(Transient.class))
.toArray(Field[]::new);
List<ColumnInfo> columns = new ArrayList<>();
int idCount = 0;
for (Field field:fields){
ColumnInfo columnInfo = new ColumnInfo();
columnInfo.setFieldClass(field.getDeclaringClass());
columnInfo.setField(field);
Id id = field.getAnnotation(Id.class);
idCount = idCount + (id == null?0:1);
columnInfo.setPrimaryKey(id == null?Boolean.FALSE:Boolean.TRUE);
columnInfo.setPrimaryKeyAuto(id == null?Boolean.FALSE:id.auto());
Column column = field.getAnnotation(Column.class);
String columnName = field.getName();
if (column != null && StringUtils.isNotEmpty(column.value())){
columnName = column.value();
}
columnInfo.setColumn(columnName);
FilterOperator filterOperator = FilterOperator.EQ;
if (column != null && column.filterOperator() != null){
filterOperator = column.filterOperator();
}
columnInfo.setFilterOperator(filterOperator);
if (columnInfo.isPrimaryKeyAuto()){
columnInfo.setInsertable(false);
}else {
columnInfo.setInsertable(true);
if (column != null){
columnInfo.setInsertable(column.insertable());
}
}
columnInfo.setUpdatable(true);
columnInfo.setSelectable(true);
if (column != null){
columnInfo.setSelectable(column.selectable());
columnInfo.setUpdatable(column.updatable());
}
OrderBy orderBy = field.getAnnotation(OrderBy.class);
if (orderBy != null){
columnInfo.setOrderBy(orderBy.order());
columnInfo.setOrderByPriority(orderBy.orderPriority());
}
columns.add(columnInfo);
}
if (idCount > 1){
info.setUnionId(Boolean.TRUE);
}
info.setColumns(columns);
return info;
}
6、字典字段自动翻译
简单实现思路：对需要翻译的字段加上@FieldTrans注解来表明这个字段需要翻译，通过AOP方式对结果数据进行增强，来将字段进行翻译更新。
此部分内容留待后续实现，同时调研一下是否还有更优雅简单的实现方式。
7、数据权限
我们先来思考一下数据权限到底要干啥？一句话来概括：查一张表的数据时在where条件中追加“and 控制权限的列 in (???)”。
简单实现方法：在控制权限的字段加上@DataAuthrity注解来表明通过这个字段控制权限，而???的内容肯定是由业务代码来生成的，因此考虑给这个注解增加一个属性，用来指明权限数据由执行哪个接口或方法来获取。
此部分内容留待后续实现，同时调研一下是否还有更优雅简单的实现方式。
五、使用示例
1、数据库表
CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
@Table("user")
public class User {
@Id(auto = true)
@Column(value = "id")
private Long id;
@Column(value = "name", filterOperator = FilterOperator.LIKE)
@OrderBy(orderPriority = 0)
private String name;
@OrderBy(order = Order.DESC, orderPriority = 1)
private Integer age;
private String email;
@Transient
private String test;
}
3、Mapper
public interface UserMapper extends BaseMapper<User, Long> {
}
至此，不需要写任何mapper.xml，UserMapper已经具备了增删改查能力。
4、联合主键示例
public class User1 {
@Id
@Column(value = "id1")
private String id1;
@Id
@Column(value = "id2")
private String id2;
@Column(value = "name", filterOperator = FilterOperator.LIKE)
@OrderBy(orderPriority = 0)
private String name;
@OrderBy(order = Order.DESC, orderPriority = 1)
private Integer age;
private String email;
@Transient
private String test;
}
public class User1Id {
private String id1;
private String id2;
}
public interface User1Mapper extends BaseMapper<User1,User1Id> {
}
本轮子目前基本上不值一提，但相信后面我再把字典翻译、通用数据权限加上的话，仍然会不值一提。
实际上轮子本身不重要，开发过程中的各种思考、试验更重要吧。
欢迎阅读，欢迎转载，转载请注明出处，求你了。
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK