若依、多选框前后端处理，MyBatis处理多对多关系（一）

• 背景 很经典的CRUD，整理下笔记。

后端

实体类

   /**
     * 专业方向主键，用于下拉框搜索
     */
    private Long disasterTypeId;

    /**
     * 专业方向
     */
    private List<DisasterType> disasterType;

业务层

  /**
     * 新增专家信息库
     *
     * @param expertInfo 专家信息库
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int insertExpertInfo(ExpertInfo expertInfo) {
        expertInfo.setCreateTime(DateUtils.getNowDate());
        expertInfo.setCreateBy(SecurityUtils.getUsername());
        AssertUtil.insertDataBaseIsOk(expertInfoMapper.insertExpertInfo(expertInfo), "添加数据失败");
        return expertInfoMapper.batchInsertDisasterTypeRelation(expertInfo.getId(), expertInfo.getDisasterType());
    }

    /**
     * 修改专家信息库
     *
     * @param expertInfo 专家信息库
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int updateExpertInfo(ExpertInfo expertInfo) {
        expertInfo.setUpdateBy(SecurityUtils.getUsername());
        expertInfo.setUpdateTime(DateUtils.getNowDate());
        ExpertInfo oldExpertInfo = selectExpertInfoById(expertInfo.getId());

        Set<Long> oldDisasterTypeSet = oldExpertInfo.getDisasterType()
                .stream().collect(Collectors.toMap(DisasterType::getId, o -> o)).keySet();

        Set<Long> newDisasterTypeSet = expertInfo.getDisasterType()
                .stream().collect(Collectors.toMap(DisasterType::getId, o -> o)).keySet();

        //用集合交、并、补运算获取新增记录
        Set<Long> addDisasterTypeSet = new HashSet<>(newDisasterTypeSet);
        addDisasterTypeSet.removeAll(oldDisasterTypeSet);
        if (!CollectionUtils.isEmpty(addDisasterTypeSet)) {
            List<DisasterType> t = new ArrayList<>(32);
            for (Long id : addDisasterTypeSet) {
                t.add(new DisasterType(id));
            }
            expertInfoMapper.batchInsertDisasterTypeRelation(expertInfo.getId(), t);
        }
        //用集合交、并、补运算获取删除记录
        Set<Long> deleteDisasterTypeSet = new HashSet<>(oldDisasterTypeSet);
        deleteDisasterTypeSet.removeAll(newDisasterTypeSet);
        if (!CollectionUtils.isEmpty(deleteDisasterTypeSet)) {
            List<DisasterType> t = new ArrayList<>(32);
            for (Long id : deleteDisasterTypeSet) {
                t.add(new DisasterType(id));
            }
            expertInfoMapper.batchDeleteDisasterTypeRelationByIdAndDisasterTypeId(expertInfo.getId(), t);
        }

        return expertInfoMapper.updateExpertInfo(expertInfo);
    }

    /**
     * 批量删除专家信息库
     *
     * @param ids 需要删除的专家信息库主键
     * @return 结果
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public int deleteExpertInfoByIds(Long[] ids) {
        for (Long id : ids) {
            expertInfoMapper.batchDeleteDisasterTypeRelationById(id);
        }
        return expertInfoMapper.deleteExpertInfoByIds(ids);
    }

mapper

    <resultMap type="ExpertInfo" id="ExpertInfoResult">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="professionalTitle" column="professional_title"/>
        <result property="education" column="education"/>
        <result property="workUnit" column="work_unit"/>
        <result property="phone" column="phone"/>
        <result property="desc" column="desc"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <collection property="disasterType" ofType="DisasterType" javaType="ArrayList">
            <result property="id" column="disaster_type_id"/>
            <result property="name" column="disaster_type_name"/>
        </collection>
    </resultMap>


    <sql id="selectExpertInfoVo">
    SELECT
        e.id,
        e.`name`,
        e.sex,
        e.professional_title,
        e.education,
        e.work_unit,
        e.phone,
        e.`desc`,
        e.create_by,
        e.create_time,
        e.update_by,
        e.update_time,
        t.id as disaster_type_id,
        t.name as disaster_type_name
    FROM erss_expert_info e
	INNER JOIN erss_expert_info_disaster_type_relation r ON e.id=r.expert_id
	INNER JOIN erss_disaster_type t ON t.id=r.disaster_type_id
    </sql>

 
   /**
     * 批量插入专业方向中间表
     *
     * @param id           专家主键
     * @param disasterType 专业方向
     * @return 结果
     * @author weimingzhong
     * @date 2022/11/28 14:43
     */
    int batchInsertDisasterTypeRelation(@Param("id") Long id, @Param("disasterType") List<DisasterType> disasterType);

    /**
     * 批量删除专业方向中间表
     *
     * @param id           专家主键
     * @param disasterType 专业方向
     * @return 结果
     * @author weimingzhong
     * @date 2022/11/28 14:43
     */
    int batchDeleteDisasterTypeRelationByIdAndDisasterTypeId(@Param("id") Long id, @Param("disasterType") List<DisasterType> disasterType);


    /**
     * 批量删除专业方向中间表
     *
     * @param id 专家主键
     * @return 结果
     * @author weimingzhong
     * @date 2022/11/28 14:43
     */
    int batchDeleteDisasterTypeRelationById(Long id);

