001
002 /*
003 * Copyright (C) 2011 Archie L. Cobbs. All rights reserved.
004 *
005 * $Id: SQLCommandList.java 205 2012-01-06 22:43:05Z archie.cobbs $
006 */
007
008 package org.dellroad.stuff.schema;
009
010 import java.sql.Connection;
011 import java.sql.SQLException;
012 import java.util.ArrayList;
013 import java.util.List;
014 import java.util.regex.Pattern;
015
016 /**
017 * Holds and executes a configured SQL script, possibly containing multiple statements.
018 *
019 * <p>
020 * If the script contains multiple SQL statements, individual statements will be executed individually, in order, by
021 * {@link #apply apply()}; however, this requires proper configuration of the {@linkplain #setSplitPattern split pattern}.
022 *
023 * <p>
024 * When using Spring, beans of this type can be created succintly using the <code><dellroad-stuff:sql></code> custom
025 * XML element. The {@linkplain #setSplitPattern split pattern} may be configured via the {@code split-pattern} attribute,
026 * and the SQL script is specified either directly via inline text or using the {@code resource} attribute. In the latter case,
027 * the character encoding can specified via the {@code charset} attribute (default is {@code "UTF-8"}).
028 *
029 * <p>
030 * For example:
031 * <blockquote><pre>
032 * <beans xmlns="http://www.springframework.org/schema/beans"
033 * <b>xmlns:dellroad-stuff="http://dellroad-stuff.googlecode.com/schema/dellroad-stuff"</b>
034 * xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
035 * xsi:schemaLocation="
036 * http://www.springframework.org/schema/beans
037 * http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
038 * <b>http://dellroad-stuff.googlecode.com/schema/dellroad-stuff
039 * http://dellroad-stuff.googlecode.com/svn/wiki/schemas/dellroad-stuff-1.0.xsd</b>">
040 *
041 * <!-- SQL action that clears the audit log -->
042 * <b><dellroad-stuff:sql>DELETE * FROM AUDIT_LOG</dellroad-stuff:sql></b>
043 *
044 * <!-- A more complicated, externally referenced, SQL script -->
045 * <b><dellroad-stuff:sql resource="classpath:reset.sql" split-pattern=";\n"/></b>
046 *
047 * <!-- other beans... -->
048 *
049 * </beans>
050 * </pre></blockquote>
051 */
052 public class SQLCommandList implements DatabaseAction<Connection> {
053
054 /**
055 * The default split pattern: <code>{@value}</code>.
056 */
057 public static final String DEFAULT_SPLIT_PATTERN = ";[ \\t\\r]*\\n\\s*";
058
059 private String sqlScript;
060 private String splitPattern = DEFAULT_SPLIT_PATTERN;
061
062 public SQLCommandList() {
063 }
064
065 public SQLCommandList(String sqlScript) {
066 this.setSQLScript(sqlScript);
067 }
068
069 /**
070 * Configure the SQL script. This is a required property.
071 *
072 * <p>
073 * For scripts in external resources, consider {@link org.dellroad.stuff.spring.ResourceReaderFactoryBean}
074 * or use the <code><dellroad-stuff:sql></code> element.
075 *
076 * @param sqlScript script containing one or more SQL statements; if more than one statement is present,
077 * a {@link #setSplitPattern split pattern} must also be configured
078 * @see #setSplitPattern setSplitPattern()
079 */
080 public void setSQLScript(String sqlScript) {
081 this.sqlScript = sqlScript;
082 }
083
084 /**
085 * Set the <i>split pattern</i> used to split apart a script containing multiple SQL statements into individual statements.
086 *
087 * <p>
088 * The default value for this property is <code>";[ \t\r]*\n\s*"</code>, which should handle cases where
089 * SQL statements are terminated by semi-colons and each SQL statement starts on a new line.
090 *
091 * <p>
092 * If this is set to {@code null}, or the script does not contain any instances of the regular expression,
093 * the script is assumed to contain a single SQL statement. SQL statements are whitespace-trimmed and any
094 * "statements" that consist entirely of whitespace are ignored.
095 *
096 * @throws java.util.regex.PatternSyntaxException if the pattern is not a valid Java regular expression
097 */
098 public void setSplitPattern(String splitPattern) {
099 if (splitPattern != null)
100 Pattern.compile(splitPattern);
101 this.splitPattern = splitPattern;
102 }
103
104 /**
105 * Applies each individual SQL command in the script. Commands are separated
106 * using the {@link #setSplitPattern split pattern}.
107 */
108 @Override
109 public void apply(Connection c) throws SQLException {
110 for (SQLCommand sqlCommand : this.split())
111 sqlCommand.apply(c);
112 }
113
114 /**
115 * Split the SQL script into individual statements and return them as {@link DatabaseAction}s.
116 */
117 public List<SQLCommand> split() {
118 ArrayList<SQLCommand> list = new ArrayList<SQLCommand>();
119 for (String sql : this.splitSQL())
120 list.add(new SQLCommand(sql));
121 return list;
122 }
123
124 /**
125 * Split the {@linkplain #setSQLScript configured SQL script} into individual SQL statements
126 * using the configured {@linkplain #setSplitPattern split pattern}.
127 *
128 * @return an array of individual SQL statements
129 * @throws IllegalArgumentException if no SQL script is configured
130 */
131 public String[] splitSQL() {
132 if (this.sqlScript == null)
133 throw new IllegalArgumentException("no SQL script configured");
134 String[] sqls = this.splitPattern != null ? this.sqlScript.split(this.splitPattern) : new String[] { this.sqlScript };
135 ArrayList<String> list = new ArrayList<String>(sqls.length);
136 for (String sql : sqls) {
137 sql = sql.trim();
138 if (sql.length() == 0)
139 continue;
140 list.add(sql);
141 }
142 return list.toArray(new String[list.size()]);
143 }
144 }
145