使用mybatis的ScriptRunner执行sql文件

       最近有需要通过java执行sql文件（进行数据库、表的创建），使用的mybatis的ScriptRunner工具类，现在记录下。

    pom.xml主要jar

<dependency>
    		<groupId>org.mybatis</groupId>
    		<artifactId>mybatis</artifactId>
    		<version>3.3.0</version>
		</dependency>
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		    <version>5.1.36</version>
		</dependency>
        <dependency>
			<groupId>net.sourceforge.jtds</groupId>
			<artifactId>jtds</artifactId>
			<version>1.2.4</version>
		</dependency>

package com.yhxy.vdc.dbcp.util;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLPermission;

import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.log4j.Logger;


public class DbUtil {

	private static Logger LOG =Logger.getLogger(DbUtil.class.getName());
	
	/**
	 * 
	 * @param ip
	 * @param port
	 * @param userName
	 * @param pwd
	 * @param sqlFilePath
	 * @throws Exception 
	 */
	public static void execSqlFileByMysql(String ip,String port,String userName,String pwd,String sqlFilePath) throws Exception{
		
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://"+ip+":"+port;
		
		Exception error = null;
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, pwd);
			ScriptRunner runner = new ScriptRunner(conn);
			//下面配置不要随意更改，否则会出现各种问题
			runner.setAutoCommit(true);//自动提交
			runner.setFullLineDelimiter(false);
			runner.setDelimiter(";");每条命令间的分隔符
			runner.setSendFullScript(false);
			runner.setStopOnError(false);
		//	runner.setLogWriter(null);//设置是否输出日志
			//如果又多个sql文件，可以写多个runner.runScript(xxx),
			runner.runScript(new InputStreamReader(new FileInputStream(sqlFilePath),"utf-8"));
			close(conn);
		} catch (Exception e) {
			LOG.error("执行sql文件进行数据库创建失败....",e);
			error = e;
		}finally{
			close(conn);
		}
		if(error != null){
			throw error;
		}
	}
	
	
	public static void execSqlFileBySqlserver(String ip,String port,String userName,
			String pwd,String sqlFilePath,String dbName) throws Exception{
		String driver = "net.sourceforge.jtds.jdbc.Driver";
		String url = "jdbc:jtds:sqlserver://"+ip+":"+port;
		
		
		
		Exception error = null;
		Connection conn = null;
		try {
			replaceAndCreate(f1,f2,oldDbName,newDbName);
			
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, pwd);
			ScriptRunner runner = new ScriptRunner(conn);
			//下面的配置，不要随意更改，否则导致各种问题
			runner.setAutoCommit(true);//自动提交
			runner.setFullLineDelimiter(true);
			runner.setDelimiter("GO");//每条命令间的分隔符
			runner.setSendFullScript(false);
			runner.setStopOnError(false);
	//		runner.setLogWriter(null);//设置是否输出日志
			//如果又多个sql文件，可以写多个runner.runScript(xxx),
			runner.runScript(new InputStreamReader(new FileInputStream(sqlFilePath),"utf8"));
			
		} catch (Exception e) {
			LOG.error("执行sql文件进行数据库创建失败....",e);
			error = e;
		}finally{
			close(conn);
			//删除文件
			deleteFile(f2);
		}
		
		if(error != null){
			throw error;
		}
	}
	
	private static void close(Connection conn){
		try {
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			if(conn != null){
				conn = null;
			}
		}
	}
}

本项目编码都是utf8，maven编译也是utf8，所以要检查sql脚本文件是不是也是utf8，否则会乱码。sqlserver2008 脚本检查下最后是否是意GO结尾

其中要注意的是，不同的数据库的命令之间的分隔符是不一样的，mysql是分号，sqlserver是"GO",不能弄错