这个案例来自于一个真实的需求

需求描述

需求分析

这个需求其实就是开发一个springboot starter模块，在项目启动前向数据库导入数据。因此，只要starter开发完毕，在其他模块的commandLineRunner中使用即可。
实现数据库导入的编码思路有两种，第一种使用jdbc连接源数据库，可以获取到源数据库的信息、表结构以及表中数据。再将表结构和表中信息逐个导入到目标数据库中。但这种方式使用select语句会查询到大量信息，然后逐条插入到目标数据库，过程比较繁琐，且需要占用内存作为数据中转。
第二种方式，利用mysqldump命令，可以实现数据库导出导入sql文件，也可以实现跨数据库导入导出数据。命令格式如下：
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2
这里我采用第二种方式，即直接利用mysql的特性来实现数据导出和导入。

模板结构

首先在idea中利用springboot initializr快速创建一个springboot模块，pom中引入自动配置的依赖包

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>

starter模块的核心代码主要有这四部分组成

starter模块

开发过程

（1）先写业务类：
这里我们利用参数拼接成mysql的命令，然后通过runtime.exec方法执行。注意：exec方法只负责发送命令，发送成功不代表命令执行成功，如果要判断命令是否执行成功，需要获取返回的错误流信息。

public class TransferDataTable {
    private Logger logger = LoggerFactory.getLogger(TransferDataTable.class);

    private String sourceDBUserName;
    //注意特殊符号，必要时用单引号包住密码
    private String sourceDBPassword;
    private String sourceHost;
    private String sourcePort;
    private String sourceDBName;
    private String sourceTableName;
    //导出至本地目录，如果是linux系统，则需要修改
    private String exportPath;

    private String targetDBUserName;
    private String targetDBPassword;
    private String targetHost;
    private String targetPort;
    private String targetDBName;
    private String targetTableName;
    private String importPath;

    public TransferDataTable() {
    }

    public TransferDataTable(String sourceDBUserName, String sourceDBPassword, String sourceHost, String sourcePort, String sourceDBName, String sourceTableName, String exportPath, String targetDBUserName, String targetDBPassword, String targetHost, String targetPort, String targetDBName, String targetTableName, String importPath) {
        this.sourceDBUserName = sourceDBUserName;
        this.sourceDBPassword = sourceDBPassword;
        this.sourceHost = sourceHost;
        this.sourcePort = sourcePort;
        this.sourceDBName = sourceDBName;
        this.sourceTableName = sourceTableName;
        this.exportPath = exportPath;
        this.targetDBUserName = targetDBUserName;
        this.targetDBPassword = targetDBPassword;
        this.targetHost = targetHost;
        this.targetPort = targetPort;
        this.targetDBName = targetDBName;
        this.targetTableName = targetTableName;
        this.importPath = importPath;
    }

    public void transferData() {
        Runtime runtime = Runtime.getRuntime();
        String transferCommand = getTransferCommand();
        StringBuffer result = new StringBuffer();
        try {
            logger.info("start to transfer data");
            Process process = runtime.exec(transferCommand);
            //获取错误的流信息
            InputStream is = process.getErrorStream();
            BufferedReader reader = new BufferedReader(new InputStreamReader(is));
            String line;
            while ((line = reader.readLine()) != null) {
                result.append(line + "\n");
            }
            process.waitFor();
            is.close();
            reader.close();
            process.destroy();
        } catch (IOException | InterruptedException e) {
            logger.error(e.getMessage());
        }
        if(result.toString()==null||result.toString().equals("")){
            logger.info("data transfer success!");
        }else{
            logger.error("data transfer error!"+result.toString());
        }
    }


    public void exportData() {
        Runtime runtime = Runtime.getRuntime();
        String exportCommand = getExportCommand();
        try {
            logger.info("start to export data to " + this.exportPath);
            runtime.exec(exportCommand);
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
        logger.info("data export success!");
    }


    public void importData() {
        Runtime runtime = Runtime.getRuntime();
        String importCommand = getImportCommand();
        try {
            logger.info("start to import data from " + this.importPath);
            runtime.exec(importCommand);
        } catch (IOException e) {
            logger.error(e.getMessage());
        }
        logger.info("data import success!");
    }


    private String getExportCommand() {
        StringBuffer command = new StringBuffer();
        //注意：如果运行在linux系统，则需要适配修改
        command.append("cmd /c mysqldump -u")
                .append(this.sourceDBUserName)
                .append(" -p")
                .append(this.sourceDBPassword)
                .append(" -h ")
                .append(this.sourceHost)
                .append(" -P")
                .append(this.sourcePort)
                .append(" ")
                .append(this.sourceDBName)
                .append(" ")
                .append(this.sourceTableName)
                .append(" >")
                .append(this.exportPath);
        return command.toString();
    }

    public String getImportCommand() {
        StringBuffer command = new StringBuffer();
        command.append("cmd /c mysql -u")
                .append(this.targetDBUserName)
                .append(" -p")
                .append(this.targetDBPassword)
                .append(" -h ")
                .append(this.targetHost)
                .append(" -P")
                .append(this.targetPort)
                .append(" ")
                .append(this.targetDBName)
                .append(" <")
                .append(this.importPath);
        return command.toString();
    }

    public String getTransferCommand() {
        StringBuffer command = new StringBuffer();
        command.append("cmd /c mysqldump --host=").append(this.sourceHost)
                .append(" -u").append(this.sourceDBUserName)
                .append(" -p").append(this.sourceDBPassword)
                .append(" -P").append(this.sourcePort)
                .append(" -C --databases ").append(this.sourceDBName)
                .append(" --tables ").append(this.sourceTableName)
                .append("|mysql --host=")
                .append(this.targetHost)
                .append(" -u")
                .append(this.targetDBUserName)
                .append(" -p")
                .append(this.targetDBPassword).append(" -P")
                .append(this.targetPort)
                .append(" ")
                .append(this.targetDBName);
        return command.toString();
    }
}

（2）写参数类
参数类用来保存业务参数，配置类中调用参数类的get方法来给业务类写入参数。可以加上lombok注解，避免set和get方法占用了大量篇幅。

@ConfigurationProperties(prefix = "auto.transfer")
public class TransferDataTableProperties {
    private String sourceDBUserName = "xxxxx";
    private String sourceDBPassword = "xxxxx";
    private String sourceHost = "xxxxx";
    private String sourcePort = "3306";
    private String sourceDBName = "xxxxx";
    private String sourceTableName = "xxxxx";
    private String exportPath = "d:\\export.sql";

