第5章 数据接口发布
这里基于MVC开发范式完成数据接口程序的搭建。
5.1 编写Service
可视化大屏共需要8个数据接口，根据数据接口的返回值类型编写Service层的相关方法如下:
public interface DataInterfaceService { //查询Mysql中某天分钟级别的下单和退单数 JSONObject queryOrderStatsByDate(String dt); //从redis中查询各手机的访问量 JSONObject queryVisitStatsTop5(); //从ck中查询各手机当日的销售额 JSONObject querySalesAmountToday(); //从ck统计每个手机品牌在昨日和今日的总销售额 JSONObject querySalesAmount(); //从ES中查询各来源数 JSONObject querySourceData(); //从ES中查询行业信息 JSONObject queryIndustryStats(); //从hbase中查询各省份今日的订单数和销售额 JSONObject queryProvinceStatToday(); //从hbase中查询各省份的目标完成进度 JSONObject queryProviceCompletion(String city); }
5.2 从MySQL中返回数据
源数据结构:
案例一：查询MySQL中某天分钟级别的下单和退单数。效果图如下:
读取MySQL可以使用JDBC来读取，这里使用Mybatis简化JDBC操作。
5.2.1 封装Bean
@Data @NoArgsConstructor @AllArgsConstructor public class OrderStats { private String dt; private Integer orderCount; private Integer refundCount; }
封装日期，下单数和退单数三个字段。
5.2.2 编写Mapper
@DS(“mysql”) @Mapper public interface MysqlMapper { //查询当天每分钟的退单数和订单数 @Select("SELECT " + " DATE_FORMAT(dt,‘%H:%I’) dt, " + " SUM(orderCount) orderCount, " + " SUM(refundCount) refundCount " + "FROM di " + "WHERE DATE(dt)=#{date} " + "GROUP BY DATE_FORMAT(dt,‘%H:%I’) ") List queryOrderStatsByDate(@Param(“date”) String dt); }
使用@DS注解指定Mapper连接的数据源。编写方法，通过日期查询当天的每分钟的下单数和退单数。
使用@Mapper注解标注类可以让Spring容器启动时，使用Mybatis的动态代理技术在容器中为接口创建一个实例。
5.2.3 编写ServiceImpl
根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。
通常{ }格式可以使用Map或JSONObject对象封装。而[ ]格式可以使用List或JSONArray对象封装。
@Service public class DataInterfaceServiceImpl implements DataInterfaceService { @Autowired private MysqlMapper mysqlMapper; @Override public JSONObject queryOrderStatsByDate(String dt) { if (null == dt){ dt = LocalDate.now().toString(); } List data = mysqlMapper.queryOrderStatsByDate(dt); System.out.println(data); List ocs = data.stream().map(d -> d.getOrderCount()).collect(Collectors.toList()); List rcs = data.stream().map(d -> d.getRefundCount()).collect(Collectors.toList()); List dts = data.stream().map(d -> d.getDt()).collect(Collectors.toList()); JSONObject oJO = new JSONObject(); oJO.put(“name”,“新增订单数”); oJO.put(“data”,ocs); JSONObject rJO = new JSONObject(); rJO.put(“name”,“退单数”); rJO.put(“data”,rcs); List series = new ArrayList<>(2); series.add(oJO); series.add(rJO); JSONObject dataJO = new JSONObject(); dataJO.put(“categories”,dts); dataJO.put(“series”,series); JSONObject result = new JSONObject(); result.put(“status”,0); result.put(“msg”,“”); result.put(“data”,dataJO); return result ; } }
5.2.4 编写Controller
使用@RestController注解可以标识当前类为控制器，并且可以将处理方法的返回值作为数据写入响应体返回。
@RestController public class DataInterfaceController { @Autowired private DataInterfaceService dIService; @RequestMapping(value = “/orderStats”) public Object queryOrderStats(String date){ return dIService.queryOrderStatsByDate(date); } }
5.2.5 测试
5.3 从Redis中返回数据
源数据结构: key=hotphone, valueType=zset。member为手机品牌，score为热度值。
案例二：在Redis中查询实时访问量前5的热点手机品牌。效果图:
5.3.1 封装Bean
此案例需要的数据字段个数较少，可以不封装Bean。
5.3.2 编写Mapper
Redis为NoSQL数据库，可以使用Spring提供的RedisTemplate客户端进行操作，因此也无需封装Mapper。
5.3.3 编写ServiceImpl
根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。
@Service public class DataInterfaceServiceImpl implements DataInterfaceService { @Autowired private RedisTemplate redisTemplate; @Override public JSONObject queryVisitStatsTop5() { Set typedTuples = redisTemplate.opsForZSet().reverseRangeWithScores(redisDataKey, 0, 4); List scores = new ArrayList<>(); List brands = new ArrayList<>(); List series = new ArrayList<>(1); for (ZSetOperations.TypedTuple t : typedTuples) { scores.add(t.getScore()); brands.add(t.getValue()); } JSONObject result = getJsonObject(scores, brands, series); return result; } }
5.3.4 编写Controller
使用@RestController注解可以标识当前类为控制器，并且可以将处理方法的返回值作为数据写入响应体返回。
@RestController public class DataInterfaceController { @Autowired private DataInterfaceService dIService; @RequestMapping(value = “/phoneVistTop5”) public Object queryPhoneVistTop5(){ return dIService.queryVisitStatsTop5(); } }
5.3.5 测试
5.4 从Clickhouse中返回数据
源数据结构:
案例三：从ck中查询各手机当日的销售额。效果图:
案例四：从ck中计算每个手机品牌昨天和今天的总销售额。效果图:
读取Clickhouse可以使用JDBC来读取，这里使用Mybatis简化JDBC操作。
5.4.1 封装Bean
//案例三
@Data @NoArgsConstructor @AllArgsConstructor public class PhoneSales { private String brand; private Double salesAmount; } //案例四 @Data @NoArgsConstructor @AllArgsConstructor public class PhoneSalesCompare { private String brand; private Double todaySalesAmount; private Double yestodaySalesAmount; }
5.4.2 编写Mapper
@Mapper @DS(“ck”) public interface CKMapper { //案例三 @Select(“select brand,sum(saleAmount) salesAmount from di where dt = #{dt} group by brand”) List queryPhoneSalesToday(String dt); //案例四 @Select("select " + " ifNull(t1.brand,t2.brand) brand, " + " ifNull(todayAmount,0.0) todaySalesAmount, " + " ifNull(yestodayAmount,0.0) yestodaySalesAmount " + "from " + "(select brand,sum(saleAmount) todayAmount from di where toDate(dt) = today() group by brand) t1 " + "full join " + "(select brand,sum(saleAmount) yestodayAmount from di where toDate(dt) = yesterday() group by brand) t2 " + "on t1.brand = t2.brand ") List queryPhoneSales(); }
5.4.3 编写ServiceImpl
根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。
@Service public class DataInterfaceServiceImpl implements DataInterfaceService { @Autowired private CKMapper ckMapper; //案例三 @Override public JSONObject querySalesAmountToday() { List datas = ckMapper.queryPhoneSalesToday(LocalDate.now().toString()); List scores = new ArrayList<>(); List brands = new ArrayList<>(); List series = new ArrayList<>(1); for (PhoneSales data : datas) { scores.add(data.getSalesAmount()); brands.add(data.getBrand()); } JSONObject result = getJsonObject(scores, brands, series); return result; }
//案例四
@Override public JSONObject querySalesAmount() { List data = ckMapper.queryPhoneSales(); List today = new ArrayList<>(); List yesterday = new ArrayList<>(); List brands = new ArrayList<>(); List series = new ArrayList<>(2); for (PhoneSalesCompare d : data) { today.add(d.getTodaySalesAmount()); yesterday.add(d.getYestodaySalesAmount()); brands.add(d.getBrand()); } JSONObject todayJO = new JSONObject(); JSONObject yesterdayJO = new JSONObject(); JSONObject result = new JSONObject(); JSONObject dataJO = new JSONObject(); todayJO.put(“name”,“今天”); todayJO.put(“data”,today); yesterdayJO.put(“name”,“昨天”); yesterdayJO.put(“data”,yesterday); series.add(todayJO); series.add(yesterdayJO); dataJO.put(“categories”,brands); dataJO.put(“series”,series); result.put(“status”,0); result.put(“msg”,“”); result.put(“data”,dataJO); return result; } }
5.4.4 编写Controller
@RestController public class DataInterfaceController { @Autowired private DataInterfaceService dIService; //案例三 @RequestMapping(value = “/phoneSalesAmount”) public Object queryPhoneSalesAmountToday(){ return dIService.querySalesAmountToday(); } //案例四 @RequestMapping(value = “/phoneSalesAmountCompare”) public Object queryPhoneSalesAmount(){ return dIService.querySalesAmount(); } }
5.4.5 测试
案例三：
案例四：
5.5 从ES中返回数据
源数据结构:
{ “mappings”: { “_doc”: { “properties”: { “_class”: { “type”: “keyword”, “index”: false, “doc_values”: false }, “id”: { “type”: “long” }, “name”: { “type”: “keyword” }, “profession”: { “type”: “keyword” }, “sex”: { “type”: “keyword” }, “source”: { “type”: “keyword” } } } } }
案例五：查询ES中手机购买用户的访问渠道统计信息。效果图:
案例六：查询ES中手机购买用户的行业背景及性别比例。效果图:
ES是NoSql数据库，这里使用SpringData中提供的ElasticsearchRestTemplate客户端访问数据库。
5.5.1 封装Bean
封装Bean映射ES目标index中的数据模型。
@Data @NoArgsConstructor @AllArgsConstructor @Document(indexName = “di”) public class Customer { @Id private Integer id; @Field(type = FieldType.Keyword) private String name; @Field(type = FieldType.Keyword) private String sex; @Field(type = FieldType.Keyword) private String source; @Field(type = FieldType.Keyword) private String profession; }
5.5.2 编写Dao及Impl
public interface ESDao { SearchHits queryFromES(AbstractAggregationBuilder aggregationBuilder,Class t); } 在ESDao中编写通用的聚合查询方法。并提供实现如下： @Repository public class ESDaoImpl implements ESDao { @Autowired private ElasticsearchRestTemplate et; @Override public SearchHits queryFromES(AbstractAggregationBuilder aggregationBuilder, Class t) { Query query = new NativeSearchQueryBuilder() .addAggregation(aggregationBuilder) .build(); SearchHits searchHits = et.search(query, t); return searchHits; } }
5.5.3 编写ServiceImpl
根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。
@Service public class DataInterfaceServiceImpl implements DataInterfaceService { @Autowired private ESDao esDao; //案例五 @Override public JSONObject querySourceData() { TermsAggregationBuilder aggregation = AggregationBuilders.terms(“sc”) .field(“source”); SearchHits searchHits = esDao.queryFromES(aggregation, Customer.class); Aggregations aggregations = searchHits.getAggregations(); Terms tagg= aggregations.get(“sc”); List elasticBucket = tagg.getBuckets(); List data = new ArrayList<>(); elasticBucket.forEach(el -> { data.add(new SourceInfo(el.getKeyAsString(),el.getDocCount())); }); JSONObject jsonObject = new JSONObject(); jsonObject.put(“status”,0); jsonObject.put(“msg”,“”); jsonObject.put(“data”,data); return jsonObject; } //案例六 @Override public JSONObject queryIndustryStats() { TermsAggregationBuilder termsAggregationBuilder = AggregationBuilders .terms(“pc”).field(“profession”).size(10) .subAggregation(AggregationBuilders.terms(“sc”).field(“sex”).size(2)); SearchHits searchHits = esDao.queryFromES(termsAggregationBuilder, Customer.class); Aggregations aggregations = searchHits.getAggregations(); List categeryList = new ArrayList<>(); List maleList = new ArrayList<>(); List femaleList = new ArrayList<>(); Terms byCompanyAggregation = aggregations.get(“pc”); List elasticBucket = byCompanyAggregation.getBuckets(); elasticBucket.forEach(el -> { categeryList.add(el.getKeyAsString()); Terms sexAggregation = el.getAggregations().get(“sc”); List buckets = sexAggregation.getBuckets(); buckets.forEach(d -> { if (“男”.equals(d.getKeyAsString())) { double malePercent = d.getDocCount() * 100 / el.getDocCount() ; maleList.add((int)malePercent); femaleList.add(100 - (int)malePercent); } }); }); List series = Arrays.asList( new IndustryInfo(“男”, “%”, maleList), new IndustryInfo(“女”, “%”, femaleList) ); JSONObject dataJO = new JSONObject(); dataJO.put(“categories”,categeryList); dataJO.put(“series”,series); dataJO.put(“yUnit”,“%”); JSONObject resultJO = new JSONObject(); resultJO.put(“status”,0); resultJO.put(“msg”,“”); resultJO.put(“data”,dataJO); return resultJO; } }
5.5.4 编写Controller
@RestController public class DataInterfaceController { @Autowired private DataInterfaceService dIService; //案例五 @RequestMapping(value = “/sourceInfo”) public Object querySourceInfo(){ return dIService.querySourceData(); } //案例六 @RequestMapping(value = “/sexcompare”) public Object querySexcompare(){ return dIService.queryIndustryStats(); } }
5.5.5 测试
案例五：
案例六：
5.6 从HBase中返回数据
源数据结构:
案例七：查询各省份今日的订单数和销售额。效果图:
案例八：查询各省份今日的千万销售额目标完成进度。效果图:
HBase是一个NoSQL数据库，这里使用Phoenix编写SQL查询HBase。Phoenix支持JDBC操作，这里使用Mybatis简化JDBC操作。
5.6.1 封装Bean
@Data @AllArgsConstructor @NoArgsConstructor public class ProvinceStat { private String name; private Double value; private Integer sizeValue; private String url = “sugar.baidu.com”; }
5.6.2 编写Mapper
@Mapper @DS(“hbase”) public interface HBaseMapper { //案例七 @Select("select province name , sum(ordercount) sizeValue , sum(saleamount) “value” " + " from di " + " where substr(to_char(saletime),1,10) = #{date} " + " group by province") List queryProvinceStatToday(String date); //案例八 @Select("select sum(saleamount) " + " from di " + " where substr(to_char(saletime),1,10) = #{date}" + " and province = #{name}") Double queryCompletionByProvinceName(@Param(“name”) String name,@Param(“date”) String date); }
5.6.3 编写ServiceImpl
根据可视化图表中接口需要返回的数据格式，编写Service的方法实现。
@Service public class DataInterfaceServiceImpl implements DataInterfaceService { @Autowired private HBaseMapper hBaseMapper; //案例七 @Override public JSONObject queryProvinceStatToday() { List mapData = hBaseMapper.queryProvinceStatToday(LocalDate.now().toString()); JSONObject dataJO = new JSONObject(); dataJO.put(“mapData”,mapData); dataJO.put(“valueName”,“销售额”); dataJO.put(“sizeValueName”,“下单数”); JSONObject result = new JSONObject(); result.put(“status”,0); result.put(“msg”,“”); result.put(“data”,dataJO); return result; } //案例八 @Override public JSONObject queryProviceCompletion(String city) { Double amount = hBaseMapper.queryCompletionByProvinceName(city, LocalDate.now().toString()); double completion = 0; if (amount==null){ completion = 0; }else if (amount >= 1000000){ completion = 100; }else{ completion = amount / 10000000 * 100; } JSONObject result = new JSONObject(); result.put(“status”,0); result.put(“msg”,“”); result.put(“data”,completion); return result; } }
5.6.4 编写Controller
@RestController public class DataInterfaceController { @Autowired private DataInterfaceService dIService; //案例七 @RequestMapping(value = “/provinceStatsToday”) public Object queryProvinceStatsToday(){ return dIService.queryProvinceStatToday(); } //案例八 @RequestMapping(value = “/provinceCompletionToday”) public Object queryProviceCompletion(String city){ return dIService.queryProviceCompletion(city); } }
5.6.5 测试
案例七：
案例八:
5.7 发布数据接口
将程序打包后部署到拥有公网IP的机器，或者也可以在window本地启动应用程序。
之后使用内网穿透工具，将项目的访问端口暴露到公网。
5.8 对接SugarBI
在SugarBI管理界面中点击编辑按钮：
点击对应图例，在后侧菜单栏中进行编辑，选择数据接收方式为API拉取，并填写之前控制器中对应方法绑定的API，调整数据刷新时间。
点击调试按钮，可以对数据格式是否符合要求进行验证：
之后点击保存后可以将BI看板分享或者发布。
