基于 lambda表达式的两表插入（List集合）

雨点打透心脏的1/2处 2023-06-30 08:48 4阅读 0赞

# 基于 lambda表达式的两表插入（List集合） #

封装的DTO

/** * @author 宫崎不骏 * @className MallManagementCategoryDTO * @Version 1.0 * @Description: TODO * @date 2020/1/1415:37 */
    @Data
    public class MallInfoDTO extends InputObject { 
        /** * 商家ID */
        private Integer mallId;
        /** * userID */
        private Long userId;
        /** * 店铺名称 */
        private String mallName;
        /** * 成立日期(开店时间) */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date openTime;
        /** * 店铺简介 */
        private String mallIntroduction;
        /** * 联系人姓名 */
        private String contactName;
    
        /** * 手机号 */
        private String telephoneNumber;
    
        /** * 邮箱 */
        private String email;
        /** * 营业执照电子版地址1 */
        private String businessLicenseUrl1;
    
        /** * 营业执照电子版地址2 */
        private String businessLicenseUrl2;
    
        /** * 公司名称 */
        private String companyName;
    
        /** * 营业执照注册号 */
        private String businessLicenseNumber;
        /** * 营业执照所在地省份 */
        private String businessLicenseProvince;
    
        /** * 营业执照所在地 市 */
        private String businessLicenseCity;
    
        /** * 营业执照所在地 区 */
        private String businessLicenseDistrict;
    
        /** * 营业执照详细地址 */
        private String businessLicenseAddress;
        /** * 成立日期 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date dateOfEstablishment;
        /** * 营业期限开始时间 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date businessTermBegin;
    
        /** * 营业期限结束时间 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date businessTermEnd;
        /** * 注册资金/0-999999999 单位万元 */
        private Integer registeredCapital;
        /** * 经营范围 */
        private String businessScope;
        /** * 法定代表人证件类型/1身份证（mybatis中写死） */
        private Integer legalRepresentativeLicenseType;
        /** * 法定代表人证件电子版地址1 */
        private String legalRepresentativeLicenseUrl1;
    
        /** * 法定代表人证件电子版地址2 */
        private String legalRepresentativeLicenseUrl2;
        /** * 法定代表人姓名 */
        private String legalRepresentativeName;
        /** * 法定代表人证件号码 */
        private String legalRepresentativeLicenseNumber;
        /** * 法定代表人证件有效期开始时间 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date legalRepresentativeLicenseTermBegin;
    
        /** * 法定代表人证件有效期结束时间/长期默认为2099-12-31 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date legalRepresentativeLicenseTermEnd;
        /** * 银行开户许可证电子版地址 */
        private String bankAccountUrl;
        /** * 组织机构代码证电子版地址 */
        private String organizingInstitutionBarCodeUrl;
    
        /** * 组织机构代码 */
        private String organizingInstitutionBarCode;
    
        /** * 组织机构代码证有效期开始时间 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date organizingInstitutionBarCodeTermBegin;
    
        /** * 组织机构代码证有效期结束时间/长期默认为2099-12-31 */
        @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
        @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
        private Date organizingInstitutionBarCodeTermEnd;
        /** * 店铺logo地址 */
        private String mallLogoUrl;
    
        /** * 店铺背景图地址 */
        private String mallLogoBackground;
    
    
        private List<MallManagementCategoryDTO> mallManagementCategoryDTO;
    
    }

/** * @author 宫崎不骏 * @className MallManagementCategoryDTO * @Version 1.0 * @Description: TODO * @date 2020/1/1415:37 */
    @Data
    public class MallManagementCategoryDTO extends InputObject { 
        /** * 主键ID */
        private Integer id;
        /** * 商家ID */
        private Integer mallId;
        /** * 一级类目ID */
        private Integer firstLevelId;
        /** * 一级类目名字 */
        private String firstLevelName;
        /** * 二级类目ID */
        private Integer secondLevelId;
        /** * 二级类目名字 */
        private String secondLevelName;
        /** * 三级类目ID */
        private Integer thirdLevelId;
        /** * 三级类目名字 */
        private String thirdLevelName;
    }

