This is the 20th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

Flyway is a database control plug-in that allows all database scripts to be controlled in the IDE (IDEA, Eclipse, etc.) so that versions can be tracked.

It is important to note that with Flyway, changes to the database table structure in the database management software are prohibited

Springboot integrates flyway

1.pom.xml

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
            <version>6.4.4</version>
        </dependency>
Copy the code

2.application.yml

Spring: Flyway: # Flyway DDL version control Enabled: true # Flyway DDL version control enabled: true # Clean-disabled: true # Table: flyway_schema_history_systemportal #flyway record which script is currently executed baseline-version: Baseline on-migrate: true baseline version will be called if the database is not empty. If the database is empty, baseline version will be called if the database is placeholder. False placeholder: # define afterMigrateError. SQL How to clean a metadata table Flyway-table: ${spring.flyway.table}Copy the code

The following error may occur during the first initialization, and placeholder-replacement is required: false.

3. Script structure

When defining the initialization script, you are advised to set the version number to V+(number)+__(double underscore)+ service name +init, for example, v1__Systemportal_init. SQL.

The version number is V+(number)+__(double underscore)+ operation + table name, for example, the main version is v2__update_table.sql.

Where v1__systemportal_init. SQL (initialization script) for the project all table structure and data export in the following way.

V2__update_table. SQL is the changed version of the table. If there is no change, only the init script exists.

  ALTER TABLE "systemportal"."task_plan"
  ALTER COLUMN "update_user" TYPE varchar(255) USING "update_user"::varchar(255);
Copy the code

After performing the above operations, delete all the original database table data (remember to do a good backup) and then start the project, you can generate tables in the database, in plain English, is to follow the script in db to execute again.

Principle 2.

When flyway is used, a log table is generated to record the names of scripts that have been currently executed. (A script field is a script in db that has been executed.) According to the above, the following records are generated during initialization.

If the table has scripts of a version greater than V2, the system queries the table for scripts of a version greater than V2. If scripts of a version greater than V2 are executed, the system does not execute any scripts.

Three. Integrate Quartz

When integrating Quartz, you need to query the table, but with Flyway you don’t have a table structure at this point, so you need to process it.

1. Comment out @postConstruct

2. Add the configuration

Add the following configuration (executed after startup)

@Component public class RuntimeConfig implements ApplicationListener<ContextRefreshedEvent> { @Autowired private Scheduler scheduler; @Autowired private QrtzJobDao qrtzJobDao; @Override public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) { this.initJob(); } public void initJob() { QrtzJobVO qrtzJobVO = new QrtzJobVO(); List<QrtzJobVO> scheduleJobList = qrtzJobDao.queryQrtzJobAll(qrtzJobVO); scheduleJobList.forEach(scheduleJob -> { CronTrigger cronTrigger = ScheduleUtils.getCronTrigger(this.scheduler, scheduleJob.getJobId()); if (cronTrigger == null) { ScheduleUtils.createScheduleJob(this.scheduler, scheduleJob); } else { ScheduleUtils.updateScheduleJob(this.scheduler, scheduleJob); }}); }}Copy the code