Acceso a base de datos con SQL y Groovy

Groovy viene con la clase groovy.sql.Sql que nos facilita el acceso a base de datos. Veamos aquí unos ejemplos de cómo acceder a base de datos con Groovy

La conexión con la base de datos

Por supuesto, en nuestro proyecto Groovy necesitamos tener accesible el conector con la base de datos a la que queramos conectarnos. Para el ejemplo que puedes ver completo en DataBase.groovy usamos la base de datos hsqldb que no necesita instalación y nos facilita el ejemplo. El driver de esta base de datos puedes encontrarlo en el repositorio de Maven.

Ya en código Groovy, la conexión con la base de datos podemos establecerla con estas líneas de código

<syntaxhightlight lang="groovy"> def url = 'jdbc:hsqldb:mem:yourDB' def user = 'sa' def password = def driver = 'org.hsqldb.jdbcDriver' def sql = Sql.newInstance(url, user, password, driver) </syntaxhighlight>

Definimos la url de conexión a la bse de datos, el usuario y password de acceso y la clase del driver. Una llamada a Sql.newInstance() pasando todos estos parámetros, nos devuelve una conexión sql con la base de datos.

Cuanto terminemos de usarla, debemos cerrarla con sql.close()

Crear una tabla de base de datos

Una vez que tenemos la conexión sql, el método execute() nos permite ejecutar sentencias 'SQL' en general. En concreto, lo usamos aquí para la creación de una primera tabla para jugar con ella

sql.execute('''CREATE TABLE Author (
               id INTEGER GENERATED BY DEFAULT AS IDENTITY,
               firstname VARCHAR(64),
               lastname  VARCHAR(64));
           ''')

Hemos usado la sintaxis propia de Groovy de triples comillas para poder poner la cadena SQL en varias líneas y que quede más claro. No es más que un CREATE TABLE propio de una base de datos.

Hacer un insert en base de datos

Para hacer un insert podemos usar también execute(), pero tenemos un método executeInsert() que hace lo mismo, pero además nos devuelve los valores auto-creados por la propia base de datos, generalmente suelen ser los identificadores id de las filas que se hayan creado.

Insert usando array de Object

El código puede ser así

for (name in ['Pedro':'Gomez','Juan':'Lopez','Antonio':'Garcia']) {

   def ids  = sql.executeInsert("""
          insert into Author
          values (null,?,?)
          """,
          [name.key, name.value])

   ids.each {println it}
}

Hemos hecho un bucle usando un Map en el que la clave es el nombre de una persona y el valor es el apellido de la misma. Es algo "artificioso", pero es lo más cómodo para generar el ejemplo. En el bucle, tendremos en name.key el nombre de la persona y en name.value el apellido de la persona.

En el executeInsert() van dos parámetros. Uno es la sentencia SQL de insert, poniendo con ? los valores que queremos insertar. null para el campo id, ya que lo autogenerará la base de datos.

El método executeInsert() nos devuelve en ids los identificadores generados por la base de datos, así que después de cada insert, los sacamos por pantalla para verificar que es así. La salida de este programa es

[0]
[1]
[2]

Cada insert devuelve un array de valores generados, en este caso, cada array sólo tiene un valor generado, el de la columna id.

Groovy, como siempre, nos ofrece muchas formas de hacer lo mismo. El método executeInsert() no va a ser menos.

Insert usando GString

En vez de ?, podemos poner un GString de Groovy con sus valores embebidos, algo como esto

for (name in ['Pedro':'Gomez','Juan':'Lopez','Antonio':'Garcia']) {

   sql.executeInsert("""
          insert into Author
          values (null,$name.key,$name.value)
          """)
}

En vez de ?, hemos puesto $name.key y $name.value que es donde tenemos guardado el nombre y apellido. Esta forma es tan segura desde el punto de vista de inyección SQL como puede serlo la opción de los ?. Groovy se encarga por debajo de hacer la inserción de forma segura.

Insert usando named parameters

En vez de ?, podemos usar nombres que nos inventemos precedidos de :. Luego, como parámetro para indicar los valores, podemos hacer varias cosas. Una de ellas es pasar un Map en el que las claves sean los nombres que nos hemos inventado y los valores los valores que queremos insertar. También podemos usar una clase Groovy cuyos atributos sean los nombres que nos hemos inventando y cuyos valores sean los que queramos insertar. Veamos ambos ejemplos

Map person = [name:'Pedro', surname:'Gomez']

ids  = sql.executeInsert("""
   insert into Author
   values (null, :name, :surname)
   """, person)

o con una clase

// La definición de la clase
class Person {
    String name
    String surname
}

...

// Una instancia con valores
def person = new Person(name:'Pedro', surname:'Gomez')

