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

待我称王封你为后i 2021-09-24 15:08 464阅读 0赞

### 目录 ###

*  jpacomment-spring-boot-starter
 *   *  需求说明
     *  思路说明

# jpacomment-spring-boot-starter #

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

1.  Mysql
2.  Sqlserver
3.  Oracle

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

GitHub 地址，使用查看README.md  
[https://github.com/dwhgygzt/jpacomment-spring-boot-starter][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][https_github.com_dwhgygzt_jpacomment-spring-boot-starter]

[https_github.com_dwhgygzt_jpacomment-spring-boot-starter]: https://github.com/dwhgygzt/jpacomment-spring-boot-starter