在后台服务中对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