数据引入提示插入重复键值是什么意思

概述

本篇是 POI系列 的最后一篇。传送门 java 开发中如何用 POI 优雅的导出 Excel 文件, Java 开发中如何用 POI 优雅的导入 Excel 文件.

场景分析

大多数开发中是不需要重复的数据的, 所以后端开发中需要做去重操作, 而且为了更加友好的交互, 我们需要将导入失败的数据返回给用户。一般数据重复有以下几个场景:

1. Excel 中本身存在重复数据, 即本次导入存在重复数据;
2. 数据库中已经存在了该条数据, 即历史导入存在重复数据;

为了减轻数据库的压力, 这里在设计中引入缓存 Redis 。

整体思路如下:

1. bitmap 判断是否存在;
2. 内存中数据是否重复;
3. redis 和 mysql 批量插入;
4. 数据库中插入失败处理;

代码实现

为简化无聊的 CRUD 编写, 引入了 mybatis-plus 的逆向 generator 插件。

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="; xmlns:xsi=";
xsi:schemaLocation=" ;>
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.;/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<;
</properties>

<dependencies>
<dependency>
<groupId>org.;/groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.;/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.;/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>
<!-- -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!-- -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.1</version>
</dependency>

<dependency>
<groupId>org.;/groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!-- -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<!-- -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.;/groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
<!-- -->
<dependency>
<groupId>org.a;/groupId>
<artifactId>commons-pool2</artifactId>
<version>2.8.0</version>
</dependency>
<!-- -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.68</version>
</dependency>
<!-- -->
<dependency>
<groupId>org.a;/groupId>
<artifactId>commons-lang3</artifactId>
<version>3.10</version>
</dependency>

</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.;/groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

a

server:
port: 8888
spring:
datasource:
type: com.alibaba.druid.
driverClassName: com.my
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 = Sy("u;);
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);
gc.setSwagger2(true); // 实体属性 Swagger2 注解
gc.setIdType);
gc.setMapperName("%sMapper");
gc.setXmlName("%sMapper");
gc.setserviceName("%sService");
gc.setServiceImplName("%sServiceImpl");
gc.setControllerName("%sController");
m(gc);

// 数据源配置
DataSourceConfig dsc = new DataSourceConfig();
d("jdbc:mysql://localhost:3306/mp_base?useUnicode=true&serverTimezone=GMT&useSSL=false&characterEncoding=utf8");
// d("public");
d("com.my");
d("root");
d("root");
m(dsc);

// 包配置
PackageConfig pc = new PackageConfig();
("mp");
("cn.idea360.demo.modules");
m(pc);

// 自定义配置
InjectionConfig cfg = new InjectionConfig() {
@Override
public void initMap() {
// to do nothing
}
};
List<FileOutConfig> focList = new ArrayList<>();
(new FileOutConfig("/template;) {
@Override
public String outputFile(TableInfo tableInfo) {
// 自定义输入文件名称
return projectPath + "/idc-mp/src/main/resources/mapper/" + ()
+ "/" + () + "Mapper" + S;
}
});
c(focList);
m(cfg);
m(new TemplateConfig().setXml(null));

// 策略配置
StrategyConfig strategy = new StrategyConfig();
);
);
// ("com.baomidou.myba;);
(true);
// ("com.baomidou.myba;);
(new String[]{"user"});
(true);
("id");
(true);
// (() + "_");
m(strategy);
// 选择 freemarker 引擎需要指定如下加，注意 pom 依赖必须有！
mEngine(new FreemarkerTemplateEngine());
m();
}

}

Redis

@Configuration
public class RedisConfig {

@Bean
public RedisTemplate<String, Serializable> redisTemplate(LettuceConnectionFactory connectionFactory) {
RedisTemplate<String, Serializable> redisTemplate = new RedisTemplate<>();
redi(new StringRedisSerializer());
redi(new GenericJackson2JsonRedisSerializer());
redi(connectionFactory);
return redisTemplate;
}

// @Bean
// public HashOperations<String, String, Object> hashOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redi();
// }
//
// @Bean
// public ValueOperations<String, Serializable> valueOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redi();
// }
//
// @Bean
// public ListOperations<String, Serializable> listOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redi();
// }
//
// @Bean
// public SetOperations<String, Serializable> setOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redi();
// }
//
// @Bean
// public ZSetOperations<String, Serializable> zSetOperations(RedisTemplate<String, Serializable> redisTemplate) {
// return redi();
// }
}

