Java批量导入时如何去除重复数据并返回结果
本篇是 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"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.0.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.idea360</groupId>
<artifactId>idc-mp</artifactId>
<version>0.0.1</version>
<name>idc-mp</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-generator -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger2 -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/io.springfox/springfox-swagger-ui -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.8.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.68</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
复制代码
application.yml
server:
port: 8888
spring:
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<FileOutConfig> 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
@Configuration
public class RedisConfig {
@Bean
public RedisTemplate<String, Serializable> redisTemplate(LettuceConnectionFactory connectionFactory) {
RedisTemplate<String, Serializable> redisTemplate = new RedisTemplate<>();
redisTemplate.setKeySerializer(new StringRedisSerializer());
redisTemplate.setValueSerializer(new GenericJackson2JsonRedisSerializer());
redisTemplate.setConnectionFactory(connectionFactory);
return redisTemplate;
}
// @Bean
// public HashOperations<String, String, Object> hashOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redisTemplate.opsForHash();
// }
//
// @Bean
// public ValueOperations<String, Serializable> valueOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redisTemplate.opsForValue();
// }
//
// @Bean
// public ListOperations<String, Serializable> listOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redisTemplate.opsForList();
// }
//
// @Bean
// public SetOperations<String, Serializable> setOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redisTemplate.opsForSet();
// }
//
// @Bean
// public ZSetOperations<String, Serializable> zSetOperations(RedisTemplate<String, Serializable> 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;
}
}
复制代码
核心逻辑
/**
* <p>
* 服务实现类
* </p>
*
* @author 当我遇上你
* @since 2020-05-19
*/
@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
private RedisTemplate redisTemplate;
/**
* 1. bitmap判断是否存在
* 2. 内存中数据是否重复
* 3. redis和mysql批量插入
* 4. 数据库中是否插入失败
* @param list
* @return
*/
@Override
public JSONObject importBatch(List<User> list) {
if (CollectionUtils.isEmpty(list)) {
throw new NullPointerException("数据为空");
}
CopyOnWriteArrayList<User> importFailList = new CopyOnWriteArrayList<>();
CopyOnWriteArrayList<User> 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<User> 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<String>() {
@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
@SpringBootTest
class 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<User> 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<User> 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<User> userList = Arrays.asList(user1, user2, user3);
JSONObject result = userService.importBatch(userList);
log.info(result.toJSONString());
}
}
复制代码
最后
本文到此结束，感谢阅读。如果您觉得不错，请关注公众号【当我遇上你】支持一下。
正文到此结束
- 本文标签： final entity Action iBATIS JDBC UTC mysql 测试 js map 代码 value src Property Collection 连接池 GMT ssl https sql API key 开发 lib Freemarker IO equals tag root ip cat NSA Excel dependencies dist 代码生成器 plugin ArrayList druid web mail 压力 IDE XML mybatis build connectionFactory sqlsession session stream apache pom 数据库 redis json BaseResultMap schema message mapper springboot junit update maven description 数据 App REST Connection Word bean Spring Boot zab java db tar UI CTO 插件 dataSource http Service id 缓存 tk spring tab 配置 时间 list
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
