Sharding-JDBC自动实现MySQL读写分离的示例代码

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(45) NOT NULL,
  `nickname` varchar(18) NOT NULL,
  `password` varchar(45) NOT NULL,
  `headimage_url` varchar(45) DEFAULT NULL,
  `introduce` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `account_UNIQUE` (`account`),
  UNIQUE KEY `nickname_UNIQUE` (`nickname`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8;

        <!--Sharding-JDBC实现读写分离-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>4.1.1</version>
        </dependency>
 
        <!-- mybatis-plus 依赖 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
 
        <!-- mysql 依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        
         <!--支持Web开发，包括Tomcat和spring-webmvc-->
        <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
        <!--LomBok使用@Data注解-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.4</version>
            <scope>provided</scope>
        </dependency>

spring:
  shardingsphere:
    datasource:
      names: master,slave                                   # 数据源名字
      master:
        type: com.zaxxer.hikari.HikariDataSource        # 连接池
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://xxxxxx:3306/test?allowMultiQueries=true&useSSL=false&useUnicode=true&characterEncoding=utf-8 #主库地址
        username: root
        password: xxxxxx
        hikari:
          maximum-pool-size: 20                        #最大连接数量
          minimum-idle: 10                             #最小空闲连接数
          max-lifetime: 0                          #最大生命周期，0不过期。不等于0且小于30秒，会被重置为默认值30分钟.设置应该比mysql设置的超时时间短
          idle-timeout: 30000                          #空闲连接超时时长，默认值600000（10分钟）
          connection-timeout: 60000                    #连接超时时长
          data-source-properties:
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            cachePrepStmts: true
            useServerPrepStmts: true
      slave:
        type: com.zaxxer.hikari.HikariDataSource        # 连接池
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://xxxxxx:3306/test?allowMultiQueries=true&useSSL=false&useUnicode=true&characterEncoding=utf-8 #从库地址
        username: root
        password: xxxxxx
        hikari:
          maximum-pool-size: 20
          minimum-idle: 10
          max-lifetime: 0
          idle-timeout: 30000
          connection-timeout: 60000
          data-source-properties:
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            cachePrepStmts: true
            useServerPrepStmts: true
    masterslave:
      load-balance-algorithm-type: round_robin              # 负载均衡算法，用于配置从库负载均衡算法类型，可选值：ROUND_ROBIN(轮询)，RANDOM（随机）
      name: ms                                              # 最终的数据源名称
      master-data-source-name: master                       # 主库数据源名称
      slave-data-source-names: slave                        # 从库数据源名称列表，多个逗号分隔
    props:
      sql:
        show: true                                          # 在执行SQL时，会打印SQL，并显示执行库的名称，默认false

package com.hs.sharingjdbc.entity;
 
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
 
@Data
//定义表名:当数据库名与实体类名不一致或不符合驼峰命名时，需要在此注解指定表名
@TableName(value = "user")
public class UserEntity {
 
    //指定主键自增策略:value与数据库主键列名一致，若实体类属性名与表主键列名一致可省略value
    @TableId(type = IdType.AUTO)
    private Integer user_id;
    private String account;
    private String nickname;
    private String password;
    private String headimage_url;
    private String introduce;
}

package com.hs.sharingjdbc.mapper;
 
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hs.sharingjdbc.entity.UserEntity;
 
public interface UserMapper extends BaseMapper<UserEntity> {
 
//    int insert(T entity);
//
//    int deleteById(Serializable id);
//
//    int deleteByMap(@Param("cm") Map<String, Object> columnMap);
//
//    int delete(@Param("ew") Wrapper<T> queryWrapper);
//
//    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> idList);
//
//    int updateById(@Param("et") T entity);
//
//    int update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper);
//
//    T selectById(Serializable id);
//
//    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> idList);
//
//    List<T> selectByMap(@Param("cm") Map<String, Object> columnMap);
//
//    T selectOne(@Param("ew") Wrapper<T> queryWrapper);
//
//    Integer selectCount(@Param("ew") Wrapper<T> queryWrapper);
//
//    List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);
//
//    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> queryWrapper);
//
//    List<Object> selectObjs(@Param("ew") Wrapper<T> queryWrapper);
//
//    <E extends IPage<T>> E selectPage(E page, @Param("ew") Wrapper<T> queryWrapper);
//
//    <E extends IPage<Map<String, Object>>> E selectMapsPage(E page, @Param("ew") Wrapper<T> queryWrapper);
 
}

package com.hs.sharingjdbc;
 
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@SpringBootApplication()
@MapperScan("com.hs.sharingjdbc.mapper")
public class SharingJdbcApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(SharingJdbcApplication.class, args);
    }
}

package com.hs.sharingjdbc.service;
 
import com.hs.sharingjdbc.entity.UserEntity;
import com.hs.sharingjdbc.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
 
@Service
public class UserService {
 
    @Autowired
    UserMapper userMapper;
 
    public List<UserEntity> findAll()
    {
        //selectList() 方法的参数为 mybatis-plus 内置的条件封装器 Wrapper，这里不填写表示无任何条件，全量查询
        List<UserEntity> userEntities = userMapper.selectList(null);
 
        return userEntities;
    }
 
    public int insertUser(UserEntity user)
    {
        int i = userMapper.insert(user);
        return i;
    }
}

package com.hs.sharingjdbc.controller;
 
import com.hs.sharingjdbc.entity.UserEntity;
import com.hs.sharingjdbc.service.UserService;
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
public class UserController
{
    @Autowired
    UserService userService;
 
    @RequestMapping("/listUser")
    public List<UserEntity>  listUser()
    {
        List<UserEntity> users = userService.findAll();
        return users;
    }
 
    @RequestMapping("/insertUser")
    public void insertUser()
    {
       UserEntity userEntity = new UserEntity();
       userEntity.setAccount("22222");
       userEntity.setNickname("hshshs");
       userEntity.setPassword("123");
       userService.insertUser(userEntity);
    }
}

     hikari:
          maximum-pool-size: 20                        #最大连接数量
          minimum-idle: 10                             #最小空闲连接数
          max-lifetime: 0                          #最大生命周期，0不过期。不等于0且小于30秒，会被重置为默认值30分钟.设置应该比mysql设置的超时时间短
          idle-timeout: 30000                          #空闲连接超时时长，默认值600000（10分钟）
          connection-timeout: 60000                    #连接超时时长
          data-source-properties:
            prepStmtCacheSize: 250
            prepStmtCacheSqlLimit: 2048
            cachePrepStmts: true
            useServerPrepStmts: true

datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://${ip}:3306/${数据库}?useSSL=false&characterEncoding=UTF-8
    username: ${username}
    password: ${password}
    hikari:
      auto-commit: true
      #空闲连接超时时长
      idle-timeout: 60000
      #连接超时时长
      connection-timeout: 60000
      #最大生命周期，0不过期
      max-lifetime: 0
      #最小空闲连接数
      minimum-idle: 10
      #最大连接数量
      maximum-pool-size: 20

    public List<UserInfo> findAllUser() 
    {
        // 强制路由主库
        HintManager.getInstance().setMasterRouteOnly();
 
        return this.list();
    }