1.最近開發(fā)java 備份數(shù)據(jù)庫功能發(fā)現(xiàn)網(wǎng)上都是通過oracle客戶端進行備份证九，部署項目的服務器還裝oracle服務端那不是太麻煩了,mysql 也是通過客戶端實現(xiàn),
我折騰了好久終于不用客戶端實現(xiàn)
實現(xiàn)環(huán)境為spring boot,實現(xiàn)方法如下：

@Service
public class BackUpDoServiceImpl extends ServiceImpl<BackUpDoMapper, BackUpDo> implements BackUpDoService {
Logger logger = LoggerFactory.getLogger(BackUpDoServiceImpl.class);
    @Value("${spring.datasource.driverClassName}")
    private String jdbcDriver;

    @Value("${spring.datasource.druid.first.url}")
    private String jdbcUrl;
    @Resource
    private UploadProperties uploadProperties;

    @Value("${spring.datasource.druid.first.username}")
    private String jdbcUserName;

    @Value("${spring.datasource.druid.first.password}")
    private String jdbcPassword;
    private String orcaleSQL = "SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, U.object_name), U.OBJECT_TYPE "
            + "FROM USER_OBJECTS U "
            + "where U.OBJECT_TYPE = 'TABLE' "
            + "or U.OBJECT_TYPE = 'VIEW' "
            + "or U.OBJECT_TYPE = 'INDEX' "
            + "or U.OBJECT_TYPE = 'PROCEDURE' "
            + "or U.OBJECT_TYPE = 'SEQUENCE' "
            + "or U.OBJECT_TYPE = 'TRIGGER' " + "order by U.OBJECT_TYPE desc";

