spring boot + mybatis 多数据源，mysql服务主从读写分离

在后台服务中对mysql采用主从复制，采用读写分离，这样可以大大减轻mysql的压力，当然这种操作需要实时性要求不高，mysql主从服务存在一定的延时

一、项目结构图：

二、application.properties

spring.jpa.database=mysql datasource.master.url=jdbc:mysql://192.168.199.235:3306/lovewhf?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC datasource.master.driverClassName=com.mysql.jdbc.Driver datasource.master.username=root datasource.master.password=root datasource.slave.url=jdbc:mysql://localhost:3306/lovewhf?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC datasource.slave.driverClassName=com.mysql.jdbc.Driver datasource.slave.username=root datasource.slave.password=root

三、主从配置类

package com.mjlf.manyjpa.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; /** *mysql主服务配置类 *需要注意的是MapperScan中backage包地址一定要对应上主服务的mapper接口包 *@ConfigurationProperties("datasource.master")对应的是主服务器配置，它在application.properties中有对应的值配置 *sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath*:master/*.xml")); 这个是配置编写sql配置文件的位置，该地址千万不能写错 */ import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.mjlf.manyjpa.mapper.master",sqlSessionFactoryRef = "masterSqlSessionFactory") public class MasterDataSourceConfig { @Bean(name = "masterDataSource") @ConfigurationProperties("datasource.master") public DataSource masterDataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "masterSqlSessionFactory") public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean(); sessionFactoryBean.setDataSource(dataSource); sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath*:master/*.xml")); return sessionFactoryBean.getObject(); } } package com.mjlf.manyjpa.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; /** *mysql从服务器配置 *描述和主服务器类 */ import javax.sql.DataSource; @Configuration @MapperScan(basePackages = "com.mjlf.manyjpa.mapper.slave",sqlSessionFactoryRef = "slaveSqlSessionFactory") public class SlaveSourceConfig { @Bean(name = "slaveDataSource") @ConfigurationProperties("datasource.slave") public DataSource slaveDataSource(){ return DataSourceBuilder.create().build(); } @Bean(name = "slaveSqlSessionFactory") public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean(); sessionFactoryBean.setDataSource(dataSource); sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver() .getResources("classpath*:slave/*.xml")); return sessionFactoryBean.getObject(); } }

四、xml文件配置

<?xml version="1.0" encoding="UTF-8"?> <!-- 这是主服务器写相关配置 此处需要注意的是不能写错了namespace，各个sql语句对应的id必须要在mapper接口类中有方法对应， 否则会抛出异常。还有在这个项目中还不知道去掉xml配置文件而直接采用@注入的方式，如果读者知道，可留意，谢谢！ --> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD com.example.Mapper 3.4//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mjlf.manyjpa.mapper.master.LoveMasterMapper"> <resultMap id="master" type="com.mjlf.manyjpa.entity.Love"> <result property="id" column="id"/> <result property="name" column="name"/> </resultMap> <insert id="slave" parameterType="com.mjlf.manyjpa.entity.Love"> INSERT INTO love(id, name) values(#{id}, #{name}); </insert> <delete id="delete" parameterType="int"> DELETE FROM love where id = #{id}; </delete> </mapper> <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD com.example.Mapper 3.4//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mjlf.manyjpa.mapper.slave.LoveSlaveMapper"> <resultMap id="slave" type="com.mjlf.manyjpa.entity.Love"> <result property="id" column="id"/> <result property="name" column="name"/> </resultMap> <select id="findById" parameterType="int" resultMap="slave"> SELECT id, name from love where id = #{id}; </select> </mapper>

五、mapper接口

package com.mjlf.manyjpa.mapper.master; import com.mjlf.manyjpa.entity.Love; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; /** * Created by a123 on 17/7/9. * 主服务器 */ @Mapper public interface LoveMasterMapper { public int slave(Love love); public int delete (@Param("id") int id); } package com.mjlf.manyjpa.mapper.slave; import com.mjlf.manyjpa.entity.Love; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Param; /** * Created by a123 on 17/7/9. * 从服务 */ @Mapper public interface LoveSlaveMapper { public Love findById(@Param("id") int id); }

spring root + mybatis: githup : spring boot mybatis

mysql 主从复制