Groovy and database

related posts : BDD with groovy and Spock

1. Intro

Groovy can help to implement a lot of things. One of them - it can be use for database testing -  Grooovy can make it very simple and easy. In this post - example of how it can used for testing some features which were implemented using Oracle DB : I had to test views and triggers which were created for views. For that I had to deploy to DB scripts. Before deploying i had to re-point views to tests views/tables. And, of course, run some tests with data modification.

2. DB connection with bootstrap of  DB connection library

Before connecting to DB, we have to load driver class. For this purpose we don't need any classpath settings - we can just load needed class in our code :

def jarFile = new File("C://MyProject/java/lib/global/oracle/ojdbc6.jar");
this.class.classLoader.rootLoader.addURL(jarFile.toURI().toURL());

And then the class is loaded - we can connect to DB:

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1522:MYDB", "MYUSERNAME", "MYPASSWORD","oracle.jdbc.driver.OracleDriver")

3. Test the connection 

To test the connection there are a lot of options, I decided just to get a time from Oracle :

def testConnection(sql) {
   sql.eachRow("SELECT sysdate as dt FROM dual") {
     println "current date : ${it.dt} "
   }
}

- if this procedure is returning time - everything is working and we can move forward. 

4. Execute statement

On previous step we executed SELECT statement. For executing just SQL with no return (for example DDL operation) we can use execute method :

sql.execute("create table TEST_TABLE as select * from "+sourceTable+" where 0=1")

Also very often i needed to execute SQL operation in "silent mode" - without any error notification. For example, I have to DROP the table, but if doesn't exist - I will have an exception. But I don't needed - if table not exists - that's exactly what i want! So I wrapped execute method with try/catch :

def executeSilent(sql, statement) {
  //println statement
  try {     
     sql.execute(statement) 
  } catch(Exception e) {
  }
}

Now we can make some table modification this way :

  sql.eachRow("select * from user_tab_columns where table_name='"+sourceTable+"'") {
   def modifySql="ALTER TABLE TEST_TABLE MODIFY("+it.column_name+"  NULL)"
   executeSilent(sql, modifySql)         
  }

5. Deploy script to DB 

Also I needed before every test deploy to DB script. That is also simple for implementing : I used symbol "/" as a "block end" in SQL scripts, so, to split file content for SQL statements for execution them "one by one" I used fileContent.split("/") method  :

def deploySqlScript(sql, filePath, tableName, viewName) {
  def fileContent = new File(filePath).text
  fileContent=fileContent.toUpperCase()
  fileContent=fileContent.replace(viewName, "V_TEST_TABLE")  
  fileContent=fileContent.replace(tableName, "TEST_VIEW")  
  for (String s:fileContent.split("/")) {
    try {
       sql.execute(s.trim())
    } catch (Exception e) {
         println "Failed to execute:" +s.trim()+" Exception:"+e.toString()
    }
  }
}

6. Simple test

For my tests, I needed to compare count of records, so I created function :

def selectDataCount(sql, tableName, id) {
  def selectSql="select count(*) as cnt from "+tableName+" where id="+id; 
  def result=0;
  sql.eachRow(selectSql) {
     result=it.cnt
  }
  return result;
}

And for checking, I created analog of "ASSERT" :