  @Override
  public String backupData() throws IOException, ClassNotFoundException, SQLException {

        File file=new File(uploadProperties.getBasePath());
        if(!file.exists()){
            file.mkdir();
        }
        Format f = new SimpleDateFormat("yyyyMMddHmmss");
        Date date = new Date();
        //oracle備份模式
        if (jdbcDriver.contains("oracle")) {
            String tablesFile = "";
            String sequencesFile = "";
            String indexsFile = "";
            String createTableStr=f.format(date) + "oracle創(chuàng)表.sql";
            String dataStr=f.format(date) + "oracle數(shù)據(jù)" + ".sql";
            File dataFile=null;
            StringBuilder fwT = new StringBuilder();
            StringBuilder fwS = new StringBuilder();
            StringBuilder fwI = new StringBuilder();
            File outFile=null;

            Class.forName(jdbcDriver);
            Connection con = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);
            Statement statement = con.createStatement();
            ResultSet rs = statement.executeQuery(orcaleSQL);

             outFile = new File(uploadProperties.getBasePath() + "/" + createTableStr);
            if (!outFile.exists()) {
                /* 創(chuàng)建文件夾*/
                outFile.createNewFile();
            }
            FileWriter fw = new FileWriter(outFile, true);
            while (rs.next()) {
                Clob ddl = rs.getClob(1);
                String objectName = rs.getString(2);

                String ddlStr = ddl.getSubString(1L, (int) ddl.length());
                if ("TABLE".equals(objectName)) {
                    fwT.append(ddlStr).append("\r\n");
                    ;
                }
                if ("SEQUENCE".equals(objectName)) {
                    fwS.append(ddlStr).append("\r\n");
                    ;
                }
                if ("INDEX".equals(objectName)) {
                    fwI.append(ddlStr).append("\r\n");
                    ;
                }
            }
            fw.write("---創(chuàng)表腳本---\r\n");
            fw.write(fwT.toString());
            fw.write("---序列腳本---\r\n");
            fw.write(fwS.toString());
            fw.write("---索引腳本---\r\n");
            fw.write(fwI.toString());
            fw.flush();
            fw.close();
            DatabaseMetaData dbmd = con.getMetaData();
            /*getTables獲取表對象時用戶名必須為大寫*/
            String dbUserNameStr = jdbcUserName.toUpperCase();
            rs = dbmd.getTables("null", dbUserNameStr, "%", new String[]{"TABLE"});
            List<String> tableNames = new ArrayList<>();
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tableNames.add(tableName);
            }
            if (tableNames != null && tableNames.size() > 0) {
                 dataFile = new File(uploadProperties.getBasePath() + "/" + dataStr);
                if (!dataFile.exists()) {
                    /* 創(chuàng)建文件*/
                    dataFile.createNewFile();
                }
                FileWriter dataFw = new FileWriter(dataFile, true);
                for (String str : tableNames) {
                    StringBuilder datWt = new StringBuilder();
                    try {
                        String insertSqlStr = SqlUtils.genOracleInsertInto(str, "", statement);
                        dataFw.write(insertSqlStr);

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                dataFw.flush();
                dataFw.close();
            }
            rs.close();
            statement.close();
            con.close();
            /** 壓縮方法2  */
            List<File> fileList = new ArrayList<>();
            if(dataFile!=null){
                fileList.add(dataFile);
            }
            if(outFile!=null){
                fileList.add(outFile);
            }
            FileOutputStream fos2 = new FileOutputStream(new File(uploadProperties.getBasePath()+f.format(date)+"oracle數(shù)據(jù)"+".zip"));
            try {
                FileUtils.toZip(fileList, fos2);
                dataFile.delete();
                outFile.delete();
                fos2.close();
            }catch (Exception ex){
                logger.error("壓縮mysql腳本失敗"+ex.toString());
                return "";
            }
            return uploadProperties.getBasePath()+f.format(date)+"oracle數(shù)據(jù)"+".zip";
        }
        if(jdbcDriver.contains("mysql")){

            File dataFile=null;
            Class.forName(jdbcDriver);
            Connection con = DriverManager.getConnection(jdbcUrl, jdbcUserName, jdbcPassword);

            Statement statement = con.createStatement();

            DatabaseMetaData dbmd = con.getMetaData();

            String dbName="";
            Integer index=jdbcUrl.lastIndexOf("/");
            String before=jdbcUrl.substring(0,index);
            String after=jdbcUrl.substring(index+1);
            if(after.contains("?")){
                index=after.lastIndexOf("?");
                dbName=after.substring(0,index);
            }
            con.getMetaData().getDatabaseProductName();
              ResultSet  rs = dbmd.getTables(dbName, null, null, new String[] { "TABLE" });
            List<String> tableNames = new ArrayList<>();
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                tableNames.add(tableName);
            }
            if (tableNames != null && tableNames.size() > 0) {
                 dataFile = new File(uploadProperties.getBasePath() + "/" + f.format(date) + "mysql數(shù)據(jù)" + ".sql");
                if (!dataFile.exists()) {
                    /* 創(chuàng)建文件*/
                    dataFile.createNewFile();
                }
                FileWriter dataFw = new FileWriter(dataFile, true);
                for (String str : tableNames) {
                    StringBuilder datWt = new StringBuilder();
                    try {
                        String insertSqlStr = SqlUtils.getMysqlInsertSql(statement, str, "");
                        dataFw.write(insertSqlStr);

                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                dataFw.flush();
                dataFw.close();
            }
                rs.close();
                statement.close();
                con.close();
            /** 壓縮方法2  */
            List<File> fileList = new ArrayList<>();
            if(dataFile!=null){
                fileList.add(dataFile);
            }
            FileOutputStream fos2 = new FileOutputStream(new File(uploadProperties.getBasePath()+f.format(date)+"mysql數(shù)據(jù)"+".zip"));
           try {
               FileUtils.toZip(fileList, fos2);
               dataFile.delete();
               fos2.close();
           }catch (Exception ex){
               logger.error("壓縮mysql腳本失敗"+ex.toString());
               return "";
           }
            return uploadProperties.getBasePath()+f.format(date)+"mysql數(shù)據(jù)"+".zip";
        }
        return "";
    }

核心代碼：Sqlutils代碼如下：

 * Copyright
 *
 * @author chenwenchao
 * @date 2018/11/15 0015
 * Description:
 */
public class SqlUtils {

    /**
     * oracle生成插入語句語句
     *
     * @param tableName
     * @param whereSql
     * @param stmt
     * @return
     */
    public static String genOracleInsertInto(String tableName, String whereSql, Statement stmt) {
        ResultSet results = null;
        if (tableName.equals(null)) {
            return "/* No table! */\n";
        }
        // 存放表屬性名
        String[] columnNames;
        // 存放一條記錄的值
        String[] oneRowData;
        String insertOneTalbeDatas = null;
        // 查詢結(jié)果屬性
        ResultSetMetaData rsma = null;
        // 列數(shù)
        int numCols = 0;
        // 行數(shù)
        int numRows = 0;
        // 查詢語句內(nèi)容
        String queryStr = "SELECT * FROM " + tableName;
        if (whereSql != null && whereSql.length() > 0) {
            queryStr = queryStr + " where " + whereSql;
        }

        try {
            // 獲取一個查詢結(jié)果集為一張表的內(nèi)容
            results = stmt.executeQuery(queryStr);
            // 表的記錄數(shù)
            numRows = 0;
            boolean more = results.next();
            while (more) {
                numRows++;
                more = results.next();
            }
            // 初始化獲取查詢記錄集屬性
            rsma = results.getMetaData();
            // 初始化表的列數(shù)
            numCols = rsma.getColumnCount();
            columnNames = new String[numCols];
            // 初始化獲取列名
            for (int i = 0; i < numCols; i++) {
                columnNames[i] = new String(rsma.getColumnName(i + 1));
            }
            // 初始化存放一條記錄的數(shù)據(jù)
            oneRowData = new String[numCols];
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("three" + e.getMessage());
            return warning;
        }
        try {
            // 獲取一個查詢結(jié)果集為一張表的內(nèi)容
            results = stmt.executeQuery(queryStr);
            // 初始化獲取查詢記錄集屬性
            rsma = results.getMetaData();
            //獲取記錄的內(nèi)容
            StringBuffer insertAllRows = new StringBuffer();
            boolean more = results.next();
            int rowCounts = 0;
            while (more) {
                for (int i = 1; i <= numCols; i++) {
                    oneRowData[i - 1] = results.getString(i);
                }
                insertAllRows.append("insert into " + tableName.trim() + " (");
                for (int i = 0; i < numCols; i++) {
                    if (i != 0) {
                        insertAllRows.append(',');
                    }
                    insertAllRows.append(columnNames[i]);
                }
                insertAllRows.append(") values (");
                String columnType = "";
                for (int i = 0; i < numCols; i++) {
                    if (i != 0) {
                        insertAllRows.append(',');
                    }
                    columnType = rsma.getColumnTypeName(i + 1);
                    columnType = columnType.toUpperCase();
                    if (columnType.indexOf("VAR") >= 0 ||
                            columnType.indexOf("CHAR") >= 0 ||
                            columnType.indexOf("DATE") >= 0 ||
                            columnType.indexOf("TIME") >= 0 ||
                            columnType.indexOf("TEXT") >= 0 ||
                            columnType.indexOf("BINARY") >= 0) {
                        if (columnType.indexOf("DATE") >= 0) {
                            //  String NotNull="TO_DATE(\'" +   oneRowData[i].trim() + "', 'YYYY-MM-DD HH24:MI:SS')";
                            insertAllRows.append("" +
                                    ((oneRowData[i] == null ||
                                            oneRowData[i].length() == 0) ? "NULL" :
                                            "TO_DATE(\'" + oneRowData[i].trim() + "', 'YYYY-MM-DD HH24:MI:SS')"));
                        } else {
                            insertAllRows.append("'" +
                                    ((oneRowData[i] == null ||
                                            oneRowData[i].length() == 0) ? "" :
                                            oneRowData[i].trim()) + "'");
                        }
                    } else {
                        insertAllRows.append(((oneRowData[i] == null ||
                                oneRowData[i].length() == 0) ? "\'\'" :
                                oneRowData[i]));
                    }
                }
                insertAllRows.append(");\n");
                more = results.next();
            }
            results.close();
            if (insertAllRows.length() != 0) {
                insertOneTalbeDatas = new String(insertAllRows);
            } else {
                insertOneTalbeDatas = "/* Not found data in the table: " + tableName +
                        "! */\n";
            }
            insertAllRows.delete(0, insertAllRows.length() - 1);
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("four");
            e.printStackTrace();
            return warning;
        }
        return insertOneTalbeDatas;
    }

    /**
     * 插入sql
     */
    private static String insert = "INSERT INTO";

    /**
     * values關(guān)鍵字
     */
    private static String values = "VALUES";


    /**
     *
     * @param stmt
     * @param tableName
     * @param whereSql
     * @return
     * @throws SQLException
     */
    public static String getMysqlInsertSql(Statement stmt, String tableName, String whereSql)
            throws SQLException {
        ResultSet results = null;
        if (tableName.equals(null)) {
            return "/* No table! */\n";
        }
        // 查詢語句內(nèi)容
        String queryStr = "SELECT * FROM " + tableName;
        if (whereSql != null && whereSql.length() > 0) {
            queryStr = queryStr + " where " + whereSql;
        }
        System.out.println(queryStr);
        StringBuffer insertSQL = new StringBuffer();
        try {
            // 獲取一個查詢結(jié)果集為一張表的內(nèi)容
            results = stmt.executeQuery(queryStr);
            ResultSetMetaData rsmd = results.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (results.next()) {
                StringBuffer columnName = new StringBuffer();
                StringBuffer columnValue = new StringBuffer();

                for (int i = 1; i <= columnCount; i++) {
                    String value = results.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 {
                                //append("timestamp'")
                                columnValue.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 {
                                //append("timestamp'")
                                columnValue.append(value).append("',");
                            }
                        } else {
                            if (value == null) {
                                columnValue.append("null,");
                            } else {
                                columnValue.append(value).append(",");
                            }
                        }
                    }
                }
                insertSQL.append(insert).append(" ").append(tableName).append("(").append(columnName.toString()) .append(")").append(values).append("(").append(columnValue.toString()).append(");\n");
            }
        } catch (Exception e) {
            String warning = "/* Not found the table: " + tableName + "! */\n";
            System.out.println("three" + e.getMessage());
            return warning;
        }

        return insertSQL.toString();
    }

}

壓縮工具類FileUtils也提供給大家

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.zip.ZipOutputStream;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
/**
 * Copyright
 *
 * @author chenwenchao
 * @date 2018/11/15 0015
 * Description:
 */
public class FileUtils {

