com.raulexposito.alarife.sqlexecutor.SQLExecutor.java Source code

Java tutorial

Introduction

Here is the source code for com.raulexposito.alarife.sqlexecutor.SQLExecutor.java

Source

/**
 *           DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
 *                   Version 2, December 2004
 *
 * Copyright (C) 2004 Sam Hocevar
 * 14 rue de Plaisance, 75014 Paris, France
 * Everyone is permitted to copy and distribute verbatim or modified
 * copies of this license document, and changing it is allowed as long
 * as the name is changed.
 *
 *            DO WHAT THE FUCK YOU WANT TO PUBLIC LICENSE
 *   TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION
 *
 *  0. You just DO WHAT THE FUCK YOU WANT TO.
 */
package com.raulexposito.alarife.sqlexecutor;

import com.raulexposito.alarife.creatorfromscratch.properties.DatabaseCreatorFromScratchPropertiesReader;
import com.raulexposito.alarife.enumeration.ApplicationMode;

import com.raulexposito.alarife.exception.DatabaseException;
import com.raulexposito.alarife.enumeration.DatabaseType;
import com.raulexposito.alarife.upgrader.DatabaseUpgrader;
import com.raulexposito.alarife.upgrader.pojo.Version;
import com.raulexposito.alarife.upgrader.properties.DatabaseUpgraderPropertiesReader;
import com.raulexposito.alarife.util.DatabasePropertiesReaderUtil;
import com.raulexposito.alarife.util.ScriptsDirectoryUtil;
import com.raulexposito.alarife.util.VersionGeneratorUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Executes the commands to the database to upgrade, request information, regenerate ...
 */
public final class SQLExecutor {

    private static final Log log = LogFactory.getLog(SQLExecutor.class);
    private static final String CANNOT_UPGRADE_TO_VERSION = "Cannot upgrade to version ";
    private static final String SQL_COMMENT = "--";
    private static final String SQL_END_OF_COMMAND = ";";
    private static final int NOT_EXISTS = -1;
    // properties file reader
    private DatabasePropertiesReaderUtil dpru = null;
    // properties file reader
    private DatabaseCreatorFromScratchPropertiesReader dcfspr = null;
    // properties file reader
    private DatabaseUpgraderPropertiesReader dupr = null;

    /**
     * Recovers the version of the database that is being used by the application<br>
     * @param databaseType type of the database in use
     * @param propertiesFile name of the properties file where you can read the database configuration
     * @throws com.raulexposito.alarife.exception.DatabaseException if something goes wrong
     * @return the version of the database that is being used by the application
     */
    public Version recoverCurrentVersion(final DatabaseType databaseType, final ApplicationMode applicationMode,
            final String propertiesFile) throws DatabaseException {
        return performSQLAction(SQLExecutorCommandType.RECOVER_CURRENT_VERSION, databaseType, applicationMode,
                propertiesFile, null, null);
    }

    /**
     * Recovers the version of the database that is being used by the application<br>
     * @param databaseType type of the database in use
     * @param propertiesFile name of the properties file where you can read the database configuration
     * @throws es.futbol.database.exception.DatabaseException if something goes wrong
     * @return the version of the database that is being used by the application
     */
    public Version updateToVersion(final List<Version> versionList, final ApplicationMode applicationMode,
            final String propertiesFile, final DatabaseType databaseType) throws DatabaseException {

        return performSQLAction(SQLExecutorCommandType.UPDATE_TO_VERSION, databaseType, applicationMode,
                propertiesFile, versionList, null);
    }

    /**
     * Drops the old database and regenerates it to make a new and fresh
     * database
     * @param databaseType enumeration with the database type
     * @throws com.raulexposito.alarife.exception.DatabaseException if something goes wrong
     */
    public void createFromScratch(final DatabaseType databaseType, final ApplicationMode applicationMode,
            final String propertiesFile, final String version) throws DatabaseException {

        performSQLAction(SQLExecutorCommandType.CREATE_FROM_SCRATCH, databaseType, applicationMode, propertiesFile,
                null, version);
    }

