Java tutorial
/** * The contents of this file are subject to the license and copyright * detailed in the LICENSE and NOTICE files at the root of the source * tree and available online at * * http://www.dspace.org/license/ */ package org.dspace.storage.rdbms.migration; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.lang.StringUtils; /** * This Utility class offers utility methods which may be of use to perform * common Java database migration task(s) (via Flyway). * <P> * NOTE: This class specifically CANNOT utilize Hibernate, because it is * used for database migrations which take place PRIOR to Hibernate loading. * However, as you'll see below, all methods are protected to ensure the rest * of the API cannot bypass Hibernate. * * @author Tim Donohue */ public class MigrationUtils { /** * Drop a given Database Constraint (based on the current database type). * Returns a "checksum" for this migration which can be used as part of * a Flyway Java migration * * @param connection the current Database connection * @param tableName the name of the table the constraint applies to * @param columnName the name of the column the constraint applies to * @param constraintSuffix Only used for PostgreSQL, whose constraint naming convention depends on a suffix (key, fkey, etc) * @return migration checksum as an Integer * @throws SQLException if a database error occurs */ protected static Integer dropDBConstraint(Connection connection, String tableName, String columnName, String constraintSuffix) throws SQLException { Integer checksum = -1; // First, in order to drop the appropriate Database constraint, we // must determine the unique name of the constraint. As constraint // naming is DB specific, this is dependent on our DB Type String dbtype = connection.getMetaData().getDatabaseProductName(); String constraintName = null; String constraintNameSQL = null; boolean cascade = false; switch (dbtype.toLowerCase()) { case "postgres": case "postgresql": // In Postgres, constraints are always named: // {tablename}_{columnname(s)}_{suffix} // see: http://stackoverflow.com/a/4108266/3750035 constraintName = StringUtils.lowerCase(tableName); if (!StringUtils.equals(constraintSuffix, "pkey")) { constraintName += "_" + StringUtils.lowerCase(columnName); } constraintName += "_" + StringUtils.lowerCase(constraintSuffix); cascade = true; break; case "oracle": // In Oracle, constraints are listed in the USER_CONS_COLUMNS table constraintNameSQL = "SELECT CONSTRAINT_NAME " + "FROM USER_CONS_COLUMNS " + "WHERE TABLE_NAME = ? AND COLUMN_NAME = ?"; cascade = true; break; case "h2": // In H2, constraints are listed in the "information_schema.constraints" table constraintNameSQL = "SELECT DISTINCT CONSTRAINT_NAME " + "FROM information_schema.constraints " + "WHERE table_name = ? AND column_list = ?"; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported in this migration."); } // If we have a SQL query to run for the constraint name, then run it if (constraintNameSQL != null) { // Run the query to obtain the constraint name, passing it the parameters PreparedStatement statement = connection.prepareStatement(constraintNameSQL); statement.setString(1, StringUtils.upperCase(tableName)); statement.setString(2, StringUtils.upperCase(columnName)); try { ResultSet results = statement.executeQuery(); if (results.next()) { constraintName = results.getString("CONSTRAINT_NAME"); } results.close(); } finally { statement.close(); } } // As long as we have a constraint name, drop it if (constraintName != null && !constraintName.isEmpty()) { // This drop constaint SQL should be the same in all databases String dropConstraintSQL = "ALTER TABLE " + tableName + " DROP CONSTRAINT " + constraintName; if (cascade) { dropConstraintSQL += " CASCADE"; } try (PreparedStatement statement = connection.prepareStatement(dropConstraintSQL)) { statement.execute(); } // Return the size of the query we just ran // This will be our "checksum" for this Flyway migration (see getChecksum()) checksum = dropConstraintSQL.length(); } return checksum; } /** * Drop a given Database Table (based on the current database type). * Returns a "checksum" for this migration which can be used as part of * a Flyway Java migration * <P> * NOTE: Ideally, if you need to do a DROP TABLE, you should just create * a Flyway SQL migration. This method should ONLY be used if the table name * needs to be dynamically determined via Java. * * @param connection the current Database connection * @param tableName the name of the table to drop * @return migration checksum as an Integer * @throws SQLException if a database error occurs */ protected static Integer dropDBTable(Connection connection, String tableName) throws SQLException { String dropTableSQL = null; Integer checksum = -1; // First, in order to drop the appropriate Database table, we must // determine the query based on DB type String dbtype = connection.getMetaData().getDatabaseProductName(); switch (dbtype.toLowerCase()) { case "postgres": case "postgresql": dropTableSQL = "DROP TABLE IF EXISTS " + tableName + " CASCADE"; break; case "oracle": dropTableSQL = "DROP TABLE " + tableName + " CASCADE CONSTRAINTS"; break; case "h2": dropTableSQL = "DROP TABLE IF EXISTS " + tableName + " CASCADE"; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported in this migration."); } // If we have a SQL query to run, then run it if (dropTableSQL != null) { try (PreparedStatement statement = connection.prepareStatement(dropTableSQL)) { statement.execute(); } // Return the size of the query we just ran // This will be our "checksum" for this Flyway migration (see getChecksum()) checksum = dropTableSQL.length(); } return checksum; } /** * Drop a given Database Sequence (based on the current database type). * Returns a "checksum" for this migration which can be used as part of * a Flyway Java migration * <P> * NOTE: Ideally, if you need to do a DROP SEQUENCE, you should just create * a Flyway SQL migration. This method should ONLY be used if the sequence name * needs to be dynamically determined via Java. * * @param connection the current Database connection * @param sequenceName the name of the sequence to drop * @return migration checksum as an Integer * @throws SQLException if a database error occurs */ protected static Integer dropDBSequence(Connection connection, String sequenceName) throws SQLException { String dropSequenceSQL = null; Integer checksum = -1; String dbtype = connection.getMetaData().getDatabaseProductName(); switch (dbtype.toLowerCase()) { case "postgres": case "postgresql": dropSequenceSQL = "DROP SEQUENCE IF EXISTS " + sequenceName; break; case "oracle": dropSequenceSQL = "DROP SEQUENCE " + sequenceName; break; case "h2": dropSequenceSQL = "DROP SEQUENCE IF EXISTS " + sequenceName; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported in this migration."); } // If we have a SQL query to run, then run it if (dropSequenceSQL != null) { try (PreparedStatement statement = connection.prepareStatement(dropSequenceSQL)) { statement.execute(); } // Return the size of the query we just ran // This will be our "checksum" for this Flyway migration (see getChecksum()) checksum = dropSequenceSQL.length(); } return checksum; } /** * Drop a given Database View (based on the current database type). * Returns a "checksum" for this migration which can be used as part of * a Flyway Java migration * <P> * NOTE: Ideally, if you need to do a DROP VIEW, you should just create * a Flyway SQL migration. This method should ONLY be used if the view name * needs to be dynamically determined via Java. * * @param connection the current Database connection * @param viewName the name of the view to drop * @return migration checksum as an Integer * @throws SQLException if a database error occurs */ protected static Integer dropDBView(Connection connection, String viewName) throws SQLException { String dropViewSQL = null; Integer checksum = -1; String dbtype = connection.getMetaData().getDatabaseProductName(); switch (dbtype.toLowerCase()) { case "postgres": case "postgresql": dropViewSQL = "DROP VIEW IF EXISTS " + viewName + " CASCADE"; break; case "oracle": dropViewSQL = "DROP VIEW " + viewName + " CASCADE CONSTRAINTS"; break; case "h2": dropViewSQL = "DROP VIEW IF EXISTS " + viewName + " CASCADE"; break; default: throw new SQLException("DBMS " + dbtype + " is unsupported in this migration."); } // If we have a SQL query to run, then run it if (dropViewSQL != null) { try (PreparedStatement statement = connection.prepareStatement(dropViewSQL)) { statement.execute(); } // Return the size of the query we just ran // This will be our "checksum" for this Flyway migration (see getChecksum()) checksum = dropViewSQL.length(); } return checksum; } }