Java中执行mysql脚本

本文介绍如何在Java代码里执行sql脚本

一、使用到的依赖

<!-- https://mvnrepository.com/artifact/com.ibatis/ibatis -->
        <dependency>
            <groupId>com.ibatis</groupId>
            <artifactId>ibatis</artifactId>
            <version>2.3.4.726-patched</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>

二、java代码

package com.test;


import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.ibatis.common.jdbc.ScriptRunner;


public class MysqlScriptRun {

    public static void main(String[] args) throws Exception {
        String aSQLScriptFilePath = "E:\\AATest\\01.sql";

        // Create MySql Connection
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/ecology?serverTimezone=Asia/Shanghai", "root", "123456");

        try {
            // Initialize object for ScripRunner
            ScriptRunner sr = new ScriptRunner(con, false, false);

            // Give the input file to Reader
            Reader reader = new BufferedReader(
                    new FileReader(aSQLScriptFilePath));

            // Exctute script
            sr.runScript(reader);

        } catch (Exception e) {
            System.err.println("Failed to Execute" + aSQLScriptFilePath
                    + " The error is " + e.getMessage());
        }
    }
}

注：jdbc地址里最好加上serverTimezone，不然可能会报错：
Exception in thread “main” java.sql.SQLException: The server time zone value ‘�й���׼ʱ��’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the ‘serverTimezone’ configuration property) to use a more specific time zone value if you want to utilize time zone support.

参考文章：如何使用Java运行MySQL脚本