Package org.dellroad.stuff.schema

Class SQLCommandList

java.lang.Object
org.dellroad.stuff.schema.SQLCommandList
All Implemented Interfaces:
DatabaseAction<Connection>
public class SQLCommandList extends Object implements DatabaseAction<Connection>
Holds and executes a configured SQL script, possibly containing multiple statements.

If the script contains multiple SQL statements, individual statements will be executed individually, in order, by apply(); however, this requires proper configuration of the split pattern.

When using Spring, beans of this type can be created succintly using the <dellroad-stuff:sql> custom XML element. The split pattern may be configured via the split-pattern attribute, and the SQL script is specified either directly via inline text or using the resource attribute. In the latter case, the character encoding can specified via the charset attribute (default is "UTF-8").

For example: 


  <beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:dellroad-stuff="http://dellroad-stuff.googlecode.com/schema/dellroad-stuff"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
      http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
      http://dellroad-stuff.googlecode.com/schema/dellroad-stuff
        http://dellroad-stuff.googlecode.com/svn/wiki/schemas/dellroad-stuff-1.0.xsd">

      <!-- SQL action that clears the audit log -->
      <dellroad-stuff:sql>DELETE * FROM AUDIT_LOG</dellroad-stuff:sql>

      <!-- A more complicated, externally referenced, SQL script -->
      <dellroad-stuff:sql resource="classpath:reset.sql" split-pattern=";\n"/>

      <!-- other beans... -->

  </beans>

  • Field Details

    • DEFAULT_SPLIT_PATTERN

      public static final String DEFAULT_SPLIT_PATTERN
      The default split pattern: ";[ \\t\\r]*\\n\\s*".
      See Also:

  • Constructor Details

    • SQLCommandList

      public SQLCommandList()

    • SQLCommandList

      public SQLCommandList(String sqlScript)

  • Method Details

    • setSQLScript

      public void setSQLScript(String sqlScript)
      Configure the SQL script. This is a required property.

      For scripts in external resources, consider ResourceReaderFactoryBean or use the <dellroad-stuff:sql> element.

      Parameters:
      sqlScript - script containing one or more SQL statements; if more than one statement is present, a split pattern must also be configured
      See Also:

    • setSplitPattern

      public void setSplitPattern(String splitPattern)
      Set the split pattern used to split apart a script containing multiple SQL statements into individual statements.

      The default value for this property is ";[ \t\r]*\n\s*", which should handle cases where SQL statements are terminated by semi-colons and each SQL statement starts on a new line.

      If this is set to null, or the script does not contain any instances of the regular expression, the script is assumed to contain a single SQL statement. SQL statements are whitespace-trimmed and any "statements" that consist entirely of whitespace are ignored.

      Parameters:
      splitPattern - regular expression pattern used to split multiple SQL statements
      Throws:
      PatternSyntaxException - if the pattern is not a valid Java regular expression

    • apply

      public void apply(Connection c) throws SQLException
      Applies each individual SQL command in the script. Commands are separated using the split pattern.
      Specified by:
      apply in interface DatabaseAction<Connection>
      Parameters:
      c - open transaction
      Throws:
      SQLException

    • split

      public List<SQLCommand> split()
      Split the SQL script into individual statements and return them as DatabaseActions.
      Returns:
      list of individual SQL statements

    • splitSQL

      public String[] splitSQL()
      Split the configured SQL script into individual SQL statements using the configured split pattern.
      Returns:
      an array of individual SQL statements
      Throws:
      IllegalArgumentException - if no SQL script is configured