8. SpringBoot 整合MySQL高可用数据库 (二)

37 阅读1分钟

7. SpringBoot 整合MySQL高可用数据库

📕 SpringBoot 实现MySQL读写分离(二)

application.properties

#配置文件
spring.profiles.active=master
#thymeleaf 后缀
spring.freemarker.suffix=.html
# 处理SpringBoot2.6.x与Swagger2 3.0.0版本冲突
spring.mvc.pathmatch.matching-strategy=ant_path_matcher
#log4j2的配置文件地址
logging.config=classpath:log4j2.xml
#mybatis的配置信息
mybatis.mapper-locations=classpath:mapper/*/*.xml
mybatis.type-aliases-package=com.yjren.yjrenweb.model
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

application-master.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.150.15:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root@1234

application-slave.properties

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.150.17:3306/rzleyou?useSSL=false&serverTimezone=UTC
spring.datasource.username=rzleyou2
spring.datasource.password=root@1234

t_coun_user.sql

CREATE TABLE `t_coun_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `operator_id` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人id',
  `operator_name` varchar(32) NOT NULL DEFAULT '' COMMENT '操作人名称',
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='用户表'

mybatis 逆序文件配置t_coun_user信息

        <table tableName="t_coun_user" domainObjectName="UserEntity"
               enableInsert="true" enableSelectByPrimaryKey="true"
               enableSelectByExample="true" enableUpdateByPrimaryKey="true"
               enableDeleteByPrimaryKey="true" enableDeleteByExample="true"
               enableCountByExample="true" enableUpdateByExample="true"
               selectByExampleQueryId="true">
            <property name="runtimeTableName" value="t_coun_user"/>
            <generatedKey column="id" sqlStatement="Mysql" identity="true"
                          type="post"/>
        </table>

Junit5 测试

@SpringBootTest
@AutoConfigureMockMvc
public class TestApplication {
​
​
    @Resource
    private UserEntityMapper userEntityMapper;
​
​
    @Test
    public void insertMaster(){
        //spring.profiles.active=master,主服务器进行插入数据
        UserEntity userEntity = new UserEntity();
        userEntity.setName("张家口");
        userEntity.setPhone("15835125802");
        userEntity.setOperatorId("1001");
        userEntity.setOperatorName("任雨杰");
​
        userEntityMapper.insertSelective(userEntity);
​
    }
​
    @Test
    public void selectMaster(){
        //spring.profiles.active=master,判断是否插入成功
        UserEntityExample example = new UserEntityExample();
        example.createCriteria().andIsDeletedEqualTo((byte) 0);
        List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
        System.out.println(userEntities);
    }
​
    @Test
    public void selectSlave(){
        //spring.profiles.active=slave,从服务器进行查询,是否数据同步
        UserEntityExample example = new UserEntityExample();
        example.createCriteria().andIsDeletedEqualTo((byte) 0);
        List<UserEntity> userEntities = userEntityMapper.selectByExample(example);
        System.out.println(userEntities);
    }
​
​
    @Test
    public void deleteSlave(){
        //spring.profiles.active=slave,限制从服务器用户只读,执行该方法报错代表只读权限成功
        UserEntityExample example = new UserEntityExample();
        example.createCriteria().andIsDeletedEqualTo((byte) 0);
        userEntityMapper.deleteByExample(example);
    }
    
    /*
        create user 'rzleyou2'@'%' identified by 'root@1234'; // 创建普通用户,可以远程连接
        grant select on *.* to 'rzleyou2'@'%'; //授权所有库,只能查询操作
        FLUSH PRIVILEGES;
    */
}