正文

mybatis用spring的动态数据源实现读写分离

IP云
IP云V管理员/5 阅读
11/28
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一、环境：

三个mysql数据库。一个master，两个slaver。master写数据，slaver读数据。

二、原理：

借助Spring的 AbstractRoutingDataSource 这个抽象实现。我们要实现 determineCurrentLookupKey()这个方法来动态的选择使用哪个数据源操着数据库 

public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {

    protected abstract Object determineCurrentLookupKey();

}

三、实现步骤：

1、添加spring，mybatis，mysql相关的pom依赖。

2、写jdbc.properties，定义三个数据库。 

jdbc.driver=com.mysql.jdbc.Driver

#master库

master.jdbc.url=jdbc:mysql://127.0.0.1:3306/master?characterEncoding=utf8

master.jdbc.user=root

master.jdbc.password=tiger

#slave 一 库

slave.one.jdbc.url=jdbc:mysql://127.0.0.1:3306/slave-one?characterEncoding=utf8

slave.one.jdbc.user=root

slave.one.jdbc.password=tiger

#slave 二 库

slave.two.jdbc.url=jdbc:mysql://127.0.0.1:3306/slave-two?characterEncoding=utf8

slave.two.jdbc.user=root

slave.two.jdbc.password=tiger

3、配置三个数据源，分别写到三个配置文件中。

datasources-master.xml、datasource-slave-one.xml和datasource-slave-two.xml三个文件都一样，这里就写一个 

<!--master数据源，支持读写-->

    <bean id="dataSourceMaster" class="com.alibaba.druid.pool.DruidDataSource"

          init-method="init" destroy-method="close">

        <property name="driverClassName" value="${jdbc.driver}"/>

        <property name="url" value="${master.jdbc.url}"/>

        <property name="username" value="${master.jdbc.user}"/>

        <property name="password" value="${master.jdbc.password}"/>

        <property name="filters" value="stat"/>

        <property name="maxActive" value="20"/>

        <property name="initialSize" value="1"/>

        <property name="maxWait" value="60000"/>

        <property name="minIdle" value="1"/>

        <property name="timeBetweenEvictionRunsMillis" value="60000"/>

        <property name="minEvictableIdleTimeMillis" value="300000"/>

        <property name="validationQuery" value="SELECT 'x'"/>

        <property name="testWhileIdle" value="true"/>

        <property name="testOnBorrow" value="false"/>

        <property name="testOnReturn" value="false"/>

    </bean>