// Y la inserción
ids  = sql.executeInsert("""
   insert into Author
   values (null, :name, :surname)
   """, person)

Consulta a la base de datos

Para hacer una consulta a base de datos, tenemos el método eachRow(), que nos irá dando directamente cada una de las filas. Veamos el ejemplo

sql.eachRow("select * from Author") { row ->
   println "${row.id} ${row.firstname} ${row.lastname}"
}

Simplemente pasamos como primer parámetro el select de consulta y como segundo parámetro una Closure donde iremos recibiendo cada una de las filas en la variable row. En row.nombre-columna, tendremos el valor de cada una de las columnas de la base de datos.

Existe también un método rows() y un método firstRow() que nos devuelven directamente en el return los resultados, sin necesidad de pasar una closures para ir tratando cada fila una a una.

• rows es útil para obtener de golpe todos los resultados, siempre que no sean muchos para no cargar la memoria.
• firstRow es útil si sabemos que nuestra consulta sólo devuelve un resultado, o sólo nos interesa el primero.

Veamos un pequeño código de ejemplo con firstRow para ver el tipo de resultado que devuelve.

<sytanxhighlight lang="groovy"> GroovyRowResult row = sql.firstRow("select * from Author")

// GroovyRowResult implementa Map println "$row.ID $row.FIRSTNAME $row.LASTNAME" </syntaxhighlight>

No haría falta poner el tipo devuelto, bastaría con def al estilo groovy, pero lo ponemos para que quede más claro qué nos devuelve. Como GroovyRowResult implementa la interfaz Map, se pueden obtener los resultados simplemente llamando a los atributos con el nombre de la columna de base de datos, como se muestra en el ejemplo.

rows devolvería un List<GroovyRowResultSet>

Por supuesto, al igual que con executeInsert() tenemos varias variantes del método que nos permiten pasar parámetros a la consulta, bien en forma de GString con parámetros embebidos, bien en forma de ?, bien en forma de nombres que nos inventemos precedidos de :. No vamos a detallarlo porque es muy similar y no aporta nada nuevo. Vamos sin embargo a ver otros puntos interesantes de las consultas a base de datos.

Paginado de las consultas

Hay métodos que permiten marcar un offset de inicio y un número de resultados deseados, que nos permitiría fácilmente hacer consultas para presentar los resultados al operador de forma paginada. El siguiente código muestra cómo

sql.eachRow("select * from Author", 2, 4) { row ->
   println "${row.id} ${row.firstname} ${row.lastname}"
}

Este código muestra cuatro resultados de base de datos a partir del resultado número dos, incluido.

Metadatos de la base de datos

Hay métodos a los que se les puede pasar una closure en la que recibiremos, justo después de ejecutarse la consulta y antes de empezar a tratar los resultados, los metadatos de la consulta que hemos realizado, como el nombre de las columnas, etc. Este tipo de métodos puede ser útil, por ejemplo, para sacar un listado de resultados en el que pongamos en la cabecera el nombre de las columnas. El siguiente código muestra cómo se podría hacer

// Una closure para escribir la cabecera de la lista de resultados
def metaClosure = { ResultSetMetaData meta ->
    (1..meta.columnCount).each { index ->
        print meta.getColumnLabel(index).padRight(20)
    }
    println()
}

// La consulta, pasando la closure anterior y otra para tratar cada fila.
sql.eachRow("select * from Author", metaClosure) { row ->
    println "${row.id.toString().padRight(20)}${row.firstname.padRight(20)}${row.lastname.padRight(20)}"
}

La closure metaClosure recibe los metadatos del resultado de la consulta. Por supuesto, en la closure no es necesario poner el tipo ResultSetMetaData, pero lo ponemos para que quede más claro qué estamos recibiendo y tratando. Luego, simplemente un bucle desde 1 hasta el número de columnas que tenga el resultado de la consulta meta.columnCount e ir poniendo los nombres en pantalla obtenidos con meta.getColumnLabel(index)

Luego en la consulta sql.eachRow(), se añade una segunda closure igual que antes, sacando por pantalla cada fila.

Un detalle nada más. para que queden las columnas correctamente alineadas, usamos el método padRight que groovy añade a la clase String de java. Como $row.id es un Integer, sólo en este caso es necesario llamar previamente a toString()

Cerrar la conexión

Y no te olvides de cerrar la conexión cuando acabes

sql.close()

Enlaces

• Hay un buen tutorial en la misma api de la clase Sql
• Más extenso en la documentación oficial de Groovy
• Al estilo Hibernate existe GORM (Groovy Object Relational Mapping), pero debe usarse en el entorno de Grails, o bien puede usarse sin Grails, pero con Spring.