SpringBoot with LiquiBase using Groovy Build by Gradle

SpringBoot with LiquiBase using Groovy

Build by Gradle

1. Overview

Database schema migration is performed on a database whenever it is necessary to update/revert changes in the database or need to migrate the database from one version to other. There are two major migration tools: Liquibase, Flyway.

In this tutorial, we will go with the Liquibase. The Liquibase is open source tool for database schema migration or database version controlling. It supports most of the major databases and different type of format for schema change file like XML, SQL, YAML, JSON. It is database vendor independent means it does not depend on any DB specific syntax. It can generate database change documentation.

In this tutorial, we will learn how to configure Liquibase with Spring Boot project. Here, we will write database schema changes in XML format. Those schema changes will be automatically performed on the configured database using liquibase when spring boot project will be started.

Here, we will consider 3 simple database operations:
1). Create table,
2). Insert data into table and
3). Update data on precondition.
Those operations will be executed by liquibase on H2 database with spring boot.

2. Example

2.1 Project Structure

2.2 POM file configuration

Add dependencies liquibase-core, spring-boot-starter-jdbc for Liquibase support. The h2 dependency added for in-memory H2 database on which we will perform DB operations.

plugins {
 id 'org.springframework.boot' version '2.2.4.RELEASE'
 id 'io.spring.dependency-management' version '1.0.9.RELEASE'
 id 'groovy'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

configurations {
 developmentOnly
 runtimeClasspath {
  extendsFrom developmentOnly
 }
}

repositories {
 mavenCentral()
}

dependencies {
 implementation 'org.springframework.boot:spring-boot-starter-batch'
 implementation 'org.springframework.boot:spring-boot-starter-jooq'
 implementation 'org.springframework.boot:spring-boot-starter-web'
 implementation 'org.codehaus.groovy:groovy'
 implementation 'org.liquibase:liquibase-core'
 developmentOnly 'org.springframework.boot:spring-boot-devtools'
 runtimeOnly 'com.h2database:h2'
 testImplementation('org.springframework.boot:spring-boot-starter-test') {
  exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
 }
 testImplementation 'org.springframework.batch:spring-batch-test'
}

test {
 useJUnitPlatform()
}

2.3 application.properties

Change log file is the main point from where Liquibase looks for configuration. If we do not define change log file path in Spring Boot, it considers db/changelog/db.changelog-master.yaml as default path for YAML format. As we will go with XML format, we need to set spring.liquibase.change-log=classpath:/db/changelog/changelog-master.xml for change log file path in application.properties file. You can set logging level of liquibase logs by setting log level in logging.level.liquibase property. Other properties in given below properties file are for H2 database configuration.

spring.liquibase.change-log=classpath:/db/changelog/changelog-master.xml
logging.level.liquibase = DEBUG

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.url=jdbc:h2:mem:test;
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

2.4 Change Log Files

2.4.1 changelog-master.xml

First, create the master change log file changelog-master.xml with default liquibase XML format at configured change log path. We can define all changes in one master file but it can be hard to manage after the huge number of changes. So, we will define each changeSet in different change log file and that change files will be included in master change log file by tag as per below. The ordering of included file should be always in correct sequence.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <include file="/db/changelog/changes/create-person-table-changelog-1.xml"/>
    <include file="/db/changelog/changes/insert-person-table-changelog-2.xml"/>
    <include file="/db/changelog/changes/update-person-table-precondition-3.xml"/>
</databaseChangeLog>

2.4.2 create-person-table-changelog-1.xml

Now create first change log file create-person-table-changelog-1.xml at shown in the path above file. The tag must be the root element in every change log file. Write database related operation in tag with author and unique id attribute in liquibase defined XML syntax. As per best practice, write one change per changeSet to avoid failed auto-commit statements. Below, changeSet will create table person with three columns named id, name and address in the test H2 database. We can also perform a rollback on changes performed. For manual rollback, we need to define the operation in rollback tag. That rollback can be performed by maven command using till tag, date or change count (maven rollback).

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="admin" id="1">
        <createTable tableName="person">
            <column autoIncrement="true" name="id" type="INT">
                <constraints primaryKey="true"/>
            </column>
            <column name="name" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="address" type="VARCHAR(255)"/>
        </createTable>
        <rollback>
            <dropTable tableName="person"/>
        </rollback>
    </changeSet>
</databaseChangeLog>

2.4.3 insert-person-table-changelog-2.xml

Now create second change log file insert-person-table-changelog-2.xml at shown in the path above file. This file’s changeSet will perform insert values operation for person table. You can find more database operation syntax for liquibase at this link.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="admin" id="2">
        <insert tableName="person">
            <column name="id" valueNumeric="1"/>
            <column name="name" value="Tom"/>
            <column name="address" value="USA"/>
        </insert>
    </changeSet>
</databaseChangeLog>

2.4.4 update-person-table-precondition-3.xml

Now create third change log file update-person-table-precondition-3.xml at shown in the path above file. In this file, we have used tag for defining preconditional statement. The changes will be executed in DB only when precondition will be satisfied. Here, we have given the condition for DBMS type must be h2. We can perform different conditions based on different factors like tableExist, sqlCheck, columnExist, etc (preconditions). Below changeSet will update the value in person table.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <changeSet author="admin" id="3">
        <preConditions>
            <dbms type="h2"/>
        </preConditions>
        <update tableName="person">
            <column name="address" value="India"/>
            <where>name = 'Tom'</where>
        </update>
    </changeSet>
</databaseChangeLog>

2.5 Output

Run mvn spring-boot run command and run spring boot application. Now, open http://localhost:8080/h2-console/ url in the browser. Login with database properties configured in application.properties file. Write SELECT * FROM PERSON in textarea and click on Run. Your three database changes(create table, insert into table and update table with precondition) has been reflected in the output. You can also perform one by one changeSet and check the output.

3. Important properties related to Liquibase and Spring application

We can write following properties in application.properties file for more specific configurations –
References

spring.liquibase.change-log=classpath:/db/changelog/db.changelog-master.yaml # Change log configuration path.
spring.liquibase.check-change-log-location=true # Whether to check that the change log location exists.
spring.liquibase.contexts= # Comma-separated list of runtime contexts to use.
spring.liquibase.database-change-log-lock-table=DATABASECHANGELOGLOCK # Name of table to use for tracking concurrent Liquibase usage.
spring.liquibase.database-change-log-table=DATABASECHANGELOG # Name of table to use for tracking change history.
spring.liquibase.default-schema= # Default database schema.
spring.liquibase.drop-first=false # Whether to first drop the database schema.
spring.liquibase.enabled=true # Whether to enable Liquibase support.
spring.liquibase.labels= # Comma-separated list of runtime labels to use.
spring.liquibase.liquibase-schema= # Schema to use for Liquibase objects.
spring.liquibase.liquibase-tablespace= # Tablespace to use for Liquibase objects.
spring.liquibase.parameters.*= # Change log parameters.
spring.liquibase.password= # Login password of the database to migrate.
spring.liquibase.rollback-file= # File to which rollback SQL is written when an update is performed.
spring.liquibase.test-rollback-on-update=false # Whether rollback should be tested before update is performed.
spring.liquibase.url= # JDBC URL of the database to migrate. If not set, the primary configured data source is used.
spring.liquibase.user= # Login user of the database to migrate.

For Liquibase Reference : https://docs.liquibase.com/change-types/home.html

Download from GitLab

Integration of Spring Boot with Apache Camel & Camunda

1. Integration of Spring Boot with Apache Camel & Camunda

1.1 Spring Initializr:

Spring Boot has really made developers' lives easier. Spring Boot's starters and auto-configurators reduce a lot of burden on developers. Another nice integration framework is Apache Camel, which provided abstraction over different technologies. In this article, we'll learn how to integrate Spring Boot and Apache Camel.

Spring Boot projects can be created in two ways. One is through Spring Boot Intitializr (https://start.spring.io/) (which we are doing here) and the other is through the STS Plugin for Eclipse.

When you enter the Spring Initializr website, you'll be greeted with the interface below.

After the ZIP downloads, extract the ZIP file and fire up Eclipse import as a Maven project. When the import process completes, Spring starters will help Maven download all the required dependencies for Camel.

---

1.2. Add additional maven Dependencies for Camunda:

In your pom.xml add below dependencies.

 <repositories>
  <repository>
   <id>camunda-bpm-nexus</id>
   <name>camunda-bpm-nexus</name>
   <url>https://app.camunda.com/nexus/content/groups/public</url>
  </repository>
 </repositories>
 <dependency>
  <groupId>org.camunda.bpm.springboot</groupId>
  <artifactId>camunda-bpm-spring-boot-starter</artifactId>
  <version>3.0.0</version>
 </dependency>
 <dependency>
  <groupId>org.apache.camel</groupId>
  <artifactId>camel-spring</artifactId>
  <version>2.22.1</version>
 </dependency>

 <dependency>
  <groupId>org.camunda.bpm.extension.camel</groupId>
  <artifactId>camunda-bpm-camel-spring</artifactId>
  <version>0.5</version>
 </dependency>
 <dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
 </dependency>

Now If we run this application as mvn springboot:run, we get following logs in console, 

---

1.3 Integrating Apache Camel with Camunda using Spring Boot:

In your pom.xml add below dependencies.

Now, let's get our hands dirty.

Create a CamelController for invoking the Camel route:

Here, we're calling firstRoute and sending the body "Calling via Spring Boot Rest Controller" using ProducerTemplate.

Let's create a component class for placing Camel Routes: 

The specialty of Camel starter and Camunda starter is that it'll auto-wire the Camel context and auto-detect all of the Camel routes in our application.

You already have a main method, which was created by Intializr. In that add CamelServiceImpl bean and set camel context & process engine as its property. Autowire them as well.

---

1.4 Create BPMN file:

Using Camunda Moduler we can create one bpmn flow as below,

You can refer camunda website (https://camunda.com/products/) to create bpmn file.

1.5 Run our application:

Once every thing is set lets run our application with the command mvn springboot:run. 

• We can see camel has started and one router is started.
• After that Camunda engine also started.
• Hit http://localhost:8080/camel in the browser, we able to see full output in the console as below,

1.5 Project Structure:

Download GitLab

SPRING BOOT & MYBATIS INTEGRATION WITH HSQL DATABASE

MyBatis is a SQL Mapping framework with support for custom SQL, stored procedures and advanced mappings.

1. Create a SpringBoot Maven project and add following MyBatis Starter Dependency:

<dependency>
 <groupId>org.mybatis.spring.boot</groupId>
 <artifactId>mybatis-spring-boot-starter</artifactId>
 <version>1.0.0</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

2. Create Domain class:

public class User {
    private Integer id;
    private String name;
    private String email;
 
    // setters & getters
}

3. Create schema.sql:

CREATE TABLE users
(
    id int(11) NOT NULL AUTO_INCREMENT,
    name varchar(100) NOT NULL,
    email varchar(100) DEFAULT NULL,
    PRIMARY KEY (id)
);

4. Create data.sql:

insert into users(id, name, email) values(1,'Siva','siva@gmail.com');
insert into users(id, name, email) values(2,'Prasad','prasad@gmail.com');
insert into users(id, name, email) values(3,'Reddy','reddy@gmail.com');

5. Create MyBatis SQL mapper interface (UserMapper.java):

public interface UserMapper {
    void insertUser(User user);
    User findUserById(Integer id);
    List<User> findAllUsers();
}

We need to create Mapper XML files to define the queries for the mapped SQL statements for the corresponding Mapper interface methods.

6. Create UserMapper.xml: (Using XML configuration)

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sivalabs.demo.mappers.UserMapper">
 <resultMap id="UserResultMap" type="User">
  <id column="id" property="id" />
  <result column="name" property="name" />
  <result column="email" property="email" />
 </resultMap>

 <select id="findAllUsers" resultMap="UserResultMap">
  select id, name, email from users
 </select>

 <select id="findUserById" resultMap="UserResultMap">
  select id, name, email from users WHERE id=#{id}
 </select>
 
 <insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
  insert into users(name,email) values(#{name},#{email})
 </insert>
</mapper>

A few things to observe here are:

• Namespace in Mapper XML should be same as Fully Qualified Name (FQN) for Mapper Interface
• Statement id values should be same as Mapper Interface method names.
• If the query result column names are different from bean property names we can use <resultMap> configuration to provide mapping between column names and their corresponding bean property names. 

MyBatis also provides annotation based query configurations without requiring Mapper XMLs.

7. Create UserMapper.java: (Using annotation configuration)

public interface UserMapper{
    @Insert("insert into users(name,email) values(#{name},#{email})")
    @SelectKey(statement="call identity()", keyProperty="id",
    before=false, resultType=Integer.class)
    void insertUser(User user);
    @Select("select id, name, email from users WHERE id=#{id}")
    User findUserById(Integer id);
    @Select("select id, name, email from users")
    List<User> findAllUsers();
}

SpringBoot MyBatis starter provides the following MyBatis configuration parameters which we can use to customize MyBatis settings in application.properties file.

8. Application.properties:

mybatis.config = mybatis config file name
mybatis.mapperLocations = classpath*:**/mappers/*.xml
mybatis.typeAliasesPackage = domain object's package
mybatis.typeHandlersPackage = handler's package
mybatis.check-config-location = check the mybatis configuration exists
mybatis.executorType = mode of execution. Default is SIMPLE

9. Application.java:

@SpringBootApplication
@MapperScan("com.ushan.demo.mappers")
public class Application{
    public static void main(String[] args)
    {
        SpringApplication.run(Application.class, args);
    }
}

10. JUnit Test Class:

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(Application.class)
public class ApplicationTests
{
    @Autowired
    private UserMapper userMapper;
    @Test
    public void findAllUsers() {
        List<User> users = userMapper.findAllUsers();
        assertNotNull(users);
        assertTrue(!users.isEmpty());
    }
    @Test
    public void findUserById() {
        User user = userMapper.findUserById(1);
        assertNotNull(user);
    }
    @Test
    public void createUser() {
        User user = new User(0, "Ushan", "ushan@gmail.com");
        userMapper.insertUser(user);
        User newUser = userMapper.findUserById(user.getId());
        assertEquals("Ushan", newUser.getName());
        assertEquals("ushan@gmail.com", newUser.getEmail());
    }
}