考勤记录表——在导入时——应该做判断——避免重复导入成功报错

1.现在是，可以多次成功导入同一个文档，导致数据展示出错

期望：

导入文件时，如果这个文件名内数据select值不为0，那么覆盖表数据（update）

如果查数据为0，那么添加(insert)

思路：

1.impl--导入---添加数据（set）

2.导入做处理

select 查是否有数据

返回0  没数据,添加数据  insert

返回>0 有数据，覆盖源数据 update

第一步：

去数据库mapper.xml查看，是否都有值

count()  可以查看返回数据的数量

这是数据库初始查询语句，查询具体字段值

select id, user_name, year, month, day, user_id, att_time, status, remark, section, typefrom t_attend_record
and year = #{year}and month = #{month}and day = #{day}and user_id = #{userId}and att_time = #{attTime}and status = #{status}and section = #{section}and type = #{type}

 我们需要的不是具体字段值是多少，而是需要他一共查询的数量

做法：

select count(*)from t_attend_recordand year = #{year}and month = #{month}and day = #{day}and user_id = #{userId}and section = #{section}and type = #{type}

 这就查出来了你所查的数据有没有值，如果查询结果为0，那就是没值，接着insert，添加就好

如果>0，那就需要update

回到当前代码，你会发现，id="selectTAttendRecordNum"，这句话爆红。

说明，没有这个mapper方法，你要去到mapper给他添加上。

第二部：Mapper.java

public int selectTAttendRecordNum(TAttendRecord tAttendRecord);

 第三步：service 调mapper（这一步你也可不用再单独写放方法，直接用之前的一些方法调用也可）

去service，再写一遍这个方法：

public int selectTAttendRecordNum(TAttendRecord tAttendRecord);

 然后去impl，实现这个接口

去到impl,你会发现爆红

 alt+enter-----

对应实现类就自己创建好了，对于数据的处理，我们写在这里面 

 第三步 直接在impl里调用mapper

因为我这个是查看导入情况，我们去找impl的导入方法

