CODEWITHSUNDEEP
javaspring-bootspring-data-jpa
Patric Hollenstein
Patric Hollenstein

Reputation: 75

How to create database table with spring boot after change the data source URL

I use spring-boot with DATAJPA repository and two datasource. One datasource connect to a MySQL DB and the 2nd to a file based embedded H2 DB.

On the H2 DB i want change the URL during my application ist running. The URL changing work and after change the URL to an not existing DB spring create the DB. But the tables will not create and spring give me an error like Table "SYMBOL" not found; SQL statement:. When i restart my app now then spring create the tables with the new URL and everything work fine.

How i can create the tables after change the URL?

To change the URL i write my own datasource and build it new on each connection.

public class ProjectDataSource extends AbstractDataSource {

    @Autowired ToolService toolService;

    @Override
    public Connection getConnection() throws SQLException {
        return determineTargetDataSource().getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return determineTargetDataSource().getConnection(username, password);
    }

    @ConfigurationProperties(prefix = "project.datasource")
    private DataSource determineTargetDataSource() {
        String projectName = toolService.get().getProjectName();
        String url = "jdbc:h2:file:../db/" + projectName;

        return DataSourceBuilder
                .create()
                .url(url)
                .build();
    }
}

Here the configuration

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "projectEntityManagerFactory",
        transactionManagerRef = "projectTransactionManager",
        basePackages = {"at.ltw.test.bz.model.project"})
public class ProjectDbConfiguration {


    @Bean(name = "projectDataSource")
    public DataSource dataSource() {
        return new ProjectDataSource();
    }

    @Bean(name = "projectEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean
    barEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("projectDataSource") DataSource dataSource
    ) {

        HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        jpaVendorAdapter.setGenerateDdl(true);

        return builder
                .dataSource(dataSource)
                .packages("at.ltw.test.bz.model.project")
                .persistenceUnit("bzProject")
                .properties(jpaVendorAdapter.getJpaPropertyMap())
                .build();
    }

    @Bean(name = "projectTransactionManager")
    public PlatformTransactionManager barTransactionManager(
            @Qualifier("projectEntityManagerFactory") EntityManagerFactory projectEntityManagerFactory) {
        return new JpaTransactionManager(projectEntityManagerFactory);
    }

}

Here my application.properties

#jpa
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true

#tool database
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost/ltw?useSSL=false
spring.datasource.username=ltw
spring.datasource.password=password

#project database
project.datasource.driver-class-name=org.h2.Driver

#logging
logging.level.at.ltw = trace

Hope somebody can help me and sorry for my bad English ...

Upvotes: 1

Views: 1297

Answers (1)

wargre
wargre

Reputation: 4753

JPA Will not help you because it is only at init state that DDL are checked.

You need to use something like flywayDB or liquibase or simple SQL file to generate the new DB.

Anyway, you code is wrong, and I'm surprise you don't have error.

@Bean(name = "projectDataSource")
public DataSource dataSource() {
    return new ProjectDataSource();
}

There is no injection but a simple new object and ...

public class ProjectDataSource extends AbstractDataSource {
    @Autowired ToolService toolService;

    @ConfigurationProperties(prefix = "project.datasource")
    private DataSource determineTargetDataSource() {
        String projectName = toolService.get().getProjectName();
        ...
    }

toolService will be null, so determineTargetDataSouce will fail with a null pointer exception.

Upvotes: 1

Related Questions