项目中业务需求的不同,有时候我们需要动态创建数据表;
1、创建mapper接口
/**
* 判断表是否存在
* @param tableName
* @return
*/
Integer isExitsTable(@Param("tableName") String tableName);
/**
*
* @param tableName
* @return
*/
Integer createTable(@Param("tableName") String tableName);
2、对应xml中增加sql语句
<select id="isExitsTable" resultType="java.lang.Integer">
SELECT COUNT(*) FROM information_schema.TABLES WHERE table_name=#{tableName} and TABLE_SCHEMA=(select database())
</select>
<update id="createTable">
CREATE TABLE ${tableName} (
`id` varchar(36) NOT NULL COMMENT 'ID',
`state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '状态1失败2已发送3成功',
`retry_num` tinyint(2) NOT NULL DEFAULT '1' COMMENT '重试次数',
`content` varchar(5000) NOT NULL DEFAULT '' COMMENT '数据',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='重发信息表'
</update>
3、在service中验证表是否创建
@Service
public class ForwardRetryServiceImpl extends ServiceImpl<ForwardRetryMapper, ForwardRetry> implements IForwardRetryService {
@PostConstruct
public void checkTableExists(){
String tableName="forward_retry_"+ IPUtils.getIP().replaceAll("\\.","")+System.getProperty("server.port");
if(this.baseMapper.isExitsTable(tableName)==0){
this.baseMapper.createTable(tableName);
}
}
}