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);
}
}