def checkCount(sql, tableName, id, expectedCnt) {
   def actualCnt=selectDataCount(sql, tableName, id)
   if (expectedCnt!=actualCnt) {
      println "!!![Failed] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   } else {
      println "[OK] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   }
}

After that, I was able to write my tests : 

def runTest(sql, tableName) {

  def id=1

  deleteData(sql, tableName)   

  println "\ninsert first row"

  insertData(sql, tableName, id)  

  checkCount(sql, tableName, id, 1)

  println "\ncheck for delete:"

  deleteData(sql, tableName, id)

  checkCount(sql, tableName, id, 0)  

.....................................................................

}

7. Full source

def jarFile = new File("C://MyProject/java/lib/oracle/ojdbc6.jar");
this.class.classLoader.rootLoader.addURL(jarFile.toURI().toURL());

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:oracle:thin:@localhost:1522:MYDB", "MYUSERNAME", "MYPASSWORD","oracle.jdbc.driver.OracleDriver")

def testConnection(sql) {
   sql.eachRow("SELECT sysdate as dt FROM dual") {
     println "current date : ${it.dt} "
   }
}

def executeSilent(sql, statement) {
  //println statement
  try {     
     sql.execute(statement) 
  } catch(Exception e) {
  }
}

def prepareTestTable(sql,sourceTable) {
   executeSilent(sql, "drop table TEST_TABLE") 
   executeSilent(sql, "drop view  V_TEST_TABLE") 

   // create test table 
   sql.execute("create table TEST_TABLE as select * from "+sourceTable+" where 0=1")  

   // make colums "nullable"
   sql.eachRow("select * from user_tab_columns where table_name='"+sourceTable+"'") {
   def modifySql="ALTER TABLE TEST_TABLE MODIFY("+it.column_name+"  NULL)"
   executeSilent(sql, modifySql)         
  }

  // create unique index on SERVICE_ID, SERVICE_VERSION
  sql.execute("CREATE UNIQUE INDEX IDX_TEST_MIG_SRV_UNQ ON TEST_MIG (SERVICE_ID, SERVICE_VERSION)")
  println "Test table for $sourceTable is ready"
}

def deploySqlScript(sql, filePath, tableName, viewName) {
  def fileContent = new File(filePath).text
  fileContent=fileContent.toUpperCase()
  fileContent=fileContent.replace(viewName, "V_TEST_TABLE")  
  fileContent=fileContent.replace(tableName, "TEST_VIEW")  
  for (String s:fileContent.split("/")) {
    try {
       sql.execute(s.trim())
    } catch (Exception e) {
         println "Failed to execute:" +s.trim()+" Exception:"+e.toString()
    }
  }
}

def deleteData(sql, tableName, id=null) {
   def delSql="delete from "+tableName;
   if (id!=null) {
     delSql+=" where id="+id;
   }  
   sql.executeUpdate(delSql);
}

def insertData(sql, tableName, id) {    
    def insertStr="insert into "+tableName+" (id) values ("+id+")"
    sql.execute(insertStr)
}

def selectDataCount(sql, tableName, id) {
  def selectSql="select count(*) as cnt from "+tableName+" where id="+id; 
  def result=0;
  sql.eachRow(selectSql) {
     result=it.cnt
  }
  return result;
}

def checkCount(sql, tableName, id, expectedCnt) {
   def actualCnt=selectDataCount(sql, tableName, id)
   if (expectedCnt!=actualCnt) {
      println "!!![Failed] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   } else {
      println "[OK] for $tableName, $id. Expected count:$expectedCnt actual count:$actualCnt"
   }
}

def runTest(sql, tableName) {
  def id=1
  deleteData(sql, tableName)   

  println "\ninsert first row"
  insertData(sql, tableName, id)  
  checkCount(sql, tableName, id, 1)

  println "\ncheck for delete:"
  deleteData(sql, tableName, id)
  checkCount(sql, tableName, id, 0)  
...........................................................................................................
}

def prepareAndRunTest(sql, tableName, viewName, scriptPath) {
   prepareTestTable(sql, tableName)
   deploySqlScript(sql, scriptPath, tableName, viewName)
   runTest(sql, "V_TEST_VIEW")  
}

testConnection(sql) 
//prepareAndRunTest(sql, "TABLE1", "V_TABLE1", "scripts/V_TABLE1.sql")
//prepareAndRunTest(sql, "TABLE2", "V_TABLE2", "scripts/V_TABLE2.sql")
........................................................................................................

Spring data simple example

1.intro

Very often, on DB level there are a lot of classes(DAOs) with similar methods:

To avoid that  - there are  lot of different strategies. In my blog a described some of them : 1. using genericDao    2. EasyJDBC tool
Spring Data - another approach  : you just  have to create an interface (which can be extended from  default CRUD repository) and leave it without implementation. String will generate implementation for you!

2.project structure

Everything is pretty simple : one entity (Category), one dao-repository(CategoryRepository) interface, one config class for spring and application runner(class with main procedure):

3. project pom.xml

Just 3 dependencies here :  spring data, persistence api for annotation support, H2 database.
 

<?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>com.demien</groupId>
    <artifactId>sdata</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>

    <dependency>
        <groupId>org.springframework.data</groupId>
        <artifactId>spring-data-jpa</artifactId>
        <version>1.3.4.RELEASE</version>
    </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>4.2.5.Final</version>
        </dependency>

        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib-nodep</artifactId>
            <version>2.2</version>
        </dependency>

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.3.173</version>
        </dependency>

    </dependencies>

</project>

4. app.properties

Properties for H2 DB and hibernate
 

#DB properties:db.driver=org.h2.Driverdb.url=jdbc:h2:~/testdb.username=sadb.password=sa
#Hibernate Configuration:db.hibernate.dialect=org.hibernate.dialect.H2Dialectdb.hibernate.show_sql=falsedb.entitymanager.packages.to.scan=com.demien.sdata.domaindb.hibernate.hbm2ddl.auto = create

 

5. test entity

It's a very simple entity with persistence api annotations. 

package com.demien.sdata.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity(name = "CATEGORY")
public class Category {

    @Id    @Column(name = "CATEGORY_ID")
    private Long categoryId;

    @Column(name="CATEGORY_NAME")
    private String categoryName;

    @Column(name = "CATEGORY_DESCRIPTION")
    private String categoryDescription;

    @Column(name="PARENT_CATEGORY_ID")
    private Long parentCategoryId;

    public Category() {}

    public Long getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Long categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public String getCategoryDescription() {
        return categoryDescription;
    }

    public void setCategoryDescription(String categoryDescription) {
        this.categoryDescription = categoryDescription;
    }

    public Long getParentCategoryId() {
        return parentCategoryId;
    }

    public void setParentCategoryId(Long parentCategoryId) {
        this.parentCategoryId = parentCategoryId;
    }

    @Override    public String toString() {
        return "Category{" +
                "categoryId=" + categoryId +
                ", categoryName='" + categoryName + '\'' +
                '}';
    }
}

6. repository

As I mention before, we just have to create interface - implementation will be created by Spring. Interface extended from JpaRepository - it has definition of all base db methods (like save, find, delete...). I added only one custom method to interface, and again : it's just a method without implementation. 

package com.demien.sdata.repository;

import com.demien.sdata.domain.Category;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository(value = "CategoryRepository")
public interface CategoryRepository extends JpaRepository<Category, Long> {

    @Query("FROM CATEGORY where CATEGORY_NAME like %?1% ")
    List<Category> findByPattern(String pattern);
}

7. spring config

XML configs are not very popular at modern time, so I used XML-less Spring configuration. 

package com.demien.sdata;

import org.hibernate.ejb.HibernatePersistence;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Properties;

@Configuration@EnableTransactionManagement@ComponentScan("com.demien.sdata")
@PropertySource("classpath:app.properties")
@EnableJpaRepositories("com.demien.sdata.repository")
public class AppConfig {


private static final String PROP_DATABASE_DRIVER = "db.driver";
private static final String PROP_DATABASE_PASSWORD = "db.password";
private static final String PROP_DATABASE_URL = "db.url";
private static final String PROP_DATABASE_USERNAME = "db.username";
private static final String PROP_HIBERNATE_DIALECT = "db.hibernate.dialect";
private static final String PROP_HIBERNATE_SHOW_SQL = "db.hibernate.show_sql";
private static final String PROP_ENTITYMANAGER_PACKAGES_TO_SCAN = "db.entitymanager.packages.to.scan";
private static final String PROP_HIBERNATE_HBM2DDL_AUTO = "db.hibernate.hbm2ddl.auto";

@Resourceprivate Environment env;

    @Bean    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();

        dataSource.setDriverClassName(env.getRequiredProperty(PROP_DATABASE_DRIVER));
        dataSource.setUrl(env.getRequiredProperty(PROP_DATABASE_URL));
        dataSource.setUsername(env.getRequiredProperty(PROP_DATABASE_USERNAME));
        dataSource.setPassword(env.getRequiredProperty(PROP_DATABASE_PASSWORD));

        return dataSource;
    }

    @Bean    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource());
        entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistence.class);
        entityManagerFactoryBean.setPackagesToScan(env.getRequiredProperty(PROP_ENTITYMANAGER_PACKAGES_TO_SCAN));

        entityManagerFactoryBean.setJpaProperties(getHibernateProperties());

        return entityManagerFactoryBean;
    }

    @Bean    public JpaTransactionManager transactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(entityManagerFactory().getObject());

        return transactionManager;
    }

    private Properties getHibernateProperties() {
        Properties properties = new Properties();
        properties.put("hibernate.dialect", env.getRequiredProperty(PROP_HIBERNATE_DIALECT));
        properties.put("hibernate.show_sql", env.getRequiredProperty(PROP_HIBERNATE_SHOW_SQL));
        properties.put("hibernate.hbm2ddl.auto", env.getRequiredProperty(PROP_HIBERNATE_HBM2DDL_AUTO));

        return properties;
    }
}

