Above Using JdbcTemplate In this paper, we mainly use JdbcTemplate provided by spring to add, delete and check user tables. In implementing this example, we created user tables in MySQL beforehand. The process of creating tables is often used in the actual development of systems, but there has always been a problem. Because the program version of a system is well controlled by git, and the database structure is not. Even if we versioned statements through Git, then the database in each environment. How to do version management well? Now let's learn how to use Flyway to manage database versions in Spring Book.

Flyway introduction

Flyway is a simple open source database version controller (convention is greater than configuration), which mainly provides migrate, clean, info, validate, baseline, repair s and other commands. It supports SQL (PL/SQL, T-SQL) mode and Java mode, command line client, etc. It also provides a series of plug-in support (Maven, Gradle, SBT, ANT, etc.).

Official website: https://flywaydb.org/

This article does not introduce Flyway's own functions too much. Readers can get more information by reading official documents or using search engines. Let's talk about how Flyway can be used to create databases and check for inconsistencies in Spring Book applications.

Try it yourself

Here we can go through Using JdbcTemplate The examples in this paper are processed. Readers can also experiment with any project related to data access as follows:

• The first step is to add flyway dependencies in pom.xml:

• <dependency>
  	<groupId>org.flywaydb</groupId>
  	<artifactId>flyway-core</artifactId>
  	<version>5.0.3</version>
  </dependency>

  The second step is to create versioned SQL scripts according to Flyway specifications.

• Create the db directory under the src/main/resources directory of the project
• Create versioned SQL script V1_u Base_version.sql in db directory
• The third step is to configure the location of the SQL script Flyway will load in the application.properties file. The results created in the second step are configured as follows:

  flyway.locations=classpath:/db
  

   

• The fourth step is to execute the unit test Application Tests, at which point we can see the following information in the log:

  INFO 82441 --- [main] o.f.core.internal.util.VersionPrinter    : Flyway Community Edition 5.0.3 by Boxfuse
  INFO 82441 --- [main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/test (MySQL 5.7)
  INFO 82441 --- [main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.022s)
  INFO 82441 --- [main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: `test`.`flyway_schema_history`
  INFO 82441 --- [main] o.f.core.internal.command.DbMigrate      : Current version of schema `test`: << Empty Schema >>
  INFO 82441 --- [main] o.f.core.internal.command.DbMigrate      : Migrating schema `test` to version 1 - Base version
  WARN 82441 --- [main] o.f.core.internal.sqlscript.SqlScript    : DB: Unknown table 'test.user' (SQL State: 42S02 - Error Code: 1051)
  INFO 82441 --- [main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `test` (execution time 00:00.128s)
  

  Flyway monitored the need to run version scripts to initialize the database, so it executed the V1_u Base_version.sql script to create user tables, which allowed a series of unit tests (CRUD operations on user tables) to pass.

• Fifth, we can continue to run the unit test, and we will find that the log output is different from the previous one.

  INFO 83150 --- [main] o.f.core.internal.util.VersionPrinter    : Flyway Community Edition 5.0.3 by Boxfuse
  INFO 83150 --- [main] o.f.c.internal.database.DatabaseFactory  : Database: jdbc:mysql://localhost:3306/test (MySQL 5.7)
  INFO 83150 --- [main] o.f.core.internal.command.DbValidate     : Successfully validated 1 migration (execution time 00:00.031s)
  INFO 83150 --- [main] o.f.core.internal.command.DbMigrate      : Current version of schema `test`: 1
  INFO 83150 --- [main] o.f.core.internal.command.DbMigrate      : Schema `test` is up to date. No migration necessary.
  

  Since the initialization script was executed at step 4, the V1_u Base_version.sql script was no longer executed to rebuild the user table.

• Step 6, we can try to modify the length of the name field in the V1_u Base_version.sql script, and then run the unit test. At this point, we can get the following error:

  ERROR 83791 --- [main] o.s.boot.SpringApplication               : Application startup failed
  
  org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Invocation of init method failed; nested exception is org.flywaydb.core.api.FlywayException: Validate failed: Migration checksum mismatch for migration version 1
  -> Applied to database : 466264992
  -> Resolved locally    : -270269434
  

  Flyway checkout failed due to the change of initialization script. The current V1_u Base_version.sql script is different from the last one. It prompts an error and terminates the program so as to avoid more serious data structure damage.

• Source of source code