Package io.toro.martini

Class SqlMethods

  • public final class SqlMethods
extends Object

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

    • Constructor Detail

      • SqlMethods

        @Autowired
SqlMethods​(io.toro.martini.database.DataSourceManager sourceManager)

    • Method Detail

      • sql

        public static Sql sql​(@GloopParameter(allowNull=false)
                      String poolName)
               throws 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:
        SQLException

      • sql

        public static Object sql​(@GloopParameter(allowNull=false)
                         String poolName,
                         @GloopParameter(allowNull=false)
                         Closure c)
                  throws 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:
        SQLException

      • commitTransaction

        public static void commitTransaction()
        Commit the current JTA transaction.
        Since:
        1.0

      • commitTransaction

        public static void commitTransaction​(@GloopParameter(allowNull=false)
                                     io.toro.martini.transaction.TransactionFacade transaction)
                              throws SQLException
        Commit changes in the provided JTA transaction.
        Parameters:
        transaction - the affected transaction
        Throws:
        SQLException
        Since:
        1.0
        See Also:
        TransactionFacade.commit()

      • rollbackTransaction

        public static void rollbackTransaction()
        Perform a roll back of the current JTA transaction.

      • rollbackTransaction

        public static void rollbackTransaction​(@GloopParameter(allowNull=false)
                                       io.toro.martini.transaction.TransactionFacade transaction)
        Perform a roll back of given JTA transaction.

      • getDatabaseConnection

        @GloopParameter(name="databaseConnection")
public static Connection getDatabaseConnection​(@GloopParameter(allowNull=false)
                                               String databaseConnectionName,
                                               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("connectionParameters:JDBC Connection Parameters{\ndriverClassName::JDBC Driver Class Name\nconnectionUrl::JDBC Connection URL\nusername::Database username\npassword::Database password\n}") @GloopParameter(allowNull=false)
                                               GloopModel connectionParameters,
                                               @GloopParameter(defaultValue="true")
                                               boolean autoCommit,
                                               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 GloopCursor getCursorForSelectStatement​(@GloopParameter(allowNull=false)
                                                      String databaseConnectionName,
                                                      @GloopParameter(allowNull=false)
                                                      String sqlStatement,
                                                      GloopModel sqlParameters,
                                                      String modelReferenceName,
                                                      GloopModel modelTemplate,
                                                      @GloopParameter(defaultValue="false")
                                                      boolean updateable,
                                                      GloopExecutionContext context)
                                               throws 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:
        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 GloopCursor getCursorForInsertUpdateStatement​(@GloopParameter(allowNull=false)
                                                            String databaseConnectionName,
                                                            @GloopParameter(allowNull=false)
                                                            String sqlStatement,
                                                            GloopModel model,
                                                            @GloopParameter(defaultValue="-1")
                                                            int batchSize,
                                                            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 GloopCursor getInsertedKeysCursor​(@GloopParameter(allowNull=false)
                                                GloopCursor batchCursor,
                                                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

        @Deprecated(since="2.2")
@GloopParameter(name="updateCount")
public static int executeSql​(@GloopParameter(allowNull=false)
                             String databaseConnectionName,
                             @GloopParameter(allowNull=false)
                             String sqlStatement,
                             GloopModel sqlParameters,
                             GloopExecutionContext context)
                      throws SQLException
        Deprecated.
        Execute a SQL statement against the specified data source.
        This method has been deprecated due to non-user-friendly name. Use executeWriteSql() instead.
        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

      • executeWriteSql

        @GloopParameter(name="updateCount")
public static int executeWriteSql​(@GloopParameter(allowNull=false)
                                  String databaseConnectionName,
                                  @GloopParameter(allowNull=false)
                                  String sqlStatement,
                                  GloopModel sqlParameters,
                                  GloopExecutionContext context)
                           throws SQLException
        Execute a Write SQL statement against the specified data source. This method is specifically designed for executing SQL statements that perform write operations, including INSERT, UPDATE, DELETE, and DDL (Data Definition Language) methods such as CREATE, ALTER, and DROP. Use this method when you need to modify the data or structure of the database.
        Parameters:
        databaseConnectionName - the name of the saved database connection in Martini that will be receive the statement
        sqlStatement - the Write SQL statement to execute
        sqlParameters - the SQL statement 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

      • startTransaction

        public static void startTransaction​(@GloopParameter(defaultValue="false")
                                    boolean autoCommit,
                                    @GloopParameter(defaultValue="60")
                                    int timeout,
                                    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)

      • startTransaction

        public static io.toro.martini.transaction.TransactionFacade startTransaction​(@GloopParameter(defaultValue="false")
                                                                             boolean autoCommit,
                                                                             @GloopParameter(defaultValue="60")
                                                                             int timeout,
                                                                             @GloopParameter(defaultValue="SUPPORTS",choices={"REQUIRED","SUPPORTS","MANDATORY","NEVER"})
                                                                             String propagationType,
                                                                             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
        propagationType - defaults to SUPPORTS, supported propagation types are REQUIRED, SUPPORTS, MANDATORY, NEVER
        timeout - amount of time in seconds to wait before timing out the transaction
        context - the Gloop execution context (automatically mapped by Gloop)