io.toro.martini

Class SqlMethods

java.lang.Object

io.toro.martini.SqlMethods

public final class SqlMethods
extends Object

Provides one-liner methods for easily creating, connecting, and executing statements against a JDBC data source.

Method Summary

Modifier and Type	Method and Description
static void	closeConnection(Connection connection) Close the provided connection.
static void	commitTransaction() Commit the current JTA transaction.
static void	commitTransaction(Connection connection) Commit changes in the provided connection.
static io.toro.gloop.object.property.GloopModel	executeSelect(String databaseConnectionName, String sqlStatement, io.toro.gloop.object.property.GloopModel sqlParameters, io.toro.gloop.engine.GloopExecutionContext context) Execute a SELECT statement, and then return the first row of the result set.
static int	executeSql(String databaseConnectionName, String sqlStatement, io.toro.gloop.object.property.GloopModel sqlParameters, io.toro.gloop.engine.GloopExecutionContext context) Execute a SQL statement against the specified data source.
static io.toro.gloop.object.cursor.GloopCursor	getCursorForInsertUpdateStatement(String databaseConnectionName, String sqlStatement, io.toro.gloop.object.property.GloopModel model, int batchSize, io.toro.gloop.engine.GloopExecutionContext context) Get a cursor for writing to the specified data source.
static io.toro.gloop.object.cursor.GloopCursor	getCursorForSelectStatement(String databaseConnectionName, String sqlStatement, io.toro.gloop.object.property.GloopModel sqlParameters, String modelReferenceName, io.toro.gloop.object.property.GloopModel modelTemplate, boolean updateable, io.toro.gloop.engine.GloopExecutionContext context) Get a cursor for reading the results of a SQL statement executed against the specified data source.
static Connection	getDatabaseConnection(io.toro.gloop.object.property.GloopModel connectionParameters, boolean autoCommit, io.toro.gloop.engine.GloopExecutionContext context) Attempt to establish a connection to a data source using the provided connection details.
static Connection	getDatabaseConnection(String databaseConnectionName, io.toro.gloop.engine.GloopExecutionContext context) Attempt to establish a connection to a data source using information from the provided Martini database connection.
static io.toro.gloop.object.cursor.GloopCursor	getInsertedKeysCursor(io.toro.gloop.object.cursor.GloopCursor batchCursor, io.toro.gloop.engine.GloopExecutionContext context) Get a cursor pointing to the keys generated from a batch insert operation.
static void	rollbackTransaction() Perform a roll back of the current JTA transaction.
static void	rollbackTransaction(Connection connection) Roll back changes in the provided connection.
static groovy.sql.Sql	sql(String poolName) Construct an Sql instance using a pre-defined database connection in Martini.
static Object	sql(String poolName, groovy.lang.Closure c) Invoke the provided closure, which will be supplied with a Sql instance from the specified Martini database connection.
static void	startTransaction(boolean autoCommit, int timeout, io.toro.gloop.engine.GloopExecutionContext context) Start a new JTA transaction using the Martini transaction manager.

Methods inherited from class java.lang.Object

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

Method Detail

sql

public static groovy.sql.Sql sql(@GloopParameter(allowNull=false)
                                 String poolName)
                          throws ToroException,
                                 SQLException

Construct an Sql instance using a pre-defined database connection in Martini. It is the caller's responsibility to close the Sql object after use.

Example usage:

 def sql = 'my-connection-pool'.sql()
 println 'Your existing projects:'
 sql.eachRow('select * from PROJECT') { row ->
     println "${row.name.padRight(10)} ($row.url)"
 }
 sql.close()
 

Parameters:

poolName - the name of the JDBC connection pool saved in Martini

Returns:

the SQL object

Throws:

ToroException

SQLException

sql

public static Object sql(@GloopParameter(allowNull=false)
                         String poolName,
                         @GloopParameter(allowNull=false)
                         groovy.lang.Closure c)
                  throws ToroException,
                         SQLException

Invoke the provided closure, which will be supplied with a Sql instance from the specified Martini database connection. The Sql instance will be automatically closed after the closure's execution.

Example usage:

 'my-connection-pool'.sql() { sql ->
     println 'Your existing projects:'
     sql.eachRow('select * from PROJECT') { row ->
         println "${row.name.padRight(10)} ($row.url)"
     }
 }
 

Parameters:

