Fading Coder

One Final Commit for the Last Sprint

Home > Notes > Content

Spring Boot MyBatis with Two MySQL DataSources Using Druid

Notes 2
  • Required dependencies
  • application.properties: define two data sources and poooling
  • Java configuration for both data sources
  • MyBatis mappers for each data source
  • Controller endpoints to verify both connecsions
  • Sample project layout and test URLs

Dependencies

<!-- MySQL JDBC driver -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

<!-- Druid connection pool -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.13</version>
</dependency>

<!-- MyBatis Spring Boot starter -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>2.1.0</version>
</dependency>

application.properties

# ========== master (test) ==========
spring.datasource.master.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.datasource.master.username=root
spring.datasource.master.password=root
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
# Druid-specific settings for master
spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.master.initialSize=2
spring.datasource.master.minIdle=1
spring.datasource.master.maxActive=20
spring.datasource.master.maxWait=60000

# ========== replica (cbh) ==========
spring.datasource.replica.url=jdbc:mysql://127.0.0.1:3306/cbh?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
spring.datasource.replica.username=root
spring.datasource.replica.password=root
spring.datasource.replica.driver-class-name=com.mysql.cj.jdbc.Driver
# Druid-specific settings for replica
spring.datasource.replica.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.replica.initialSize=2
spring.datasource.replica.minIdle=1
spring.datasource.replica.maxActive=20
spring.datasource.replica.maxWait=60000

Java Configuration

Master data source (test)

package io.acme.multids.config;

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "io.acme.multids.repo.master", sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterDataSourceConfig {

  @Bean(name = "masterDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.master")
  @Primary
  public DataSource masterDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean(name = "masterSqlSessionFactory")
  @Primary
  public SqlSessionFactory masterSqlSessionFactory(
      @Qualifier("masterDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource);
    return factory.getObject();
  }

  @Bean(name = "masterTxManager")
  @Primary
  public DataSourceTransactionManager masterTxManager(
      @Qualifier("masterDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean(name = "masterSqlSessionTemplate")
  @Primary
  public SqlSessionTemplate masterSqlSessionTemplate(
      @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

Replica data source (cbh)

package io.acme.multids.config;

import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

@Configuration
@MapperScan(basePackages = "io.acme.multids.repo.replica", sqlSessionTemplateRef = "replicaSqlSessionTemplate")
public class ReplicaDataSourceConfig {

  @Bean(name = "replicaDataSource")
  @ConfigurationProperties(prefix = "spring.datasource.replica")
  public DataSource replicaDataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean(name = "replicaSqlSessionFactory")
  public SqlSessionFactory replicaSqlSessionFactory(
      @Qualifier("replicaDataSource") DataSource dataSource) throws Exception {
    SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
    factory.setDataSource(dataSource);
    return factory.getObject();
  }

  @Bean(name = "replicaTxManager")
  public DataSourceTransactionManager replicaTxManager(
      @Qualifier("replicaDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
  }

  @Bean(name = "replicaSqlSessionTemplate")
  public SqlSessionTemplate replicaSqlSessionTemplate(
      @Qualifier("replicaSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }
}

MyBatis Mappers

Domain model

package io.acme.multids.model;

public class User {
  private Long id;
  private String name;
  private String phone;
  private String uuid;
  private String passwordHash;

  public Long getId() { return id; }
  public void setId(Long id) { this.id = id; }
  public String getName() { return name; }
  public void setName(String name) { this.name = name; }
  public String getPhone() { return phone; }
  public void setPhone(String phone) { this.phone = phone; }
  public String getUuid() { return uuid; }
  public void setUuid(String uuid) { this.uuid = uuid; }
  public String getPasswordHash() { return passwordHash; }
  public void setPasswordHash(String passwordHash) { this.passwordHash = passwordHash; }
}

Mapper targeting the master database

package io.acme.multids.repo.master;

import io.acme.multids.model.User;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {
  @Select("SELECT id, name, phone FROM t_user")
  List<User> findAll();
}

Mapper targeting the replica database

package io.acme.multids.repo.replica;

import io.acme.multids.model.User;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface ReplicaUserMapper {
  @Select("SELECT id, name, phone, uuid, login_pwd FROM t_user")
  @Results({
    @Result(column = "login_pwd", property = "passwordHash")
  })
  List<User> loadAll();
}

Controller

package io.acme.multids.web;

import io.acme.multids.model.User;
import io.acme.multids.repo.master.UserMapper;
import io.acme.multids.repo.replica.ReplicaUserMapper;
import java.util.List;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/api")
public class UserController {

  private final UserMapper masterMapper;
  private final ReplicaUserMapper replicaMapper;

  public UserController(UserMapper masterMapper, ReplicaUserMapper replicaMapper) {
    this.masterMapper = masterMapper;
    this.replicaMapper = replicaMapper;
  }

  @GetMapping("/master/users")
  public List<User> masterUsers() {
    return masterMapper.findAll();
  }

  @GetMapping("/replica/users")
  public String replicaCount() {
    int size = replicaMapper.loadAll().size();
    return "recordCount=" + size;
  }
}

Project structure (example)

src
 └─ main
    ├─ java
    │   └─ io
    │       └─ acme
    │           └─ multids
    │               ├─ config
    │               │   ├─ MasterDataSourceConfig.java
    │               │   └─ ReplicaDataSourceConfig.java
    │               ├─ model
    │               │   └─ User.java
    │               ├─ repo
    │               │   ├─ master
    │               │   │   └─ UserMapper.java
    │               │   └─ replica
    │               │       └─ ReplicaUserMapper.java
    │               └─ web
    │                   └─ UserController.java
    └─ resources
        └─ application.properties

Test URLs

  • Master database: http://localhost:8080/api/master/users
  • Replica database: http://localhost:8080/api/replica/users
Tags: spring-bootmybatisMySQLdruid
Back to List

Prev: Spring Boot and RabbitMQ: JSON Serialization for Producers and Listeners

Next: Spring MVC Controllers, Bean Scopes, and Thread Safety Under Concurrency

Related Articles

Designing Alertmanager Templates for Prometheus Notifications

This guide explains how to craft Alertmanager templates to format alert messages, improving clarity and presentation. Alertmanager uses Go’s text/template engine with additional helper functions. Ale...

Deploying a Maven Web Application to Tomcat 9 Using the Tomcat Manager

Tomcat 9 does not provide a dedicated Maven plugin. The Tomcat Manager interface, however, is backward-compatible, so the Tomcat 7 Maven Plugin can be used to deploy to Tomcat 9. This guide shows two...

Skipping Errors in MySQL Asynchronous Replication

When a replica halts because the SQL thread encounters an error, you can resume replication by skipping the problematic event(s). Two common approaches are available. Methods to Skip Errors 1) Skip a...

Leave a Comment

Anonymous

◎Feel free to join the discussion and share your thoughts.

Copyright © fadingcoder.top