Java中用sql语句显示最近30天每日登录量每日登录量，并以echarts页面统计出最近15天登录量

最新推荐文章于 2024-08-18 10:59:07 发布

peizi丫

最新推荐文章于 2024-08-18 10:59:07 发布

阅读量5.1k

点赞数 4

分类专栏： echarts sql语句统计源码 java 文章标签： sql统计 echarts统计 Java中用sql语句按天统计统计最近30天数据

本文链接：https://blog.csdn.net/qq_37258878/article/details/79570037

版权

java 同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

echarts

1 篇文章 0 订阅

订阅专栏

sql语句统计

1 篇文章 0 订阅

订阅专栏

需求：Java中统计每日登录总数，页面显示最近30 天数据，并以条形统计图形式显示出来。

效果图（新手，欢迎指教）

sql语句如下：

步骤1.：按天数统计每日登录量

select count(id) as 每天操作数量,to_char(CREATE_TIME, 'yyyy/mm/dd') 日期 from 表名 group by to_char(CREATE_TIME, 'yyyy/mm/dd') order by to_char(CREATE_TIME, 'yyyy/mm/dd') desc

步骤2：显示最近30天每日登录量

select count(id) as num ,to_char(CREATE_TIME, 'yyyy/mm/dd') as data from organ_login_log where CREATE_TIME between sysdate-30 and sysdate group by to_char(CREATE_TIME, 'yyyy/mm/dd') order by to_char(CREATE_TIME, 'yyyy/mm/dd') desc

步骤3：后台Java代码

@RequestMapping(value = "/countLogin", method = { RequestMethod.POST, RequestMethod.GET })
	public String countLogin(SecurityUserRequest request, JSONResultResponse response) {
		Session session = FlowEnvironmentImpl.getCurrentSession();
		//列表显示统计最近30天的登录数据
		List<?> listCountLogins = session.createSQLQuery("select count(id) as num ,to_char(CREATE_TIME, 'yyyy/mm/dd') as data from organ_login_log  where CREATE_TIME  between sysdate-30 and sysdate group by to_char(CREATE_TIME, 'yyyy/mm/dd')  order by to_char(CREATE_TIME, 'yyyy/mm/dd') desc" ).list();
		request.setAttribute("listCountLogins", listCountLogins);//页面显示数据
		//统计图显示最近15天登录数据
		@SuppressWarnings("unchecked")
		List<Object[]> countLogins = session.createSQLQuery("select count(id) as num ,to_char(CREATE_TIME, 'yyyy/mm/dd') as data from organ_login_log  where CREATE_TIME  between sysdate-15 and sysdate group by to_char(CREATE_TIME, 'yyyy/mm/dd')  order by to_char(CREATE_TIME, 'yyyy/mm/dd') desc" ).list();
		JSONArray jsoncount = new JSONArray();
		//将每日登录总数转化为数组
		for (int i = 0; i < countLogins.size(); i++) {
			String count = countLogins.get(i)[0].toString();
			jsoncount.put(count);
		}
		request.setAttribute("jsoncount", jsoncount);//得到每日登录次数["1","13","25","22","18","1","15","23","28","26","14"]
	
		//将日期转化为数组
		JSONArray jsondata = new JSONArray();
		for (int i = 0; i < countLogins.size(); i++) {
			String data = countLogins.get(i)[1].toString();
			jsondata.put(data);
		}
		request.setAttribute("jsondata", jsondata);//得到日期["2018/03/15","2018/03/14","2018/03/13","2018/03/12","2018/03/09","2018/03/08","2018/03/07","2018/03/06","2018/03/05","2018/03/02","2018/03/01"]

		return "/sysLog/list_loginLog";
	}

步骤4：jsp页面显示数据，用列表、统计图两种形式显示显示

<%@ page contentType="text/html; charset=UTF-8"%>
<%@ include file="/common/taglib.jsp"%>
<!DOCTYPE html>
<html lang="en">
<head>
<common:metas title="登录日志" />
<link rel="stylesheet" href="${path}/admin/css/style.css">
<script type="text/javascript" src="${path}/admin/js/jquery.min.js"></script>
<script type="text/javascript" src="${path}/admin/js/tabs&toggle.js"></script>
<script type="text/javascript" src="${path}/admin/js/jquery.dialog.js"></script>
<script type="text/javascript" src="${path}/common/layer-v3.1.0/layer/layer.js"></script>
<jsp:include page="/WEB-INF/jsps/common/embed/script.jsp" />
<script type="text/javascript" src="${path}/common/js/jquery.ext.js"></script>
<script type="text/javascript" src="${path}/common/echarts_v3.8.4/echarts.js"></script>
<script type="text/javascript" src="${path}/common/echarts_v3.8.4/westeros.js"></script>
<script type="text/javascript">
$(document).ready(function() {
	$(this).settingSelectionDefaultValue();
	var myChart = echarts.init(document.getElementById('main'));
	var countLogins = ${jsoncount};//获取登录次数作为Y轴参数
	var loginData=${jsondata};//获取登录日期作为X轴参数
	option = {
			title: {
			left: 'center',
            text: '按天统计登录数',
           },
		    color: ['#3398DB'],
		    tooltip : {
		        trigger: 'axis',
		        axisPointer : {            // 坐标轴指示器，坐标轴触发有效
		            type : 'shadow'        // 默认为直线，可选为：'line' | 'shadow'
		        }
		    },
		    grid: {
		        left: '3%',
		        right: '4%',
		        bottom: '3%',
		        containLabel: true
		    },
		    xAxis : [
		        {
		            type : 'category',
		            data : loginData,
		            name:'日期',
		            axisTick: {
		                alignWithLabel: true
		            }
		        }
		    ],
		    yAxis : [
		        {
		            type : 'value',
		            name:'次数',
		            axisLabel:{
		            	formatter:countLogins
		            }
		        }
		    ],
		    series : [
		        {
		            type:'bar',
		            barWidth: '70%',
		            data:countLogins
		        }
		    ]
		};
	myChart.setOption(option);
	window.onresize = function () {
		myChart.resize();
	};
});
</script>
<body>
	<div id="container">
        <!-- 	树状图显示数据 -->
		<div id="main" style="width: 100%; height: 400px;"></div>
        <!--    列表显示数据 -->
		<div class="page-box">
			<div class="page-list-list-box">
				<table cellpadding="0" cellspacing="0" width="100%" border="0" class="list">
					<tr>
						<th width="30%" colspan="2"><span>图表统计最近15天登录数据，列表显示最近30天登录数据</span></th>
					</tr>
					<tr>
						<th width="30%"><span>登录日期</span></th>
						<th width="30%"><span>登录次数</span></th>
					</tr>
					<c:forEach items="${listCountLogins}" var="loginNum">
						<tr>
							<td id="val">${loginNum[1]}</td>
							<td id="key">${loginNum[0]}</td>
						</tr>
					</c:forEach>
					</div>
				</table>
			</div>
		</div>
</body>
</html>