org.oddjob.sql
Class SQLJob

java.lang.Object
  extended by org.oddjob.sql.SQLJob
All Implemented Interfaces:
Serializable, Runnable, ArooaSessionAware, ServiceProvider, BusServiceProvider, Stoppable

public class SQLJob
extends Object
implements Runnable, Serializable, ArooaSessionAware, Stoppable, BusServiceProvider

Description

Runs one or more SQL statements.

Parsing

The SQL will be parsed and broken into individual statements before being executed using JDBC. The statements are broken according to the delimiter and delimiterType properties. Setting the expandProperties property to true will cause Oddjob to expand ${} expressions within the SQL. Comments are achieved by starting a line with -- or // or REM. Note that /* */ is not yet supported.

Result Processing

An optional result processor may be provided. SQLResultsSheet allows the results to be displayed on a result sheet in a similar style to an SQL query tool. SQLResultsBean allows results to be captured as beans who's properties can be used elsewhere in Oddjob.

Errors and Auto Commit

The onError property controls what to do if a statement fails. By default it is ABORT. Auto commit is false by default so the changes are rolled back. If auto commit is true the ABORT has the same affect as STOP which commits statements already executed.

Parameterised Statements and Procedures

SQL statements can be parameterised, and can be stored procedure or function calls. Out parameter values can also be accessed and used elsewhere in Oddjob by wrapping them with an IdentifiableValueType. See example 2 for an example of this.

Caveats

SQLServer stored procedures with parameters must be made using the JDBC style call. E.g. { call sp_help(?) } otherwise an exception is thrown from getParameterMetaData.

Example

A simple example shows first the execution of multiple statements, then a simple parameterised query.
<oddjob>
  <job>
    <sequential>
      <jobs>
        <variables id="vars">
          <connection>
            <connection driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:mem:test" username="sa"/>
          </connection>
        </variables>
        <sql name="Create table">
          <connection>
            <value value="${vars.connection}"/>
          </connection>
          <input>
            <buffer>
	        	            
create table GREETINGS(STYLE varchar(20),
       TEXT varchar(20))
       
insert into GREETINGS values('nice', 'Hello')

insert into GREETINGS values('grumpy', 'Bah Humbug')

            </buffer>
          </input>
        </sql>
        <sql id="query">
          <connection>
            <value value="${vars.connection}"/>
          </connection>
          <input>
            <buffer>
	        	            
select TEXT from GREETINGS where STYLE = ?

            </buffer>
          </input>
          <parameters>
            <value value="nice"/>
          </parameters>
          <results>
            <sql-results-bean/>
          </results>
        </sql>
        <echo name="Single Row Result">${query.results.row.TEXT}</echo>
        <echo name="Result By Row Index">${query.results.rows[0].TEXT}</echo>
      </jobs>
    </sequential>
  </job>
</oddjob>
The results are made available to the echo jobs using a SQLResultsBean.

Example

An Callable Statement example. Showing support for IN, INOUT, and OUT parameters. Note that declaring the stored procedure requires a change in delimiter otherwise the semicolon is inturprited as an end of statement.
<oddjob>
  <job>
    <sequential>
      <jobs>
        <variables id="vars">
          <connection>
            <connection driver="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:mem:test" username="sa"/>
          </connection>
        </variables>
        <sql callable="true" delimiterType="ROW">
          <connection>
            <value value="${vars.connection}"/>
          </connection>
          <input>
            <buffer>
              create procedure TEST (out a int, inout b int, in c int)
              MODIFIES SQL DATA
              begin atomic
              set a = b;
              set b = c;
              end
            </buffer>
          </input>
        </sql>
        <sql id="sql-call" callable="true">
          <connection>
            <value value="${vars.connection}"/>
          </connection>
          <parameters>
            <identify id="a">
              <value>
                <value/>
              </value>
            </identify>
            <identify id="b">
              <value>
                <value value="2"/>
              </value>
            </identify>
            <value value="3"/>
          </parameters>
          <input>
            <buffer>
              call TEST (?, ?, ?)
            </buffer>
          </input>
        </sql>
        <echo>a=${a}, b=${b}.</echo>
      </jobs>
    </sequential>
  </job>
