com.sptci.rwt
Class BatchQueryExecutor

java.lang.Object
  com.sptci.rwt.AbstractQueryExecutor
      com.sptci.rwt.BatchQueryExecutor

public class BatchQueryExecutor
extends AbstractQueryExecutor

A utility class to execute SQL statements (single or batch).

© Copyright 2007 Sans Pareil Technologies, Inc.

Version:

$Id: BatchQueryExecutor.java 4123 2008-05-25 21:49:01Z rakesh $

Author:

Rakesh Vidyadharan 2007-10-01

Field Summary

static String[]

SPECIAL An array database names that need special handling.

Fields inherited from class com.sptci.rwt.AbstractQueryExecutor

manager

Constructor Summary

BatchQueryExecutor(ConnectionManager manager)
Create a new instance using the specified manager to fetch connections.

Method Summary

List<Rows>	execute(String sql, int... parameters) Execute the specified statement(s) and return all the ResultSet and Statement.getUpdateCount() values that result from executing the statement(s).
private void	execute(String sql, List<Rows> list, Connection connection, int maxRows, int columnLength) Execute the specified SQL statement and fetch its associated result set or update count.
org.apache.poi.hssf.usermodel.HSSFWorkbook	export(String sql) Execute the specified statement(s) and return a Excel workbook that represents all the data contained in the ResultSets obtained by executing the statement.
private List<String>	parseStatements(String sql) Parse the specified SQL statement batch and extract individual statements from the batch.

Methods inherited from class com.sptci.rwt.AbstractQueryExecutor

createStatement, getClobValue, processResultSet, processText, processUpdateCount

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

SPECIAL

public static final String[] SPECIAL

An array database names that need special handling.

Constructor Detail

BatchQueryExecutor

public BatchQueryExecutor(ConnectionManager manager)

Create a new instance using the specified manager to fetch connections.

Parameters:

manager - The connection manager to use.

Method Detail

execute

public List<Rows> execute(String sql,
                          int... parameters)
                   throws SQLException

Execute the specified statement(s) and return all the ResultSet and Statement.getUpdateCount() values that result from executing the statement(s).

Note: Statement processing is handled differently for different database engines. PostgreSQL and Sybase (probably MS SQL Server) automatically support execution of a batch of statements and return multiple result sets and update counts corresponding to each statement included in the batch. Oracle and MySQL do not support this feature. For databases that do not support automatic batch execution, the SQL statement is parsed using a very simple rule - a semi-colon is treated as the delimiter for a statement; and executed individually. The Oracle default / character is used to denote the separator between individual statements. Needless to say this is a very serious shortcoming. You will not be able to execute complex statements such as procedure creation etc for these engines. The following shows simple syntax for delimited queries.

   select * from table1 order by column1;
   /
   select * from table2;
 

Parameters:

sql - The statement(s) to be executed.

parameters - Optioanl parameters to control the result set size. The following parameters are supported:

1. maxRows The maximum number of rows to retrieve. Specify 0 for no restriction.
2. maxColumnLength The maximum size of a column. Specify 0 for no restriction.

Returns:

A collection of Rows objects that represents all the ResultSet and Statement.getUpdateCount()s generated by executing the statement(s).

Throws:

SQLException - If errors are encountered while executing the statement(s).

See Also:

parseStatements(java.lang.String), execute( String, List, Connection, int, int )

export

public org.apache.poi.hssf.usermodel.HSSFWorkbook export(String sql)
                                                  throws SQLException

Execute the specified statement(s) and return a Excel workbook that represents all the data contained in the ResultSets obtained by executing the statement.

Parameters:

sql - The statement to be executed.

Returns:

The excel workbook that contains the result set.

Throws:

SQLException - If errors are encountered while executing the statement.

See Also:

execute(java.lang.String, int...)

execute

private void execute(String sql,
                     List<Rows> list,
                     Connection connection,
                     int maxRows,
                     int columnLength)
              throws SQLException

Execute the specified SQL statement and fetch its associated result set or update count. Fetches multiple result set or update counts if the statement produced multiple results.

Parameters:

sql - The SQL statement to execute.

list - The list to which the results are to be added.

connection - The database connection to use.

maxRows - The maximum number of rows to fetch.

columnLength - The maximum size for the data in a column.

Throws:

SQLException - If errors are encountered while executing the query.

Since:

Version 1.1

See Also:

AbstractQueryExecutor.createStatement(java.sql.Connection), AbstractQueryExecutor.processResultSet(java.sql.Statement, int, int, int), AbstractQueryExecutor.processUpdateCount(java.sql.Statement)

parseStatements

private List<String> parseStatements(String sql)

Parse the specified SQL statement batch and extract individual statements from the batch. Uses very simple parsing rules, so only ver basic statements can be parsed by this method. This method may be enhanced over the life-time of the application with more robust parsing rules.

Note This method currently supports Oracle style / separated commands. It is expected that MySQL users follow the same rule.

The parser code is taken from a post in the PostgreSQL forum. The original post can be viewed here.

Parameters:

sql - The batch of statements to be parsed.

Returns:

The collection of individual statements to be executed.

Since:

Version 1.1