mybatis完成不同数据库间数据迁移工作

臧兴学

2023-12-01

背景

系统升级后，部署在不同服务器且表字段有增减（如将两张表字段合为一张表），希望可以自动化将老系统数据按照新系统数据格式要求入库

思考

数据库数据迁移自然涉及到mysql，考虑使用mybatis来完成数据库相关操作
既然使用mybatis则要考虑多数据源配置，需要同时连接新老系统数据库
日志输出，使用常规log4j即可

准备工作

搭建项目，导入maven依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>data-migration</groupId>
    <artifactId>data-migration</artifactId>
    <version>1.0.0.0</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.7</source>
                    <target>1.7</target>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-assembly-plugin</artifactId>
                <configuration>
                    <appendAssemblyId>false</appendAssemblyId>
                    <descriptorRefs>
                        <descriptorRef>jar-with-dependencies</descriptorRef>
                    </descriptorRefs>
                    <archive>
                        <manifest>
                            <!-- 此处指定main方法入口的class -->
                            <mainClass>com.migration.DataMigration</mainClass>
                        </manifest>
                    </archive>
                </configuration>
                <executions>
                    <execution>
                        <id>make-assembly</id>
                        <phase>package</phase>
                        <goals>
                            <goal>assembly</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
    <name>data-migration</name>

    <dependencies>
        <!-- mybatis start -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.29</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-collections</groupId>
            <artifactId>commons-collections</artifactId>
            <version>3.2.1</version>
        </dependency>

        <!-- mybatis end -->
    </dependencies>

</project>

数据库配置

config.properties

jdbc.driverClassName=com.mysql.jdbc.Driver

ucenter.url=jdbc\:mysql\://127.0.0.1\:3306/old?useUnicode\=true&characterEncoding\=UTF-8&zeroDateTimeBehavior\=convertToNull
ucenter.username=root
ucenter.password=root

platform.url=jdbc\:mysql\://127.0.0.1\:3306/new?useUnicode\=true&characterEncoding\=UTF-8&zeroDateTimeBehavior\=convertToNull
platform.username=root
platform.password=root

mybatis多数据源配置

mybatis-config.xml

通过environment标签来完成多数据源配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--引入config静态配置文件-->
    <properties resource="config.properties"/>


    <settings>
        <!-- 日志打印 -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!-- 对事务的管理和连接池的配置 -->
    <environments default="ucenter">
        
        <environment id="ucenter">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${ucenter.url}"/>
                <property name="username" value="${ucenter.username}"/>
                <property name="password" value="${ucenter.password}"/>
            </dataSource>
        </environment>

        <environment id="platform">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${platform.url}"/>
                <property name="username" value="${platform.username}"/>
                <property name="password" value="${platform.password}"/>
            </dataSource>
        </environment>

    </environments>

    <!-- mapping 文件路径配置 -->
    <mappers>
        <mapper resource="mapper/SystemMapper.xml"/>
    </mappers>
</configuration>

log4j配置

log4j.properties

log4j.rootLogger=info,file,console
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=${catalina.home}/logs/mybatis.log
log4j.appender.file.MaxFileSize=100MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.Encoding=UTF-8
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %t (%F:%L) %-5p %x - %m%n


log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=INFO
log4j.appender.systemOut.Target=System.err
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %t (%F:%L) %-5p %x - %m%n

开发工作

数据库连接工具类

public class SqlSessionFactoryUtil {

	private static Logger logger = Logger.getLogger(SqlSessionFactoryUtil.class);

	private static SqlSessionFactory factory;

	/**
	 * mybatis-config environments 节点环境
	 */
	private String environment;

	public SqlSessionFactoryUtil() {
		Reader r;
		try {
			String xml = "mybatis-config.xml";
			r = Resources.getResourceAsReader(xml);
			factory = new SqlSessionFactoryBuilder().build(r);
		} catch (IOException e) {
			logger.info("读取mybatis配置文件失败", e);
		}
	}

	public SqlSessionFactoryUtil(String environment) {
		this.environment = environment;
		Reader r;
		try {
			String xml = "mybatis-config.xml";
			r = Resources.getResourceAsReader(xml);
			factory = new SqlSessionFactoryBuilder().build(r, environment);
		} catch (IOException e) {
			logger.info("读取mybatis配置文件失败", e);
		}
	}

