日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

公告：魔扣目錄網(wǎng)為廣大站長提供免費收錄網(wǎng)站服務(wù)，提交前請做好本站友鏈：【 網(wǎng)站目錄：http://www.ylptlb.cn 】， 免友鏈快審服務(wù)（50元/站），

點擊這里在線咨詢客服
新站提交
  • 網(wǎng)站：51998
  • 待審：31
  • 小程序：12
  • 文章：1030137
  • 會員：747

Java批量導(dǎo)入時，如何去除重復(fù)數(shù)據(jù)并返回結(jié)果？概述場景分析代碼實現(xiàn)pom.xmlApplication.ymlmysql-schemageneratorRedisUser.javaHashUtils

發(fā)布時間：2023-07-03 13:39:26 作者：網(wǎng)友整理

概述

本篇是 POI系列 的最后一篇。傳送門 JAVA 開發(fā)中如何用 POI 優(yōu)雅的導(dǎo)出 Excel 文件, Java 開發(fā)中如何用 POI 優(yōu)雅的導(dǎo)入 Excel 文件.

場景分析

大多數(shù)開發(fā)中是不需要重復(fù)的數(shù)據(jù)的, 所以后端開發(fā)中需要做去重操作, 而且為了更加友好的交互, 我們需要將導(dǎo)入失敗的數(shù)據(jù)返回給用戶。一般數(shù)據(jù)重復(fù)有以下幾個場景:

  1. Excel 中本身存在重復(fù)數(shù)據(jù), 即本次導(dǎo)入存在重復(fù)數(shù)據(jù);
  2. 數(shù)據(jù)庫中已經(jīng)存在了該條數(shù)據(jù), 即歷史導(dǎo)入存在重復(fù)數(shù)據(jù);

為了減輕數(shù)據(jù)庫的壓力, 這里在設(shè)計中引入緩存 redis 。

整體思路如下:

  1. bitmap 判斷是否存在;
  2. 內(nèi)存中數(shù)據(jù)是否重復(fù);
  3. redis 和 MySQL 批量插入;
  4. 數(shù)據(jù)庫中插入失敗處理;

代碼實現(xiàn)

為簡化無聊的 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:    # 密碼（默認(rèn)為空）
    timeout: 6000ms  # 連接超時時長（毫秒）
    lettuce:
      pool:
        max-active: 1000  # 連接池最大連接數(shù)（使用負(fù)值表示沒有限制）
        max-wait: -1ms      # 連接池最大阻塞等待時間（使用負(fù)值表示沒有限制）
        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("當(dāng)我遇上你");
        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);

        // 數(shù)據(jù)源配置
        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>
 *  服務(wù)實現(xiàn)類
 * </p>
 *
 * @author 當(dāng)我遇上你
 * @since 2020-05-19
 */
@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Autowired
    private RedisTemplate redisTemplate;

    /**
     * 1. bitmap判斷是否存在
     * 2. 內(nèi)存中數(shù)據(jù)是否重復(fù)
     * 3. redis和mysql批量插入
     * 4. 數(shù)據(jù)庫中是否插入失敗
     * @param list
     * @return
     */
    @Override
    public JSONObject importBatch(List<User> list) {
        if (CollectionUtils.isEmpty(list)) {
            throw new NullPointerException("數(shù)據(jù)為空");
        }
        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());
                // 數(shù)據(jù)已存在，數(shù)據(jù)放入失敗集合
                importFailList.add(user);
                return;
            }
            if (importSuccessList.contains(user)) {
                log.error("內(nèi)存中name={}的用戶已存在", user.getName());
                importFailList.add(user);
                return;
            }
            importSuccessList.add(user);
        });



        if (!CollectionUtils.isEmpty(importSuccessList)) {
            try {
                // 批量插入數(shù)據(jù)庫
                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);
                    }
                }
            }
            // 將導(dǎo)入成功的數(shù)據(jù)批量寫入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;

    /**
     * 模擬內(nèi)存中存在重復(fù)數(shù)據(jù)
     *
     * 2020-05-19 15:18:10.468 ERROR 6612 --- [           main] c.i.d.m.mp.service.impl.UserServiceImpl  : 內(nèi)存中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中存在重復(fù)數(shù)據(jù)
     *
     * 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條數(shù)據(jù), 模擬MySQL中存在重復(fù)數(shù)據(jù)
     *
     * 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());
    }
}

最后

本文到此結(jié)束，感謝閱讀。如果您覺得不錯，轉(zhuǎn)發(fā)+關(guān)注一波唄！

分享到：
標(biāo)簽：批量 導(dǎo)入 Java
用戶無頭像

網(wǎng)友整理

注冊時間：

網(wǎng)站：5 個   小程序：0 個  文章：12 篇

  • 51998

    網(wǎng)站

  • 12

    小程序

  • 1030137

    文章

  • 747

    會員

趕快注冊賬號，推廣您的網(wǎng)站吧！
最新入駐小程序

數(shù)獨大挑戰(zhàn)2018-06-03

數(shù)獨一種數(shù)學(xué)游戲，玩家需要根據(jù)9

答題星2018-06-03

您可以通過答題星輕松地創(chuàng)建試卷

全階人生考試2018-06-03

各種考試題，題庫，初中，高中，大學(xué)四六

運動步數(shù)有氧達(dá)人2018-06-03

記錄運動步數(shù)，積累氧氣值。還可偷

每日養(yǎng)生app2018-06-03

每日養(yǎng)生,天天健康

體育訓(xùn)練成績評定2018-06-03

通用課目體育訓(xùn)練成績評定