Skip to content

Instantly share code, notes, and snippets.

@hiroto-yamashita
Last active August 29, 2015 14:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hiroto-yamashita/eb95f08f7ce5a2a2fd5e to your computer and use it in GitHub Desktop.
Save hiroto-yamashita/eb95f08f7ce5a2a2fd5e to your computer and use it in GitHub Desktop.
PostgreSQL data migration
public class Migrate {
private static String oldUrl;
private static Properties oldProps = new Properties();
private static String newUrl;
private static Properties newProps = new Properties();
public static void main(String[] args) {
oldUrl = "jdbc:postgresql://hostname/dbname";
oldProps.setProperty("user", "xxxx");
newUrl = "jdbc:postgresql://hostname/dbname";
newProps.setProperty("user", "xxxx");
migrate("table1");
migrate("table2");
migrate("table3");
migrate("table4");
migrate("table5");
}
private static void migrate(String tableName) {
try (Connection oldConn = DriverManager.getConnection(oldUrl, oldProps);
Connection newConn = DriverManager.getConnection(newUrl,
newProps)) {
QueryRunner oldRunner = new QueryRunner(true);
ResultSetHandler<List<Object[]>> rsh = new ArrayListHandler();
List<Object[]> result = oldRunner.query(oldConn, "select * from "
+ tableName, rsh);
String insertSQL = "insert into " + tableName + " values(";
for (int i = 0; i < result.get(0).length - 1; i++) {
insertSQL += "?,";
}
insertSQL += "?)";
QueryRunner newRunner = new QueryRunner();
newRunner
.batch(newConn, insertSQL, result.toArray(new Object[0][]));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment