org.oddjob.sql

Class SQLJob

java.lang.Object

org.oddjob.sql.SQLJob

All Implemented Interfaces:

java.io.Serializable, java.lang.Runnable, ArooaSessionAware, ServiceProvider, BusServiceProvider, Stoppable

public class SQLJob
extends java.lang.Object
implements java.lang.Runnable, java.io.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

Nested Classes

Modifier and Type	Class and Description
static class	SQLJob.DelimiterType delimiter type between SQL statements.
static class	SQLJob.OnError The action a task should perform on an error.

Constructor Summary

Constructors

Constructor and Description
SQLJob() Constructor.

Method Summary

Modifier and Type	Method and Description
java.lang.String	getDelimiter() Get the statement delimiter.
SQLJob.DelimiterType	getDelimiterType() Get the delimiter type.
DatabaseDialect	getDialect() Getter for dialect.
java.lang.String	getEncoding() Get the input encoding name.
int	getExecutedSQLCount()
boolean	getExpandProperties() Is property expansion inside inline text enabled?
java.lang.String	getName() Get the name.
SQLJob.OnError	getOnError() Get on error action.
ValueType	getParameters(int index) Indexed getter for parameter types.
java.util.Collection<java.lang.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) Accept the current Arooa Session.
void	setAutocommit(boolean autocommit)
void	setCallable(boolean callable)
void	setConnection(java.sql.Connection connection)
void	setDelimiter(java.lang.String delimiter)
void	setDelimiterType(SQLJob.DelimiterType delimiterType)
void	setDialect(DatabaseDialect dialect)
void	setEncoding(java.lang.String encoding)
void	setEscapeProcessing(boolean enable)
void	setExpandProperties(boolean expandProperties)
void	setInput(java.io.InputStream sql)
void	setKeepFormat(boolean keepformat)
void	setName(java.lang.String name) Set the name
void	setOnError(SQLJob.OnError action)
void	setParameters(int index, ValueType parameter)
void	setResults(java.util.Collection<java.lang.Object> results) Setter for results.
void	stop() Stop executing.
java.lang.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)

Description copied from interface: ArooaSessionAware

Accept the current Arooa Session.

Specified by:

setArooaSession in interface ArooaSessionAware

Parameters:

session - The Arooa Session. Never null.

getName

public java.lang.String getName()

Get the name.

Returns:

The name.

setName

public void setName(java.lang.String name)

Set the name

Parameters:

name - The name.

run

public void run()

Specified by:

run in interface java.lang.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 java.util.Collection<java.lang.Object> getResults()

Getter for results.

Returns:

Result Handler. May be null.

setResults

public void setResults(java.util.Collection<java.lang.Object> results)

Setter for results.

Parameters:

results - Result Handler. May be null.

setInput

public void setInput(java.io.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(java.lang.String encoding)

Property: encoding

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

Required: No.

getEncoding

public java.lang.String getEncoding()

Get the input encoding name.

Returns:

setDelimiter

public void setDelimiter(java.lang.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 java.lang.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(java.sql.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 java.lang.String toString()

Overrides:

toString in class java.lang.Object