    /**
     * Connects with the database and performs some operations with it
     * @param commandType enumeration with the operation to perform
     * @param databaseType enumeration with the database type
     * @param propertiesFile propertiesFile name of the properties file where you can read the database configuration
     * @param nextVersion version you are migrating to in upgrades
     * @param initialVersionNumber version to start the database in regenerations
     * @return initially a Version object
     * @throws com.raulexposito.alarife.exception.DatabaseException if something goes worng
     */
    private Version performSQLAction(final SQLExecutorCommandType commandType, final DatabaseType databaseType,
            final ApplicationMode applicationMode, final String propertiesFile, final List<Version> versionList,
            final String initialVersionNumber) throws DatabaseException {
        Connection con = null;
        Statement st = null;
        boolean rollbackConnection = false;

        try {
            // read the database properties file if it's needed
            if (dpru == null) {
                dpru = new DatabasePropertiesReaderUtil(propertiesFile);

                final String driverClassName = dpru.getDriverClassName();

                // load the driver
                Class.forName(driverClassName);
                log.info("database driver loaded: '" + driverClassName + "'");
            }

            if (dcfspr == null) {
                dcfspr = new DatabaseCreatorFromScratchPropertiesReader(propertiesFile);
            }

            // connection to the database using an instance, an username and password
            con = DriverManager.getConnection(dpru.getInstance(), dpru.getUsername(), dpru.getPassword());
            con.setAutoCommit(false);

            // creation of a statements to execute commands
            st = con.createStatement();

            // actions to be performed
            if (commandType.equals(SQLExecutorCommandType.CREATE_FROM_SCRATCH)) {
                regenerateDatabaseFromScratch(propertiesFile, databaseType, st, initialVersionNumber);
            } else if (commandType.equals(SQLExecutorCommandType.RECOVER_CURRENT_VERSION)) {
                return recoverCurrentVersion(propertiesFile, databaseType, st);
            } else if (commandType.equals(SQLExecutorCommandType.UPDATE_TO_VERSION)) {
                Version currentVersion = this.recoverCurrentVersion(databaseType, applicationMode, propertiesFile);
                final Version latestVersion = DatabaseUpgrader.recoverLatestVersion(versionList);

                while (DatabaseUpgrader.upgradeIsNeeded(currentVersion, latestVersion)) {
                    log.info("the current version '" + currentVersion + "' is older than the latest version '"
                            + latestVersion + "', so an upgrade is needed");

                    final Version nextVersion = DatabaseUpgrader.getNextVersion(currentVersion, versionList);
                    updateToVersion(databaseType, applicationMode, st, nextVersion);
                    currentVersion = recoverCurrentVersion(databaseType, applicationMode, propertiesFile);
                }
            }

            // commit the changes in database
            con.commit();
        } catch (IOException ex) {
            log.error("There is a problem reading the configuration file");
            rollbackConnection = true;
            throw new DatabaseException(ex.getMessage(), ex);
        } catch (SQLException ex) {
            log.error("There is a problem with a SQL command");
            rollbackConnection = true;
            throw new DatabaseException(ex.getMessage(), ex);
        } catch (DatabaseException ex) {
            log.error("There is a problem updating the database");
            rollbackConnection = true;
            throw new DatabaseException(ex.getMessage(), ex);
        } catch (ClassNotFoundException ex) {
            log.error("The database driver cannot be loaded");
            rollbackConnection = true;
            throw new DatabaseException(ex.getMessage(), ex);
        } finally {
            if (rollbackConnection && (con != null)) {
                try {
                    con.rollback();
                } catch (Exception e) {
                    log.warn("cannot rollback the connection: " + e);
                }
            }

            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                    log.warn("cannot close the connection: " + e);
                }
            }

