SpringBoot基于MYSQL动态生成数据库

种豆走天下

已于 2023-06-06 16:33:38 修改

阅读量1.4k

点赞数

文章标签：数据库 mysql java

于 2023-06-06 16:31:24 首次发布

本文链接：https://blog.csdn.net/qq_40708522/article/details/131070362

版权

application-pro.yml

mysql:
  # mysql用户名
  username: root
  # mysql密码
  password: 123456
  # 主机
  host: 127.0.0.1
  # 端口 3306是mysql默认端口,一般不动
  port: 3306
  # 数据库名称(不用手动建,程序会自动建立)
  dbName: haruhibot
gocq:
  # go-cqhttp正向ws代理地址,与go-cqhttp配置中的保持一致 必要
  ws: ws://127.0.0.1:9909
  # go-cqhttp正向http代理地址,与go-cqhttp配置中的保持一致 后面不要带斜杠 必要
  http: http://127.0.0.1:5700
  # 如果gocq设置了access-token 则此处与gocq的access-token保持一致 否则会连接失败
  access-token:
bot:
  # bot程序端口
  port: 8081
  # 超级用户/管理员qq 只能填一个
  super-user:
  # bot的名字 只能填一个
  name: 春日酱
  # 搜图/识图功能需要配置这个项 Saucenao的API_KEY，通过 https://saucenao.com/user.php?page=search-api 注册获取 免费的
  search-image-key:
  bt-search: http://www.eclzz.guru
  agefans: https://www.agemys.vip
job:
  # 下载色图的定时爬虫 每15秒 enable是否开启这个定时任务 0非 1是
  downloadPixiv:
    cron: 0/15 * * * * ?
    enable: 0
  # 订阅新闻job 每天7点 注意：如果关闭这个job，即使订阅了新闻也不会定时发新闻了
  news:
    cron: 0 0 7 * * ?
    enable: 1

#########################  以下配置项勿改  #########################

server:
  port: ${bot.port}
  servlet:
    context-path: /haruhi
spring:
  datasource:
    dynamic:
      datasource:
        master:
          dbName: mysql
          username: ${mysql.username}
          password: ${mysql.password}
          url: jdbc:mysql://${mysql.host}:${mysql.port}/${spring.datasource.dynamic.datasource.master.dbName}?useUnicode=true&characterEncoding=utf-8&useSSL=false
          driver-class-name: com.mysql.cj.jdbc.Driver
mybatis-plus:
  type-aliases-package: com.haruhi.bot.entity
  mapper-locations: classpath:/mapper/**/*.xml
# 如果你要查看程序运行的每一条sql语句 将以下注释放开
#logging:
#  level:
#    com.haruhi.bot.mapper: debug
env:
  active: pro
log:
  level: info
  path: ./logs

DataSourceConfig

package com.haruhi.bot.config;

import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.text.MessageFormat;

/**
 * 不需要在这里进行配置数据库（配置了不会生效）
 * 这个类会自己读取配置文件
 *
 * 之所以这样写，是为了让静态变量能够读到配置文件
 */
@Component
@Slf4j
public class DataSourceConfig {

    // 系统数据源名称
    public final static String DATA_SOURCE_SYSTEM = "system";
    // 主数据库名称 必须为mysql 这是mysql的系统库
    public static String DATA_BASE_MASTER_NAME = "";
    // 驱动类全命名 reference
    public static String DATA_BASE_MASTER_DRIVERCLASSNAME = "";


    public static String jdbcUrlTemplate = "jdbc:mysql://{0}:{1}/{2}?useUnicode=true&characterEncoding=utf-8&useSSL=false";
    public static String JDBC_URL = "";
    // bot数据源名称
    public final static String DATA_SOURCE_MASTER = "master";
    // bot数据库名称
    public static String DATA_BASE_BOT = "";
    // 数据库用户名
    public static String DATA_BASE_BOT_USERNAME = "";
    // 数据库密码
    public static String DATA_BASE_BOT_PASSWORD = "";
    //  数据库 host
    public static String DATA_BASE_BOT_HOST = "";
    // 数据库 port
    public static String DATA_BASE_BOT_PORT = "";

