import groovy.sql.Sqldef url = 'jdbc:hsqldb:mem:yourDB'def user = 'sa'def password = ''def driver = 'org.hsqldb.jdbcDriver'def sql = Sql.newInstance(url, user, password, driver)// use 'sql' instance ...sql.close()

Sql.withInstance(url, user, password, driver) { sql ->  // use 'sql' instance ...}

It is often preferred to use a DataSource. You may have one available to you from a connection pool.Here we’ll use the one provided as part of the HSQLDB driver jar as shown here:

If you have your own connection pooling, the details will be different, e.g. for Apache Commons DBCP:

The previous examples assume that the necessary database driver jar is already on your classpath.For a self-contained script you can add@Grab statements to the top of the script to automatically download the necessary jar as shown here:

The@GrabConfig statement is necessary to make sure the system classloader is used. This ensures that the driver classes andsystem classes likejava.sql.DriverManager are in the same classloader.

The simplest way to execute SQL is to call theexecute() method passing the SQL you wish to execute as a String as shown here:

There is a variant of this method which takes a GString and another with a list of parameters. There are also other variants with similar names:executeInsert andexecuteUpdate.We’ll see examples of these variants in other examples in this section.

You can use the sameexecute() statement we saw earlier but to insert a row by using a SQL insert statement as follows:

You can use a specialexecuteInsert method instead ofexecute. This will return a list of all keys generated.Both theexecute andexecuteInsert methods allow you to place '?' placeholders into your SQL string and supply a list of parameters.In this case a PreparedStatement is used which avoids any risk of SQL injection. The following example illustratesexecuteInsert using placeholders and parameters:

In addition, both theexecute andexecuteInsert methods allow you to use GStrings. Any '$' placeholders within the SQL are assumedto be placeholders. An escaping mechanism exists if you want to supply part of the GString with a variable in aposition which isn’t where normal placeholders go within SQL. See the GroovyDoc for more details.Also,executeInsert allows you to supply a list of key column names, when multiple keys are returned and you are only interested in some of them. Here is a fragment illustrating key name specification and GStrings:

Reading rows of data from the database is accomplished using one of several available methods:query,eachRow,firstRow androws.

Use thequery method if you want to iterate through theResultSet returned by the underlying JDBC API as shown here:

Use theeachRow method if you want a slightly higher-level abstraction which provides a Groovy friendly map-like abstraction for theResultSet as shown here:

Note that you can use Groovy list-style and map-style notations when accessing the row of data.

Use thefirstRow method if you for similar functionality aseachRow but returning only one row of data as shown here:

Use therows method if you want to process a list of map-like data structures as shown here:

Note that the map-like abstraction has case-insensitive keys (hence we can use 'FIRSTNAME' or 'firstname' as the key) andalso that -ve indices (a standard Groovy feature) works when using an index value (to count column numbers from the right).

You can also use any of the above methods to return scalar values, though typicallyfirstRow is all that is required in such cases. An example returning the count of rows is shown here:

Updating rows can again be done using theexecute() method. Just use a SQL update statement as the argument to the method.You can insert an author with just a lastname and then update the row to also have a firstname as follows:

There is also a specialexecuteUpdate variant which returns the number of rows updated as a result of executing the SQL.For example, you can change the lastname of an author as follows:

Theexecute method is also used for deleting rows as this example shows:

The easiest way to perform database operations within a transaction is to include the database operation within awithTransaction closure as shown in the following example:

Here the database starts empty and has two rows after successful completion of the operation. Outside the scope of thetransaction, the database is never seen as having just one row.

If something goes wrong, any earlier operations within thewithTransaction block are rolled back.We can see that in operation in the following example where we use database metadata (more details coming up shortly) to find themaximum allowable size of thefirstname column and then attempt to enter a firstname one larger than that maximum value as shown here:

Even though the first sql execute succeeds initially, it will be rolled back and the number of rows will remain the same.

When dealing with large volumes of data, particularly when inserting such data, it can be more efficient to chunk the data into batches. This is doneusing thewithBatch statement as shown in the following example:

After executing these statements, there will be 7 new rows in the database. In fact, they will have been added in batcheseven though you can’t easily tell that after that fact. If you want to confirm what is going on under the covers, you canadd a little bit of extra logging into your program. Add the following lines before thewithBatch statement:

With this extra logging turned on, and the changes made as per the above comment for the logging.properties file, you should seeoutput such as:

We should also note, that any combination of SQL statements can be added to the batch. They don’t all have to beinserting a new row to the same table.

We noted earlier that to avoid SQL injection, we encourage you to use prepared statements, this is achieved using thevariants of methods which take GStrings or a list of extra parameters. Prepared statements can be used in combinationwith batches as shown in the following example:

This provides a much safer option if the data could come from a user such as via a script or a web form. Of course, giventhat a prepared statement is being used, you are limited to a batch of the same SQL operation (insert in our example)to the one table.

When presenting large tables of data to a user, it is often convenient to present information a page ata time. Many of Groovy’s SQL retrieval methods have extra parameters which can be used to select a particularpage of interest. The starting position and page size are specified as integers as shown in the following exampleusingrows:

JDBC metadata can be retrieved in numerous ways. Perhaps the most basic approach is to extract themetadata from any row as shown in the following example which examines the tablename, column names and column type names:

And another slight variant to the previous example, this time also looking at the column label:

Accessing metadata is quite common, so Groovy also provides variants to many of its methods that let yousupply a closure that will be called once with the row metadata in addition to the normal row closurewhich is called for each row. The following example illustrates the two closure variant foreachRow:

Note that our SQL query will only return one row, so we could have equally usedfirstRow for the previous example.

Finally, JDBC also provides metadata per connection (not just for rows). You can also access such metadata from Groovy as shown in this example:

Consult the JavaDoc for your driver to find out what metadata information is available for you to access.

Groovy supports some additional alternative placeholder syntax variants. The GString variantsare typically preferred over these alternatives but the alternatives are useful for Java integrationpurposes and sometimes in templating scenarios where GStrings might already be in heavy use as partof a template. The named parameter variants are much like the String plus list of parameter variants butinstead of having a list of? placeholders followed by a list of parameters, you have one or moreplaceholders having the form:propName or?.propName and a single map, named arguments or adomain object as the parameter. The map or domain object should have a property namedpropNamecorresponding to each supplied placeholder.

Here is an example using the colon form:

And another example using the question mark form:

If the information you need to supply is spread across multiple maps or domain objects you canuse the question mark form with an additional ordinal index as shown here:

The exact syntax for creating a stored procedure or function varies slightly between different databases.For the HSQLDB database we are using, we can create a stored function which returns the initials of all authors in a tableas follows:

We can use a SQLCALL statement to invoke the function using Groovy’s normal SQL retrieval methods.Here is an example usingeachRow.

Here is the code for creating another stored function, this one taking the lastname as a parameter:

We can use the placeholder syntax to specify where the parameter belongs and note the special placeholder position to indicate the result:

Finally, here is a stored procedure with input and output parameters:

To use theCONCAT_NAME stored procedure parameter, we make use of a specialcall method. Any input parameters are simply providedas parameters to the method call. For output parameters, the resulting type must be specified as shown here:

def qry = """SELECT * FROM Author  WHERE (firstname > ?)  AND (lastname < ?)  ORDER BY lastname DESC"""def params = ['Dierk', 'Pragt']def result = sql.rows(qry, params)assert result*.firstname == ['Eric', 'Guillaume', 'Paul']

def authorDS = sql.dataSet('Author')def result = authorDS.findAll{ it.firstname > 'Dierk' }        .findAll{ it.lastname < 'Pragt' }        .sort{ it.lastname }        .reverse()assert result.rows()*.firstname == ['Eric', 'Guillaume', 'Paul']

class Author {    String firstname    String lastname}