</oddjob>
See Also:
Serialized Form
Author:
rob and Ant.

Nested Class Summary
static class SQLJob.DelimiterType
          delimiter type between SQL statements.
static class SQLJob.OnError
          The action a task should perform on an error.
 
Constructor Summary
SQLJob()
          Constructor.
 
Method Summary
 String getDelimiter()
          Get the statement delimiter.
 SQLJob.DelimiterType getDelimiterType()
          Get the delimiter type.
 DatabaseDialect getDialect()
          Getter for dialect.
 String getEncoding()
          Get the input encoding name.
 int getExecutedSQLCount()
           
 boolean getExpandProperties()
          Is property expansion inside inline text enabled?
 String getName()
          Get the name.
 SQLJob.OnError getOnError()
          Get on error action.
 ValueType getParameters(int index)
          Indexed getter for parameter types.
 Collection<Object> getResults()
          Getter for results.
 SimpleBusService getServices()
          Provider the services.
 int getSuccessfulSQLCount()
           
 boolean isAutocommit()
          Getter for autocommit.
 boolean isCallable()
          Is the statement a stored procedure.
 boolean isEscapeProcessing()
          Getter for escapeProcessing.
 boolean isKeepFormat()
          Get if SQL keeps input format.
 void run()
           
 void setArooaSession(ArooaSession session)
           
 void setAutocommit(boolean autocommit)
           
 void setCallable(boolean callable)
           
 void setConnection(Connection connection)
           
 void setDelimiter(String delimiter)
           
 void setDelimiterType(SQLJob.DelimiterType delimiterType)
           
 void setDialect(DatabaseDialect dialect)
           
 void setEncoding(String encoding)
           
 void setEscapeProcessing(boolean enable)
           
 void setExpandProperties(boolean expandProperties)
           
 void setInput(InputStream sql)
           
 void setKeepFormat(boolean keepformat)
           
 void setName(String name)
          Set the name
 void setOnError(SQLJob.OnError action)
           
 void setParameters(int index, ValueType parameter)
           
 void setResults(Collection<Object> results)
          Setter for results.
 void stop()
          Stop executing.
 String toString()
           
 
Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait
 

Constructor Detail

SQLJob

public SQLJob()
Constructor.

Method Detail

setArooaSession

public void setArooaSession(ArooaSession session)
Specified by:
setArooaSession in interface ArooaSessionAware

getName

public String getName()
Get the name.

Returns:
The name.

setName

public void setName(String name)
Set the name

Parameters:
name - The name.

run

public void run()
Specified by:
run in interface Runnable

stop

public void stop()
Description copied from interface: Stoppable
Stop executing. This method should not return until the Stoppable has actually stopped.

Specified by:
stop in interface Stoppable

getServices

public SimpleBusService getServices()
Description copied from interface: ServiceProvider
Provider the services.

Specified by:
getServices in interface ServiceProvider
Specified by:
getServices in interface BusServiceProvider
Returns:
The services. May be null.

getResults

public Collection<Object> getResults()
Getter for results.

Returns:
Result Handler. May be null.

setResults

public void setResults(Collection<Object> results)
Setter for results.

Parameters:
results - Result Handler. May be null.

setInput

public void setInput(InputStream sql)

Property: input

Description: The input from where to read the SQL query or DML statement(s) to run. Probably either FileType for reading the SQL from a file or BufferType for configuring the SQL in line.

Required: Yes.


setExpandProperties

public void setExpandProperties(boolean expandProperties)

Property: expandProperties

Description: Enable property expansion inside the SQL statements read from the input.

Required: No, defaults to false.


getExpandProperties

public boolean getExpandProperties()
Is property expansion inside inline text enabled?

Returns:
true if properties are to be expanded.

setEncoding

public void setEncoding(String encoding)

Property: encoding

Description: Set the string encoding to use on the SQL read in.

Required: No.


getEncoding

public String getEncoding()
Get the input encoding name.

Returns:

setDelimiter