poolName - the name of the JDBC connection pool saved in Martini

Returns:

whatever object the closure returns

Throws:

ToroException

SQLException

commitTransaction

public static void commitTransaction()

Commit the current JTA transaction.

Since:

1.0

commitTransaction

public static void commitTransaction(@GloopParameter(allowNull=false)
                                     Connection connection)
                              throws SQLException

Commit changes in the provided connection.

Parameters:

connection - the affected connection

Throws:

SQLException

Since:

1.0

See Also:

Connection.commit()

rollbackTransaction

public static void rollbackTransaction()

Perform a roll back of the current JTA transaction.

rollbackTransaction

public static void rollbackTransaction(@GloopParameter(allowNull=false)
                                       Connection connection)
                                throws SQLException

Roll back changes in the provided connection.

Parameters:

connection - the affected connection

Throws:

SQLException

Since:

1.0

See Also:

Connection.rollback()

closeConnection

public static void closeConnection(@GloopParameter(allowNull=false)
                                   Connection connection)
                            throws SQLException

Close the provided connection.

Parameters:

connection - the affected connection

Throws:

SQLException

Since:

1.0

See Also:

Connection.close()

getDatabaseConnection

@GloopParameter(name="databaseConnection")
public static Connection getDatabaseConnection(@GloopParameter(allowNull=false)
                                                                                          String databaseConnectionName,
                                                                                          io.toro.gloop.engine.GloopExecutionContext context)
                                                                                   throws SQLException

Attempt to establish a connection to a data source using information from the provided Martini database connection.

Parameters:

databaseConnectionName - the name of the saved database connection in Martini

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

connection to the requested data source

Throws:

SQLException - if a database connection with the provided name could not be found

getDatabaseConnection

@GloopParameter(name="databaseConnection")
public static Connection getDatabaseConnection(@GloopObjectParameter(value="connectionParameters:JDBC Connection Parameters{\ndriverClassName::JDBC Driver Class Name\nconnectionUrl::JDBC Connection URL\nusername::Database username\nupassword::Database password\n}") @GloopParameter(allowNull=false)
                                                                                          io.toro.gloop.object.property.GloopModel connectionParameters,
                                                                                          @GloopParameter(defaultValue="true")
                                                                                          boolean autoCommit,
                                                                                          io.toro.gloop.engine.GloopExecutionContext context)
                                                                                   throws ClassNotFoundException,
                                                                                          SQLException

Attempt to establish a connection to a data source using the provided connection details.

Parameters:

connectionParameters - the connection details of the data source to connect to

autoCommit - flag for setting the to-be-created connection on auto-commit mode

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

connection to the requested data source

Throws:

ClassNotFoundException - if the provided database driver does not exist in the class path

SQLException - if a database access error occurs; if the database's URL is null

getCursorForSelectStatement

@GloopParameter(name="selectCursor")
public static io.toro.gloop.object.cursor.GloopCursor getCursorForSelectStatement(@GloopParameter(allowNull=false)
                                                                                                                       String databaseConnectionName,
                                                                                                                       @GloopParameter(allowNull=false)
                                                                                                                       String sqlStatement,
                                                                                                                       io.toro.gloop.object.property.GloopModel sqlParameters,
                                                                                                                       String modelReferenceName,
                                                                                                                       io.toro.gloop.object.property.GloopModel modelTemplate,
                                                                                                                       @GloopParameter(defaultValue="false")
                                                                                                                       boolean updateable,
                                                                                                                       io.toro.gloop.engine.GloopExecutionContext context)
                                                                                                                throws io.toro.gloop.exception.GloopException,
                                                                                                                       SQLException

Get a cursor for reading the results of a SQL statement executed against the specified data source.

Parameters:

databaseConnectionName - the name of the saved database connection in Martini that will be receive the statement

sqlStatement - the SQL statement to execute; supports query parameters

sqlParameters - the SQL statement query parameters

modelReferenceName - the namespace of the model to use as reference for the entries in the cursor;

modelTemplate - the model to use as reference for the entries in the cursor if modelReferenceName is unspecified

updateable - whether or not the cursor's result set is updatable

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

an input cursor where results can be read from

Throws:

io.toro.gloop.exception.GloopException - if modelReferenceName could not be resolved (if provided)

SQLException - if a database connection with the provided name could not be found

getCursorForInsertUpdateStatement

