Shulou(Shulou.com)06/01 Report--

Editor to share with you how the springboot project integrates flyway. I hope you will get something after reading this article. Let's discuss it together.

1. Flyway

Flyway is a database version management tool that applies, manages and tracks database changes independently of the database. In popular terms, Flyway can manage different people's sql scripts in the same way that Git manages different people's code, thus achieving database synchronization.

II. Process

1. After you first configure the basic information of flyway, run the project and create a new data table in the database table to store the running information of flyway by default. The default database name is flyway_schema_history.

2. Immediately after Flyway will begin to scan the classpath of the file system or application for migration. The data migration for Flyway will then be sorted based on the version number of the script using sql and applied sequentially:

You can see that a value is stored in checksum after the database table is executed, which can be used to compare whether the sql file execution has changed during a later run.

Note:

When flyway executes the script, it checks the checksum value in the source data table and determines which script file it ran to last time. This execution starts with the next script file. So don't modify the content of the original script when writing the script, and the new script version number should be continuous.

2.1 Import dependent org.flywaydb flyway-core org.postgresql postgresql runtime org.springframework.boot spring-boot-starter-jdbc org.flywaydb flyway-maven-plugin 5.2.1 2.2 configuration ymlspring: # druid data source datasource: druid: db-type: com.alibaba.druid.pool.DruidDataSource driverClassName: net .sf.log4jdbc.sql.jdbcapi.DriverSpy url: jdbc:log4jdbc:mysql://127.0.0.1/plumvill_test?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true username: root password: 123456 flyway: # location of the migration script Default db/migration, configurable locations: classpath:/db # the use of flyway for existing databases with table structure data needs to be set to true, and the version number should be larger than 1 # baseline-on-migrate: true2.3 create sql script

/ db/V1__init_database.sql

Format of sql script: v + version number + double underscore + description .sql

Description: v uppercase, with two underscores in the middle (_ _)

USE plumvill_test;SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for sys_user-- DROP TABLE IF EXISTS `sys_ User` CREATE TABLE `user` (`id`bigint (0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', `username` varchar) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT' 'COMMENT' username', `nick_ name` varchar 'CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT' 'COMMENT' nickname', `gender` bit (1) NOT NULL DEFAULT boun0' COMMENT 'gender (0 is the male default) 1 is female)',) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'system user' ROW_FORMAT = Compact -Records of sys_user-- INSERT INTO `sys_ user`VALUES (1, 'admin',' administrator', bread0'); INSERT INTO `sys_ user`VALUES (2, 'test',' test', baim1testing,); 2.4 start the project

You can see that the sql script starts to execute

After successful execution, you can find that there is an extra flyway_schema_history table in the database to record each script execution.

In the future, whenever there is an update to the database, you only need to create a higher version of the sql script under the db folder (V1.xroomxxx.sql, V2roomroomxxx.sql), which will be executed automatically when the project is restarted.

All right.

2.5 FAQ

1. Can I implement the same initialization script for the same environment based on environment variables?

Based on the core of our configuration, you can modify the flyway.locations configuration, and the initialization scripts for the same environment can be placed in the same directory.

2. Error rollback occurs in the process of initializing data?

Each sql file will have a separate thing. If an error occurs in a single file, the operation of a single file will be rolled back, for example, if there are 1, 2 or 3 files, if an error occurs in the second file, all operations in the second file will be rolled back and the third file will be executed. But: Unfortunately, today only DB2, PostgreSQL, Derby, EnterpriseDB and to a certain extent SQL Server support DDL statements inside a transaction. Therefore, it is recommended not to put ddl files and dml statements in the same file to avoid unnecessary trouble.

After reading this article, I believe you have a certain understanding of "how springboot projects integrate flyway". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.