为什么需要主从数据库？
在互联网应用中，一般主库Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到从库Slave上进行。这样一来的可以大大提高读取的效率。
在一般的应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是集中在读的操作，这也就是为什么我们会有多个Slave的原因。但是为什么要分离读和写呢？熟悉DB的研发人员都道，写操作涉及到锁的问题，不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。
1、首先在数据库中创建主库test1和从库test2，然后添加表结构：
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `users` -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `userName` varchar(32) DEFAULT NULL COMMENT '用户名', `passWord` varchar(32) DEFAULT NULL COMMENT '密码', `user_sex` varchar(32) DEFAULT NULL, `nick_name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;
在添加数据的时候，由于user_sex字段使用到了枚举，所以只能添加MAN或WOMAN。
2、pom文件中添加依赖：
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies>
3、application.properties文件中添加以下配置：
mybatis.config-location=classpath:mybatis/mybatis-config.xml spring.datasource.test1.jdbc-url=jdbc:mysql://localhost:3306/test1?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true spring.datasource.test1.username=root spring.datasource.test1.password=root spring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.test2.jdbc-url=jdbc:mysql://localhost:3306/test2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true spring.datasource.test2.username=root spring.datasource.test2.password=root spring.datasource.test2.driver-class-name=com.mysql.cj.jdbc.Driver
4、resources文件夹下创建如下：
test1和test2下的配置文件UserMapper.xml内容相同，如下：
<?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.neo.mapper.test1.User1Mapper" > <resultMap id="BaseResultMap" type="com.neo.model.User" > <id column="id" property="id" jdbcType="BIGINT" /> <result column="userName" property="userName" jdbcType="VARCHAR" /> <result column="passWord" property="passWord" jdbcType="VARCHAR" /> <result column="user_sex" property="userSex" javaType="com.neo.enums.UserSexEnum"/> <result column="nick_name" property="nickName" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, userName, passWord, user_sex, nick_name </sql> <select id="getAll" resultMap="BaseResultMap" > SELECT <include refid="Base_Column_List" /> FROM users </select> <select id="getOne" parameterType="java.lang.Long" resultMap="BaseResultMap" > SELECT <include refid="Base_Column_List" /> FROM users WHERE id = #{id} </select> <insert id="insert" parameterType="com.neo.model.User" > INSERT INTO users (userName,passWord,user_sex) VALUES (#{userName}, #{passWord}, #{userSex}) </insert> <update id="update" parameterType="com.neo.model.User" > UPDATE users SET <if test="userName != null">userName = #{userName},</if> <if test="passWord != null">passWord = #{passWord},</if> nick_name = #{nickName} WHERE id = #{id} </update> <delete id="delete" parameterType="java.lang.Long" > DELETE FROM users WHERE id =#{id} </delete> </mapper>
mybatis-config.xml：
<?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> <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>
5、数据库核心类、实体类和dao文件如下：
DataSource1Config：
@Configuration @MapperScan(basePackages = "com.neo.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate") public class DataSource1Config { @Bean(name = "test1DataSource") @ConfigurationProperties(prefix = "spring.datasource.test1") @Primary public DataSource testDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "test1SqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test1/*.xml")); return bean.getObject(); } @Bean(name = "test1TransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "test1SqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
DataSource2Config：
@Configuration @MapperScan(basePackages = "com.neo.mapper.test2", sqlSessionTemplateRef = "test2SqlSessionTemplate") public class DataSource2Config { @Bean(name = "test2DataSource") @ConfigurationProperties(prefix = "spring.datasource.test2") public DataSource testDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "test2SqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/test2/*.xml")); return bean.getObject(); } @Bean(name = "test2TransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("test2DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "test2SqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
UserSexEnum：
public enum UserSexEnum { MAN, WOMAN }
User1Mapper和User2Mapper内容相同，如下：
@Mapper @Repository public interface User1Mapper { List<User> getAll(); User getOne(Long id); void insert(User user); void update(User user); void delete(Long id); }
User：
public class User implements Serializable { private static final long serialVersionUID = 1L; private Long id; private String userName; private String passWord; private UserSexEnum userSex; private String nickName; public User() { super(); } public User(String userName, String passWord, UserSexEnum userSex) { super(); this.passWord = passWord; this.userName = userName; this.userSex = userSex; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getPassWord() { return passWord; } public void setPassWord(String passWord) { this.passWord = passWord; } public UserSexEnum getUserSex() { return userSex; } public void setUserSex(UserSexEnum userSex) { this.userSex = userSex; } public String getNickName() { return nickName; } public void setNickName(String nickName) { this.nickName = nickName; } @Override public String toString() { // TODO Auto-generated method stub return "userName " + this.userName + ", pasword " + this.passWord + "sex " + userSex.name(); } }
UserController：
@RestController public class UserController { @Autowired private User1Mapper user1Mapper; @Autowired private User2Mapper user2Mapper; @RequestMapping("/getUsers") public List<User> getUsers() { List<User> users=user1Mapper.getAll(); return users; } @RequestMapping("/getUser") public User getUser(Long id) { User user=user2Mapper.getOne(id); return user; } @RequestMapping("/add") public void save(User user) { user2Mapper.insert(user); } @RequestMapping(value="update") public void update(User user) { user2Mapper.update(user); } @RequestMapping(value="/delete/{id}") public void delete(@PathVariable("id") Long id) { user1Mapper.delete(id); } }
6、测试：
测试主库：在浏览器中输入：http://localhost:8080/getUsers
数据库test1中的数据：
测试从库：在浏览器中输入：
http://localhost:8080/getUser?id=2
数据库test2中的数据：
