如何根据表名获取表数据的插入sql

如何根据表名获取表数据的插入sql

声名成员变量

private  Connection conn = null;
    private  Statement sm = null;
    private  String insert = "INSERT INTO";//插入sql
    private  String values = "VALUES";//values关键字
    private  String select = "SELECT * FROM ";//查询sql
    //private static List<String> tableList = new ArrayList<String>();//全局存放表名列表
    private  List<String> selectList = new ArrayList<String>();//全局存查询列表
    private  List<String> insertList = new ArrayList<String>();//全局存放insertsql文件的数据
    private  String filePath = "E://insertSql";//绝对路径 导出数据的文件
    private  String fileName = "";//文件名称

生成插入文件

public void executeSelectSQLFile(List<String> tableList) throws Exception {
        //创建连接
        connectSQL();
        //创建查询语句
        selectSQL(tableList);
        //执行sql并拼装
        executeSQL(conn, sm, selectList, tableList);
        //创建文件
        createFile();//创建文件
    }
private  ResultSet getColumnNameAndColumeValue(Statement sm,
                                                         List selectList, List ListTable, ResultSet rs) throws SQLException {
        for (int j = 0; j < selectList.size(); j++) {
            String sql = String.valueOf(selectList.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) {
                                ColumnValue.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) {
                                ColumnValue.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) {
                                ColumnValue.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.toString());
                //System.out.println(ColumnValue.toString());
                insertSQL(ListTable.get(j).toString(), ColumnName, ColumnValue);
            }
        }
        return rs;
    }

    private   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());
    }


    /**
     * 连接数据库
     */
    private  void connectSQL() {
        try {
            conn = getConnection();
            sm = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 生成selectList
     * @throws Exception
     */
    private   List<String> selectSQL(List<String> tableList) throws Exception {

        if(tableList.size()>0){
            for(String tableName: tableList){
                StringBuffer selectSQL = new StringBuffer();
                selectSQL.append(select).append(tableName);
                selectList.add(selectSQL.toString());
            }
        }
        return selectList;
    }

    public String getFileName(){
        return fileName;
    }
    /**
     * 创建insert文件
     */
    private  void createFile() {
        File file=new File(filePath);
        if(!file.exists()){//如果文件夹不存在
            file.mkdir();//创建文件夹
        }
        fileName = UUID.randomUUID().toString().replace("-", "").substring(0,5) + ".txt";
        File finalfile = new File(filePath+"//"+fileName);
        if (!file.exists()) {
            try {
                finalfile.createNewFile();
            } catch (IOException e) {
                System.out.println("创建文件名失败！！");
                e.printStackTrace();
            }
        }
        FileWriter fw = null;
        BufferedWriter bw = null;
        try {
            fw = new FileWriter(finalfile);
            bw = new BufferedWriter(fw);
            if (insertList.size() > 0) {
                for (int i = 0; i < insertList.size(); i++) {
                    bw.append(insertList.get(i));
                    bw.append("\r\n");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bw.close();
                fw.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

类需为多例模式