MySql数据库文件

-- ----------------------------
    -- Table structure for mall_info
    -- ----------------------------
    DROP TABLE IF EXISTS `mall_info`;
    CREATE TABLE `mall_info` (
      `mall_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '商家ID',
      `mall_name` varchar(64) DEFAULT NULL COMMENT '店铺名称',
      `mall_introduction` varchar(512) DEFAULT NULL COMMENT '店铺简介',
      `contact_name` varchar(64) DEFAULT NULL COMMENT '联系人姓名',
      `telephone_number` varchar(32) DEFAULT NULL COMMENT '手机号',
      `email` varchar(64) DEFAULT NULL COMMENT '邮箱',
      `business_license_url1` varchar(255) DEFAULT NULL COMMENT '营业执照电子版地址1',
      `business_license_ur2` varchar(255) DEFAULT NULL COMMENT '营业执照电子版地址2',
      `company_name` varchar(64) NOT NULL COMMENT '公司名称',
      `business_license_number` varchar(64) NOT NULL COMMENT '营业执照注册号',
      `business_license_province` varchar(32) NOT NULL COMMENT '营业执照所在地省份',
      `business_license_city` varchar(32) NOT NULL COMMENT '营业执照所在地 市',
      `business_license_district` varchar(32) NOT NULL COMMENT '营业执照所在地 区',
      `business_license_address` varchar(255) NOT NULL COMMENT '营业执照详细地址',
      `date_of_establishment` date NOT NULL COMMENT '成立日期',
      `business_term_begin` date NOT NULL COMMENT '营业期限开始时间',
      `business_term_end` date NOT NULL COMMENT '营业期限结束时间',
      `registered_capital` int(11) NOT NULL COMMENT '注册资金/0-999999999 单位万元',
      `business_scope` text NOT NULL COMMENT '经营范围',
      `legal_representative_license_type` tinyint(1) NOT NULL COMMENT '法定代表人证件类型/1身份证',
      `legal_representative_license_number` varchar(64) NOT NULL COMMENT '法定代表人证件号码',
      `legal_representative_license_url1` varchar(255) DEFAULT NULL COMMENT '法定代表人证件电子版地址1',
      `legal_representative_license_url2` varchar(255) DEFAULT NULL COMMENT '法定代表人证件电子版地址2',
      `legal_representative_name` varchar(64) DEFAULT NULL COMMENT '法定代表人姓名',
      `legal_representative_license_term_begin` date NOT NULL COMMENT '法定代表人证件有效期开始时间',
      `legal_representative_license_term_end` date NOT NULL COMMENT '法定代表人证件有效期结束时间/长期默认为2099-12-31',
      `bank_account_url` varchar(255) DEFAULT NULL COMMENT '银行开户许可证电子版地址',
      `organizing_institution_bar_code_url` varchar(255) NOT NULL COMMENT '组织机构代码证电子版地址',
      `organizing_institution_bar_code` varchar(64) NOT NULL COMMENT '组织机构代码',
      `organizing_institution_bar_code_term_begin` date NOT NULL COMMENT '组织机构代码证有效期开始时间',
      `organizing_institution_bar_code_term_end` date NOT NULL COMMENT '组织机构代码证有效期结束时间/长期默认为2099-12-31',
      `mall_logo_url` varchar(255) DEFAULT NULL COMMENT '店铺logo地址',
      `mall_logo_background` varchar(255) DEFAULT NULL COMMENT '店铺logo地址',
      `online_customerservice_status` char(1) DEFAULT NULL COMMENT '在线客服状态：0关闭，1开启',
      `telephone_customerservice_status` char(1) DEFAULT NULL COMMENT '电话客服状态：0关闭，1开启',
      `customerservice_telephone1` varchar(20) DEFAULT NULL COMMENT '电话客服号码1',
      `customerservice_telephone2` varchar(20) DEFAULT NULL COMMENT '电话客服号码2',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`mall_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='商家基本信息表';

-- ----------------------------
    -- Table structure for mall_management_category
    -- ----------------------------
    DROP TABLE IF EXISTS `mall_management_category`;
    CREATE TABLE `mall_management_category` (
      `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `mall_id` int(10) unsigned NOT NULL COMMENT '商家ID',
      `first_level_id` int(10) DEFAULT NULL COMMENT '一级类目ID',
      `first_level_name` varchar(64) DEFAULT NULL COMMENT '一级类目名字',
      `second_level_id` int(10) DEFAULT NULL COMMENT '二级类目ID',
      `second_level_name` varchar(64) DEFAULT NULL COMMENT '二级类目名字',
      `third_level_id` int(10) DEFAULT NULL COMMENT '三级类目ID',
      `third_level_name` varchar(64) DEFAULT NULL COMMENT '三级类目名字',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      PRIMARY KEY (`id`),
      KEY `ind_mallmanagementcategory_mallid` (`mall_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 COMMENT='商家经营类目表';

dao层

/** * @Description 向商家基本信息表插入信息 * @Retuen * @Param **/
        int saveMallInfo(List<MallInfoDTO> mallInfoDTO);
    
        /** * @Description 向商家经营类目表插入信息 * @Retuen * @Param **/
        int saveMallManagementCategory(List<MallManagementCategoryDTO> mallManagementCategoryDTO);

Mapper.xml

<insert id="saveMallInfo" parameterType="com.dy.mallmanagement.pojo.dto.MallInfoDTO" useGeneratedKeys="true"
                keyProperty="mallId">
            INSERT INTO mall_info (
                    user_id,
                    mall_name,
                    open_time,
                    mall_introduction,
                    contact_name,
                    telephone_number,
                    email,
                    business_license_url1,
                    business_license_url2,
                    company_name,
                    business_license_number,
                    business_license_province,
                    business_license_city,
                    business_license_district,
                    business_license_address,
                    date_of_establishment,
                    business_term_begin,
                    business_term_end,
                    registered_capital,
                    business_scope,
                    legal_representative_license_type,
                    legal_representative_license_url1,
                    legal_representative_license_url2,
                    legal_representative_name,
                    legal_representative_license_number,
                    legal_representative_license_term_begin,
                    legal_representative_license_term_end,
                    bank_account_url,
                    organizing_institution_bar_code_url,
                    organizing_institution_bar_code,
                    organizing_institution_bar_code_term_begin,
                    organizing_institution_bar_code_term_end,
                    mall_logo_url,
                    mall_logo_background
            ) VALUES
            <foreach collection="list" separator="," item="item">
                (
                    #{ item.userId},
                    #{ item.mallName},
                    #{ item.openTime},
                    #{ item.mallIntroduction},
                    #{ item.contactName},
                    #{ item.telephoneNumber},
                    #{ item.email},
                    #{ item.businessLicenseUrl1},
                    #{ item.businessLicenseUrl2},
                    #{ item.companyName},
                    #{ item.businessLicenseNumber},
                    #{ item.businessLicenseProvince},
                    #{ item.businessLicenseCity},
                    #{ item.businessLicenseDistrict},
                    #{ item.businessLicenseAddress},
                    #{ item.dateOfEstablishment},
                    #{ item.businessTermBegin},
                    #{ item.businessTermEnd},
                    #{ item.registeredCapital},
                    #{ item.businessScope},
                    #{ item.legalRepresentativeLicenseType},
                    #{ item.legalRepresentativeLicenseUrl1},
                    #{ item.legalRepresentativeLicenseUrl2},
                    #{ item.legalRepresentativeName},
                    #{ item.legalRepresentativeLicenseNumber},
                    #{ item.legalRepresentativeLicenseTermBegin},
                    #{ item.legalRepresentativeLicenseTermEnd},
                    #{ item.bankAccountUrl},
                    #{ item.organizingInstitutionBarCodeUrl},
                    #{ item.organizingInstitutionBarCode},
                    #{ item.organizingInstitutionBarCodeTermBegin},
                    #{ item.organizingInstitutionBarCodeTermEnd},
                    #{ item.mallLogoUrl},
                    #{ item.mallLogoBackground})
            </foreach>
        </insert>
    
        <insert id="saveMallManagementCategory" parameterType="com.dy.mallmanagement.pojo.dto.MallInfoDTO">
            INSERT INTO mall_management_category (
                    mall_id,
                    first_level_id,
                    first_level_name,
                    second_level_id,
                    second_level_name,
                    third_level_id,
                    third_level_name) values
                    <foreach collection="list" item="item" separator="," >
                    (
                    #{ item.mallId},
                    #{ item.firstLevelId},
                    #{ item.firstLevelName},
                    #{ item.secondLevelId},
                    #{ item.secondLevelName},
                    #{ item.thirdLevelId},
                    #{ item.thirdLevelName})
                    </foreach>
        </insert>

Service层

/** * @Description 向商家基本信息表插入信息 * @Retuen * @Param **/
        OutputObject saveMallInfo(List<MallInfoDTO> mallInfoDTO);

ServiceImpl层

/** * @author 宫崎不骏 * @className MallInfoServiceImpl * @Version 1.0 * @Description: TODO * @date 2020/1/1420:26 */
    @Service
    public class MallInfoServiceImpl implements MallInfoService { 
    
        @Autowired
        private MallInfoMapper mallInfoMapper;
    
        /** * @Description 向商家基本信息表插入信息 * @Retuen * @Param **/
        @Override
        @Transactional(rollbackFor = Exception.class)
        public OutputObject saveMallInfo(List<MallInfoDTO> mallInfoDTO) { 
            try{ 
                //批量添加商家基本信息表信息
                int i = mallInfoMapper.saveMallInfo(mallInfoDTO);
                //将商家经营类目表抽离出来
                List<MallManagementCategoryDTO> mallManagementCategoryDTO = mallInfoDTO.stream()
                        .flatMap(o -> o.getMallManagementCategoryDTO().stream().peek(s ->{ 
                    s.setMallId(o.getMallId());
                })).collect(Collectors.toList());
                if (i > 0 || mallManagementCategoryDTO.size()>0){ 
                    mallInfoMapper.saveMallManagementCategory(mallManagementCategoryDTO);
                    return new OutputObject(ReturnCode.SUCCESS,"添加成功","添加成功");
                }else { 
                    return new OutputObject(ReturnCode.FAIL,"添加失败",mallInfoDTO);
                }
            }catch (Exception e){ 
                // 事务回滚
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new OutputObject(ReturnCode.FAIL,"添加失败",e.getMessage());
            }
        }
        }

Controller层

/** * @author 宫崎不骏 * @className MallInfoController * @Version 1.0 * @Description: TODO * @date 2020/1/1420:54 */
    @Controller
    @RequestMapping("/api/mallInfo/")
    public class MallInfoController { 
    
        private static final Logger LOGGER = LoggerFactory.getLogger(MallInfoController.class);
    
        @Autowired
        private MallInfoService mallInfoService;
    
        @RequestMapping("/saveMallInfo")
        @ResponseBody
        public OutputObject saveMallInfo(@RequestBody List<MallInfoDTO> mallInfoDTO){ 
            LOGGER.info("com.dy.mallmanagement.MallInfoController.saveMallInfo.向商家基本信息表插入信息 mallInfoDTO{}",mallInfoDTO);
            return mallInfoService.saveMallInfo(mallInfoDTO);
        }
        }

postman测试  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70]

[
      { 
        "userId": 11,
        "mallName": "解忧杂货铺",
        "openTime": "2020-01-01 12:12:12",
        "mallIntroduction": "帮你解忧",
        "contactName": "宫崎骏",
        "telephoneNumber": "13838384438",
        "email": "13838384438@163.com",
        "businessLicenseUrl1": "F:/photo/1.jpg",
        "businessLicenseUrl2": "F:/photo/2.jpg",
        "companyName": "解忧集团",
        "businessLicenseNumber": "123456654321",
        "businessLicenseProvince": "河南省",
        "businessLicenseCity": "郑州市",
        "businessLicenseDistrict": "金水区",
        "businessLicenseAddress": "坑你大厦18楼",
        "dateOfEstablishment": "2020-01-01 12:12:13",
        "businessTermBegin": "2020-01-01 12:12:13",
        "businessTermEnd": "2220-01-01 12:12:13",
        "registeredCapital": 100000000,
        "businessScope": "智慧",
        "legalRepresentativeLicenseType": 1,
        "legalRepresentativeLicenseUrl1": "F:/photo/3.jpg",
        "legalRepresentativeLicenseUrl2": "F:/photo/4.jpg",
        "legalRepresentativeName": "张三丰",
        "legalRepresentativeLicenseNumber": "12345670987654",
        "legalRepresentativeLicenseTermBegin": "2020-01-01 12:12:13",
        "legalRepresentativeLicenseTermEnd": "2220-01-01 12:12:13",
        "bankAccountUrl": "F:/photo/5.jpg",
        "organizingInstitutionBarCodeUrl": "F:/photo/6.jpg",
        "organizingInstitutionBarCode": "123ertyu",
        "organizingInstitutionBarCodeTermBegin": "2020-01-01 12:12:13",
        "organizingInstitutionBarCodeTermEnd": "2220-01-01 12:12:13",
        "mallLogoUrl": "F:/photo/7.jpg",
        "mallLogoBackground": "F:/photo/8.jpg",
        "mallManagementCategoryDTO": [
          { 
            "firstLevelId": 1,
            "firstLevelName": "一级分类",
            "secondLevelId": 1,
            "secondLevelName": "二级分类",
            "thirdLevelId": 1,
            "thirdLevelName": "三级分类"
          }
        ]
      },
      { 
        "userId": 12,
        "mallName": "解忧杂货铺",
        "openTime": "2020-01-01 12:12:12",
        "mallIntroduction": "帮你解忧",
        "contactName": "宫崎骏",
        "telephoneNumber": "13838384438",
        "email": "13838384438@163.com",
        "businessLicenseUrl1": "F:/photo/1.jpg",
        "businessLicenseUrl2": "F:/photo/2.jpg",
        "companyName": "解忧集团",
        "businessLicenseNumber": "123456654321",
        "businessLicenseProvince": "河南省",
        "businessLicenseCity": "郑州市",
        "businessLicenseDistrict": "金水区",
        "businessLicenseAddress": "坑你大厦18楼",
        "dateOfEstablishment": "2020-01-01 12:12:13",
        "businessTermBegin": "2020-01-01 12:12:13",
        "businessTermEnd": "2220-01-01 12:12:13",
        "registeredCapital": 100000000,
        "businessScope": "智慧",
        "legalRepresentativeLicenseType": 1,
        "legalRepresentativeLicenseUrl1": "F:/photo/3.jpg",
        "legalRepresentativeLicenseUrl2": "F:/photo/4.jpg",
        "legalRepresentativeName": "张三丰",
        "legalRepresentativeLicenseNumber": "12345670987654",
        "legalRepresentativeLicenseTermBegin": "2020-01-01 12:12:13",
        "legalRepresentativeLicenseTermEnd": "2220-01-01 12:12:13",
        "bankAccountUrl": "F:/photo/5.jpg",
        "organizingInstitutionBarCodeUrl": "F:/photo/6.jpg",
        "organizingInstitutionBarCode": "123ertyu",
        "organizingInstitutionBarCodeTermBegin": "2020-01-01 12:12:13",
        "organizingInstitutionBarCodeTermEnd": "2220-01-01 12:12:13",
        "mallLogoUrl": "F:/photo/7.jpg",
        "mallLogoBackground": "F:/photo/8.jpg",
        "mallManagementCategoryDTO": [
          { 
            "firstLevelId": 1,
            "firstLevelName": "一级分类",
            "secondLevelId": 1,
            "secondLevelName": "二级分类",
            "thirdLevelId": 1,
            "thirdLevelName": "三级分类"
          }
        ]
      }
    ]

mysql数据库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200116100536441.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200116100735266.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200116100803689.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0l0aXNzb2hhcmR0b2c=,size_16,color_FFFFFF,t_70