java csv导入千万数据

௸྄ིོུ倾心ღ᭄ᝰꫛꫀꪝ

已于 2022-10-17 16:19:05 修改

阅读量1.2k

点赞数

分类专栏： Java 文章标签： java mybatis 开发语言

于 2022-09-06 09:28:03 首次发布

本文链接：https://blog.csdn.net/qq_38152423/article/details/126717377

版权

Java 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

依赖

       <dependency>
            <groupId>de.siegmar</groupId>
            <artifactId>fastcsv</artifactId>
            <version>1.0.3</version>
        </dependency>

controller层

@Slf4j
@RestController
public class CsvImportController {


    @PostMapping("csv")
    public Result csvImport(
            @RequestParam MultipartFile file ){
        System.out.println("进来了====");

        File fileToFile = multipartFileToFile(file);

        CsvReader csvReader = new CsvReader();
        csvReader.setContainsHeader(true);
        CsvContainer csv = null;
        try {
            csv = csvReader.read(fileToFile, StandardCharsets.UTF_8);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List add = new ArrayList();
        for (CsvRow row : csv.getRows()) {
            //通过头获取对应属性
            String str=row.getField("userid");
            System.out.println("str  ==="+str);
            add.add(str);
        }


        return Result.ok(add);
    }

    public static File multipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            return null;
        }
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        try {
            File file = File.createTempFile(fileName, prefix);
            multiFile.transferTo(file);
            return file;
        } catch (Exception e) {
            log.error("MultipartFile转换为File转换异常：" + e.getMessage());
        }
        return null;
    }

}

 public static void main(String[] args) throws IOException {
        File file = new File("xxxx (7).csv");
        CsvReader csvReader = new CsvReader();
        csvReader.setContainsHeader(true);
        CsvContainer csv = csvReader.read(file, StandardCharsets.UTF_8);
        for (CsvRow row : csv.getRows()) {
            //通过头获取对应属性
            String str=row.getField("userid");
            System.out.println("str  ==="+str);
        }

    }

批量新增入库

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.3.4</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-core</artifactId>
            <version>3.4.3.4</version>
        </dependency>

实体映射类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("conf_user")
public class ConfUser {
    private static final long serialVersionUID = 1L;

    @TableId(value = "pid", type = IdType.AUTO)
    private String id;

    private String taskId;

    private String userTp;


    private String userId;


    private Integer delFlg;

    private String listType;
}

dao层

@Mapper
public interface ConfUserMapper extends BaseMapper<ConfUser> {
}

业务层

public interface ConfUserService extends IService<ConfUser> {
}

@Service
public class ConfUserServiceImpl extends ServiceImpl<ConfUserMapper, ConfUser> {
}

@Slf4j
@RestController
public class CsvImportController {


    @Resource
    private ConfUserService confUserService;


    @PostMapping("csv")
    public Result csvImport(
            @RequestParam MultipartFile file ){
        System.out.println("进来了====");

        File fileToFile = multipartFileToFile(file);

        CsvReader csvReader = new CsvReader();
        csvReader.setContainsHeader(true);
        CsvContainer csv = null;
        try {
            csv = csvReader.read(fileToFile, StandardCharsets.UTF_8);
        } catch (IOException e) {
            e.printStackTrace();
        }
        List<ConfUser> add = new ArrayList();
        for (CsvRow row : csv.getRows()) {
            //通过头获取对应属性
            String str=row.getField("userid");
            System.out.println("str  ==="+str);
            ConfUser conf = new ConfUser();

            conf.setDelFlg(0);
            conf.setListType("01");
            conf.setUserId(str);
            conf.setUserTp("01");
            conf.setTaskId("20220906");

            add.add(conf);
        }

        confUserService.saveBatch(add);

        return Result.ok(add);
    }

    public static File multipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            return null;
        }
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        try {
            File file = File.createTempFile(fileName, prefix);
            multiFile.transferTo(file);
            return file;
        } catch (Exception e) {
            log.error("MultipartFile转换为File转换异常：" + e.getMessage());
        }
        return null;
    }

}

