package com.alatus.config.listener;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.model.TUser;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import java.util.Date;
import java.util.List;
/**
* 每读一行Excel的数据,就会触发该监听器中的invoke()方法,Excel读完之后会触发该监听器中的doAfterAllAnalysed()方法
*/
@Slf4j
public class UploadDataListener implements ReadListener<TClue> {
/**
* 每隔100条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
//缓存List
private List<TClue> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private TClueMapper tClueMapper;
private String token;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param tClueMapper
*/
public UploadDataListener(TClueMapper tClueMapper, String token) {
this.tClueMapper = tClueMapper;
this.token = token;
}
/**
* 这个每一条数据解析都会来调用
*
* @param tClue one row value. It is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(TClue tClue, AnalysisContext context) {
log.info("读取到的每一条数据:{}", JSONUtils.toJSON(tClue));
//给读到的clue对象设置创建时间(导入时间)和创建人(导入人)
tClue.setCreateTime(new Date());
TUser tUser = JWTUtils.parseUserFromJWT(token);
tClue.setCreateBy(tUser.getId());
//每读取一行,就把该数据放入到一个缓存List中
cachedDataList.add(tClue);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
//把缓存list中的数据写入到数据库
saveData();
//存储完成清空list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
tClueMapper.saveClue(cachedDataList);
log.info("存储数据库成功!");
}
}
package com.alatus.config.listener;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.model.TUser;
import com.alatus.util.JSONUtils;
import com.alatus.util.JWTUtils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import java.util.Date;
import java.util.List;
/**
* 每读一行Excel的数据,就会触发该监听器中的invoke()方法,Excel读完之后会触发该监听器中的doAfterAllAnalysed()方法
*/
@Slf4j
public class UploadDataListener implements ReadListener<TClue> {
/**
* 每隔100条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
//缓存List
private List<TClue> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private TClueMapper tClueMapper;
private String token;
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param tClueMapper
*/
public UploadDataListener(TClueMapper tClueMapper, String token) {
this.tClueMapper = tClueMapper;
this.token = token;
}
/**
* 这个每一条数据解析都会来调用
*
* @param tClue one row value. It is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(TClue tClue, AnalysisContext context) {
log.info("读取到的每一条数据:{}", JSONUtils.toJSON(tClue));
//给读到的clue对象设置创建时间(导入时间)和创建人(导入人)
tClue.setCreateTime(new Date());
TUser tUser = JWTUtils.parseUserFromJWT(token);
tClue.setCreateBy(tUser.getId());
//每读取一行,就把该数据放入到一个缓存List中
cachedDataList.add(tClue);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
//把缓存list中的数据写入到数据库
saveData();
//存储完成清空list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
tClueMapper.saveClue(cachedDataList);
log.info("存储数据库成功!");
}
}
package com.alatus.service.impl;
import com.alatus.config.listener.UploadDataListener;
import com.alatus.constant.Constants;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.query.BaseQuery;
import com.alatus.service.ClueService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.List;
@Service
public class ClueServiceImpl implements ClueService {
@Resource
private TClueMapper tClueMapper;
@Override
public PageInfo<TClue> getClueByPage(Integer current) {
// 1.设置PageHelper
PageHelper.startPage(current, Constants.PAGE_SIZE);
// 2.查询
List<TClue> list = tClueMapper.selectClueByPage(BaseQuery.builder().build());
// 3.封装分页数据到PageInfo
PageInfo<TClue> info = new PageInfo<>(list);
return info;
}
@Override
public void importExcel(InputStream inputStream,String token) {
// 第一个参数是文件,第二个参数是excel模板类,第三个是监听器
EasyExcel.read(inputStream,TClue.class,new UploadDataListener(tClueMapper,token)).sheet().doRead();
}
}
package com.alatus.service.impl;
import com.alatus.config.listener.UploadDataListener;
import com.alatus.constant.Constants;
import com.alatus.mapper.TClueMapper;
import com.alatus.model.TClue;
import com.alatus.query.BaseQuery;
import com.alatus.service.ClueService;
import com.alibaba.excel.EasyExcel;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.List;
@Service
public class ClueServiceImpl implements ClueService {
@Resource
private TClueMapper tClueMapper;
@Override
public PageInfo<TClue> getClueByPage(Integer current) {
// 1.设置PageHelper
PageHelper.startPage(current, Constants.PAGE_SIZE);
// 2.查询
List<TClue> list = tClueMapper.selectClueByPage(BaseQuery.builder().build());
// 3.封装分页数据到PageInfo
PageInfo<TClue> info = new PageInfo<>(list);
return info;
}
@Override
public void importExcel(InputStream inputStream,String token) {
// 第一个参数是文件,第二个参数是excel模板类,第三个是监听器
EasyExcel.read(inputStream,TClue.class,new UploadDataListener(tClueMapper,token)).sheet().doRead();
}
}
package com.alatus.service;
import com.alatus.model.TClue;
import com.github.pagehelper.PageInfo;
import java.io.InputStream;
public interface ClueService {
PageInfo<TClue> getClueByPage(Integer current);
void importExcel(InputStream inputStream,String token);
}
package com.alatus.service;
import com.alatus.model.TClue;
import com.github.pagehelper.PageInfo;
import java.io.InputStream;
public interface ClueService {
PageInfo<TClue> getClueByPage(Integer current);
void importExcel(InputStream inputStream,String token);
}
package com.alatus.web;
import com.alatus.constant.Constants;
import com.alatus.model.TClue;
import com.alatus.result.Result;
import com.alatus.service.ClueService;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
@RestController
public class ClueController {
@Resource
private ClueService clueService;
@GetMapping(value = "/api/clues")
public Result cluePage(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
if(current == null){
current = 1;
}
PageInfo<TClue> pageInfo = clueService.getClueByPage(current);
return Result.OK(pageInfo);
}
@PostMapping(value = "/api/club/importExcel")
public Result importExcel(MultipartFile file, @RequestHeader(value = Constants.TOKEN_NAME)String token) throws IOException {
// 接收到的文件名字需要和前端提交的名字相同,否则无法接收
clueService.importExcel(file.getInputStream(),token);
return Result.OK();
}
}
package com.alatus.web;
import com.alatus.constant.Constants;
import com.alatus.model.TClue;
import com.alatus.result.Result;
import com.alatus.service.ClueService;
import com.github.pagehelper.PageInfo;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
@RestController
public class ClueController {
@Resource
private ClueService clueService;
@GetMapping(value = "/api/clues")
public Result cluePage(@RequestParam(value = Constants.CURRENT,required = false)Integer current){
if(current == null){
current = 1;
}
PageInfo<TClue> pageInfo = clueService.getClueByPage(current);
return Result.OK(pageInfo);
}
@PostMapping(value = "/api/club/importExcel")
public Result importExcel(MultipartFile file, @RequestHeader(value = Constants.TOKEN_NAME)String token) throws IOException {
// 接收到的文件名字需要和前端提交的名字相同,否则无法接收
clueService.importExcel(file.getInputStream(),token);
return Result.OK();
}
}
---
spring:
data:
redis:
lettuce:
pool:
max-idle: 8
min-idle: 0
max-wait: -1ms
max-active: 8
cluster:
refresh:
adaptive: true
period: 2000
cluster:
max-redirects: 3
nodes: 192.168.127.128:6381,192.168.127.128:6382,192.168.127.130:6383,192.168.127.130:6384,192.168.127.129:6385,192.168.127.129:6386
password: abc123
timeout: 5000
---
server:
port: 8089
servlet:
context-path: /
session:
persistent: false
---
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/crm_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: abc123
hikari:
maximum-pool-size: 30
minimum-idle: 30
connection-timeout: 5000
idle-timeout: 0
max-lifetime: 18000000
# 设置jackson返回json数据时的时区格式
jackson:
time-zone: GMT+8
date-format: yyyy-MM-dd HH:mm:ss
---
mybatis:
mapper-locations: classpath:mapper/*.xml
# configuration:
# 控制台打印SQL语句
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
---
#定义任务调度时间
project:
task:
cron: '* */60 * * * *'
delay: 3600000
---
spring:
data:
redis:
lettuce:
pool:
max-idle: 8
min-idle: 0
max-wait: -1ms
max-active: 8
cluster:
refresh:
adaptive: true
period: 2000
cluster:
max-redirects: 3
nodes: 192.168.127.128:6381,192.168.127.128:6382,192.168.127.130:6383,192.168.127.130:6384,192.168.127.129:6385,192.168.127.129:6386
password: abc123
timeout: 5000
---
server:
port: 8089
servlet:
context-path: /
session:
persistent: false
---
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/crm_system?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: abc123
hikari:
maximum-pool-size: 30
minimum-idle: 30
connection-timeout: 5000
idle-timeout: 0
max-lifetime: 18000000
# 设置jackson返回json数据时的时区格式
jackson:
time-zone: GMT+8
date-format: yyyy-MM-dd HH:mm:ss
---
mybatis:
mapper-locations: classpath:mapper/*.xml
# configuration:
# 控制台打印SQL语句
# log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
---
#定义任务调度时间
project:
task:
cron: '* */60 * * * *'
delay: 3600000
package com.alatus;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.util.HashMap;
import java.util.Map;
@SpringBootApplication
@MapperScan(basePackages = {"com.alatus.mapper"})
public class NetCrmServerApplication {
public static final Map<String,Object> cacheMap = new HashMap<>();
public static void main(String[] args) {
SpringApplication.run(NetCrmServerApplication.class, args);
}
}
package com.alatus;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import java.util.HashMap;
import java.util.Map;
@SpringBootApplication
@MapperScan(basePackages = {"com.alatus.mapper"})
public class NetCrmServerApplication {
public static final Map<String,Object> cacheMap = new HashMap<>();
public static void main(String[] args) {
SpringApplication.run(NetCrmServerApplication.class, args);
}
}