利用MyBatis的ScriptRunner执行sql文件
使用原因
我的需求是ORACLE数据库.
在执行sql文件的时候可以把文件内容复制到工具里面执行,比如plsql里面,
在语句比较少或者数据比较少的情况下在PLSQL里面执行还是比较快的.
但是当数据比较多的时候执行就会让PLSQ卡死 出现无响应的情况.
所以需要使用到java代码来执行sql文件,这样速度比较快也不会出现上述所说的问题.
为什么选择scriptRunner?
在网上搜索方法,分别有三种方法:
- 纯java代码引用文件流的方式
- ibatis方式执行脚本(scriptRunner)
- ant执行SQL脚本
下面分别列出三种方法的写法:
纯java代码引用文件流的方式
package com.unmi.db;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* 读取 SQL 脚本并执行
* @author Unmi
*/
public class SqlFileExecutor {
/**
* 读取 SQL 文件,获取 SQL 语句
* @param sqlFile SQL 脚本文件
* @return List<sql> 返回所有 SQL 语句的 List
* @throws Exception
*/
private List<String> loadSql(String sqlFile) throws Exception {
List<String> sqlList = new ArrayList<String>();
try {
InputStream sqlFileIn = new FileInputStream(sqlFile);
StringBuffer sqlSb = new StringBuffer();
byte[] buff = new byte[1024];
int byteRead = 0;
while ((byteRead = sqlFileIn.read(buff)) != -1) {
sqlSb.append(new String(buff, 0, byteRead));
}
// Windows 下换行是 /r/n, Linux 下是 /n
String[] sqlArr = sqlSb.toString().split("(;//s*//r//n)|(;//s*//n)");
for (int i = 0; i < sqlArr.length; i++) {
String sql = sqlArr[i].replaceAll("--.*", "").trim();
if (!sql.equals("")) {
sqlList.add(sql);
}
}
return sqlList;
} catch (Exception ex) {
throw new Exception(ex.getMessage());
}
}
/**
* 传入连接来执行 SQL 脚本文件,这样可与其外的数据库操作同处一个事物中
* @param conn 传入数据库连接
* @param sqlFile SQL 脚本文件
* @throws Exception
*/
public void execute(Connection conn, String sqlFile) throws Exception {
Statement stmt = null;
List<String> sqlList = loadSql(sqlFile);
stmt = conn.createStatement();
for (String sql : sqlList) {
stmt.addBatch(sql);
}
int[] rows = stmt.executeBatch();
System.out.println("Row count:" + Arrays.toString(rows));
}
/**
* 自建连接,独立事物中执行 SQL 文件
* @param sqlFile SQL 脚本文件
* @throws Exception
*/
public void execute(String sqlFile) throws Exception {
Connection conn = DBCenter.getConnection();
Statement stmt = null;
List<String> sqlList = loadSql(sqlFile);
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
for (String sql : sqlList) {
stmt.addBatch(sql);
}
int[] rows = stmt.executeBatch();
System.out.println("Row count:" + Arrays.toString(rows));
DBCenter.commit(conn);
} catch (Exception ex) {
DBCenter.rollback(conn);
throw ex;
} finally {
DBCenter.close(null, stmt, conn);
}
}
public static void main(String[] args) throws Exception {
List<String> sqlList = new SqlFileExecutor().loadSql(args[0]);
System.out.println("size:" + sqlList.size());
for (String sql : sqlList) {
System.out.println(sql);
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
此方法的缺点是分割的考虑比较麻烦
ant执行SQL脚本
package com.unmi;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
/**
* 调用 ant.jar 的 SQLExec 执行 SQL 脚本文件
* @author Unmi
*/
public class AntExecSql {
/**
* @param args
*/
public static void main(String[] args) {
SQLExec sqlExec = new SQLExec();
//设置数据库参数
sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");
sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");
sqlExec.setUserid("xxuser");
sqlExec.setPassword("xxpass");
//要执行的脚本
sqlExec.setSrc(new File("src/data.sql"));
//有出错的语句该如何处理
sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(
SQLExec.OnError.class, "abort")));
sqlExec.setPrint(true); //设置是否输出
//输出到文件 sql.out 中;不设置该属性,默认输出到控制台
sqlExec.setOutput(new File("src/sql.out"));
sqlExec.setProject(new Project()); // 要指定这个属性,不然会出错
sqlExec.execute();
}
}
package com.unmi;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
/**
* 调用 ant.jar 的 SQLExec 执行 SQL 脚本文件
* @author Unmi
*/
public class AntExecSql {
/**
* @param args
*/
public static void main(String[] args) {
SQLExec sqlExec = new SQLExec();
//设置数据库参数
sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");
sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");
sqlExec.setUserid("xxuser");
sqlExec.setPassword("xxpass");
//要执行的脚本
sqlExec.setSrc(new File("src/data.sql"));
//有出错的语句该如何处理
sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(
SQLExec.OnError.class, "abort")));
sqlExec.setPrint(true); //设置是否输出
//输出到文件 sql.out 中;不设置该属性,默认输出到控制台
sqlExec.setOutput(new File("src/sql.out"));
sqlExec.setProject(new Project()); // 要指定这个属性,不然会出错
sqlExec.execute();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
此方法我测试过,只要有一句语句报错,就整个文件不执行,rollback,这种方式在执行多个文件的是不方便.希望有错误的能忽略把正确的导入.
ibatis方式执行脚本(scriptRunner)
package cn.zb.java8.utils;
import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
public class ScriptRunnerExecSql {
public static void main(String[] args) {
try {
Configuration configuration = new PropertiesConfiguration(ScriptRunnerExecSql.class.getClassLoader().getResource("")
+ "META-INF/spring/db.properties");
String url = configuration.getString("jdbc.url");
String driver = configuration.getString("jdbc.driver");
String username = configuration.getString("jdbc.user");
String password = configuration.getString("jdbc.password");
Class.forName(driver).newInstance();
Connection conn =DriverManager.getConnection(url, username, password);
ScriptRunner runner = new ScriptRunner(conn);
Resources.setCharset(Charset.forName("GBK")); //设置字符集,不然中文乱码插入错误
runner.setLogWriter(null);//设置是否输出日志
runner.runScript(Resources.getResourceAsReader("sql/CC20-01.sql"));
runner.runScript(Resources.getResourceAsReader("sql/CC21-01.sql"));
runner.closeConnection();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
这种方式就是我现在使用的方式,可以同时执行多个文件.遇到错误会忽略而且还能继续执行.
使用 ibatis方式执行脚本(scriptRunner)需要的步骤
- 首先需要下载mybatis包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.3.0</version>
</dependency>
- 1
- 2
- 3
- 4
- 5
读取配置文件的话可以使用commons包
<dependency>
<groupId>commons-configuration</groupId>
<artifactId>commons-configuration</artifactId>
<version>1.8</version>
</dependency>
- 1
- 2
- 3
- 4
- 5
添加数据库驱动
oracle
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.4.0</version> </dependency>
- 1
- 2
- 3
- 4
- 5
mysql
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency>
- 1
- 2
- 3
- 4
- 5
配置文件
jdbc.driver = oracle.jdbc.driver.OracleDriver
jdbc.url = jdbc:oracle:thin:@ \
(DESCRIPTION =\
(ADDRESS_LIST =\
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))\
)\
(CONNECT_DATA =\
(SID = xxx)\
(SERVER = DEDICATED)\
)\
)
jdbc.user = xxxx
jdbc.password = xxxx
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 复制下面的代码:
package cn.zb.java8.utils;
import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
public class ScriptRunnerExecSql {
public static void main(String[] args) {
try {
Configuration configuration = new PropertiesConfiguration(ScriptRunnerExecSql.class.getClassLoader().getResource("")
+ "META-INF/spring/db.properties");
String url = configuration.getString("jdbc.url");
String driver = configuration.getString("jdbc.driver");
String username = configuration.getString("jdbc.user");
String password = configuration.getString("jdbc.password");
Class.forName(driver).newInstance();
Connection conn =DriverManager.getConnection(url, username, password);
ScriptRunner runner = new ScriptRunner(conn);
Resources.setCharset(Charset.forName("GBK")); //设置字符集,不然中文乱码插入错误
runner.setLogWriter(null);//设置是否输出日志
runner.runScript(Resources.getResourceAsReader("sql/CC20-01.sql"));
runner.runScript(Resources.getResourceAsReader("sql/CC21-01.sql"));
runner.closeConnection();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
最后编译运行就OK了.
转自 https://blog.csdn.net/fish_817/article/details/49890753