How to migrate databases to a new schema automatically

From Coder's Log

This is a class that I put together to perform migrations of hibernate maintained schema. This is a bit much to explain in the state that it is in. but in a nutshell. For every table that is not new rename the table to temp_{tableName} create the new table schema and copy all common fields from temp_{tableName} to {tableName}. Indexes will be recreated as well.

• propFile - defines a property file for hibernate and other variables

public class DDLConversion {
	public static void main(String[] args) throws Exception {
		// Export schema to sql.ddl
		GenericApplicationContext context = new GenericApplicationContext();
		context.setResourceLoader(new FileSystemResourceLoader());
		XmlBeanDefinitionReader xmlReader = new XmlBeanDefinitionReader(context);

		xmlReader.loadBeanDefinitions(new FileSystemResource("WEB-INF/applicationContext.xml"));
		context.refresh();
		DataSource ds = (DataSource) context.getBean("dataSource");
		// AnnotationSessionFactoryBean sf=(AnnotationSessionFactoryBean)
		// context.getBean("sessionFactory");
		Properties props=(Properties)context.getBean("propFile");
		String dialect = props.getProperty("hibernate.dialect");
		HibernateSchemaExporter.export(context,true, dialect);
		Connection connection = ds.getConnection();
		connection.setAutoCommit(true);
		if (args.length > 0 && args[0].toUpperCase().equals("REVERT")) {
			revertStructure(connection);
			System.out.println("REVERTED");
			return;
		}
		List<String> tables = getTableNames(connection);
		for (String tableName : tables) {
			dropConstraints(connection, tableName);
			executeSql(connection, "ALTER TABLE " + tableName + " RENAME TO TEMP_" + tableName);
		}
		try {
			BufferedReader br = new BufferedReader(new FileReader("sql.ddl"));
			String sqlLine = br.readLine();
			while (sqlLine != null) {
				executeSql(connection, sqlLine);
				sqlLine = br.readLine();
			}
			try {
				executeSql(connection, "set referential_integrity FALSE");
			} catch (SQLException se) {
			}// this will file for some dialects
			for (String tableName : tables) {
				System.out.println("COPYING DATA :" + tableName);
				List<String> oldCols = getColumnNames(connection, "TEMP_" + tableName);
				List<String> newCols = getColumnNames(connection, tableName);
				String cols = "";
				for (String col : oldCols)
					if (newCols.contains(col))
						cols += "," + col;
				if (cols.length() > 0) {
					cols = cols.substring(1);
					executeSql(connection, "INSERT INTO " + tableName + "(" + cols + ") SELECT " + cols + " FROM TEMP_" + tableName);
				} else {
					System.out.println("***** WARNING NO COMMON COLUMNS IN TABLE :" + tableName);
				}
			}
			try {
				executeSql(connection, "set referential_integrity TRUE");
			} catch (SQLException se) {
			}// this will file for some dialects
			for (String tableName : tables) {
				executeSql(connection, "DROP TABLE TEMP_" + tableName);
			}
			System.out.println("DONE");
		} catch (Exception e) {
			e.printStackTrace();
			revertStructure(connection);
			System.out.println("FAILED-REVERTED");
		}
		connection.close();
	}
	public static void revertStructure(Connection connection) throws SQLException {
		List<String> tables = getTableNames(connection);
		for (String tableName : tables) {
			dropConstraints(connection, tableName);
		}
		for (String tableName : tables) {
			if (tableName.toUpperCase().startsWith("TEMP_")) {
				String newTable = tableName.substring(5);
				if (tables.contains(newTable))
					executeSql(connection, "DROP TABLE " + newTable);
				executeSql(connection, "ALTER TABLE " + tableName + " RENAME TO " + newTable);
			}
		}
	}
	public static void executeSql(Connection connection, String sql) throws SQLException {
		System.out.println(sql);
		connection.prepareStatement(sql).execute();
	}
	public static void dropConstraints(Connection connection, String tableName) throws SQLException {
		ResultSet rsConstraints = connection.getMetaData().getImportedKeys(null, null, tableName);
		while (rsConstraints.next()) {
			String fkName = rsConstraints.getString("FK_NAME");
			String sql = "ALTER TABLE " + tableName + " DROP CONSTRAINT " + fkName;
			executeSql(connection, sql);
		}
	}
	public static List<String> getTableNames(Connection connection) throws SQLException {
		ResultSet rsTables = connection.getMetaData().getTables(null, null, null, new String[] { "TABLE" });
		ArrayList<String> tables = new ArrayList();
		while (rsTables.next()) {
			String tableName = rsTables.getString("TABLE_NAME");
			tables.add(tableName);
		}
		return tables;
	}
	public static List<String> getColumnNames(Connection connection, String tableName) throws SQLException {
		ResultSet rsColumns = connection.getMetaData().getColumns(null, null, tableName, null);
		ArrayList<String> columns = new ArrayList();
		while (rsColumns.next()) {
			String columnName = rsColumns.getString("COLUMN_NAME");
			columns.add(columnName);
		}
		return columns;
	}
	public static void dumpRow(ResultSet rs) throws SQLException {
		System.out.println("=====");
		for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
			System.out.println(rs.getMetaData().getColumnName(i) + " => " + rs.getString(i));
		}
		System.out.println("=====");
	}
}