SpringMVC+MyBatis realizes multi-data source switching

In enterprise-level application development, it is often necessary to process data from different databases. To meet this requirement, we can configure multiple data sources to access different databases. This article will introduce how to combine MyBatis with dynamic switching of multiple data sources under the Spring MVC framework.

1. Environmental preparation

Java: 1.8 or later

Spring Boot：2.3.

MyBatis：3.5.2

Database: MySQL (The example uses two different database instances)

2. Add dependencies

First, add the necessary dependencies to the file:

<dependencies>
    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
 
    <!-- MyBatis Spring Boot Starter -->
    <dependency>
        <groupId></groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.1</version>
    </dependency>
 
    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
 
    <!-- Spring Boot Test -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

3. Configure multiple data sources

Configure two data sources in the file:

# Data Source 1.-url=jdbc:mysql://localhost:3306/db1?useSSL=false&amp;serverTimezone=UTC
.=root
.=password
.-class-name=
 
# Data Source 2.-url=jdbc:mysql://localhost:3306/db2?useSSL=false&amp;serverTimezone=UTC
.=root
.=password
.-class-name=

4. Create a data source configuration class

Create a configuration class to manage multiple data sources:

import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
 
import ;
 
@Configuration
@MapperScan(basePackages = "", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {
 
    @Bean(name = "datasource1")
    @ConfigurationProperties(prefix = ".datasource1")
    public DataSource dataSource1() {
        return ().build();
    }
 
    @Bean(name = "datasource2")
    @ConfigurationProperties(prefix = ".datasource2")
    public DataSource dataSource2() {
        return ().build();
    }
 
    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("datasource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        (dataSource);
        (new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return ();
    }
 
    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("datasource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

5. Dynamic data source switching

In order to achieve dynamic data source switching, we need to create a dynamic data source class and a section to manage the selection of data source:

5.1 Dynamic data source class

import ;
 
public class DynamicDataSource extends AbstractRoutingDataSource {
 
    @Override
    protected Object determineCurrentLookupKey() {
        return ();
    }
}

5.2 Data source context holder

public class DynamicDataSourceContextHolder {
 
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
    public static void setDataSourceKey(String key) {
        (key);
    }
 
    public static String getDataSourceKey() {
        return ();
    }
 
    public static void clearDataSourceKey() {
        ();
    }
}

5.3 Selection of data source for section management

import ;
import ;
import ;
 
@Aspect
@Component
public class DataSourceAspect {
 
    @Before("@annotation()")
    public void switchDataSource(JoinPoint point) {
        MethodSignature signature = (MethodSignature) ();
        TargetDataSource targetDataSource = ().getAnnotation();
        if (targetDataSource != null) {
            String dataSource = ();
            (dataSource);
        }
    }
}

5.4 Custom annotations

import .*;
 
@Target()
@Retention()
@Documented
public @interface TargetDataSource {
    String value();
}

6. Use examples

Suppose we have two database tables user and order located in db1 and db2, respectively, we can write the DAO layer like this:

6.1 UserMapper

import ;
import ;
import ;
 
@Mapper
public interface UserMapper {
 
    @Select("SELECT * FROM user WHERE id = #{id}")
    User getUserById(@Param("id") int id);
}

6.2 OrderMapper

import ;
import ;
import ;
 
@Mapper
public interface OrderMapper {
 
    @Select("SELECT * FROM order WHERE id = #{id}")
    Order getOrderById(@Param("id") int id);
}

6.3 Service layer

import ;
import ;
import ;
import ;
import ;
import ;
import ;
 
@Service
public class UserService {
 
    @Autowired
    private UserMapper userMapper;
 
    @Autowired
    private OrderMapper orderMapper;
 
    @TargetDataSource("datasource1")
    public User getUserById(int id) {
        return (id);
    }
 
    @TargetDataSource("datasource2")
    public Order getOrderById(int id) {
        return (id);
    }
}

Through the above steps, we successfully implemented the dynamic switching of MyBatis multi-data source in the Spring MVC project. This approach not only improves system flexibility, but also makes cross-database operations easier and more efficient. Hope this article will help you!

The above is a technical blog post about combining MyBatis to achieve dynamic switching of multiple data sources under the Spring MVC framework. Hope it helps you! sure!

Here is a simple example showingHow to use MyBatis to implement multi-data source switching in Spring MVC project. This example will include the following parts:

Configuration file: Define multiple data sources and corresponding SQLSessionFactory.

Customize data source switching strategy: Dynamically select data sources with an annotation.

Service layer: Use custom annotations to specify the data source.

Controller layer: Calling the Service layer method.

1. Configuration file

First, we need to configure multiple data sources and corresponding SQLSessionFactory in it.

&lt;beans xmlns="/schema/beans"
       xmlns:xsi="http:///2001/XMLSchema-instance"
       xmlns:context="/schema/context"
       xmlns:tx="/schema/tx"
       xsi:schemaLocation="/schema/beans
           /schema/beans/
           /schema/context
           /schema/context/
           /schema/tx
           /schema/tx/"&gt;
 
    &lt;!-- Data Source1 --&gt;
    &lt;bean  class="." destroy-method="close"&gt;
        &lt;property name="driverClassName" value=""/&gt;
        &lt;property name="url" value="jdbc:mysql://localhost:3306/db1"/&gt;
        &lt;property name="username" value="user1"/&gt;
        &lt;property name="password" value="password1"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- Data Source2 --&gt;
    &lt;bean  class="." destroy-method="close"&gt;
        &lt;property name="driverClassName" value=""/&gt;
        &lt;property name="url" value="jdbc:mysql://localhost:3306/db2"/&gt;
        &lt;property name="username" value="user2"/&gt;
        &lt;property name="password" value="password2"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- 动态Data Source --&gt;
    &lt;bean  class=""&gt;
        &lt;property name="targetDataSources"&gt;
            &lt;map key-type=""&gt;
                &lt;entry key="dataSource1" value-ref="dataSource1"/&gt;
                &lt;entry key="dataSource2" value-ref="dataSource2"/&gt;
            &lt;/map&gt;
        &lt;/property&gt;
        &lt;property name="defaultTargetDataSource" ref="dataSource1"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- SQLSessionFactory1 --&gt;
    &lt;bean  class=""&gt;
        &lt;property name="dataSource" ref="dataSource1"/&gt;
        &lt;property name="mapperLocations" value="classpath:mapper/db1/*.xml"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- SQLSessionFactory2 --&gt;
    &lt;bean  class=""&gt;
        &lt;property name="dataSource" ref="dataSource2"/&gt;
        &lt;property name="mapperLocations" value="classpath:mapper/db2/*.xml"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- SqlSessionTemplate1 --&gt;
    &lt;bean  class=""&gt;
        &lt;constructor-arg index="0" ref="sqlSessionFactory1"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- SqlSessionTemplate2 --&gt;
    &lt;bean  class=""&gt;
        &lt;constructor-arg index="0" ref="sqlSessionFactory2"/&gt;
    &lt;/bean&gt;
 
    &lt;!-- Transaction Manager --&gt;
    &lt;bean  class=""&gt;
        &lt;property name="dataSource" ref="dynamicDataSource"/&gt;
    &lt;/bean&gt;
 
    &lt;tx:annotation-driven transaction-manager="transactionManager"/&gt;
 
    &lt;!-- scanningServicelayer --&gt;
    &lt;context:component-scan base-package=""/&gt;
 
&lt;/beans&gt;

2. Customize the data source switching policy

Create a dynamic data source class DynamicDataSource and define an annotation @TargetDataSource to specify the data source.

package ;
 
import ;
 
public class DynamicDataSource extends AbstractRoutingDataSource {
 
    @Override
    protected Object determineCurrentLookupKey() {
        return ();
    }
}
 
package ;
 
import .*;
 
@Target()
@Retention()
@Documented
public @interface TargetDataSource {
    String value();
}

Create a thread-local variable DataSourceContextHolder to save the current data source.

package ;
 
public class DataSourceContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
    public static void setDataSource(String dataSource) {
        (dataSource);
    }
 
    public static String getDataSource() {
        return ();
    }
 
    public static void clearDataSource() {
        ();
    }
}

3. Service layer

Use the @TargetDataSource annotation in the Service layer to specify the data source.

package ;
 
import ;
import ;
import ;
import ;
 
@Service
public class UserService {
 
    @Autowired
    private UserMapper userMapper;
 
    @Transactional
    @TargetDataSource("dataSource1")
    public void addUserToDB1(User user) {
        (user);
    }
 
    @Transactional
    @TargetDataSource("dataSource2")
    public void addUserToDB2(User user) {
        (user);
    }
}

4. Controller layer

Call the Service layer method in the Controller layer.

package ;
 
import ;
import ;
import .*;
 
@RestController
@RequestMapping("/users")
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @PostMapping("/db1")
    public String addUserToDB1(@RequestBody User user) {
        userService.addUserToDB1(user);
        return "User added to DB1";
    }
 
    @PostMapping("/db2")
    public String addUserToDB2(@RequestBody User user) {
        userService.addUserToDB2(user);
        return "User added to DB2";
    }
}

5. Mapper interface and XML files

Define the Mapper interface and the corresponding XML file.

package ;
 
import ;
import ;
import ;
 
@Mapper
public interface UserMapper {
 
    @Insert("INSERT INTO users (name, email) VALUES (#{name}, #{email})")
    void insertUser(User user);
}

Define SQL maps in src/main/resources/mapper/db1/ and src/main/resources/mapper/db2/.

<!-- src/main/resources/mapper/db1/ -->
<mapper namespace="">
    <insert  parameterType="">
        INSERT INTO users (name, email) VALUES (#{name}, #{email})
    </insert>
</mapper>
 
<!-- src/main/resources/mapper/db2/ -->
<mapper namespace="">
    <insert  parameterType="">
        INSERT INTO users (name, email) VALUES (#{name}, #{email})
    </insert>
</mapper>

6. Entity Class

Define a simple entity class User.

package ;
 
public class User {
    private String name;
    private String email;
 
    // Getters and Setters
}

The above code shows how to use MyBatis to implement multi-data source switching in Spring MVC project. With custom annotations and dynamic data source classes, you can switch different data sources as needed at runtime. Hope this example helps you! If you have any questions or need further assistance, feel free to let me know. When using the Spring MVC framework in Java project in combination with MyBatis for development, if you need to handle data source switching of multiple databases, you can implement it by configuring Spring's AbstractRoutingDataSource.

The following is a detailed step and sample code to showHow to implement multi-data source switching in a Spring MVC + MyBatis project。

1. Add dependencies

First, add Spring, MyBatis and related dependencies to your file:

<dependencies>
    <!-- Spring MVC -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>5.3.10</version>
    </dependency>
 
    <!-- MyBatis -->
    <dependency>
        <groupId></groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.4</version>
    </dependency>
 
    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>
 
    <!-- HikariCP Connection Pool -->
    <dependency>
        <groupId></groupId>
        <artifactId>HikariCP</artifactId>
        <version>4.0.3</version>
    </dependency>
</dependencies>

2. Configure the data source

Create a configuration class to define multiple data sources and implement dynamic switching of data sources through AbstractRoutingDataSource.

import ;
import ;
import ;
import ;
import ;
import ;
import ;
 
import ;
import ;
import ;
 
@Configuration
@MapperScan("")
public class DataSourceConfig {
 
    @Bean
    public DataSource dataSource() {
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        ("dataSource1", dataSource1());
        ("dataSource2", dataSource2());
        (targetDataSources);
        (dataSource1());
        return dynamicDataSource;
    }
 
    @Bean
    public DataSource dataSource1() {
        HikariDataSource dataSource = new HikariDataSource();
        ("");
        ("jdbc:mysql://localhost:3306/db1");
        ("root");
        ("password");
        return dataSource;
    }
 
    @Bean
    public DataSource dataSource2() {
        HikariDataSource dataSource = new HikariDataSource();
        ("");
        ("jdbc:mysql://localhost:3306/db2");
        ("root");
        ("password");
        return dataSource;
    }
 
    @Bean
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        (dataSource);
        return ();
    }
 
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

3. Implement dynamic data source switching

Create a class inherited from AbstractRoutingDataSource to manage the switching logic of the data source.

import ;
 
public class DynamicDataSource extends AbstractRoutingDataSource {
 
    @Override
    protected Object determineCurrentLookupKey() {
        return ();
    }
}

4. Data source context management

Create a thread-safe context manager to save the data source keys used by the current thread.

public class DataSourceContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
 
    public static void setDataSourceKey(String key) {
        (key);
    }
 
    public static String getDataSourceKey() {
        return ();
    }
 
    public static void clearDataSourceKey() {
        ();
    }
}

5. Switch data source

Where you need to switch the data source, call the DataSourceContextHolder method to set the data source used by the current thread.

@RestController
public class UserController {
 
    @Autowired
    private UserMapper userMapper;
 
    @GetMapping("/users1")
    public List<User> getUsersFromDataSource1() {
        ("dataSource1");
        List<User> users = ();
        ();
        return users;
    }
 
    @GetMapping("/users2")
    public List<User> getUsersFromDataSource2() {
        ("dataSource2");
        List<User> users = ();
        ();
        return users;
    }
}

6. Mapper interface

Defines the Mapper interface of MyBatis to access the database.

public interface UserMapper {
    List<User> selectAll();
}

7. XML mapping file

Create an XML mapping file in the resources/mapper directory, for example.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/">
<mapper namespace="">
    <select  resultType="">
        SELECT * FROM user
    </select>
</mapper>

This article shows how to implement multi-data source switching in Spring MVC + MyBatis project. In this way, you can flexibly switch data sources in different requests or business logic.

The above is the detailed content of SpringMVC+MyBatis to realize multi-data source switching. For more information about SpringMVC MyBatis multi-data source switching, please pay attention to my other related articles!