sql表字段 翻译 开发一个MyBatis通用Mapper的轮子

一、前言

程序猿为什么如此执着于造轮子？-Plus如此强大的工具流行这么多年了，我为啥还在重复造这样的轮子？

1、公司的技术规范不允许使用-Plus，咱也不知道什么原因；

3、以前使用惯了，今年第一次用，必须把它打造成我想要的样子；

6、-Plus好像不支持联合主键；

7、还有一些其它的需求，比如对字典字段自动翻译：字典可能来自枚举、字典表、Redis……

10、通用数据权限控制；

11、如果不造此轮子，就没有这篇文章。

以上12点原因，便是造这个轮子的理由。实际上，轮子不重要，重要的是掌握轮子的原理，取其精华，去其糟粕。也欢迎大家拍砖，请轻拍，数学能力被谁拍坏了谁来陪。

二、需求

通用起码应该包含以下功能：

1、增

2、删

3、改

4、批量增

5、批量删

6、只更新指定字段

7、分页查询查当前页

8、分页查询查总数

9、字典字段翻译

10、数据权限控制

大概长下面这个样子：

public interface BaseMapper {
 int insert(T t);
 int batchInsert(List entity);
 
 int deleteById(K id);
 
 int deleteBatchIds(Collection ids);
 
 int updateById(T entity);
 
 int updateSelectiveById(T entity);
 
 T selectById(K id);
 
 List selectBatchIds(Collection ids);
 
 List selectAll();
 
 List selectPage(PageRequest pageRequest);
 
 Long selectCount(T entity);
}

三、实现原理1、基于提供的构建动态Sql

例如如下代码：

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List 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，从而实现该通用。

四、代码实现1、自定义注解

1）@Table

了解Jpa的朋友一定很熟悉，这个就是为实体指定表名，实体不加这个注解就认为实体名与表名一致：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {
 //表名，不指定则使用实体类名
 String value() default "";
}

2）@

指定完表名，该指定列名了，同样的如果字段不指定则认为字段名与表列名一致：

@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

这个注解就是为了表明该字段是否是数据库主键。当然，这个注解可以与@合并，但为了更清晰，我还是决定单独使用这个注解。并且，也方便后期扩展。

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Id {
 //主键是否自动生成
 boolean auto() default false;
}

4）@

这个注解来标明查询时的排序字段，同时考虑如果排序字段有多个，可定义优先级：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface OrderBy {
 //排序
 Order order() default Order.ASC;
 //多个排序字段先后顺序
 int orderPriority() default 0;
}

5）@

考虑实体中有些字段在数据库中不存在的情况。使用这个注解来标注这样的字段：

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Transient {
}

2、几个pojo，用来保存实体对应的信息

1），表示一个实体对应的数据库表信息

public class TableInfo {
 //表对应的实体类型
 private Class entityClass;
 //表名
 private String tableName;
 //列
 private List columns;
 //是否联合主键
 private boolean isUnionId;
}

2），表示实体中的一个字段对应的数据库表字段信息

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、定义开头说的

这个的定义模仿了，它需要两个泛型，T表示实体类型，K表示主键类型。

一般情况下K为简单数据类型，比如Long，；

联合主键情况下，K为自定义的一个复杂数据类型，具体使用方法见文章最后章节。

public interface BaseMapper {
 @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 entity);
 @DeleteProvider(type = SqlProvider.class,method = "deleteById")
 int deleteById(@Param("id") K id);
 @DeleteProvider(type = SqlProvider.class,method = "deleteBatchIds")
 int deleteBatchIds(@Param("ids") Collection 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 selectBatchIds(@Param("ids") Collection ids);
 @SelectProvider(type = SqlProvider.class,method = "selectAll")
 List selectAll();
 @SelectProvider(type = SqlProvider.class,method = "selectPage")
 List selectPage(PageRequest pageRequest);
 @SelectProvider(type = SqlProvider.class,method = "selectCount")
 Long selectCount(T entity);
}

4、

public class SqlProvider {
 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 += "" +
 " " +
 " " + values +
 " " +
 "";
 sql = ""+sql+"";
 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 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 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 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、实体类转

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 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、字典字段自动翻译

简单实现思路：对需要翻译的字段加上@注解来表明这个字段需要翻译，通过AOP方式对结果数据进行增强，来将字段进行翻译更新。

此部分内容留待后续实现，同时调研一下是否还有更优雅简单的实现方式。

7、数据权限

我们先来思考一下数据权限到底要干啥？一句话来概括：查一张表的数据时在where条件中追加“and 控制权限的列 in (???)”。

简单实现方法：在控制权限的字段加上@注解来表明通过这个字段控制权限，而???的内容肯定是由业务代码来生成的，因此考虑给这个注解增加一个属性，用来指明权限数据由执行哪个接口或方法来获取。

此部分内容留待后续实现，同时调研一下是否还有更优雅简单的实现方式。

五、使用示例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;

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;
}

3、

public interface UserMapper extends BaseMapper {
}

至此，不需要写任何.xml，已经具备了增删改查能力。

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 {
}

六、总结

本轮子目前基本上不值一提，但相信后面我再把字典翻译、通用数据权限加上的话，仍然会不值一提。

实际上轮子本身不重要，开发过程中的各种思考、试验更重要吧。