踩坑--Springboot之Scheduled定时执行insert into ...select联合语句
踩坑--Springboot之Scheduled定时执行insert into ...select联合语句背景踩坑开始
背景
作者：马瑾瑜
由于这个项目的数据库采用的postgresql，是不具备任务调度功能的，需要用pgagent实现，上网搜了搜比较麻烦，刚好用的是springboot架构，配置定时任务几分钟就搞定了，索性就用java服务来完成数据汇聚。
踩坑开始定时任务部分代码
public class ConvergeSheduled {
@Autowired
private InterfaceKpiMapper interfaceKpiMapper;
@Scheduled(cron = "0 0/1 * * * ?")
public void ConvergeData() {
logger.info("<<<<<>>>>>>");
Integer integer = interfaceKpiMapper.ConverageCity();
logger.info("---地市数据入库条数"+integer);
logger.info("<<<<<>>>>>>");
Integer integer1 = interfaceKpiMapper.ConverageCounty();
logger.info("---全国数据入库条数"+integer1);
}
}
mapper部分代码
public interface InterfaceKpiMapper {
Integer ConverageCity();
Integer ConverageCounty();
}
xml代码
insert into nhm.nhm_interface_kpi_city(city,name,province_id,time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps)
select d.city,cy.name,cy.province_id,date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(AVG(a.if_out_traffic),2)if_out_traffic,round(AVG(a.if_in_pps),2)if_in_pps,
round(AVG(a.if_in_utility),2)if_in_utility,round(AVG(a.if_in_traffic),2)if_in_traffic,round(AVG(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a
inner join rm.rm_device d on d.device_id=a.device_uuid
inner join rm.city_cn cy on d.city=cy.city_id
where a.time_stamp> now()-INTERVAL '5 minutes' group by cy.name, d.city,cy.province_id,a.time_stamp;
insert into nhm.nhm_interface_kpi_county(time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps)
select date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(SUM(a.if_out_traffic),2)if_out_traffic,round(SUM(a.if_in_pps),2)if_in_pps,
round(AVG(a.if_in_utility),2)if_in_utility,round(SUM(a.if_in_traffic),2)if_in_traffic,round(SUM(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a
inner join rm.rm_device d on d.device_id=a.device_uuid
inner join rm.city_cn cy on d.city=cy.city_id
where a.time_stamp> now()-INTERVAL '5 minutes' group by a.time_stamp
一顿操作猛如虎，本地也可以执行，然后更新到服务器却…定时任务是ok的，但是sql肯定是没有执行成功的，因为没有数据入库，但是有没有报任何异常。。。
我怀疑是注入有mapper问题,于是直接把mapper干掉，直接通过jdbc操作(此处纯属个人经验不足所致的弯路，如果注入失败是会报错的)。
@Autowired
private JdbcTemplate jdbcTemplate;
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Scheduled(cron = "0 0/1 * * * ?")
public void ConvergeData() {
logger.info("<<<<<>>>>>>");
StringBuilder sss = new StringBuilder();
sss.append("insert into nhm.nhm_interface_kpi_city(city,name,province_id,time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps) ");
sss.append("select d.city,cy.name,cy.province_id,date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(AVG(a.if_out_traffic),2)if_out_traffic,round(AVG(a.if_in_pps),2)if_in_pps, ");
sss.append("round(AVG(a.if_in_utility),2)if_in_utility,round(AVG(a.if_in_traffic),2)if_in_traffic,round(AVG(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a ");
sss.append("inner join rm.rm_device d on d.device_id=a.device_uuid ");
sss.append("inner join rm.city_cn cy on d.city=cy.city_id ");
sss.append("where a.time_stamp> now()-INTERVAL '5 minutes' group by cy.name, d.city,cy.province_id,a.time_stamp");
System.out.println(sss.toString());
jdbcTemplate.update(sss.toString());
jdbcTemplate.update("insert into nhm.nhm_interface_kpi_county(time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps) " +
"select date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(AVG(a.if_out_traffic),2)if_out_traffic,round(AVG(a.if_in_pps),2)if_in_pps, " +
"round(AVG(a.if_in_utility),2)if_in_utility,round(AVG(a.if_in_traffic),2)if_in_traffic,round(AVG(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a " +
"inner join rm.rm_device d on d.device_id=a.device_uuid " +
"inner join rm.city_cn cy on d.city=cy.city_id " +
"where a.time_stamp> now()-INTERVAL '5 minutes' group by a.time_stamp");
}
}
上面的sql用StringBuild是为了排除sql拼接错误的可能。
依旧，本地运行定时任务是OK的，然而更新到服务器后并没有出现奇迹。
于是我找了运维同学，把sql更装到脚本里，通过他那边的方式来定时调度完成。
脚本确实可以执行成功，但是一放到定时任务就不行了。
情况和我用java实现有点相似,于是我怀疑是定时任务的时间和sql里面的时间函数有冲突。now()-INTERVAL '5 minutes'
于是我改用从服务获取时间，传入sql内，并将inert into…select拆分为两个语句，先select，在insert.
果然，查询出了内容。也insert进去了。
就在我以为万事俱备的时候，我把两个sql又合并了
insert into nhm.nhm_interface_kpi_city(city,name,province_id,time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps)
select d.city,cy.name,cy.province_id,date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(SUM(a.if_out_traffic),2)if_out_traffic,round(SUM(a.if_in_pps),2)if_in_pps,
round(AVG(a.if_in_utility),2)if_in_utility,round(SUM(a.if_in_traffic),2)if_in_traffic,round(SUM(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a
inner join rm.rm_device d on d.device_id=a.device_uuid
inner join rm.city_cn cy on d.city=cy.city_id
where a.time_stamp>= to_timestamp(#{time},'yyyy-MM-dd HH24:MI') group by cy.name, d.city,cy.province_id,a.time_stamp
insert into nhm.nhm_interface_kpi_county(time_stamp,if_out_utility,if_out_traffic,if_in_pps,if_in_utility,if_in_traffic,if_out_pps)
select date_trunc('min', a.time_stamp)time_stamp,round(AVG(a.if_out_utility),2)if_out_utility,round(SUM(a.if_out_traffic),2)if_out_traffic,round(SUM(a.if_in_pps),2)if_in_pps,
round(AVG(a.if_in_utility),2)if_in_utility,round(SUM(a.if_in_traffic),2)if_in_traffic,round(SUM(a.if_out_pps),2)if_out_pps from nhm.nhm_interface_kpi a
inner join rm.rm_device d on d.device_id=a.device_uuid
inner join rm.city_cn cy on d.city=cy.city_id
where a.time_stamp>= to_timestamp(#{time},'yyyy-MM-dd HH24:MI') group by a.time_stamp
然后合并后又是没执行成功…
最终定位到mybatis上
虽然是联合语句，没有出参入参，但是select查询出来的是一个集合，如果不标注 出来，mybatis是识别不了的。
折腾了两天，终于把这两个大坑踩完了。到此汇聚完成！
作者：马瑾瑜