package com.jhsoft.product.wages.service.impl;import com.jhsoft.product.record.domain.TAttendRecord;
import com.jhsoft.product.record.mapper.TAttendRecordMapper;
import com.jhsoft.product.wages.service.WorkExcelService;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.mapper.SysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;@Service
public class WorkExcelServiceImpl implements WorkExcelService {@Autowiredprivate TAttendRecordMapper tAttendRecordMapper;@Autowiredprivate SysUserMapper userMapper;/*** 导入数据** @param commoditList 数据列表* @return 结果*/@Override@Transactionalpublic String importworkExcel(List> commoditList) {if (StringUtils.isNull(commoditList) || commoditList.size() == 0) {return "导入数据不能为空！";}System.out.println("导入数据");int failureNum = 0;for (Map map : commoditList) {List check = (List) map.get("check");int day = 1;for (int i = 0; i < check.size(); i++) {TAttendRecord tAttendRecord = new TAttendRecord();String stringCellValue = String.valueOf(map.get("stringCellValue"));String a = stringCellValue.replace("年", "-");stringCellValue = a.replace("月", "-").substring(0, 7);//年月String name = String.valueOf(map.get("name"));SysUser sysUser = userMapper.selectUserByUserame(name);sysUser.setAccountNumber(map.get("accountNumber").toString());userMapper.updateUser(sysUser);tAttendRecord.setUserName(sysUser.getUserName());tAttendRecord.setUserId(sysUser.getUserId());//用户idtAttendRecord.setYear(Long.valueOf(stringCellValue.substring(0, 4)));tAttendRecord.setMonth(Long.valueOf(stringCellValue.substring(5, stringCellValue.length())));Long sign = (Long) map.get("sign");tAttendRecord.setSection(sign);//上午或下午//拼接打卡年月日if (day < 10) {stringCellValue += "-0" + day + " ";} else {stringCellValue += "-" + day + " ";}tAttendRecord.setDay(Long.valueOf(day));//放入日day++;//日期加1//取考勤数据String che = check.get(i);SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");int leak = che.indexOf("漏刷");//判断是否包含条件文字  不包含<0int late = che.indexOf("迟到");int leaveEarly = che.indexOf("早退");int leave = che.indexOf("事假");if (leave >= 0) {tAttendRecord.setType(6l);//事假} else if (leak >= 0 || "".equals(che)) {tAttendRecord.setType(4l);//漏刷} else if (late >= 0) {che = che.substring(0, 5);//取前五位stringCellValue += che + ":00";//拼接成时间try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(1l);//迟到} else if (leaveEarly >= 0) {che = che.substring(0, 5);stringCellValue += che + ":00";try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(3l);//早退} else {if (sign==0) {//上午数据stringCellValue += che + ":00";try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(0l);} else {//下午数据stringCellValue += che + ":00";//这里应该判断时间是否过8点  然后记加班，以后再改try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(2l);}}tAttendRecord.setStatus(0L);
//                TAttendRecord tAttendRecord1 = tAttendRecordMapper.selectTAttendRecordById(tAttendRecord.getId());
//                if(tAttendRecord1.toString() !=""|tAttendRecord1 !=null){
//                    int i2 = tAttendRecordMapper.updateTAttendRecord(tAttendRecord);
//                }int i1 = tAttendRecordMapper.insertTAttendRecord(tAttendRecord);}}if (failureNum > 0) {System.out.println("很抱歉，导入失败！共 " + failureNum);} else {System.out.println("导入成功，0条失败");}return "操作成功，有" + failureNum + "条数据导入失败";}}

package com.jhsoft.product.wages.service.impl;import com.jhsoft.product.record.domain.TAttendRecord;
import com.jhsoft.product.record.mapper.TAttendRecordMapper;
import com.jhsoft.product.wages.service.WorkExcelService;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.system.mapper.SysUserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;@Service
public class WorkExcelServiceImpl implements WorkExcelService {
//这一块是导入相关的文件   @Autowiredprivate TAttendRecordMapper tAttendRecordMapper;@Autowiredprivate SysUserMapper userMapper;
/*** 导入数据** @param commoditList 数据列表* @return 结果*/@Override@Transactional
//这一块是把导入的数据全部查出来  public String importworkExcel(List> commoditList) {if (StringUtils.isNull(commoditList) || commoditList.size() == 0) {return "导入数据不能为空！";}System.out.println("导入数据");int failureNum = 0;for (Map map : commoditList) {List check = (List) map.get("check");int day = 1;for (int i = 0; i < check.size(); i++) {TAttendRecord tAttendRecord = new TAttendRecord();String stringCellValue = String.valueOf(map.get("stringCellValue"));String a = stringCellValue.replace("年", "-");stringCellValue = a.replace("月", "-").substring(0, 7);//年月String name = String.valueOf(map.get("name"));SysUser sysUser = userMapper.selectUserByUserame(name);sysUser.setAccountNumber(map.get("accountNumber").toString());userMapper.updateUser(sysUser);tAttendRecord.setUserName(sysUser.getUserName());tAttendRecord.setUserId(sysUser.getUserId());//用户idtAttendRecord.setYear(Long.valueOf(stringCellValue.substring(0, 4)));tAttendRecord.setMonth(Long.valueOf(stringCellValue.substring(5, stringCellValue.length())));Long sign = (Long) map.get("sign");tAttendRecord.setSection(sign);//上午或下午//拼接打卡年月日if (day < 10) {stringCellValue += "-0" + day + " ";} else {stringCellValue += "-" + day + " ";}tAttendRecord.setDay(Long.valueOf(day));//放入日day++;//日期加1//取考勤数据String che = check.get(i);SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");int leak = che.indexOf("漏刷");//判断是否包含条件文字  不包含<0int late = che.indexOf("迟到");int leaveEarly = che.indexOf("早退");int leave = che.indexOf("事假");if (leave >= 0) {tAttendRecord.setType(6l);//事假} else if (leak >= 0 || "".equals(che)) {tAttendRecord.setType(4l);//漏刷} else if (late >= 0) {che = che.substring(0, 5);//取前五位stringCellValue += che + ":00";//拼接成时间try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(1l);//迟到} else if (leaveEarly >= 0) {che = che.substring(0, 5);stringCellValue += che + ":00";try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(3l);//早退} else {if (sign==0) {//上午数据stringCellValue += che + ":00";try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(0l);} else {//下午数据stringCellValue += che + ":00";//这里应该判断时间是否过8点  然后记加班，以后再改try {tAttendRecord.setAttTime(formatter.parse(stringCellValue));} catch (ParseException e) {e.printStackTrace();}tAttendRecord.setType(2l);}}tAttendRecord.setStatus(0L);
//                TAttendRecord tAttendRecord1 = tAttendRecordMapper.selectTAttendRecordById(tAttendRecord.getId());
//                if(tAttendRecord1.toString() !=""|tAttendRecord1 !=null){
//                    int i2 = tAttendRecordMapper.updateTAttendRecord(tAttendRecord);
//                }//        查询数据返回数量
//原本是只有这一句，将上面获取到的数据，调用insert方法，把值全部放进去
//                int i1 = tAttendRecordMapper.insertTAttendRecord(tAttendRecord);

这一块，是调用上面写的，mapper的返回数据，如果没数据，就调用insert方法，有数据就调用update方法。
 int  havenum=tAttendRecordMapper.selectTAttendRecordNum(tAttendRecord);         
 if(havenum==0){ 
        int i1 = tAttendRecordMapper.insertTAttendRecord(tAttendRecord); 
        }{ 
        int i2 = tAttendRecordMapper.updateTAttendRecord(tAttendRecord);
         }
 }}if (failureNum > 0) {System.out.println("很抱歉，导入失败！共 " + failureNum);} else {System.out.println("导入成功，0条失败");}return "操作成功，有" + failureNum + "条数据导入失败";}}

写在for循环里，是因为循环内走到的每一个数据分别做处理

 

int havenum=tAttendRecordMapper.selectTAttendRecordNum(tAttendRecord);
if(havenum==0){int i1 = tAttendRecordMapper.insertTAttendRecord(tAttendRecord);
}{int i2 = tAttendRecordMapper.updateTAttendRecord(tAttendRecord);
}