java编写一个执行Sql脚本文件实例

1、项目搭建 引入相关依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>

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

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

        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>

2、代码实现

1、获取数据库连接

String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&nullCatalogMeansCurrent=true&serverTimezone=UTC&allowMultiQueries=true";
String user = "test";
String password = "test";
Connection connection = DriverManager.getConnection(url, user, password);

2、创建Statement对象

Statement statement = connection.createStatement();

3、读取SQL文件

String filePath = "test.sql";
BufferedReader bufferedReader = new BufferedReader(new FileReader(filePath));
String line ;
while ((line = bufferedReader.readLine()) != null){
    
}

4、按行读取，去除注释

 while ((line = bufferedReader.readLine()) != null){
            //去除注释
            if(line.trim().startsWith("--")){//单行注释
                line = line.replaceAll("--.*","");
            }
            line = line.replaceAll("/\\*.*?\\*/","");//多行注释
            byte[] bytes = line.getBytes();
            if(bytes.length == 0){//跳过空行
                continue;
            }
            sqlAppender = sqlAppender + line;
            try {
                //跳过多行注释
                if(line.startsWith("/*")){
                    skip = true;
                    sqlAppender = "";
                    continue;
                }
                if(line.endsWith("*/")){
                    skip = false;
                    sqlAppender = "";
                    continue;
                }
                if(skip){
                    sqlAppender = "";
                    continue;
                }
                if(line.endsWith(");") || line.trim().startsWith("DROP")){//执行sql
                    logger.info("正在执行" +sqlAppender.toString());
                    statement.execute(sqlAppender.toString());
                    atomicInteger.addAndGet(1);
                    sqlAppender = "";
                }
            }catch (Exception e){
                //处理异常的sql
                logger.error("处理异常------------->"+sqlAppender.toString());
                sqlAppender = "";
            }
        }

5、完整代码：

package com.liao.test;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.io.BufferedReader;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.atomic.AtomicInteger;

@Component
public class SqlScriptExecutor {

    String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&nullCatalogMeansCurrent=true&serverTimezone=UTC&allowMultiQueries=true";
    String user = "test";
    String password = "test";

    Logger logger = LoggerFactory.getLogger(SqlScriptExecutor.class);
    

    @PostConstruct
    public void init() throws Exception {

        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        Date startdate = new Date();
        String startTime = sdf.format(startdate);

        logger.info("脚本执行开始"+startTime);

        String filePath = "test.sql";

        Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement();

        BufferedReader bufferedReader = new BufferedReader(new FileReader(filePath));

        String line ;

        AtomicInteger atomicInteger = new AtomicInteger(0);

        String sqlAppender = "";

        boolean skip = false;


        while ((line = bufferedReader.readLine()) != null){
            //去除注释
            if(line.trim().startsWith("--")){//单行注释
                line = line.replaceAll("--.*","");
            }
            line = line.replaceAll("/\\*.*?\\*/","");//多行注释
            byte[] bytes = line.getBytes();
            if(bytes.length == 0){//跳过空行
                continue;
            }
            sqlAppender = sqlAppender + line;
            try {
                //跳过多行注释
                if(line.startsWith("/*")){
                    skip = true;
                    sqlAppender = "";
                    continue;
                }
                if(line.endsWith("*/")){
                    skip = false;
                    sqlAppender = "";
                    continue;
                }
                if(skip){
                    sqlAppender = "";
                    continue;
                }
                if(line.endsWith(");") || line.trim().startsWith("DROP")){//执行sql
                    logger.info("正在执行" +sqlAppender.toString());
                    statement.execute(sqlAppender.toString());
                    atomicInteger.addAndGet(1);
                    sqlAppender = "";
                }
            }catch (Exception e){
                //处理异常的sql
                logger.error("处理异常------------->"+sqlAppender.toString());
                sqlAppender = "";
            }
        }

        logger.info("总条数"+atomicInteger.get());

        connection.close();
        statement.close();

        Date endDate = new Date();

        logger.info("脚本执行完成"+sdf.format(endDate));
        long milliseconds = endDate.getTime() - startdate.getTime();

        long remainingMilliseconds = milliseconds % (1000 * 60 * 60);

        long minutes = remainingMilliseconds / (1000 * 60);

        logger.info("耗时毫秒：------------------>"+remainingMilliseconds);
        logger.info("耗时分钟：------------------>"+minutes);

    }

}