<dependencies>
<!--springboot依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
</dependency>
<dependency>
<groupId>com.opencsv</groupId>
<artifactId>opencsv</artifactId>
<version>5.5.1</version>
</dependency>
<!-- MySQL 驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
<scope>runtime</scope>
</dependency>
<!-- mybatis plus的依赖 -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>21.0</version>
</dependency>
</dependencies>
package com.ohc.controller;
import com.ohc.service.FileUploadService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
@RestController
@RequestMapping("/file")
public class FileUploadController {
@Autowired
FileUploadService fileUploadService;
@PostMapping("/csv/import")
public String csvImport(@RequestParam("file") MultipartFile file) {
fileUploadService.csvImport(file);
return "文件上传成功";
}
//大数据量的CSV文件导入用多线程
@PostMapping("/csv/import/async")
public String csvImportAsync(@RequestParam("file") MultipartFile file) {
fileUploadService.csvImportAsync(file);
return "大数据量文件上传成功";
}
}
package com.ohc.service.impl;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.ohc.dao.BookDao;
import com.ohc.entity.Book;
import com.ohc.service.FileUploadService;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.opencsv.bean.HeaderColumnNameMappingStrategy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
@Service
public class FileUploadServiceImpl extends ServiceImpl<BookDao,Book> implements FileUploadService {
public static final Integer N_Thread = Runtime.getRuntime().availableProcessors() * 2 + 1;
@Override
public void csvImport(MultipartFile file) {
List<Book> bookList = convertCsvToList(file);
saveBatch(bookList);
}
//多线程导入数据库
@Override
public void csvImportAsync(MultipartFile file) {
List<Book> bookList = convertCsvToList(file);
//拆分List
List<List<Book>> bookLists = Lists.partition(bookList, 10000);
//创建线程池
ExecutorService executor = Executors.newFixedThreadPool(N_Thread);
for (int i = 0; i < bookLists.size(); i++) {
List<Book> books = bookLists.get(i);
executor.execute(() -> saveBatch(books));
}
}
private List<Book> convertCsvToList (MultipartFile file){
HeaderColumnNameMappingStrategy<Book> mappingStrategy = new HeaderColumnNameMappingStrategy<>();
mappingStrategy.setType(Book.class);
CsvToBean<Book> build = null;
try {
build = new CsvToBeanBuilder<Book>(new InputStreamReader(file.getInputStream(), StandardCharsets.UTF_8)).withMappingStrategy(mappingStrategy).withSeparator(',').build();
} catch (IOException e) {
throw new RuntimeException(e);
}
List<Book> bookList = build.parse();
return bookList;
}
}
package com.ohc.service;
import com.baomidou.mybatisplus.extension.service.IService;
import com.ohc.entity.Book;
import org.springframework.web.multipart.MultipartFile;
public interface FileUploadService extends IService<Book> {
void csvImport(MultipartFile file);
//大数据量CSV文件导入数据库
void csvImportAsync(MultipartFile file);
}
package com.ohc.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ohc.entity.Book;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface BookDao extends BaseMapper<Book> {
}
package com.ohc.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.opencsv.bean.CsvBindByName;
import lombok.Data;
import java.io.Serializable;
@Data
@TableName("book")
public class Book implements Serializable {
private static final long serialVersionUID = 1L;
@CsvBindByName(column = "id")
@TableId(value = "id", type = IdType.AUTO)
private int id;
@CsvBindByName(column = "cover")
private String cover;
@CsvBindByName(column = "title")
private String title;
@CsvBindByName(column = "author")
private String author;
@CsvBindByName(column = "date")
private String date;
@CsvBindByName(column = "press")
private String press;
@CsvBindByName(column = "abs")
private String abs;
@CsvBindByName(column = "cid")
private int cid;
}
server:
port: 8888
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://IP:3306/book_note?serverTimezone=UTC&userUnicode=true&useSSL=false&
username: root
password: password
hikari:
connection-timeout: 60000
idle-timeout: 500000
max-lifetime: 540000
maximum-pool-size: 20
minimum-idle: 10
servlet:
multipart:
max-file-size: 700MB
max-request-size: 700MB
Postman 如何上传csv文件
1、post请求
2、填写Headers
Key:Content-Type
Value:multipart/form-data
3、填写body
选择form-data
然后在key里填写file(对应接口的形参),这里注意在旁边选择文件file