This article has been included in my Github selection, welcome to Star: github.com/yehongzhi/l…

Ask questions

In daily development, it is often encountered that multiple data sources need to be used in A project. For example, one part of the data is in data source A and the other part is in data source B, and the business needs to merge the data of the two parts and return it through the interface.

Alternatively, after data source A is operated, you need to switch data source to operate data source B. How to realize such demand?

To solve the problem

In fact, there is a relevant implementation in Mybatis – Plus, is a fast integration of multi-data source launcher based on SpringBoot.

First of all, we need to build a springBoot+Mybatis+Mybatis-Plus project, which will not be demonstrated, relatively simple. Here is how to use multiple data sources, first introduce dynamic-datasource-spring-boot-starter.

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>${version}</version><! -- Version number -->
</dependency>
Copy the code

Second, modify the application configuration file to configure the data source.

# default data source
spring.datasource.dynamic.primary=master
# data source A
spring.datasource.dynamic.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.master.url=JDBC: mysql: / / 192.168.0.101:3306 / user? createDatabaseIfNotExist=true
spring.datasource.dynamic.datasource.master.username=root
spring.datasource.dynamic.datasource.master.password=
Data source B
spring.datasource.dynamic.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.dynamic.datasource.slave.url=JDBC: mysql: / / 192.168.0.102:3306 / user? createDatabaseIfNotExist=true
spring.datasource.dynamic.datasource.slave.username=root
spring.datasource.dynamic.datasource.slave.password=
Copy the code

Third, switch the data source using the @DS annotation. I like to put it on top of the Mapper interface. The value of the annotation is the name of the data source.

There are now two data sources, as shown:

The Mapper interface corresponding to the User table of the slave data source is as follows:

@Mapper
@DS("slave")
public interface UserMapper extends BaseMapper<User> {}Copy the code

The commodity table of the master data source corresponds to the Mapper interface as follows:

@Mapper
@DS("master")
public interface CommodityMapper extends BaseMapper<Commodity> {}Copy the code

Then we write a Service to test:

@Service
public class DynamicServiceImpl implements DynamicService {

    @Resource
    private UserMapper userMapper;

    @Resource
    private CommodityMapper commodityMapper;

    @Override
    public ResultObject getUserListAndCommodityList(a) {
        // Query the slave data source
        List<User> userList = userMapper.selectList(null);
        // Query the master data source
        List<Commodity> commodityList = commodityMapper.selectList(null);
        // Return the two data source queries together
        ResultObject resultObject = new ResultObject();
        resultObject.setUserList(userList);
        resultObject.setCommodityList(commodityList);
        returnresultObject; }}Copy the code

Controller interface, as follows:

@RestController
@RequestMapping("/dynamic")
public class DynamicController {

    @Resource
    private DynamicService dynamicService;

    @RequestMapping("/getUserListAndCommodityList")
    public ResultObject getUserListAndCommodityList(a) {
        returndynamicService.getUserListAndCommodityList(); }}Copy the code

Start the project, then request the interface address, we can see the result contains two data source query data:

Some doubt

Q1: Where can @DS annotations be used?

Look at the source code, you can know is used in class and method.

@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DS {
    /**
     * groupName or specific database name or spring SPEL name.
     *
     * @return the database you want to switch
     */
    String value(a);
}
Copy the code

It is usually marked on a Service or Mapper. Marking on methods takes precedence over marking on classes.

Question 2: Is multi-layer nested switching data sources supported?

What is a nested switch data source? Let’s look at an example:

@Service
@DS("slave")
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public List<User> getList(a) {
        return userMapper.selectList(null); }}Copy the code

@Service
@DS("master")
public class CommodityServiceImpl implements CommodityService {
    
    @Resource
    private UserService userService;
    
    @Override
    public ResultObject getUserAndCommodity(a) {
        // Switch to the slave data source and query the user table
        List<User> userList = userService.getList();
        // Switch back to the master data source and query commodity
        List<Commodity> commodityList = commodityMapper.selectList(null);
        ResultObject resultObject = new ResultObject();
        resultObject.setUserList(userList);
        resultObject.setCommodityList(commodityList);
        returnresultObject; }}Copy the code

@RestController
@RequestMapping("/dynamic")
public class DynamicController {

    @Resource
    private CommodityService commodityService;

    @RequestMapping("/getUserListAndCommodityList")
    public ResultObject getUserListAndCommodityList(a) {
        returncommodityService.getUserAndCommodity(); }}Copy the code

