java导出数据库sql脚本

private static Connection conn = null;
private static Statement sm = null;
private static String schema = "lp_library";//模式名
private static String select = "SELECT * FROM";//查询sql
private static String insert = "INSERT INTO";//插入sql
private static String values = "VALUES";//values关键字
private static String[] table = {"dispose_manner","gift_type","introduction","status_manage","sys_user","t_lp","t_sh"};//table数组
private static List insertList = new ArrayList();//全局存放insertsql文件的数据

private static String URL = "jdbc:mysql://127.0.0.1:3306/lp_library?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8&autoReconnect=true";
private static String DRIVER = "com.mysql.jdbc.Driver";
private static String USERNAME = "root";
private static String PASSWORD = "123456";

 

@Autowired
private DataSource dataSource;

@RequestMapping("/download_sql")
public void downloadSql(HttpServletRequest request, HttpServletResponse response, ModelMap model) {
    SimpleDateFormat ymd = new SimpleDateFormat("yyyy-MM-dd");
    String filePath = sysConfig.getFullUploadPath()+"/lpDB.sql";
    String fileName = "lpDB.sql";
    try {
        List listSQL = new ArrayList();
        conn = dataSource.getConnection();
        sm = conn.createStatement();
        listSQL = createSQL();//创建查询语句
        executeSQL(conn, sm, listSQL);//执行sql并拼装
        createFile(filePath);//创建文件

        File file = null;
        if(filePath!=null && filePath != "") {
            file = new File(filePath);
            download(request, response, fileName, file, false);
            file.delete();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}



/**
 * 导出数据库表*@paramargs *@throwsSQLException
 */
public static void main(String[] args) throws SQLException {
    List listSQL = new ArrayList();
    connectSQL(DRIVER, URL, USERNAME, PASSWORD);//连接数据库

}

/**
 * 创建insertsql.txt并导出数据
 */
private static void createFile(String filePath) {
    File file = new File(filePath);
    if (!file.exists()) {
        try {
            file.createNewFile();
        } catch (IOException e) {
            System.out.println("创建文件名失败！！");
            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.write(insertList.get(i).toString());
                bw.write("\n");
            }
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            bw.close();
            fw.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 拼装查询语句
 *
 * @return返回 select集合
 */
private static List createSQL() {
    List listSQL = new ArrayList();
    for (int i = 0; i < table.length; i++) {
        StringBuffer sb = new StringBuffer();
        sb.append(select).append(" ").append(schema).append(".").append(table[i]);
        listSQL.add(sb.toString());
    }
    return listSQL;
}

/**
 * 连接数据库创建statement对象
 * *@paramdriver
 * *@paramurl
 * *@paramUserName
 * *@paramPassword
 */
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();
    }
}

/**
 * 执行sql并返回插入sql
 *
 * @paramconn
 * @paramsm
 * @paramlistSQL *
 * @throwsSQLException
 */
public static void executeSQL(Connection conn, Statement sm, List listSQL) throws SQLException {
    List insertSQL = new ArrayList();
    ResultSet rs = null;
    try {
        rs = getColumnNameAndColumeValue(sm, listSQL, rs);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        rs.close();
        sm.close();
        conn.close();
    }
}

/**
 * 获取列名和列值
 *
 * @return
 * @paramsm
 * @paramlistSQL
 * @paramrs
 * @throwsSQLException
 */
private static ResultSet getColumnNameAndColumeValue(Statement sm, List listSQL, ResultSet rs) throws SQLException {
    if (listSQL.size() > 0) {
        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 = null;
                    if(rs.getString(i) != null){
                        value = rs.getString(i).trim();
                    }

                    if ("".equals(value)) {
                        value = "";
                    }
                    if (i == 1 || i == columnCount) {
                        if(i==columnCount){
                            ColumnName.append(",");
                        }
                        ColumnName.append(rsmd.getColumnName(i));
                        if( i== 1){
                            if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                                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)|| Types.TINYINT == rsmd.getColumnType(i)) {
                                ColumnValue.append(value).append(",");
                            } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                                if(StringUtils.isNotBlank(value)){
                                    ColumnValue.append("'").append(value).append("',");
                                }else{
                                    ColumnValue.append("null,");
                                }
                            } else {
                                ColumnValue.append(value).append(",");

                            }
                        }else{
                            if (Types.CHAR == rsmd.getColumnType(i) || Types.VARCHAR == rsmd.getColumnType(i) || Types.LONGVARCHAR == rsmd.getColumnType(i)) {
                                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)|| Types.TINYINT == rsmd.getColumnType(i)) {
                                ColumnValue.append(value);
                            } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                                if(StringUtils.isNotBlank(value)){
                                    ColumnValue.append("'").append(value).append("'");
                                }else{
                                    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)) {
                            ColumnValue.append("'").append(value).append("'").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)|| Types.TINYINT == rsmd.getColumnType(i)) {
                            ColumnValue.append(value).append(",");
                        } else if (Types.DATE == rsmd.getColumnType(i) || Types.TIME == rsmd.getColumnType(i) || Types.TIMESTAMP == rsmd.getColumnType(i)) {
                            //ColumnValue.append("timestamp'").append(value).append("',");
                            if(StringUtils.isNotBlank(value)){
                                ColumnValue.append("'").append(value).append("',");
                            }else{
                                ColumnValue.append("null,");
                            }
                        } else {
                            ColumnValue.append(value).append(",");
                        }
                    }
                }
                System.out.println(ColumnName.toString());
                System.out.println(ColumnValue.toString());
                insertSQL(ColumnName, ColumnValue,table[j]);
            }
        }
    }
    return rs;
}

