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實現(xiàn)讀寫分離-->
        <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開發(fā)，包括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                                   # 數(shù)據(jù)源名字
      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                        #最大連接數(shù)量
          minimum-idle: 10                             #最小空閑連接數(shù)
          max-lifetime: 0                          #最大生命周期，0不過期。不等于0且小于30秒，會被重置為默認(rèn)值30分鐘.設(shè)置應(yīng)該比mysql設(shè)置的超時時間短
          idle-timeout: 30000                          #空閑連接超時時長，默認(rèn)值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              # 負(fù)載均衡算法，用于配置從庫負(fù)載均衡算法類型，可選值：ROUND_ROBIN(輪詢)，RANDOM（隨機）
      name: ms                                              # 最終的數(shù)據(jù)源名稱
      master-data-source-name: master                       # 主庫數(shù)據(jù)源名稱
      slave-data-source-names: slave                        # 從庫數(shù)據(jù)源名稱列表，多個逗號分隔
    props:
      sql:
        show: true                                          # 在執(zhí)行SQL時，會打印SQL，并顯示執(zhí)行庫的名稱，默認(rèn)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
//定義表名:當(dāng)數(shù)據(jù)庫名與實體類名不一致或不符合駝峰命名時，需要在此注解指定表名
@TableName(value = "user")
public class UserEntity {
 
    //指定主鍵自增策略:value與數(shù)據(jù)庫主鍵列名一致，若實體類屬性名與表主鍵列名一致可省略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() 方法的參數(shù)為 mybatis-plus 內(nèi)置的條件封裝器 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                        #最大連接數(shù)量
          minimum-idle: 10                             #最小空閑連接數(shù)
          max-lifetime: 0                          #最大生命周期，0不過期。不等于0且小于30秒，會被重置為默認(rèn)值30分鐘.設(shè)置應(yīng)該比mysql設(shè)置的超時時間短
          idle-timeout: 30000                          #空閑連接超時時長，默認(rèn)值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/${數(shù)據(jù)庫}?useSSL=false&characterEncoding=UTF-8
    username: ${username}
    password: ${password}
    hikari:
      auto-commit: true
      #空閑連接超時時長
      idle-timeout: 60000
      #連接超時時長
      connection-timeout: 60000
      #最大生命周期，0不過期
      max-lifetime: 0
      #最小空閑連接數(shù)
      minimum-idle: 10
      #最大連接數(shù)量
      maximum-pool-size: 20

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