完整的增删改查、导入导出Excel

一、完整的增删改查、导入导出Excel功能开发案例。

1、Controller层

import com.ctcemti.machinery.common.result.Result;
import com.ctcemti.machinery.common.result.ResultEnum;
import com.ctcemti.machinery.project.business.util.ExcelUtil;
import com.github.pagehelper.PageInfo;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import java.io.IOException;
import java.util.List;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ctcemti.machinery.project.business.entity.DmSkilledPersonInfo;
import com.ctcemti.machinery.project.business.service.IDmSkilledPersonInfoService;
import org.springframework.web.multipart.MultipartFile;
import java.util.Map;


@RestController
@RequestMapping("business/skilledPerson")
public class DmSkilledPersonInfoController {

    @Autowired
    private IDmSkilledPersonInfoService dmSkilledPersonInfoService;

    
    @ApiOperation(value = "查询技能人员信息对象")
    @PostMapping("query")
    @ApiImplicitParam(name = "entity", value = "技能人员信息实体类")
    public Result query(HttpServletRequest request, HttpServletResponse response, String id) {
        DmSkilledPersonInfo dmSkilledPersonInfo = dmSkilledPersonInfoService.selectDmSkilledPersonInfoById(id);
        return new Result(ResultEnum.SUCCESS, dmSkilledPersonInfo);
    }

    
    @ApiOperation(value = "查询技能人员信息列表")
    @PostMapping("list")
    @RequiresPermissions("business.skilledPerson.view")
    @ApiImplicitParam(name = "entity", value = "技能人员信息实体类")
    public Result> queryList(HttpServletRequest request, HttpServletResponse response, DmSkilledPersonInfo dmSkilledPersonInfo) {
        PageInfo pageInfo = dmSkilledPersonInfoService.selectDmSkilledPersonInfoList(dmSkilledPersonInfo);
        return new Result>(ResultEnum.SUCCESS, pageInfo);
    }


    
    @ApiOperation(value = "保存技能人员信息数据")
    @PostMapping("add")
    @RequiresPermissions("business.skilledPerson.add")
    @ApiImplicitParams({@ApiImplicitParam(name = "entity", value = "技能人员信息数据实体类")})
    public Result insertDmSkilledPersonInfo(HttpServletRequest request, HttpServletResponse response, DmSkilledPersonInfo dmSkilledPersonInfo) {
        dmSkilledPersonInfoService.insertDmSkilledPersonInfo(dmSkilledPersonInfo);
        return new Result(ResultEnum.SUCCESS, null);
    }

    
    @ApiOperation(value = "更新技能人员信息数据")
    @PostMapping("update")
    @RequiresPermissions("business.skilledPerson.edit")
    @ApiImplicitParam(name = "entity", value = "技能人员信息数据实体类")
    public Result updateDmSkilledPersonInfo(HttpServletRequest request, HttpServletResponse response, DmSkilledPersonInfo dmSkilledPersonInfo) {
        dmSkilledPersonInfoService.updateDmSkilledPersonInfo(dmSkilledPersonInfo);
        return new Result(ResultEnum.SUCCESS, null);
    }


    
    @ApiOperation(value = "删除技能人员信息")
    @PostMapping("delete")
    @RequiresPermissions("business.skilledPerson.delete")
    @ApiImplicitParams({@ApiImplicitParam(name = "ids", value = "主键ids串")})
    public Result remove(HttpServletRequest request, HttpServletResponse response, String ids) {
        dmSkilledPersonInfoService.deleteDmSkilledPersonInfoByIds(ids);
        return new Result(ResultEnum.SUCCESS, null);
    }

    
    @ApiOperation(value = "导入船机管理人信息")
    @PostMapping("importData")
    public Result> importData(@RequestParam(value = "file") MultipartFile file) {
        List dmSkilledPersonInfoList = ExcelUtil.importExcel(file, 1, 1, DmSkilledPersonInfo.class);
        Map result = dmSkilledPersonInfoService.importData(dmSkilledPersonInfoList);
        return new Result>(ResultEnum.SUCCESS, result);
    }

    @ApiOperation(value = "导出技能人员信息")
    @GetMapping("export")
    @ApiImplicitParam(name = "entity", value = "技能人员信息数据实体类")
    public void export(HttpServletRequest request, HttpServletResponse response) throws IOException {
        dmSkilledPersonInfoService.export(request, response);
    }

}