    private String targetDBUserName="xxxxx";
    private String targetDBPassword="xxxxx";
    private String targetHost="127.0.0.1";
    private String targetPort="3306";
    private String targetDBName="xxxxx";
    private String targetTableName="";
    private String importPath="d:\\export.sql";

    public void setSourceDBUserName(String sourceDBUserName) {
        this.sourceDBUserName = sourceDBUserName;
    }

    public void setSourceDBPassword(String sourceDBPassword) {
        this.sourceDBPassword = sourceDBPassword;
    }

    public void setSourceHost(String sourceHost) {
        this.sourceHost = sourceHost;
    }

    public void setSourcePort(String sourcePort) {
        this.sourcePort = sourcePort;
    }

    public void setSourceDBName(String sourceDBName) {
        this.sourceDBName = sourceDBName;
    }

    public void setSourceTableName(String sourceTableName) {
        this.sourceTableName = sourceTableName;
    }

    public void setExportPath(String exportPath) {
        this.exportPath = exportPath;
    }

    public void setTargetDBUserName(String targetDBUserName) {
        this.targetDBUserName = targetDBUserName;
    }

    public void setTargetDBPassword(String targetDBPassword) {
        this.targetDBPassword = targetDBPassword;
    }

    public void setTargetHost(String targetHost) {
        this.targetHost = targetHost;
    }

    public void setTargetPort(String targetPort) {
        this.targetPort = targetPort;
    }

    public void setTargetDBName(String targetDBName) {
        this.targetDBName = targetDBName;
    }

    public void setTargetTableName(String targetTableName) {
        this.targetTableName = targetTableName;
    }

    public void setImportPath(String importPath) {
        this.importPath = importPath;
    }

    public String getSourceDBUserName() {
        return sourceDBUserName;
    }

    public String getSourceDBPassword() {
        return sourceDBPassword;
    }

    public String getSourceHost() {
        return sourceHost;
    }

    public String getSourcePort() {
        return sourcePort;
    }

    public String getSourceDBName() {
        return sourceDBName;
    }

    public String getSourceTableName() {
        return sourceTableName;
    }

    public String getExportPath() {
        return exportPath;
    }

    public String getTargetDBUserName() {
        return targetDBUserName;
    }

    public String getTargetDBPassword() {
        return targetDBPassword;
    }

    public String getTargetHost() {
        return targetHost;
    }

    public String getTargetPort() {
        return targetPort;
    }

    public String getTargetDBName() {
        return targetDBName;
    }

    public String getTargetTableName() {
        return targetTableName;
    }

    public String getImportPath() {
        return importPath;
    }
}

（3）写配置类
springboot提倡用java代码来写配置文件，这个类其实就等效于一个配置文件。

@Configuration
@ConditionalOnClass(TransferDataTable.class)
@EnableConfigurationProperties(TransferDataTableProperties.class)
public class TransferDataTableConfiguration {

    @Bean
    @ConditionalOnMissingBean
    public TransferDataTable transferDataTable(TransferDataTableProperties t){
        return new TransferDataTable(t.getSourceDBUserName(),
                t.getSourceDBPassword(),
                t.getSourceHost(),
                t.getSourcePort(),
                t.getSourceDBName(),
                t.getSourceTableName(),
                t.getExportPath(),
                t.getTargetDBUserName(),
                t.getTargetDBPassword(),
                t.getTargetHost(),
                t.getTargetPort(),
                t.getTargetDBName(),
                t.getTargetTableName(),
                t.getImportPath()
                );
    }
}

(4)写注解，实现自动配置

@Inherited
@Documented
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Import({TransferDataTableConfiguration.class})
public @interface EnableTransferDataTableConfiguration {
}

到这里我们的starter就写好了。下面说一下如何使用。

使用方法

假设我们项目的模块要使用这个starter。
（1）首先在pom文件中引入starter模块

image.png

（2）（可选）在配置文件中配置参数，这里我只选择了部分参数进行配置，如果不配置的话，会自动加载默认配置。默认配置已经写在了starter模块的参数类里面。

auto.transfer.exportPath=d:\\export2.sql
auto.transfer.sourceDBName=xxx
auto.transfer.targetDBUserName=xxx
auto.transfer.targetDBPassword=xxx
auto.transfer.targetHost=xxx
auto.transfer.targetDBName=xxx

配置文件中参数前缀要和注解中写的一致。

参数类

（3）直接调用方法即可！
注意添加我们自动配置的注解

调用starter模块的方法

这样，我们启动项目时，starter模块中的方法就会被调用，完成了数据导入。
而starter模块所有参数都实现了自动配置，需要修改的话，直接在配置文件中处理即可。