[JAVA] I tried Flyway with Spring Boot

Introduction

Flyway is famous as Java DB migration, but I haven't touched it, so I introduced it during the New Year holidays. It was estimated that it would take 3 hours to work, but in reality it took about 15 minutes.

Identify what you want to do for the time being

1. Introducing Flyway to your own app (using Gradle)
2. Beginner DB migration using Flyway
3. Summary (Qiita article creation)

Introduced Flyway

Try installing it in a Web Application running on SpringBoot + Gradle.

The only steps required to install it are as follows: Easy!

1. Fill out build.gradle
2. Place the SQL file below the CretateTable statement.
3. Start Springboot (start from IntelliJ IDEA)

Fill in the required items in build.gradle.

build.gradle

ext {
	// Flyway
	flywayVersion = "4.1.2" // 2017,0315 latest
}
dependencies {
	// Flyway
	compile ("org.flywaydb:flyway-core:$flywayVersion")
}

plugins {
    id "org.flywaydb.flyway" version "4.1.2" // 2017,0315 latest
}

flyway {
    user = '' //DB user name
    url = ''//DB URL
}

Placement of SQL files

Next, deploy SQL files such as CretateTable statement below

・ Src / main / resources / db / migration /

There is a naming convention for the file names of the files to be deployed. Also, it seems that Create Database etc. can not be written

• In the first place, if you specify Database in the Database URL, you can not do it

sql:V1.0__Initial_Setup.sql

CREATE TABLE IF NOT EXISTS ANSWER_INFO_TBL (
  NO BIGINT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  TYPE TINYINT(1) NOT NULL,
  ANSWERKEY VARCHAR(81) UNIQUE NOT NULL,
  KEYHASH VARCHAR(64) UNIQUE NOT NULL ,
  CREATE_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS SCORE_INFO_TBL (
  NO BIGINT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  SCORE MEDIUMINT(7) UNSIGNED DEFAULT 0 NOT NULL,
  NAME VARCHAR(64) DEFAULT '' NOT NULL,
  MEMO VARCHAR(64) DEFAULT '' NOT NULL,
  UPDATE_DATE DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (NO)
          REFERENCES ANSWER_INFO_TBL(NO)
          ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

ALTER TABLE ANSWER_INFO_TBL ADD INDEX INDEX_ANSWERKEY(ANSWERKEY ASC);
ALTER TABLE ANSWER_INFO_TBL ADD INDEX INDEX_KEYHASH(KEYHASH ASC);

Start Springboot

DB migration is performed automatically when launching the application from IntelliJ IDEA.

• I'm not sure if it worked unless I Drop the existing table before starting, so Drop before it works.

Somehow it becomes convenient using Flyway.

While making an application, I may create garbage data or want to reset the DB. However, I want to keep clean data ... → Extract existing records that have no problem and insert them automatically after DB reset →→ Let Flyway do it!

1. Get existing records with MysqlWorkbench (can get as sql file in the form of insert statement in Export Results)
2. Deploy the sql file of the insert statement under src / main / resources / db / migration /

If you start Springboot under the above conditions after dropping the table, it will automate the table creation and record creation. If the version control of the sql file is also good, the hurdle for building the environment will be lowered!

Summary

I was able to introduce it smoothly, so I feel like pushing my back, which I had been hesitant to introduce. DB resets are frequent, so be careful not to neglect the management of sql files. In addition, I think that there are some useful functions that I haven't seen yet just by introducing them, so I will add them as soon as the functions are discovered. Also, it seems that there is an article tag of Flyway, so I think you can follow it.

bonus

When changing from Date type to LocalDate type in JPA Entity It seems that you have to use the following techniques

WebApp.java

@SpringBootApplication
@Import(Domain.class)
@EntityScan(basePackageClasses = {WebApp.class, Jsr310JpaConverters.class})
@EnableScheduling
public class WebApp extends SpringBootServletInitializer {
	public static void main(String[] args) {
		new SpringApplicationBuilder(WebApp.class)
						.profiles("dev")
						.run(args);
	}
}

The miso is that Jsr310JpaConverters.class is the target of Entity Scan.