Java 执行 SQL 脚本文件

是拷贝的别人的，以备学习

 

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