001 package com.sptci.rwt;
002
003 import java.sql.Connection;
004 import java.sql.DatabaseMetaData;
005 import java.sql.Statement;
006 import java.sql.SQLException;
007
008 import java.util.ArrayList;
009 import java.util.List;
010
011 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
012
013 import com.sptci.util.CloseJDBCResources;
014
015 /**
016 * A utility class to execute SQL statements (single or batch).
017 *
018 * <p>© Copyright 2007 <a href='http://sptci.com/' target='_new'>Sans Pareil Technologies, Inc.</a></p>
019 * @author Rakesh Vidyadharan 2007-10-01
020 * @version $Id: BatchQueryExecutor.java 4123 2008-05-25 21:49:01Z rakesh $
021 */
022 public class BatchQueryExecutor extends AbstractQueryExecutor
023 {
024 /** An array database names that need special handling. */
025 public static final String[] SPECIAL = { "oracle", "mysql" };
026
027 /**
028 * Create a new instance using the specified manager to fetch connections.
029 *
030 * @param manager The connection manager to use.
031 */
032 public BatchQueryExecutor( final ConnectionManager manager )
033 {
034 super( manager );
035 }
036
037 /**
038 * Execute the specified statement(s) and return all the {@link
039 * java.sql.ResultSet} and {@link java.sql.Statement#getUpdateCount}
040 * values that result from executing the statement(s).
041 *
042 * <p><b>Note:</b> Statement processing is handled differently for
043 * different database engines. PostgreSQL and Sybase (probably MS
044 * SQL Server) automatically support execution of a batch of statements
045 * and return multiple result sets and update counts corresponding to
046 * each statement included in the batch. Oracle and MySQL do not
047 * support this feature. For databases that do not support automatic
048 * batch execution, the SQL statement is parsed using a very simple rule -
049 * a semi-colon is treated as the delimiter for a statement; and executed
050 * individually. The Oracle default <i>/</i> character is used to denote
051 * the separator between individual statements. Needless to say this is
052 * a very serious shortcoming. You will not be able to execute complex
053 * statements such as procedure creation etc for these engines. The
054 * following shows simple syntax for delimited queries.</p>
055 *
056 * <pre>
057 * select * from table1 order by column1;
058 * /
059 * select * from table2;
060 * </pre>
061 *
062 * @see #parseStatements
063 * @see #execute( String, List, Connection, int, int )
064 * @param sql The statement(s) to be executed.
065 * @param parameters Optioanl parameters to control the result set size. The
066 * following parameters are supported:
067 * <ol>
068 * <li><code>maxRows</code> The maximum number of rows to retrieve.
069 * Specify 0 for no restriction.</li>
070 * <li><code>maxColumnLength</code> The maximum size of a column. Specify
071 * 0 for no restriction.</li>
072 * </ol>
073 * @return A collection of {@link Rows} objects that represents all
074 * the {@link java.sql.ResultSet} and {@link
075 * java.sql.Statement#getUpdateCount}s generated by executing the
076 * statement(s).
077 * @throws SQLException If errors are encountered while executing the
078 * statement(s).
079 */
080 public List<Rows> execute( final String sql, final int... parameters )
081 throws SQLException
082 {
083 Connection connection = null;
084 List<Rows> collection = new ArrayList<Rows>();
085
086 try
087 {
088 int maxRows = 0;
089 int columnLength = 0;
090 switch ( parameters.length )
091 {
092 case 1:
093 maxRows = parameters[0];
094 break;
095 case 2:
096 maxRows = parameters[0];
097 columnLength = parameters[1];
098 break;
099 }
100
101 connection = manager.open();
102
103 DatabaseMetaData dmd = connection.getMetaData();
104 final String name = dmd.getDatabaseProductName().toLowerCase();
105 boolean special = false;
106 for ( String db : SPECIAL )
107 {
108 if ( name.contains( db ) )
109 {
110 special = true;
111 break;
112 }
113 }
114
115 if ( special )
116 {
117 for ( String query : parseStatements( sql ) )
118 {
119 execute( query, collection, connection, maxRows, columnLength );
120 }
121 }
122 else
123 {
124 execute( sql, collection, connection, maxRows, columnLength );
125 }
126 }
127 finally
128 {
129 CloseJDBCResources.close( connection );
130 }
131
132 return collection;
133 }
134
135 /**
136 * Execute the specified statement(s) and return a Excel workbook
137 * that represents all the data contained in the {@link
138 * java.sql.ResultSet}s obtained by executing the statement.
139 *
140 * @see #execute
141 * @param sql The statement to be executed.
142 * @return The excel workbook that contains the result set.
143 * @throws SQLException If errors are encountered while executing the
144 * statement.
145 */
146 public HSSFWorkbook export( final String sql ) throws SQLException
147 {
148 List<Rows> rows = execute( sql );
149 ExcelGenerator generator = new ExcelGenerator();
150 return generator.generate( sql, rows );
151 }
152
153 /**
154 * Execute the specified SQL statement and fetch its associated
155 * result set or update count. Fetches multiple result set or update
156 * counts if the statement produced multiple results.
157 *
158 * @since Version 1.1
159 * @see #createStatement
160 * @see #processResultSet
161 * @see #processUpdateCount
162 * @param sql The SQL statement to execute.
163 * @param list The list to which the results are to be added.
164 * @param connection The database connection to use.
165 * @param maxRows The maximum number of rows to fetch.
166 * @param columnLength The maximum size for the data in a column.
167 * @throws SQLException If errors are encountered while executing the query.
168 */
169 private void execute( final String sql, final List<Rows> list,
170 final Connection connection, final int maxRows, final int columnLength )
171 throws SQLException
172 {
173 Statement statement = null;
174
175 try
176 {
177 statement = createStatement( connection );
178 statement.setMaxRows( maxRows );
179
180 boolean status = statement.execute( sql );
181
182 if ( status )
183 {
184 list.add( processResultSet( statement, 0, Integer.MAX_VALUE, columnLength ) );
185 }
186 else
187 {
188 list.add( processUpdateCount( statement ) );
189 }
190
191 boolean hasMore = false;
192 int updateCount = -1;
193 while ( ( hasMore = statement.getMoreResults() ) ||
194 ( ( updateCount = statement.getUpdateCount() ) != -1 ) )
195 {
196 list.add( ( ( hasMore ) ?
197 processResultSet( statement, 0, Integer.MAX_VALUE, columnLength ) :
198 processUpdateCount( statement ) ) );
199
200 hasMore = false;
201 updateCount = -1;
202 }
203 }
204 finally
205 {
206 CloseJDBCResources.close( statement );
207 }
208 }
209
210 /**
211 * Parse the specified SQL statement batch and extract individual
212 * statements from the batch. Uses very simple parsing rules, so only
213 * ver basic statements can be parsed by this method. This method may
214 * be enhanced over the life-time of the application with more robust
215 * parsing rules.
216 *
217 * <p><b>Note</b> This method currently supports Oracle style <i>/</i>
218 * separated commands. It is expected that MySQL users follow the same
219 * rule.</p>
220 *
221 * <p>The parser code is taken from a post in the PostgreSQL forum. The
222 * original post can be viewed
223 * <a href='http://archives.postgresql.org/pgsql-jdbc/2006-02/msg00034.php'>here</a>.</p>
224 *
225 * @since Version 1.1
226 * @param sql The batch of statements to be parsed.
227 * @return The collection of individual statements to be executed.
228 */
229 private List<String> parseStatements( final String sql )
230 {
231 List<String> list = new ArrayList<String>();
232 StringBuilder builder = new StringBuilder( 128 );
233 String query = sql.replaceAll( "\\r\\n", "\\n" );
234 String[] queries = query.split( "\\n" );
235
236 boolean statementReady = false;
237 int count = 0;
238 for ( String line : queries )
239 {
240 // different continuation for oracle and postgres
241 line = line.trim();
242 if ( "--/exe/--".equals( line ) ) //execute finished statement for postgres
243 {
244 builder.append( ' ' );
245 statementReady = true;
246 }
247 else if ( "/".equals( line ) ) //execute finished statement for oracle
248 {
249 builder.append( ' ' );
250 statementReady = true;
251 }
252 else if ( "go".equalsIgnoreCase( line ) ) //execute finished statement for sybase and potentially MS SQL Server
253 {
254 builder.append( ' ' );
255 statementReady = true;
256 }
257 else if ( line.startsWith( "--" ) || line.length() == 0 ) // comment or empty
258 {
259 continue;
260 }
261 else if ( line.endsWith( ";" ) )
262 {
263 builder.append( ' ' );
264 builder.append( line.substring( 0, line.length() - 1 ) );
265 statementReady = true;
266 }
267 else
268 {
269 builder.append( ' ' );
270 builder.append( line );
271 statementReady = false;
272 }
273
274 if (statementReady)
275 {
276 if ( builder.length() < 2 ) continue;
277 list.add( builder.toString() );
278 ++count;
279 builder = new StringBuilder( 128 );
280 }
281 }
282
283 return list;
284 }
285 }