Java千万数据导入mysql_Java实现大批量数据导入导出(100W以上) -（一）导入

最近业务方有一个需求，需要一次导入超过100万数据到系统数据库。可能大家首先会想，这么大的数据，干嘛通过程序去实现导入，为什么不直接通过SQL导入到数据库。

一、为什么一定要在代码实现

说说为什么不能通过SQL直接导入到数据库，而是通过程序实现：

1. 首先，这个导入功能开始提供页面导入，只是开始业务方保证的一次只有<3W的数据导入；

2. 其次，业务方导入的内容需要做校验，比如门店号，商品号等是否系统存在，需要程序校验；

3. 最后，业务方导入的都是编码，数据库中还要存入对应名称，方便后期查询，SQL导入也是无法实现的。

基于以上上三点，就无法直接通过SQL语句导入数据库。那就只能老老实实的想办法通过程序实现。

二、程序实现有以下技术难点

1. 一次读取这么大的数据量，肯定会导致服务器内存溢出；

2. 调用接口保存一次传输数据量太大，网络传输压力会很大；

3. 最终通过SQL一次批量插入，对数据库压力也比较大，如果业务同时操作这个表数据，很容易造成死锁。

三、解决思路

根据列举的技术难点我的解决思路是：

1. 既然一次读取整个导入文件，那就先将文件流上传到服务器磁盘，然后分批从磁盘读取(支持多线程读取)，这样就防止内存溢出；

2. 调用插入数据库接口也是根据分批读取的内容进行调用；

3. 分批插入数据到数据库。

四、具体实现代码

1. 流式上传文件到服务器磁盘

略，一般Java上传就可以实现，这里就不贴出。

2. 多线程分批从磁盘读取

批量读取文件：

1 importorg.slf4j.Logger;2 importorg.slf4j.LoggerFactory;3

4 importjava.io.File;5 importjava.io.FileNotFoundException;6 importjava.io.RandomAccessFile;7 importjava.nio.ByteBuffer;8 importjava.nio.channels.FileChannel;9

10 /**

11 * 类功能描述：批量读取文件12 *13 *@authorWangXueXing create at 19-3-14 下午6:4714 *@version1.0.015 */

