1、根据sql的select查询语句字段来在VO包中创建对应的VO类
例如sql语句:
SELECT SUBSTRING_INDEX(CONCAT(SUBSTRING_INDEX(city,'市',1),'市'),'省',-1)as 'city',bod51 as 'inBod5',bod52 as 'outBod5' from (SELECT a.region as 'city',bod51,bod52 from sewage_plant a JOIN (select factory_name ,SUM(bod51)as 'bod51',SUM(bod52)as 'bod52' from report_form WHERE MONTH(report_time)=MONTH(CURDATE()) GROUP BY factory_name)b ON a.factory_name=b.factory_name)c
我们可以看到select出来的字段被重命名as为新字段名。则新建类相应的属性名应与此字段名相同
千万不要忘记@Data注解!!!!!!!!!!!!!!!!!!
package com.project.sewageplant3.entity.vo; import lombok.Data; import java.math.BigDecimal; @Data public class StandardSituationVO { private String city; private BigDecimal inBod5; private BigDecimal outBod5; }
2、在对应的mapper层中添加注解和方法
@Repository public interface ReportFormMapper extends BaseMapper<ReportForm> { @Select("SELECT SUBSTRING_INDEX(CONCAT(SUBSTRING_INDEX(city,'市',1),'市'),'省',-1)as 'city',bod51 as 'inBod5',bod52 as 'outBod5' from (SELECT a.region as 'city',bod51,bod52 from sewage_plant a JOIN (select factory_name ,SUM(bod51)as 'bod51',SUM(bod52)as 'bod52' from report_form WHERE MONTH(report_time)=MONTH(CURDATE()) GROUP BY factory_name)b ON a.factory_name=b.factory_name)c") List<StandardSituationVO> standardSituationList(); }
3、在service层的抽象类和实现类中分别添加方法
①抽象类
public List<StandardSituationVO> standardSituationList();
②抽象类的实现类(IDEA可以ctrl+o快捷添加方法)
@Override public List<StandardSituationVO> standardSituationList() { return reportFormMapper.standardSituationList(); //返回接口的方法 }
4、最后在controller层中执行service层方法
@GetMapping("/standardSituationList") public List<StandardSituationVO> StandardSituationList(){ return reportFormService.standardSituationList(); //返回service层接口方法 }