Springboot集成使用mycat

数据库

1.数据使用mysql，版本为8.0.20，这里在docker上运行起来

docker run -d --name mysql \
-p 3306:3306 \
-v /opt/mysql/conf.d:/etc/mysql/conf.d \
-v /opt/mysql/data:/var/lib/mysql \
-v /opt/mysql/logs:/logs \
-v /etc/localtime:/etc/localtime:ro \
-e MYSQL_ROOT_PASSWORD=123456 \
mysql:8.0.20

2.创建三个数据库db1、db2、db3

3.三个数据库中分别建表

create table user
(
    id   bigint       not null
        primary key,
    name varchar(255) null
);

mycat的安装配置

1.下载mycat
地址：http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

2.解压下载包，目录如下

3.修改主要配置文件schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<table name="user" dataNode="dn1,dn2,dn3" rule="mod-long" splitTableNames ="false"/>
	</schema>

	<dataNode name="dn1" dataHost="localhost1" database="db1" />
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />
	
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root" password="123456"></writeHost>
	</dataHost>
	
</mycat:schema>

5.复制高版本的mysql-connector-java到mycat安装目录

4.启动mycat

./bin/startup_nowrap.sh 

查看log是否启动成功：tail -f ./logs/mycat.log

SpringBoot代码

1.pom主要依赖

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter</artifactId>
 </dependency>
 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-actuator</artifactId>
 </dependency>
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.20</version>
 </dependency>
 <dependency>
     <groupId>org.mybatis.spring.boot</groupId>
     <artifactId>mybatis-spring-boot-starter</artifactId>
     <version>1.3.2</version>
 </dependency>

2.application.yml配置如下

mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
  type-aliases-package: com.wisea.tp.entity
spring:
  datasource:
    url: jdbc:mysql://192.168.29.128:8066/TESTDB
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456

3.User实体类

package com.wisea.tp.entity;

import org.apache.ibatis.type.Alias;

@Alias("User")
public class User {

    private Long id;

    private String name;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

4.dao相关

package com.wisea.tp.mapper;
import com.wisea.tp.entity.User;
import java.util.List;
public interface UserMapper {
    List<User> findList();
    int insert(User user);
}

<?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.wisea.tp.mapper.UserMapper">
    <select id="findList" resultType="User">
        select * from user
    </select>
    <insert id="insert" parameterType="User">
        insert into user(ID,NAME)
        values (#{id},#{name})
    </insert>
</mapper>

5.编写测试controller

package com.wisea.tp.controller;
import com.wisea.tp.entity.User;
import com.wisea.tp.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserController {
    @Autowired
    private UserMapper userMapper;
    @RequestMapping("/list")
    public List<User> list() {
        return userMapper.findList();
    }
    @RequestMapping("/add")
    public Integer add(User user) {
        return userMapper.insert(user);
    }
}

测试

插入数据：
http://lcoalhost:8080/user/add?id=1&name=jack
http://lcoalhost:8080/user/add?id=2&name=jerry
http://lcoalhost:8080/user/add?id=3&name=tom
…
多插入几条数据，可以发现数据分散到db1、db2、db3的user表了。