When developing web application, it is important to consider how to create schema for production and evolve the database schema going forward of a Java web application.

Liquibase is an open source library for tracking, managing and applying database changes that can be used for any database. It helps you create the schema, run them during deployment and also help you write automated tests so that your changes will work in production.

Liquibase is a tool that reads a list of database changes from a changelog file. The changelog file is often XML-based, but it could be in other formats also, such as YAML, JSON and SQL formats. Once a new change is added to the changelog file, Liquibase will read the file, detect the new change, and apply it to the database. When code changes are committed to the version control system, the changelog file containing the database “version” also gets committed along with it.

For more information on Liquibase please read http://www.liquibase.org/

In my previous tutorial Spring Boot Liquibase Gradle Example I had shown how to write liquibase code using XML but in this Spring Boot Liquibase example I will show you how to write the same code in YAML and SQL formats. In this example I have used Oracle database but in my previous tutorial Spring Boot Liquibase Gradle Example I had used MySQL database. Even you can use a database of your choice.

Recommended reading: How to setup Liquibase in Spring for Multiple DataSources

Benefits of Liquibase

This could be found in my previous tutorial Spring Boot Liquibase Gradle Example

Prerequisites

Java version 8 needs to be installed and configured
Gradle plugin needs to be installed into Eclipse
Gradle 4.x needs to installed and configured
Dependencies : Spring boot, slf4j, Oracle database

Creating and setting up Gradle project

Project name – SpringBootLiquibaseSQL