4、写spring的配置文件applicationContext.xml。 

    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

        <property name="locations">

            <list>

                <value>classpath*:jdbc.properties</value>

            </list>

        </property>

        <property name="fileEncoding" value="UTF-8" />

        <property name= "ignoreResourceNotFound" value="false"/>

    </bean>

    <context:component-scan base-package="org.hope.lee"/>

    <aop:aspectj-autoproxy/>

    <!--spring的路由来管理数据源-->

    <bean id="dynamicDataSource" class="org.hope.lee.utils.DynamicDataSource">

        <property name="targetDataSources">

            <map>

                <entry value-ref="dataSourceMaster" key="db_master"/>

                <entry value-ref="dataSourceSlaveOne" key="db_slave_one"/>

                <entry value-ref="dataSourceSlaveTwo" key="db_slave_two"/>

            </map>

        </property>

    </bean>

    <!--spring-mybatis整合-->

    <bean id="dynamicsqlSessionFactory"

          class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dynamicDataSource"/>

        <property name="mapperLocations">

            <array>

                <value>classpath:mappers/*Mapper.xml</value>

            </array>

        </property>

        <property name="configLocation" value="classpath:mybatis-config.xml"/>

        <property name="typeAliasesPackage" value="org.hope.lee.model"/>

    </bean>

    <!--自动扫描所有的Mapper接口与文件-->

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

        <property name="basePackage" value="org.hope.lee.dao"></property>

        <property name="sqlSessionFactoryBeanName" value="dynamicsqlSessionFactory"></property>

    </bean>

    <!--配置事务-->

    <bean id="transactionManager"

          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">

        <property name="dataSource" ref="dynamicDataSource"/>

    </bean>

    <!--开启注解事务-->

    <tx:annotation-driven transaction-manager="transactionManager"/>

    <import resource="classpath:datasource-master.xml"/>

    <import resource="classpath:datasource-slave-one.xml"/>

    <import resource="classpath:datasource-slave-two.xml"/>

5、新建DBContextHolder，DBType为动态设置数据库的util 

package org.hope.lee.utils;

public class DBType {

    public final static String DB_TYPE_MASTER = "db_master";

    public final static String DB_TYPE_SLAVE_ONE = "db_slave_one";

    public final static String DB_TYPE_SLAVE_TWO = "db_slave_two";

}
package org.hope.lee.utils;

public class DBContextHolder {

    private static ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    public static String getDBType() {

        String db = contextHolder.get();

        if(db == null) {

            db = DBType.DB_TYPE_MASTER; //默认是master库

        }

        return db;

    }

    public static void setDBType(String dbType) {

        contextHolder.set(dbType);

    }

    public static void clearDBType() {

        contextHolder.remove();

    }

}

6、继承Spring的 AbstractRoutingDataSource 来动态的进行数据库路由 

package org.hope.lee.utils;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override

    protected Object determineCurrentLookupKey() {

        return DBContextHolder.getDBType();

    }

}

7、创建三个数据库master、slave-one、slave-two。三个库建同一张user表进行测试。 

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

8、写mybatis的dao层,service层,model层 

package org.hope.lee.model;

public class User {

    private int id;

    private String name;

    setters()&getters()

}
package org.hope.lee.dao;

import org.hope.lee.model.User;

import org.springframework.stereotype.Repository;

@Repository

public interface UserMapper {

    void insert(User user);

    User selectOne(int id);

}
<?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="org.hope.lee.dao.UserMapper">

    <resultMap id="usersResultMap" type="User">

        <id column="id" property="id" javaType="Integer" />

        <result column="name" property="name" />

    </resultMap>

    <insert id="insert" useGeneratedKeys="true" parameterType="User">

        INSERT INTO `user`(name) VALUES(#{name, jdbcType=VARCHAR});

    </insert>

    <select id="selectOne" resultMap="usersResultMap" parameterType="int" >

        SELECT id, name FROM `user` WHERE id=#{id, jdbcType=INTEGER}

    </select>

</mapper>
package org.hope.lee.service;

import org.hope.lee.dao.UserMapper;

import org.hope.lee.model.User;

import org.hope.lee.utils.DBContextHolder;

import org.hope.lee.utils.DBType;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;

@Service("userService")

public class UserServiceImpl implements UserService {

    @Autowired

    private UserMapper userMapper;

    public void addUser(User user) {

        //设置数据库

        DBContextHolder.setDBType(DBType.DB_TYPE_MASTER);

        userMapper.insert(user);

    }

    public User getUserById(int id) {

       //设置数据库，单元测试的时候自己手动修改一下，看看效果       
　　　　DBContextHolder.setDBType(DBType.DB_TYPE_SLAVE_ONE);

       return userMapper.selectOne(id);

    }

}

9、单元测试。 

import org.hope.lee.model.User;

import org.hope.lee.service.UserService;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = {"classpath*:applicationContext.xml"})

public class UserServiceTest {

    @Autowired

    private UserService userService;

    @Test

    public void addUserTest() {

        User user = new User();

        user.setName("马云");

        userService.addUser(user);

    }

    @Test

    public void getUserOneTest() {

        int id = 1;

        User u = userService.getUserById(id);

        System.out.println(u.getName());

    }

}

10、在service层修改 DBContextHolder.setDBType()来看看效果。

四、遇到的问题：

1、遇到Spring的PropertyPlaceholderConfigurer不起效果，在数据源配置的${jdbc.driver}中获取不到jdbc.properties中的值。 

<bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">

      <property name="locations">

          <list>

              <value>classpath*:jdbc.properties</value>

          </list>

      </property>

      <property name="fileEncoding" value="UTF-8" />

      <property name= "ignoreResourceNotFound" value="false"/>

</bean>

解决： 

<!--
　　原来id的名称是sqlSessionFactory,但是在spring里使用org.mybatis.spring.mapper.MapperScannerConfigurer

   进行自动扫描的时候，设置了sqlSessionFactory 的话，他会优先于PropertyPlaceholderConfigurer执行。

   从而导致PropertyPlaceholderConfigurer失效，

   这时在xml中用${url}、${username}、${password}等这样之类的表达式，

   将无法获取到properties文件里的内容。
-->

    <bean id="dynamicsqlSessionFactory"

          class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="dynamicDataSource"/>

        <property name="mapperLocations">

            <array>

                <value>classpath:mappers/*Mapper.xml</value>

            </array>

        </property>

        <property name="configLocation" value="classpath:mybatis-config.xml"/>

        <property name="typeAliasesPackage" value="org.hope.lee.model"/>

    </bean>

六、还有一种方式是使用mysql自带的replicationDriver来实现读写分离。大家自己也可以试试

http://blog.csdn.net/lixiucheng005/article/details/17391857

https://gitee.com/huayicompany/mybatis-learn/tree/master/separated-read-write

参考：

[1] 博客，http://blog.csdn.net/xtj332/article/details/43953699

[2] 博客，http://blog.csdn.net/keda8997110/article/details/16827215

相关阅读

目录[+]