SpringBoot2多SQLite数据源配置求助：读写库自动建表失效

Let's fix your multi-data-source configuration step by step. The core issues with your current setup are: you only defined DataSource beans but didn't configure the corresponding EntityManagerFactory and TransactionManager for each data source, and Hibernate properties were incorrectly placed under the data source prefix (DataSourceBuilder doesn't handle Hibernate configs).

Step 1: Restructure application.properties #

Split data source connection properties and JPA/Hibernate configurations into separate prefixes, so we can properly bind them later:

# Read-Only Data Source (Kracken)
lsst.kracken.datasource.jdbcUrl = jdbc:sqlite:file:./data/kraken_2026.db?mode=ro
lsst.kracken.datasource.driver-class-name = org.sqlite.JDBC
lsst.kracken.datasource.maximum-pool-size=10

# Kracken JPA/Hibernate Settings
lsst.kracken.jpa.hibernate.dialect=org.hibernate.dialect.SQLiteDialect
lsst.kracken.jpa.hibernate.hbm2ddl.auto=none
lsst.kracken.jpa.show-sql=false
lsst.kracken.jpa.hibernate.format_sql=false

# Writable Data Source (AjsData)
lsst.ajsdata.datasource.jdbcUrl = jdbc:sqlite:file:./data/ajs_kraken_2026.db?mode=rw
lsst.ajsdata.datasource.driver-class-name = org.sqlite.JDBC
lsst.ajsdata.datasource.maximum-pool-size=30
lsst.ajsdata.datasource.default-auto-commit=true

# AjsData JPA/Hibernate Settings (this will handle table creation)
lsst.ajsdata.jpa.hibernate.dialect=org.hibernate.dialect.SQLiteDialect
lsst.ajsdata.jpa.hibernate.hbm2ddl.auto=create
lsst.ajsdata.jpa.show-sql=true
lsst.ajsdata.jpa.hibernate.format_sql=true
lsst.ajsdata.jpa.hibernate.use-new-id-generator-mappings=true
lsst.ajsdata.jpa.hibernate.open-in-view=false

# Global JPA Configurations (shared across both data sources)
spring.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyComponentPathImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Step 2: Complete the Primary (Read-Only) Persistence Context #

You need to define not just the DataSource, but also the EntityManagerFactory and TransactionManager, and link them to your entities/repositories:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.yourpackage.kracken.repository", // Replace with your actual read-only repo package
        entityManagerFactoryRef = "krackenEntityManagerFactory",
        transactionManagerRef = "krackenTransactionManager"
)
@EnableTransactionManagement
public class KrackenPersistenceContext {

    @Autowired
    private Environment env;

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "lsst.kracken.datasource")
    public DataSource krackenDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "krackenEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean krackenEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("krackenDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.kracken.entity") // Replace with your read-only entity package
                .persistenceUnit("krackenPU")
                .properties(getHibernateProperties("lsst.kracken.jpa"))
                .build();
    }

    @Primary
    @Bean(name = "krackenTransactionManager")
    public PlatformTransactionManager krackenTransactionManager(
            @Qualifier("krackenEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    private Properties getHibernateProperties(String prefix) {
        Properties props = new Properties();
        props.put("hibernate.dialect", env.getProperty(prefix + ".hibernate.dialect"));
        props.put("hibernate.hbm2ddl.auto", env.getProperty(prefix + ".hibernate.hbm2ddl.auto"));
        props.put("hibernate.show_sql", env.getProperty(prefix + ".show-sql"));
        props.put("hibernate.format_sql", env.getProperty(prefix + ".hibernate.format_sql"));
        return props;
    }
}

Step 3: Configure the Writable Data Source Persistence Context #

Repeat the same for your writable AjsData source, making sure to use distinct packages for entities/repositories:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.Properties;

@Configuration
@EnableJpaRepositories(
        basePackages = "com.yourpackage.ajsdata.repository", // Replace with your writable repo package
        entityManagerFactoryRef = "ajsdataEntityManagerFactory",
        transactionManagerRef = "ajsdataTransactionManager"
)
@EnableTransactionManagement
public class AjsDataPersistenceContext {

    @Autowired
    private Environment env;

    @Bean
    @ConfigurationProperties(prefix = "lsst.ajsdata.datasource")
    public DataSource ajsdataDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ajsdataEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean ajsdataEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("ajsdataDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.yourpackage.ajsdata.entity") // Replace with your writable entity package
                .persistenceUnit("ajsdataPU")
                .properties(getHibernateProperties("lsst.ajsdata.jpa"))
                .build();
    }

    @Bean(name = "ajsdataTransactionManager")
    public PlatformTransactionManager ajsdataTransactionManager(
            @Qualifier("ajsdataEntityManagerFactory") LocalContainerEntityManagerFactoryBean entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory.getObject());
    }

    private Properties getHibernateProperties(String prefix) {
        Properties props = new Properties();
        props.put("hibernate.dialect", env.getProperty(prefix + ".hibernate.dialect"));
        props.put("hibernate.hbm2ddl.auto", env.getProperty(prefix + ".hibernate.hbm2ddl.auto"));
        props.put("hibernate.show_sql", env.getProperty(prefix + ".show-sql"));
        props.put("hibernate.format_sql", env.getProperty(prefix + ".hibernate.format_sql"));
        props.put("hibernate.use_new_id_generator_mappings", env.getProperty(prefix + ".hibernate.use-new-id-generator-mappings"));
        props.put("hibernate.open_in_view", env.getProperty(prefix + ".hibernate.open-in-view"));
        return props;
    }
}

Key Fixes & Explanations #

• Separated Configurations: Data source connection properties (like jdbcUrl) are bound to DataSource beans, while Hibernate settings are passed directly to EntityManagerFactory — this is why your hbm2ddl.auto=create wasn't working before; those properties weren't being picked up by Hibernate.
• Entity/Repository Isolation: By specifying distinct basePackages in @EnableJpaRepositories and packages in EntityManagerFactory, Spring knows which repositories belong to which data source.
• Full Persistence Stack: Each data source needs its own EntityManagerFactory and TransactionManager to handle ORM operations and transactions correctly.
• SQLite URL: Ensure the file path is correct (relative paths are resolved from your application's working directory) and that your app has write permissions for the ajs_kraken_2026.db location.

Final Checks #

• Make sure you have the sqlite-jdbc dependency in your pom.xml or build.gradle:

  <!-- Maven -->
  <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
  </dependency>
  

• Verify that your entities for the writable data source are annotated properly (with @Entity, @Id, etc.) — Hibernate needs these to generate tables.

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