2、service层：

import com.ctcemti.machinery.project.business.entity.DmSkilledPersonInfo;
import java.util.List;
import com.github.pagehelper.PageInfo;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public interface IDmSkilledPersonInfoService {
    
    DmSkilledPersonInfo selectDmSkilledPersonInfoById(String id);

    
    PageInfo selectDmSkilledPersonInfoList(DmSkilledPersonInfo dmSkilledPersonInfo);

    
    int insertDmSkilledPersonInfo(DmSkilledPersonInfo dmSkilledPersonInfo);

    
    int updateDmSkilledPersonInfo(DmSkilledPersonInfo dmSkilledPersonInfo);

    
    int deleteDmSkilledPersonInfoByIds(String ids);


    
    Map importData(List dmSkilledPersonInfoList);

    
    void export(HttpServletRequest request, HttpServletResponse response);


}

3、serviceImpl层： 

import com.alibaba.fastjson.JSON;
import com.ctcemti.machinery.common.base.utils.BaseEntityUtil;
import com.ctcemti.machinery.common.enums.EnumDeleteFlag;
import com.ctcemti.machinery.common.enums.EnumFileDataType;
import com.ctcemti.machinery.common.util.JWTUtil;
import com.ctcemti.machinery.common.util.ServletUtils;
import com.ctcemti.machinery.framework.vo.LoginUserInfo;
import com.ctcemti.machinery.project.business.entity.DmFile;
import com.ctcemti.machinery.project.business.enums.EnumWhether;
import com.ctcemti.machinery.project.business.service.IDmFileService;
import com.ctcemti.machinery.project.business.util.ExcelUtil;
import com.ctcemti.machinery.project.sys.dao.InfDictItemMapper;
import com.ctcemti.machinery.project.sys.dao.SysOrgMapper;
import com.ctcemti.machinery.project.sys.entity.OrgOrganization;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import com.ctcemti.machinery.project.tool.generatecode.util.Convert;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ctcemti.machinery.project.business.dao.DmSkilledPersonInfoMapper;
import com.ctcemti.machinery.project.business.entity.DmSkilledPersonInfo;
import com.ctcemti.machinery.project.business.service.IDmSkilledPersonInfoService;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@Service
@Slf4j
public class DmSkilledPersonInfoServiceImpl implements IDmSkilledPersonInfoService {
    @Autowired
    private DmSkilledPersonInfoMapper dmSkilledPersonInfoMapper;
    @Autowired
    protected HttpServletRequest request;
    @Autowired
    private IDmFileService fileService;
    @Autowired
    private SysOrgMapper sysOrgMapper;
    @Autowired
    private InfDictItemMapper infDictItemMapper;


    private List allList;

