This recipe is about the various approaches we can use to connect to a database using Groovy and the groovy.sql.Sql
class that we briefly encountered in the Creating a database table recipe.
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.
newInstance
method is written as follows:Sql sql = Sql.newInstance( 'jdbc:hsqldb:hsql://localhost/cookingdb', 'sa', '', 'org.hsqldb.jdbcDriver' ) println 'connected with connection data: ok'
Sql reusedConnection = new Sql(sql.createConnection()) println 'connected with reused connection: ok'
Sql
class is by passing a javax.sql.DataSource
instance to the constructor:@Grab('commons-dbcp:commons-dbcp:1.4') import org.apache.commons.dbcp.BasicDataSource def ds = new BasicDataSource() ds.with { driverClassName = 'org.hsqldb.jdbcDriver' password = '' username = 'sa' url = 'jdbc:hsqldb:hsql://localhost/cookingdb' } Sql sql3 = new Sql(ds) println 'connected with datasource: ok'
reusedConnection.close()
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.