Simple DB Migration with JDBC
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);
Querying The Database
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();
}
}
}
Granted, it was a little more complicated than that, but honestly not by much. At anypoint if an exception is raised, we catch the exception and print the stack trace to see where may have goofed, otherwise we clean up whatever resources/connections were aquired and the migration is done. I'm still doing alot of work getting the new design up and running, but wanted to post something new. That's all i've got for now, so until next time Happy Hacking!
-
Simple DB Migration with JDBC
-
Welcome to CodeBlahger, A Blahging Platform for Programmers.
-
Design Patterns: The Façade Pattern
-
The Interpreter Pattern: Implementing Interpreters the OOP way
-
Parsing Right-Associative Operators with Recursive Descent
-
BST Iterators Revisited: No Parent Pointer, No Stack, No Problem
-
Deleting Arbitrary Values from Binary Search Trees
-
Solving the N Queens Problem with Breadth First Search
-
Performing the Knights Tour in Linear Time
-
Knuth's Algorithm X For the Exact Cover Problem
Leave A Comment