Package org.oddjob.sql

Class SQLJob

java.lang.Object

org.oddjob.sql.SQLJob

All Implemented Interfaces:

Serializable, Runnable, ArooaSessionAware, Stoppable

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

Author:

rob and Ant.

See Also:

• Serialized Form

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.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 interrupted 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>

Nested Class Summary

Nested Classes

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

Constructor Summary

Constructors

Constructor	Description
SQLJob()	Constructor.

Method Summary

Modifier and Type	Method	Description
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.
Consumer<Object>	getResults()	Getter for results.
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(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(Consumer<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 Details

SQLJob

public SQLJob()

Constructor.

Method Details

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 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

getResults

public Consumer<Object> getResults()

Getter for results.

Returns:

Result Handler. May be null.

setResults

public void setResults(Consumer<Object> results)

Setter for results.

Parameters:

results - Result Handler. May be null.

setInput

public void setInput(InputStream sql)

Reference 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)

Reference 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)

Reference 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)

Parameters:

delimiter - the separator.

Reference 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 ;

getDelimiter

public String getDelimiter()

Get the statement delimiter.

Returns:

setDelimiterType

public void setDelimiterType(SQLJob.DelimiterType delimiterType)

Parameters:

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

Reference 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.

getDelimiterType

public SQLJob.DelimiterType getDelimiterType()

Get the delimiter type.

Returns:

setKeepFormat

public void setKeepFormat(boolean keepFormat)

Parameters:

keepFormat - The true or false to keep the format of the SQL.

Reference Property:

keepFormat

Description

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

Required:

No. Defaults to false.

isKeepFormat

public boolean isKeepFormat()

Get if SQL keeps input format.

Returns:

setConnection

public void setConnection(Connection connection)

Reference 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)

Parameters:

autocommit -

Reference Property:

autocommit

Description

Autocommit statements once executed.

Required:

No, defaults to false.

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)

Reference 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)

Reference 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)

Parameters:

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

Reference Property:

escapeProcessing

Description

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

Required:

No, defaults to false.

isEscapeProcessing

public boolean isEscapeProcessing()

Getter for escapeProcessing.

Returns:

setDialect

public void setDialect(DatabaseDialect dialect)

Parameters:

dialect - The Database Dialect.

Reference 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.

getDialect

public DatabaseDialect getDialect()

Getter for dialect.

Returns:

setOnError

public void setOnError(SQLJob.OnError action)

Parameters:

action - the action to perform on statement failure.

Reference 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.

getOnError

public SQLJob.OnError getOnError()

Get on error action.

Returns:

getExecutedSQLCount

public int getExecutedSQLCount()

Returns:

The number.

Reference Property:

executedSQLCount

Description

The number of SQL statements executed.

getSuccessfulSQLCount

public int getSuccessfulSQLCount()

Returns:

The number.

Reference Property:

successfulSQLCount

Description

The number of SQL statements successfully executed.

toString

public String toString()

Overrides:

toString in class Object