8. main runner

I'm starting Spring context and testing main  repository methods here

package com.demien.sdata;

import com.demien.sdata.domain.Category;
import com.demien.sdata.repository.CategoryRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import java.util.Arrays;

/** * Created by dmitry on 05.10.15. */public class App {




    private static ApplicationContext applicationContext;
    public static void main(String args[]) {
        applicationContext=new AnnotationConfigApplicationContext(AppConfig.class);
        new App().test();
    }

    public void test() {
        CategoryRepository categoryRepository=(CategoryRepository)applicationContext.getBean("CategoryRepository");

        Category  category=new Category();
        category.setCategoryId(1l);
        category.setCategoryName("Test 1");
        categoryRepository.save(category);

        category=new Category();
        category.setCategoryId(2l);
        category.setCategoryName("Test 2");

        categoryRepository.save(category);

        System.out.println("\n categoryRepository.findAll()");
        System.out.println(Arrays.toString(categoryRepository.findAll().toArray()));

        System.out.println("\n categoryRepository.findOne(1l)");
        System.out.println(categoryRepository.findOne(1l));


        System.out.println("\n categoryRepository.findByPattern(2)");
        System.out.println(Arrays.toString(categoryRepository.findByPattern("2").toArray()));


        category.setCategoryName("new name");
        categoryRepository.save(category);
        System.out.println("\n UPDATE to new name for element with id=2");
        System.out.println(Arrays.toString(categoryRepository.findAll().toArray()));

        categoryRepository.delete(category);
        System.out.println("\n DELETE of element with id=2");
        System.out.println(Arrays.toString(categoryRepository.findAll().toArray()));


    }
}

9. results 

Everything is working as expected:


 categoryRepository.findAll()
[Category{categoryId=1, categoryName='Test 1'}, Category{categoryId=2, categoryName='Test 2'}]

 categoryRepository.findOne(1l)
Category{categoryId=1, categoryName='Test 1'}

 categoryRepository.findByPattern(2)
[Category{categoryId=2, categoryName='Test 2'}]

 UPDATE to new name for element with id=2
[Category{categoryId=1, categoryName='Test 1'}, Category{categoryId=2, categoryName='new name'}]

 DELETE of element with id=2
[Category{categoryId=1, categoryName='Test 1'}]

Process finished with exit code 0

10. the end

Spring data - very good option to be used on DAO level of application.
Full source code can be downloaded from here.

Spring Data JPA - Reference Documentation