基于SpringBoot和Mybatis的多表（三个表）插入

梦里梦外; 2023-05-31 12:07 24阅读 0赞

# 基于SpringBoot和Mybatis的多表（三个表）插入 #

前几天写多表插入，业务逻辑很xxx。不过最后还是写出来了。在这里和大家分享一下！  
业务需求是这样的，一张主表，两张副表。主表插入一条数据，第二张表拿到第一张表的id插入list集合，第三张表拿到第二张表的id插入list集合。  
废话不多说了，下面给出思路:

封装的DTO

/** * @Description: 运动计划提供参数 （运动计划表） * @Author: 保尔·柯察金 * @CreateDate: 2019/10/16 21:00 * @UpdateUser: * @UpdateDate: 2019/10/16 21:00 * @UpdateRemark: 修改内容 * @Version: 1.0 */
    @Data
    public class SportsPlanDto extends InputObject { 
        /** * 计划id */
        private long id;
    
        /** * 企业id * */
        private long enterpriseId;
    
        /** * 赞助总金额 * */
        private BigDecimal planSponsonshipAmountTotal;
        /** * 已捐金额 */
        private BigDecimal sponsoredAmount;
        /** * @return 捐赠人数 * @param * @date 2019/10/16 10:43 */
        private Integer donationPeopleNum;
        /** * 得到红星数量 * */
        private long getRedStartNum;
        /** * 计划开始时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String beginTime;
        /** * 计划结束时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String endTime;
        /** * 计划状态（1待执行2执行中3执行完毕） * */
        private Integer status;
        /** * 创建时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String createTime;
        /** * 修改时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String updateTime;
    
        /** * 运动app关联表 * @param SportControPlanAppDto * @return */
        private List<SportControPlanAppDto> sportControPlanAppDto;
    
        /** * 运动计划提供参数 (运动计划详情表表）集合 * @param SportsPlanDetailDto * @return */
        private List<SportsPlanDetailDto> sportsPlanDetailDto;
    
    }

/** * @Description: 企业用户录入运动计划（运动计划App关联表） * @Author: 保尔·柯察金 * @CreateDate: 2019/10/17 16:11 * @UpdateUser: * @UpdateDate: 2019/10/17 16:11 * @UpdateRemark: 修改内容 * @Version: 1.0 */
    @Data
    public class SportControPlanAppDto extends InputObject { 
        /** * 表主键ID */
        private Long id;
        /** * 计划编号 * */
        private Long planId;
        /** * app id * */
        private Long appId;
        /** * 创建时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private Date createTime;
    
    
    }

/** * @Description: 运动计划提供参数 (运动计划详情表表） * @Author: 保尔·柯察金 * @CreateDate: 2019/10/16 21:00 * @UpdateUser: * @UpdateDate: 2019/10/16 21:00 * @UpdateRemark: 修改内容 * @Version: 1.0 */
    @Data
    public class SportsPlanDetailDto  extends InputObject { 
    
        /** * 运动级别id * */
        private Long levelId;
        /** * 计划app关联编号 * */
        private Long planAppId;
        /** * 公益金兑换率（兑换公益金额） * */
        private BigDecimal amountExchangeRate;
        /** * 已捐金额 * */
        private BigDecimal sponsoredAmount;
        /** * 得到红星数量 * */
        private long getRedStartNum;
        /** * @return 捐赠人数 * @param * @date 2019/10/16 10:43 */
        private Integer donationPeopleNum;
        /** * 计划开始时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String beginTime;
        /** * 计划结束时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String endTime;
        /** * 创建时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String createTime;
        /** * 修改时间 * */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="GMT+8")
        private String updateTime;
    }

MySql数据库文件

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for sport_contro_plan
    -- ----------------------------
    DROP TABLE IF EXISTS `sport_contro_plan`;
    CREATE TABLE `sport_contro_plan` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '赞助计划id',
      `enterprise_id` bigint(11) NOT NULL COMMENT '企业id',
      `plan_sponsonship_amount_total` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '赞助总金额',
      `sponsored_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '已捐金额',
      `donation_people_num` int(11) NOT NULL DEFAULT '0' COMMENT '捐赠人数',
      `get_red_start_num` bigint(11) NOT NULL DEFAULT '0' COMMENT '得到红星数量',
      `begin_time` datetime NOT NULL COMMENT '计划开始时间',
      `end_time` datetime NOT NULL COMMENT '计划结束时间',
      `status` int(2) NOT NULL DEFAULT '1' COMMENT '计划状态(1待执行2执行中3执行完毕)',
      `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '计划创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=236 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for sport_contro_plan_app
    -- ----------------------------
    DROP TABLE IF EXISTS `sport_contro_plan_app`;
    CREATE TABLE `sport_contro_plan_app` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '赞助计划id',
      `plan_id` bigint(11) NOT NULL COMMENT '运动计划编号',
      `app_id` bigint(11) NOT NULL COMMENT 'APP id',
      `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '计划 app关联记录的创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for sport_contro_plan_detail
    -- ----------------------------
    DROP TABLE IF EXISTS `sport_contro_plan_detail`;
    CREATE TABLE `sport_contro_plan_detail` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '运动计划级别id',
      `plan_app_id` bigint(11) NOT NULL COMMENT '计划app关联编号',
      `level_id` bigint(11) NOT NULL COMMENT '运动级别id',
      `amount_exchange_rate` decimal(10,2) NOT NULL COMMENT '公益金兑换率(兑换公益金额)',
      `sponsored_amount` decimal(10,2) NOT NULL COMMENT '已捐金额',
      `get_red_start_num` bigint(11) NOT NULL COMMENT '得到红星数量',
      `begin_time` datetime NOT NULL COMMENT '计划开始时间',
      `end_time` datetime NOT NULL COMMENT '计划结束的时间',
      `donation_people_num` int(11) unsigned NOT NULL COMMENT '捐赠人数',
      `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近一次修改计划详情的时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=539 DEFAULT CHARSET=utf8;