Start the project, then request the address, and discover that the result is still found, proving that it supports nested switching data sources.

Question 3: How do I use the @Transactional annotation to provide transaction support?

Let’s look at an example: First we define an insert object called InsertRequest:

public class InsertRequest {

    private User user;

    private Commodity commodity;
}
Copy the code

Then annotate the Service class with the @Transactional annotation and insert the data into the tables of the two data sources:

@Service
@Transactional
public class DynamicServiceImpl implements DynamicService {

    @Resource
    private UserMapper userMapper;

    @Resource
    private CommodityMapper commodityMapper;
    
    
    @Override
    public void insertUserAndCommodity(InsertRequest insertRequest) { commodityMapper.insert(insertRequest.getCommodity()); userMapper.insert(insertRequest.getUser()); }}Copy the code

Then add an entry method to the Controller interface layer:

@RequestMapping("/insertUserAndCommodity")
public String insertUserAndCommodity(@RequestParam(name = "name") String name,
                                     @RequestParam(name = "age") Integer age,
                                     @RequestParam("commodityName") String commodityName,
                                     @RequestParam("commodityPrice") String commodityPrice) {
    // Build the user object
    User user = new User();
    user.setName(name);
    user.setAge(age);
    // Build commodity objects
    Commodity commodity = new Commodity();
    commodity.setCommodityName(commodityName);
    commodity.setCommodityPrice(commodityPrice);
    // Build an insertRequest object
    InsertRequest insertRequest = new InsertRequest();
    insertRequest.setUser(user);
    insertRequest.setCommodity(commodity);
    // Call the insert method
    dynamicService.insertUserAndCommodity(insertRequest);
    return "success";
}
Copy the code

Start project, test, result is 500 error:

If we look at the console log, we can find the problem:

Since @Transactional is a local transaction management, not a distributed transaction, you cannot annotate methods that require switching data sources with @Transactional annotations.

Instead of using distributed transactions, we let them manage their own transactions, with an extra layer of Service in between.

@Service
@DS("master")
public class CommodityServiceImpl implements CommodityService {
    
    @Resource
    private CommodityMapper commodityMapper;
    
    @Override
    @Transactional
    public int insertOne(Commodity commodity) {
        returncommodityMapper.insert(commodity); }}@Service
@DS("slave")
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;
    
    @Override
    @Transactional
    public int insertOne(User user) {
        returnuserMapper.insert(user); }}Copy the code

@Service
public class DynamicServiceImpl implements DynamicService {
    @Resource
    private UserService userService;

    @Resource
    private CommodityService commodityService;
    
    @Override
    public void insertUserAndCommodity(InsertRequest insertRequest) { commodityService.insertOne(insertRequest.getCommodity()); userService.insertOne(insertRequest.getUser()); }}Copy the code

After that, we can test again and see that the data source switch is successful.

If you need to use distributed transactions, the official website also provides a distributed transaction solution based on SEATA, which will not be demonstrated here. Readers can try it for themselves.

Q4: Can two methods defined in the same Service correspond to different data sources call each other?

Let’s stick to the facts. Let’s start with the whole example:

@Override
@DS("slave")
public ResultObject getResultObject(a) {
    List<User> userList = userService.getList();
    // Call another method of the same class to switch the data source
    List<Commodity> commodityList = getCommodityList();
    ResultObject resultObject = new ResultObject();
    resultObject.setUserList(userList);
    resultObject.setCommodityList(commodityList);
    return resultObject;
}

@Override
@DS("master")
public List<Commodity> getCommodityList(a) {
    return commodityMapper.selectList(null);
}
Copy the code

Start project, test, find error:

The reason for this is that the underlying principle of data source switching is implemented using AOP, which is not used for internal method calls, so there is no way to switch data sources.

The solution is to refer the getCommodityList() method to another class.

The last

Dynamic-datasource has a lot of features that we haven’t covered yet. If you’re interested, go to the dynamic-datasource website. Multiple data sources are common in project development, so I found it useful to learn about this plug-in. If I have time later, I’ll read the dynamic-datasource source code to explore the underlying implementation.

Thank you very much for reading and I hope this article has been helpful and enlightening to you.

Please give me a thumbs-up if you think it is useful. Your thumbs-up is the biggest motivation for my creation

I’m a programmer who tries to be remembered. See you next time!!

Ability is limited, if there is any mistake or improper place, please criticize and correct, study together!