按月统计注冊人数
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; @Controller
public 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>
执行效果图：
watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQva2VuaGlucw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="">