Class SQLScriptHelper


  • public final class SQLScriptHelper
    extends Object
    Example of simple use: SQLScriptHelper.createTableIfNotExists(dataSourceId, "QRTZ_JOB_DETAILS", "plugin:core://scripts/%s/quartz.sql", _sourceResolver); Will test if table QRTZ_JOB_DETAILS exits in database from datasource dataSourceId. If not, the script plugin:core://scripts/%s/quartz.sql will be resolved and executed (where %s is replaced by the database type 'mysql', 'derby'...) Tools to run SQL scripts.

    Default separator for isolating statements is the semi colon character: ;.
    It can be changed by using a comment like the following snippet for using the string ---:
    -- _separator_=---
    begin
      execute immediate 'DROP TABLE MYTABLE';
      Exception when others then null;
    end;
    ---
    -- _separator_=;
    CREATE TABLE MYTABLE;
    ...

    Note that the command must be placed at the end of the comment.

    The runner can be configured to ignore SQLExceptions. This can be useful to execute DROP statements when it's unknown if the tables exist:
    --_ignore_exceptions_=on
    DROP TABLE MYTABLE;
    --_ignore_exceptions_=off

    • Method Detail

      • createTableIfNotExists

        public static boolean createTableIfNotExists​(String datasourceId,
                                                     String tableNameToCheck,
                                                     String location,
                                                     SourceResolver sourceResolver)
                                              throws SQLException,
                                                     IOException
        This method will test if a table exists, and if not will execute a script to create it
        Parameters:
        datasourceId - The data source id to open a connection to the database
        tableNameToCheck - The name of the table that will be checked
        location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.
        sourceResolver - The source resolver
        Returns:
        true if the table was created, false otherwise
        Throws:
        SQLException - If an error occurred while executing SQL script, or while testing table existence
        IOException - If an error occurred while getting the script file, or if the url is malformed
      • createTableIfNotExists

        public static boolean createTableIfNotExists​(String datasourceId,
                                                     String tableNameToCheck,
                                                     String location,
                                                     SourceResolver sourceResolver,
                                                     Map<String,​String> replace)
                                              throws SQLException,
                                                     IOException
        This method will test if a table exists, and if not will execute a script to create it
        Parameters:
        datasourceId - The data source id to open a connection to the database
        tableNameToCheck - The name of the table that will be checked
        location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.
        sourceResolver - The source resolver
        replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.
        Returns:
        true if the table was created, false otherwise
        Throws:
        SQLException - If an error occurred while executing SQL script, or while testing table existence
        IOException - If an error occurred while getting the script file, or if the url is malformed
      • createTableIfNotExists

        public static boolean createTableIfNotExists​(Connection connection,
                                                     String tableNameToCheck,
                                                     String location,
                                                     SourceResolver sourceResolver)
                                              throws SQLException,
                                                     IOException
        This method will test if a table exists, and if not will execute a script to create it
        Parameters:
        connection - The database connection to use
        tableNameToCheck - The name of the table that will be checked
        location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.
        sourceResolver - The source resolver
        Returns:
        true if the table was created, false otherwise
        Throws:
        SQLException - If an error occurred while executing SQL script, or while testing table existence
        IOException - If an error occurred while getting the script file, or if the url is malformed
      • createTableIfNotExists

        public static boolean createTableIfNotExists​(Connection connection,
                                                     String tableNameToCheck,
                                                     String location,
                                                     SourceResolver sourceResolver,
                                                     Map<String,​String> replace)
                                              throws SQLException,
                                                     IOException
        This method will test if a table exists, and if not will execute a script to create it
        Parameters:
        connection - The database connection to use
        tableNameToCheck - The name of the table that will be checked
        location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.
        sourceResolver - The source resolver
        replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.
        Returns:
        true if the table was created, false otherwise
        Throws:
        SQLException - If an error occurred while executing SQL script, or while testing table existence
        IOException - If an error occurred while getting the script file, or if the url is malformed
      • createTableIfNotExists

        public static boolean createTableIfNotExists​(String datasourceId,
                                                     String tableNameToCheck,
                                                     String location,
                                                     SourceResolver sourceResolver,
                                                     Map<String,​String> replace,
                                                     String componentId,
                                                     String versionId,
                                                     MigrationExtensionPoint migrationEP,
                                                     SqlVersionStorage sqlVersionStorage)
                                              throws SQLException,
                                                     IOException
        This method will test if a table exists, and if not will execute a script to create it
        Parameters:
        datasourceId - The data source id to open a connection to the database
        tableNameToCheck - The name of the table that will be checked
        location - The source location where to find the script to execute to create the table. This string will be format with String.format with the dbType as argument.
        sourceResolver - The source resolver
        replace - The map of string to replace. Key is the regexp to seek, value is the replacing string.
        componentId - component used to get the most recent version number
        versionId - version id that will be stored in database (can be the same as componentId)
        migrationEP - Migration Extension Point used to read the version
        sqlVersionStorage - SQJ Version Storage used to store the version
        Returns:
        true if the table was created, false otherwise
        Throws:
        SQLException - If an error occurred while executing SQL script, or while testing table existence
        IOException - If an error occurred while getting the script file, or if the url is malformed
      • tableExists

        public static boolean tableExists​(Connection connection,
                                          String tableName)
                                   throws SQLException
        Checks whether the given table exists in the database.
        Parameters:
        connection - The database connection
        tableName - the name of the table
        Returns:
        true is the table exists
        Throws:
        SQLException - In an SQL exception occurs
      • runScript

        public static void runScript​(Connection connection,
                                     String script)
                              throws IOException,
                                     SQLException
        Run a SQL script using the connection passed in.
        Parameters:
        connection - the connection to use for the script
        script - the script data.
        Throws:
        IOException - if an error occurs while reading the script.
        SQLException - if an error occurs while executing the script.
      • runScript

        public static void runScript​(Connection connection,
                                     InputStream is)
                              throws IOException,
                                     SQLException
        Run a SQL script using the connection passed in.
        Parameters:
        connection - the connection to use for the script
        is - the input stream containing the script data.
        Throws:
        IOException - if an error occurs while reading the script.
        SQLException - if an error occurs while executing the script.
      • processScriptLine

        protected static boolean processScriptLine​(String line,
                                                   StringBuilder commandBuffer,
                                                   SQLScriptHelper.ScriptContext scriptContext)
        Process a script line.
        Parameters:
        line - the line to process.
        commandBuffer - the command buffer.
        scriptContext - the script execution context.
        Returns:
        true to immediately process the command (a separator was found), false to process it later.