mysql+spring+mybatis实现数据库读写分离的代码配置

MySQL数据库读写分离是提高Web应用性能和可用性的重要手段之一。开发人员可以通过使用JDBC、Spring和MyBatis等技术实现MySQL数据库读写分离。

以下是实现数据库读写分离的完整攻略：

1. 安装和配置MySQL主从服务器

确保安装和配置了MySQL主从服务器，并确保主服务器和从服务器之间已正确配置了“主从同步”。可以考虑使用软件程序如MySQL官方文档中的Mysql_Master_Master或者Tungsten Replicator等，简化主从配置流程。

2. 配置MySQL主从服务器中的读写分离

• 针对主服务器，必须配置启用二进制日志记录功能，以支持同步数据到从服务器。
• 针对从服务器，可以使用read-only开关指定只读数据库，以防止从服务器对从服务器进行写入操作。

3. 配置Spring和MyBatis

在Spring和MyBatis配置文件中添加如下内容：

配置数据源

<bean id="dataSourceMaster" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${db.master.driverClassName}" />
    <property name="url" value="${db.master.url}" />
    <property name="username" value="${db.master.username}" />
    <property name="password" value="${db.master.password}" />
    .
    .
    .
</bean>

<bean id="dataSourceSlave" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${db.slave.driverClassName}" />
    <property name="url" value="${db.slave.url}" />
    <property name="username" value="${db.slave.username}" />
    <property name="password" value="${db.slave.password}" />
    .
    .
    .
</bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
    <property name="targetDataSources">
        <map>
            <entry key="master" value-ref="dataSourceMaster" />
            <entry key="slave" value-ref="dataSourceSlave" />
        </map>
    </property>
    <property name="defaultTargetDataSource" ref="dataSourceMaster" />
</bean>

配置SqlSessionFactoryBean

<bean id="sqlSessionFactoryMaster" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSourceMaster" />
    .
    .
    .
</bean>

<bean id="sqlSessionFactorySlave" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSourceSlave" />
    .
    .
    .
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="mapperLocations" value="classpath*:mapper/*.xml" />
</bean>

配置SqlSessionTemplate

<bean id="sqlSessionMaster" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactoryMaster" />
</bean>

<bean id="sqlSessionSlave" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactorySlave" />
</bean>

<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory" />
    <property name="defaultSqlSessionFactory" ref="sqlSessionFactoryMaster" />
    <property name="sqlSessionFactories">
        <map>
            <entry key="master" value-ref="sqlSessionFactoryMaster" />
            <entry key="slave" value-ref="sqlSessionFactorySlave" />
        </map>
    </property>
    <property name="defaultTargetSqlSessionFactory" ref="sqlSessionFactory" />
</bean>

编写Mapper

<mapper namespace="com.example.mapper.UserMapper">
    <select id="getUserById" resultMap="userResultMap" flushCache="false">
        <choose>
            <when test="'read'.equals($dataSourceType)">
                SELECT id, name FROM user_slave WHERE id = #{id}
            </when>
            <otherwise>
                SELECT id, name FROM user WHERE id = #{id}
            </otherwise>
        </choose>
    </select>
</mapper>

测试

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    @Transactional(readOnly = true)
    public User getUserById(long id) {
        // 打印当前数据源类型
        System.out.println("数据源类型: " + DataSourceContextHolder.getDataSourceKey());
        return userMapper.getUserById(id, DataSourceContextHolder.getDataSourceKey());
    }
}

完整的代码示例可以参考：

https://github.com/codingapi/spring-mybatis-read-write-separation

示例1：通过注解实现

@Service
public class UserService {

    @Autowired
    private UserMapper userMapperMaster;

    @Autowired
    private UserMapper userMapperSlave;

    /**
    * 添加用户
    */
    @Transactional
    public void addUser(User user) {
        userMapperMaster.insertUser(user);
    }

    /**
    * 根据ID获取用户
    */
    @DataSource(DataSourceType.SLAVE)
    @Transactional(readOnly = true)
    public User getUserById(long id) {
        return userMapperSlave.selectUserById(id);
    }

    /**
    * 更新用户
    */
    @Transactional
    public void updateUser(User user) {
        userMapperMaster.updateUser(user);
    }

    /**
    * 删除用户
    */
    @Transactional
    public void deleteUserById(long id) {
        userMapperMaster.deleteUserById(id);
    }
}

示例2：通过AOP实现

@Component
@Aspect
public class DataSourceAspect {

    @Before("execution(* com.example.service..*.select*(..)) " +
            "|| execution(* com.example.service..*.get*(..))")
    public void setReadDataSourceType() {
        DataSourceContextHolder.setDataSourceKey(DataSourceType.SLAVE);
    }

    @Before("execution(* com.example.service..*.insert*(..)) " +
            "|| execution(* com.example.service..*.update*(..)) " +
            "|| execution(* com.example.service..*.delete*(..))")
    public void setWriteDataSourceType() {
        DataSourceContextHolder.setDataSourceKey(DataSourceType.MASTER);
    }
}