package offline.repository; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @Author shaowei.Liu * @Date 2020/11/5 19:48 * @Description 查询离线数据: appKey对应的流量 */ public class TkioOfflineRepository extends JdbcDaoSupport { public Map<String,Double> findOfflineResult(String startDate,String endDate){ String sql = "SELECT appid as appkey,sum(if(click_num is null,0,click_num))+sum(if(num_impression is null,0,num_impression)) as appVal " + "FROM measures_trackingio_new " + "where DATE_FORMAT(ds,'%Y-%m-%d')>='"+startDate+"' and DATE_FORMAT(ds,'%Y-%m-%d')<='"+endDate+"' " + "GROUP BY appid"; List<Map<String, Double>> list = this.getJdbcTemplate().query(sql, new RowMapper<Map<String, Double>>() { @Override public Map<String, Double> mapRow(ResultSet rs, int rowNum) throws SQLException { String key = rs.getString("appkey"); Double val = rs.getDouble("appVal"); Map<String,Double> row = new HashMap<>(); row.put(key, val); return row; } }); Map<String,Double> result = new HashMap<>(); for(Map<String,Double> map:list){ for(String key:map.keySet()){ result.put(key,map.get(key)); } } return result; } }