jpacomment-spring-boot-starter

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

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

1. Mysql
2. Sqlserver
3. 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;
    }
}

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