去掉utf-8的Bom頭：使用java以及jdbc不使用第三方庫執行sql檔案指令碼

標籤：存在   ESS   參數錯誤   pat   acea   個數   fileinput   conf   createdb   

package com.xxx.xxx.dao;import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.InputStreamReader;import java.io.UnsupportedEncodingException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.log4j.Logger;import org.apache.log4j.PropertyConfigurator;import com.ft.hsm.Util.ConstantValue;import com.ft.hsm.Util.StringUtil;/* * 使用java以及jdbc執行sql指令碼的工具範例程式碼 */public class SqlHelper {    private static Logger logger;    static {        logger = Logger.getLogger(ScreenDaoImpl.class);        PropertyConfigurator.configure(ConstantValue.PATH_SCREENSERVER_LOG4J_PROPERTIES);    }    public static boolean createDBFromSQLFile(String SQLPath, String SQLFileCharsetName, String dbFilePath,            int[] retRowsExpected) {        if (StringUtil.isEmpty(SQLPath) || StringUtil.isEmpty(SQLFileCharsetName) || StringUtil.isEmpty(dbFilePath)                || 0 >= retRowsExpected.length) {            logger.error("參數錯誤");            return false;        }        // 檢驗是否己建立        File dbfile = new File(dbFilePath);        if (dbfile.exists() || dbfile.isDirectory()) {            logger.error(dbFilePath + "資料庫檔案己存在或存在同名檔案夾");            return false;        }        // 讀取SQL檔案        String sql = getText(SQLPath, SQLFileCharsetName); // "UTF-8"        if (StringUtil.isEmpty(sql)) {            logger.error("讀取SQL檔案失敗");            return false;        }        // 轉換為SQL語句        List<String> sqlList = getSql(sql, SQLFileCharsetName);        for (int i = 0; i < sqlList.size(); i++) {            logger.info(i + ":" + sqlList.get(i));        }        boolean isSuccess = false;        try {            // 執行SQL語句            int[] rows = SqlHelper.execute(getConn(dbFilePath), sqlList);            logger.info("Row count Expected:" + Arrays.toString(retRowsExpected));            // 執行結果集評鑑            isSuccess = Arrays.equals(rows, retRowsExpected);            // 調試列印執行結果集            if (null == rows || rows.length != retRowsExpected.length) {                logger.error("返回結果與期望個數不符, rows.length=" + rows.length + ", retRowsExpected.length="                        + retRowsExpected.length);            } else {                for (int index = 0; index < rows.length; index++) {                    logger.info("rows[" + index + "] return=" + rows[index] + ", expected=" + retRowsExpected[index]                            + ",sql=" + sqlList.get(index));                }            }        } catch (Exception e) {            e.printStackTrace();        }        return isSuccess;    }    private static Connection getConn(String dbFile) {        String driver = "org.sqlite.JDBC"; // "com.mysql.jdbc.Driver";        String url = "jdbc:sqlite:" + dbFile; // "資料庫連接";        //        String username = "帳號";        //        String password = "密碼";        Connection conn = null;        try {            Class.forName(driver); //classLoader,載入對應驅動            conn = (Connection) DriverManager.getConnection(url/*, username, password*/);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }        return conn;    }    public static int[] execute(Connection conn, List<String> sqlList) throws Exception {        Statement stmt = null;        stmt = conn.createStatement();        for (String sql : sqlList) {            sql = sql.trim();            if (sql != null && !sql.trim().equals(""))                stmt.addBatch(sql);        }        int[] rows = stmt.executeBatch();        logger.info("Row count returned:" + Arrays.toString(rows));        conn.close();        return rows;    }    /*     * getText方法吧path路徑裡面的檔案按行讀數來放入一個大的String裡面去     * 並在換行的時候加入\r\n     */    public static String getText(String path, String SQLFileCharsetName) {        File file = new File(path);        if (!file.exists() || file.isDirectory()) {            logger.error(path + "檔案不存在或存在同名檔案夾");            return null;        }        StringBuilder sb = new StringBuilder();        try {            FileInputStream fis = new FileInputStream(path);            InputStreamReader isr = new InputStreamReader(fis, SQLFileCharsetName);            BufferedReader br = new BufferedReader(isr);            String temp = null;            temp = br.readLine();            while (temp != null) {                if (temp.length() >= 2) {                    String str1 = temp.substring(0, 1);                    String str2 = temp.substring(0, 2);                    if (str1.equals("#") || str2.equals("--") || str2.equals("/*") || str2.equals("//")) {                        temp = br.readLine();                        continue;                    }                    sb.append(temp + "\r\n");                }                temp = br.readLine();            }            br.close();        } catch (Exception e) {            e.printStackTrace();        }        return sb.toString();    }    /*     * getSqlArray方法     * 從檔案的sql字串中分析出能夠獨立執行的sql語句並返回     */    public static List<String> getSql(String sql, String SQLFileCharsetName) {        String s = sql;        s = tryDeleteBOM(SQLFileCharsetName, s);        s = s.replaceAll("\r\n", "\r");        s = s.replaceAll("\r\n", "\r");        s = s.replaceAll("\r", "\n");        List<String> ret = new ArrayList<String>();        String[] sqlarry = s.split(";"); //用;把所有的語句都分開成一個個單獨的句子        sqlarry = filter(sqlarry);        ret = Arrays.asList(sqlarry);        return ret;    }    public static String[] filter(String[] ss) {        List<String> strs = new ArrayList<String>();        for (String s : ss) {            if (s != null && !s.equals("")) {                strs.add(s);            }        }        String[] result = new String[strs.size()];        for (int i = 0; i < strs.size(); i++) {            result[i] = strs.get(i).toString();        }        return result;    }    private static String tryDeleteBOM(String SQLFileCharsetName, String s) {        byte[] byteSQL = null;        logger.info("判斷是否含有UTF-8的BOM頭");        try {            byteSQL = s.getBytes(SQLFileCharsetName);            // 去掉UTF-8的BOM頭            if (byteSQL[0] == (byte) 0xef && byteSQL[1] == (byte) 0xbb && byteSQL[2] == (byte) 0xbf) {                logger.info("含有UTF-8的BOM頭");                logger.info("去掉UTF-8的BOM頭前" + Arrays.toString(byteSQL));                s = new String(byteSQL, 3, byteSQL.length - 3, SQLFileCharsetName);                logger.info("去掉UTF-8的BOM頭後為:" + Arrays.toString(s.getBytes(SQLFileCharsetName)));            } else {                logger.info("不含有UTF-8的BOM頭");            }        } catch (UnsupportedEncodingException ex) {            ex.printStackTrace();        }        return s;    }    public static void main(String[] args) {        String SQLPath = "config/xxx_create.utf8.sql";        String dbFile = "db" + File.separator + "test.db";        String SQLFileCharsetName = "UTF-8";        //                String SQLFileCharsetName = "GBK";        int[] retRowsExpected = { 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };        boolean isSuccess = createDBFromSQLFile(SQLPath, SQLFileCharsetName, dbFile, retRowsExpected);        logger.info("建立資料庫" + (isSuccess ? "成功" : "失敗"));    }}

　　

去掉utf-8的Bom頭：使用java以及jdbc不使用第三方庫執行sql檔案指令碼