java备份数据库，指定字段（存在修改，不存在新增）

原创已于 2023-09-05 14:15:30 修改 · 284 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#java #数据库 #开发语言

于 2023-09-05 14:13:17 首次发布

本文详细展示了使用Java的JDBC进行数据库源到目标的迁移过程，包括连接、查询、创建表、插入数据以及处理可能的重复键冲突。

public class Test {
	public static void main(String[] args) {
        String sourceDbUrl = "jdbc:mysql://source_database_url";
        String sourceUsername = "source_username";
        String sourcePassword = "source_password";

        String targetDbUrl = "jdbc:mysql://target_database_url";
        String targetUsername = "target_username";
        String targetPassword = "target_password";

        try {
            Connection sourceConnection = DriverManager.getConnection(sourceDbUrl, sourceUsername, sourcePassword);
            Connection targetConnection = DriverManager.getConnection(targetDbUrl, targetUsername, targetPassword);
       
            String query = "SELECT column1, column2, column3 FROM table";
            PreparedStatement preparedStatement = sourceConnection.prepareStatement(query);
            ResultSet resultSet = preparedStatement.executeQuery();

            String createTableQuery = "CREATE TABLE destination_table (column1 datatype, column2 datatype, column3 datatype)";
            Statement createTableStatement = targetConnection.createStatement();
            createTableStatement.execute(createTableQuery);

            String insertQuery = "INSERT INTO destination_table (column1, column2, column3,...) VALUES (?, ?, ?...) " +
                    " ON DUPLICATE KEY UPDATE column1 = values(column1),column2 = values(column2) ...";
            PreparedStatement insertStatement = targetConnection.prepareStatement(insertQuery);

            while (resultSet.next()) {
                insertStatement.setString(1, resultSet.getString("column1"));
                insertStatement.setString(2, resultSet.getString("column2"));
                insertStatement.setString(3, resultSet.getString("column3"));

                insertStatement.executeUpdate();
            }

            System.out.println("Backup completed successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    }