ShardingSphere应用专题--4.1.1版本--Sharding-JDBC环境搭建（四）

红太狼 2022-11-10 14:27 777阅读 0赞

##### 前言： #####

以下数据配置在后面讲的所有场景中不变，具体场景需要增加的配置，各场景均有介绍。你可以打开具体的场景文章，对着本文理解

文章链接：

*  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC读写分离（五）][ShardingSphere_4.1.1_Sharding-JDBC]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC分库分表（六）][ShardingSphere_4.1.1_Sharding-JDBC 1]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC读写分离+分库分表（七）][ShardingSphere_4.1.1_Sharding-JDBC 2]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC应用注意点（八）][ShardingSphere_4.1.1_Sharding-JDBC 3]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC 字段加密之新业务加密（九）][ShardingSphere_4.1.1_Sharding-JDBC 4]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC 字段加密之已上线业务加密（十）][ShardingSphere_4.1.1_Sharding-JDBC 5]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-JDBC 字段加密之自定义加密策略（十一）][ShardingSphere_4.1.1_Sharding-JDBC 6]
 *  ShardingSphere应用专题–4.1.1版本–Sharding-JDBC Hint路由（十二）（TODO）
 *  ShardingSphere应用专题–4.1.1版本–Sharding-JDBC 分布式主键-LEAF主键（十三）（TODO）
 *  [ShardingSphere应用专题–4.1.1版本–服务治理（十四）][ShardingSphere_4.1.1]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-UI的使用（十五）][ShardingSphere_4.1.1_Sharding-UI]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-Proxy的使用（十六）][ShardingSphere_4.1.1_Sharding-Proxy]
 *  [ShardingSphere应用专题–4.1.1版本–Sharding-Scaling实现弹性伸缩（十七）][ShardingSphere_4.1.1_Sharding-Scaling]

专题文章涉及的所有用例均在github的项目中（其中sql在common-demo同级db文件下），点击[地址下载][Link 1]，项目目录如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70]  
项目中配置了启动参数，点击选在具体的环境即可。注意：确认启动日志是否是你要的环境，防止误选环境启动报错  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70 1]

##### 1.数据库 #####

数据库：mysql  
数据库版本：5.7  
数据库特殊配置：需要两个mysql，并开启主从复制

如果想实操一遍，你可以选择：

*  在自己本地执行sql脚本，自己搭建主从环境（比较费时费力）
 *  在自己本地执行sql脚本，不搭建主从环境（比较省力，但：是否走从库查询需要根据log确定，测试效果差）
 *  （推荐）下载本文配套的mysql-master-slave-docker-docker-compose，环境及数据都在里面具体使用见[下载链接][Link 2]。

###### （1）数据库sql ######

