Java 执行 SQL 脚本文件

  1package com.unmi.db;
  2
  3import java.io.FileInputStream;
  4import java.io.InputStream;
  5import java.sql.Connection;
  6import java.sql.Statement;
  7import java.util.ArrayList;
  8import java.util.Arrays;
  9import java.util.List;
 10
 11/**
 12 * 读取 SQL 脚本并执行
 13 * @author Unmi
 14 */
 15public class SqlFileExecutor {
 16
 17    /**
 18     * 读取 SQL 文件，获取 SQL 语句
 19     * @param sqlFile SQL 脚本文件
 20     * @return List<sql> 返回所有 SQL 语句的 List
 21     * @throws Exception
 22     */
 23    private List<String> loadSql(String sqlFile) throws Exception {
 24        List<String> sqlList = new ArrayList<String>();
 25
 26        try {
 27            InputStream sqlFileIn = new FileInputStream(sqlFile);
 28
 29            StringBuffer sqlSb = new StringBuffer();
 30            byte[] buff = new byte[1024];
 31            int byteRead = 0;
 32            while ((byteRead = sqlFileIn.read(buff)) != -1) {
 33                sqlSb.append(new String(buff, 0, byteRead));
 34            }
 35
 36            // Windows 下换行是 \r\n, Linux 下是 \n
 37            String[] sqlArr = sqlSb.toString().split("(;\\s*\\r\\n)|(;\\s*\\n)");
 38            for (int i = 0; i < sqlArr.length; i++) {
 39                String sql = sqlArr[i].replaceAll("--.*", "").trim();
 40                if (!sql.equals("")) {
 41                    sqlList.add(sql);
 42                }
 43            }
 44            return sqlList;
 45        } catch (Exception ex) {
 46            throw new Exception(ex.getMessage());
 47        }
 48    }
 49
 50    /**
 51     * 传入连接来执行 SQL 脚本文件，这样可与其外的数据库操作同处一个事物中
 52     * @param conn 传入数据库连接
 53     * @param sqlFile SQL 脚本文件
 54     * @throws Exception
 55     */
 56    public void execute(Connection conn, String sqlFile) throws Exception {
 57        Statement stmt = null;
 58        List<String> sqlList = loadSql(sqlFile);
 59        stmt = conn.createStatement();
 60        for (String sql : sqlList) {
 61            stmt.addBatch(sql);
 62        }
 63        int[] rows = stmt.executeBatch();
 64        System.out.println("Row count:" + Arrays.toString(rows));
 65    }
 66
 67    /**
 68     * 自建连接，独立事物中执行 SQL 文件
 69     * @param sqlFile SQL 脚本文件
 70     * @throws Exception
 71     */
 72    public void execute(String sqlFile) throws Exception {
 73        Connection conn = DBCenter.getConnection();
 74        Statement stmt = null;
 75        List<String> sqlList = loadSql(sqlFile);
 76        try {
 77            conn.setAutoCommit(false);
 78            stmt = conn.createStatement();
 79            for (String sql : sqlList) {
 80                stmt.addBatch(sql);
 81            }
 82            int[] rows = stmt.executeBatch();
 83            System.out.println("Row count:" + Arrays.toString(rows));
 84            DBCenter.commit(conn);
 85        } catch (Exception ex) {
 86            DBCenter.rollback(conn);
 87            throw ex;
 88        } finally {
 89            DBCenter.close(null, stmt, conn);
 90        }
 91    }
 92
 93    public static void main(String[] args) throws Exception {
 94        List<String> sqlList = new SqlFileExecutor().loadSql(args[0]);
 95        System.out.println("size:" + sqlList.size());
 96        for (String sql : sqlList) {
 97            System.out.println(sql);
 98        }
 99    }
100}