Groovy SQL Example

Mary ZhengApril 23rd, 2018Last Updated: March 31st, 2019

0 1,383 9 minutes read

1. Groovy SQL – Introduction

Apache Groovy (Groovy) is an object-oriented dynamic programming language for the Java platform. It is dynamically compiled to the Java Virtual Machine (JVM) bytecode, and inter-operates with other Java source codes and libraries. Groovy is written in Java and was first released in 2007. Groovy SQL module provides a higher-level abstraction on JDBC technology.

The Groovy SQL API supports a wide variety of databases: HSQLDB, Oracle, SQL Server, MySQL, MongoDB. In this example, I will create a Spring boot application which utilizes Groovy SQL to manage data in an H2 database.

2. Technologies Used

The example code in this article was built and run using:

Java 1.8.101 (1.8.x will do fine)
Maven 3.3.9 (3.3.x will do fine)
Eclipse Mars (Any Java IDE would work)
Spring boot 1.5.10.RELEASE
Groovy 2.4

3. Spring-boot Application

Spring is the greatest tool to manage resources. In this example, we use Spring to manage database connections and transactions.

The easiest way to generate a Spring-boot application is via the Spring starter tool. Please check my other article here for more details. A maven project will be generated and downloaded to your workstation. Import it into your Eclipse work space. You should have no errors on building and running it. The generated project has a Java source folder. We will add two folders: src/main/groovy and src/test/groovy. Then we will include them as the build resources.

3.1 Dependencies

We will add Groovy library as a dependency in the generated pom.xml.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>jcg.zheng.demo</groupId>
    <artifactId>groovysql</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>groovysql</name>
    <description>Demo project for Groovy SQL</description>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.10.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.codehaus.groovy</groupId>
            <artifactId>groovy-all</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
            </resource>
            <resource>
                <directory>src/main/groovy</directory>
            </resource>
            <resource>
                <directory>src/test/groovy</directory>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

3.2 Groovy Spring Application

We will add several Spring beans in the generated GroovySpringApplication class to manage the data resource:

dataSource – In-memory H2 database
jpaVendorAdapter – HibernateJpaVendorAdapter with GenerateDdl enabled
transactionManager – The database transaction manager
entityManagerFactory – LocalContainerEntityManagerFactoryBean to scan Entity classes in jcg.zheng.demo.groovysql package

GroovySpringApplication.java

package jcg.zheng.demo.groovysql;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.JpaVendorAdapter;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.Database;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
@SpringBootApplication
public class GroovySpringApplication {
    DataSource dataSource() {
        return new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).build();
    }
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        HibernateJpaVendorAdapter bean = new HibernateJpaVendorAdapter();
        bean.setDatabase(Database.H2);
        bean.setGenerateDdl(true);  
        return bean;
    }
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource,
            JpaVendorAdapter jpaVendorAdapter) {
        LocalContainerEntityManagerFactoryBean bean = new LocalContainerEntityManagerFactoryBean();
        bean.setDataSource(dataSource);
        bean.setJpaVendorAdapter(jpaVendorAdapter);
        bean.setPackagesToScan("jcg.zheng.demo.groovysql");
        return bean;
    }
    @Bean
    public JpaTransactionManager transactionManager(EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(GroovySpringApplication.class, args);
    }
}

4. Groovy SQL

Groovy SQL provides adequate APIs to manage the data in the database. In this step, we will demonstrate the usages of several common APIs:

eachRow – Perform the SQL query with the given Closure. It will hold the database transaction until the operations inside of the Closure completes.
rows – Return rows of results set for the given SQL query. The connection is released when the rows return.
firstRow – Return the first row of the result set for the SQL query.
execute – Execute the SQL statement.
executeInsert – Execute the INSERT SQL statement and return any auto-generated value.
executeUpdate – Execute theUPDATE SQL statement and return the updated row count.
withBatch – Perform the Closure within a batch for a better performance.

4.1 Entities

We will create a User class and annotate it with @Entity which has attributes: ID, name, and autoGeneratedId. The autoGeneratedId is used to demonstrate the executeInsert method later.

User.groovy

package jcg.zheng.demo.groovysql.entity
import javax.persistence.CascadeType
import javax.persistence.Entity
import javax.persistence.FetchType
import javax.persistence.GeneratedValue
import javax.persistence.GenerationType
import javax.persistence.Id
import javax.persistence.OneToMany
import javax.persistence.Table
import groovy.transform.Canonical
@Canonical
@Entity
@Table(name="USER")
class User {
    String id
    String name
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    Long autoGeneratedId
    
}

4.2 CRUD Operations

We will build a Spring bean UserDao to create, update, delete, and find a user via Groovy SQL module. We will use parameterized query to avoid the SQL injection security vulnerability.

UserDao.groovy

package jcg.zheng.demo.groovysql.component
import javax.annotation.PostConstruct
import javax.annotation.PreDestroy
import javax.sql.DataSource
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Component
import org.springframework.transaction.annotation.Transactional
import groovy.sql.Sql
import jcg.zheng.demo.groovysql.entity.User
@Component
@Transactional
class UserDao {
    private Sql userSql
    @Autowired
    DataSource datasoure
    def selectNamedSqlStr = "SELECT * FROM User WHERE id=:id "
    def insertNamedSqlstr = "INSERT INTO User (ID, name) VALUES ( :id, :name ) "
    def deleteNamedSqlStr = "DELETE FROM User WHERE  ID = :id "
    def updateNamedSqlStr = "UPDATE User set name = :name WHERE  ID = :id "
    def searchNamedSqlStr = "SELECT * FROM User WHERE name like :name "
    @PostConstruct
    void setUp(){
        userSql = Sql.newInstance(datasoure)
    }
    @PreDestroy
    void cleanup(){
        userSql.close()
    }
    List search(String name){
        List values = []
        Map params = [name:"%" + name + "%"]
        
        userSql.rows(searchNamedSqlStr, params).each { row ->
            values.add( mapUser(row) )
        }
        return values
    }
    List findAll(){
        List values = []
        userSql.eachRow("SELECT * from User") { row ->
            values.add(mapUser(row))
        }
        
        return values
    }
    
    User mapUser(def row){
        User user = new User(id: row.id, name: row.name, autoGeneratedId: row.autoGeneratedId)
    }
    void deleteAll(){
        userSql.execute("DELETE from User ")
    }
    User findById(String userId){
        User ret = null
        Map params = [id:userId]
        def row = userSql.firstRow(selectNamedSqlStr, params)
        if( row != null) {
            ret = new User(id: row.id, name: row.name, autoGeneratedId: row.autoGeneratedId )
        }
        return ret
    }
    void  create(User user){
        Map params = [id:user.id, name:user.name]
        def ret = userSql.executeInsert(insertNamedSqlstr, params)
        user.setAutoGeneratedId(ret[0][0])
    }
    
    int delete(String userId){
        Map params = [id:userId]
        userSql.execute(deleteNamedSqlStr, params)
        return userSql.updateCount
    }
    int update(User user){
        Map params = [id:user.id, name:user.name]
        userSql.executeUpdate(updateNamedSqlStr, params)
    }
}

line 43: rows usage
line 52: eachRow usage
line 64, 88: execute usages
line 71 : firstRow usage
line 81, 83: executeInsert usage
line 94: executeUpdate usage

4.3 Batch Operations

We will create a Spring Bean UserBatchDao to create and delete a list of users in a batch model for better performance.

UserBatchDao.groovy

package jcg.zheng.demo.groovysql.component
import java.util.List
import javax.annotation.PostConstruct
import javax.annotation.PreDestroy
import javax.sql.DataSource
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.beans.factory.annotation.Value
import org.springframework.stereotype.Component
import org.springframework.transaction.annotation.Transactional
import groovy.sql.Sql
import jcg.zheng.demo.groovysql.entity.User
@Component
@Transactional
class UserBatchDao {
    private Sql userSql
    @Autowired
    DataSource datasoure;
    @PostConstruct
    void setUp(){
        userSql = Sql.newInstance(datasoure)
    }
    @PreDestroy
    void cleanup(){
        userSql.close()
    }
    void batchInsert(List users){
        def prepareParameterizedQuery = 'INSERT INTO User (ID, name) VALUES (?, ?)'
        userSql.withBatch(5, prepareParameterizedQuery) { preparedQuery ->
            users.each{ user->
                preparedQuery.addBatch(user.id, user.name)
            }
        }
    }
    void batchDelete(List users){
        userSql.withBatch( { query ->
            users.each{ user->
                query.addBatch("DELETE FROM User WHERE ID = '${user.id}' ")
            }
        })
    }
    
}

Note: line 37, 39, 45, 47: withBatch usages

5. Unit Test Classes

We will demonstrate the Groovy SQL APIs via the unit test classes.

5.1 BaseTest

We will create a base test class for the common data and methods.

BaseTest.groovy

package jcg.zheng.demo.groovysql.component
import static org.junit.Assert.*
import java.util.List
import jcg.zheng.demo.groovysql.entity.User
import org.junit.After
import org.junit.Before
import org.springframework.beans.factory.annotation.Autowired
class BaseTest {
    List users
    @Autowired
    UserDao userDao
    @Before
    public void setup(){
        users =[
            new User("1", "AMary"),
            new User("2", "AShan"),
            new User("3", "AZheng"),
            new User("4", "AZhang"),
            new User("5", "ALee"),
            new User("6", "AJohnson"),
            new User("7", "AShan1"),
            new User("8", "AZheng1"),
            new User("9", "AZhang1"),
            new User("10", "ALee1")         
        ]
        for( int i = 0; i < 100; i++){
            User user = new User("${i}", "dummyName{${i}")
            users.add(user)
        }
    }
    @After
    public void cleanup(){
        userDao.deleteAll()
    }
    
}

5.2 UserDaoTest

We will create a test class to create, read, update, and delete users.

UserDaoTest.groovy

package jcg.zheng.demo.groovysql.component
import static org.junit.Assert.*
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import org.springframework.transaction.annotation.Transactional
import jcg.zheng.demo.groovyspring.model.*
import jcg.zheng.demo.groovysql.entity.User
@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
class UserDaoTest extends BaseTest {
    @Test
    public void test_get() {
        List users = userDao.findAll()
        assertTrue(users.empty)
    }
    @Test
    void test_create_get() {
        User user0 = new User("id001", "Mary")
        userDao.create(user0)
        assertTrue(user0.autoGeneratedId > 0)
        User user = userDao.findById("id001")
        assertNotNull(user)
        assertEquals(user0, user)
    }
    @Test
    void test_create_delete() {
        User user0 = new User("id001", "Mary")
        userDao.create(user0)
        int deletedCount = userDao.delete(user0.id)
        assertEquals(1, deletedCount)
        User user = userDao.findById(user0.id)
        assertNull(user)
    }
    @Test
    public void test_create_update_get() {
        User userBefore = new User("id001", "Mary")
        userDao.create(userBefore)
        userBefore.setName("Mary2")
        def updateCount = userDao.update(userBefore)
        assertEquals(1, updateCount)
        User userAfter = userDao.findById("id001")
        assertEquals("Mary2", userAfter.name)
    }
}

5.3 UserBatchDaoTest

We will create a test class to compare the performance between a batch insert of 110 users to non-batch inserts. We will also compare the performance between rows and eachRow on retrieving data.

UserBatchDaoTest.groovy

package jcg.zheng.demo.groovysql.component
import static org.junit.Assert.*
import org.junit.After
import org.junit.Before
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.test.context.junit4.SpringRunner
import jcg.zheng.demo.groovyspring.model.*
import jcg.zheng.demo.groovysql.entity.User
@RunWith(SpringRunner.class)
@SpringBootTest
class UserBatchDaoTest extends BaseTest {
    
    @Autowired
    private UserBatchDao batchDao
    @Test
    public void test_batchInsert() {
        batchDao.batchInsert(users)
        List users = userDao.findAll()
        assertFalse(users.empty)
        assertEquals(110, users.size())
    }
    @Test
    public void test_inserts(){
        users.each({user -> userDao.create(user)})
        List users = userDao.findAll()
        assertFalse(users.empty)
        assertEquals(110, users.size())
    }
    @Test
    public void test_batchDelete() {
        batchDao.batchInsert(users)
        batchDao.batchDelete(users)
        List users = userDao.findAll()
        assertTrue(users.empty)
    }
    
    @Test
    public void test_search_found() {
        batchDao.batchInsert(users)
        
        List foundUser = userDao.search("Zh")
        assertEquals(4, foundUser.size())
    }
    
    @Test
    public void test_search_not_found() {
        batchDao.batchInsert(users)
        
        List foundUser = userDao.search("bad")
        assertEquals(0, foundUser.size())
    }
    
    @Test
    public void test_all_rows() {
        batchDao.batchInsert(users)
        
        List foundUser = userDao.search("A")
        assertEquals(10, foundUser.size())
    }
    
    @Test
    public void test_all_eachrow() {
        batchDao.batchInsert(users)
        
        List foundUser = userDao.findAll()
        assertEquals(110, foundUser.size())
    }   
    
}

As you see from the test results below, the batch to insert users took 47 milliseconds and the insert one user at a time took 94 milliseconds. The batch operation is faster than the non-batch operation. There is a tiny performance difference between rows and eachRow.

Groovy SQL - unit tests results — Groovy SQL unit tests results

6. Summary

In this example, we built a Spring Boot application to demonstrate how to use Groovy SQL to manage the data in H2 database. Groovy SQL API is very simple to use. There are other JDBC tools available. Please check out my other articles for Spring data JPA and Quesydsl.

7. Download the Source Code

This example consists of a Spring boot application which demonstrates how to use Groovy SQL API to manage data stored in a H2 database.

Download
You can download the full source code of this example here: Groovy SQL Example

Groovy SQL Example

1. Groovy SQL – Introduction

2. Technologies Used

3. Spring-boot Application

3.1 Dependencies

3.2 Groovy Spring Application

4. Groovy SQL

4.1 Entities

4.2 CRUD Operations

4.3 Batch Operations

Thank you!

5. Unit Test Classes

5.1 BaseTest

5.2 UserDaoTest

5.3 UserBatchDaoTest

6. Summary

7. Download the Source Code

Thank you!

Mary Zheng

Thank you!

1. Groovy SQL – Introduction

2. Technologies Used

3. Spring-boot Application

3.1 Dependencies

3.2 Groovy Spring Application

4. Groovy SQL

4.1 Entities

4.2 CRUD Operations

4.3 Batch Operations

Thank you!

5. Unit Test Classes

5.1 BaseTest

5.2 UserDaoTest

5.3 UserBatchDaoTest

6. Summary

7. Download the Source Code

Thank you!

Related Articles

Thank you!