Java使用JDBC执行SQL脚本文件

Java使用JDBC执行SQL脚本文件

代码如下

package com.common.core.datasource;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.ArrayUtil;
import com.fthj.fba.common.core.GlobalException;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.support.JdbcUtils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@Slf4j
public class DataSourceUtilTest2 {

    public void loadDriverClass(String driverClassName) {
        try {
            Class.forName(driverClassName);
        } catch (Exception e) {
            log.error("加载数据源驱动失败，错误信息：{}，详情见日志", e.getMessage(), e);
            throw new GlobalException("加载数据源驱动失败");
        }
    }

    public Connection getConnection(DataSourceProperty properties) {
        Connection connection;
        loadDriverClass(properties.getDriverClassName());
        try {
            // 3秒后连接超时
            DriverManager.setLoginTimeout(3);
            connection = DriverManager.getConnection(properties.getUrl(), properties.getUsername(),
                    properties.getPassword());
        } catch (Exception e) {
            log.error("数据源连接超时，错误信息：{}，详情见日志", e.getMessage(), e);
            throw new GlobalException("数据源连接超时");
        }
        return connection;
    }

    @SneakyThrows
    public boolean validConn(DataSourceProperty properties) {
        long st = System.currentTimeMillis();
        Connection connection = getConnection(properties);
        PreparedStatement ps = null;
        try {
            ps = connection.prepareStatement("SELECT 1");
            ResultSet rs = ps.executeQuery();
            String result = "";
            while (rs.next()) {
                result = rs.getString(1);
            }
            if (!"1".equals(result)) {
                throw new GlobalException("数据库检查失败");
            }
            return true;
        } finally {
            JdbcUtils.closeConnection(connection);
            JdbcUtils.closeStatement(ps);
            long t = System.currentTimeMillis() - st;
            log.info("数据库连接检查耗时：{}", t);
        }
    }


    public void runnerScript(DataSourceProperty properties, File file) {
        Connection connection = getConnection(properties);
        try {
            if (file.isDirectory()) {
                File[] files = file.listFiles();
                if (ArrayUtil.isNotEmpty(files)) {
                    for (File script : files) {
                        runnerScript(connection, script);
                    }
                }
            } else {
                runnerScript(connection, file);
            }
        } catch (Exception e) {
            throw new GlobalException("执行脚本失败：" + e.getMessage());
        } finally {
            JdbcUtils.closeConnection(connection);
        }
    }

    public void runnerScript(Connection connection, File file) throws FileNotFoundException {
        FileReader reader = null;
        try {
            reader = new FileReader(file);
            ScriptRunner scriptRunner = new ScriptRunner(connection);
            // 设置编码，防止中文乱码
            Resources.setCharset(StandardCharsets.UTF_8);
            // 必须为true，不然容易报错
            scriptRunner.setSendFullScript(true);
            // 执行
            scriptRunner.runScript(reader);
        } finally {
            IoUtil.close(reader);
        }
    }

    @Test
    public void testRunnerScript() {
        DataSourceProperty property = new DataSourceProperty();
        property.setDriverClassName("com.mysql.cj.jdbc.Driver");
        property.setUrl("jdbc:mysql://xxxx:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8");
        property.setUsername("root");
        property.setPassword("123456");
        URL resource = DataSourceUtilTest.class.getClassLoader().getResource("sql_scripts");
        if (null != resource) {
            File file = new File(resource.getPath());
            runnerScript(property, file);
        }
    }

    @Data
    public static class DataSourceProperty {
        private String driverClassName;
        private String url;
        private String username;
        private String password;
    }
}