16 public classBatchReadFile {17 private final Logger LOGGER = LoggerFactory.getLogger(BatchReadFile.class);18 /**

19 * 字符集UTF-820 */

21 public static final String CHARSET_UTF8 = "UTF-8";22 /**

23 * 字符集GBK24 */

25 public static final String CHARSET_GBK = "GBK";26 /**

27 * 字符集gb231228 */

29 public static final String CHARSET_GB2312 = "gb2312";30 /**

31 * 文件内容分割符－逗号32 */

33 public static final String SEPARATOR_COMMA = ",";34

35 private int bufSize = 1024;36 //换行符

37 private byte key = "\n".getBytes()[0];38 //当前行数

39 private long lineNum = 0;40 //文件编码,默认为gb2312

41 private String encode =CHARSET_GB2312;42 //具体业务逻辑监听器

43 privateReaderFileListener readerListener;44

45 public voidsetEncode(String encode) {46 this.encode =encode;47 }48

49 public voidsetReaderListener(ReaderFileListener readerListener) {50 this.readerListener =readerListener;51 }52

53 /**

54 * 获取准确开始位置55 *@paramfile56 *@paramposition57 *@return

58 *@throwsException59 */

60 public long getStartNum(File file, long position) throwsException {61 long startNum =position;62 FileChannel fcin = new RandomAccessFile(file, "r").getChannel();63 fcin.position(position);64 try{65 int cache = 1024;66 ByteBuffer rBuffer =ByteBuffer.allocate(cache);67 //每次读取的内容

68 byte[] bs = new byte[cache];69 //缓存

70 byte[] tempBs = new byte[0];71 while (fcin.read(rBuffer) != -1) {72 int rSize =rBuffer.position();73 rBuffer.rewind();74 rBuffer.get(bs);75 rBuffer.clear();76 byte[] newStrByte =bs;77 //如果发现有上次未读完的缓存,则将它加到当前读取的内容前面

78 if (null !=tempBs) {79 int tL =tempBs.length;80 newStrByte = new byte[rSize +tL];81 System.arraycopy(tempBs, 0, newStrByte, 0, tL);82 System.arraycopy(bs, 0, newStrByte, tL, rSize);83 }84 //获取开始位置之后的第一个换行符

85 int endIndex = indexOf(newStrByte, 0);86 if (endIndex != -1) {87 return startNum +endIndex;88 }89 tempBs = substring(newStrByte, 0, newStrByte.length);90 startNum += 1024;91 }92 } finally{93 fcin.close();94 }95 returnposition;96 }97

98 /**

99 * 从设置的开始位置读取文件，一直到结束为止。如果 end设置为负数,刚读取到文件末尾100 *@paramfullPath101 *@paramstart102 *@paramend103 *@throwsException104 */

105 public void readFileByLine(String fullPath, long start, long end) throwsException {106 File fin = newFile(fullPath);107 if (!fin.exists()) {108 throw new FileNotFoundException("没有找到文件：" +fullPath);109 }110 FileChannel fileChannel = new RandomAccessFile(fin, "r").getChannel();111 fileChannel.position(start);112 try{113 ByteBuffer rBuffer =ByteBuffer.allocate(bufSize);114 //每次读取的内容

115 byte[] bs = new byte[bufSize];116 //缓存

117 byte[] tempBs = new byte[0];118 String line;119 //当前读取文件位置

120 long nowCur =start;121 while (fileChannel.read(rBuffer) != -1) {122 int rSize =rBuffer.position();123 rBuffer.rewind();124 rBuffer.get(bs);125 rBuffer.clear();126 byte[] newStrByte;127 //去掉表头

128 if(nowCur ==start){129 int firstLineIndex = indexOf(bs, 0);130 int newByteLenth = bs.length-firstLineIndex-1;131 newStrByte = new byte[newByteLenth];132 System.arraycopy(bs, firstLineIndex+1, newStrByte, 0, newByteLenth);133 } else{134 newStrByte =bs;135 }136

137 //如果发现有上次未读完的缓存,则将它加到当前读取的内容前面

138 if (null != tempBs && tempBs.length != 0) {139 int tL =tempBs.length;140 newStrByte = new byte[rSize +tL];141 System.arraycopy(tempBs, 0, newStrByte, 0, tL);142 System.arraycopy(bs, 0, newStrByte, tL, rSize);143 }144 //是否已经读到最后一位

145 boolean isEnd = false;146 nowCur +=bufSize;147 //如果当前读取的位数已经比设置的结束位置大的时候，将读取的内容截取到设置的结束位置

148 if (end > 0 && nowCur >end) {149 //缓存长度 - 当前已经读取位数 - 最后位数

150 int l = newStrByte.length - (int) (nowCur -end);151 newStrByte = substring(newStrByte, 0, l);152 isEnd = true;153 }154 int fromIndex = 0;155 int endIndex = 0;156 //每次读一行内容，以 key(默认为\n) 作为结束符

157 while ((endIndex = indexOf(newStrByte, fromIndex)) != -1) {158 byte[] bLine =substring(newStrByte, fromIndex, endIndex);159 line = new String(bLine, 0, bLine.length, encode);160 lineNum++;161 //输出一行内容，处理方式由调用方提供

162 readerListener.outLine(line.trim(), lineNum, false);163 fromIndex = endIndex + 1;164 }165 //将未读取完成的内容放到缓存中

166 tempBs =substring(newStrByte, fromIndex, newStrByte.length);167 if(isEnd) {168 break;169 }170 }171 //将剩下的最后内容作为一行，输出，并指明这是最后一行

172 String lineStr = new String(tempBs, 0, tempBs.length, encode);173 readerListener.outLine(lineStr.trim(), lineNum, true);174 } finally{175 fileChannel.close();176 fin.deleteOnExit();177 }178 }179

180 /**

181 * 查找一个byte[]从指定位置之后的一个换行符位置182 *183 *@paramsrc184 *@paramfromIndex185 *@return

186 *@throwsException187 */

188 private int indexOf(byte[] src, int fromIndex) throwsException {189 for (int i = fromIndex; i < src.length; i++) {190 if (src[i] ==key) {191 returni;192 }193 }194 return -1;195 }196

197 /**

198 * 从指定开始位置读取一个byte[]直到指定结束位置为止生成一个全新的byte[]199 *200 *@paramsrc201 *@paramfromIndex202 *@paramendIndex203 *@return

204 *@throwsException205 */

206 private byte[] substring(byte[] src, int fromIndex, int endIndex) throwsException {207 int size = endIndex -fromIndex;208 byte[] ret = new byte[size];209 System.arraycopy(src, fromIndex, ret, 0, size);210 returnret;211 }212 }

