Java 执行 SQL 脚本文件

布满荆棘的人生 2021-11-22 18:06 309阅读 0赞

转自：[http://blog.csdn.net/hongmin118/article/details/4588941][http_blog.csdn.net_hongmin118_article_details_4588941]

1 package com.unmi.db;
      2 
      3 import java.io.FileInputStream;
      4 import java.io.InputStream;
      5 import java.sql.Connection;
      6 import java.sql.Statement;
      7 import java.util.ArrayList;
      8 import java.util.Arrays;
      9 import java.util.List;
     10 
     11 /**
     12  * 读取 SQL 脚本并执行
     13  * @author Unmi
     14  */
     15 public 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 }

转载于:https://www.cnblogs.com/Mr-Nobody/p/3587901.html

[http_blog.csdn.net_hongmin118_article_details_4588941]: http://blog.csdn.net/hongmin118/article/details/4588941