您的位置: 首页  >  文章  >  Spring Boot+Mybatis+sharding-jdbc实现数据库读写分离

Spring Boot+Mybatis+sharding-jdbc实现数据库读写分离

分类: 文章 • 2024-07-13 12:20:04

前言

  本文主要分享spring boot工程使用mybatis和sharding-jdbc实现mysql数据库的读写分离。
  本文demo工程已上传github：https://github.com/hubSKH/sharding-jdbc-demo
  关于mysql数据库主从复制设置，可以参考我另外一篇文章：https://blog.****.net/u012786993/article/details/89201161
  sharding-jdbc更多介绍与用法，可以上官网查阅相关文档：http://shardingsphere.apache.org/index_zh.html

工程版本

框架 版本
spring boot 2.0.3.RELEASE(或者1.5.18.RELEASE)
mybatis 1.3.2
sharding-jdbc 3.0.0.M3

  这里，使用的sharding-jdbc是3.X版本，pom配置：

	<sharding-sphere.version>3.0.0.M3</sharding-sphere.version>

	<!-- for spring boot -->
	<dependency>
	    <groupId>io.shardingsphere</groupId>
	    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	    <version>${sharding-sphere.version}</version>
	</dependency>
	
	<!-- for spring namespace -->
	<dependency>
	    <groupId>io.shardingsphere</groupId>
	    <artifactId>sharding-jdbc-spring-namespace</artifactId>
	    <version>${sharding-sphere.version}</version>
	</dependency>

  mybatis使用1.3.2版本，pom：

<!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

  spring boot的版本最好是1.5.x或以上，本次以1.5.18.RELEASE为例，pom：

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.18.RELEASE</version>
        <!-- <version>2.0.3.RELEASE</version> -->
    </parent>

  本文使用的是spring boot集成sharding-jdbc，官网的数据源配置是写在propertis文件中，而本文配置写在yml文件中也是同样效果，yml如下：

server:
  port: 9090

spring:
  application:
    name: sharding_jdbc

#sharding-jdbc的配置
sharding.jdbc:
  datasource:
    names: ds_master,ds_slave_0,ds_slave_1
    ds_master:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/db1
      username: root
      password: root
    ds_slave_0:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/db0
      username: root
      password: root
    ds_slave_1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/db2
      username: root
      password: root
  config:
    masterslave:
      name: ds_ms
      master-data-source-name: ds_master
      slave-data-source-names: ds_slave_0,ds_slave_1
      load-balance-algorithm-type: round_robin
  props:
    sql.show: true
