基于 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测试
[
{
"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数据库