概述
本篇是 POI系列 的最后一篇。传送门 Java 开发中如何用 POI 优雅的导出 Excel 文件, Java 开发中如何用 POI 优雅的导入 Excel 文件.
场景分析
大多数开发中是不需要重复的数据的, 所以后端开发中需要做去重操作, 而且为了更加友好的交互, 我们需要将导入失败的数据返回给用户。一般数据重复有以下几个场景:
- Excel 中本身存在重复数据, 即本次导入存在重复数据;
- 数据库中已经存在了该条数据, 即历史导入存在重复数据;
为了减轻数据库的压力, 这里在设计中引入缓存 Redis 。
整体思路如下:
- bitmap 判断是否存在;
- 内存中数据是否重复;
- redis 和 mysql 批量插入;
- 数据库中插入失败处理;
代码实现
为简化无聊的 CRUD 编写, 引入了 mybatis-plus 的逆向 generator 插件。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>4.0.0org.springframework.boot spring-boot-starter-parent 2.3.0.RELEASEcn.idea360 idc-mp 0.0.1idc-mpDemo project for Spring Boot1.8org.springframework.boot spring-boot-starter-web org.springframework.boot spring-boot-devtools runtimetrueorg.projectlombok lombok trueorg.springframework.boot spring-boot-starter-test testorg.junit.vintage junit-vintage-engine com.baomidou mybatis-plus-boot-starter 3.3.1mysql mysql-connector-java runtimecom.alibaba druid 1.1.22com.baomidou mybatis-plus-generator 3.3.1org.springframework.boot spring-boot-starter-freemarker io.springfox springfox-swagger2 2.9.2io.springfox springfox-swagger-ui 2.9.2org.springframework.boot spring-boot-starter-data-redis org.apache.commons commons-pool2 2.8.0com.alibaba fastjson 1.2.68org.apache.commons commons-lang3 3.10org.springframework.boot spring-boot-maven-pluginapplication.yml
server: port: 8888spring: datasource: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/mp_base?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true username: root password: root redis: database: 0 host: localhost port: 6379 password: # 密码(默认为空) timeout: 6000ms # 连接超时时长(毫秒) lettuce: pool: max-active: 1000 # 连接池最大连接数(使用负值表示没有限制) max-wait: -1ms # 连接池最大阻塞等待时间(使用负值表示没有限制) max-idle: 10 # 连接池中的最大空闲连接 min-idle: 5 # 连接池中的最小空闲连接mysql-schema
DROP TABLE IF EXISTS user;CREATE TABLE user(id BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',name VARCHAR(30) DEFAULT NULL UNIQUE COMMENT '姓名',age INT(11) DEFAULT NULL COMMENT '年龄',email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;generator
public class MysqlGenerator { /** * RUN THIS */ public static void main(String[] args) { // 代码生成器 AutoGenerator mpg = new AutoGenerator(); // 全局配置 GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir"); gc.setOutputDir(projectPath + "/idc-mp/src/main/java"); gc.setAuthor("当我遇上你"); gc.setOpen(false); gc.setFileOverride(true);// 是否覆盖文件 gc.setBaseResultMap(true);// XML ResultMap gc.setBaseColumnList(true);// XML columList gc.setDateType(DateType.ONLY_DATE); gc.setSwagger2(true); // 实体属性 Swagger2 注解 gc.setIdType(IdType.AUTO); gc.setMapperName("%sMapper"); gc.setXmlName("%sMapper"); gc.setServiceName("%sService"); gc.setServiceImplName("%sServiceImpl"); gc.setControllerName("%sController"); mpg.setGlobalConfig(gc); // 数据源配置 DataSourceConfig dsc = new DataSourceConfig(); dsc.setUrl("jdbc:mysql://localhost:3306/mp_base?useUnicode=true&serverTimezone=GMT&useSSL=false&characterEncoding=utf8"); // dsc.setSchemaName("public"); dsc.setDriverName("com.mysql.cj.jdbc.Driver"); dsc.setUsername("root"); dsc.setPassword("root"); mpg.setDataSource(dsc); // 包配置 PackageConfig pc = new PackageConfig(); pc.setModuleName("mp"); pc.setParent("cn.idea360.demo.modules"); mpg.setPackageInfo(pc); // 自定义配置 InjectionConfig cfg = new InjectionConfig() { @Override public void initMap() { // to do nothing } }; List focList = new ArrayList<>(); focList.add(new FileOutConfig("/templates/mapper.xml.ftl") { @Override public String outputFile(TableInfo tableInfo) { // 自定义输入文件名称 return projectPath + "/idc-mp/src/main/resources/mapper/" + pc.getModuleName() + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML; } }); cfg.setFileOutConfigList(focList); mpg.setCfg(cfg); mpg.setTemplate(new TemplateConfig().setXml(null)); // 策略配置 StrategyConfig strategy = new StrategyConfig(); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel);// strategy.setSuperEntityClass("com.baomidou.mybatisplus.samples.generator.common.BaseEntity"); strategy.setEntityLombokModel(true);// strategy.setSuperControllerClass("com.baomidou.mybatisplus.samples.generator.common.BaseController"); strategy.setInclude(new String[]{"user"}); strategy.setRestControllerStyle(true); strategy.setSuperEntityColumns("id"); strategy.setControllerMappingHyphenStyle(true);// strategy.setTablePrefix(pc.getModuleName() + "_"); mpg.setStrategy(strategy); // 选择 freemarker 引擎需要指定如下加，注意 pom 依赖必须有！ mpg.setTemplateEngine(new FreemarkerTemplateEngine()); mpg.execute(); }}Redis
@Configurationpublic class RedisConfig { @Bean public RedisTemplate redisTemplate(LettuceConnectionFactory connectionFactory) { RedisTemplate redisTemplate = new RedisTemplate<>(); redisTemplate.setKeySerializer(new StringRedisSerializer()); redisTemplate.setValueSerializer(new GenericJackson2JsonRedisSerializer()); redisTemplate.setConnectionFactory(connectionFactory); return redisTemplate; }// @Bean// public HashOperations hashOperations(RedisTemplate redisTemplate) {// return redisTemplate.opsForHash();// } @Bean// public ValueOperations valueOperations(RedisTemplate redisTemplate) {// return redisTemplate.opsForValue();// } @Bean// public ListOperations listOperations(RedisTemplate redisTemplate) {// return redisTemplate.opsForList();// } @Bean// public SetOperations setOperations(RedisTemplate redisTemplate) {// return redisTemplate.opsForSet();// } @Bean// public ZSetOperations zSetOperations(RedisTemplate redisTemplate) {// return redisTemplate.opsForZSet();// }}User.java
@Data@EqualsAndHashCode(callSuper = false)@Accessors(chain = true)@ApiModel(value="User对象", description="")public class User implements Serializable { private static final long serialVersionUID = 1L; @TableId(value = "id", type = IdType.AUTO) private Long id; @ApiModelProperty(value = "姓名") private String name; @ApiModelProperty(value = "年龄") private Integer age; @ApiModelProperty(value = "邮箱") private String email; public User(String name) { this.name = name; } /** * 因为会在List中判断user是否存在, 所以需要重写equals和hashCode方法 * @param o * @return */ @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; User user = (User) o; return Objects.equals(name, user.name); } @Override public int hashCode() { return Objects.hash(name); }}HashUtils.java
public class HashUtils { public static int hash(String data) { return data.hashCode() & Integer.MAX_VALUE; }}核心逻辑
/** * * 服务实现类 *
* * @author 当我遇上你 * @since 2020-05-19 */@Slf4j@Servicepublic class UserServiceImpl extends ServiceImpl implements UserService { @Autowired private RedisTemplate redisTemplate; /** * 1. bitmap判断是否存在 * 2. 内存中数据是否重复 * 3. redis和mysql批量插入 * 4. 数据库中是否插入失败 * @param list * @return */ @Override public JSONObject importBatch(List list) { if (CollectionUtils.isEmpty(list)) { throw new NullPointerException("数据为空"); } CopyOnWriteArrayList importFailList = new CopyOnWriteArrayList<>(); CopyOnWriteArrayList importSuccessList = new CopyOnWriteArrayList<>(); list.stream().forEach(user -> { Boolean exist = redisTemplate.opsForValue().getBit("user", HashUtils.hash(user.getName())); if (exist) { log.error("Redis中name={}的用户已存在", user.getName()); // 数据已存在，数据放入失败集合 importFailList.add(user); return; } if (importSuccessList.contains(user)) { log.error("内存中name={}的用户已存在", user.getName()); importFailList.add(user); return; } importSuccessList.add(user); }); if (!CollectionUtils.isEmpty(importSuccessList)) { try { // 批量插入数据库 this.saveBatch(importSuccessList); } catch (Exception e) { log.error("MySQL写入冲突:{}", e.getMessage()); Iterator iterator = importSuccessList.iterator(); while (iterator.hasNext()) { User user = iterator.next(); if (user.getId() == null) { log.error("MySQL中name={}的用户已存在", user.getName()); importFailList.add(user); importSuccessList.remove(user); } } } // 将导入成功的数据批量写入bitmap redisTemplate.executePipelined(new RedisCallback() { @Override public String doInRedis(RedisConnection redisConnection) throws DataAccessException { importSuccessList.stream().forEach(user -> { redisConnection.setBit("user".getBytes(), HashUtils.hash(user.getName()), true); }); return null; } }); } JSONObject result = new JSONObject(); result.put("success", importSuccessList); result.put("failure", importFailList); return result; }}场景测试
@Slf4j@SpringBootTestclass UserServiceImplTest { @Autowired UserService userService; /** * 模拟内存中存在重复数据 * * 2020-05-19 15:18:10.468 ERROR 6612 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : 内存中name=张三的用户已存在 * 2020-05-19 15:18:10.475 WARN 6612 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@304e1e4e] was not registered for synchronization because DataSource is not transactional * 2020-05-19 15:18:10.533 INFO 6612 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited * 2020-05-19 15:18:10.794 INFO 6612 --- [ main] c.i.d.m.m.s.impl.UserServiceImplTest : {"success":[{"id":1,"name":"张三"}],"failure":[{"name":"张三"}]} */ @Test void importBatch1() { User user1 = new User("张三"); User user2 = new User("张三"); List userList = Arrays.asList(user1, user2); JSONObject result = userService.importBatch(userList); log.info(result.toJSONString()); } /** * 模拟Redis中存在重复数据 * * 2020-05-19 15:18:40.700 ERROR 13352 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : Redis中name=张三的用户已存在 * 2020-05-19 15:18:40.708 WARN 13352 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@60251ddb] was not registered for synchronization because DataSource is not transactional * 2020-05-19 15:18:40.768 INFO 13352 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited * 2020-05-19 15:18:41.043 INFO 13352 --- [ main] c.i.d.m.m.s.impl.UserServiceImplTest : {"success":[{"id":2,"name":"李四"}],"failure":[{"name":"张三"}]} */ @Test void importBatch2() { User user1 = new User("张三"); User user2 = new User("李四"); List userList = Arrays.asList(user1, user2); JSONObject result = userService.importBatch(userList); log.info(result.toJSONString()); } /** * 手动在MySQL中添加1条数据, 模拟MySQL中存在重复数据 * * 2020-05-19 15:19:22.337 ERROR 14128 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : Redis中name=张三的用户已存在 * 2020-05-19 15:19:22.339 ERROR 14128 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : Redis中name=李四的用户已存在 * 2020-05-19 15:19:22.347 WARN 14128 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@69fe8c75] was not registered for synchronization because DataSource is not transactional * 2020-05-19 15:19:22.405 INFO 14128 --- [ main] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited * 2020-05-19 15:19:22.609 ERROR 14128 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : MySQL写入冲突:cn.idea360.demo.modules.mp.mapper.UserMapper.insert (batch index #1) failed. Cause: java.sql.BatchUpdateException: Duplicate entry '王五' for key 'name' * ; Duplicate entry '王五' for key 'name'; nested exception is java.sql.BatchUpdateException: Duplicate entry '王五' for key 'name' * 2020-05-19 15:19:22.609 ERROR 14128 --- [ main] c.i.d.m.mp.service.impl.UserServiceImpl : MySQL中name=王五的用户已存在 * 2020-05-19 15:19:22.697 INFO 14128 --- [ main] c.i.d.m.m.s.impl.UserServiceImplTest : {"success":[],"failure":[{"name":"张三"},{"name":"李四"},{"name":"王五"}]} */ @Test void importBatch3() { User user1 = new User("张三"); User user2 = new User("李四"); User user3 = new User("王五"); List userList = Arrays.asList(user1, user2, user3); JSONObject result = userService.importBatch(userList); log.info(result.toJSONString()); }}最后
本文到此结束，感谢阅读。如果您觉得不错，转发+关注一波呗！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
