6. Processing with Multiple Data Sources

Currently, under the framework of micro-services, single-service single-library operation is more advocated.However, due to some historical burdens or other factors, multiple databases need to be accessed in the project at the same time. This article is mainly about how to handle this situation.

Most databases are divided into two cases, the two cases are handled differently, the first is simpler, the second is more complex, and we will explain the two cases carefully.

  1. Multiple databases in the same instance
  2. Multiple databases in different instances

Multiple libraries with the same instance

The solution for this scenario is simple and requires only that the configuration generate multiple libraries when the code is generated.

 <generator>
    <!-- ... -->
    <database>
      <schemata>
        <schema>
          <inputSchema>learn-jooq</inputSchema>
        </schema>
        <schema>
          <inputSchema>learn-jooq-2</inputSchema>
        </schema>
        <!-- other schema config ... -->
      </schemata>
    </database>
    <!-- ... -->
</generator>

Because multiple databases can share the same data source within the same instance.When rendering SQL, jOOQ can take the database name with it (this configuration is turned on by default) and is not affected by the database name included in the database connection parameters, for example

Query the s1_user table of the learn-jooq library:

Query `s7_user'table of `learn-jooq-2`library: ````select `learn-jooq-2`.`s7_user`.`id` from `learn-jooq-2`.`s7_user`

 

Multiple libraries with different instances

This is a relatively complex case for the same instance, because different instances definitely require different database connections, that is, different data sources, different transaction managers, and so on.

code generation

In the case of multiple data sources, there is a need for a set of configurations for each database instance, due to different configurations such as connections to each database.Using the executions option of the maven plug-in, you can configure multiple executors at the same time, and because there are too many configurations, they are no longer placed in the pom.xml file.Pull out the configuration of each database as a separate file, where two databases are tested so two files are created:

  • src/main/resources/codegen-config-jooq-learn.xml
  • src/main/resources/codegen-config-jooq-learn-2.xml

The contents of the file will not be pasted, unlike previous plug-in configurations, the differences are the connection to the database, the name of the library, and other basic configurations.In addition to some basic configurations, it is important to note that the package name of the target needs to be set to a different path and cannot be placed in the same package, as the two override each other

<configuration>
    <!-- ...  -->
    <generator>
        <!-- ...  -->
        <target>
            <packageName>com.diamondfsd.jooq.learn.codegen.learn_jooq</packageName>
            <directory>/src/main/java</directory>
        </target>
    </generator>
</configuration>

 

The plug-in configuration for pom.xml changes to the following:

<plugin>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen-maven</artifactId>
    <version>3.12.3</version>
    <executions>
        <execution>
            <id>learn-jooq</id>
            <configuration>
                <configurationFile>src/main/resources/codegen-config-jooq-learn.xml</configurationFile>
            </configuration>
        </execution>
        <execution>
            <id>learn-jooq-2</id>
            <configuration>
                <configurationFile>src/main/resources/codegen-config-jooq-learn-2.xml</configurationFile>
            </configuration>
        </execution>
    </executions>
</plugin>

 

In this case, if you want to generate code, you need to execute the mvn jooq-codegen:generate@{execution.id} command, specifying the execution ID to execute for different configurations

  • mvn jooq-codegen:generate@learn-jooq
  • mvn jooq-codegen:generate@learn-jooq-2

Data Source Configuration

In the case of multiple data sources, since the code generated by jOOQ is the same, the main task is to process Configuration objects that need to be injected into the DAO generated by jOOQ.Configuration objects are mainly used to store data source objects, SQL dialects, and some other rendering configurations, etc.

First, configure parameters for multiple database connections within jdbc.properties

datasource1.jdbc.url=jdbc:mysql://localhost:3306/learn-jooq?serverTimezone=GMT%2B8
datasource1.jdbc.username=root
datasource1.jdbc.password=root


datasource2.jdbc.url=jdbc:mysql://localhost:3306/learn-jooq-2?serverTimezone=GMT%2B8
datasource2.jdbc.username=root
datasource2.jdbc.password=root

 

Spring Data Source/Transaction Configuration, where multiple data sources are configured due to multiple database connections, and the Transaction Manager needs to be configured for each data source

The purpose of declaring the TX_LEARN_JOOQ and TX_LEARN_JOQ_2 constants is to explicitly specify the required transaction manager when using the @Transaction annotation, depending on the database, in the case of multiple data sources, because there are multiple transaction managers.If you want a default transaction manager, you can add the @Primary annotation to the default transaction manager definition

@Configuration
@EnableTransactionManagement
@PropertySource("classpath:jdbc.properties")
public class DataSourceConfig {

    public static final String TX_LEARN_JOOQ = "learnJooqTransactionManager";
    public static final String TX_LEARN_JOOQ_2 = "learnJooq2TransactionManager";

    @Bean
    public PlatformTransactionManager learnJooqTransactionManager(
            @Autowired
            @Qualifier("learnJooqDataSource")
                    DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public PlatformTransactionManager learnJooq2TransactionManager(
            @Autowired
            @Qualifier("learnJooq2DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public DataSource learnJooqDataSource(
            @Value("${datasource1.jdbc.url}")
                    String url,
            @Value("${datasource1.jdbc.username}")
                    String username,
            @Value("${datasource1.jdbc.password}")
                    String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        return new TransactionAwareDataSourceProxy(new HikariDataSource(config));
    }

    @Bean
    public DataSource learnJooq2DataSource(
            @Value("${datasource2.jdbc.url}")
                    String url,
            @Value("${datasource2.jdbc.username}")
                    String username,
            @Value("${datasource2.jdbc.password}")
                    String password) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setUsername(username);
        config.setPassword(password);
        return new TransactionAwareDataSourceProxy(new HikariDataSource(config));
    }
}

 

jOOQ Configuration

The jOOQ configuration is mainly because the code within the DAO is automatically generated, we are not very good at modifying it, and it will be restored each time the code is regenerated after the change. The solution used here is.

First declare multiple DefaultConfiguration Beans based on the data source, which are injected into the corresponding data source, then declare org.jooq.Configuration Bean, and get a Map<String, DefaultConfiguration> object in the declaration function. This object is automatically injected by Spring, which makes up a Map based on the name of the Bean and the instance object.Also get an InjectionPoint instance that stores some source information about where the @Autowired annotation is located.In this scenario, get the InjectionPoint object to get the package path where the DAO is located.Select different DefaultConfiguration objects for injection based on different package paths

@Configuration
@Import(DataSourceConfig.class)
public class JooqConfiguration {

    @Bean
    @Primary
    @Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
    public org.jooq.Configuration configuration(
            Map<String, DefaultConfiguration> configurationMap,
            InjectionPoint injectionPoint) {
        AnnotatedElement annotatedElement = injectionPoint.getAnnotatedElement();
        if (Constructor.class.isAssignableFrom(annotatedElement.getClass())) {
            Class declaringClass = ((Constructor) annotatedElement).getDeclaringClass();
            String packageName = declaringClass.getPackage().getName();
            org.jooq.Configuration configuration;
            switch (packageName) {
                case "com.diamondfsd.jooq.learn.codegen.learn_jooq.tables.daos":
                    configuration = configurationMap.get("learnJooqConfiguration");
                    break;
                case "com.diamondfsd.jooq.learn.codegen.learn_jooq_2.tables.daos":
                    configuration = configurationMap.get("learnJooq2Configuration");
                    break;
                default:
                    throw new NoSuchBeanDefinitionException("no target switch");
            }
            return configuration;
        }
        throw new NoSuchBeanDefinitionException("no target switch");
    }

    @Bean
    public DefaultConfiguration learnJooqConfiguration(@Autowired
                                                       @Qualifier("learnJooqDataSource")
                                                               DataSource learnJooqDataSource) {
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.set(SQLDialect.MYSQL);
        configuration.set(learnJooqDataSource);
        configuration.settings().setRenderSchema(false);
        return configuration;
    }

    @Bean
    public DefaultConfiguration learnJooq2Configuration(@Autowired
                                                        @Qualifier("learnJooq2DataSource")
                                                                DataSource learnJooq2DataSource) {
        DefaultConfiguration configuration = new DefaultConfiguration();
        configuration.set(SQLDialect.MYSQL);
        configuration.set(learnJooq2DataSource);
        configuration.settings().setRenderSchema(false);
        return configuration;
    }
}

Code demonstration

With these configurations, we will be able to happily use jOQ with multiple data sources, and consistently use it as usual. DAO objects can still be injected automatically.The @Transaction annotation can also be used by explicitly specifying the transaction manager

@Autowired
S1UserDao s1UserDao;

@Transactional(TX_LEARN_JOOQ)
public void insert() {
    S1UserPojo s1UserPojo = new S1UserPojo();
    s1UserPojo.setUsername("username");
    s1UserDao.insert(s1UserPojo);
}


@Autowired
S7UserDao s7UserDao;

@Test
@Transactional(TX_LEARN_JOOQ_2)
public void insert() {
    S7UserPojo s7UserPojo = new S7UserPojo();
    s7UserPojo.setUsername("s7username");
    s7UserDao.insert(s7UserPojo);
}

Content Summary

Source code for this chapter: https://github.com/k55k32/learn-jooq/tree/master/section-7

In the case of multiple data sources, this is mainly the scenario used when migrating some historical code.Normally, in a modern microservice system, only one data source is used for a service.The solution presented here is simple, using different data source configurations through the package name of the DAO, allowing Spring to manage automatic injection without affecting the use of the business tier

Published 0 original articles, received 0 reviews and visited 30
Private letter follow

Tags: JDBC Database xml MySQL

Posted on Tue, 03 Mar 2020 19:41:26 -0800 by dinno2