    // 签到表
    public final static String BOT_T_CHECKIN = "t_checkin";
    // 禁用功能表
    public final static String BOT_T_DISABLE_FUNCTION = "t_disable_function";
    // pixiv 图库表
    public final static String BOT_T_PIXIV = "t_pixiv";
    // 词条
    public final static String BOT_T_WORD_STRIP = "t_word_strip";
    // 话术
    public final static String BOT_T_VERBAL_TRICKS = "t_verbal_tricks";
    // 群聊天历史
    public final static String BOT_T_GROUP_CHAT_HISTORY = "t_group_chat_history";
    // 戳一戳回复表
    public final static String BOT_T_POKE_REPLY = "t_poke_reply";
    // 订阅新闻表
    public final static String BOT_T_SUBSCRIBE_NEWS = "t_subscribe_news";

    @Autowired
    public void setSystemDbname(@Value("${spring.datasource.dynamic.datasource.master.dbName}") String dbName) {
        DATA_BASE_MASTER_NAME = dbName;
    }

    @Autowired
    public void setSystemDriverClassName(@Value("${spring.datasource.dynamic.datasource.master.driver-class-name}") String driverClassName) {
        DATA_BASE_MASTER_DRIVERCLASSNAME = driverClassName;
    }

    @Autowired
    public void setBotDbname(@Value("${mysql.dbName}") String dbName) {
        DATA_BASE_BOT = dbName;
        if (Strings.isBlank(DATA_BASE_BOT)) {
            throw new IllegalArgumentException("未配置数据库名称！");
        }
    }

    @Autowired
    public void setUsername(@Value("${mysql.username}") String username) {
        DATA_BASE_BOT_USERNAME = username;
        if (Strings.isBlank(DATA_BASE_BOT_USERNAME)) {
            throw new IllegalArgumentException("未配置数据库用户名！");
        }
    }
    @Autowired
    public void setPassword(@Value("${mysql.password}") String password) {
        DATA_BASE_BOT_PASSWORD = password;
        if (Strings.isBlank(DATA_BASE_BOT_PASSWORD)) {
            throw new IllegalArgumentException("未配置数据库密码！");
        }
    }
    @Autowired
    public void setHost(@Value("${mysql.host}") String host) {
        DATA_BASE_BOT_HOST = host;
        if (Strings.isBlank(DATA_BASE_BOT_HOST)) {
            throw new IllegalArgumentException("未配置数据库主机地址！");
        }
    }
    @Autowired
    public void setPort(@Value("${mysql.port}") String port) {
        DATA_BASE_BOT_PORT = port;
        if (Strings.isBlank(DATA_BASE_BOT_PORT)) {
            throw new IllegalArgumentException("未配置数据库端口！");
        }
    }

    @PostConstruct
    private void postConstruct(){
        JDBC_URL = MessageFormat.format(jdbcUrlTemplate,DATA_BASE_BOT_HOST,DATA_BASE_BOT_PORT,DATA_BASE_BOT);
        log.info("数据源配置加载并校验完成:{}",JDBC_URL);
    }
}

DataBaseInitMapper

package com.haruhi.bot.mapper.system;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.haruhi.bot.config.DataSourceConfig;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
@DS(value = DataSourceConfig.DATA_SOURCE_SYSTEM)
public interface DataBaseInitMapper {

    /**
     * 判断数据库是否存在
     * 0不存在 1存在
     * @param dbName
     * @return
     */
    int dataBaseIsExist(@Param("dbName") String dbName);

    /**
     * 创建数据库
     * @param dbName
     * @return
     */
    int createDataBase(@Param("dbName") String dbName);

    /**
     * 判断表是否存在，存在则返回1，不存在则返回0
     */
    int tableIsExist(@Param("dbName") String dbName, @Param("tableName") String tableName);
}

DataBaseInitMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haruhi.bot.mapper.system.DataBaseInitMapper">

    <select id="dataBaseIsExist" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = #{dbName}
    </select>
    <update id="createDataBase">
        CREATE DATABASE ${dbName} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    </update>
    <select id="tableIsExist" parameterType="java.lang.String" resultType="java.lang.Integer">
        SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_SCHEMA = #{dbName} AND TABLE_NAME = #{tableName}
    </select>

</mapper>

TableInitMapper

