FlyDB

Philosophy

There are a variety of ways how jOOQ and Flyway could interact with each other in various development
setups. In this tutorial we're going to show just one variant of such framework team play - a variant that
we find particularly compelling for most use cases.
The general philosophy behind the following approach can be summarised as this:
- 1. Database increment
- 2. Database migration
- 3. Code re-generation
- 4. Development
The four steps above can be repeated time and again, every time you need to modify something in your
database. More concretely, let's consider:
- 1. Database increment - You need a new column in your database, so you write the necessary
DDL in a Flyway script
- 2. Database migration - This Flyway script is now part of your deliverable, which you can share
with all developers who can migrate their databases with it, the next time they check out your
change
- 3. Code re-generation - Once the database is migrated, you regenerate all jOOQ artefacts (see
code generation), locally
- 4. Development - You continue developing your business logic, writing code against the udpated,
generated database schema

Maven Project Configuration - Plugins

 <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.flywaydb</groupId>
                <artifactId>flyway-maven-plugin</artifactId>
                <version>${flywaydb.version}</version>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>migrate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <url>jdbc:mysql://localhost:3306?useUnicode=true&amp;characterEncoding=utf8&amp;autoReconnect=true&amp;rewriteBatchedStatements=TRUE</url>
                    <driver>com.mysql.jdbc.Driver</driver>
                    <user>root</user>
                    <password>root</password>
                    <baselineOnMigrate>true</baselineOnMigrate>
                    <schemas>
                        <schema>library</schema>
                    </schemas>
                    <locations>
                        <location>filesystem:src/main/resources/db/migration</location>
                    </locations>
                </configuration>
            </plugin>
        </plugins>
    </build>

src/main/resources/db/migration:迁移sql文件目录

注意： url中未指定schema， 则需要在schemas中指定，并且sql文件中表名必须是全名，如library.author，否则迁移失败。若url中指定schema， sql文件中表名可以不指定schema，可直接使用author。

版本1： V1__create_author.sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for author
-- ----------------------------
DROP TABLE IF EXISTS `library`.`author`;
CREATE TABLE `library`.`author` (
  `id` int(11) NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

版本2：V2__create_book.sql

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `library`.`book`;
CREATE TABLE `library`.`book` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `author` int(11) DEFAULT NULL,
  `version` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `library`.`book` (`id`, `name`, `author`, `version`) VALUES ('book_1', 'c# book', '1', '1');

进行迁移：mvn clean compile -e.
观察flyway-maven-plugin:4.2.0:migrate相关日志，查看是否迁移成功！

[INFO] Error stacktraces are turned on.
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building jooq 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-clean-plugin:2.6.1:clean (default-clean) @ jooq ---
[INFO] Deleting F:\code\jooq\target
[INFO]
[INFO] --- flyway-maven-plugin:4.2.0:migrate (default) @ jooq ---
[INFO] Flyway 4.2.0 by Boxfuse
Fri Oct 20 13:53:12 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default
if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true
and provide truststore for server certificate verification.
[INFO] Database: jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE (MySQL 5.7)
[INFO] Successfully validated 2 migrations (execution time 00:00.010s)
[INFO] Current version of schema `library`: 2
[INFO] Schema `library` is up to date. No migration necessary.
Fri Oct 20 13:53:18 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default
if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true
and provide truststore for server certificate verification.
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ jooq ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 5 resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ jooq ---
[INFO] Changes detected - recompiling the module!
[INFO] Compiling 19 source files to F:\code\jooq\target\classes
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 10.582 s
[INFO] Finished at: 2017-10-20T13:53:21+08:00
[INFO] Final Memory: 37M/315M
[INFO] ------------------------------------------------------------------------

迁移成功，检查表结构。其中schema_version表为flyDB默认生成，用于记录版本信息。

book表中插入一条记录。

schema_version表中记录每次迁移的版本信息。