java程序执行SQL脚本文件



1.纯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);
		}
	}
}

2.ibatis方式执行脚本

首先引入ibatis-common-2.jar包

import com.ibatis.common.jdbc.ScriptRunner;
import com.ibatis.common.resources.Resources;

 

jpetstore测试代码如下：

package com.ibatis.jpetstore.test;

import java.sql.DriverManager;
import java.util.Properties;

import com.ibatis.common.jdbc.ScriptRunner;
import com.ibatis.common.resources.Resources;
import com.mysql.jdbc.Connection;

public class DBTestSQL {

 
 public static void main(String[] args) {
  
  try {
   Properties props = Resources.getResourceAsProperties("properties/database.properties");
   String url = props.getProperty("url");
   String driver = props.getProperty("driver");
   String username = props.getProperty("username");
   String password = props.getProperty("password");
   System.out.println(url);
   if(url.equals("jdbc:mysql://localhost:3306/jpetstore1")) {
    Class.forName(driver).newInstance();
    Connection conn = (Connection) DriverManager.getConnection(url, username, password);
    ScriptRunner runner = new ScriptRunner(conn, false, false);
    runner.setErrorLogWriter(null);
    runner.setLogWriter(null);
    runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-schema.sql"));
    runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-dataload.sql"));
    
   }
   
  } catch (Exception e) {
   e.printStackTrace();
  }
  

 }

}

ScriptRunner(com.ibatis.common.jdbc.*)用法

ScriptRunner类用于执行SQL语句,例如创建数据库schema，或传入缺省或测试数据库等等。从下面的例子可以认识到它的易用性:

      例子1：使用现成的数据库连接

    Connection conn=getConnection();//some method to get a Connection

  ScriptRunner runner=new ScriptRunner();

  runner.runScript(conn,Resources.getResourceAsReader("com/some/resource/path/initialize.sql"));

    conn.close();

  例子2：使用新的数据库连接

  ScriptRunner runner=new ScriptRunner("com.some.Driver","jdbc:url://db","login","password");

  runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));

例子3：使用新创建的数据连接

  Properties props= getProperties();//some properties form somewhere

ScriptRunner runner =new ScriptRunner(props);

runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));

3.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();
}
}