java通过jdbc复制表数据实现备份

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;

@Service
public class DBService {

    private static Logger LOG = LoggerFactory.getLogger(DBService.class);

    @Autowired
    private DBProperties dbProperties;

    public String dump(String data){
        if(StringUtils.isBlank(data)){
            LOG.info("--------data is null---");
            return null;
        }
        String[] ss = data.split("\\.");
        if(ss == null || ss.length < 3){
            LOG.info("--------ss size < 3---");
            return null;
        }
        String db = ss[0];
        String schemaName = ss[1];
        String tableName = ss[2];
        String newTableName = tableName + "_" + SnowflakeUtil.getUniqueId();
        try {
            // 连接到源数据库
            Connection sourceConnection = DriverManager.getConnection(dbProperties.getUrl(), dbProperties.getUsername(), dbProperties.getPassword());

            // 连接到目标数据库
            Connection targetConnection = DriverManager.getConnection(dbProperties.getUrl(), dbProperties.getUsername(), dbProperties.getPassword());

            // 获取源表的元数据（结构信息）
            DatabaseMetaData metaData = sourceConnection.getMetaData();
            ResultSet columns = metaData.getColumns(null, schemaName, tableName, null);

            // 构建创建表的SQL语句
            StringBuilder createTableSql = new StringBuilder();
            createTableSql.append("CREATE TABLE ").append(schemaName + "." + newTableName).append(" (");

            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String dataType = columns.getString("TYPE_NAME");
                int columnSize = columns.getInt("COLUMN_SIZE");
                int nullable = columns.getInt("NULLABLE");
                if(dataType.contains("int") || dataType.contains("st_geometry") || dataType.contains("timestamp") || dataType.contains("date")){
                    createTableSql.append(columnName).append(" ").append(dataType);
                }else {
                    createTableSql.append(columnName).append(" ").append(dataType).append("(").append(columnSize).append(")");
                }


                if (nullable == DatabaseMetaData.columnNoNulls) {
                    createTableSql.append(" NOT NULL");
                }

                createTableSql.append(",");
            }

            createTableSql.deleteCharAt(createTableSql.length() - 1);  // 移除最后一个逗号
            createTableSql.append(")");
            // 执行创建表的SQL语句
            Statement createTableStmt = targetConnection.createStatement();
            createTableStmt.executeUpdate(createTableSql.toString());

            // 复制表数据
            String copyDataSql = "INSERT INTO " + schemaName + "." + newTableName + " SELECT * FROM " + schemaName + "." + tableName;
            Statement copyDataStmt = targetConnection.createStatement();
            copyDataStmt.executeUpdate(copyDataSql);
            // 关闭连接和资源
            copyDataStmt.close();
            createTableStmt.close();
            columns.close();
            sourceConnection.close();
            targetConnection.close();
            LOG.info("----success---");
            return db + "." + schemaName + "." + newTableName;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
　　 //恢复数据
    public boolean restore(String targetData, String data){
        if(StringUtils.isBlank(data) || StringUtils.isBlank(targetData)){
            LOG.info("--------data or targetData is null---");
            return false;
        }
        String[] dataSS = data.split("\\.");
        if(dataSS == null || dataSS.length < 3){
            LOG.info("--------dataSS size < 3---");
            return false;
        }
        String[] targetDataSS = targetData.split("\\.");
        if(targetDataSS == null || targetDataSS.length < 3){
            LOG.info("--------targetDataSS size < 3---");
            return false;
        }
        try {
            // 连接到目标数据库
            Connection targetConnection = DriverManager.getConnection(dbProperties.getUrl(), dbProperties.getUsername(), dbProperties.getPassword());
            String targetSchemaName = targetDataSS[1];
            String targetTableName = targetDataSS[2];

            String schemaName = dataSS[1];
            String tableName = dataSS[2];

            //删除数据
            String deleteSql = "delete from " + targetSchemaName + "." + targetTableName;
            Statement deleteDataStmt = targetConnection.createStatement();
            deleteDataStmt.executeUpdate(deleteSql);
            // 关闭连接和资源
            deleteDataStmt.close();

            // 复制表数据
            String copyDataSql = "INSERT INTO " + targetSchemaName + "." + targetTableName + " SELECT * FROM " + schemaName + "." + tableName;
            Statement copyDataStmt = targetConnection.createStatement();
            copyDataStmt.executeUpdate(copyDataSql);
            // 关闭连接和资源
            copyDataStmt.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //执行 delete，删除表等操作
    public boolean commitSql(String sql){
        if(StringUtils.isBlank(sql)){
            return true;
        }
        try {
            // 连接到目标数据库
            Connection connection = DriverManager.getConnection(dbProperties.getUrl(), dbProperties.getUsername(), dbProperties.getPassword());
            // 执行删除表的SQL语句
            Statement statement = connection.createStatement();
            statement.executeUpdate(sql);
            // 关闭连接和资源
            statement.close();
            connection.close();
            return true;
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
}