public void setDelimiter(String delimiter)

Property: delimiter

Description: Set the delimiter that separates SQL statements. Defaults to a semicolon.

For scripts that use a separate line delimiter like "GO" also set the delimiterType to "ROW".

The delimiter is case insensitive so either "GO" or "go" can be used interchangeably.

Required: No. Defaults to ;

Parameters:
delimiter - the separator.

getDelimiter

public String getDelimiter()
Get the statement delimiter.

Returns:

setDelimiterType

public void setDelimiterType(SQLJob.DelimiterType delimiterType)

Property: delimiterType

Description: Set the delimiter type: NORMAL or ROW.

NORMAL means that any occurrence of the delimiter terminates the SQL command whereas with ROW, only a line containing just the delimiter is recognised as the end of the command.

ROW is used with delimiters such as GO.

Required: No, defaults to NORMAL.

Parameters:
delimiterType - the type of delimiter - "NORMAL" or "ROW".

getDelimiterType

public SQLJob.DelimiterType getDelimiterType()
Get the delimiter type.

Returns:

setKeepFormat

public void setKeepFormat(boolean keepformat)

Property: keepFormat

Description: Whether or not the format of the SQL should be preserved.

Required: No. Defaults to false.

Parameters:
keepformat - The keepformat to set

isKeepFormat

public boolean isKeepFormat()
Get if SQL keeps input format.

Returns:

setConnection

public void setConnection(Connection connection)

Property: connection

Description: The connection to use. This can be provided by a ConnectionType or by some other means such as custom data source. This SQL job will always close the connection once it has run.

Required: Yes.


setAutocommit

public void setAutocommit(boolean autocommit)

Property: autocommit

Description: Autocommit statements once executed.

Required: No, defaults to false.

Parameters:
autocommit -

isAutocommit

public boolean isAutocommit()
Getter for autocommit.

Returns:

getParameters

public ValueType getParameters(int index)
Indexed getter for parameter types.

Parameters:
index -
Returns:

setParameters

public void setParameters(int index,
                          ValueType parameter)

Property: parameters

Description: Parameters to be bound to statement(s). This is either a ValueType or an IdentifiableValueType if the parameter is an out parameter that is to be identifiable by an id for other jobs to access.

Required: No.


setCallable

public void setCallable(boolean callable)

Property: callable

Description: If the statement calls a stored procedure.

Required: No, defaults to false.


isCallable

public boolean isCallable()
Is the statement a stored procedure.

Returns:

setEscapeProcessing

public void setEscapeProcessing(boolean enable)

Property: escapeProcessing

Description: Set escape processing for statements. See the java doc for Statement.setEscapeProcessing for more information.

Required: No, defaults to false.

Parameters:
enable - if true enable escape processing, default is true.

isEscapeProcessing

public boolean isEscapeProcessing()
Getter for escapeProcessing.

Returns:

setDialect

public void setDialect(DatabaseDialect dialect)

Property: dialect

Description: Allows a DatabaseDialect to be provided that can tune the way the result set is processed.

Required: No. A default is used.

Parameters:
dialect - The Database Dialect.

getDialect

public DatabaseDialect getDialect()
Getter for dialect.

Returns:

setOnError

public void setOnError(SQLJob.OnError action)

Property: onError

Description: What to do when a statement fails:

CONTINUE
Ignore the failure and continue executing.
STOP
Commit what has been executed but don't execute any more.
ABORT
Rollback what has been executed and don't execute any more.
Note that if autocommit is true then ABORT behaves like STOP as no roll back is possible.

Required: No, defaults to ABORT.

Parameters:
action - the action to perform on statement failure.

getOnError

public SQLJob.OnError getOnError()
Get on error action.

Returns:

getExecutedSQLCount

public int getExecutedSQLCount()

Property: executedSQLCount

Description: The number of SQL statements executed.

Returns:
The number.

getSuccessfulSQLCount

public int getSuccessfulSQLCount()

Property: successfulSQLCount

Description: The number of SQL statements successfully executed.

Returns:
The number.

toString

public String toString()
Overrides:
toString in class Object