springboot框架下如何循环数据库id进行更新数据库。
目前数据库的字段有id、labName、startTime、endTime、useTime,useTime=endTime-startTime。
按照目前已有代码,在Postman中测试接口,每次输入一个id时就可以计算出一个useTime。
目前有两个问题:
1、如果已知startTime和endTime,怎样循环计算更新整个数据表的useTime,即xml里面的id=#{id}这个id需要循环整个数据表。
2、怎样把对应labName的下一行的startTime赋值到上一行的endTime,也就是筛选出labName,id号不一定连续,把下一行的startTime赋值到上一行的endTime。
目前已有代码如下:
controller部分:
package com.example.time.controller;
import com.example.time.entity.Time;
import com.example.time.mapper.TimeMapper;
import com.example.time.service.TimeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
@RestController
@RequestMapping("/time")
public class TimeController {
@Autowired
private TimeMapper timeMapper;
@Resource
private TimeService timeService;
@PostMapping("/updateUseTime")
public Integer updateUseTime(@RequestBody Time time){
return timeService.updateUseTime(time);
}
}
entity部分:
package com.example.time.entity;
import lombok.Data;
import java.time.LocalDateTime;
import java.util.Date;
@Data
public class Time {
private Integer id;
private String labName;
private Date startTime;
private Date endTime;
private String useTime;
mapper部分:
package com.example.time.mapper;
import com.example.time.entity.Time;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface TimeMapper {
int updateUseTime(Time time);
}
xml部分:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.time.mapper.TimeMapper">
<update id="updateUseTime">
update table set useTime=(select datediff(minute,startTime,endTime) from table where id=#{id})
<where>
id=#{id}
</where>
</update>
</mapper>
service部分:
package com.example.time.service;
import com.example.time.entity.Time;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TimeService {
int updateUseTime(Time time);
}
Ipml部分:
package com.example.time.service.impl;
import com.example.time.entity.Time;
import com.example.time.mapper.TimeMapper;
import com.example.time.service.TimeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
/**
* 业务层实现类
*/
@Service
public class TimeServiceImpl implements TimeService{
@Autowired
private TimeMapper timeMapper;
@Transactional
@Override
public int updateUseTime(Time time) {
return timeMapper.updateUseTime(time);
}
}