Use Java and JDBC to execute SQL file scripts without using third-party libraries

Use Java and JDBC to execute SQL file scripts without using third-party librariesFebruary 15, 2017 15:51:45Hits: 660

Ways to execute SQL scripts using Java
Parse SQL script, delete unnecessary comments and blank lines
To disassemble a statement by a semicolon

And put the final statement into batch and execute it last.

 Packagetest;ImportJava.io.BufferedReader;ImportJava.io.File;ImportJava.io.FileInputStream;ImportJava.io.InputStreamReader;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.Statement;Importjava.util.ArrayList;Importjava.util.Arrays;Importjava.util.List;/** Tool sample code to execute SQL scripts using Java and JDBC*/ Public classSqlHelper { Public  Static voidMain (string[] args) {String path= "File Address string"; String SQL=getText (path); List<String> Sqlarr =getsql (SQL);  for(inti=0; i<10; i++) {System.out.println (i+":"+Sqlarr.get (i)); }        Try{Sqlhelper.execute (Getconn (), Sqlarr); }Catch(Exception e) {e.printstacktrace (); }    }    Private StaticConnection Getconn () {String driver= "Com.mysql.jdbc.Driver"; String URL= "Database Connection"; String username= "Account"; String Password= "Password"; Connection Conn=NULL; Try{class.forname (driver);//classLoader, load the corresponding driveconn =(Connection) drivermanager.getconnection (URL, username, password); } Catch(ClassNotFoundException e) {e.printstacktrace (); } Catch(SQLException e) {e.printstacktrace (); }        returnConn; }     Public Static voidExecute (Connection conn, list<string> Sqlfile)throwsException {Statement stmt=NULL; stmt=conn.createstatement ();  for(String sql:sqlfile) {SQL=Sql.trim (); if(sql!=NULL&&!sql.equals ("") ) stmt.addbatch (SQL); }          int[] rows =Stmt.executebatch (); System.out.println ("Row Count:" +arrays.tostring (rows));    Conn.close (); }      /** GetText method The files inside the path are placed in a large string by the line reading * and added \ r \ n when wrapping.*/     Public Staticstring GetText (string path) {File file=NewFile (path); if(!file.exists () | |file.isdirectory ()) {            return NULL; } StringBuilder SB=NewStringBuilder (); Try{FileInputStream fis=NewFileInputStream (path); InputStreamReader ISR=NewInputStreamReader (FIS, "UTF-8"); BufferedReader BR=NewBufferedReader (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 (); }        returnsb.tostring (); }    /** Getsqlarray Method * Parses an SQL statement that can execute independently from the file's SQL string and returns*/     Public StaticList<string>getsql (String sql) {string S=SQL; S= S.replaceall ("\ r \ n", "\ R")); S= S.replaceall ("\ r", "\ n")); List<String> ret =NewArraylist<string>(); String[] Sqlarry= S.split (";");//to separate all the statements into separate sentences .Sqlarry =filter (Sqlarry); RET=arrays.aslist (Sqlarry); returnret; }     Public Staticstring[] Filter (string[] ss) {List<String> STRs =NewArraylist<string>();  for(String s:ss) {if(s!=NULL&&!s.equals ("") {Strs.add (s); }} string[] result=Newstring[strs.size ()];  for(inti=0; I<strs.size (); i++) {Result[i]=Strs.get (i). toString (); }        returnresult; }}

Use Java and JDBC to execute SQL file scripts without using third-party libraries