按月统计注册人数
java类：
package com.spring.controller;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.Arrays;import java.util.Date;import java.util.HashMap;import java.util.Hashtable;import java.util.Iterator;import java.util.List;import java.util.Map;import java.util.Map.Entry;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import net.sf.json.JSONArray;import org.springframework.stereotype.Controller;import org.springframework.web.bind.ServletRequestUtils;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.servlet.ModelAndView;@Controllerpublic class EchartController { /** * Echart报表按月份统计 */ @RequestMapping(value = "user/month", method = { RequestMethod.POST, RequestMethod.GET }) public ModelAndView monthStatistics(HttpServletRequest request, HttpServletResponse response) throws IOException { String year = ServletRequestUtils.getStringParameter(request, "year", "2014"); ModelAndView mav = new ModelAndView(); MySqlData mySqlData = new MySqlData(); Map<Object, Object> map = new HashMap<Object, Object>(); map = mySqlData.selectMonth(year); List<Integer> data = new ArrayList<Integer>(); List<String> month = new ArrayList<String>(); // 遍历map /* * Iterator<Entry<Object, Object>> it = map.entrySet().iterator(); while * (it.hasNext()) { * * @SuppressWarnings("rawtypes") Map.Entry entry = (Map.Entry) * it.next(); String key = (String) entry.getKey(); Integer value = * Integer.valueOf((String) entry.getValue()); * System.out.println("--------"+key); data.add(value);//数据 * month.add(key);//月份 } */ // 遍历2map for (Object m : map.keySet()) { Integer value = Integer.valueOf((String) map.get(m)); System.out.println("--------" + m); data.add(value);// 数据 month.add(m + "月");// 月份 } mav.addObject("time", new Date()); // mav.addObject("jsonMap", // JSONArray.fromObject(map));//将map转换为json数组//map转为json时map的key必须是string类型的 mav.addObject("jsonData", JSONArray.fromObject(data));// 将list转换为json数组 mav.addObject("jsonMonth", JSONArray.fromObject(month));// 将list转换为json数组 mav.setViewName("echart/monthEchart");// return mav; } // /////////////////////////////////////////////////////////////////////////////////////////////////////// // /内部类 class MySqlData { private String username; private String password; private Connection connection; private PreparedStatement ps; // //构造函数 public MySqlData() { // TODO Auto-generated constructor stub String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull"; String username = "root"; String password = ""; // 加载驱动程序以连接数据库 try { Class.forName("com.mysql.jdbc.Driver"); connection = DriverManager.getConnection(url, username, password); } // 捕获加载驱动程序异常 catch (ClassNotFoundException cnfex) { System.err.println("装载 JDBC/ODBC 驱动程序失败"); cnfex.printStackTrace(); } // 捕获连接数据库异常 catch (SQLException sqlex) { System.err.println("无法连接数据库"); sqlex.printStackTrace(); } } // 查询方法1 /* * select DATE_FORMAT(date_added,'%Y-%m') as month,count(*) as shumu * from user where DATE_FORMAT(date_added,'%Y')=2014 group by month * order by month; */ public Map<Object, Object> selectMonth(String year) { List<Object> maplist = new ArrayList<Object>(); Map<Object, Object> map = new HashMap<Object, Object>(); try { ps = connection .prepareStatement("select DATE_FORMAT(ptime,'%m') as month,count(*) as shumu from cc_user " + " where DATE_FORMAT(ptime,'%Y')=? group by month order by month;"); ps.setString(1, year); ResultSet rs = ps.executeQuery(); while (rs.next()) { map.put(rs.getInt("month"), rs.getString("shumu")); System.out.println("月份:" + rs.getString("month") + "数目:" + rs.getString("shumu")); maplist.add(map); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return map; } }// 内部类}// /echart类页面：
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'monthEchart.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --><script src="<%=basePath%>js/jquery.min.js"></script><script type="text/javascript" src="<%=basePath%>resources/echart/esl.js"></script> </head> <body> <div id="main" style="height:400px"></div> <script type="text/javascript"> // 路径配置 require.config({ paths:{ 'echarts' : 'resources/echart/echarts', 'echarts/chart/bar' : 'resources/echart/echarts' } }); // 使用 require( [ 'echarts', 'echarts/chart/bar' // 使用柱状图就加载bar模块，按需加载 ], function (ec) { // 基于准备好的dom，初始化echarts图表 var myChart = ec.init(document.getElementById('main')); var jsonData=${jsonData};//接收后台传过来的json数组 alert(jsonData); var jsonMonth=${jsonMonth}; alert(jsonMonth); //便利json数组 /* $.each(json,function(n,value) { alert(value) }); */ var option = { tooltip: { show: true }, legend: { data:['注册人数'] }, xAxis : [ { type : 'category', data : ["一月","二月","三月","四月","五月","六月","七月","八月","九月","十月","十一月","十二月",] } ], yAxis : [ { type : 'value' } ], series : [ { "name":"注册人数", "type":"bar", itemStyle: { normal: { //柱状图颜色 color: '#0080ff', label: { show: true, //position : 'inside', formatter : '{b}', textStyle: { color: '#10E922' } } } }, "data":jsonData } ] }; // 为echarts对象加载数据 myChart.setOption(option); } ); </script> </body></html>运行效果图：