以上是关键代码：利用FileChannel与ByteBuffer从磁盘中分批读取数据

多线程调用批量读取：

1 /**

2 * 类功能描述: 线程读取文件3 *4 *@authorWangXueXing create at 19-3-14 下午6:515 *@version1.0.06 */

7 public class ReadFileThread extendsThread {8 privateReaderFileListener processDataListeners;9 privateString filePath;10 private longstart;11 private longend;12 privateThread preThread;13

14 publicReadFileThread(ReaderFileListener processDataListeners,15 long start,longend,16 String file) {17 this(processDataListeners, start, end, file, null);18 }19

20 publicReadFileThread(ReaderFileListener processDataListeners,21 long start,longend,22 String file,23 Thread preThread) {24 this.setName(this.getName()+"-ReadFileThread");25 this.start =start;26 this.end =end;27 this.filePath =file;28 this.processDataListeners =processDataListeners;29 this.preThread =preThread;30 }31

32 @Override33 public voidrun() {34 BatchReadFile readFile = newBatchReadFile();35 readFile.setReaderListener(processDataListeners);36 readFile.setEncode(processDataListeners.getEncode());37 try{38 readFile.readFileByLine(filePath, start, end + 1);39 if(this.preThread != null){40 this.preThread.join();41 }42 } catch(Exception e) {43 throw newRuntimeException(e);44 }45 }46 }

监听读取：

1 importjava.util.ArrayList;2 importjava.util.List;3

4 /**

5 * 类功能描述：读文件监听父类6 *7 *@authorWangXueXing create at 19-3-14 下午6:528 *@version1.0.09 */

10 public abstract class ReaderFileListener{11 //一次读取行数，默认为1000

12 private int readColNum = 1000;13

14 /**

15 * 文件编码16 */

17 privateString encode;18

19 /**

20 * 分批读取行列表21 */

22 private List rowList = new ArrayList<>();23

24 /**

25 *其他参数26 */

27 privateT otherParams;28

29 /**

30 * 每读取到一行数据，添加到缓存中31 *@paramlineStr 读取到的数据32 *@paramlineNum 行号33 *@paramover 是否读取完成34 *@throwsException35 */

36 public void outLine(String lineStr, long lineNum, boolean over) throwsException {37 if(null != lineStr && !lineStr.trim().equals("")){38 rowList.add(lineStr);39 }40

41 if (!over && (lineNum % readColNum == 0)) {42 output(rowList);43 rowList = new ArrayList<>();44 } else if(over) {45 output(rowList);46 rowList = new ArrayList<>();47 }48 }49

50 /**

51 * 批量输出52 *53 *@paramstringList54 *@throwsException55 */

56 public abstract void output(List stringList) throwsException;57

58 /**

59 * 设置一次读取行数60 *@paramreadColNum61 */

62 protected void setReadColNum(intreadColNum) {63 this.readColNum =readColNum;64 }65

66 publicString getEncode() {67 returnencode;68 }69

70 public voidsetEncode(String encode) {71 this.encode =encode;72 }73

74 publicT getOtherParams() {75 returnotherParams;76 }77

78 public voidsetOtherParams(T otherParams) {79 this.otherParams =otherParams;80 }81

82 public ListgetRowList() {83 returnrowList;84 }85

86 public void setRowList(ListrowList) {87 this.rowList =rowList;88 }89 }

