LiferayShardingMigrationTool.java

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.Statement;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class LiferayShardingMigrationTool {

        public static void main(String[] args) throws Exception {
                HikariConfig config = new HikariConfig();
                config.setJdbcUrl("jdbc:mysql://localhost:3306/lportal");
                config.setUsername("root");
                config.setPassword("root");
                config.addDataSourceProperty("cachePrepStmts", "true");
                config.addDataSourceProperty("prepStmtCacheSize", "250");
                config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

                HikariDataSource ds = new HikariDataSource(config);
                Connection connection = ds.getConnection();
                DatabaseMetaData databaseMetaData = connection.getMetaData();
                String[] shards = new String[]{"shard1", "shard2"};

                for (String shard : shards) {
                        ResultSet rsTables = databaseMetaData.getTables(null, null, null,
                                        new String[]{"Table"});

                        while (rsTables.next()) {
                                StringBuilder builder1 = new StringBuilder();
                                StringBuilder builder2 = new StringBuilder();
                                String tableName = rsTables.getString("TABLE_NAME");

                                if (tableName.startsWith("QUARTZ")) {
                                        continue;
                                }

                                ResultSet rsColumns = databaseMetaData.getColumns(null, null,
                                                tableName, null);

                                builder1.append(" (");

                                while (rsColumns.next()) {
                                        String columnName = rsColumns.getString("COLUMN_NAME");

                                        builder1.append(columnName);
                                        builder2.append(columnName);

                                        if (!rsColumns.isLast()) {
                                                builder1.append(",");
                                                builder2.append(",");
                                        }

                                }

                                builder1.append(") ");

                                String sql = "insert ignore into lportal." + tableName
                                                + builder1.toString() + " select "
                                                + builder2.toString() + " from " + shard + "."
                                                + tableName;

                                Statement statement = connection.createStatement();
                                int rows = statement.executeUpdate(sql);

                                System.out.println("Copied " + rows + " rows from table "
                                                + tableName);
                        }

                        Statement statement = connection.createStatement();
                        statement.executeUpdate("drop database " + shard);

                        System.out.println("Dropped database " + shard);
                        System.out.println();
                }

                ds.close();
        }
}