Class SQLScriptHelper

java.lang.Object
org.ametys.core.script.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

  • Field Details

  • Method Details

    • 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
    • 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.