#mybatis的配置
mybatis:
  config-location: classpath:mybatis/config.xml
  mapper-locations:
  - classpath:mybatis/mappers/*.xml

  这里我配置的是一主两从，目的是为了验证数据库查询的负载均衡算法round_robin，即轮询。
  代码中需要配置读取配置文件的类以及设置数据源datasource相关参数，读取类如下：

@Data
@ConfigurationProperties(prefix = "sharding.jdbc")
public class ShardingMasterSlaveConfig {

    private Map<String, DruidDataSource> dataSources = new HashMap<> ();

    private MasterSlaveRuleConfiguration masterSlaveRule;

}

  配置数据源类如下：

@Slf4j
@Configuration
@EnableConfigurationProperties(ShardingMasterSlaveConfig.class)
@ConditionalOnProperty({"sharding.jdbc.data-sources.ds_master.url", "sharding.jdbc.master-slave-rule.master-data-source-name"})
public class ShardingDataSourceConfig {
    private final static Logger log = LoggerFactory.getLogger (ShardingDataSourceConfig.class);
    @Autowired(required = false)
    private ShardingMasterSlaveConfig shardingMasterSlaveConfig;

    @Bean("dataSource")
    public DataSource masterSlaveDataSource() throws SQLException {
        shardingMasterSlaveConfig.getDataSources().forEach((k, v) -> configDataSource(v));
        Map<String, DataSource> dataSourceMap = Maps.newHashMap();
        dataSourceMap.putAll(shardingMasterSlaveConfig.getDataSources());
        DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, shardingMasterSlaveConfig.getMasterSlaveRule(),  new HashMap<String, Object> (), new Properties ());
        log.info("masterSlaveDataSource config complete");
        return dataSource;
    }

    private void configDataSource(DruidDataSource druidDataSource) {
        druidDataSource.setMaxActive(20);
        druidDataSource.setInitialSize(1);
        druidDataSource.setMaxWait(60000);
        druidDataSource.setMinIdle(1);
        druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
        druidDataSource.setMinEvictableIdleTimeMillis(300000);
        druidDataSource.setValidationQuery("select 'x'");
        druidDataSource.setTestWhileIdle(true);
        druidDataSource.setTestOnBorrow(false);
        druidDataSource.setTestOnReturn(false);
        druidDataSource.setPoolPreparedStatements(true);
        druidDataSource.setMaxOpenPreparedStatements(20);
        druidDataSource.setUseGloalDataSourceStat(true);

        try {
            druidDataSource.setFilters("stat,wall,slf4j");
        } catch (SQLException e) {
            log.error("druid configuration initialization filter", e);
        }
    }
}

  mybatis的config.xml配置。注意：这里注意用了配置文件，就不能再application里面配置configlcation属性。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!--配置命名规则-->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
    <typeAliases>
        <typeAlias alias="Integer" type="java.lang.Integer" />
        <typeAlias alias="Long" type="java.lang.Long" />
        <typeAlias alias="HashMap" type="java.util.HashMap" />
        <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
        <typeAlias alias="ArrayList" type="java.util.ArrayList" />
        <typeAlias alias="LinkedList" type="java.util.LinkedList" />
    </typeAliases>
</configuration>

  然后配置spring boot启动类


@SpringBootApplication
@MapperScan(basePackages = "com.skh.dao")
public class Application  extends WebMvcConfigurerAdapter {

    public static void main(String[] args) throws Exception {
        SpringApplication.run(Application.class, args);
    }

}

  数据库表对象映射类（po）

@Data
public class User implements Serializable {
    private  Integer id;

    private  String name;

    private  Integer age;
}

  dao层接口类

@Mapper
public interface UserMapper {
    int insert(User record);

    User selectByPrimaryKey(int id);
}

  对应UserMapper.xml配置，xml文件可通过mybatis generator工具生成。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.skh.dao.UserMapper">
    <resultMap id="BaseResultMap" type="com.skh.po.User">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="INTEGER" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
    </resultMap>
    <sql id="Base_Column_List">
        id, name, age
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from user
        where id = #{id,jdbcType=INTEGER}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id,jdbcType=INTEGER}
    </delete>
    <insert id="insert" parameterType="com.skh.po.User">
        insert into user (id, name, age)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=INTEGER}, #{age,jdbcType=INTEGER}
        )
    </insert>
    <insert id="insertSelective" parameterType="com.skh.po.User">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="age != null">
                age,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name != null">
                #{name,jdbcType=INTEGER},
            </if>
            <if test="age != null">
                #{age,jdbcType=INTEGER},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="com.skh.po.User">
        update user
        <set>
            <if test="name != null">
                name = #{name,jdbcType=INTEGER},
            </if>
            <if test="age != null">
                age = #{age,jdbcType=INTEGER},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="com.skh.po.User">
        update user
        set name = #{name,jdbcType=INTEGER},
        age = #{age,jdbcType=INTEGER},
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>

  接下来是service层接口类

public interface UserService {

    User getUser(int id);

    Integer saveUser(User user);
}

  service的实现类

@Service
public class UserServiceImpl implements UserService {
    /**
     * 注入数据接口
     */
    @Autowired
    private UserMapper vtsUserMapper;


    @Override
    public User getUser(int id) {
        User user = vtsUserMapper.selectByPrimaryKey(id);
        System.out.println(user);
        return user;
    }

    @Override
    public Integer saveUser(User user) {
        vtsUserMapper.insert(user);
        return user.getId ();
    }

}

  最后是controller类

@RestController
@RequestMapping("user")
public class UserController {

    @Resource
    private UserService userService;

    @RequestMapping("select")
    public User getuser(@RequestParam("id")int id){

        return userService.getUser (id);

    }
}

  运行application的main方法启动工程后，即可通过接口测试工具，验证读写分离配置。
Spring Boot+Mybatis+sharding-jdbc实现数据库读写分离
  为了更好体现出数据的读写分离，从库中的数据改变数据值以做区分。从下图多次调用可以看出，配置的读写分离以生效，读取操作不会使用主数据库数据源，而且数据读取也按照轮询的从库访问策略使用从库数据源。
Spring Boot+Mybatis+sharding-jdbc实现数据库读写分离

参考文献

1、https://blog.****.net/zhuwei_clark/article/details/82898497

相关推荐