CREATE DATABASE IF NOT EXISTS mydb;
    CREATE DATABASE IF NOT EXISTS mydb0;
    CREATE DATABASE IF NOT EXISTS mydb1;
    
    DROP TABLE IF EXISTS `mydb`.`bill`;
    CREATE TABLE `mydb`.`bill` (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `bill_name` varchar(255) NOT NULL DEFAULT '' COMMENT '账单名称',
      `bill_amount` int unsigned NOT NULL DEFAULT '0' COMMENT '账单金额',
      `create_time` datetime(3) NOT NULL COMMENT '创建时间',
      `is_delete` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 0:未删除 1:已删除',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    
    DROP TABLE IF EXISTS `mydb`.`bill_item`;
    CREATE TABLE `mydb`.`bill_item` (
      `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `bill_id` bigint unsigned NOT NULL DEFAULT 0 COMMENT '账单id',
      `bill_item_name` varchar(255) NOT NULL DEFAULT '' COMMENT '子账单名称',
      `bill_item_amount` int unsigned NOT NULL DEFAULT '0' COMMENT '账单金额',
      `create_time` datetime(3) NOT NULL COMMENT '创建时间',
      `is_delete` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 0:未删除 1:已删除',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

###### （2）数据库 ######

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70 2]

#### 2.SpringBoot代码环境： ####

以下代码在所有测试场景中不变。

##### （0）Entry #####

*  BillModel

package com.example.sharding.entry;
    import java.io.Serializable;
    import java.util.List;
    
    import java.util.Date;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.springframework.format.annotation.DateTimeFormat;
    
    @Data
    @NoArgsConstructor
    public class BillModel implements Serializable { 
    
        /** * 主键 */
        private Long id;
    
        /** * 账单名称 */
        private String billName;
    
        /** * 账单金额 */
        private Integer billAmount;
    
        /** * 创建时间 */
        @JsonFormat(pattern="YYYY-MM-dd HH:mm")
        @DateTimeFormat(pattern="YYYY-MM-dd HH:mm")
        private Date createTime;
    
        /** * 是否删除 0:未删除 1:已删除 */
        private Integer isDelete;
    }

*  BillItemModel

package com.example.sharding.entry;
    
    import java.util.Date;
    
    import com.fasterxml.jackson.annotation.JsonFormat;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.springframework.format.annotation.DateTimeFormat;
    
    @Data
    @NoArgsConstructor
    public class BillItemModel { 
        /** * 主键 */
        private Long id;
    
        /** * 关联账单id */
        private Long billId;
    
        /** * 子账单名称 */
        private String billItemName;
    
        /** * 账单金额 */
        private Integer billItemAmount;
    
        /** * 创建时间 */
        @JsonFormat(pattern="YYYY-MM-dd HH:mm")
        @DateTimeFormat(pattern="YYYY-MM-dd HH:mm")
        private Date createTime;
    
        /** * 是否删除 0:未删除 1:已删除 */
        private Integer isDelete;
    }

##### （1）Controller #####

package com.example.sharding.controller;
    
    import com.example.sharding.entry.BillDTO;
    import com.example.sharding.entry.BillItemModel;
    import com.example.sharding.entry.BillModel;
    import com.example.sharding.service.BillService;
    import com.github.pagehelper.PageInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.validation.annotation.Validated;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.List;
    import java.util.Map;
    
    /** * auth:suyouliang */
    @RestController
    @RequestMapping("sharding/")
    @Validated
    public class ShardingTestController { 
        @Autowired
        private BillService billService;
    
        //----------------------基础测试----------------------//
    
        @PostMapping("insertSelective")
        public int insertSelective(BillModel record) { 
            return billService.insertSelective(record);
        }
    
        @PostMapping("batchInsert")
        public int batchInsert(@RequestBody List<BillModel> list) { 
            return billService.batchInsert(list);
        }
    
        /** * 使用sharding分库分表时如果更新了分库分表字段： * Can not update sharding key, logic table: [bill], * * @param record * @return */
        @PostMapping("updateByPrimaryKeySelective")
        public int updateByPrimaryKeySelective(BillModel record) { 
            return billService.updateByPrimaryKeySelective(record);
        }
    
        /** * 批量更新测试 * <p> * 分库分表时不支持，底层使用了case when * 4.x.x不支持case when语法 * * @param * @return */
        @PostMapping("updateBatchSelective")
        public int updateBatchSelective(@RequestBody List<BillModel> list) { 
            return billService.updateBatchSelective(list);
        }
    
        /** * 删除测试 * * @param id * @return */
        @PostMapping("deleteByPrimaryKey")
        public int deleteByPrimaryKey(Long id) { 
            return billService.deleteByPrimaryKey(id);
        }
    
        /** * 查询测试 * * @param id * @return */
        @GetMapping("selectByPrimaryKey")
        public BillModel selectByPrimaryKey(Long id) { 
            return billService.selectByPrimaryKey(id);
        }
    
    
        /** * OR及range语句测试 * * @return */
        @GetMapping("selectOfOrAndRange")
        public List<BillModel> selectOfOr() { 
            return billService.selectOfOr();
        }
    
        /** * 手动分页测试 * * @param billModel * @param pageNum * @param pageSize * @return */
        @GetMapping("selectPage")
        public PageInfo<BillModel> selectPage(BillModel billModel, @RequestParam(defaultValue = "1") Integer pageNum, @RequestParam(defaultValue = "2") Integer pageSize) { 
            return billService.selectPage(billModel, pageNum, pageSize);
        }
    
        /** * pageHelper分页测试 * * @param billModel * @param pageNum * @param pageSize * @return */
        @GetMapping("selectPageByPageHelper")
        public PageInfo<BillModel> selectPageByPageHelper(BillModel billModel, @RequestParam(defaultValue = "1") Integer pageNum, @RequestParam(defaultValue = "2") Integer pageSize) { 
            return billService.selectPageByPageHelper(billModel, pageNum, pageSize);
        }
    
        /** * 简单聚合函数 * 不可以同时使用普通聚合函数和DISTINCT聚合函数 * 如：SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name * 语句去除distinct可以成功 * * @return */
        @GetMapping("selectOfSimpleSumFunction")
        public Map<String, Object> selectOfSimpleSumFunction() { 
            return billService.selectOfSimpleSumFunction();
        }
    
        /** * 聚合函数及去重 * 不可以同时使用普通聚合函数和DISTINCT聚合函数 * 如：SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name * 语句去除distinct可以成功 * * @return */
        @GetMapping("selectOfSumFunction")
        public Map<String, Object> selectOfSumFunction() { 
            return billService.selectOfSumFunction();
        }
    
        /** * 聚合排序无having * <p> * 不支持having，去除having可正常执行 * * @return */
        @GetMapping("selectOfGroupBy")
        public Map<String, Object> selectOfGroupBy() { 
            return billService.selectOfGroupBy();
        }
    
        /** * 聚合排序+having * <p> * 不支持having，去除having可正常执行 * * @return */
        @GetMapping("selectOfGroupByHaving")
        public Map<String, Object> selectOfGroupByHaving() { 
            return billService.selectOfGroupByHaving();
        }
    
        /** * UNION ALL * <p> * 分库分表时不支持UNION（ALL） * * @return */
        @GetMapping("selectOfUnionAll")
        public List<BillModel> selectOfUnionAll() { 
            return billService.selectOfUnionAll();
        }
    
        /** * 跨库关联 join * * @return */
        @GetMapping("selectOfJoin")
        public List<Map<String, Object>> selectOfJoin() { 
            return billService.selectOfJoin();
        }
    
        /** * insert on duplicate key update * 4.1.1版本有bug，报参数错误: No value specified for parameter 5 * Actual SQL: master ::: * <p> * insert into bill ( id,bill_name,bill_amount,create_time ) * values * (?, ?, ?, ?) * on duplicate key update * id = ?, bill_name = ?, bill_amount = ?,create_time = ? * <p> * ::: [20, bill-insert-on-duplicate-key-6, 10000, 2020-12-27 13:13:00.0] * <p> * 解析后的sql * insert into bill ( id,bill_name,bill_amount,create_time ) * values * ( ?, ?,?,? ) * on duplicate key update * id = ?,bill_name = ?,bill_amount = ?,create_time = ? * <p> * <p> * 分表后：不可修改分库分表逻辑字段 * <p> * 分库分表后（对应sharding-sub-db-tb）：join对应的表（bill_item）没有做分库，由于[db0,db1]<->[bill_0,bill_1]有四种组合情况 * ，而对应的bill_item会插到db0，还是db1是不固定的（随机的还是有算法暂不知道） * 导致如果bill进入了db0，很有可能bill_item进入db1，就会导致join的结果为空，但是不会报错 * 但是分库分表的表A，join一个没有库分表的表B，是存在这种情况的，比如B为字典表，数据量固定，增量不大。 * <p> * 这种情况下，可以让B在多个db中主从同步，从而方便A在各个DB join查询。 * * @return */
        @PostMapping("insertOnDuplicateKeyUpdate")
        public int insertOnDuplicateKeyUpdate(BillModel record) { 
            return billService.insertOnDuplicateKeyUpdate(record);
        }
        //----------------------基础测试----------------------//
    
    
        //----------------------基础其他测试----------------------//
    
        /** * 更新分库分表字段 * <p> * 使用sharding分库分表时：Can not update sharding key, logic table: [bill], * * @return */
        @PostMapping("updateSubDbAndTbColumn")
        public int updateSubDbAndTbColumn(Long sourceId, Long changeId) { 
            return billService.updateSubDbAndTbColumn(sourceId, changeId);
        }
    
    
        /** * 基础事务 * 说明：直接在方法里制造异常是无法证明分布式事务的问题的，制造异常，本地代码直接报错，事务在本地不会提交 * 真正测试应该是在代码运行过程正确，提交过程失败 * * 让数据冲突（无法成功,mysql会拦住）： * 设置bill或者billItem的id=A 此时A在数据库不存在 * debug到最后一行，让数据库预处理通过，此时更改某个数据的id为A，放开debug * 事实上，修改的表在执行完insert语句等待提交时，数据库会给表上表级锁，你根本无法修改数据，表也无法删除。 * * 可以使用两个不同mysql实例的两个表，debug到最后一行，让数据库预处理通过，停到其中一个mysql service，验证错误（其他的方法自行探究） * 由于现有的环境，使用的billItem表与bill在同一个mysql实例，暂时不做此测试 * * * * 分库情况下，不同的两个库的两个表：一个提交成功，一个提交失败，数据提交成功，此时将不满足事务原子性（A）：要么全部成功，要么全部失败 * * 本来是用来做事务测试的，由于不好复现，现在这个方法用来添加billItem的数据 * * //TODO 事务专题调研 * * @return */
        @PostMapping("transaction")
        public int transactionTest(BillModel billModel, BillItemModel itemModel) { 
            return billService.transactionTest(billModel, itemModel);
        }
    
    
        @GetMapping("selectBillItemById")
        public BillItemModel selectBillItemById(Long billItemId) { 
            return billService.selectBillItemById(billItemId);
        }
    
        /** * 关键字 * 使用sharding时： * 4.1.1版本有bug:no viable alternative at input 'text' * * @return */
        @GetMapping("keyword")
        public BillDTO transactionTest(Long id) { 
            return billService.getIncludeKeyword(id);
        }
    
    }

##### （2）Service #####

*  BillService

package com.example.sharding.service;
    
    import com.example.sharding.dao.BillItemMapper;
    import com.example.sharding.dao.BillMapper;
    import com.example.sharding.entry.BillDTO;
    import com.example.sharding.entry.BillItemModel;
    import com.example.sharding.entry.BillModel;
    import com.github.pagehelper.PageHelper;
    import com.github.pagehelper.PageInfo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import java.util.List;
    import java.util.Map;
    
    @Service
    public class BillService { 
    
        @Autowired
        private BillMapper billMapper;
        @Autowired
        private BillItemMapper billItemMapper;
    
    
        public int insertSelective(BillModel record) { 
            return billMapper.insertSelective(record);
        }
    
        public int deleteByPrimaryKey(Long id) { 
            return billMapper.deleteByPrimaryKey(id);
        }
    
    
        public int updateByPrimaryKeySelective(BillModel record) { 
            return billMapper.updateByPrimaryKeySelective(record);
        }
    
        public BillModel selectByPrimaryKey(Long id) { 
            return billMapper.selectByPrimaryKey(id);
        }
    
        public int updateBatchSelective(List<BillModel> list) { 
            return billMapper.updateBatchSelective(list);
        }
    
        public int batchInsert(List<BillModel> list) { 
            return billMapper.batchInsert(list);
        }
    
        public PageInfo<BillModel> selectPage(BillModel billModel, Integer pageNum, Integer pageSize) { 
            Long size = billMapper.countBySelective(billModel);
            List<BillModel> billModels = billMapper.selectPage(billModel, (pageNum - 1) * pageNum, pageSize);
            PageInfo<BillModel> pageInfo = new PageInfo<>(billModels);
            pageInfo.setTotal(size);
            return pageInfo;
        }
    
        public PageInfo<BillModel> selectPageByPageHelper(BillModel billModel, Integer pageNum, Integer pageSize) { 
            return PageHelper.startPage(pageNum, pageSize).doSelectPageInfo(() -> billMapper.selectPageByPageHelper(billModel));
        }
    
        public Map<String, Object> selectOfSimpleSumFunction() { 
            return billMapper.selectOfSimpleSumFunction();
        }
    
        public Map<String,Object> selectOfSumFunction() { 
            return billMapper.selectOfSumFunction();
        }
    
        public Map<String, Object> selectOfGroupBy() { 
            return billMapper.selectOfGroupBy();
        }
    
        public Map<String, Object> selectOfGroupByHaving() { 
            return billMapper.selectOfGroupByHaving();
        }
    
        public List<BillModel> selectOfUnionAll() { 
            return billMapper.selectOfUnionAll();
        }
    
        public List<Map<String, Object>> selectOfJoin() { 
            return billMapper.selectOfJoin();
    
        }
    
        public int updateSubDbAndTbColumn(Long sourceId, Long changeId) { 
            return billMapper.updateSubDbAndTbColumn(sourceId,changeId);
        }
    
        public int insertOnDuplicateKeyUpdate(BillModel record) { 
            return billMapper.insertOrUpdateSelective(record);
        }
        @Transactional
        public int transactionTest(BillModel billModel, BillItemModel itemModel) { 
            billMapper.insert(billModel);
            itemModel.setBillId(billModel.getId());
            billItemMapper.insertSelective(itemModel);
    
            return 1;
        }
    
        public BillDTO getIncludeKeyword(Long id) { 
            return billMapper.getIncludeKeyword(id);
        }
    
        public BillItemModel selectBillItemById(Long billItemId) { 
            return billItemMapper.selectByPrimaryKey(billItemId);
        }
    
        public List<BillModel> selectOfOr() { 
            return billMapper.selectOfOr();
        }
    }

*  BillItemService

package com.example.sharding.service;
    
    import org.springframework.stereotype.Service;
    import javax.annotation.Resource;
    import com.example.sharding.entry.BillItemModel;
    import com.example.sharding.dao.BillItemMapper;
    @Service
    public class BillItemService{ 
    
        @Resource
        private BillItemMapper billItemMapper;
    
        
        public int insertSelective(BillItemModel record) { 
            return billItemMapper.insertSelective(record);
        }
    
        
        public BillItemModel selectByPrimaryKey(Long id) { 
            return billItemMapper.selectByPrimaryKey(id);
        }
    
        
        public int updateByPrimaryKeySelective(BillItemModel record) { 
            return billItemMapper.updateByPrimaryKeySelective(record);
        }
    
    }

##### （3）DAO #####

*  BillMapper

package com.example.sharding.dao;
    
    import com.example.sharding.entry.BillDTO;
    import com.example.sharding.entry.BillModel;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.List;
    import java.util.Map;
    
    @Mapper
    public interface BillMapper { 
        int deleteByPrimaryKey(Long id);
    
        int insert(BillModel record);
    
        int insertOrUpdate(BillModel record);
    
        int insertOrUpdateSelective(BillModel record);
    
        int insertSelective(BillModel record);
    
        BillModel selectByPrimaryKey(Long id);
    
        int updateByPrimaryKeySelective(BillModel record);
    
        int updateBatchSelective(List<BillModel> list);
    
        int batchInsert(@Param("list") List<BillModel> list);
    
        List<BillModel> selectPage(@Param("bill") BillModel bill, @Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);
    
        List<BillModel> selectPageByPageHelper(BillModel billModel);
    
        Long countBySelective(BillModel record);
    
    
        Map<String, Object> selectOfSumFunction();
    
        Map<String, Object> selectOfSimpleSumFunction();
    
        Map<String, Object> selectOfGroupBy();
    
        Map<String, Object> selectOfGroupByHaving();
    
        List<BillModel> selectOfUnionAll();
    
        List<Map<String, Object>> selectOfJoin();
    
        int updateSubDbAndTbColumn(@Param("sourceId") Long sourceId, @Param("changeId") Long changeId);
    
        BillDTO getIncludeKeyword(Long id);
    
        List<BillModel> selectOfOr();
    
    }
    
    }

*  BillItemMapper

package com.example.sharding.dao;
    
    import com.example.sharding.entry.BillItemModel;
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface BillItemMapper { 
        int insertSelective(BillItemModel record);
    
        BillItemModel selectByPrimaryKey(Long id);
    
        int updateByPrimaryKeySelective(BillItemModel record);
    
    }

##### （4）Mapper.xml #####

*  BillMapper.xml

<?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.example.sharding.dao.BillMapper">
      <resultMap id="BaseResultMap" type="com.example.sharding.entry.BillModel">
        
        
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="bill_name" jdbcType="VARCHAR" property="billName" />
        <result column="bill_amount" jdbcType="INTEGER" property="billAmount" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="is_delete" jdbcType="TINYINT" property="isDelete" />
      </resultMap>
      <sql id="Base_Column_List">
        
        id, bill_name, bill_amount, create_time, is_delete
      </sql>
      <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
        
        select 
        <include refid="Base_Column_List" />
        from bill
        where id = #{id,jdbcType=BIGINT}
      </select>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Long">
        
        delete from bill
        where id = #{id,jdbcType=BIGINT}
      </delete>
      <insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.example.sharding.entry.BillModel" useGeneratedKeys="true">
        
        insert into bill (bill_name, bill_amount, create_time, 
          is_delete)
        values (#{billName,jdbcType=VARCHAR}, #{billAmount,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, 
          #{isDelete,jdbcType=TINYINT})
      </insert>
      <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.example.sharding.entry.BillModel" useGeneratedKeys="true">
        
        insert into bill
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="billName != null and billName != ''">
            bill_name,
          </if>
          <if test="billAmount != null">
            bill_amount,
          </if>
          <if test="createTime != null">
            create_time,
          </if>
          <if test="isDelete != null">
            is_delete,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
          <if test="billName != null and billName != ''">
            #{billName,jdbcType=VARCHAR},
          </if>
          <if test="billAmount != null">
            #{billAmount,jdbcType=INTEGER},
          </if>
          <if test="createTime != null">
            #{createTime,jdbcType=TIMESTAMP},
          </if>
          <if test="isDelete != null">
            #{isDelete,jdbcType=TINYINT},
          </if>
        </trim>
      </insert>
      <update id="updateByPrimaryKeySelective" parameterType="com.example.sharding.entry.BillModel">
        
        update bill
        <set>
          <if test="billName != null and billName != ''">
            bill_name = #{billName,jdbcType=VARCHAR},
          </if>
          <if test="billAmount != null">
            bill_amount = #{billAmount,jdbcType=INTEGER},
          </if>
          <if test="createTime != null">
            create_time = #{createTime,jdbcType=TIMESTAMP},
          </if>
          <if test="isDelete != null">
            is_delete = #{isDelete,jdbcType=TINYINT},
          </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
      </update>
      <update id="updateByPrimaryKey" parameterType="com.example.sharding.entry.BillModel">
        
        update bill
        set bill_name = #{billName,jdbcType=VARCHAR},
          bill_amount = #{billAmount,jdbcType=INTEGER},
          create_time = #{createTime,jdbcType=TIMESTAMP},
          is_delete = #{isDelete,jdbcType=TINYINT}
        where id = #{id,jdbcType=BIGINT}
      </update>
      <update id="updateBatchSelective" parameterType="java.util.List">
        
        update bill
        <trim prefix="set" suffixOverrides=",">
          <trim prefix="bill_name = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
              <if test="item.billName != null">
                when id = #{item.id,jdbcType=BIGINT} then #{item.billName,jdbcType=VARCHAR}
              </if>
            </foreach>
          </trim>
          <trim prefix="bill_amount = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
              <if test="item.billAmount != null">
                when id = #{item.id,jdbcType=BIGINT} then #{item.billAmount,jdbcType=INTEGER}
              </if>
            </foreach>
          </trim>
          <trim prefix="create_time = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
              <if test="item.createTime != null">
                when id = #{item.id,jdbcType=BIGINT} then #{item.createTime,jdbcType=TIMESTAMP}
              </if>
            </foreach>
          </trim>
          <trim prefix="is_delete = case" suffix="end,">
            <foreach collection="list" index="index" item="item">
              <if test="item.isDelete != null">
                when id = #{item.id,jdbcType=BIGINT} then #{item.isDelete,jdbcType=TINYINT}
              </if>
            </foreach>
          </trim>
        </trim>
        where id in
        <foreach close=")" collection="list" item="item" open="(" separator=", ">
          #{item.id}
        </foreach>
      </update>
      <insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
        
        insert into bill
        (bill_name, bill_amount, create_time, is_delete)
        values
        <foreach collection="list" item="item" separator=",">
          (#{item.billName,jdbcType=VARCHAR}, #{item.billAmount,jdbcType=INTEGER}, #{item.createTime,jdbcType=TIMESTAMP},
            #{item.isDelete,jdbcType=TINYINT})
        </foreach>
      </insert>
      <insert id="insertOrUpdate" keyColumn="id" keyProperty="id" parameterType="com.example.sharding.entry.BillModel" useGeneratedKeys="true">
        
        insert into bill
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="id != null">
            id,
          </if>
          bill_name,
          bill_amount,
          create_time,
          is_delete,
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="id != null">
            #{id,jdbcType=BIGINT},
          </if>
          #{billName,jdbcType=VARCHAR},
          #{billAmount,jdbcType=INTEGER},
          #{createTime,jdbcType=TIMESTAMP},
          #{isDelete,jdbcType=TINYINT},
        </trim>
        on duplicate key update 
        <trim suffixOverrides=",">
          <if test="id != null">
            id = values(id),
          </if>
          bill_name = values(bill_name),
          bill_amount =values(bill_amount),
          create_time =values(create_time),
          is_delete = values(is_delete)},
        </trim>
      </insert>
      <insert id="insertOrUpdateSelective" keyColumn="id" keyProperty="id" parameterType="com.example.sharding.entry.BillModel" useGeneratedKeys="true">
        
        insert into bill
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="id != null">
            id,
          </if>
          <if test="billName != null">
            bill_name,
          </if>
          <if test="billAmount != null">
            bill_amount,
          </if>
          <if test="createTime != null">
            create_time,
          </if>
          <if test="isDelete != null">
            is_delete,
          </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
          <if test="id != null">
            #{id,jdbcType=BIGINT},
          </if>
          <if test="billName != null">
            #{billName,jdbcType=VARCHAR},
          </if>
          <if test="billAmount != null">
            #{billAmount,jdbcType=INTEGER},
          </if>
          <if test="createTime != null">
            #{createTime,jdbcType=TIMESTAMP},
          </if>
          <if test="isDelete != null">
            #{isDelete,jdbcType=TINYINT},
          </if>
        </trim>
        on duplicate key update 
        <trim suffixOverrides=",">
          <if test="id != null">
            id = #{id,jdbcType=BIGINT},
          </if>
          <if test="billName != null">
            bill_name = #{billName,jdbcType=VARCHAR},
          </if>
          <if test="billAmount != null">
            bill_amount = #{billAmount,jdbcType=INTEGER},
          </if>
          <if test="createTime != null">
            create_time = #{createTime,jdbcType=TIMESTAMP},
          </if>
          <if test="isDelete != null">
            is_delete = #{isDelete,jdbcType=TINYINT},
          </if>
        </trim>
      </insert>
    
    
      <select id="selectPage" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from bill
        <where>
          <if test="bill.id != null">
            and id=#{bill.id,jdbcType=BIGINT}
          </if>
          <if test="bill.billName != null and bill.billName != ''">
            and bill_name=#{bill.billName,jdbcType=VARCHAR}
          </if>
          <if test="bill.billAmount != null">
            and bill_amount=#{bill.billAmount,jdbcType=INTEGER}
          </if>
          <if test="bill.createTime != null">
            and create_time=#{bill.createTime,jdbcType=TIMESTAMP}
          </if>
          <if test="bill.isDelete != null">
            and is_delete=#{bill.isDelete,jdbcType=TINYINT}
          </if>
        </where>
        order by create_time
        LIMIT #{pageNum},#{pageSize}
      </select>
    
      <select id="selectPageByPageHelper" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from bill
        <where>
          <if test="id != null">
            and id=#{id,jdbcType=BIGINT}
          </if>
          <if test="billName != null and billName != ''">
            and bill_name=#{billName,jdbcType=VARCHAR}
          </if>
          <if test="billAmount != null">
            and bill_amount=#{billAmount,jdbcType=INTEGER}
          </if>
          <if test="createTime != null">
            and create_time=#{createTime,jdbcType=TIMESTAMP}
          </if>
          <if test="isDelete != null">
            and is_delete=#{isDelete,jdbcType=TINYINT}
          </if>
        </where>
        order by create_time
      </select>
    
    
      <select id="countBySelective" resultType="java.lang.Long">
        select count(1)
        from bill
        <where>
          <if test="id != null">
            and id=#{id,jdbcType=BIGINT}
          </if>
          <if test="billName != null and billName != ''">
            and bill_name=#{billName,jdbcType=VARCHAR}
          </if>
          <if test="billAmount != null">
            and bill_amount=#{billAmount,jdbcType=INTEGER}
          </if>
          <if test="createTime != null">
            and create_time=#{createTime,jdbcType=TIMESTAMP}
          </if>
          <if test="isDelete != null">
            and is_delete=#{isDelete,jdbcType=TINYINT}
          </if>
        </where>
      </select>
    
      <select id="selectOfSumFunction" resultType="java.util.Map">
         select COUNT(1) as num,COUNT(distinct bill_amount) as amount_num, SUM(bill_amount) as amountTotal, MIN(bill_amount) as amountMin, MAX(bill_amount) as amountMax, AVG(bill_amount) as amountAvg
        from bill
      </select>
    
      <select id="selectOfGroupBy" resultType="java.util.Map">
           select COUNT(1),sum(bill_amount)  from bill group by is_delete
      </select>
    
      <select id="selectOfGroupByHaving" resultType="java.util.Map">
        select COUNT(1),sum(bill_amount)  from bill group by is_delete having sum(bill_amount)>200
      </select>
    
      <select id="selectOfUnionAll" resultMap="BaseResultMap">
          select
          <include refid="Base_Column_List"/>
          from bill
          where id>10
          union all
          select
          <include refid="Base_Column_List"/>
          from bill
          where id < 5
      </select>
    
      <select id="selectOfJoin" resultType="java.util.Map">
          select
          *
          from bill_item as bi
            join  bill as b
          on b.id=bi.bill_id
          where b.is_delete=0
      </select>
        <update id="updateSubDbAndTbColumn" >
            update bill set bill_amount=bill_amount+250,id=#{changeId} where id=#{sourceId}
        </update>
    
      <select id="getIncludeKeyword" resultType="com.example.sharding.entry.BillDTO">
        select
        id, bill_name as text, bill_amount as billAmount, create_time as createTime, is_delete as isDelete
        from bill
        where id = #{id,jdbcType=BIGINT}
      </select>
    
      <select id="selectOfSimpleSumFunction" resultType="java.util.Map">
        select COUNT(1) as num,COUNT(bill_amount) as amount_num, SUM(bill_amount) as amountTotal, MIN(bill_amount) as amountMin, MAX(bill_amount) as amountMax, AVG(bill_amount) as amountAvg
        from bill
      </select>
    
      <select id="selectOfOr" resultMap="BaseResultMap">
        select *
        from bill where id < 30 or bill_amount > 40
      </select>
    
    
    </mapper>

*  BillItemMapper.xml

##### （5）pom.xml #####

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.3.3.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.syl.learn</groupId>
        <artifactId>sharding-sphere</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>sharding-demo</name>
        <description>Demo project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
            <nacos-version>1.4.1</nacos-version>
            <sharding-version>4.1.1</sharding-version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.3</version>
            </dependency>
    
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.16</version>
            </dependency>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.8</version>
                <scope>provided</scope>
            </dependency>
            <dependency>
                <groupId>com.googlecode.aviator</groupId>
                <artifactId>aviator</artifactId>
                <version>5.1.4</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba.nacos</groupId>
                <artifactId>nacos-client</artifactId>
                <version>${nacos-version}</version>
            </dependency>
            <dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper-spring-boot-starter</artifactId>
                <version>1.2.10</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
        </dependencies>
    
        <profiles>
            <profile>
                
                <id>原生ORM环境</id>
                <activation>
                    <activeByDefault>true</activeByDefault>
                </activation>
                <properties>
                    <profileActive>common</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>读写分离</id>
                <properties>
                    <profileActive>sharding-rw</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分表</id>
                <properties>
                    <profileActive>sharding-sub-tb</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分表+读写分离</id>
                <properties>
                    <profileActive>sharding-sub-tb-rw</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分库分表</id>
                <properties>
                    <profileActive>sharding-sub-db-tb</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分库分表+读写分离</id>
                <properties>
                    <profileActive>sharding-sub-db-tb-rw</profileActive>
                </properties>
            </profile>
            
            <profile>
                
                <id>数据加密</id>
                <properties>
                    <profileActive>sp-encrypt</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分布式主键</id>
                <properties>
                    <profileActive>sp-key-generator</profileActive>
                </properties>
            </profile>
            <profile>
                
                <id>分布式治理</id>
                <properties>
                    <profileActive>sp-distributed</profileActive>
                </properties>
            </profile>
        </profiles>
    
    
        <build>
            <filters> 
                <filter>src/main/filters/${profileActive}.properties</filter>
            </filters>
            
            <resources>
                <resource>
                    <directory>src/main/resources</directory>
                    <filtering>true</filtering>
                    
                </resource>
                <resource>
                    <directory>src/main/bin</directory>
                    
                    <filtering>true</filtering>
                </resource>
            </resources>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
                <plugin>
                    <artifactId>maven-resources-plugin</artifactId>
                    <configuration>
                        <encoding>utf-8</encoding>
                        <useDefaultDelimiters>true</useDefaultDelimiters>
                    </configuration>
                </plugin>
            </plugins>
            <finalName>${project.artifactId}</finalName>
        </build>
    
    
    </project>

##### （6）application.properties #####

spring.profiles.active=${ profileActive}
    
    mybatis.mapper-locations=classpath:mapper/*Mapper.xml
    mybatis.type-aliases-package=com.example.sharding.entry
    
    #pagehelper
    pagehelper.helperDialect=mysql
    pagehelper.params=count=countSql
    
    #showSql
    logging.level.com.example.mapper=info
    
    spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    spring.datasource.username=root
    spring.datasource.password=111
    spring.datasource.url=jdbc:mysql://127.0.0.1:4406/mydb?useUnicode=true&character_set_server=utf8mb4&autoReconnect=true&useSSL=false&verifyServerCertificate=false&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL
    spring.datasource.hikari.maximum-pool-size=10
    spring.datasource.hikari.minimum-idle=5
    
    #log相关配置
    logging.level.root=info
    server.compression.enabled=true
    mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

[ShardingSphere_4.1.1_Sharding-JDBC]: https://blog.csdn.net/qq_31457665/article/details/115217056
[ShardingSphere_4.1.1_Sharding-JDBC 1]: https://blog.csdn.net/qq_31457665/article/details/115219306
[ShardingSphere_4.1.1_Sharding-JDBC 2]: https://blog.csdn.net/qq_31457665/article/details/115301416
[ShardingSphere_4.1.1_Sharding-JDBC 3]: https://blog.csdn.net/qq_31457665/article/details/115302413
[ShardingSphere_4.1.1_Sharding-JDBC 4]: https://blog.csdn.net/qq_31457665/article/details/115307627
[ShardingSphere_4.1.1_Sharding-JDBC 5]: https://blog.csdn.net/qq_31457665/article/details/115325042
[ShardingSphere_4.1.1_Sharding-JDBC 6]: https://blog.csdn.net/qq_31457665/article/details/115325098
[ShardingSphere_4.1.1]: https://blog.csdn.net/qq_31457665/article/details/115331685
[ShardingSphere_4.1.1_Sharding-UI]: https://blog.csdn.net/qq_31457665/article/details/115335439
[ShardingSphere_4.1.1_Sharding-Proxy]: https://blog.csdn.net/qq_31457665/article/details/115336591
[ShardingSphere_4.1.1_Sharding-Scaling]: https://blog.csdn.net/qq_31457665/article/details/115336660
[Link 1]: https://github.com/2892824942/shardingsphere-demo
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70]: /images/20221022/8c163155cd084021b0f24c88466c008e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70 1]: /images/20221022/48fa674e0ad64b59a8ca92fe148af064.png
[Link 2]: https://download.csdn.net/download/qq_31457665/16100271
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDU3NjY1_size_16_color_FFFFFF_t_70 2]: /images/20221022/1bec905bdf3d40e1be9ecea784baa01b.png