    @Value("${ctcemti.errorfilepath}")
    private String errorfilepath;

    
    @Override
    public DmSkilledPersonInfo selectDmSkilledPersonInfoById(String id) {
        DmSkilledPersonInfo dmSkilledPersonInfo = dmSkilledPersonInfoMapper.selectDmSkilledPersonInfoById(id);
        // 查询附件数据
        List fileList = getFileList(dmSkilledPersonInfo.getId(), EnumFileDataType.技能人员_附件.getValue());
        if (!CollectionUtils.isEmpty(fileList)) {
            dmSkilledPersonInfo.setFileList(fileList);
        }
        return dmSkilledPersonInfo;
    }

    
    @Override
    public PageInfo selectDmSkilledPersonInfoList(DmSkilledPersonInfo dmSkilledPersonInfo) {
        // 获取当前登录用户信息
        LoginUserInfo loginUserInfo = JWTUtil.getLoginUserInfo(ServletUtils.getRequest());

        // 查询权限
        String token = ServletUtils.gianToken(request);
        String currentMid = JWTUtil.getPersonMidId(token);
        dmSkilledPersonInfo.setCurrentMid(currentMid);
        dmSkilledPersonInfo.setSearchDeptId(loginUserInfo.getOrgId());
        dmSkilledPersonInfo.setDeleteFlag(EnumDeleteFlag.有效.getValue());
        allList = dmSkilledPersonInfoMapper.selectDmSkilledPersonInfoList(dmSkilledPersonInfo);//用于导出数据

        PageHelper.startPage(dmSkilledPersonInfo.getPageIndex(), dmSkilledPersonInfo.getPageSize(), "skilled.create_time desc");
        List list = dmSkilledPersonInfoMapper.selectDmSkilledPersonInfoList(dmSkilledPersonInfo);
        PageInfo pageInfo = new PageInfo(list);
        return pageInfo;
    }

    
    @Override
    public int insertDmSkilledPersonInfo(DmSkilledPersonInfo dmSkilledPersonInfo) {

        dmSkilledPersonInfo.setId(UUID.randomUUID().toString());//id
        //初始化新增信息
        BaseEntityUtil.initFields(dmSkilledPersonInfo);
        if (dmSkilledPersonInfo.getFileList() != null && dmSkilledPersonInfo.getFileList().size() > 0) {
            // 删除附件数据
            fileService.deleteDmFileByDataIds(Convert.toStrArray(dmSkilledPersonInfo.getId()));
            // 保存附件数据
            fileService.saveFiles(dmSkilledPersonInfo.getFileList(), dmSkilledPersonInfo.getId(),
                    EnumFileDataType.技能人员_附件.getValue());
        }
        dmSkilledPersonInfo.setDeleteFlag(EnumDeleteFlag.有效.getValue());
        return dmSkilledPersonInfoMapper.insertSelective(dmSkilledPersonInfo);
    }

    
    @Override
    public int updateDmSkilledPersonInfo(DmSkilledPersonInfo dmSkilledPersonInfo) {

        //初始化修改信息
        BaseEntityUtil.initFieldsWithUpd(dmSkilledPersonInfo);
        //如果是没有证书，就把证书信息清空
        if (dmSkilledPersonInfo.getHaveCertificateFlag().equals(EnumWhether.否.toString())) {
            dmSkilledPersonInfo.setCertificateName("");
            dmSkilledPersonInfo.setIssuingAuthority("");
            dmSkilledPersonInfo.setSkillsContent("");
        }
        if (dmSkilledPersonInfo.getFileList() != null && dmSkilledPersonInfo.getFileList().size() > 0) {
            // 删除附件数据
            fileService.deleteDmFileByDataIds(Convert.toStrArray(dmSkilledPersonInfo.getId()));
            // 保存附件数据
            fileService.saveFiles(dmSkilledPersonInfo.getFileList(), dmSkilledPersonInfo.getId(),
                    EnumFileDataType.技能人员_附件.getValue());
        }
        dmSkilledPersonInfo.setDeleteFlag(EnumDeleteFlag.有效.getValue());
        return dmSkilledPersonInfoMapper.updateDmSkilledPersonInfoById(dmSkilledPersonInfo);
    }

    
    @Override
    public int deleteDmSkilledPersonInfoByIds(String ids) {
        return dmSkilledPersonInfoMapper.deleteDmSkilledPersonInfoByIds(Convert.toStrArray(ids));
    }

    
    @Override
    public Map importData(List dmSkilledPersonInfoList) {
        Map resultMap = new HashMap();
        String errorInfo = "";

        // 验证数据的有效性
        if (dmSkilledPersonInfoList.size() <= 0) {
            errorInfo = "没有可导入数据！";
        }
        if (org.springframework.util.StringUtils.isEmpty(errorInfo)) {
            int cc = 3;
            for (DmSkilledPersonInfo item : dmSkilledPersonInfoList) {

                if (!org.springframework.util.StringUtils.isEmpty(item.getName())) {

                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("姓名不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getBirthday())) {

                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("出生年月不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getManagementUnitId())) {
                    OrgOrganization orgOrganization = new OrgOrganization();
                    orgOrganization.setId(item.getManagementUnitId());
                    int count = sysOrgMapper.select(orgOrganization).size();
                    if (count == 0) {
                        errorInfo += "第".concat((cc) + "行，").concat("管理单位不存在；
");
                    }
                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("管理单位不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getBelongUnitId())) {
                    OrgOrganization orgOrganization = new OrgOrganization();
                    orgOrganization.setId(item.getBelongUnitId());
                    int count = sysOrgMapper.select(orgOrganization).size();
                    if (count == 0) {
                        errorInfo += "第".concat((cc) + "行，").concat("所在单位不存在；
");
                    }
                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("所在单位不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getWorkTypeName())) {
                    String workTypeCode = infDictItemMapper.selectCodeByname(item.getWorkTypeName(), "WorkType");
                    if (org.springframework.util.StringUtils.isEmpty(workTypeCode)) {
                        errorInfo += "第".concat((cc) + "行，").concat("工种不存在；
");
                    }
                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("工种不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getEducationalQualificationName())) {
                    String educationalQualificationCode = infDictItemMapper.selectCodeByname(item.getEducationalQualificationName(), "educationalQualification");
                    if (org.springframework.util.StringUtils.isEmpty(educationalQualificationCode)) {
                        errorInfo += "第".concat((cc) + "行，").concat("学历不存在；
");
                    }
                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("学历不能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getHaveCertificateFlagName())) {
                    String haveCertificateFlagCode = infDictItemMapper.selectCodeByname(item.getHaveCertificateFlagName(), "Whether");
                    if (org.springframework.util.StringUtils.isEmpty(haveCertificateFlagCode)) {
                        errorInfo += "第".concat((cc) + "行，").concat("是否持证值不正确；
");
                    }
                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("是否持证不能为空；
");
                }

                if (!StringUtils.isEmpty(item.getHaveCertificateFlagName()) && item.getHaveCertificateFlagName().equals("是")) {
                    if (StringUtils.isEmpty(item.getIssuingAuthority())) {
                        errorInfo += "第".concat((cc) + "行，").concat("发证机关不能为空；
");
                    }
                    if (StringUtils.isEmpty(item.getCertificateName())) {
                        errorInfo += "第".concat((cc) + "行，").concat("证书名称不能为空；
");
                    }
                    if (StringUtils.isEmpty(item.getSkillsContent())) {
                        errorInfo += "第".concat((cc) + "行，").concat("操作项目、技能等级不能为空；
");
                    }
                }
                if (!StringUtils.isEmpty(item.getHaveCertificateFlagName()) && item.getHaveCertificateFlagName().equals("否")) {
                    if (!StringUtils.isEmpty(item.getIssuingAuthority())) {
                        errorInfo += "第".concat((cc) + "行，").concat("没有持证，发证机关不填写；
");
                    }
                    if (!StringUtils.isEmpty(item.getCertificateName())) {
                        errorInfo += "第".concat((cc) + "行，").concat("没有持证，证书名称不填写；
");
                    }
                    if (!StringUtils.isEmpty(item.getSkillsContent())) {
                        errorInfo += "第".concat((cc) + "行，").concat("没有持证，操作项目、技能等级不填写；
");
                    }
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getRecruitmentPropertyName())) {
                    String recruitmentPropertyCode = infDictItemMapper.selectCodeByname(item.getRecruitmentPropertyName(), "recruitmentProperty");
                    if (org.springframework.util.StringUtils.isEmpty(recruitmentPropertyCode)) {
                        errorInfo += "第".concat((cc) + "行，").concat("用工性质不存在；
");
                    }

                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("用工性质能为空；
");
                }

                if (!org.springframework.util.StringUtils.isEmpty(item.getPhone())) {
                    String regex = "^((13[0-9])|(14[5|7])|(15([0-3]|[5-9]))|(17[013678])|(18[0,5-9]))\d{8}$";
                    if (item.getPhone().length() != 11) {
                        errorInfo += "第".concat((cc) + "行，").concat("手机号不是11位数；
");
                    } else {
                        Pattern p = Pattern.compile(regex);
                        Matcher m = p.matcher(item.getPhone());
                        boolean isMatch = m.matches();
                        if (!isMatch) {
                            errorInfo += "第".concat((cc) + "行，").concat("手机号格式错误；
");
                        }
                    }

                } else {
                    errorInfo += "第".concat((cc) + "行，").concat("电话不能为空；
");
                }
                cc++;
            }
        }


        //保存数据
        if (org.springframework.util.StringUtils.isEmpty(errorInfo)) {
            List list = new ArrayList<>();
            int count = 1;
            for (DmSkilledPersonInfo item : dmSkilledPersonInfoList) {
                item.setId(UUID.randomUUID().toString());
                item.setCreateUserId(JWTUtil.getLoginId(ServletUtils.getRequest()));
                item.setCreateTime(new Date());
                item.setCreateOrgId(JWTUtil.getLoginUserInfo(ServletUtils.getRequest()).getOrgId());
                item.setDeleteFlag(EnumDeleteFlag.有效.getValue());
                String workTypeCode = infDictItemMapper.selectCodeByname(item.getWorkTypeName(), "WorkType");
                item.setWorkType(workTypeCode);
                String educationalQualificationCode = infDictItemMapper.selectCodeByname(item.getEducationalQualificationName(), "educationalQualification");
                item.setEducationalQualification(educationalQualificationCode);
                String haveCertificateFlagCode = infDictItemMapper.selectCodeByname(item.getHaveCertificateFlagName(), "Whether");
                item.setHaveCertificateFlag(haveCertificateFlagCode);
                String recruitmentPropertyCode = infDictItemMapper.selectCodeByname(item.getRecruitmentPropertyName(), "recruitmentProperty");
                item.setRecruitmentProperty(recruitmentPropertyCode);
                list.add(item);
                if (count == dmSkilledPersonInfoList.size()) {
                    dmSkilledPersonInfoMapper.insertBatch(list);
                    list = new ArrayList<>();
                }
                if (count % 50 == 0) {
                    dmSkilledPersonInfoMapper.insertBatch(list);
                    list = new ArrayList<>();
                }
                count++;
            }
        }

        if (StringUtils.isEmpty(errorInfo)) {
            resultMap.put("flag", "导入成功");
        } else {
            resultMap.put("flag", errorInfo);
        }

        log.info("--->>导入结果：{}", JSON.toJSONString(resultMap));
        return resultMap;
    }


    
    @Override
    public void export(HttpServletRequest request, HttpServletResponse response) {

        if (allList != null && allList.size() > 0) {
            // 导出数据
            ExcelUtil.exportExcel(allList, "技能人员数据", "技能人员数据",
                    DmSkilledPersonInfo.class, "技能人员数据导出.xls", response);
        }

    }


    
    private List getFileList(String deviceBaseCheckId, Integer fileType) {
        List fileList = fileService.getFiles(deviceBaseCheckId, fileType);
        return fileList;
    }
}

4、Dao层：

import com.ctcemti.machinery.project.business.entity.DmSkilledPersonInfo;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import tk.mybatis.mapper.common.IdsMapper;


@Mapper
public interface DmSkilledPersonInfoMapper extends tk.mybatis.mapper.common.Mapper, IdsMapper {

    
    public List selectDmSkilledPersonInfoList(DmSkilledPersonInfo dmSkilledPersonInfo);


    
    public DmSkilledPersonInfo selectDmSkilledPersonInfoById(String id);

    
    public int deleteDmSkilledPersonInfoByIds(String[] ids);

    
    public int updateDmSkilledPersonInfoById(DmSkilledPersonInfo dmSkilledPersonInfo);


    
    public void insertBatch(@Param("list") List list);

}

5、xml文件：

    
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
    

    
        select skilled.*,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='educationalQualification' and
        a.code=skilled.educational_qualification) as educationalQualificationName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='recruitmentProperty' and a.code=skilled.recruitment_property) as
        recruitmentPropertyName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='WorkType' and a.code=skilled.work_type) as workTypeName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='Whether' and a.code=skilled.have_certificate_flag) as
        haveCertificateFlagName,
        (select top 1 abbr from org_organization org
        where org.id = skilled.belong_unit_id) as belongUnitName, --使用单位
        (select top 1 abbr from org_organization org
        where org.id = skilled.management_unit_id) as managementUnitName --管理单位
        from dm_skilled_person_info as skilled
        
            join (SELECt distinct k.child_id FROM rel_person_org rel
            JOIN sys_org_kv k ON k.org_id = rel.org_id
            WHERe rel.m_id = #{currentMid}
            AND k.child_id in (SELECt sok.child_id from sys_org_kv sok WHERe sok.org_id= #{searchDeptId})) kv
            on kv.child_id = skilled.management_unit_id
        
    


    
         select skilled.*,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='educationalQualification' and a.code=skilled.educational_qualification) as educationalQualificationName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='recruitmentProperty' and a.code=skilled.recruitment_property) as recruitmentPropertyName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='WorkType' and a.code=skilled.work_type) as workTypeName,
        (select top 1 a.name from inf_dict_item a
        join inf_dict_sort b on a.pid=b.id and b.code='Whether' and a.code=skilled.have_certificate_flag) as haveCertificateFlagName,
        (select top 1 abbr from org_organization org
        where org.id = skilled.belong_unit_id) as belongUnitName, --使用单位
        (select top 1 abbr from org_organization org
        where org.id = skilled.management_unit_id) as managementUnitName --管理单位
        from dm_skilled_person_info as skilled
        where skilled.id=#{id}
    


    
        
        
            and delete_flag = #{deleteFlag}
            and create_user_id = #{createUserId}
            and create_org_id = #{createOrgId}
            and update_user_id = #{updateUserId}
            and update_org_id = #{updateOrgId}
            and org_id = #{orgId}
            and name like '%'+#{name}+'%'
            and birthday = #{birthday}
            and
                educational_qualification = #{educationalQualification}
            
            and phone = #{phone}
            and management_unit_id =
                #{managementUnitId}
            
            and belong_unit_id = #{belongUnitId}
            and recruitment_property =
                #{recruitmentProperty}
            
            and work_type = #{workType}
            and have_certificate_flag =
                #{haveCertificateFlag}
            
            and certificate_name like
                '%'+#{certificateName}+'%'
            
            and issuing_authority =
                #{issuingAuthority}
            
            and skills_content = #{skillsContent}
        
    


    
        update dm_skilled_person_info
        
            delete_flag = #{deleteFlag},
            create_time = #{createTime},
            create_user_id = #{createUserId},
            create_org_id = #{createOrgId},
            update_time = #{updateTime},
            update_user_id = #{updateUserId},
            update_org_id = #{updateOrgId},
            org_id = #{orgId},
            name = #{name},
            birthday = #{birthday},
            educational_qualification =
                #{educationalQualification},
            
            phone = #{phone},
            management_unit_id = #{managementUnitId},
            
            belong_unit_id = #{belongUnitId},
            recruitment_property =
                #{recruitmentProperty},
            
            work_type = #{workType},
            have_certificate_flag = #{haveCertificateFlag},
            certificate_name = #{certificateName},
            issuing_authority = #{issuingAuthority},
            skills_content = #{skillsContent},
            remark = #{remark},
        
        where id = #{id}
    


    
        delete from dm_skilled_person_info where id in
        
            #{id}
        
    



    
        insert into dm_skilled_person_info
        (id, delete_flag, create_time, create_user_id, create_org_id, name, birthday, educational_qualification, phone,
        management_unit_id, belong_unit_id,
        recruitment_property,work_type,have_certificate_flag,certificate_name,issuing_authority,skills_content,remark)
        values
        
            (#{item.id},#{item.deleteFlag},#{item.createTime},#{item.createUserId},#{item.createOrgId},#{item.name},#{item.birthday},#{item.educationalQualification},#{item.phone},
            #{item.managementUnitId},#{item.belongUnitId},#{item.recruitmentProperty},#{item.workType},#{item.haveCertificateFlag},#{item.certificateName},#{item.issuingAuthority},#{item.skillsContent},#{item.remark})
        
    

 二、另外配上前端（使用layui框架）：

1、显示列表：

 
    function loadTable() {
        var keyValues = form.getValue('searchForm');
        var config = {
            url: publicUrl + 'business/skilledPerson/list',
            where: keyValues,
            cols: [[
                {
                    type: 'checkbox',
                    title: '',
                    width: 80,
                    align: 'center'
                },
                {
                    type: 'numbers',
                    title: '序号',
                    width: 80,
                    align: 'center'
                },
                {
                    field: 'id',
                    hide: true,
                    align: 'center'
                },
                {
                    field: 'name',
                    title: '姓名',
                    align: 'center',
                },
                {
                    field: 'educationalQualificationName',
                    title: '学历',
                    align: 'center',
                },

                {
                    field: 'haveCertificateFlag',
                    title: '是否持证',
                    align: 'center',
                    templet: function (d) {
                        if (d.haveCertificateFlag != null) {
                            return Whether.get(d.haveCertificateFlag + '');;
                        } else {
                            return '否';
                        }
                    }
                },
               
            ]],
            even: true, //开启隔行背景
            page: true,
            done: function (res) {
                tdTitle();
            }
        };
        $.table.init(config);
    }

2、点击显示show.html页面弹窗：

 
    function show(obj) {
        var url = './show.html';
        var config = {
            area: ["75%", "92%"],
            title: "详情",
            content: url,
            btn: ['关闭'],
            tranParam: function () {
                return {
                    id: obj.data.id
                }
            },
            cancel: function (index) {
                return true;
            }
        };
        $.modal.open(config);
    }

 show.html页面获取详情：

    //初始化表单数据
    function transmission(data) {
        // 附件模块
        createAttachment();
        // 详情查询
        $.common.ajax({
            url: "business/skilledPerson/query",
            data: {id: data.id},
            success: function (r) {
                if (r.code != 2000) {
                    $.modal.alert("查询错误");
                }
                if (r.code == 2000) {
                    $("#id").text(g_FormatStr(r.data.id));
                    $("#name").text(g_FormatStr(r.data.name));
                    $("#birthday").text(g_FormatDate(r.data.birthday, "yyyy-MM"));
                    $("#educationalQualificationName").text(g_FormatStr(r.data.educationalQualificationName));
                    $("#managementUnitName").text(g_FormatStr(r.data.managementUnitName));
                    $("#belongUnitName").text(g_FormatStr(r.data.belongUnitName));
                    $("#phone").text(g_FormatStr(r.data.phone));
                    $("#recruitmentPropertyName").text(g_FormatStr(r.data.recruitmentPropertyName));
                    $("#workType").text(g_FormatStr(r.data.workTypeName));
                    $("#haveCertificateFlag").text(g_FormatStr(r.data.haveCertificateFlagName));
                    $("#certificateName").text(g_FormatStr(r.data.certificateName));
                    $("#issuingAuthority").text(g_FormatStr(r.data.issuingAuthority));
                    $("#skillsContent").text(g_FormatStr(r.data.skillsContent));
                    $("#remark").text(g_FormatStr(r.data.remark));
                }
                // 附件展示
                showFileList(r.data);
                form.render();

            }
        })
    }

3、添加：add.html弹窗

    
    function add() {
        var config = {
            area: ["75%", "80%"],
            title: "添加",
            content: './add.html',
            btn: ['提交', '关闭'],

            yes: function (index, layero) {
                $.modal.load();
                var result = $.modal.submitAndResult(index, layero, constant.submit_state);
                $.modal.closeLoad();
                $.modal.btnSureHandle(result, 'add', index, true, loadTable);
            },
            btn2: function (index, layero) {
                return true;
            }
        };
        $.modal.open(config);
    }

add.html页面提交：

 
    function submit(index, layero, type) {
        //表单验证
        if (!formVerify.doVerify('skilledPersonForm')) {
            return false;
        }
        //检查输入的账号长度
        form.verify({
            account: function(value, item){
                var max = item.getAttribute('lay-max');
                if(value.length > max){
                    return '字数不能大于'+max+'个字符的长度';
                }
            }
        });
        var data = $("#skilledPersonForm").serializeJson();
        var url = 'business/skilledPerson/add';
        var result;
        $.common.ajax({
            url: url,
            data: data,
            async: false,
            success: function (r) {
                result = r;
                $.modal.btnSureHandle(result, 'add');
            }
        });
        return result;
    }

4、修改： edit.html弹窗

  
    function edit() {
        //请选中一行数据
        if (!$.table.verificationTableIsOnly('datatable')) {
            return;
        }
        var checks = table.checkStatus("datatable");
        var data = checks.data[0]; //idTest 即为基础参数 id 对应的值
        var config = {
            area: ["75%", "92%"],
            title: "修改",
            content: "./edit.html?id=" + data.id,
            btn: ['提交', '关闭'],
            tranParam: function () {
                return {}
            },
            yes: function (index, layero) {
                $.modal.load();
                var result = $.modal.submitAndResult(index, layero, constant.submit_state);
                $.modal.closeLoad();
                $.modal.btnSureHandle(result, 'edit', index, true, loadTable);
            },
            btn2: function (index, layero) {
                return true;
            }
        };
        $.modal.open(config);
    }

 edit.html页面回显、提交修改：

    
    function init() {
        // 详情查询
        $.common.ajax({
            url: "business/skilledPerson/query",
            data: {
                'id': id
            },
            success: function (r) {
                if (r.code != 2000) {
                    $.modal.alert("查询错误");
                }
                if (r.code == 2000) {
                    $("#id").val(r.data.id);
                    $("#name").val(r.data.name);
                    $("#birthday").val(g_FormatDate(r.data.birthday, "yyyy-MM"));
                    $("#educationalQualification").val(r.data.educationalQualification);
                    $("#managementUnitId").val(r.data.managementUnitId);
                    $("#managementUnitName").val(r.data.managementUnitName);
                    $("#belongUnitId").val(r.data.belongUnitId);
                    $("#belongUnitName").val(r.data.belongUnitName);
                    $("#phone").val(r.data.phone);
                    $("#recruitmentProperty").val(r.data.recruitmentProperty);
                    $("#workType").val(r.data.workType);
                    $("#haveCertificateFlag").val(r.data.haveCertificateFlag);
                    if (r.data.certificateName != ''){
                        $("#certificateName").val(r.data.certificateName);
                    }
                    if (r.data.issuingAuthority != ''){
                        $("#issuingAuthority").val(r.data.issuingAuthority);
                    }
                    if (r.data.skillsContent != ''){
                        $("#skillsContent").val(r.data.skillsContent);
                    }
                    $("#remark").val(r.data.remark);
                    if (r.data.haveCertificateFlag == constant_yesOrNo.no){
                      //隐藏对应div  
                   document.getElementById("isHaveCertificateFlag").style.display="none";
                        //取消验证
                        $("#certificateName").attr('lay-verify','')
                        $("#issuingAuthority").attr('lay-verify','')
                        $("#skillsContent").attr('lay-verify','')
                    }
                }
                // 附件展示
                showFileList(r.data);
                form.render();
            }
        })
    }



    
    function submit(index, layero, type) {
        //表单验证
        if (!formVerify.doVerify('skilledPersonForm')) {
            return false;
        }
        //检查输入的账号长度
        form.verify({
            account: function (value, item) {
                var max = item.getAttribute('lay-max');
                if (value.length > max) {
                    return '字数不能大于' + max + '个字符的长度';
                }
            }
        });
        //序列化json数据
        var data = $("#skilledPersonForm").serializeJson();
        var url = 'business/skilledPerson/update';
        var result;
        $.common.ajax({
            url: url,
            data: data,
            async: false,
            success: function (r) {
                result = r;
                $.modal.btnSureHandle(result, 'edit');
            }
        });
        return result;
    }

 5、删除

  
    function del() {
        //请选中一行数据
        if (!$.table.verificationTableIsNotNull('datatable')) {
            return;
        }
        //获取选中项
        var obj = table.checkStatus('datatable');
        var ids = new Array();
        for (var i = 0; i < obj.data.length; i++) {
            ids.push(obj.data[i].id);
        }
        $.modal.confirm('确定删除？', {icon: 3, title: '提示'}, function (index) {
            $.modal.load();
            $.common.ajax({
                url: 'business/skilledPerson/delete',
                data: {"ids": ids.join(",")},
                async: false,
                success: function (r) {
                    if (r.code == 2000) {
                        $.modal.closeLoad();
                        $.modal.btnSureHandle(r, 'del', null, null, loadTable());
                    } else {
                        $.modal.closeLoad();
                        $.modal.msgError('删除失败');
                    }
                }
            });
        });
    }

6、导入：跳转到导入页面import.html

    //导入
    function inport() {
        var iframeWin;
        layer.open({
            type: 2,
            area: ["500px", "350px"],
            fix: false,
            //不固定
            shade: 0.3,
            title: "导入",
            content: "./import.html",
            btn: ['关闭'],
            // 弹层外区域关闭
            shadeClose: false,
            yes: false,
            cancel: function (index) {
                return true;
            },
            success: function (layero, index) {
                iframeWin = layero.find('iframe')[0];
                iframeWin.contentWindow.init();
            }
        });
    }

导入完整页面：import.html：

    
    
    
    
    
    
    

    



    
        
第一步：下载导入模板，点击下载
        
第二步：选择需要导入的excel文件，导入的excel文件必须按模板填写
        
            选择文件
        
        
第三步：点击开始上传按钮导入文件，点击取消按钮侧关闭页面
        
            开始上传
        
        

    

效果：

 7、导出：

    //导出
    function Export() {
        parent.window.location.href = publicUrl + "business/skilledPerson/export";
    }