            if (st != null) {
                try {
                    st.close();
                } catch (Exception e) {
                    log.warn("cannot close the statement: " + e);
                }
            }
        }

        return null;
    }

    /**
     * Drops the database and regenerates it using the SQL commands from the configuration file
     * @param propertiesFile configuration file
     * @param databaseType type of the database
     * @param st statement to launch the SQL commands
     * @param initialVersionNumber number to be inserted into the database as initial version
     * @throws java.io.IOException if the configuration file cannot be readed
     * @throws java.sql.SQLException if there is an error with the SQL commands
     */
    private void regenerateDatabaseFromScratch(final String propertiesFile, final DatabaseType databaseType,
            final Statement st, final String initialVersionNumber) throws IOException, SQLException {
        if (dcfspr == null) {
            dcfspr = new DatabaseCreatorFromScratchPropertiesReader(propertiesFile);
        }

        // drop the database
        st.executeUpdate(dcfspr.getDropDatabase(databaseType));
        log.debug("schema '" + dcfspr.getInstance() + "' dropped");

        // create a new database
        st.executeUpdate(dcfspr.getCreateDatabase(databaseType));
        log.debug("schema '" + dcfspr.getInstance() + "' created");

        // changes to the new database
        st.executeUpdate(dcfspr.getChangeDatabase(databaseType));
        log.debug("changed to schema '" + dcfspr.getInstance() + "'");

        // create a table 'VERSION' in the database with the version of the software
        st.executeUpdate(dcfspr.getCreateVersionTable(databaseType));
        log.debug("table 'VERSION' created");

        // save the version by default in the new table 'Version'
        st.executeUpdate(dcfspr.getInsertVersionTable(databaseType, initialVersionNumber));
        log.debug("inserted the version '" + initialVersionNumber + "' in the table 'VERSION'");
    }

    /**
     * Recovers the current database version
     * @param propertiesFile configuration file
     * @param databaseType type of the database
     * @param st statement to launch the SQL commands
     * @param result result of the query
     * @param con connection to the database
     * @return an instance of the class 'Version' with the version saved in database
     * @throws java.io.IOException if the configuration file cannot be readed
     * @throws com.raulexposito.alarife.exception.DatabaseException if convert the version is not possible
     * @throws java.sql.SQLException if there is an error with the SQL commands
     */
    private Version recoverCurrentVersion(final String propertiesFile, final DatabaseType databaseType,
            final Statement st) throws IOException, DatabaseException, SQLException {
        if (dupr == null) {
            dupr = new DatabaseUpgraderPropertiesReader(propertiesFile);
        }

        String recoveredVersion = "";

        // launchs the query
        final ResultSet executeQueryResultset = st.executeQuery(dupr.getDatabaseVersionTable(databaseType));

        if (executeQueryResultset != null) {

            // there must be only one result
            while (executeQueryResultset.next()) {
                recoveredVersion = executeQueryResultset.getString(1);
            }

            log.debug("current version: '" + recoveredVersion + "'");

            try {
                executeQueryResultset.close();
            } catch (Exception e) {
                log.warn("cannot rollback the result set: " + e);
            }
        }

        return VersionGeneratorUtil.generateVersion(recoveredVersion);
    }

    /**
     * Updates the database from one version to other by using the scripts
     * @param databaseType database type
     * @param st sql statement to launch the creates and updates
     * @param nextVersion version of the scripts to be launched to upgrade to the next version
     * @throws com.raulexposito.alarife.exception.DatabaseException if something goes wrong
     */
    private void updateToVersion(final DatabaseType databaseType, final ApplicationMode applicationMode,
            final Statement st, final Version nextVersion) throws DatabaseException {
        log.info("migrating to '" + nextVersion + "' version");

        final long startTime = System.currentTimeMillis();
        final ScriptsDirectoryUtil scriptsDirectoryUtil = new ScriptsDirectoryUtil();

        try {

            st.executeUpdate(dcfspr.getChangeDatabase(databaseType));
            log.debug("changed to schema '" + dcfspr.getInstance() + "'");

            // read the content of the file with the SQL commands to update tables
            final InputStream upgradeTables = scriptsDirectoryUtil.getUpgradeTablesScript(databaseType,
                    applicationMode, nextVersion);
            log.info("reading the content of the upgrade tables script");

            final List<String> upgradeTablesCommands = getSQLCommandsFromScriptFile(upgradeTables);

            for (String command : upgradeTablesCommands) {
                st.addBatch(command);
            }

            // read the content of the file with the SQL commands to insert data
            final InputStream insertData = scriptsDirectoryUtil.getInsertDataScript(databaseType, applicationMode,
                    nextVersion);
            log.info("reading the content of the insert data script");

            final List<String> insertDataCommands = getSQLCommandsFromScriptFile(insertData);

            for (String command : insertDataCommands) {
                st.addBatch(command);
            }

            // execution of the different commands
            st.executeBatch();
            log.info("scripts succesfully executed [" + (System.currentTimeMillis() - startTime) + " ms]");
        } catch (Exception e) {
            log.error(CANNOT_UPGRADE_TO_VERSION + "'" + nextVersion + "': " + e);
            throw new DatabaseException(CANNOT_UPGRADE_TO_VERSION + "'" + nextVersion + "'", e);
        }
    }

    /**
     * Reads the content of a script file and, for each command on it, generates a single command
     * @param file the script file with the commands
     * @return a list with the commands of the script
     * @throws java.io.IOException if the file cannot be readed
     */
    protected static List<String> getSQLCommandsFromScriptFile(final InputStream file) throws IOException {
        // list of commands to be returned
        final List<String> SQLCommands = new ArrayList<String>();

        // reader for the script file
        final LineNumberReader scriptFile = new LineNumberReader(
                new InputStreamReader(new BufferedInputStream(file), "ISO-8859-1"));

        // string builder where append the lines of the file
        final StringBuilder content = new StringBuilder();

        // next line to be readed from the script file
        String line;

        while ((line = scriptFile.readLine()) != null) {
            log.trace(line);
            line = line.trim();

            // deletes the comments
            if (line.indexOf(SQL_COMMENT) != NOT_EXISTS) {
                line = line.substring(0, line.indexOf(SQL_COMMENT));
            }

            content.append(line);

            // is the end of the command?
            if (line.endsWith(SQL_END_OF_COMMAND)) {
                SQLCommands.add(content.toString());
                content.setLength(0);
            }
        }

        return SQLCommands;
    }
}