package com.haruhi.bot.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface TableInitMapper {

    /**
     * 创建签到表
     * @return
     */
    int createCheckin(@Param("tableName") String tableName);

    /**
     * 创建禁用功能表
     * @return
     */
    int createDisableFunction(@Param("tableName") String tableName);

    /**
     * pixiv图库表
     * @param tableName
     * @return
     */
    int createPixiv(@Param("tableName") String tableName);

    /**
     * 词条表
     * @param tableName
     * @return
     */
    int createWordStrip(@Param("tableName") String tableName);

    /**
     * 话术表
     * @param tableName
     * @return
     */
    int createVerbalTricks(@Param("tableName") String tableName);

    /**
     * 群聊天记录表
     * @param tableName
     * @return
     */
    int createGroupChatHistory(@Param("tableName") String tableName);

    /**
     * 戳一戳回复表
     * @param tableName
     * @return
     */
    int createPokeReply(@Param("tableName") String tableName);

    /**
     * 订阅新闻表
     * @param tableName
     * @return
     */
    int createSubscribeNews(@Param("tableName") String tableName);
}

TableInitMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.haruhi.bot.mapper.TableInitMapper">

    <update id="createCheckin">
        CREATE TABLE `${tableName}` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `user_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
            `group_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
            `day_count` int(11) DEFAULT '0',
            `favorability` int(11) DEFAULT '0',
            `first_date` timestamp DEFAULT CURRENT_TIMESTAMP,
            `last_date` timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到表';
    </update>
    <update id="createDisableFunction">
        CREATE TABLE `${tableName}` (
           `id` INT auto_increment NOT NULL,
           `class_name` varchar(100) NOT NULL,
           `name` varchar(30) NOT NULL,
           `weight` INT NOT NULL,
           `global` TINYINT(1) NOT NULL COMMENT '是否为全局禁用,0非(群禁用)1是(全局禁用)',
           `group_id` varchar(20) COMMENT '群号',
           `disable_time` datetime(3),
           PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='禁用的功能表';
    </update>
    <update id="createPixiv">
        CREATE TABLE `${tableName}` (
        `id` INT auto_increment NOT NULL,
        `pid` varchar(30) NOT NULL,
        `title` varchar(100) NOT NULL,
        `width` INT NULL,
        `height` INT NULL,
        `view` INT NULL,
        `bookmarks` INT NULL,
        `img_url` varchar(255) NOT NULL,
        `img_p` varchar(100) NULL,
        `uid` varchar(30) NULL,
        `author` varchar(100) NULL,
        `is_r18` bit NOT NULL,
        `tags` text NOT NULL,
        PRIMARY KEY (`id`),
        KEY `index_tags` (`tags`(767))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='pixiv图库（定时爬虫下载,需要在配置文件开启该爬虫）';
    </update>
    <update id="createWordStrip">
        CREATE TABLE `${tableName}` (
            `id` INT auto_increment NOT NULL,
            `user_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
            `group_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
            `key_word` varchar(255) NOT NULL,
            `answer` text NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='词条表';
    </update>
    <update id="createVerbalTricks">
        CREATE TABLE `${tableName}` (
            `id` INT auto_increment NOT NULL,
            `regex` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
            `answer` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='话术表';
    </update>
    <update id="createGroupChatHistory">
        CREATE TABLE `${tableName}` (
            `id` INT auto_increment NOT NULL,
            `card` varchar(30) COMMENT '群昵称',
            `nickname` varchar(30) COMMENT 'qq昵称',
            `message_id` varchar(30) NOT NULL,
            `group_id` varchar(20) NOT NULL,
            `user_id` varchar(20) NOT NULL,
            `content` TEXT COLLATE utf8mb4_unicode_ci,
            create_time BIGINT NOT NULL,
            PRIMARY KEY (`id`),
            KEY `index_content` (`content`(767))
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='群聊历史聊天表';
    </update>
    <update id="createPokeReply">
        CREATE TABLE `${tableName}` (
            `id` INT auto_increment NOT NULL,
            `reply` varchar(255) NOT NULL,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='戳一戳回复表';
    </update>
    <update id="createSubscribeNews">
        CREATE TABLE `${tableName}` (
            `id` INT auto_increment NOT NULL,
            `subscriber` varchar(20),
            `group_id` varchar(20),
            `type` INT NOT NULL COMMENT '1:群 2:私',
            create_time datetime(3),
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订阅新闻表';
    </update>
</mapper>

DataBaseService

package com.haruhi.bot.service;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.druid.DruidConfig;
import com.haruhi.bot.config.DataSourceConfig;
import com.haruhi.bot.mapper.system.DataBaseInitMapper;
import com.haruhi.bot.mapper.TableInitMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.DependsOn;
import org.springframework.stereotype.Component;

@Slf4j
@Component
@DependsOn("dataSourceConfig")
public class DataBaseService {

    @Autowired
    private DataBaseInitMapper dataBaseInitMapper;
    @Autowired
    private TableInitMapper tableInitMapper;
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;
    @Autowired
    private DynamicRoutingDataSource dynamicRoutingDataSource;


    public synchronized void initDataBase(){
        log.info("开始初始化数据库...");
        try {
            if(dataBaseInitMapper.dataBaseIsExist(DataSourceConfig.DATA_BASE_BOT) == 0){
                log.info("数据库不存在,开始创建...");
                dataBaseInitMapper.createDataBase(DataSourceConfig.DATA_BASE_BOT);
                log.info("数据库创建成功");
            }
            log.info("开始重新加载数据源...");
            reloadDatabaseSource();

            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_CHECKIN) == 0){
                tableInitMapper.createCheckin(DataSourceConfig.BOT_T_CHECKIN);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_DISABLE_FUNCTION) == 0){
                tableInitMapper.createDisableFunction(DataSourceConfig.BOT_T_DISABLE_FUNCTION);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_PIXIV) == 0){
                tableInitMapper.createPixiv(DataSourceConfig.BOT_T_PIXIV);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_WORD_STRIP) == 0){
                tableInitMapper.createWordStrip(DataSourceConfig.BOT_T_WORD_STRIP);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_VERBAL_TRICKS) == 0){
                tableInitMapper.createVerbalTricks(DataSourceConfig.BOT_T_VERBAL_TRICKS);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_GROUP_CHAT_HISTORY) == 0){
                tableInitMapper.createGroupChatHistory(DataSourceConfig.BOT_T_GROUP_CHAT_HISTORY);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_POKE_REPLY) == 0){
                tableInitMapper.createPokeReply(DataSourceConfig.BOT_T_POKE_REPLY);
            }
            if(dataBaseInitMapper.tableIsExist(DataSourceConfig.DATA_BASE_BOT,DataSourceConfig.BOT_T_SUBSCRIBE_NEWS) == 0){
                tableInitMapper.createSubscribeNews(DataSourceConfig.BOT_T_SUBSCRIBE_NEWS);
            }

            log.info("初始化数据库完成");
        }catch (Exception e){
            log.error("初始化数据库异常",e);
            System.exit(0);
        }
    }

    /**
     * 创建bot数据源
     */
    private void reloadDatabaseSource(){
        addDataSource();
        try {
            dynamicRoutingDataSource.afterPropertiesSet();
        } catch (Exception e) {
            log.error("重新加载数据源失败",e);
            System.exit(0);
        }
    }

    private void addDataSource(){
        DataSourceProperty newMaster = new DataSourceProperty();
        newMaster.setUsername(DataSourceConfig.DATA_BASE_BOT_USERNAME);
        newMaster.setPassword(DataSourceConfig.DATA_BASE_BOT_PASSWORD);
        newMaster.setDriverClassName(DataSourceConfig.DATA_BASE_MASTER_DRIVERCLASSNAME);
        newMaster.setUrl(DataSourceConfig.JDBC_URL);
        newMaster.setDruid(new DruidConfig());

        // 将旧主数据拉出来 下面做替换
        DataSourceProperty oldMaster = dynamicDataSourceProperties.getDatasource().get(DataSourceConfig.DATA_SOURCE_MASTER);

        // 将程序配置的数据库作为主数据源
        dynamicDataSourceProperties.getDatasource().put(DataSourceConfig.DATA_SOURCE_MASTER,newMaster);
        dynamicDataSourceProperties.getDatasource().put(DataSourceConfig.DATA_SOURCE_SYSTEM,oldMaster);
    }
}

SystemCommandLineRunner

package com.haruhi.bot;

import com.haruhi.bot.job.schedule.JobManage;
import com.haruhi.bot.service.DataBaseService;
import com.haruhi.bot.thread.FirstTask;
import com.haruhi.bot.ws.Client;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Slf4j
@Component
public class SystemCommandLineRunner implements CommandLineRunner {

    @Autowired
    private DataBaseService dataBaseService;
    @Autowired
    private FirstTask firstTask;
    @Autowired
    private JobManage jobManage;

    @Override
    public void run(String... args) throws Exception {
        // 初始化数据库
        dataBaseService.initDataBase();
        log.info("开始连接go-cqhttp...");
        if(!Client.connect()){
            Client.reConnection();
        }
        // 执行项目首次启动需要执行的任务 比如从库里加载一些数据到内存
        firstTask.execute(firstTask);
        // 开启job
        jobManage.startAllJob();
    }
}