实现监听读取并分批调用插入数据接口：

1 importcom.today.api.finance.ImportServiceClient;2 importcom.today.api.finance.request.ImportRequest;3 importcom.today.api.finance.response.ImportResponse;4 importcom.today.api.finance.service.ImportService;5 importcom.today.common.Constants;6 importcom.today.domain.StaffSimpInfo;7 importcom.today.util.EmailUtil;8 importcom.today.util.UserSessionHelper;9 importcom.today.util.readfile.ReadFile;10 importcom.today.util.readfile.ReadFileThread;11 importcom.today.util.readfile.ReaderFileListener;12 importorg.slf4j.Logger;13 importorg.slf4j.LoggerFactory;14 importorg.springframework.beans.factory.annotation.Value;15 importorg.springframework.stereotype.Service;16 importorg.springframework.util.StringUtils;17

18 importjava.io.File;19 importjava.io.FileInputStream;20 importjava.util.ArrayList;21 importjava.util.Arrays;22 importjava.util.List;23 importjava.util.concurrent.FutureTask;24 importjava.util.stream.Collectors;25

26 /**

27 * 类功能描述：报表导入服务实现28 *29 *@authorWangXueXing create at 19-3-19 下午1:4330 *@version1.0.031 */

32 @Service33 public class ImportReportServiceImpl extends ReaderFileListener{34 private final Logger LOGGER = LoggerFactory.getLogger(ImportReportServiceImpl.class);35 @Value("${READ_COL_NUM_ONCE}")36 privateString readColNum;37 @Value("${REPORT_IMPORT_RECEIVER}")38 privateString reportImportReceiver;39 /**

40 * 财务报表导入接口41 */

42 private ImportService service = newImportServiceClient();43

44 /**

45 * 读取文件内容46 *@paramfile47 */

48 public void readTxt(File file, ImportRequest importRequest) throwsException {49 this.setOtherParams(importRequest);50 ReadFile readFile = newReadFile();51 try(FileInputStream fis = newFileInputStream(file)){52 int available =fis.available();53 long maxThreadNum = ３L;54 //线程粗略开始位置

55 long i = available /maxThreadNum;56

57 this.setRowList(new ArrayList<>());58 StaffSimpInfo staffSimpInfo =((StaffSimpInfo)UserSessionHelper.getCurrentUserInfo().getData());59 String finalReportReceiver =getEmail(staffSimpInfo.getEmail(), reportImportReceiver);60 this.setReadColNum(Integer.parseInt(readColNum));61 this.setEncode(ReadFile.CHARSET_GB2312);62 //这里单独使用一个线程是为了当maxThreadNum大于1的时候，统一管理这些线程

63 new Thread(()->{64 Thread preThread = null;65 FutureTask futureTask = null;66 try{67 for (long j = 0; j < maxThreadNum; j++) {68 //计算精确开始位置

69 long startNum = j == 0 ? 0 : readFile.getStartNum(file, i *j);70 long endNum = j + 1 < maxThreadNum ? readFile.getStartNum(file, i * (j + 1)) : -2L;71

72 //具体监听实现

73 preThread = new ReadFileThread(this, startNum, endNum, file.getPath(), preThread);74 futureTask = new FutureTask(preThread, newObject());75 futureTask.run();76 }77 if(futureTask.get() != null) {78 EmailUtil.sendEmail(EmailUtil.REPORT_IMPORT_EMAIL_PREFIX, finalReportReceiver, "导入报表成功", "导入报表成功" ); //todo 等文案

79 }80 } catch(Exception e){81 futureTask.cancel(true);82 try{83 EmailUtil.sendEmail(EmailUtil.REPORT_IMPORT_EMAIL_PREFIX, finalReportReceiver, "导入报表失败", e.getMessage());84 } catch(Exception e1){85 //ignore

86 LOGGER.error("发送邮件失败", e1);87 }88 LOGGER.error("导入报表类型:"+importRequest.getReportType()+"失败", e);89 } finally{90 futureTask.cancel(true);91 }92 }).start();93 }94 }95

96 privateString getEmail(String infoEmail, String reportImportReceiver){97 if(StringUtils.isEmpty(infoEmail)){98 returnreportImportReceiver;99 }100 returninfoEmail;101 }102

103 /**

104 * 每批次调用导入接口105 *@paramstringList106 *@throwsException107 */

108 @Override109 public void output(List stringList) throwsException {110 ImportRequest importRequest = this.getOtherParams();111 List> dataList =stringList.stream()112 .map(x->Arrays.asList(x.split(ReadFile.SEPARATOR_COMMA)).stream().map(String::trim).collect(Collectors.toList()))113 .collect(Collectors.toList());114 LOGGER.info("上传数据:{}", dataList);115 importRequest.setDataList(dataList);116 //LOGGER.info("request对象：{}",importRequest, "request增加请求字段：{}", importRequest.data);

117 ImportResponse importResponse =service.batchImport(importRequest);118 LOGGER.info("===========SUCESS_CODE======="+importResponse.getCode());119 //导入错误,输出错误信息

120 if(!Constants.SUCESS_CODE.equals(importResponse.getCode())){121 LOGGER.error("导入报表类型:"+importRequest.getReportType()+"失败","返回码为：", importResponse.getCode() ,"返回信息：",importResponse.getMessage());122 throw new RuntimeException("导入报表类型:"+importRequest.getReportType()+"失败"+"返回码为："+ importResponse.getCode() +"返回信息："+importResponse.getMessage());123 }124 //if(importResponse.data != null && importResponse.data.get().get("batchImportFlag")!=null) {125 //LOGGER.info("eywa-service请求batchImportFlag不为空");126 //}

127 importRequest.setData(importResponse.data);128

129 }130 }

