根据mybatis plus注解动态创建sqlite表和表字段
根据mybatis plus注解动态创建sqlite表和表字段
启动时动态创建sqlite数据库，根据mybatis plus注解动态创建表。如果有新增字段，动态创建字段。
文章目录
一、初始化数据库
1.系统启动时初始化数据库
通过@PostConstruct注解在项目启动时调用初始化方法
@PostConstruct
public 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);
//扫描到的class
String classname = reader.getClassMetadata().getClassName();
Class<?> clazz = Class.forName(classname);
//判断是否有指定主解
TableName anno = clazz.getAnnotation(TableName.class);
if (anno != null) {
//将注解中的类型值作为key，对应的类作为 value
domainTableNames.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(classMap.get(tableName), 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了，就不要重复造轮子了。
本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
二维码