根据mybatis plus注解动态创建sqlite表和表字段

根据mybatis plus注解动态创建sqlite表和表字段

启动时动态创建sqlite数据库，根据mybatis plus注解动态创建表。如果有新增字段，动态创建字段。

---

---

一、初始化数据库

1.系统启动时初始化数据库

通过@PostConstruct注解在项目启动时调用初始化方法

   @PostConstructpublic void init() throws SQLException, IOException {//初始化数据库createDatabase();//初始化数据库表createTables();}

2.初始化sqlite数据库文件

sqlite放在外部目录下，如果放在resource目录下。打成jar后不好管理和读取。

    @ApiModelProperty("数据源地址")@Value("${spring.datasource.url}")private String sqliteDbPath;/*** 初始化数据库*/private void createDatabase() throws IOException {String sqlite = sqliteDbPath.substring("jdbc:sqlite:".length(), sqliteDbPath.indexOf("?"));File audioStationDbFile = new File(sqlite);log.info("sqlite数据库文件地址:" + audioStationDbFile.getAbsolutePath());if (!audioStationDbFile.getParentFile().exists()) {audioStationDbFile.getParentFile().mkdirs();}if (!audioStationDbFile.exists()) {audioStationDbFile.createNewFile();}}

3.根据mybatis plus注解初始化数据库

    /*** 初始化表*/private void createTables() throws SQLException {List<String> tables = jdbcTemplate.queryForList("SELECT name FROM sqlite_master ", String.class);log.info("已存在的数据库:" + tables);List<String> domainTableNames = new ArrayList<String>();Map<String, Class> classMap = new HashMap<String, Class>();//spring工具类，可以获取指定路径下的全部类ResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();try {String pattern = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX +ClassUtils.convertClassNameToResourcePath(DOMAIN_PACKAGE) + "/*.class";Resource[] resources = resourcePatternResolver.getResources(pattern);//MetadataReader 的工厂类MetadataReaderFactory readerfactory = new CachingMetadataReaderFactory(resourcePatternResolver);for (Resource resource : resources) {//用于读取类信息MetadataReader reader = readerfactory.getMetadataReader(resource);//扫描到的classString classname = reader.getClassMetadata().getClassName();Class<?> clazz = Class.forName(classname);//判断是否有指定主解TableName anno = clazz.getAnnotation(TableName.class);if (anno != null) {//将注解中的类型值作为key，对应的类作为 valuedomainTableNames.add(anno.value());classMap.put(anno.value(), clazz);}}} catch (IOException | ClassNotFoundException e) {e.printStackTrace();}
//        log.info("实体类表清单:" + domainTableNames);for (String tableName : domainTableNames) {if (!tables.contains(tableName)) {log.info("数据库[" + tableName + "]不存在，正在创建");String createTableSql = MyBatisPlusSuppotSqliteInit.getInstance().createTable(classMap.get(tableName));jdbcTemplate.update(createTableSql);} else {//存在表，检查字段是否存在List<Map<String, Object>> sqliteTableMapList = jdbcTemplate.queryForList("PRAGMA  table_info(" + tableName + ")");List<SqliteTableStructureDto> sqliteTableStructureDto = new ArrayList<>();for (Map<String, Object> map : sqliteTableMapList) {SqliteTableStructureDto dto = new SqliteTableStructureDto();BeanUtil.copyProperties(map, dto);dto.setDfltValue(null != map.get("dflt_value") ? String.valueOf(map.get("dflt_value")) : null);dto.setNotNull("1".equals(map.get("notnull")) ? true : false);dto.setPk("1".equals(map.get("pk")) ? true : false);sqliteTableStructureDto.add(dto);}sqliteTableMapList = null;List<String> createFieldSqlList = MyBatisPlusSuppotSqliteInit.getInstance().createField(JsMobileUser.class, sqliteTableStructureDto);String createFieldSql = createFieldSqlList.stream().collect(Collectors.joining(";\n"));jdbcTemplate.update(createFieldSql);}}//初始化参数int userCount = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM js_mobile_user", Integer.class);if (userCount <= 0) {jdbcTemplate.update("INSERT INTO `xxx`(`xxx`) " +" VALUES ('xxx'");}}

@Data
@NoArgsConstructor
@ApiModel("sqlite表结构字段")
public class SqliteTableStructureDto {@ApiModelProperty("主键id")private String cid;@ApiModelProperty("字段名称")private String name;@ApiModelProperty("字段映射")private String type;@ApiModelProperty("是否允许为空")private boolean notNull;@ApiModelProperty("默认值")private String dfltValue;@ApiModelProperty("是否为主键")private boolean pk;}

4.解析mybatis plus注解提取数据库信息

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.faker.audioStation.model.dto.ModelField;
import com.faker.audioStation.model.dto.SqliteTableStructureDto;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;/*** <p>MyBatisPlus支持sqlite初始化建表</p>*/
@Slf4j
public class MyBatisPlusSuppotSqliteInit {/*** 单里模式*/private static MyBatisPlusSuppotSqliteInit myBatisPlusSuppotSqliteInit = null;/*** 私有化构造函数，使用getInstance()去获取实例*/private MyBatisPlusSuppotSqliteInit() {}/*** 获取实例** @return*/public static MyBatisPlusSuppotSqliteInit getInstance() {if (null == myBatisPlusSuppotSqliteInit) {myBatisPlusSuppotSqliteInit = new MyBatisPlusSuppotSqliteInit();}return myBatisPlusSuppotSqliteInit;}@Data@ApiModel("mybatisPlus对象")public class MybatisPlusDto {//表名String tableName = null;//主键对应的字段名String tableId = null;//表字段结构List<ModelField> modelFieldList = new ArrayList();//表字段名称列表List<String> columnList = new ArrayList<String>();}/*** 获取实例** @param clazz* @return*/public MybatisPlusDto getMybatisPlusDto(Class clazz) {MybatisPlusDto mybatisPlusDto = new MybatisPlusDto();//检查实体类是否缺少注解boolean isTableName = clazz.isAnnotationPresent(TableName.class);if (isTableName) {TableName tableNameIn = (TableName) clazz.getAnnotation(TableName.class);if (null == tableNameIn.value() || "".equals(tableNameIn.value())) {throw new RuntimeException("实体类无TableName注解！");}mybatisPlusDto.tableName = tableNameIn.value();}//是否包含注解boolean isTableField = false;boolean isTableId = false;Field[] fields = clazz.getDeclaredFields();for (int i = 0; i < fields.length; i++) {if (isTableField == false) {isTableField = fields[i].isAnnotationPresent(TableField.class);}if (isTableId == false) {isTableId = fields[i].isAnnotationPresent(TableId.class);}if (isTableField && isTableId) {//都找到注解了 就终止break;}}if (!isTableField) {throw new RuntimeException("实体类无TableField注解！");}if (!isTableId) {log.warn("实体类无isTableId注解！");}//获取表结构for (int i = 0; i < fields.length; i++) {ModelField modelField = new ModelField();modelField.setModelName(fields[i].getName());modelField.setModelType(fields[i].getType());boolean annotationPresent = fields[i].isAnnotationPresent(TableField.class);if (annotationPresent) {// 获取注解值String tableField = fields[i].getAnnotation(TableField.class).value();if (null != tableField && !"".equals(tableField)) {modelField.setTableField(tableField.toUpperCase());mybatisPlusDto.columnList.add(tableField.toUpperCase());boolean apiMp = fields[i].isAnnotationPresent(ApiModelProperty.class);if (apiMp) {modelField.setApiModelProperty(fields[i].getAnnotation(ApiModelProperty.class).value());} else {log.debug("类" + clazz.getName() + "的字段" + fields[i].getName() + "没有注解ApiModelProperty");}mybatisPlusDto.modelFieldList.add(modelField);} else {log.warn("属性[" + modelField.getModelName() + "]对应表字段为空！");}} else if (fields[i].isAnnotationPresent(TableId.class)) {// 获取注解值mybatisPlusDto.tableId = fields[i].getAnnotation(TableId.class).value().toUpperCase();if (null != mybatisPlusDto.tableId && !"".equals(mybatisPlusDto.tableId)) {modelField.setTableField(mybatisPlusDto.tableId.toUpperCase());mybatisPlusDto.columnList.add(mybatisPlusDto.tableId.toUpperCase());boolean apiMp = fields[i].isAnnotationPresent(ApiModelProperty.class);if (apiMp) {modelField.setApiModelProperty(fields[i].getAnnotation(ApiModelProperty.class).value());} else {log.debug("类" + clazz.getName() + "的字段" + fields[i].getName() + "没有注解ApiModelProperty");}mybatisPlusDto.modelFieldList.add(modelField);} else {log.warn("属性[" + modelField.getModelName() + "]对应表字段为空！");}}}
//        log.debug("类[" + clazz.getName() + "]的表名为[" + mybatisPlusDto.tableName + "];字段信息为:" + mybatisPlusDto.modelFieldList);return mybatisPlusDto;}/*** 建表** @param clazz*/public String createTable(Class clazz) {MybatisPlusDto dto = this.getMybatisPlusDto(clazz);StringBuffer sql = new StringBuffer();sql.append("CREATE TABLE ").append("\"").append(dto.getTableName()).append("\" (\n");//表字段结构List<ModelField> modelFieldList = dto.getModelFieldList();for (ModelField modelField : modelFieldList) {if (modelField.getTableField().equals(dto.getTableId())) {if (modelField.getModelType().equals(Integer.class)|| modelField.getModelType().equals(int.class)|| modelField.getModelType().equals(Long.class)|| modelField.getModelType().equals(long.class)) {sql.append("\"" + modelField.getTableField() + "\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n");} else {sql.append("\"" + modelField.getTableField() + "\" TEXT NOT NULL PRIMARY KEY,\n");}} else {sql.append("\"" + modelField.getTableField() + "\" ").append(this.getSqliteType(modelField.getModelType())).append(",\n");}}sql.setLength(sql.length() - 2);sql.append(");\n");
//        log.info(sql.toString());return sql.toString();}/*** 转换数据类型为sqlite类型** @param aClass* @return*/private String getSqliteType(Class aClass) {if (aClass.equals(Integer.class)|| aClass.equals(int.class)|| aClass.equals(Long.class)|| aClass.equals(long.class)) {return "INTEGER";}if (aClass.equals(Float.class)|| aClass.equals(float.class)|| aClass.equals(Double.class)|| aClass.equals(double.class)) {return "REAL";}if (aClass.equals(Date.class)) {return "NUMERIC";}if (aClass.equals(String.class)) {return "TEXT";}if (aClass.equals(Boolean.class)|| aClass.equals(boolean.class)) {return "INTEGER";}return "NUMERIC";}/*** 创建表字段** @param clazz* @param sqliteList* @return*/public List<String> createField(Class clazz, List<SqliteTableStructureDto> sqliteList) {List<String> sqlList = new ArrayList<String>();List<String> fields = sqliteList.stream().map(item -> item.getName().toUpperCase()).collect(Collectors.toList());MybatisPlusDto dto = this.getMybatisPlusDto(clazz);StringBuffer sql = new StringBuffer();//表字段结构List<ModelField> modelFieldList = dto.getModelFieldList();for (ModelField modelField : modelFieldList) {if (!fields.contains(modelField.getTableField().toUpperCase())) {log.warn("表[" + dto.getTableName() + "]字段[" + modelField.getTableField().toUpperCase() + "]缺失，正在生成重建sql");sqlList.add("alter table \"" + dto.getTableName() + "\" add \"" + modelField.getTableField().toUpperCase() + "\""+ this.getSqliteType(modelField.getModelType()) + "");}}return sqlList;}
}

后记

一个简单的根据注解的转换，也可以替换成mysql或oracle的写法，不过mysql已经有了mybatis-enhance-actable了，就不要重复造轮子了。

---