buildscript {
	ext {
	   springBootVersion = '1.5.9.RELEASE'
    }
    repositories {
		mavenLocal()
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'

jar {
                baseName = 'SpringBootLiquibaseSQL'
                version = '0.0.1-SNAPSHOT'
                manifest {
					attributes("Main-Class": "com.jeejava.main.Application")
                }
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

configurations {
    all*.exclude module : 'spring-boot-starter-logging'
}

repositories {
    mavenLocal()
    mavenCentral()
}

dependencies {
	compile('org.springframework.boot:spring-boot-starter-web')
	compile('org.springframework.boot:spring-boot-starter-jdbc')  
	compile('org.liquibase:liquibase-core')
	runtime("com.oracle.jdbc:ojdbc7:12.1.0.2")
	compile("org.slf4j:slf4j-api")
    compile("org.slf4j:slf4j-log4j12")
    compile("commons-logging:commons-logging")
}

When referring to build directories, the tutorial assumes the standard Gradle build paths for your gradle project:

SpringBootLiquibaseSQL/src/main/java – Java source directory
SpringBootLiquibaseSQL/src/main/resources – Resource directory
SpringBootLiquibaseSQL/src/test/java – Java test directory
SpringBootLiquibaseSQL/src/test/resources – Resource test directory

Create loggers

Put below log4j.properties file under classpath resource SpringBootLiquibaseSQL/src/main/resources

log4j.rootLogger=INFO, CONSOLE
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern= %d{hh:mm:ss,SSS} [%t] %-5p %c %x - %m%n

Changing default server port and database configurations

Create an application.properties file under SpringBootLiquibaseGradle/src/main/resources with following content.

#start server at below port instead of random port
server.port=9999

#Oracle database configurations
spring.datasource.url=jdbc:Oracle:thin:@//:/
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver

Create databaseChangeLog file

Create a YAML file called db.changelog-master.yaml under SpringBootLiquibaseGradle/src/main/resources/db/changelog. This master file will include all the changelogs written in separate files. The complete master changelog file content given as below

databaseChangeLog:
  - changeSet:
      id: createTable
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: oracle
          encoding: utf8
          endDelimiter: \n/\s*\n|\n/\s*$
          path: scripts/01-create-users-and-addresses-schema.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true
          
  - changeSet:
      id: insertTableAddresses
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: oracle
          encoding: utf8
          path: scripts/02-insert-data-addresses.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true
          
  - changeSet:
      id: insertTableUsers
      author: Soumitra Roy
      changes:
      - sqlFile:
          dbms: oracle
          encoding: utf8
          path: scripts/02-insert-data-users.sql
          relativeToChangelogFile: true
          splitStatements: true
          stripComments: true

Notice in the above YAML file, I have not specified any endDelimiter for changeSet ids insertTableAddresses and insertTableUsers because the default endDelimiter is ;.

Now create below SQL file 01-create-users-and-addresses-schema.sql under SpringBootLiquibaseGradle/src/main/resources/db/changelog/scripts to create tables in the Oracle database

CREATE TABLE ""."ADDRESSES" 
   (
                "ID" NUMBER NOT NULL,
                "STREET" VARCHAR2(100) NOT NULL,
    "CITY" VARCHAR2(100),
    "PIN" NUMBER(6),
    CONSTRAINT "ADDRESSES_PK" PRIMARY KEY ("ID")
   )
/
CREATE TABLE ""."USERS" 
   (
                "ID" NUMBER NOT NULL,
                "NAME" VARCHAR2(50) NOT NULL,
                "EMAIL" VARCHAR2(100),
                "PHONE" NUMBER,
                "ADDRESS" NUMBER NOT NULL,
                CONSTRAINT "USERS_PK" PRIMARY KEY ("ID"),
                CONSTRAINT "USERS_FK" FOREIGN KEY ("ADDRESS") REFERENCES ""."ADDRESSES" ("ID")
   )
/

Now create below SQL file 02-insert-data-addresses.sql under SpringBootLiquibaseGradle/src/main/resources/db/changelog/scripts to insert data into ADDRESSES table

insert into ""."ADDRESSES"(ID, STREET, CITY, PIN) values (1, 'street1', 'city1', 111111);
insert into ""."ADDRESSES"(ID, STREET, CITY) values (2, 'street2', 'city2');

Now create below SQL file 02-insert-data-users.sql under SpringBootLiquibaseGradle/src/main/resources/db/changelog/scripts to insert data into USERS table

insert into ""."USERS"(ID, NAME, EMAIL, ADDRESS) values (1, 'Soumitra', 'soumitra@email.com', 1);
insert into ""."USERS"(ID, NAME, EMAIL, PHONE, ADDRESS) values (2, 'Suman', 'suman@email.com', 1254789541, 2);

Create the main class

Create bwlow main class in order to start up the application and above tables creation and insertion into tables will be occurring during the application startup.

package com.jeejava.main;

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

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

Running the application

Run the main class Application.java file.

Console Output

01:21:43,758 [main] INFO  liquibase  - Successfully acquired change log lock
01:21:47,717 [main] INFO  liquibase  - Reading from .DATABASECHANGELOG
01:21:48,640 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::createTable::Soumitra Roy: SQL in file scripts/01-create-users-and-addresses-schema.sql executed
01:21:48,781 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::createTable::Soumitra Roy: ChangeSet classpath:/db/changelog/db.changelog-master.yaml::createTable::Soumitra Roy ran successfully in 610ms
01:21:49,767 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy: SQL in file scripts/02-insert-data-addresses.sql executed
01:21:49,892 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy: ChangeSet classpath:/db/changelog/db.changelog-master.yaml::insertTableAddresses::Soumitra Roy ran successfully in 563ms
01:21:50,739 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::insertTableUsers::Soumitra Roy: SQL in file scripts/02-insert-data-users.sql executed
01:21:50,864 [main] INFO  liquibase  - classpath:/db/changelog/db.changelog-master.yaml: classpath:/db/changelog/db.changelog-master.yaml::insertTableUsers::Soumitra Roy: ChangeSet classpath:/db/changelog/db.changelog-master.yaml::insertTableUsers::Soumitra Roy ran successfully in 548ms
01:21:51,692 [main] INFO  liquibase  - Successfully released change log lock

You will find tables created into the database. You will find two rows inserted into addresses and two rows inserted into users tables. You will also find three rows inserted into the table DATABASECHANGELOG. These row identifies all details about the executed file. You will also find one row inserted into the table DATABASECHANGELOGLOCK. This row identifies whether current operation holds lock on changesets or not.

Recommended reading: How to setup Liquibase in Spring for Multiple DataSources

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit | Email Me

Leave a Reply

Your email address will not be published. Required fields are marked *