開發一個MyBatis通用Mapper的輪子
2022-12-21 12:01:48
一、前言
程式猿為什麼如此執著於造輪子？MyBatis-Plus如此強大的工具流行這麼多年了，我為啥還在重複造這樣的輪子？
1、公司的技術規範不允許使用MyBatis-Plus，咱也不知道什麼原因；
3、以前使用SpringDataJpa慣了，今年第一次用MyBatis，必須把它打造成我想要的樣子；
6、MyBatis-Plus好像不支援聯合主鍵；
7、還有一些其它的需求，比如對字典欄位自動翻譯：字典可能來自列舉、字典表、Redis......
10、通用資料許可權控制；
11、如果不造此輪子，就沒有這篇文章。
以上12點原因，便是造這個輪子的理由。實際上，輪子不重要，重要的是掌握輪子的原理，取其精華，去其糟粕。也歡迎大家拍磚，請輕拍，數學能力被誰拍壞了誰來陪。
二、需求
通用Mapper起碼應該包含以下功能：
1、增
2、刪
3、改
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;
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、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> { }
六、總結
本輪子目前基本上不值一提，但相信後面我再把字典翻譯、通用資料許可權加上的話，仍然會不值一提。
實際上輪子本身不重要，開發過程中的各種思考、試驗更重要吧。
歡迎閱讀，歡迎轉載，轉載請註明出處，求你了。