dao层

public interface SportsPlanAddMapper { 
        /** * 企业用户录入运动计划（运动计划表） * @param sportsPlanDto 提供参数 * @return */
        int saveSportControPlan(SportsPlanDto sportsPlanDto);
    
        /** * 企业用户录入运动计划（运动计划App关联表） * @param sportControPlanAppDto 提供参数 * @return */
        int saveSportControPlanApp(SportControPlanAppDto sportControPlanAppDto);
    
        /** * 企业用户录入运动计划（运动计划详情表表） * @param sportsPlanDetailDto 提供参数 * @return */
        int saveSportControPlanDetail(SportsPlanDetailDto sportsPlanDetailDto);
    
    }

Mapper.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.bao.er.dao.SportsPlanAddMapper">
    
        <insert id="saveSportControPlan" parameterType="com.bao.er.pojo.dto.SportsPlanDto">
            INSERT INTO sport_contro_plan
            <trim suffixOverrides="," prefix="(" suffix=")">
                <if test='enterpriseId !=null and enterpriseId !=""'>
                    enterprise_id,
                </if>
                <if test='planSponsonshipAmountTotal !=null and planSponsonshipAmountTotal !=""'>
                    plan_sponsonship_amount_total,
                </if>
                    sponsored_amount,
                    donation_people_num,
                    get_red_start_num,
                <if test='beginTime !=null and beginTime !=""'>
                    begin_time,
                </if>
                <if test='endTime !=null and endTime !=""'>
                    end_time,
                </if>
                    status,
                    create_time,
                    update_time,
            </trim>
            <trim suffixOverrides="," prefix="values (" suffix=")">
                <if test='enterpriseId !=null and enterpriseId !=""'>
                    #{ enterpriseId},
                </if>
                <if test='planSponsonshipAmountTotal !=null and planSponsonshipAmountTotal !=""'>
                    #{ planSponsonshipAmountTotal},
                </if>
                    0,
                    0,
                    0,
                <if test='beginTime !=null and beginTime !=""'>
                    #{ beginTime},
                </if>
                <if test='endTime !=null and endTime !=""'>
                    #{ endTime},
                </if>
                    1,
                    NOW(),
                    NOW()
            </trim>
            <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long" >
                SELECT LAST_INSERT_ID() as id
            </selectKey>
        </insert>
    
        <insert id="saveSportControPlanApp"  parameterType="com.bao.er.pojo.dto.SportControPlanAppDto">
            INSERT INTO sport_contro_plan_app
            <trim suffixOverrides="," prefix="(" suffix=")">
                <if test="planId !=null and planId !=''">
                    plan_id,
                </if>
                <if test="appId !=null and appId !=''">
                    app_id,
                </if>
                    create_time,
                    update_time,
            </trim>
            <trim suffixOverrides="," prefix="values (" suffix=")">
                <if test="planId !=null and planId !=''">
                    #{ planId},
                </if>
                <if test="appId !=null and appId !=''">
                    #{ appId},
                </if>
                    NOW(),
                    NOW()
            </trim>
            <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Long" >
                SELECT LAST_INSERT_ID() as id
            </selectKey>
        </insert>
    
       <insert id="saveSportControPlanDetail"  parameterType="com.bao.er.pojo.dto.SportsPlanDetailDto">
           INSERT INTO sport_contro_plan_detail
           <trim suffixOverrides="," prefix="(" suffix=")">
               <if test="levelId !=null and levelId!=''">
                   level_id,
               </if>
               <if test="planAppId !=null and planAppId!=''">
                   plan_app_id,
               </if>
               <if test="amountExchangeRate !=null and amountExchangeRate!=''">
                   amount_exchange_rate,
               </if>
                   sponsored_amount,
                   get_red_start_num,
                   donation_people_num,
               <if test="beginTime !=null and beginTime !=''">
                   begin_time,
               </if>
               <if test="endTime !=null and endTime !=''">
                   end_time,
               </if>
                   create_time,
                   update_time,
           </trim>
           <trim suffixOverrides=","  prefix="values (" suffix=")">
               <if test="levelId !=null and levelId!=''">
                   #{ levelId},
               </if>
               <if test="planAppId !=null and planAppId!=''">
                   #{ planAppId},
               </if>
               <if test="amountExchangeRate !=null and amountExchangeRate!=''">
                   #{ amountExchangeRate},
               </if>
                    0,
                    0,
                    0,
               <if test="beginTime !=null and beginTime !=''">
                   #{ beginTime},
               </if>
               <if test="endTime !=null and endTime !=''">
                   #{ endTime},
               </if>
                    NOW(),
                    NOW()
           </trim>
        </insert>
    
    
    </mapper>

