Liquibase Spring Boot Testing with MySQL DB

Some development teams prefer to keep test case records in persistent db, So that results can be varied later, in the below example, we will be using MySQL as database and appleDbTest schema for application persistence and for testing purposes same MySQL database but appleDbTest schema. 

package com.example.SpringLiquibase.controller;

import com.example.SpringLiquibase.entity.Apple;
import com.example.SpringLiquibase.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class LiquibaseController {
    @Autowired
    AppleRepository appleRepository;

    @GetMapping
    public List<Apple> getApple() {
        return appleRepository.findAll();
    }
}
package com.example.SpringLiquibase.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
public class Apple {
    @Id
    @Column
    @GeneratedValue
    private Long appleId;
    @Column
    private String appleName;
}
package com.example.SpringLiquibase.repository;

import com.example.SpringLiquibase.entity.Apple;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
}
package com.example.SpringLiquibase;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringLiquibaseApplication {
    public static void main(String[] args) {
        SpringApplication.run(SpringLiquibaseApplication.class, args);
    }
}

\src\main\resources\db\changelog\changelog.mysql.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   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-latest.xsd">
    <changeSet author="wesome" id="1692446190573-1">
        <createTable catalogName="appledb" tableName="apple">
            <column autoIncrement="true" name="apple_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="apple_name" type="VARCHAR(255)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime"/>
        </createTable>
        <rollback>
            <dropTable tableName="apple"/>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-2">
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_name" value="Fuji"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Fuji'
                </where>
            </delete>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-3">
        <insert catalogName="appledb" tableName="apple">
            <column name="apple_name" value="Gala"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Gala'
                </where>
            </delete>
        </rollback>
    </changeSet>
</databaseChangeLog>

\src\main\resources\application.properties 

server.servlet.context-path=/liquibase
spring.datasource.url=jdbc:mysql://localhost:3306/appledb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=rootroot
spring.liquibase.change-log=db/changelog/changelog.mysql.xml
spring.jpa.show-sql=true
package com.example.SpringLiquibase.controller;

import com.example.SpringLiquibase.entity.Apple;
import com.example.SpringLiquibase.repository.AppleRepository;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.orm.jpa.DataJpaTest;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertAll;
import static org.junit.jupiter.api.Assertions.assertEquals;

@DataJpaTest
class LiquibaseControllerTest {

    @Autowired
    AppleRepository appleRepository;

    @Test
    void getApple() {
        List<Apple> apples = appleRepository.findAll();
        Assertions.assertFalse(apples.isEmpty());
        assertAll(() -> Assertions.assertFalse(apples.isEmpty(), "apple list should not be empty"), () -> Assertions.assertTrue(apples.size() == 2, "apple list should contain 2 elements"), () -> assertEquals(1, apples.get(0).getAppleId(), "appleId should be 1"), () -> assertEquals("Macintosh", apples.get(0).getAppleName(), "appleName should be Macintosh"), () -> assertEquals(2, apples.get(1).getAppleId(), "appleId should be 2"), () -> assertEquals("Jonagold", apples.get(1).getAppleName(), "appleName should be Jonagold"));

    }
}

\src\test\resources\db\changelog\changelog.mysql.test.xml

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   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-latest.xsd">
    <changeSet author="wesome" id="1692446190573-1">
        <createTable catalogName="appleDbTest" tableName="apple">
            <column autoIncrement="true" name="apple_id" type="BIGINT">
                <constraints nullable="false" primaryKey="true"/>
            </column>
            <column name="apple_name" type="VARCHAR(255)"/>
            <column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime"/>
        </createTable>
        <rollback>
            <dropTable tableName="apple"/>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-2">
        <insert catalogName="appleDbTest" tableName="apple">
            <column name="apple_name" value="Macintosh"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Macintosh'
                </where>
            </delete>
        </rollback>
    </changeSet>
    <changeSet author="wesome" id="1692701200879-3">
        <insert catalogName="appleDbTest" tableName="apple">
            <column name="apple_name" value="Jonagold"/>
            <column name="current_date_time" valueDate="now()"/>
        </insert>
        <rollback>
            <delete tableName="apple">
                <where>
                    apple_name = 'Jonagold'
                </where>
            </delete>
        </rollback>
    </changeSet>
</databaseChangeLog>

\src\test\resources\application.properties

spring.test.database.replace=none
spring.jpa.hibernate.ddl-auto=none
spring.liquibase.change-log=db/changelog/changelog.mysql.test.xml
spring.liquibase.liquibase-schema=appleDbTest
spring.liquibase.drop-first=true
spring.datasource.url=jdbc:mysql://localhost:3306/appleDbTest?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=rootroot

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.1.4</version>
        <relativePath/>
    </parent>
    <groupId>com.example</groupId>
    <artifactId>SpringLiquibaseApplication</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SpringLiquibaseApplication</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</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-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Once the Application is Up and running, the inserted record can be verified by 

SELECT * FROM appledb.apple;

the testing record can be verified by 

SELECT * FROM appleDbTest.apple;

The API result can be verified by executing

curl --location 'http://localhost:8081/liquibase/'

follow us on

YouTube IconLinkedIn Icon