统计内容:按照年月,统计消费额度
准备的Dao:按年月统计每个月的销售额 sql语句如下:
需要注意的:1,dao中数组大小跟sql语句查询返回的结果有关系
2,要在jsp页面中。添加显示位置的id。
select year(o_date) nian,MONTH(o_date) yue,sum(total) total from order_info GROUP BY(o_date),Month(o_date) |
第一步:复制echarts文件
第二步:编写dao方法,biz方法;dao如下
public List<Object[]> findOrderToalByMonth() throws Exception { List<Object[]> objList = new ArrayList<Object[]>(); String sql = "select year(o_date) nian,MONTH(o_date) yue,sum(total) total from order_info GROUP BY(o_date),Month(o_date)"; DbUtil.getConnection(); DbUtil.pstmt = DbUtil.conn.prepareStatement(sql); DbUtil.rst = DbUtil.pstmt.executeQuery(); while (DbUtil.rst.next()) { Object[] objs = new Object[3]; objs[0] = DbUtil.rst.getString("nian"); objs[1] = DbUtil.rst.getString("yue"); objs[2] = DbUtil.rst.getString("total"); objList.add(objs); } return objList; } |
第三步:编写servlet
try { List<Object[]> objList=orderInfoBiz.findOrderToalByMonth(); //分别获取统计数据中的月份和月份对应的销售额 List<String> titleList=new ArrayList<String>(); List<Integer> valuList=new ArrayList<Integer>(); for(Object[] objs:objList){ titleList.add(objs[0]+"-"+objs[1]); valuList.add(Integer.parseInt(objs[2].toString())); } //把结果生成为json字符串 Map<String,List> resuMap=new HashMap<String,List>(); resuMap.put("titleList", titleList); resuMap.put("valuList", valuList);
//把resuMap转换为json字符串 String jsonStr=JSONObject.fromObject(resuMap).toString(); System.out.println("jsonStr"+jsonStr); response.setCharacterEncoding("UTF-8"); PrintWriter out=response.getWriter(); out.print(jsonStr); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); response.sendRedirect("error.jsp"); } |
第四步:导入echarts文件,修改jsp页面,添加post提交方式(复制柱形图源码)
$.post("<%=request.getContextPath()%>/BookServlet",{"method":"monthtongji"},function(obj){ var jsonobj=eval("("+obj+")"); var myChart = echarts.init(document.getElementById('yueTjt')); var option = { xAxis: { type: 'category', data:jsonobj.titleList }, yAxis: { type: 'value' }, series: [{ data: jsonobj.valuList, type: 'bar' }] }; myChart.setOption(option); })
|
饼状图
统计内容:按图书类型实现销量的统计(饼状图)
准备的Dao:按图书类型实现销量的统计 sql语句如下:
select type_name,sum(total) total from order_detail od inner join book_info bi on od.b_id = bi.b_id inner join book_type bt on bi.type_id = bt.type_id group by type_name; |
需要额外添加一个实体类:统计的实体类(name sum)
需要注意的:1,dao中数组大小跟sql语句查询返回的结果有关系
2,要在jsp页面中。添加显示位置的id。
第一步:复制echarts文件
第二步:编写dao方法,biz方法;dao如下
public List<TypeTongji> findTypeCount() throws Exception{ String sql="select type_name,sum(total) total from order_detail od inner join book_info bi on od.b_id = bi.b_id inner join book_type bt on bi.type_id = bt.type_id group by type_name"; List<TypeTongji> typetongjiList=new ArrayList<TypeTongji>(); DbUtil.getConnection(); DbUtil.pstmt=DbUtil.conn.prepareStatement(sql); DbUtil.rst=DbUtil.pstmt.executeQuery(); while(DbUtil.rst.next()){ TypeTongji tj=new TypeTongji(); tj.setName(DbUtil.rst.getString("type_name")); tj.setValue(DbUtil.rst.getInt("total")); typetongjiList.add(tj); } return typetongjiList; } |
第三步:编写servlet
try {
List<TypeTongji> typeList=bookTypebiz.findTypeCount(); //分别获取统计数据中的月份和月份对应的销售额 List<String> titleList=new ArrayList<String>();
for(TypeTongji tj:typeList){ titleList.add(tj.getName()); } //把结果生成为json字符串 Map<String,List> resuMap=new HashMap<String,List>(); resuMap.put("titleList", titleList); resuMap.put("valuList", typeList);
//把resuMap转换为json字符串 String jsonStr=JSONObject.fromObject(resuMap).toString(); System.out.println("jsonStr"+jsonStr); response.setCharacterEncoding("UTF-8"); PrintWriter out=response.getWriter(); out.print(jsonStr); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); response.sendRedirect("error.jsp"); } |
第四步:导入echarts文件,修改jsp页面,添加post提交方式(复制饼状图源码)
$.post("<%=request.getContextPath()%>/BookServlet",{"method":"typetongji"},function(obj){ alert("obj"+obj); var jsonobj=eval("("+obj+")"); var myChart = echarts.init(document.getElementById('bingzhuangtu')); var option = { title : { text: '', subtext: '纯属虚构', x:'center' }, tooltip : { trigger: 'item', formatter: "{a} <br/>{b} : {c} ({d}%)" }, legend: { orient: 'vertical', left: 'left', data: jsonobj.titleList }, series : [ { name: '访问来源', type: 'pie', radius : '55%', center: ['50%', '60%'], data:jsonobj.valuList, itemStyle: { emphasis: { shadowBlur: 10, shadowOffsetX: 0, shadowColor: 'rgba(0, 0, 0, 0.5)' } } } ] }; myChart.setOption(option); }) |