mybatis

<insert id="batchInsertDisasterTypeRelation">
        insert into erss_expert_info_disaster_type_relation
        <trim prefix="(" suffix=") VALUES">
            expert_id,
            disaster_type_id
        </trim>
        <foreach collection="disasterType" item="item" index="index" separator=",">
            <trim prefix="(" suffix=")">
                #{id},
                #{item.id}
            </trim>
        </foreach>
    </insert>

   <delete id="batchDeleteDisasterTypeRelationByIdAndDisasterTypeId">
        delete from erss_expert_info_disaster_type_relation
        where expert_id = #{id}
        and disaster_type_id in
        <foreach collection="disasterType" item="item" index="index" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </delete>

    <delete id="batchDeleteDisasterTypeRelationById">
        delete from erss_expert_info_disaster_type_relation
        where expert_id = #{id}
    </delete>

前端

<template>
  <div class="app-container">
    <el-form :model="queryParams" ref="queryForm" size="small" :inline="true" v-show="showSearch" label-width="68px">

      <el-form-item label="专业方向" prop="warehouseId">
        <el-select clearable placeholder="请选择专业方向" v-model="queryParams.disasterTypeId" style="width: 240px">
          <el-option
            :key="item.id"
            :label="item.name"
            :value="item.id"
            v-for="item in disasterTypeOptions"
          />
        </el-select>
      </el-form-item>
      <el-form-item>
        <el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery">搜索</el-button>
        <el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
      </el-form-item>
    </el-form>

    <el-row :gutter="10" class="mb8">
      <el-col :span="1.5">
        <el-button
          type="primary"
          plain
          icon="el-icon-plus"
          size="mini"
          @click="handleAdd"
          v-hasPermi="['manage:expertInfo:add']"
        >新增
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="success"
          plain
          icon="el-icon-edit"
          size="mini"
          :disabled="single"
          @click="handleUpdate"
          v-hasPermi="['manage:expertInfo:edit']"
        >修改
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="danger"
          plain
          icon="el-icon-delete"
          size="mini"
          :disabled="multiple"
          @click="handleDelete"
          v-hasPermi="['manage:expertInfo:remove']"
        >删除
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="warning"
          plain
          icon="el-icon-download"
          size="mini"
          @click="handleExport"
          v-hasPermi="['manage:expertInfo:export']"
        >导出
        </el-button>
      </el-col>
      <right-toolbar :showSearch.sync="showSearch" @queryTable="getList"></right-toolbar>
    </el-row>

    <el-table v-loading="loading" :data="expertInfoList" @selection-change="handleSelectionChange">
      <el-table-column type="selection" width="55" align="center"/>
      <el-table-column label="主键" align="center" prop="id"/>
      <el-table-column label="专家姓名" align="center" prop="name"/>
      <el-table-column label="性别" align="center" prop="sex"/>
      <el-table-column label="职称" align="center" prop="professionalTitle"/>
      <el-table-column label="学历" align="center" prop="education"/>
      <el-table-column label="工作单位" align="center" prop="workUnit"/>
      <el-table-column label="联系电话" align="center" prop="phone"/>
      <el-table-column label="描述" align="center" prop="desc"/>
      <el-table-column label="专业方向" align="center" min-width="200%">
        <template #default="{ row }">
          <span v-for="item in row.disasterType" :key="item.id">
            {{ item.name }}
          </span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="scope">
          <el-button
            size="mini"
            type="text"
            icon="el-icon-edit"
            @click="handleUpdate(scope.row)"
            v-hasPermi="['manage:expertInfo:edit']"
          >修改
          </el-button>
          <el-button
            size="mini"
            type="text"
            icon="el-icon-delete"
            @click="handleDelete(scope.row)"
            v-hasPermi="['manage:expertInfo:remove']"
          >删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>

    <pagination
      v-show="total>0"
      :total="total"
      :page.sync="queryParams.pageNum"
      :limit.sync="queryParams.pageSize"
      @pagination="getList"
    />

    <!-- 添加或修改专家信息库对话框 -->
    <el-dialog :title="title" :visible.sync="open" width="500px" append-to-body>
      <el-form ref="form" :model="form" :rules="rules" label-width="80px">
        <el-form-item label="专家姓名" prop="name">
          <el-input v-model="form.name" placeholder="请输入专家姓名"/>
        </el-form-item>
        <el-form-item label="职称" prop="professionalTitle">
          <el-input v-model="form.professionalTitle" placeholder="请输入职称"/>
        </el-form-item>
        <el-form-item label="学历" prop="education">
          <el-input v-model="form.education" placeholder="请输入学历"/>
        </el-form-item>
        <el-form-item label="工作单位" prop="workUnit">
          <el-input v-model="form.workUnit" placeholder="请输入工作单位"/>
        </el-form-item>
        <el-form-item label="联系电话" prop="phone">
          <el-input v-model="form.phone" placeholder="请输入联系电话"/>
        </el-form-item>
        <el-form-item label="描述" prop="desc">
          <el-input v-model="form.desc" placeholder="请输入描述"/>
        </el-form-item>
        <el-form-item label="专业方向" prop="disasterType">
          <el-checkbox v-model="disasterTypeNodeAll"
                       @change="handleCheckedDisasterTypeNodeAll($event, 'disasterType')">
            全选/全不选
          </el-checkbox>
          <el-tree
            class="tree-border"
            :data="disasterTypeOptions"
            show-checkbox
            ref="disasterType"
            node-key="id"
            empty-text="加载中，请稍候"
            :props="defaultProps"
          ></el-tree>
        </el-form-item>
      </el-form>
      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitForm">确 定</el-button>
        <el-button @click="cancel">取 消</el-button>
      </div>
    </el-dialog>
  </div>