U

@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 = IdTy)
private Long id;

@ApiModelProperty(value = "姓名")
private String name;

@ApiModelProperty(value = "年龄")
private Integer age;

@ApiModelProperty(value = "邮箱")
private String email;

public User(String 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 Objec(name, u);
}

@Override
public int hashCode() {
return Objec(name);
}
}

Ha

public class HashUtils {

public static int hash(String data) {
return da() & In;
}
}

核心逻辑

/**
* <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 (list)) {
throw new NullPointerException("数据为空");
}
CopyOnWriteArrayList<User> importFailList = new CopyOnWriteArrayList<>();
CopyOnWriteArrayList<User> importSuccessList = new CopyOnWriteArrayList<>();

li().forEach(user -> {
Boolean exist = redi().getBit("user", Ha()));
if (exist) {
log.error("Redis中name={}的用户已存在", u());
// 数据已存在，数据放入失败集合
im(user);
return;
}
if (user)) {
log.error("内存中name={}的用户已存在", u());
im(user);
return;
}
im(user);
});

if (!Collec(importSuccessList)) {
try {
// 批量插入数据库
(importSuccessList);
} catch (Exception e) {
log.error("MySQL写入冲突:{}", e.getMessage());
iterator<User> iterator = im();
while ()) {
User user = i();
if () == null) {
log.error("MySQL中name={}的用户已存在", u());
im(user);
im(user);
}
}
}
// 将导入成功的数据批量写入bitmap
redi(new RedisCallback<String>() {
@Override
public String doInRedis(RedisConnection redisConnection) throws DataAccessException {
importSuccessLi().forEach(user -> {
redi("user".getBytes(), Ha()), true);
});
return null;
}
});
}

JSONObject result = new JSONObject();
re("success", importSuccessList);
re("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. : 内存中name=张三的用户已存在
* 2020-05-19 15:18:10.475 WARN 6612 --- [ main] c.i.d.m.mp. : SqlSession [org.a] was not registered for synchronization because DataSource is not transactional
* 2020-05-19 15:18:10.533 INFO 6612 --- [ main] com.alibaba.druid. : {dataSource-1} inited
* 2020-05-19 15:18:10.794 INFO 6612 --- [ main] c.i.d.m.m.s.im : {"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 = u(userList);
log.info());
}

/**
* 模拟Redis中存在重复数据
*
* 2020-05-19 15:18:40.700 ERROR 13352 --- [ main] c.i.d.m.mp. : Redis中name=张三的用户已存在
* 2020-05-19 15:18:40.708 WARN 13352 --- [ main] c.i.d.m.mp. : SqlSession [org.a] was not registered for synchronization because DataSource is not transactional
* 2020-05-19 15:18:40.768 INFO 13352 --- [ main] com.alibaba.druid. : {dataSource-1} inited
* 2020-05-19 15:18:41.043 INFO 13352 --- [ main] c.i.d.m.m.s.im : {"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 = u(userList);
log.info());
}

/**
* 手动在MySQL中添加1条数据, 模拟MySQL中存在重复数据
*
* 2020-05-19 15:19:22.337 ERROR 14128 --- [ main] c.i.d.m.mp. : Redis中name=张三的用户已存在
* 2020-05-19 15:19:22.339 ERROR 14128 --- [ main] c.i.d.m.mp. : Redis中name=李四的用户已存在
* 2020-05-19 15:19:22.347 WARN 14128 --- [ main] c.i.d.m.mp. : SqlSession [org.a] was not registered for synchronization because DataSource is not transactional
* 2020-05-19 15:19:22.405 INFO 14128 --- [ main] com.alibaba.druid. : {dataSource-1} inited
* 2020-05-19 15:19:22.609 ERROR 14128 --- [ main] c.i.d.m.mp. : MySQL写入冲突:cn.idea360.demo.modules.mp.mapper.UserMapper.insert (batch index #1) failed. Cause: java.: Duplicate entry '王五' for key 'name'
* ; Duplicate entry '王五' for key 'name'; nested exception is java.: Duplicate entry '王五' for key 'name'
* 2020-05-19 15:19:22.609 ERROR 14128 --- [ main] c.i.d.m.mp. : MySQL中name=王五的用户已存在
* 2020-05-19 15:19:22.697 INFO 14128 --- [ main] c.i.d.m.m.s.im : {"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 = u(userList);
log.info());
}
}

最后

本文到此结束，感谢阅读。如果您觉得不错，转发+关注一波呗！