Methods for exporting INSERT into statements based on MySQL table data

Because the project needs to be based on the MySQL table data generation INSERT INTO statement, found some out-of-the-box code, the original author who did not know, but found that there is a bug, can not be applied, hence the code of others have been modified. You can export the INSERT INTO statement better after the modification. The code is as follows:

Package Dwz.interaction;import java.io.*;import java.sql.*;import java.util.arraylist;import java.util.List;/** * Update by Internetroot on 2014-09-06.    */public class Sqlitesqlgenerator {private static Connection conn = null;    private static Statement SM = null; private static string insert = "INSERT into";//Insert SQL private static string values = "values";//values keyword private sta Tic list<string> tablelist = new arraylist<string> ();//Global storage table Name list private static list<string> Insertlis t = new arraylist<string> ()///Global data store insertsql file private static String FilePath = "e://insertsql.txt";//absolute path Export data    The file public static string Generatetabledatasql (String sql, string[] params) {return null; public static string Executeselectsqlfile (string file, string[] params) throws Exception {list<string>        Listsql = new arraylist<string> (); Connectsql ("Com.mysql.jdbc.Driver", "jdbc:mysql://127.0.0.1:3308/htedu?useunicode=true&amP;characterencoding=utf-8 "," Root "," ");//Connect Database Listsql = createsql (file);//Create QUERY Statement EXECUTESQL (conn, SM, List    SQL, tablelist);//Execute SQL and assemble CreateFile ();//create file return null; /** * Assemble query statement * * @return return to select Collection */private static list<string> Createsql (String file) th        Rows Exception {list<string> listsql = new arraylist<string> ();        BufferedReader br = null;        InputStreamReader FR = null;        InputStream is = null;        int i;//The first character position of the table name int k;//The position of the last character of the table list String tableName;            try {is = SqliteSQLGenerator.class.getResourceAsStream (file);            FR = new InputStreamReader (IS);            br = new BufferedReader (FR);                String rec = null;//row while (rec = Br.readline ()) = null) {rec = Rec.tolowercase ();                i = Rec.indexof ("from", 1) + 5;                K = Rec.indexof ("", I);           if (k = =-1) {         K = Rec.length ();                }                ;                TableName = Rec.substring (i, k);                Tablelist.add (TableName);            Get all query Statements Listsql.add (Rec.tostring ());            }} finally {if (BR! = null) {br.close ();            } if (fr! = null) {fr.close ();            } if (is = null) {is.close ();    }} return listsql;        }/** * Create insertsql.txt and Export data */private static void CreateFile () {File File = new file (FilePath);            if (!file.exists ()) {try {file.createnewfile (); } catch (IOException e) {System.out.println ("Create File name failed!!                ");            E.printstacktrace ();        }} FileWriter fw = null;        BufferedWriter bw = NULL;            try {fw = new FileWriter (file);        BW = new BufferedWriter (FW);    if (insertlist.size () > 0) {for (int i = 0; i < insertlist.size (); i++) {bw.                    Append (Insertlist.get (i));                Bw.append ("\ n");        }}} catch (IOException e) {e.printstacktrace ();                } finally {try {bw.close ();            Fw.close ();            } catch (IOException e) {e.printstacktrace (); }}}/** * Connect database Create statement Object * * @param driver * @param URL * @param UserName * @p        Aram Password */public static void Connectsql (string driver, string URL, String UserName, String Password) {            try {class.forname (driver). newinstance ();            conn = drivermanager.getconnection (URL, UserName, Password);        SM = Conn.createstatement ();        } catch (Exception e) {e.printstacktrace (); }}/** * Executes SQL and returns INSERT SQL * * @param conn *@param sm * @param listsql * @throws java.sql.SQLException */public static void ExecuteSQL (Connection conn , Statement SM, list listsql, List listtable) throws SQLException {list<string> insertsql = new Arraylist&lt ;        String> ();        ResultSet rs = null;        try {rs = getcolumnnameandcolumevalue (SM, Listsql, listtable, RS);        } catch (SQLException e) {e.printstacktrace ();            } finally {rs.close ();            Sm.close ();        Conn.close (); }}/** * Get column names and column values * * @param SM * @param listsql * @param rs * @return * @throws java.sq                                                         L.sqlexception */private static ResultSet Getcolumnnameandcolumevalue (Statement SM, List Listsql, List listtable, ResultSet rs) throws SQLException {for (int j = 0; J < Listsql.size ();            J + +) {String sql = string.valueof (Listsql.get (j)); rs = Sm.ExecuteQuery (SQL);            ResultSetMetaData RSMD = Rs.getmetadata ();            int columnCount = Rsmd.getcolumncount ();                while (Rs.next ()) {StringBuffer ColumnName = new StringBuffer ();                StringBuffer columnvalue = new StringBuffer ();                    for (int i = 1; I <= columnCount; i++) {String value = rs.getstring (i);                        if (i = = ColumnCount) {columnname.append (Rsmd.getcolumnname (i)); if (Types.char = = Rsmd.getcolumntype (i) | | Types.varchar = = Rsmd.getcolumntype (i) | |                                Types.longvarchar = = Rsmd.getcolumntype (i)) {if (value = = null) {                            Columnvalue.append ("null");                            } else {columnvalue.append ("'"). Append (Value). Append ("'"); }} else if (Types.smallint = = Rsmd.getcoluMntype (i) | | Types.integer = = Rsmd.getcolumntype (i) | | Types.bigint = = Rsmd.getcolumntype (i) | | Types.float = = Rsmd.getcolumntype (i) | | Types.double = = Rsmd.getcolumntype (i) | | Types.numeric = = Rsmd.getcolumntype (i) | | Types.decimal = = Rsmd.getcolumntype (i)) {if (value = = null) {C                            Olumnvalue.append ("null");                            } else {columnvalue.append (value); }} else if (types.date = = Rsmd.getcolumntype (i) | | Types.time = = Rsmd.getcolumntype (i) | |                                Types.timestamp = = Rsmd.getcolumntype (i)) {if (value = = null) {                            Columnvalue.append ("null"); } else {columnvalue.append ("timestamp"). Append (Value). Append ("'"); }} else {if (value = = null) {Columnval                            Ue.append ("null");                            } else {columnvalue.append (value); }}} else {columnname.append (Rsmd.getcolumnname (i) + ",                        "); if (Types.char = = Rsmd.getcolumntype (i) | | Types.varchar = = Rsmd.getcolumntype (i) | |                                Types.longvarchar = = Rsmd.getcolumntype (i)) {if (value = = null) {                            Columnvalue.append ("null,");                            } else {columnvalue.append ("'"). Append (Value). Append ("',"); }} else if (Types.smallint = = Rsmd.getcolumntype (i) | |                        Types.integer = = Rsmd.getcolumntype (i)        || Types.bigint = = Rsmd.getcolumntype (i) | | Types.float = = Rsmd.getcolumntype (i) | | Types.double = = Rsmd.getcolumntype (i) | | Types.numeric = = Rsmd.getcolumntype (i) | | Types.decimal = = Rsmd.getcolumntype (i)) {if (value = = null) {C                            Olumnvalue.append ("null,");                            } else {Columnvalue.append (value). Append (","); }} else if (types.date = = Rsmd.getcolumntype (i) | | Types.time = = Rsmd.getcolumntype (i) | |                                Types.timestamp = = Rsmd.getcolumntype (i)) {if (value = = null) {                            Columnvalue.append ("null,");                            } else {columnvalue.append ("timestamp"). Append (Value). Append ("',"); }} ELSE {if (value = = null) {Columnvalue.append ("null,");                            } else {Columnvalue.append (value). Append (","); }}}}//system.out.println (Columnname.tostr                ing ());                System.out.println (Columnvalue.tostring ());            Insertsql (Listtable.get (j). ToString (), ColumnName, Columnvalue);    }} return RS;  /** * Assemble insertsql into global list * * @param ColumnName * @param columnvalue */private static void        Insertsql (String TableName, StringBuffer ColumnName, StringBuffer columnvalue) {        StringBuffer insertsql = new StringBuffer (); Insertsql.append (Insert). Append (""). Append (TableName). Append ("("). Append (Columnname.tostring ()). Append (") "). Append (values). Append ("("). Append (Columnvalue.tostring ()). Append ("); ");        Insertlist.add (Insertsql.tostring ());    System.out.println (Insertsql.tostring ()); } public static void Main (string[] args) throws Exception {//string file1 = "/config/export_sqlite_data_clear.c        FG ";        Executeselectsqlfile (file1, NULL);        String file2 = "/config/export_sqlite_data_select.cfg";    Executeselectsqlfile (file2, NULL); }}

The CFG file form applied in the code:

SELECT * FROM T_chapterselect * from T_question_type

There are no problems found in the use, if there is a bug, please the great God to improve.

Methods for exporting INSERT into statements based on MySQL table data