前情回顾

通过前面的两篇博文:Mycat - 实现数据库的读写分离与高可用 和 Mycat - 高可用与负载均衡实现,满满的干货!,我们完成了如下图所示的组件部署

组件结构图二


具体的部署过程可参考:Mycat - 实现数据库的读写分离与高可用,此时应用如何集成了?其实非常简单,只需要将我们的连接池配置中的数据库地址改成mycat的地址即可(将mycat看成数据库),具体如下

application.yml

server:
?port:?8886
spring:
?#连接池配置
?datasource:
?type:?com.alibaba.druid.pool.DruidDataSource
?druid:
?driver-class-name:?com.mysql.jdbc.Driver
?url:?jdbc:mysql://192.168.1.212:8066/TESTDB?useSSL=false&useUnicode=true&characterEncoding=utf-8
?username:?root
?password:?123456
?initial-size:?1?#连接池初始大小
?max-active:?20?#连接池中最大的活跃连接数
?min-idle:?1?#连接池中最小的活跃连接数
?max-wait:?60000?#配置获取连接等待超时的时间
?pool-prepared-statements:?true?#打开PSCache,t6对战游戏:并且指定每个连接上PSCache的大小
?max-pool-prepared-statement-per-connection-size:?20
?validation-query:?SELECT?1?FROM?DUAL
?validation-query-timeout:?30000
?test-on-borrow:?false?#是否在获得连接后检测其可用性
?test-on-return:?false?#是否在连接放回连接池后检测其可用性
?test-while-idle:?true?#是否在连接空闲一段时间后检测其可用性
#mybatis配置
mybatis:
?type-aliases-package:?com.lee.mycat.entity
?#config-location:?classpath:mybatis/mybatis-config.xml
?mapper-locations:?classpath:mybatis/*.xml
#?pagehelper配置
pagehelper:
?helperDialect:?mysql
?#分页合理化,pageNum<=0则查询第一页的记录;pageNum大于总页数,则查询最后一页的记录
?reasonable:?true
?supportMethodsArguments:?true
?params:?count=countSql
logging:
?level:
?com.lee.mycat.mapper:?DEBUG

UserWeb.java

package?com.lee.mycat.web;
import?com.lee.mycat.entity.User;
import?com.lee.mycat.service.IUserService;
import?org.springframework.beans.factory.annotation.Autowired;
import?org.springframework.web.bind.annotation.RequestMapping;
import?org.springframework.web.bind.annotation.RestController;
@RestController
@RequestMapping("/mycat")
public?class?UserWeb?{
?@Autowired
?private?IUserService?userService;
?@RequestMapping("/getUserByNameFromMasterDb")
?public?User?getUserByNameFromMasterDb(String?name)?{
?return?userService.getUserByNameFromMasterDb(name);
?}
?@RequestMapping("/getUserByNameFromSlaveDb")
?public?User?getUserByNameFromSlaveDb(String?name)?{
?return?userService.getUserByNameFromSlaveDb(name);
?}
?@RequestMapping("/getUserByName")
?public?User?getUserByName(String?name)?{
?return?userService.getUserByName(name);
?}
?@RequestMapping("/addUser")
?public?Integer?addUser(String?name,?Integer?age)?{
?return?userService.insertUser(new?User(name,?age));
?}
}

UserMapper.xml

<?xml?version="1.0"?encoding="UTF-8"??>
<!DOCTYPE?mapper?PUBLIC?"-//mybatis.org//DTD?Mapper?3.0//EN"?"http://www.624.774ib.com/dtd/mybatis-3-mapper.dtd">
<mapper?namespace="com.lee.mycat.mapper.UserMapper">
?<sql?id="Base_Column_List">
?id,name,age
?</sql>
?<select?id="getUserByNameFromMasterDb"?resultType="User"?parameterType="String">
?/*!mycat:db_type=master*/?SELECT
?<include?refid="Base_Column_List"?/>
?FROM
?tbl_user
?WHERE?name=#{name}
?</select>
?<select?id="getUserByNameFromSlaveDb"?resultType="User"?parameterType="String">
?/*!mycat:db_type=slave*/?SELECT
?<include?refid="Base_Column_List"?/>
?FROM
?tbl_user
?WHERE?name=#{name}
?</select>
?<select?id="getUserByName"?resultType="User"?parameterType="String">
?SELECT
?<include?refid="Base_Column_List"?/>
?FROM
?tbl_user
?WHERE?name=#{name}
?</select>
?<insert?id="insertUser"?parameterType="User"?useGeneratedKeys="true"?keyProperty="id">
?INSERT?INTO
?tbl_user(name,?age)
?VALUES
?(#{name},?#{age})
?</insert>
</mapper>

UserMapper.xml文件中会与我们平时的写法有些许不同,有时候需要明确指定强制走master还是slave节点。具体细节可查看:spring-boot-mycat

测试结果

组件结构图三

应用工程改动非常小,只需要将数据库连接配置的url改成VIP即可,如下

jdbc:mysql://192.168.1.212:8066/TESTDB?useSSL=false&useUnicode=true&characterEncoding=utf-8
改成
jdbc:mysql://192.168.1.200:8066/TESTDB?useSSL=false&useUnicode=true&characterEncoding=utf-8

测试结果