/**
 * 拼装insertsql放到全局list里面
 * @paramColumnName
 * @paramColumnValue
 */
private static void insertSQL(StringBuffer ColumnName, StringBuffer ColumnValue,String tableName) {
    StringBuffer insertSQL = new StringBuffer();
    //insertSQL.append(insert).append(" ").append(schema).append(".")
            //.append(tableName).append("(").append(ColumnName.toString()).append(")").append(values).append("(").append(ColumnValue.toString()).append(");");
    insertSQL.append(insert).append(" ").append(schema).append(".")
            .append(tableName).append(" ").append(values).append("(").append(ColumnValue.toString()).append(");");
    insertList.add(insertSQL.toString());
    System.out.println(insertSQL.toString());

}

//文件下载

public static void download(HttpServletRequest request, HttpServletResponse response, String filename, File file, boolean del) throws IOException {
   OutputStream outp = null;
   FileInputStream in = null;
   try {
      String userAgent = request.getHeader("user-agent");
      response.setCharacterEncoding("UTF-8");
      response.setContentType("application/octet-stream");
      // 针对不同的浏览器需要有不同的编码，否则文件名会出现乱码
      if (userAgent.toLowerCase().indexOf("firefox") > 0) {
         filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
      } else if (userAgent.toUpperCase().indexOf("MSIE") > 0) {
         filename = URLEncoder.encode(filename, "UTF-8");
      } else if (userAgent.toUpperCase().indexOf("CHORM") > 0) {
         filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
      } else {
         filename = URLEncoder.encode(filename, "UTF-8");
      }
      response.addHeader("Content-disposition", "attachment;filename=" + filename);
      outp = response.getOutputStream();
      in = new FileInputStream(file);
      byte[] b = new byte[1024];
      int i = 0;
      while ((i = in.read(b)) > 0) {
         outp.write(b, 0, i);
      }
      outp.flush();
   }
   finally {
      if (in != null) {
         try {
            in.close();
            in = null;
         }
         catch (IOException e) {
            e.printStackTrace();
         }
      }
      if (outp != null) {
         try {
            outp.close();
            outp = null;
         }
         catch (IOException e) {
            e.printStackTrace();
         }
      }
      if (file != null && del) {
         if (file.exists()) {
            file.delete();
         }
      }
   }
}