Groovy SQL Example

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: Sign up

Groovy SQL Example

Thank you!

Thank you!

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