</template>

<script>
  import {delExpertInfo, getExpertInfo, listExpertInfo} from "@/api/manage/expertInfo";
  import {listDisasterType} from "@/api/manage/disasterType";
  import {addExpertInfo, updateExpertInfo} from "../../../api/manage/expertInfo";

  export default {
    name: "ExpertInfo",
    dicts: ['erss_expert_professional_direction'],
    data() {
      return {
        // 遮罩层
        loading: true,
        // 选中数组
        ids: [],
        // 非单个禁用
        single: true,
        // 非多个禁用
        multiple: true,
        // 显示搜索条件
        showSearch: true,
        // 总条数
        total: 0,
        // 专家信息库表格数据
        expertInfoList: [],
        // 弹出层标题
        title: "",
        // 是否显示弹出层
        open: false,
        // 查询参数
        queryParams: {
          pageNum: 1,
          pageSize: 10,
          disasterTypeId: null,
        },
        // 表单参数
        form: {},
        // 表单校验
        rules: {},
        //多选框是否全选
        disasterTypeNodeAll: false,
        //多选框数据
        disasterTypeOptions: [],
        //多选框属性 label指定节点标签为节点对象的某个属性值	即多选框名字
        defaultProps: {
          label: "name"
        },
      };
    },
    created() {
      this.getList();
      this.getDisasterType();
    },
    methods: {
      //获取多选框勾选的主键
      getDisasterTypeAllCheckedKeys() {
        // 目前被选中的节点
        let checkedKeys = this.$refs.disasterType.getCheckedKeys();
        checkedKeys.unshift.apply(checkedKeys);
        return checkedKeys;
      },
      //获取多选框数据
      getDisasterType() {
        listDisasterType({pageNum: 1, pageSize: 99999}).then((res) => {
          this.disasterTypeOptions = res.rows;
        });
      },
      // 多选框（全选/全不选）按钮被点击处理
      handleCheckedDisasterTypeNodeAll(value, type) {
        if (type === 'disasterType') {
          this.$refs.disasterType.setCheckedNodes(value ? this.disasterTypeOptions : []);
        }
      },
      /** 查询专家信息库列表 */
      getList() {
        this.loading = true;
        listExpertInfo(this.queryParams).then(response => {
          this.expertInfoList = response.rows;
          this.total = response.total;
          this.loading = false;
        });
      },
      // 取消按钮
      cancel() {
        this.open = false;
        this.reset();
      },
      // 表单重置
      reset() {
        this.form = {
          id: null,
          name: null,
          sex: null,
          professionalTitle: null,
          education: null,
          workUnit: null,
          phone: null,
          desc: null,
          disasterType: [],
          createBy: null,
          createTime: null,
          updateBy: null,
          updateTime: null
        };
        if (this.$refs.disasterType !== undefined) {
          this.$refs.disasterType.setCheckedKeys([]);
        }
        this.resetForm("form");
      },
      /** 搜索按钮操作 */
      handleQuery() {
        this.queryParams.pageNum = 1;
        this.getList();
      },
      /** 重置按钮操作 */
      resetQuery() {
        this.resetForm("queryForm");
        this.handleQuery();
      },
      // 多选框选中数据
      handleSelectionChange(selection) {
        this.ids = selection.map(item => item.id)
        this.single = selection.length !== 1
        this.multiple = !selection.length
      },
      /** 新增按钮操作 */
      handleAdd() {
        this.reset();
        this.open = true;
        this.title = "添加专家信息库";
      },
      /** 修改按钮操作 */
      handleUpdate(row) {
        this.reset();
        const id = row.id || this.ids
        getExpertInfo(id).then(response => {
          this.$nextTick(() => {
            let checkedKeys = response.data.disasterType
            checkedKeys.forEach((v) => {
              this.$nextTick(() => {
                this.$refs.disasterType.setChecked(v.id, true, false);
              })
            })
          })
          this.form = response.data;
          this.open = true;
          this.title = "修改专家信息库";
        });
      },
      /** 提交按钮 */
      submitForm() {
        this.$refs["form"].validate(valid => {
          if (valid) {
            const disasterTypeCheckedKeys = this.getDisasterTypeAllCheckedKeys();
            if (this.form.id != null) {
              this.form.disasterType = []
              disasterTypeCheckedKeys.forEach((item) => {
                this.form.disasterType.push({"id": item})
              })
              updateExpertInfo(this.form).then(response => {
                this.$modal.msgSuccess("修改成功");
                this.open = false;
                this.getList();
              });
            } else {
              disasterTypeCheckedKeys.forEach((item) => {
                this.form.disasterType.push({"id": item})
              })
              addExpertInfo(this.form).then(response => {
                this.$modal.msgSuccess("新增成功");
                this.open = false;
                this.getList();
              });
            }
          }
        });
      },
      /** 删除按钮操作 */
      handleDelete(row) {
        const ids = row.id || this.ids;
        this.$modal.confirm('是否确认删除专家信息库编号为"' + ids + '"的数据项？').then(function () {
          return delExpertInfo(ids);
        }).then(() => {
          this.getList();
          this.$modal.msgSuccess("删除成功");
        }).catch(() => {
        });
      },
      /** 导出按钮操作 */
      handleExport() {
        this.download('manage/expertInfo/export', {
          ...this.queryParams
        }, `expertInfo_${new Date().getTime()}.xlsx`)
      }
    }
  };
</script>