@GloopParameter(name="insertUpdateCursor")
public static io.toro.gloop.object.cursor.GloopCursor getCursorForInsertUpdateStatement(@GloopParameter(allowNull=false)
                                                                                                                                   String databaseConnectionName,
                                                                                                                                   @GloopParameter(allowNull=false)
                                                                                                                                   String sqlStatement,
                                                                                                                                   io.toro.gloop.object.property.GloopModel model,
                                                                                                                                   @GloopParameter(defaultValue="-1")
                                                                                                                                   int batchSize,
                                                                                                                                   io.toro.gloop.engine.GloopExecutionContext context)
                                                                                                                            throws SQLException

Get a cursor for writing to the specified data source.

Parameters:

databaseConnectionName - the name of the saved database connection in Martini that will be receive the statement

sqlStatement - the SQL statement to execute

model - the model to use as reference for the entries to be written to the cursor

batchSize - batch size before sending the update request to the database

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

an output cursor where records can be written to

Throws:

SQLException - if a database connection with the provided name could not be found; if a database access error occurs

getInsertedKeysCursor

@GloopParameter(name="insertedKeysCursor")
public static io.toro.gloop.object.cursor.GloopCursor getInsertedKeysCursor(@GloopParameter(allowNull=false)
                                                                                                                       io.toro.gloop.object.cursor.GloopCursor batchCursor,
                                                                                                                       io.toro.gloop.engine.GloopExecutionContext context)
                                                                                                                throws SQLException

Get a cursor pointing to the keys generated from a batch insert operation. Requires the batch insert statement to have been executed with the RETURN_GENERATED_KEYS flag.

Parameters:

batchCursor - open batch insert cursor

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

an input cursor containing generated keys

Throws:

SQLException - if the provided cursor is already closed; if provided cursor is not a batch cursor

executeSql

@GloopParameter(name="updateCount")
public static int executeSql(@GloopParameter(allowNull=false)
                                                                 String databaseConnectionName,
                                                                 @GloopParameter(allowNull=false)
                                                                 String sqlStatement,
                                                                 io.toro.gloop.object.property.GloopModel sqlParameters,
                                                                 io.toro.gloop.engine.GloopExecutionContext context)
                                                          throws SQLException

Execute a SQL statement against the specified data source.

Parameters:

databaseConnectionName - the name of the saved database connection in Martini that will be receive the statement

sqlStatement - the SQL statement to execute

sqlParameters - the SQL statement query parameters

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

the row count for SQL Data Manipulation Language (DML) statements; 0 for SQL statements that return nothing

Throws:

SQLException - if a database connection with the provided name could not be found; if a database access error occurs

executeSelect

@GloopParameter(name="firstRow")
public static io.toro.gloop.object.property.GloopModel executeSelect(@GloopParameter(allowNull=false)
                                                                                                      String databaseConnectionName,
                                                                                                      @GloopParameter(allowNull=false)
                                                                                                      String sqlStatement,
                                                                                                      @GloopParameter(value="Query parameters")
                                                                                                      io.toro.gloop.object.property.GloopModel sqlParameters,
                                                                                                      io.toro.gloop.engine.GloopExecutionContext context)
                                                                                               throws io.toro.gloop.exception.GloopException,
                                                                                                      SQLException,
                                                                                                      IOException

Execute a SELECT statement, and then return the first row of the result set.

Parameters:

databaseConnectionName - the name of the saved database connection in Martini that will be receive the statement

sqlStatement - the SQL statement to execute

sqlParameters - the SQL statement query parameters

context - the Gloop execution context (automatically mapped by Gloop)

Returns:

the first row

Throws:

SQLException - if a database connection with the provided name could not be found

IOException - if cursor cannot be closed

io.toro.gloop.exception.GloopException

startTransaction

public static void startTransaction(@GloopParameter(defaultValue="false")
                                    boolean autoCommit,
                                    @GloopParameter(defaultValue="60")
                                    int timeout,
                                    io.toro.gloop.engine.GloopExecutionContext context)

Start a new JTA transaction using the Martini transaction manager. This only works with running services, not services that are being debugged.

Parameters:

autoCommit - if true, this will try and commit after the service has finished, even if an exception was thrown

timeout - amount of time in seconds to wait before timing out the transaction

context - the Gloop execution context (automatically mapped by Gloop)