Remove Utf-8 BOM Header: Use Java and JDBC do not use third-party libraries to execute SQL file scripts

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;/* *    Example code for a tool that uses Java and JDBC to execute SQL scripts */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, in T[] retrowsexpected) {if (Stringutil.isempty (SQLPath) | | Stringutil.isempty (sqlfilecharsetname) | | StringUtil.isempty (Dbfilepath) | |            0 >= retrowsexpected.length) {logger.error ("parameter Error");        return false;        }//Verify that you have created file DBFile = new file (Dbfilepath);            if (dbfile.exists () | | dbfile.isdirectory ()) {Logger.error (dbfilepath + "database file already exists or has a folder with the same name");        return false; }//Read SQL file String sql = GetText (SQLPath, sqlfilecharsetname);            "UTF-8" if (Stringutil.isempty (SQL)) {Logger.error ("failed to read SQL file");        return false;        }//Convert to SQL statement list<string> sqllist = getsql (sql, sqlfilecharsetname);        for (int i = 0; i < sqllist.size (); i++) {Logger.info (i + ":" + sqllist.get (i));        } Boolean issuccess = false;            try {//Execute SQL statement int[] rows = Sqlhelper.execute (Getconn (Dbfilepath), sqllist);            Logger.info ("Row count expected:" + arrays.tostring (retrowsexpected));       Perform result set authentication     issuccess = arrays.equals (rows, retrowsexpected); Debug print Execution result set if (null = = Rows | | Rows.length! = retrowsexpected.length) {Logger.error ("Returns a result with expected            Number does not match, 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.Drive        R "; String url = "Jdbc:sqlite:" + dbfile;        "Database Connection";        String username = "account number";        String password = "password";        Connection conn = null;try {class.forname (driver);//classloader, load corresponding driver conn = (Connection) drivermanager.getconnection (Ur        l/*, 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 = n        Ull        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 method The file inside the path is placed in a large string by a line of reading * and added \ r \ n/public static string GetText (string) in line break Path, String sqlfilecharsetname) {File File = new file (Path);            if (!file.exists () | | file.isdirectory ()) {Logger.error (path + "file does not exist or there is a folder with the same name");        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.re                        Adline ();                    Continue                } sb.append (temp + "\ r \ n");            } temp = Br.readline ();        } br.close (); } CatCH (Exception e) {e.printstacktrace ();    } return sb.tostring (); }/* * Getsqlarray method * Parses an SQL statement that can execute independently from the file's SQL string and returns the */public static list<string> GetSQL (string s        QL, 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 (";");        To separate all the statements into individual sentences 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 ("Determine if the BOM header containing the UTF-8");            try {bytesql = s.getbytes (sqlfilecharsetname); Remove UTF-8 BOM Header if (bytesql[0] = = (byte) 0xef && bytesql[1] = = (byte) 0xbb && bytesql[2] = = (by                TE) 0xbf) {logger.info ("BOM header containing UTF-8");                Logger.info ("Remove UTF-8 BOM Head" + arrays.tostring (bytesql));                s = new String (Bytesql, 3, bytesql.length-3, sqlfilecharsetname);            Logger.info ("Remove UTF-8 BOM Header after:" + arrays.tostring (s.getbytes (Sqlfilecharsetname)));            } else {logger.info ("BOM Header not containing UTF-8");        }} 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 ("CREATE DATABASE" + (issuccess?)    "Success": "Failure"); }}

　　

Remove Utf-8 BOM Header: Use Java and JDBC do not use third-party libraries to execute SQL file scripts