我想实现:当我单击页面时,它将更新表“staffinfo”中的一列,然后显示该表。
我想更新“staffinfo”表中名为“lengthofworking”的列。它的值由另一个名为“bookingtable”的表决定。
以下是mysql查询:
UPDATE crm.staffinfo SET `lengthofworking` = (
select temp.workinghour from (
SELECT b.assignedstfid as staffid, SUM(b.duration) as workinghour
FROM crm.bookingtable b, crm.staffinfo s
where s.staffid = b.assignedstfid
GROUP BY b.assignedstfid) as temp
where temp.staffid = staffinfo.staffid
);
这意味着:当我想得到一个员工的总工作时间时,我需要在“bookingtable”表中汇总每个与员工相关的预订记录的持续时间。
我已经在mysql中测试了这个查询,它是有效的。现在我想在mybatis中实现这个查询。
以下是我所做的:
类内staff.java:
public class Staff {
private Integer staffid;
...
private Double lengthofworking;
}
在staffservice.java接口和staffserviceimpl.java类中:
public interface StaffService {
List<Staff> getAllStaffs(); // get all the staffs
...
int updateStaffWorkingLength(); // update the 'length of working' of a staff
}
@Service
public class StaffServiceImpl implements StaffService{
@Autowired
private StaffMapper sm;
@Override
public List<Staff> getAllStaffs() {
return sm.getAllStaffs();
}
...
@Override
public int updateStaffWorkingLength() {
return sm.updateStaffWorkingLength();
}
}
在staffmapper.java接口中:
@Mapper
@Repository
public interface StaffMapper {
List<Staff> getAllStaffs(); // get all the staffs
...
int updateStaffWorkingLength(); // update the 'length of working' of a staff
}
在staffmapper.xml中:
...
<mapper namespace="com.crm.mapper.StaffMapper">
<select id="getAllStaffs" resultType="Staff" >
select * from crm.staffinfo ;
</select>
...
<select id="updateStaffWorkingLength" parameterType="Staff">
UPDATE crm.staffinfo SET `lengthofworking/minute` = (select temp.workinghour from (SELECT b.assignedstfid as staffid, SUM(b.duration) as workinghour FROM crm.bookingtable b, crm.staffinfo s where s.staffid = b.assignedstfid GROUP BY b.assignedstfid)as temp where temp.staffid = staffinfo.staffid);
</select>
</mapper>
最后,在类staffcontroller.java中:
@RequestMapping("/stafflist")
public String list(Model model){
ss.updateStaffWorkingLength(); //here for update the column
List<Staff> allStaffs = ss.getAllStaffs(); // here for retrieve the data from table 'staffinfo'
model.addAttribute("allStaffs", allStaffs);
return "staff/list";
}
我想强调的是,我已经在“staff”目录中定义了“list.html”。
现在,当我单击链接“/stafflist”时,我得到:
白标错误页
此应用程序没有针对/error的显式Map,因此您将此视为回退。
暂无答案!
目前还没有任何答案,快来回答吧!