    /**
     * 得到当前session
     */
    public SqlSession getSqlSession() {
        // 自动提交
        return factory.openSession(true);
    }

	/**
	 * 关闭SqlSession
	 *
	 * @param session
	 *            关闭session
	 */
	public static void closeSqlSession(SqlSession session) {
		if (session != null) {
			session.close();
		}
	}
}

main方法

private static Logger logger = Logger.getLogger(DataMigration.class);

public static void main(String[] args) {
    try {
        // 老数据库会话
        SqlSession ucenter = new SqlSessionFactoryUtil().getSqlSession();
        // 新数据库会话
        SqlSession platform = new SqlSessionFactoryUtil("platform").getSqlSession();
        
        // 菜单表
        BasicUtil.menuDataMigration(ucenter, platform);
        
        // 关闭数据库会话
        SqlSessionFactoryUtil.closeSqlSession(ucenter);
        SqlSessionFactoryUtil.closeSqlSession(platform);
    } catch (Exception e) {
        logger.error("数据迁移失败", e);
    }
}

具体菜单表迁移方法

public static void menuDataMigration(SqlSession ucenter, SqlSession platform) {
    logger.info("老数据迁移菜单表: old.tab_menu --> platform.platform_menu start");
    // 查询老系统表 tab_menu
    List<MenuVo> menuVos = ucenter.selectList("MenuMapper.MenuUcenterQuery");
    if (CollectionUtils.isNotEmpty(menuVos)) {
        // 入库新系统 platform_menu
        platform.insert("MenuMapper.MenuBatchInsert", menuVos);
    } else {
        logger.info("数据迁移菜单表: old.tab_menu 数据为空");
    }
    logger.info("数据迁移菜单表: old.tab_menu --> platform.platform_menu end");
}

扩展

不能使用框架，要使用原始jdbc

在main方法中添加static静态块进行数据库连接初始化。但是这种方式比较麻烦，因为jdbc的查询语句返回值用ResultSet接收，在去遍历返回结果进行赋值、入库。

static {
    Properties properties = new Properties();
    InputStream inputStream = Object.class.getResourceAsStream("/config.properties");
    InputStreamReader inputStreamReader = null;

    String driver="com.mysql.jdbc.Driver";
    Connection connection = null;
    PreparedStatement stmt=null;
    //查询返回结果
    ResultSet rs = null;
    try{
        inputStreamReader = new InputStreamReader(inputStream, "GBK");
        properties.load(inputStreamReader);
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String pwd = properties.getProperty("pwd");

        Class.forName(driver);
        connection = DriverManager.getConnection(url,user,pwd);
        stmt = connection.prepareStatement("INSERT INTO t_malicious_app_features_log (`data_origin`, `operate_type`) VALUES ('2', '1');");
        rs = stmt.executeQuery();
    }catch (Exception e){
        e.printStackTrace();
    }
}

将数据库配置文件移到jar包外进行读取

同样使用Properties方式读取，这样可以更加灵活的修改config.properties文件配置

//将配置文件路径以参数的方式传进jar包
java -jar xxxxxx.jar /opt/config.properties

public static void main(String[] args) {
    try {
        //接收外界传递的参数，从0开始
        String configFilePath = args[0];
        
        InputStream inputStream = Object.class.getResourceAsStream(configFilePath);
        InputStreamReader inputStreamReader = null;
        
        String driver="com.mysql.jdbc.Driver";
        Connection connection = null;
        PreparedStatement stmt=null;
        //查询返回结果
        ResultSet rs = null;
        try{
            inputStreamReader = new InputStreamReader(inputStream, "GBK");
            properties.load(inputStreamReader);
            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String pwd = properties.getProperty("pwd");
            
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,pwd);
            stmt = connection.prepareStatement("INSERT INTO t_malicious_app_features_log (`data_origin`, `operate_type`) VALUES ('2', '1');");
            rs = stmt.executeQuery();
        }catch (Exception e){
            e.printStackTrace();
        }
    
        
    } catch (Exception e) {
        logger.error("数据迁移失败", e);
    }
}