SpringBoot对数据库表中的数据进行统计分析

最新推荐文章于 2024-03-18 15:28:23 发布

跟着BOSS有肉吃

最新推荐文章于 2024-03-18 15:28:23 发布

阅读量1.4w

点赞数 3

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/a15028596338/article/details/86367382

版权

SpringBoot 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

数据库表asm_log存放的是某金融机构所有自助机的操作日志：

CREATE TABLE `atm_log` (
  `log_no` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志编号',
  `atm_no` varchar(32) NOT NULL COMMENT '自助机编号',
  `card_id` varchar(32) NOT NULL COMMENT '卡号',
  `operate_name` varchar(32) NOT NULL COMMENT '操作名称',
  `operate_context` text COMMENT '操作内容',
  `operate_time` datetime NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`log_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='自助机日志表';

表中数据如下：

现在要对自助机的日志进行统计分析，形式如下：

1、数据库表atm_log的实体类

package com.cn.demo.count.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "atm_log")
public class AtmLog implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Id
	@Column(name = "log_no")
	private Integer logNo;
	
	@Column(name = "atm_no")
	private String atmNo;
	
	@Column(name = "card_id")
	private String cardId;
	
	@Column(name = "operate_name")
	private String operateName;
	
	@Column(name = "operate_context")
	private String operateContext;
	
	@Column(name = "operate_time")
	private Date operateTime;

	public Integer getLogNo() {
		return logNo;
	}

	public void setLogNo(Integer logNo) {
		this.logNo = logNo;
	}

	public String getAtmNo() {
		return atmNo;
	}

	public void setAtmNo(String atmNo) {
		this.atmNo = atmNo;
	}

	public String getCardId() {
		return cardId;
	}

	public void setCardId(String cardId) {
		this.cardId = cardId;
	}

	public String getOperateName() {
		return operateName;
	}

	public void setOperateName(String operateName) {
		this.operateName = operateName;
	}

	public String getOperateContext() {
		return operateContext;
	}

	public void setOperateContext(String operateContext) {
		this.operateContext = operateContext;
	}

	public Date getOperateTime() {
		return operateTime;
	}

	public void setOperateTime(Date operateTime) {
		this.operateTime = operateTime;
	}
}

有两种方式实现统计：

方法一：

2、创建一个用来访问统计结果的实体CountClass

package com.cn.demo.count.entity;

import java.io.Serializable;


public class CountClass implements Serializable{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String atmNo;
	private String operateName;
	private long count;
	
	public CountClass(String atmNo, String operateName, long count) {
		this.atmNo = atmNo;
		this.operateName = operateName;
		this.count = count;
	}
	
	public String getAtmNo() {
		return atmNo;
	}

	public void setAtmNo(String atmNo) {
		this.atmNo = atmNo;
	}

	public String getOperateName() {
		return operateName;
	}

	public void setOperateName(String operateName) {
		this.operateName = operateName;
	}

	public long  getCount() {
		return count;
	}
	
	public void setCount(long count) {
		this.count = count;
	}
}

3、接口层CountAtmLogRepository.java

package com.cn.demo.count.repository;

import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.cn.demo.count.entity.AtmLog;
import com.cn.demo.count.entity.CountClass;


public interface CountAtmLogRepository extends JpaRepository<AtmLog, String>{

	@Query(value = "select new com.cn.demo.count.entity.CountClass(z.atmNo as atmNo, "
			+ " z.operateName as operateName, count(z) as count) "
			+ " from AtmLog z "
			+ " where z.atmNo like %?1% and z.operateName like %?2% and z.operateTime>=?3 and z.operateTime<=?4 "
			+ " group by z.operateName, z.atmNo")
	public Page<CountClass> countLog(String atmNo, String operateName, Date startDate, Date endDate, Pageable pageable);
}

4、服务层CountAtmLogService.java

package com.cn.demo.count.service;

import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.cn.demo.count.entity.CountClass;
import com.cn.demo.count.repository.CountAtmLogRepository;


@Service
public class CountAtmLogService {

	@Autowired
	private CountAtmLogRepository countAtmLogRepository;
	
	public Page<CountClass> countLog(String atmNo, String operateName, Date startDate, Date endDate, Pageable pageable) {
		Page<CountClass> list = countAtmLogRepository.countLog(atmNo, operateName, startDate, endDate, pageable); 
		return list;
	}
}

5、控制层CountAtmLogController.java

package com.cn.demo.count.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cn.demo.count.entity.CountClass;
import com.cn.demo.count.service.CountAtmLogService;


@RestController
@RequestMapping("/count")
public class CountAtmLogController {
	@Autowired
	private CountAtmLogService service;
	
	@RequestMapping(value = "/countLog", method = RequestMethod.GET)
	public Page<CountClass> countLog(@RequestParam(value = "atmNo", required = false, defaultValue = "") String atmNo,
			@RequestParam(value = "operateName", required = false, defaultValue = "") String operateName,
			@RequestParam(value = "operateTime1", required = false, defaultValue = "") String operateTime1,
			@RequestParam(value = "operateTime2", required = false, defaultValue = "") String operateTime2,
			Pageable pageable){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(operateTime1.equals("")) {
			operateTime1 = "1999-01-01 00:00:00";
		}
		if(operateTime2.equals("")) {
			operateTime2 = "2999-01-01 00:00:00";
		}
		Date startDate = null;
		Date endDate = null;
		Page<CountClass> list = null;
		try {
			startDate = sdf.parse(operateTime1);
			endDate = sdf.parse(operateTime2);
			list = service.countLog(atmNo, operateName, startDate, endDate, pageable);
		}catch (ParseException e) {
			e.printStackTrace();
		}
		return list;
	}
	
}

运行程序，在浏览器地址栏输入http://localhost:9000/test/count/countLog?page=0&size=20

content中是查询到的内容

可以根据需要添加查询条件，例如：http://localhost:9000/test/count/countLog?atmNo=ATM001&operateName=存款&operateTime1=2019-10-15 15:29:00&operateTime2=2019-10-16 00:00:00&page=0&size=20。

方法二：

2、接口层AtmLogRepository.java

package com.cn.demo.count.repository;

import java.util.Date;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.cn.demo.count.entity.AtmLog;

public interface AtmLogRepository extends JpaRepository<AtmLog, Integer>{

	@Query(nativeQuery = true, value = "select z.atm_no as atmNo, z.operate_name as operateName,count(*) as count from atm_log z where z.atm_no like %?1% and z.operate_name like %?2% and z.operate_time>=?3 and z.operate_time <=?4 group by z.atm_no,z.operate_name")
	Page<String[]> countLog(String atmNo, String operateName, Date operateTime1, Date operateTime2, Pageable pageable);

}

3、服务层AtmLogService.java

package com.cn.demo.count.service;

import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.cn.demo.count.repository.AtmLogRepository;

@Service
public class AtmLogService {
	@Autowired
	private AtmLogRepository atmLogRepository;

	public Page<String[]> countLog(String atmNo, String operateName, Date startDate, Date endDate, Pageable pageable) {
		return atmLogRepository.countLog(atmNo, operateName, startDate, endDate, pageable);
	}
}

4、控制层AtmLogController.java

package com.cn.demo.count.controller;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.cn.demo.count.service.AtmLogService;

@RestController
@RequestMapping("/api")
public class AtmLogController {
	@Autowired
	private AtmLogService atmLogService;
	
	@RequestMapping(value = "/countLog", method = RequestMethod.GET)
	public Page<String[]> countLog(@RequestParam(value = "atmNo", required = false, defaultValue = "") String atmNo,
			@RequestParam(value = "operateName", required = false, defaultValue = "") String operateName,
			@RequestParam(value = "operateTime1", required = false, defaultValue = "") String operateTime1,
			@RequestParam(value = "operateTime2", required = false, defaultValue = "") String operateTime2,
			Pageable pageable){
		if(operateTime1.equals("")) {
			operateTime1 = "1999-01-01 00:00:00";
		}
		if(operateTime2.equals("")) {
			operateTime2 = "2999-01-01 00:00:00";
		}
		Date startDate = null;
		Date endDate = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			startDate = sdf.parse(operateTime1);
			endDate = sdf.parse(operateTime2);
		} catch (ParseException e) {
			e.printStackTrace();
		}
			
		Page<String[]> arr = atmLogService.countLog(atmNo, operateName, startDate, endDate, pageable);
		return arr;
	}
	
}

运行程序，在浏览器地址栏输入http://localhost:9000/test/api/countLog?page=0&size=20

content中是查询到的内容

可以根据需要添加查询条件，例如：http://localhost:9000/test/api/countLog?atmNo=ATM001&operateName=存款&operateTime1=2019-10-15 15:29:00&operateTime2=2019-10-16 00:00:00&page=0&size=20。

跟着BOSS有肉吃

关注

3
点赞
踩
25

收藏

觉得还不错? 一键收藏
6
评论
SpringBoot对数据库表中的数据进行统计分析

数据库表asm_log存放的是某金融机构所有自助机的操作日志：CREATE TABLE `atm_log` ( `log_no` int(11) NOT NULL AUTO_INCREMENT COMMENT '日志编号', `atm_no` varchar(32) NOT NULL COMMENT '自助机编号', `card_id` varchar(32) NOT NULL ...
复制链接

扫一扫