QueryDSL+SpringDataJpa 多数据源配置

这里用1个Mysql数据源和一个SqlServer数据源来举例

QueryDSL代码生成插件相关的依赖和配置)

配置中包含了常用的Lombok和MapStruct插件 因为这两个原理和QueryDSL类似都是在编译器生成模板代码
pom.xml:

    <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-core</artifactId>
            <version>5.1.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.querydsl/querydsl-jpa -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>5.1.0</version>
            <classifier>jakarta</classifier>
        </dependency>

<build>
    <plugins>
     <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <source>21</source>
                <target>21</target>
                <compilerArgs>--enable-preview</compilerArgs>
                <annotationProcessorPaths>
                    <path>
                        <groupId>org.projectlombok</groupId>
                        <artifactId>lombok</artifactId>
                        <version>1.18.30</version>
                    </path>
                    <path>
                        <groupId>org.mapstruct</groupId>
                        <artifactId>mapstruct-processor</artifactId>
                        <version>1.5.5.Final</version>
                    </path>
                    <path>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>5.1.0</version>
                        <classifier>jakarta</classifier>
                    </path>
                    <path>
                        <groupId>jakarta.persistence</groupId>
                        <artifactId>jakarta.persistence-api</artifactId>
                        <version>3.1.0</version>
                    </path>
                </annotationProcessorPaths>
            </configuration>
        </plugin>
    </plugins>
</build>

按照上述的配置 build项目之后会生成Q文件到项目target目录下的generated-sources目录下的annotations下面

配置多数据源

连接参数配置文件:

spring:
  datasource:
    sqlserver:
      url: jdbc:sqlserver://127.0.0.1:11433;databaseName=ZKTeco;encrypt=true;trustServerCertificate=true;
      username: root
      password: 123456
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
    mysql:
      url: jdbc:mysql://127.0.0.1:3306/attence?useUnicode=true&serverTimezone=Asia/Shanghai
      username: root
      password: 12345678
      driver-class-name: com.mysql.cj.jdbc.Driver

DbConfig.java:

@Configuration
public class DbConfig {


    @Bean
    @ConfigurationProperties("spring.datasource.sqlserver")
    public DataSourceProperties sqlServerDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.mysql")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }


    @Bean
    @Primary
    public DataSource sqlServerDataSource() {
        return sqlServerDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    public DataSource mysqlDataSource() {
        return mysqlDataSourceProperties().initializeDataSourceBuilder().build();
    }
}

配置Jpa上下文

Jpa通过一个EntityManager来生成SQL语句和发起查询 需要新建下面2个Bean
MysqlJpaConfig.java:

  @Bean
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(
            @Qualifier("mysqlDataSource") DataSource dataSource,
            EntityManagerFactoryBuilder builder){
        Map<String, Object> props = new HashMap<>();
        props.put("hibernate.hbm2ddl.auto", "update");
        props.put("hibernate.physical_naming_strategy", CamelCaseToUnderscoresNamingStrategy.class.getName());
        props.put("hibernate.implicit_naming_strategy", SpringImplicitNamingStrategy.class.getName());
        //命名策略
        return   builder
                .dataSource(dataSource)
                .persistenceUnit("mysqlEntityManagerFactory")
                .properties(props)
                .packages("com.example.model.mysql")
                .build();
    }

    @Bean
    public PlatformTransactionManager mysqlTransactionManager(
            @Qualifier("mysqlEntityManagerFactory") LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory){
        if (mysqlEntityManagerFactory == null || mysqlEntityManagerFactory.getObject() == null){
            throw new RuntimeException("mysqlEntityManagerFactory is null");
        }
        return new JpaTransactionManager(mysqlEntityManagerFactory.getObject());
    }
 
    @Bean
    @DependsOn("mysqlEntityManagerFactory")
    public JPAQueryFactory jpaQueryFactory(@Qualifier("mysqlEntityManagerFactory") EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

在这段配置中 通过指定JpaQueryFactory的EntityManager是mysqlEntityManagerFactory 就可以通过QueryDSL查询Mysql的数据了
另外上述参数中有几个关键的信息

• hibernate.hbm2ddl.auto 指定表结构自动生成
• hibernate.physical_naming_strategy,hibernate.implicit_naming_strategy 指定命名策略从驼峰到下划线分割
• .persistenceUnit("mysqlEntityManagerFactory")给EntityManager命名 方便后续的注入指定名字

SQlServer的配置也是差不多 这里不配置QueryDSL
SqlServerJpaConfig.java:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.example.repo.sqlserver",
        entityManagerFactoryRef = "sqlServerEntityManagerFactory",
        transactionManagerRef = "sqlServerTransactionManager")
public class SqlServerJpaConfig {
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory(
            @Qualifier("sqlServerDataSource") DataSource dataSource,
            EntityManagerFactoryBuilder builder) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.model.sqlserver")
                .build();
    }

    @Bean
    public PlatformTransactionManager sqlServerTransactionManager(
            @Qualifier("sqlServerEntityManagerFactory") LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory) {
        if (sqlServerEntityManagerFactory == null || sqlServerEntityManagerFactory.getObject() == null){
            throw new RuntimeException("sqlServerEntityManagerFactory is null");
        }
        return new JpaTransactionManager(sqlServerEntityManagerFactory.getObject());
    }
}