原生JDBC方式导出MySQL数据库

现在的应用系统越来越多的都是业务系统与数据库系统是分离的，这里就会涉及到数据库备份的问题。如果业务系统与数据库是在同一服务器，可以很简单的用mysql自带的命令：

mysqldump --databases dbName > dbNameBak.sql

现在是数据库服务器与应用系统不在同一台服务器，我们可以通过JDBC方式进行备份数据库操作，

代码如下：
备份数据库主要方法：backDB

/**
 * 备份数据
 */
public void backupDB() {
    Connection conn = null;
    String dbName = null;
    try {
        conn = jdbcDao.getDataSource().getConnection();
        dbName = conn.getCatalog();
    } catch (SQLException e) {
        throw new RuntimeException("无法获取数据库连接！");
    }    
    String tableName = null,
            procName = null;
    BufferedWriter writer = null;
    Statement stmtInfo = null, stmtData = null;
    ResultSet rsInfo = null, rsData = null;
    try {
        //存放文件目录
        String folderBackup = CommonUtils.absoluteClassPathUsrPath(Constants.FOLDER_BACKUP);
        //文件名
        String sqlFilename = String.format("%s-%s.sql", dbName, Dates.dateToString(new Date(), Constants.DATETIME_FORMAT_FILENAME));
        writer = new BufferedWriter(new FileWriter(folderBackup + Constants.PATH_SEPARATOR + sqlFilename));
        
        /*
         * 头内容
         */
        writer.write("/*!40101 SET NAMES utf8 */;");
        writer.newLine();
        writer.write("/*!40101 SET SQL_MODE=''*/;");
        writer.newLine();
        writer.write("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
        writer.newLine();
        writer.write("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
        writer.newLine();
        writer.write("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
        writer.newLine();
        writer.write("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;");
        writer.flush();
        
        /*
         * 导出表数据
         */
        stmtInfo = conn.createStatement();
        rsInfo = stmtInfo.executeQuery(String.format("SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE = 'BASE TABLE'", dbName));
        // 遍历所有表
        while(rsInfo.next()) {
            tableName = rsInfo.getString(1);
            this.dbBackExportTable(conn, tableName, writer, true);
        }    //end for tables
        if(null != rsInfo) {
            rsInfo.close();
        }
        if(null != stmtInfo) {
            stmtInfo.close();
        }

        /*
         * 导出存储过程信息
         */
        stmtInfo = conn.createStatement();
        rsInfo = stmtInfo.executeQuery(String.format("SELECT `SPECIFIC_NAME` from `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = '%s' AND ROUTINE_TYPE = 'PROCEDURE'; ", dbName));
        while (rsInfo.next()) {
            procName = rsInfo.getString(1);
            
            /* 存储过程结构 */
            stmtData = conn.createStatement();
            rsData = stmtData.executeQuery(String.format("SHOW CREATE PROCEDURE `%s`", procName));
            if(!rsData.next()) {
                continue ;
            }
            writer.newLine();
            writer.newLine();
            writer.write(String.format("/* Procedure structure for procedure `%s` */", procName));
            writer.newLine();
            writer.write(String.format("/*!50003 DROP PROCEDURE IF EXISTS  `%s` */;", procName));
            writer.newLine();
            writer.write("DELIMITER $$");
            writer.newLine();
            writer.append("/*!50003 ").append(rsData.getString(3)).append(" */$$");
            writer.newLine();
            writer.write("DELIMITER ;");
            if(null != rsData) {
                rsData.close();
            }
            if(null != stmtData) {
                stmtData.close();
            }
        }
        
        /*
         * 尾内容
         */
        writer.newLine();
        writer.newLine();
        writer.write("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;");
        writer.newLine();
        writer.write("/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;");
        writer.newLine();
        writer.write("/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;");
        writer.newLine();
        writer.write("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;");
        writer.newLine();
        
        writer.close();
        if(null != stmtData) {
            stmtData.close();
        }
        if(null != rsData) {
            rsData.close();
        }
        if(null != stmtInfo) {
            stmtInfo.close();
        }
        if(null != rsInfo) {
            rsInfo.close();
        }
    } catch (Exception e) {
        throw new RuntimeException(e.getMessage());
    }
}

备份具体表数据

/**
 * 导出表数据
 * @param conn
 * @param tableName
 * @param writer
 * @param bulkFlag   是否将数据放在一起
 * @throws SQLException 
 * @throws IOException 
 */
private void dbBackExportTable(Connection conn, String tableName, BufferedWriter writer, boolean bulkFlag) throws SQLException, IOException {
    Statement stmt = null;
    ResultSet rs = null;
    /* 表结构 */
    stmt = conn.createStatement();
    rs = stmt.executeQuery(String.format("SHOW CREATE TABLE `%s`", tableName));
    if(!rs.next()) {
        return ;
    }
    writer.newLine();
    writer.newLine();
    writer.write(String.format("/*Table structure for table `%s` */", tableName));
    writer.newLine();
    writer.write(String.format("DROP TABLE IF EXISTS `%s`;", tableName));
    writer.newLine();                
    writer.write(rs.getString(2) + ";");
    writer.newLine();
    if(null != rs) {
        rs.close();
    }
    if(null != stmt) {
        stmt.close();
    }
    
    /* 导出表数据 */
    // 先获取记录数
    stmt = conn.createStatement();
    rs = stmt.executeQuery(String.format("SELECT COUNT(1) FROM `%s`", tableName));
    int rowCount = rs.next() ? rs.getInt(1) : 0;
    if(0 >= rowCount) {
        writer.flush();
        return ;
    }
    writer.write(String.format("/*Data for the table `%s` */", tableName));
    writer.newLine();
    
    stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    stmt.setFetchSize(Integer.MIN_VALUE);
    stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
    rs = stmt.executeQuery(String.format("SELECT * FROM `%s`", tableName));
    int colCount = 0;
    Object colValue = null;
    // 所有数据用","连接
    if(!bulkFlag) {
        while(rs.next()) {
            colCount = rs.getMetaData().getColumnCount();
            
            writer.write(String.format("INSERT INTO `%s` VALUES (", tableName));
            // 获取表每一列数据
            for(int j = 0; j < colCount; j ++) {
                if(j > 0) {
                    writer.write(',');    
                }
                colValue = rs.getObject(j + 1);
                if(null != colValue) {
                    writer.write(String.format("'%s'", CommonUtils.escapeString(colValue.toString())));
                } else {
                    writer.write("NULL");
                }
            }    //end for one record columns
            writer.write(");");
            writer.newLine();
            writer.flush();
        }    //end for table records
    }
    // 每行数据独立分开
    else {
        ResultSetMetaData rsMetaData = null;
        int counter = 0;
        while(rs.next()) {
            ++ counter;
            rsMetaData = rs.getMetaData();
            colCount = rsMetaData.getColumnCount();

            // 第一条记录，则列出列名
            if(1 == counter) {
                writer.write(String.format("INSERT INTO `%s` (", tableName));
                for(int i = 0; i < colCount; i ++) {
                    if(i > 0) {
                        writer.write(",");
                    }
                    writer.append('`').append(rsMetaData.getColumnName(i + 1)).append('`');
                }
                writer.append(") VALUES ");
            }
            // 获取表每一列数据
            for(int j = 0; j < colCount; j ++) {
                writer.write((0 >= j) ? '(' : ',');                        
                colValue = rs.getObject(j + 1);
                if(null != colValue) {
                    writer.write(String.format("'%s'", CommonUtils.escapeString(colValue.toString())));
                } else {
                    writer.write("NULL");
                }
            }    //end for one record columns
            // 是否是最后记录
            if(rowCount > counter) {
                writer.write("),");
            } else {
                writer.write(");");
            }
            writer.flush();
        }    //end for table records
    }
    
    if(null != rs) {
        rs.close();
    }
    if(null != stmt) {
        stmt.close();
    }
}

说明：上面用到的处理数据方法CommonUtils.escapeString(String x)))，请参看文章：MySQL字符转义涉及的问题及解决
需要注意的是，从数据库获取的原数据如果涉及单、双引号，虽然不会影响导出的sql内容，但是导出的内容就可能无法正常再导入到原数据库，原因就是数据内容包含有单双引号（PS：此处排查浪费了近2天时间）

待完善内容：

• 上面所有的导出sql相关内容都放在一个StringBuilder对象中， 对于数据量不大并且内存足够的情况下没有问题，但是如果数据库数据比较大或者分配给java内存有限，可能导致内存不够而报错java.lang.OutOfMemoryError: Java heap space。

参考文章：

• How to take MySql DB backup from java (JDBC and Relational Databases forum at Coderanch)
• mysql中单双引号的特殊使用 以及mysql常见的转移字符
• MySQL JDBC Memory Usage on Large ResultSet
• Java Code:db2sql
• 正确使用MySQL JDBC setFetchSize()方法解决JDBC处理大结果集 java.lang.OutOfMemoryError: Java heap space