Spring Boot Data R2DBC自动创建表格

Question

我有一个简单的问题，使用Spring Boot Data R2DBC是否可以自动生成MySQL或其他数据库的表格？在JPA中，我添加了spring.jpa.hibernate.ddl-auto = update并创建了表格。

这是我的pom：

    <dependencies>
    <dependency>
        <groupId>org.springframework.boot.experimental</groupId>
        <artifactId>spring-boot-starter-data-r2dbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-webflux</artifactId>
    </dependency>
    <dependency>
        <groupId>dev.miku</groupId>
        <artifactId>r2dbc-mysql</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot.experimental</groupId>
        <artifactId>spring-boot-test-autoconfigure-r2dbc</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>io.projectreactor</groupId>
        <artifactId>reactor-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>io.netty</groupId>
        <artifactId>netty-all</artifactId>
        <version>4.1.43.Final</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

- Mateusz Sobczak

Answer 1

我知道这不是确切的答案，但在R2DBC中创建表的唯一方法是利用databasePopulator：

@Bean
fun initializer(connectionFactory: ConnectionFactory) =
    ConnectionFactoryInitializer().apply {
        setConnectionFactory(connectionFactory)
        setDatabasePopulator(CompositeDatabasePopulator()
            .apply {
                addPopulators(ResourceDatabasePopulator(FileSystemResource("src/test/resources/sql/init.sql")))
            })
    }

它在测试期间会很方便。

- pixel

Answer 2

为了测试目的，我创建了两个指向同一数据库的数据源。一个是用于JDBC，另一个是用于R2DBC。

JDBC数据源

@Configuration
@EnableJpaRepositories( basePackages = "com.ns.reactivetest.repository.jdbc", entityManagerFactoryRef = "jdbcEntityManager", transactionManagerRef = "jdbcTransactionManager" )
@EnableTransactionManagement
public class JdbcConfiguration {

    @Autowired
    Environment env;

    @Bean( name = "jdbcDataSource" )
    @Primary
    public DataSource jdbcDataSource( ) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource( );
        dataSource.setUrl( env.getProperty( "spring.datasource.url" ) );
        dataSource.setUsername( env.getProperty( "spring.datasource.username" ) );
        dataSource.setPassword( env.getProperty( "spring.datasource.password" ) );

        HikariDataSource hikariDataSource = new HikariDataSource( );
        hikariDataSource.setDataSource( dataSource );
        hikariDataSource.setMinimumIdle( Integer.valueOf( env.getProperty( "spring.datasource.hikari.minimum-idle" ) ) );
        hikariDataSource.setMaximumPoolSize( Integer.valueOf( env.getProperty( "spring.datasource.hikari.maximum-pool-size" ) ) );
        hikariDataSource.setSchema( env.getProperty( "spring.datasource.hikari.schema" ) );

        return hikariDataSource;
    }

    @Bean( name = "jdbcEntityManager" )
    @Primary
    public LocalContainerEntityManagerFactoryBean jdbcEntityManager( @Qualifier( "jdbcDataSource" ) DataSource dataSource ) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean( );
        em.setDataSource( dataSource );
        em.setPackagesToScan( new String[]{ "com.ns.reactivetest.domain" } );
        em.setPersistenceUnitName( "jdbc_persistence_Unit_Name" );

        JpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter( );
        em.setJpaVendorAdapter( jpaVendorAdapter );

        Properties properties = new Properties( );
        properties.setProperty( "hibernate.hbm2ddl.auto", env.getProperty( "spring.jpa.hibernate.ddl-auto" ) );
        properties.setProperty( "hibernate.dialect", env.getProperty( "spring.jpa.properties.hibernate.dialect" ) );
        em.setJpaProperties( properties );

        return em;
    }

    @Bean( name = "jdbcTransactionManager" )
    @Primary
    public PlatformTransactionManager jdbcTransactionManager( @Qualifier( "jdbcDataSource" ) DataSource dataSource ) {
        JpaTransactionManager transactionManager = new JpaTransactionManager( );
        transactionManager.setDataSource( dataSource );
        transactionManager.setPersistenceUnitName( "jdbc_persistence_Unit_Name" );
        return transactionManager;
    }

}

R2DBC 数据源

@Configuration
@EnableR2dbcRepositories( basePackages = "com.ns.reactivetest.repository.reactive" )
@EnableTransactionManagement
public class R2DbcConfiguration  {

    @Autowired
    private Environment env;


    @Bean
    public ConnectionFactory connectionFactory( ) {
        ConnectionFactory connectionFactory = new PostgresqlConnectionFactory(
                PostgresqlConnectionConfiguration.builder( )
                        .host( env.getProperty( "spring.r2dbc.properties.hostname", "localhost" ) )
                        .database( env.getProperty( "spring.r2dbc.name", "postgres" ) )
                        .username( env.getProperty( "spring.r2dbc.username", "postgres" ) )
                        .password( env.getProperty( "spring.r2dbc.password", "password" ) )
                        .schema( env.getProperty( "spring.r2dbc.properties.schema", "public" ) )
                        .port( Integer.valueOf( env.getProperty( "spring.r2dbc.properties.port", "5432" ) ) )
                        //.options( options )
                        .build( )
        );
        if ( env.getProperty( "spring.r2dbc.pool.enabled", Boolean.class, false ) ) {
            ConnectionPoolConfiguration connectionPoolConfiguration = ConnectionPoolConfiguration.builder( connectionFactory )
                    .maxIdleTime( Duration.ofSeconds( env.getProperty( "spring.r2dbc.pool.max-idle-time", Long.class, 1800L ) ) ) // 30 Minutes
                    .initialSize( env.getProperty( "spring.r2dbc.pool.initial-size", Integer.class, 10 ) )
                    .maxSize( env.getProperty( "spring.r2dbc.pool.max-size", Integer.class, 25 ) )
                    .validationQuery( env.getProperty( "spring.r2dbc.validation-query", String.class, "SELECT 1" ) )
                    .build( );
            return new ConnectionPool( connectionPoolConfiguration );
        } else {
            return connectionFactory;
        }
    }

    @Bean
    ReactiveTransactionManager transactionManager( ConnectionFactory connectionFactory ) {
        return new R2dbcTransactionManager( connectionFactory );
    }

}

注意： 我在这里使用了JDBC和R2DBC Repositoties两个包。

目前看起来运行良好。但是我不知道它在复杂情况和要求下如何工作。

- Nipuna Perera

Answer 3

为了使r2dbc在应用启动时自动创建表，你只需要一个带有@Configuration注解的配置Java类，例如：

import io.r2dbc.spi.ConnectionFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer;
import org.springframework.r2dbc.connection.init.ResourceDatabasePopulator;

@Configuration
public class DbSchemaInitOnStartup {

    @Bean
    ConnectionFactoryInitializer initializer(@Qualifier("connectionFactory") ConnectionFactory connectionFactory) {
        ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
        initializer.setConnectionFactory(connectionFactory);
        ResourceDatabasePopulator resource = new ResourceDatabasePopulator(new ClassPathResource("schema.sql"));
        initializer.setDatabasePopulator(resource);
        return initializer;
    }

}

schema.sql文件必须放在/resources文件夹中才能正常工作。

- stackr