    private static final int  BUFFER_SIZE = 2 * 1024;
    /**

     * 壓縮成ZIP 方法2
     * @param srcFiles 需要壓縮的文件列表
     * @param out           壓縮文件輸出流
     * @throws RuntimeException 壓縮失敗會拋出運行時異常
     */
    public static void toZip(List<File> srcFiles , OutputStream out)throws RuntimeException {
        long start = System.currentTimeMillis();
        ZipOutputStream zos = null ;
        try {
            zos = new ZipOutputStream(out);
            for (File srcFile : srcFiles) {
                byte[] buf = new byte[BUFFER_SIZE];
                zos.putNextEntry(new ZipEntry(srcFile.getName()));
                int len;
                FileInputStream in = new FileInputStream(srcFile);
                while ((len = in.read(buf)) != -1){
                    zos.write(buf, 0, len);
                }
                zos.closeEntry();
                in.close();
            }
            long end = System.currentTimeMillis();
            System.out.println("壓縮完成西乖，耗時：" + (end - start) +" ms");
        } catch (Exception e) {
            throw new RuntimeException("zip error from ZipUtils",e);
        }finally{
            if(zos != null){
                try {
                    zos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

目前oracle只是實現(xiàn)普通創(chuàng)建表腳本绍撞，其中表的字段加注釋還沒實現(xiàn)宋梧，沒想到好法子，備份數(shù)據(jù)腳本硫狞，mysql實現(xiàn)備份數(shù)據(jù)