As mentioned in the first recipe of this chapter, we are going to reuse the DBUtil class, which contains some helper methods to simplify our work with the database. Create a new Groovy script and add the following import statements before adding the code presented in the next section:

@Grab('commons-dbcp:commons-dbcp:1.4')
import static DBUtil.*
import groovy.sql.Sql
import org.apache.commons.dbcp.BasicDataSource

Do not forget to start the HyperSQL server by calling startServer. As shown in the Creating a database table recipe, pass the -cp argument to the groovy command with the path to the folder with the DBUtil.groovy script in order to execute the script containing the steps of this recipe.

The simplest way to connect to a running instance of a database is by calling the newInstance method on the groovy.sql.Sql class.

Internally groovy.sql.Sql uses the JDBC driver infrastructure to connect to the database and eventually make the SQL calls. The required database driver has to be added to the classpath either with the help of @Grab annotation, which will load any required dependency from the central dependency repository (http://repo1.maven.org/), or in case the driver is not available on a public server, you can use any other standard Java way to add the driver's jar to the classpath.

For example, in order to connect to a MySQL database you need to use the following @Grab annotation:

@Grab('mysql:mysql-connector-java:5.1.21')
import com.mysql.jdbc.Driver

The newInstance method creates a new connection every time it is invoked, so it should be used with caution as you may end up exhausting the database resources. In the Creating a database table recipe, we saw how to call newInstace with a Map. In step 1 of this recipe, we use the standard constructor which accepts four arguments: database connection URL, username, password, and driver class name. In step 2, we pass a java.sql.Connection to the groovy.sql.Sql constructor.

This method of establishing a connection is normally used in conjunction with a connection pool that manages the connections for us, decreasing dramatically the time it takes to open a new connection. It is the caller's responsibility to close the connection after the Sql instance has been used. The preferred approach is to call the close method which will close the connection, but also free any cached resources (see step 4).

The Sql class exposes a constructor that also accepts a javax.sql.Datasource object, as shown in step 3. Datasource objects are normally retrieved via a JNDI invocation from an application server such as Tomcat, Oracle Weblogic, or IBM Websphere. A Datasource object is an abstraction that avoids manually specifying the connection properties in the code. It renders hard coding the driver name or JDBC URL obsolete, making the software more portable.

Moreover, storing the Datasource on an application server increases the maintainability of the software: changes to the database (for example, a new IP address) do not impact the deployed applications but only the server configuration. Lastly, Datasource normally provides connection pooling and transactional services.

In step 3, we create a BasicDataSource object from the time-honored Apache Commons DBCP project. When using JNDI, we would write code similar to the following snippet:

def ctx = new InitialContext()
def ds = (DataSource) ctx.lookup('jdbc/bookDB')

When using the constructor that accepts a Datasource, each operation will use a connection from the Datasource pool and close it when the operation is completed putting it back into the pool.