大文件csv导入

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-slf4j18-impl</artifactId>
            <version>2.18.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.github.biyanwen</groupId>
            <artifactId>easycsv</artifactId>
            <version>1.0.0</version>
     <!--       <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>log4j</groupId>
                    <artifactId>log4j</artifactId>
                </exclusion>
            </exclusions>-->
        </dependency>

springboot 启动类

public class Application {
	public static ConfigurableApplicationContext ac;
    public static void main(String[] args) {
        ClassPool classPool = ClassPool.getDefault();
        //将当前ClassLoader添加到ClassPath
        classPool.appendClassPath(new LoaderClassPath(Thread.currentThread().getContextClassLoader()));
        ac = SpringApplication.run(SopStoryApplication.class, args);
    }
}

读取工具类业务逻辑删除了根据自己需要去增加修改

public class CsvParser extends AbstractCsvFileParser<ConfUserOutputBean> {
    /**
     * 每隔3000条存储数据库，然后清理list ，方便内存回收
     */
    public static final int BATCH_COUNT =10000;
    public int count= 0;

    private String uuid =null;

    private ConfUserService confUserService = ac.getBean(ConfUserService.class);

   


    /**
     * 缓存的数据
     */
    private List<ConfUser> cachedData = new ArrayList<>(BATCH_COUNT);
    private ConfUser confUser;

    public CsvParser(ConfUser confUser, String id, File file) {
        System.out.println("count ==="+count);
        

    }
    /**
     * 所有数据解析完成了 会来调用,防止有数据没有被保存
     *
     */
    @Override
    protected void doAfterAllAnalysed() {
 			
    }
    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    protected void invoke(ConfUserOutputBean confUserOutputBean) {
     
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        confUserService.batchInsert(cachedData,this.confUser,this.uuid);
    }

    public static File multipartFileToFile(MultipartFile multiFile) {
        // 获取文件名
        String fileName = multiFile.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            return null;
        }
        // 获取文件后缀
        String prefix = fileName.substring(fileName.lastIndexOf("."));
        System.out.println("prfix==="+prefix);
        if (prefix.equals(".csv")){
            try {
                File file = File.createTempFile(fileName, prefix);
                multiFile.transferTo(file);
                return file;
            } catch (Exception e) {
                System.out.println("MultipartFile转换为File转换异常：" + e.getMessage());
            }
        }
        return null;
    }
}

读取大文件csv行数

    public int countRows(File file){
        int lines =0;
        long times = System.currentTimeMillis();
        if (file.exists()){
            try {
                long length = file.length();
                LineNumberReader lineNumberReader = new LineNumberReader(new FileReader(file));
                lineNumberReader.skip(length);
                lines=lineNumberReader.getLineNumber();
                lineNumberReader.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }

        }
        long end = System.currentTimeMillis();
        System.out.println("lines 总行数为:"+lines);
        return lines;
    }

controller 层

   @PostMapping("csv")
    public Result csvImport(ConfUserVo confUser){
        System.out.println("进来了===="+confUser);

        System.out.println("file ==="+confUser.getFile());
        File file = multipartFileToFile(confUser.getFile());
        if (file==null){
            return Result.error("请上传正确的csv文件");
        }
        System.out.println("file path "+file.getPath());
        String  uuid = IdUtil.simpleUUID();
        int rows = countRows(file)-1;
        // 异步调用
        confUserService.batchAdd(confUser,file,uuid,false);
        JSONObject json =new JSONObject();
        json.putOpt("uuid",uuid);
        json.putOpt("rows",rows);
        return Result.ok(json).setMsg("开始导入");
    }

service层

    @Async
    @Override
    public void batchAdd(ConfUserVo confUser,File file,String uuid, boolean isClose) {
        ConfUser user = new ConfUser();
        user.setUserTp(confUser.getUserTp());
        user.setDelFlg(0);
        user.setListType(confUser.getListType());
        user.setTaskId(confUser.getTaskId());
        //调用工具类读取文件
        EasyCsv.read(file.getPath(), ConfUserOutputBean.class, new CsvParser(user,uuid,file))
                .doRead();
    }

批量新增使用 mybatisplus的批量新增方法

    @Transactional
    @Override
    public void batchInsert( List<ConfUser> add,ConfUser confUser,String uuid) {
        System.out.println("开始新增=====");
        boolean b = this.saveBatch(add);
        System.out.println("b ==="+b);

}