Saturday, 22 May 2021

Spring boot: Flyway: Hello world application using MySQL

In this post, I am going to explain how to integrate Flyway with spring boot application.

 

Follow below step-by-step procedure to build complete working application.

 

Step 1: Create new maven project 'spring-flyway-helloworld'.

 

Step 2: Update pom.xml with maven dependencies.

 

pom.xml

<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.sample.app</groupId>
	<artifactId>spring-flyway-helloworld</artifactId>
	<version>1</version>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.6.RELEASE</version>
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<dependencies>

		<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>org.flywaydb</groupId>
			<artifactId>flyway-core</artifactId>
		</dependency>
	</dependencies>
</project>

 

Step 3: Create application.yml file in src/main/resources folder.

 

application.yml

spring:
  profiles.active: local
---
spring:
  profiles: local  

Step 4: Create ‘V1__Create_employee_table.sql’ file under src/main/resources/db/migration folder.

 

V1__Create_employee_table.sql

create table EMPLOYEE (
    ID int not null,
    NAME varchar(100) not null
);


Step 5: Define DB configuration class.

 

DBConfig.java

package com.sample.app.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
public class DBConfig {
	@Bean
	public DataSource dataSource() {

		HikariDataSource dataSource = new HikariDataSource();
		dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
		dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/abc_org");
		dataSource.setUsername("root");
		dataSource.setPassword("tiger");
		return dataSource;
	}

}


Step 6: Define main application class.

 

App.java

package com.sample.app;

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

@SpringBootApplication
public class App {

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

}


Total project structure looks like below.




Run App.java.

 

Query the database abc_org.


mysql> show tables;
+-----------------------+
| Tables_in_abc_org     |
+-----------------------+
| EMPLOYEE              |
| flyway_schema_history |
+-----------------------+
2 rows in set (0.00 sec)


Query EMPLOYEE table.

mysql> SELECT * FROM EMPLOYEE;
Empty set (0.00 sec)


Query from flyway_schema_history table.

mysql> SELECT * FROM flyway_schema_history ;
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description           | type | script                        | checksum    | installed_by | installed_on        | execution_time | success |
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
|              1 | 1       | Create employee table | SQL  | V1__Create_employee_table.sql | -2061683220 | root         | 2021-05-22 17:33:34 |              8 |       1 |
+----------------+---------+-----------------------+------+-------------------------------+-------------+--------------+---------------------+----------------+---------+
1 row in set (0.00 sec)


Let’s insert some data into EMPLOYEE table

Create ‘V2__Insert_Into_employee_table.sql’ file in src/main/resources/db/migration folder.

 

V2__Insert_Into_employee_table.sql

INSERT INTO EMPLOYEE VALUES(1, 'Krishna');
INSERT INTO EMPLOYEE VALUES(2, 'Ram');
INSERT INTO EMPLOYEE VALUES(3, 'Sailaja');
INSERT INTO EMPLOYEE VALUES(4, 'Lahari');


Define App.java and query EMPLOYEE table.

mysql> SELECT * FROM EMPLOYEE;
+----+---------+
| ID | NAME    |
+----+---------+
|  1 | Krishna |
|  2 | Ram     |
|  3 | Sailaja |
|  4 | Lahari  |
+----+---------+
4 rows in set (0.00 sec)


Query ‘flyway_schema_history’ table.

mysql> SELECT * FROM flyway_schema_history ;
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
| installed_rank | version | description                | type | script                             | checksum    | installed_by | installed_on        | execution_time | success |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
|              1 | 1       | Create employee table      | SQL  | V1__Create_employee_table.sql      | -2061683220 | root         | 2021-05-22 17:33:34 |              8 |       1 |
|              2 | 2       | Insert Into employee table | SQL  | V2__Insert_Into_employee_table.sql | -1900411443 | root         | 2021-05-22 17:38:26 |              6 |       1 |
+----------------+---------+----------------------------+------+------------------------------------+-------------+--------------+---------------------+----------------+---------+
2 rows in set (0.00 sec)


You can download complete working application from below link.

https://github.com/harikrishna553/springboot/tree/master/flyway/spring-flyway-helloworld





Previous                                                    Next                                                    Home

No comments:

Post a Comment