jpacomment-spring-boot-starter

JPA创建或修改数据库的表注释和字段注释

jpacomment-spring-boot-starter 使用非常简单，目前支持三种主流关系型数据库：

Mysql
Sqlserver
Oracle

后期可以增加其他数据库。

GitHub 地址，使用查看README.md
https://github.com/dwhgygzt/jpacomment-spring-boot-starter

需求说明

JPA 比较方便，让开发免于手动创建表操作，但有一个问题表中字段无注释，虽然JPA有提供方法，但无法适应所有主流数据库。
JPA 自身提供方法如下：

public class MyEntity { 
 @Column(nullable = false,columnDefinition = "int(2) comment '我是年龄注释...'")
 private Integer age;
}

其中 columnDefinition 其实就是写 Native Sql，这样违背了JPA的初衷“屏蔽底层数据库差异”，
因此我们计划自己写一套解决方案。

思路说明

自定义注解 @TableComment 和 @ColumnComment 到实体类和其属性上
通过 JPA EntityManager 获取java 属性对应的数据库表和字段
调用 SQL Alter 语句修改字段和表注释

自定义注解 @TableComment 和 @ColumnComment 到实体类和其属性上

@Target({ ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ColumnComment { 
    /** * 字段注释 * * @return String */
    String value() default "";
}
@Target({ ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface TableComment { 
    /** * 字段注释 * * @return String */
    String value() default "";
}
@Data
@Entity
@Table(name = "T_SYS_PEOPLE")
@TableComment("普通人员表")
public class SysPeopleEntity implements Serializable { 
    private static final long serialVersionUID = 1L;
    @EmbeddedId
    @ColumnComment("联合主键 NAME ID_NUMBER")
    private PeopleKey id;
    @ColumnComment("年龄")
    @Column(name = "AGE")
    private int age;
    @ColumnComment("地址")
    @Column(name = "ADDRESS")
    private String address;
    @ColumnComment("乐观锁版本号")
    @Version
    @Column(name = "RVERSION", nullable = false)
    private Integer rversion;
    @ColumnComment("创建时间")
    @Column(name = "CREATE_DATE", nullable = false)
    private Date createDate;
    @ColumnComment("创建人员信息")
    @Column(name = "CREATE_USER", nullable = false)
    private String createUser;
    @ColumnComment("更新时间")
    @Column(name = "UPADATE_DATE")
    private Date updateDate;
    @ColumnComment("更新人员信息")
    @Column(name = "UPDATE_USER")
    private String updateUser;
    @ColumnComment("备注")
    @Column(name = "REMARK", length = 1024)
    private String remark;
}

通过 JPA EntityManager 获取java 属性对应的数据库表和字段

public Map<String, TableCommentDTO> findAllTableAndColumn() { 
        Map<String, TableCommentDTO> tableCommentMap = new HashMap<>(256);
        //通过EntityManager获取factory
        EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) entityManagerFactory.unwrap(SessionFactory.class);
        Map<String, EntityPersister> persisterMap = sessionFactory.getMetamodel().entityPersisters();
        for (Map.Entry<String, EntityPersister> entity : persisterMap.entrySet()) { 
            SingleTableEntityPersister persister = (SingleTableEntityPersister) entity.getValue();
            Class targetClass = entity.getValue().getMappedClass();
            TableCommentDTO table = new TableCommentDTO();
            // 表注释
            getTableInfo(persister, table, targetClass);
            //除主键外的属性注释
            getColumnInfo(persister, table, targetClass);
            // 主键字段注释
            getKeyColumnInfo(persister, table, targetClass);
            tableCommentMap.put(table.getName(), table);
        }
        return tableCommentMap;
    }
    @SuppressWarnings("rawtypes")
    private void getTableInfo(SingleTableEntityPersister persister, TableCommentDTO table, Class targetClass) { 
        table.setColumnCommentDTOList(new ArrayList<>(32));
        table.setName(persister.getTableName());
        TableComment tableComment = AnnotationUtil.getAnnotation(targetClass, TableComment.class);
        if (tableComment != null) { 
            table.setComment(tableComment.value());
        } else { 
            table.setComment("");
        }
    }
    /** * 递归获取所有父类的类对象 包括自己 * 最后的子类在第一个 * * @param targetClass targetClass * @param list list */
    @SuppressWarnings("rawtypes")
    private void getAllClass(Class targetClass, List<Class> list) { 
        list.add(targetClass);
        if (!Object.class.equals(targetClass.getSuperclass())) { 
            getAllClass(targetClass.getSuperclass(), list);
        }
    }
    @SuppressWarnings("rawtypes")
    private void getColumnInfo(SingleTableEntityPersister persister, TableCommentDTO table, Class targetClass) { 
        // 情况比较复杂，必须还要判断是否有父类，存在父类则还要取父类的字段信息，优先取得子类字段为依据
        List<Class> classList = new ArrayList<>(2);
        getAllClass(targetClass, classList);
        Set<String> alreadyDealField = new HashSet<>(32);
        Set<String> allColumnField = new HashSet<>(32);
        Iterable<AttributeDefinition> attributes = persister.getAttributes();
        //属性
        for (AttributeDefinition attr : attributes) { 
            allColumnField.add(attr.getName());
        }
        classList.forEach(classItem -> Arrays.stream(ClassUtil.getDeclaredFields(classItem)).forEach(field -> { 
            if (allColumnField.contains(field.getName())) { 
                // 判断是否已经处理过
                if (!alreadyDealField.contains(field.getName())) { 
                    //对应数据库表中的字段名
                    String[] columnName = persister.getPropertyColumnNames(field.getName());
                    getColumnComment(table, classItem, field.getName(), columnName);
                    alreadyDealField.add(field.getName());
                }
            }
        }));
    }
    @SuppressWarnings("rawtypes")
    private void getKeyColumnInfo(SingleTableEntityPersister persister, TableCommentDTO table, Class targetClass) { 
        String idName = persister.getIdentifierPropertyName();
        String[] idColumns = persister.getIdentifierColumnNames();
        getColumnComment(table, targetClass, idName, idColumns);
    }
    @SuppressWarnings("rawtypes")
    private void getColumnComment(TableCommentDTO table, Class targetClass, String propertyName, String[] columnName) { 
        ColumnComment idColumnComment = AnnotationUtil.getAnnotation(
                ClassUtil.getDeclaredField(targetClass, propertyName), ColumnComment.class);
        Arrays.stream(columnName).forEach(item -> { 
            ColumnCommentDTO column = new ColumnCommentDTO();
            column.setName(item);
            if (idColumnComment != null) { 
                column.setComment(idColumnComment.value());
            } else { 
                column.setComment("");
            }
            table.getColumnCommentDTOList().add(column);
        });
    }

调用 SQL Alter 语句修改字段和表注释

public interface AlterCommentService { 
    /** * 获取当前数据库 schema * * @return 数据库 schema */
    String getSchema();
    /** * 设置当前的schema */
    void setSchema(String schema);
    /** * 修改表注释 * * @param tableName 表名称 * @param tableComment 表注释 */
    void alterTableComment(String tableName, String tableComment);
    /** * 修改表字段注释 * * @param tableName 表名称 * @param columnName 字段名称 * @param columnComment 字段注释 */
    void alterColumnComment(String tableName, String columnName, String columnComment);
    /** * 获取 jdbcTemplate * * @param jdbcTemplate jdbcTemplate */
    void setJdbcTemplate(JdbcTemplate jdbcTemplate);
}

各数据具体实现继承该接口实现自己的方法

例如Mysql的

public class MysqlAlterCommentServiceImpl implements AlterCommentService { 
    private String schema;
    private JdbcTemplate jdbcTemplate;
    @Override
    public void setSchema(String schema) { 
        this.schema = schema;
    }
    @Override
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
        this.jdbcTemplate = jdbcTemplate;
    }
    /** * 1注释 2表名称 3字段名称 */
    String updateTableComment = " ALTER TABLE %s.%s COMMENT = ?";
    /** * 1注释 2表名称 3字段名称 */
    String getUpdateColumnComment = " SELECT CONCAT('ALTER TABLE `',a.TABLE_SCHEMA,'`.`',a.TABLE_NAME,'` MODIFY COLUMN `',a.COLUMN_NAME,'` ',a.COLUMN_TYPE,\n" +
            " (CASE WHEN a.IS_NULLABLE = 'NO' THEN ' NOT NULL ' ELSE\t'' END), \n" +
            " (CASE WHEN a.COLUMN_DEFAULT IS NOT NULL THEN CONCAT(' DEFAULT ''',a.COLUMN_DEFAULT,''' ') ELSE\t'' END) ,' COMMENT ?') ALTER_SQL\n" +
            "FROM information_schema.`COLUMNS` a\n" +
            "WHERE a.TABLE_SCHEMA = ? \n" +
            "AND a.TABLE_NAME = ?\n" +
            "AND a.COLUMN_NAME = ? ";
    @Override
    public void alterTableComment(String tableName, String tableComment) { 
        jdbcTemplate.update(String.format(updateTableComment, "`" + schema + "`", "`" + tableName + "`"), tableComment);
    }
    @Override
    public void alterColumnComment(String tableName, String columnName, String columnComment) { 
        String updateColumnComment = jdbcTemplate.queryForObject(getUpdateColumnComment, String.class, schema, tableName, columnName);
        if (StrUtil.isNotBlank(updateColumnComment)) { 
            jdbcTemplate.update(updateColumnComment, columnComment);
        }
    }
    @Override
    public String getSchema() { 
        return schema;
    }
}