如何整合Flyway+H2+JOOQ Codegen？表名大小写问题待解决

问题根源 #

当前配置存在两个核心冲突：

1. 代码生成与运行时数据库行为不一致：用PostgreSQL容器生成JOOQ代码（PostgreSQL默认将表名、列名转为小写），但运行时使用开启MODE=MSSQLServer的H2数据库（大小写敏感，实际表名为大写BOOKS），导致JOOQ生成的小写表名books无法匹配数据库中的大写表。
2. Schema大小写不匹配：PostgreSQL的public是小写，H2中创建的Schema默认转为大写PUBLIC，导致JOOQ生成的代码引用小写publicSchema找不到对应对象。

分步解决方案 #

1. 统一代码生成与运行时的数据库环境 #

修改Testcontainers JOOQ代码生成插件，改用H2容器生成代码，确保和运行时数据库的大小写、Schema行为完全一致：

<plugin>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
    <version>${testcontainers-jooq-codegen-maven-plugin.version}</version>
    <dependencies>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>${testcontainers.version}</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${h2.version}</version>
        </dependency>
    </dependencies>
    <executions>
        <execution>
            <id>generate-jooq-sources</id>
            <goals>
                <goal>generate</goal>
            </goals>
            <phase>generate-sources</phase>
            <configuration>
                <database>
                    <type>H2</type>
                    <containerImage>oscarfonts/h2:latest</containerImage>
                    <urlParams>;MODE=MSSQLServer;INIT=CREATE SCHEMA IF NOT EXISTS public\;SET SCHEMA public</urlParams>
                </database>
                <flyway>
                    <defaultSchema>public</defaultSchema>
                    <createSchemas>true</createSchemas>
                    <locations>
                        filesystem:src/main/resources/db/migration
                    </locations>
                </flyway>
                <jooq>
                    <generator>
                        <database>
                            <includes>.*</includes>
                            <excludes>flyway_schema_history</excludes>
                            <inputSchema>public</inputSchema>
                            <!-- 关键配置：让JOOQ生成的标识符与数据库实际大小写一致 -->
                            <outputSchemaToDefault>true</outputSchemaToDefault>
                            <quotedNames>EXPLICIT</quotedNames>
                        </database>
                        <target>
                            <packageName>example.micronaut.jooqtest</packageName>
                            <directory>src/main/java</directory>
                        </target>
                    </generator>
                </jooq>
            </configuration>
        </execution>
    </executions>
</plugin>

2. 调整Flyway运行时配置 #

确保Flyway在H2数据库上的Schema配置与代码生成逻辑一致：

<plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>10.10.0</version>
    <executions>
        <execution>
            <id>flyway-migrate</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>migrate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <user>${database.user}</user>
        <password>${database.password}</password>
        <url>${database.url}</url>
        <locations>
            <location>classpath:db/migration</location>
        </locations>
        <!-- 指定默认Schema，避免Flyway操作错误的Schema -->
        <defaultSchema>public</defaultSchema>
    </configuration>
</plugin>

3. 简化JOOQ运行时配置 #

去掉复杂的Schema映射，确保JOOQ使用默认Schema且标识符大小写匹配：

@Configuration
public class JooqConfiguration {

    @Autowired
    private DataSource dataSource;

    @Bean
    public DefaultConfiguration jooqConfiguration() {
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.set(SQLDialect.H2);
        configuration.setDataSource(dataSource);

        Settings settings = new Settings()
                // 不渲染Schema前缀，使用默认Schema
                .withRenderSchema(false)
                // 确保标识符大小写与数据库一致
                .withQuotedNames(QuotedNames.EXPLICIT);
        configuration.set(settings);

        return configuration;
    }

    @Bean
    public DSLContext dslContext() {
        return new DefaultDSLContext(jooqConfiguration());
    }
}

4. 修正H2连接配置 #

用Spring Boot自动配置替代自定义DataSourceConfiguration，确保H2连接URL正确创建并切换到publicSchema：

# application.properties
spring.datasource.url=jdbc:h2:file:./testdb;MODE=MSSQLServer;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS public\\;SET SCHEMA public
spring.datasource.username=sa
spring.datasource.password=password
spring.datasource.driver-class-name=org.h2.Driver

验证步骤 #

1. 执行mvn clean generate-sources，检查JOOQ生成的Tables.BOOKS是否引用与数据库一致的表名大小写。
2. 执行mvn flyway:migrate，确认Flyway在H2的publicSchema中正确创建表。
3. 启动应用，通过Postman发送请求验证功能正常。

内容的提问来源于stack exchange，提问作者Adam G