Service层

public interface SportsPlanAddService { 
        /** * 企业用户设置运动计划 * @param sportsPlanDto 参数 * @return */
        OutputObject saveSportControPlan(SportsPlanDto sportsPlanDto);
    
    
    }

ServiceImpl层

@Service
    public class SportsPlanAddServiceImpl implements SportsPlanAddService { 
    
        @Autowired
        private SportsPlanAddMapper sportsPlanAddMapper;
    
        /** * 企业用户设置运动计划 * @param sportsPlanDto * @return */
        @Override
        @Transactional(rollbackFor = Exception.class)
        public OutputObject saveSportControPlan(SportsPlanDto sportsPlanDto) { 
            //主表插入一条记录
            long plan = sportsPlanAddMapper.saveSportControPlan(sportsPlanDto);
            //判断主表数据是否为一条，否的话抛出异常
            if(plan != 1){ 
                throw new RuntimeException("插入失败");
            }
            long planApp = 0;
            long detail = 0;
            //获取主表id；
            long planId = sportsPlanDto.getId();
            //如果运动计划app表插入数据为空，抛出异常
            if(sportsPlanDto.getSportControPlanAppDto().size() == 0 || sportsPlanDto.getSportControPlanAppDto() == null){ 
                throw new RuntimeException("插入失败");
            }
            //如果运动计划详情表插入数据为空，抛出异常
            if(sportsPlanDto.getSportsPlanDetailDto().size() == 0 || sportsPlanDto.getSportsPlanDetailDto() == null){ 
                throw new RuntimeException("插入失败");
            }
                //for循环遍历运动计划app表
                for (SportControPlanAppDto sportControPlanAppDto : sportsPlanDto.getSportControPlanAppDto()){ 
                    //把主表id存进运动计划app表中
                    sportControPlanAppDto.setPlanId(planId);
                    //接受运动计划app表中的数据
                    planApp = sportsPlanAddMapper.saveSportControPlanApp(sportControPlanAppDto);
                    //判断planApp长度，为空抛出异常
                    if(planApp <=0){ 
                        throw  new RuntimeException("插入失败");
                    }
                    //获取运动计划表刚插入数据的id
                    long planAppId = sportControPlanAppDto.getId();
                    //判断运动计划详情表数据不为空，否则return插入失败
                    if(sportsPlanDto.getSportsPlanDetailDto() != null){ 
                        //for循环遍历运动计划详情表
                        for (SportsPlanDetailDto sportsPlanDetailDto : sportsPlanDto.getSportsPlanDetailDto()){ 
                            //取出运动计划表刚插入数据的id，存进运动计划详情表
                            sportsPlanDetailDto.setPlanAppId(planAppId);
                            //接受运动计划详情表中的数据
                            detail = sportsPlanAddMapper.saveSportControPlanDetail(sportsPlanDetailDto);
                            //判断detail长度，为空抛出异常
                            if(detail <= 0){ 
                                throw  new RuntimeException("插入失败");
                            }
                        }
                    }else{ 
                        return new OutputObject(StatusCode.INSERT_FAILED.getCode(),StatusCode.INSERT_FAILED.getMessage(),"插入失败");
                    }
                }
            return new OutputObject(StatusCode.SUCCESS.getCode(),StatusCode.SUCCESS.getMessage(),plan);
        }
    
    }

Controller层

@CrossOrigin
    @RestController
    @RequestMapping("/sportsPlanAdd")
    public class  SportsPlanAddControlller { 
    
        @Autowired
        private SportsPlanAddService sportsPlanAddService;
    
       /** * 企业用户设置运动计划 * @param sportsPlanDto * @return */
        @PostMapping("/saveSportsPlan")
        public OutputObject saveSportControPlan(@RequestBody  SportsPlanDto sportsPlanDto){ 
            return sportsPlanAddService.saveSportControPlan(sportsPlanDto);
        }
    }

postman测试  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70]  
mysql数据库  
sport\_contro\_plan表 主表一条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 1]  
sport\_contro\_plan\_app从表 副表两条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 2]  
sport\_contro\_plan\_detail从表 副表四条数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70]: /images/20230531/f61f40d6f7b04a4d9efdc26a4784c77e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 1]: /images/20230531/66334cc088094f8697fd2087e3d3b7de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 2]: /images/20230531/8ba6520131844cba81689f24c1882fa1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 3]: /images/20230531/ac5b423672da43ea87ca67ebdfffe8b8.png