Simple DB Migration with JDBC

mgcadmin 26-09-2024

During my recent move away from wordpress to get CodeBlahger up and running I was faced with a number of technical challenges, not the least of which being how in the world I was going to take several years of posts stored in a MariaDB instance using the wordpress schemea, and move them over to the MySQL instance running in a docker container with the CodeBlahger schemea. In preparation for the migration I had decide what language I would use to automate the process - I wasnt about to do this by hand - and how integrated into the CodeBlahger platform i wanted to make the migration process. After weighing the pros and cons of using python or perl I scrapped both ideas and ultimately decided to stick Java for writing the code to handle the migration. As for integrating that code into CodeBlahger, I decided against it and implemented a standalone app. Migrating the data would (hopefully) only occur once, it didnt make sense to me to clutter up the code base with support for such a limited use feature. 

In todays post I am going to outline the steps taken to move content from one database instance to another using JDBC in Java 17, so lets get to it.

Configuring the Database Connections

import java.sql.*;
import java.time.*;

public class DBMigration {
    private static final String OLD_DB_URL = "jdbc:mariadb://localhost:3306/wordpress";
    private static final String OLD_DB_USER = "oldusername";
    private static final String OLD_DB_PASSWORD = "oldpassword";

    private static final String NEW_DB_URL = "jdbc:mysql://localhost:3307/blahgdb";
    private static final String NEW_DB_USER = "newusername";
    private static final String NEW_DB_PASSWORD = "newpassword";

public static void doMigration() {
        Connection oldDbConnection = null;
        Connection newDbConnection = null;
        ResultSet resultSet = null;
        try {
            oldDbConnection = DriverManager.getConnection(OLD_DB_URL, OLD_DB_USER, OLD_DB_PASSWORD);
            newDbConnection = DriverManager.getConnection(NEW_DB_URL, NEW_DB_USER, NEW_DB_PASSWORD);

            String selectSQL = "SELECT id, post_title, post_content, post_date FROM wp_posts WHERE post_status = 'publish'";            
            PreparedStatement selectStatement = oldDbConnection.prepareStatement(selectSQL);
            resultSet = selectStatement.executeQuery();

            String insertSQL = "INSERT INTO posts (title, content, created_at, user_id, category_id) VALUES (?, ?, ?, 1, 1)";
            PreparedStatement insertStatement = newDbConnection.prepareStatement(insertSQL);

     while (resultSet.next()) {
                // Fetch data from the old database
                String title = resultSet.getString("post_title");
                String content = "<p>" + resultSet.getString("post_content") + "</p>";
		        Date created = resultSet.getDate("post_date");
                
                //populate insert statement with fetched data
                insertStatement.setString(1, title);
                insertStatement.setString(2, content);
                insertStatement.setDate(3, created);

                // Execute insert into new database
                insertStatement.executeUpdate();
          }
          System.out.println("Data migration completed successfully.");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Close connections
            try {
                if (resultSet != null) resultSet.close();
                if (oldDbConnection != null) oldDbConnection.close();
                if (newDbConnection != null) newDbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }