Groovy

Groovy SQL Example

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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<?xml version="1.0" encoding="UTF-8"?>
    <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
  • jpaVendorAdapterHibernateJpaVendorAdapter with GenerateDdl enabled
  • transactionManager – The database transaction manager
  • entityManagerFactory – LocalContainerEntityManagerFactoryBean to scan Entity classes in jcg.zheng.demo.groovysql package

GroovySpringApplication.java

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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

Mary Zheng

Mary has graduated from Mechanical Engineering department at ShangHai JiaoTong University. She also holds a Master degree in Computer Science from Webster University. During her studies she has been involved with a large number of projects ranging from programming and software engineering. She works as a senior Software Engineer in the telecommunications sector where she acts as a leader and works with others to design, implement, and monitor the software solution.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button