注意：

第53行代码：

long maxThreadNum = ３L;

就是设置分批读取磁盘文件的线程数，我设置为３，大家不要设置太大，不然多个线程读取到内存，也会造成服务器内存溢出。

以上所有批次的批量读取并调用插入接口都成功发送邮件通知给导入人，任何一个批次失败直接发送失败邮件。

数据库分批插入数据：

1 /**

2 * 批量插入非联机第三方导入账单3 *@paramdataList4 */

5 def insertNonOnlinePayment(dataList: List[NonOnlineSourceData]) : Unit ={6 if(dataList.nonEmpty) {7 CheckAccountDataSource.mysqlData.withConnection { conn =>

8 val sql =

9 s""" INSERT INTO t_pay_source_data

10 (store_code,11 store_name,12 source_date,13 order_type,14 trade_type,15 third_party_payment_no,16 business_type,17 business_amount,18 trade_time,19 created_at,20 updated_at)21 VALUES (?,?,?,?,?,?,?,?,?,NOW(),NOW())"""22

23 conn.setAutoCommit(false)24 var stmt =conn.prepareStatement(sql)25 var i = 0

26 dataList.foreach { x =>

27 stmt.setString(1, x.storeCode)28 stmt.setString(2, x.storeName)29 stmt.setString(3, x.sourceDate)30 stmt.setInt(4, x.orderType)31 stmt.setInt(5, x.tradeType)32 stmt.setString(6, x.tradeNo)33 stmt.setInt(7, x.businessType)34 stmt.setBigDecimal(8, x.businessAmount.underlying())35 stmt.setString(9, x.tradeTime.getOrElse(null))36 stmt.addBatch()37 if ((i % 5000 == 0) && (i != 0)) { //分批提交

38 stmt.executeBatch39 conn.commit40 conn.setAutoCommit(false)41 stmt =conn.prepareStatement(sql)42

43 }44 i += 1

45 }46 stmt.executeBatch()47 conn.commit()48 }49 }50 }

以上代码实现每5000 行提交一次批量插入，防止一次提较数据库的压力。

以上，如果大家有更好方案，请留言。