利用MyBatis的ScriptRunner执行sql文件
利用MyBatis的ScriptRunner执行sql文件
使用原因为什么选择scriptRunner
纯java代码引用文件流的方式ant执行SQL脚本ibatis方式执行脚本scriptRunner 使用 ibatis方式执行脚本scriptRunner需要的步骤
使用原因
我的需求是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));
}
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);
}
}
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100
此方法的缺点是分割的考虑比较麻烦
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);
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);
sqlExec.setOutput(
new File(
"src/sql.out"));
sqlExec.setProject(
new Project());
sqlExec.execute();
}
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
此方法我测试过,只要有一句语句报错,就整个文件不执行,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()
}
}
}
1234567891011121314151617181920212223242526272829303132333435363738
这种方式就是我现在使用的方式,可以同时执行多个文件.遇到错误会忽略而且还能继续执行.
使用 ibatis方式执行脚本(scriptRunner)需要的步骤
首先需要下载mybatis包
<dependency>
<groupId>org.mybatis
</groupId>
<artifactId>mybatis
</artifactId>
<version>3.3.0
</version>
</dependency>
12345
读取配置文件的话可以使用commons包
<dependency>
<groupId>commons-configuration
</groupId>
<artifactId>commons-configuration
</artifactId>
<version>1.8
</version>
</dependency>
12345
添加数据库驱动
oracle
<dependency>
<groupId>com.oracle
</groupId>
<artifactId>ojdbc14
</artifactId>
<version>10.2.0.4.0
</version>
</dependency>
12345
mysql
<dependency>
<groupId>mysql
</groupId>
<artifactId>mysql-connector-java
</artifactId>
<version>5.1.35
</version>
</dependency>
12345
配置文件
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
12345678910111213
复制下面的代码:
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()
}
}
}
1234567891011121314151617181920212223242526272829303132333435363738
最后编译运行就OK了.
