easypoiexcelutil导入都是null_导入:Java实现大批量数据导入导出(100W以上)

阅读文本大概需要3分钟。

来源&＃xff1a;https://www.cnblogs.com/barrywxx/p/10700221.html

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

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

说说为什么不能通过SQL直接导入到数据库&＃xff0c;而是通过程序实现&＃xff1a;

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

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

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

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

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

1. 一次读取这么大的数据量&＃xff0c;肯定会导致服务器内存溢出&＃xff1b;

2. 调用接口保存一次传输数据量太大&＃xff0c;网络传输压力会很大&＃xff1b;

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

三、解决思路

根据列举的技术难点我的解决思路是&＃xff1a;

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

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

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

四、具体实现代码

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

　略&＃xff0c;一般Java上传就可以实现&＃xff0c;这里就不贴出。

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

批量读取文件&＃xff1a;

1. import org.slf4j.Logger;

2. import org.slf4j.LoggerFactory;

4. import java.io.File;

5. import java.io.FileNotFoundException;

6. import java.io.RandomAccessFile;

7. import java.nio.ByteBuffer;

8. import java.nio.channels.FileChannel;

10. /**

11. * 类功能描述&＃xff1a;批量读取文件

12. *

13. * &＃64;author WangXueXing create at 19-3-14 下午6:47

14. * &＃64;version 1.0.0

15. */

16. public class BatchReadFile {

17. private final Logger LOGGER &＃61; LoggerFactory.getLogger(BatchReadFile.class);

18. /**

19. * 字符集UTF-8

20. */

21. public static final String CHARSET_UTF8 &＃61; "UTF-8";

22. /**

23. * 字符集GBK

24. */

25. public static final String CHARSET_GBK &＃61; "GBK";

26. /**

27. * 字符集gb2312

28. */

29. public static final String CHARSET_GB2312 &＃61; "gb2312";

30. /**

31. * 文件内容分割符&＃xff0d;逗号

32. */

33. public static final String SEPARATOR_COMMA &＃61; ",";

35. private int bufSize &＃61; 1024;

36. // 换行符

37. private byte key &＃61; "\n".getBytes()[0];

38. // 当前行数

39. private long lineNum &＃61; 0;

40. // 文件编码,默认为gb2312

41. private String encode &＃61; CHARSET_GB2312;

42. // 具体业务逻辑监听器

43. private ReaderFileListener readerListener;

45. public void setEncode(String encode) {

46. this.encode &＃61; encode;

47. }

49. public void setReaderListener(ReaderFileListener readerListener) {

50. this.readerListener &＃61; readerListener;

51. }

53. /**

54. * 获取准确开始位置

55. * &＃64;param file

56. * &＃64;param position

57. * &＃64;return

58. * &＃64;throws Exception

59. */

60. public long getStartNum(File file, long position) throws Exception {

61. long startNum &＃61; position;

62. FileChannel fcin &＃61; new RandomAccessFile(file, "r").getChannel();

63. fcin.position(position);

64. try {

65. int cache &＃61; 1024;

66. ByteBuffer rBuffer &＃61; ByteBuffer.allocate(cache);

67. // 每次读取的内容

68. byte[] bs &＃61; new byte[cache];

69. // 缓存

70. byte[] tempBs &＃61; new byte[0];

71. while (fcin.read(rBuffer) !&＃61; -1) {

72. int rSize &＃61; rBuffer.position();

73. rBuffer.rewind();

74. rBuffer.get(bs);

75. rBuffer.clear();

76. byte[] newStrByte &＃61; bs;

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

78. if (null !&＃61; tempBs) {

79. int tL &＃61; tempBs.length;

80. newStrByte &＃61; new byte[rSize &＃43; tL];

81. System.arraycopy(tempBs, 0, newStrByte, 0, tL);

82. System.arraycopy(bs, 0, newStrByte, tL, rSize);

83. }

84. // 获取开始位置之后的第一个换行符

85. int endIndex &＃61; indexOf(newStrByte, 0);

86. if (endIndex !&＃61; -1) {

87. return startNum &＃43; endIndex;

88. }

89. tempBs &＃61; substring(newStrByte, 0, newStrByte.length);

90. startNum &＃43;&＃61; 1024;

91. }

92. } finally {

93. fcin.close();

94. }

95. return position;

96. }

98. /**

99. * 从设置的开始位置读取文件&＃xff0c;一直到结束为止。如果 end设置为负数,刚读取到文件末尾

100. * &＃64;param fullPath

101. * &＃64;param start

102. * &＃64;param end

103. * &＃64;throws Exception

104. */

105. public void readFileByLine(String fullPath, long start, long end) throws Exception {

106. File fin &＃61; new File(fullPath);

107. if (!fin.exists()) {

108. throw new FileNotFoundException("没有找到文件&＃xff1a;" &＃43; fullPath);

109. }

110. FileChannel fileChannel &＃61; new RandomAccessFile(fin, "r").getChannel();

111. fileChannel.position(start);

112. try {

113. ByteBuffer rBuffer &＃61; ByteBuffer.allocate(bufSize);

114. // 每次读取的内容

115. byte[] bs &＃61; new byte[bufSize];

116. // 缓存

117. byte[] tempBs &＃61; new byte[0];

118. String line;

119. // 当前读取文件位置

120. long nowCur &＃61; start;

121. while (fileChannel.read(rBuffer) !&＃61; -1) {

122. int rSize &＃61; rBuffer.position();

123. rBuffer.rewind();

124. rBuffer.get(bs);

125. rBuffer.clear();

126. byte[] newStrByte;

127. //去掉表头

128. if(nowCur &＃61;&＃61; start){

129. int firstLineIndex &＃61; indexOf(bs, 0);

130. int newByteLenth &＃61; bs.length-firstLineIndex-1;

131. newStrByte &＃61; new byte[newByteLenth];

132. System.arraycopy(bs, firstLineIndex&＃43;1, newStrByte, 0, newByteLenth);

133. } else {

134. newStrByte &＃61; bs;

135. }

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

138. if (null !&＃61; tempBs && tempBs.length !&＃61; 0) {

139. int tL &＃61; tempBs.length;

140. newStrByte &＃61; new byte[rSize &＃43; tL];

141. System.arraycopy(tempBs, 0, newStrByte, 0, tL);

142. System.arraycopy(bs, 0, newStrByte, tL, rSize);

143. }

144. // 是否已经读到最后一位

145. boolean isEnd &＃61; false;

146. nowCur &＃43;&＃61; bufSize;

147. // 如果当前读取的位数已经比设置的结束位置大的时候&＃xff0c;将读取的内容截取到设置的结束位置

148. if (end > 0 && nowCur > end) {

149. // 缓存长度 - 当前已经读取位数 - 最后位数

150. int l &＃61; newStrByte.length - (int) (nowCur - end);

151. newStrByte &＃61; substring(newStrByte, 0, l);

152. isEnd &＃61; true;

153. }

154. int fromIndex &＃61; 0;

155. int endIndex &＃61; 0;

156. // 每次读一行内容&＃xff0c;以 key(默认为\n) 作为结束符

157. while ((endIndex &＃61; indexOf(newStrByte, fromIndex)) !&＃61; -1) {

158. byte[] bLine &＃61; substring(newStrByte, fromIndex, endIndex);

159. line &＃61; new String(bLine, 0, bLine.length, encode);

160. lineNum&＃43;&＃43;;

161. // 输出一行内容&＃xff0c;处理方式由调用方提供

162. readerListener.outLine(line.trim(), lineNum, false);

163. fromIndex &＃61; endIndex &＃43; 1;

164. }

165. // 将未读取完成的内容放到缓存中

166. tempBs &＃61; substring(newStrByte, fromIndex, newStrByte.length);

167. if (isEnd) {

168. break;

169. }

170. }

171. // 将剩下的最后内容作为一行&＃xff0c;输出&＃xff0c;并指明这是最后一行

172. String lineStr &＃61; new String(tempBs, 0, tempBs.length, encode);

173. readerListener.outLine(lineStr.trim(), lineNum, true);

174. } finally {

175. fileChannel.close();

176. fin.deleteOnExit();

177. }

178. }

180. /**

181. * 查找一个byte[]从指定位置之后的一个换行符位置

182. *

183. * &＃64;param src

184. * &＃64;param fromIndex

185. * &＃64;return

186. * &＃64;throws Exception

187. */

188. private int indexOf(byte[] src, int fromIndex) throws Exception {

189. for (int i &＃61; fromIndex; i

190. if (src[i] &＃61;&＃61; key) {

191. return i;

192. }

193. }

194. return -1;

195. }

197. /**

198. * 从指定开始位置读取一个byte[]直到指定结束位置为止生成一个全新的byte[]

199. *

200. * &＃64;param src

201. * &＃64;param fromIndex

202. * &＃64;param endIndex

203. * &＃64;return

204. * &＃64;throws Exception

205. */

206. private byte[] substring(byte[] src, int fromIndex, int endIndex) throws Exception {

207. int size &＃61; endIndex - fromIndex;

208. byte[] ret &＃61; new byte[size];

209. System.arraycopy(src, fromIndex, ret, 0, size);

210. return ret;

211. }

212. }

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

多线程调用批量读取&＃xff1a; 

1. /**

2. * 类功能描述: 线程读取文件

3. *

4. * &＃64;author WangXueXing create at 19-3-14 下午6:51

5. * &＃64;version 1.0.0

6. */

7. public class ReadFileThread extends Thread {

8. private ReaderFileListener processDataListeners;

9. private String filePath;

10. private long start;

11. private long end;

12. private Thread preThread;

14. public ReadFileThread(ReaderFileListener processDataListeners,

15. long start,long end,

16. String file) {

17. this(processDataListeners, start, end, file, null);

18. }

20. public ReadFileThread(ReaderFileListener processDataListeners,

21. long start,long end,

22. String file,

23. Thread preThread) {

24. this.setName(this.getName()&＃43;"-ReadFileThread");

25. this.start &＃61; start;

26. this.end &＃61; end;

27. this.filePath &＃61; file;

28. this.processDataListeners &＃61; processDataListeners;

29. this.preThread &＃61; preThread;

30. }

32. &＃64;Override

33. public void run() {

34. BatchReadFile readFile &＃61; new BatchReadFile();

35. readFile.setReaderListener(processDataListeners);

36. readFile.setEncode(processDataListeners.getEncode());

37. try {

38. readFile.readFileByLine(filePath, start, end &＃43; 1);

39. if(this.preThread !&＃61; null){

40. this.preThread.join();

41. }

42. } catch (Exception e) {

43. throw new RuntimeException(e);

44. }

45. }

46. }

监听读取&＃xff1a;

1. import java.util.ArrayList;

2. import java.util.List;

4. /**

5. * 类功能描述&＃xff1a;读文件监听父类

6. *

7. * &＃64;author WangXueXing create at 19-3-14 下午6:52

8. * &＃64;version 1.0.0

9. */

10. public abstract class ReaderFileListener {

11. // 一次读取行数&＃xff0c;默认为1000

12. private int readColNum &＃61; 1000;

14. /**

15. * 文件编码

16. */

17. private String encode;

19. /**

20. * 分批读取行列表

21. */

22. private List rowList &＃61; new ArrayList<>();

24. /**

25. *其他参数

26. */

27. private T otherParams;

29. /**

30. * 每读取到一行数据&＃xff0c;添加到缓存中

31. * &＃64;param lineStr 读取到的数据

32. * &＃64;param lineNum 行号

33. * &＃64;param over 是否读取完成

34. * &＃64;throws Exception

35. */

36. public void outLine(String lineStr, long lineNum, boolean over) throws Exception {

37. if(null !&＃61; lineStr && !lineStr.trim().equals("")){

38. rowList.add(lineStr);

39. }

41. if (!over && (lineNum % readColNum &＃61;&＃61; 0)) {

42. output(rowList);

43. rowList &＃61; new ArrayList<>();

44. } else if (over) {

45. output(rowList);

46. rowList &＃61; new ArrayList<>();

47. }

48. }

50. /**

51. * 批量输出

52. *

53. * &＃64;param stringList

54. * &＃64;throws Exception

55. */

56. public abstract void output(List stringList) throws Exception;

58. /**

59. * 设置一次读取行数

60. * &＃64;param readColNum

61. */

62. protected void setReadColNum(int readColNum) {

63. this.readColNum &＃61; readColNum;

64. }

66. public String getEncode() {

67. return encode;

68. }

70. public void setEncode(String encode) {

71. this.encode &＃61; encode;

72. }

74. public T getOtherParams() {

75. return otherParams;

76. }

78. public void setOtherParams(T otherParams) {

79. this.otherParams &＃61; otherParams;

80. }

82. public List getRowList() {

83. return rowList;

84. }

86. public void setRowList(List rowList) {

87. this.rowList &＃61; rowList;

88. }

89. }

实现监听读取并分批调用插入数据接口&＃xff1a;

1. import com.today.api.finance.ImportServiceClient;

2. import com.today.api.finance.request.ImportRequest;

3. import com.today.api.finance.response.ImportResponse;

4. import com.today.api.finance.service.ImportService;

5. import com.today.common.Constants;

6. import com.today.domain.StaffSimpInfo;

7. import com.today.util.EmailUtil;

8. import com.today.util.UserSessionHelper;

9. import com.today.util.readfile.ReadFile;

10. import com.today.util.readfile.ReadFileThread;

11. import com.today.util.readfile.ReaderFileListener;

12. import org.slf4j.Logger;

13. import org.slf4j.LoggerFactory;

14. import org.springframework.beans.factory.annotation.Value;

15. import org.springframework.stereotype.Service;

16. import org.springframework.util.StringUtils;

18. import java.io.File;

19. import java.io.FileInputStream;

20. import java.util.ArrayList;

21. import java.util.Arrays;

22. import java.util.List;

23. import java.util.concurrent.FutureTask;

24. import java.util.stream.Collectors;

26. /**

27. * 类功能描述&＃xff1a;报表导入服务实现

28. *

29. * &＃64;author WangXueXing create at 19-3-19 下午1:43

30. * &＃64;version 1.0.0

31. */

32. &＃64;Service

33. public class ImportReportServiceImpl extends ReaderFileListener {

34. private final Logger LOGGER &＃61; LoggerFactory.getLogger(ImportReportServiceImpl.class);

35. &＃64;Value("${READ_COL_NUM_ONCE}")

36. private String readColNum;

37. &＃64;Value("${REPORT_IMPORT_RECEIVER}")

38. private String reportImportReceiver;

39. /**

40. * 财务报表导入接口

41. */

42. private ImportService service &＃61; new ImportServiceClient();

44. /**

45. * 读取文件内容

46. * &＃64;param file

47. */

48. public void readTxt(File file, ImportRequest importRequest) throws Exception {

49. this.setOtherParams(importRequest);

50. ReadFile readFile &＃61; new ReadFile();

51. try(FileInputStream fis &＃61; new FileInputStream(file)){

52. int available &＃61; fis.available();

53. long maxThreadNum &＃61; &＃xff13;L;

54. // 线程粗略开始位置

55. long i &＃61; available / maxThreadNum;

57. this.setRowList(new ArrayList<>());

58. StaffSimpInfo staffSimpInfo &＃61; ((StaffSimpInfo)UserSessionHelper.getCurrentUserInfo().getData());

59. String finalReportReceiver &＃61; getEmail(staffSimpInfo.getEmail(), reportImportReceiver);

60. this.setReadColNum(Integer.parseInt(readColNum));

61. this.setEncode(ReadFile.CHARSET_GB2312);

62. //这里单独使用一个线程是为了当maxThreadNum大于1的时候&＃xff0c;统一管理这些线程

63. new Thread(()->{

64. Thread preThread &＃61; null;

65. FutureTask futureTask &＃61; null ;

66. try {

67. for (long j &＃61; 0; j

68. //计算精确开始位置

69. long startNum &＃61; j &＃61;&＃61; 0 ? 0 : readFile.getStartNum(file, i * j);

70. long endNum &＃61; j &＃43; 1

72. //具体监听实现

73. preThread &＃61; new ReadFileThread(this, startNum, endNum, file.getPath(), preThread);

74. futureTask &＃61; new FutureTask(preThread, new Object());

75. futureTask.run();

76. }

77. if(futureTask.get() !&＃61; 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("导入报表类型:"&＃43;importRequest.getReportType()&＃43;"失败", e);

89. } finally {

90. futureTask.cancel(true);

91. }

92. }).start();

93. }

94. }

96. private String getEmail(String infoEmail, String reportImportReceiver){

97. if(StringUtils.isEmpty(infoEmail)){

98. return reportImportReceiver;

99. }

100. return infoEmail;

101. }

103. /**

104. * 每批次调用导入接口

105. * &＃64;param stringList

106. * &＃64;throws Exception

107. */

108. &＃64;Override

109. public void output(List stringList) throws Exception {

110. ImportRequest importRequest &＃61; this.getOtherParams();

111. List> dataList &＃61; 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对象&＃xff1a;{}",importRequest, "request增加请求字段&＃xff1a;{}", importRequest.data);

117. ImportResponse importResponse &＃61; service.batchImport(importRequest);

118. LOGGER.info("&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;SUCESS_CODE&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;"&＃43;importResponse.getCode());

119. //导入错误,输出错误信息

120. if(!Constants.SUCESS_CODE.equals(importResponse.getCode())){

121. LOGGER.error("导入报表类型:"&＃43;importRequest.getReportType()&＃43;"失败","返回码为&＃xff1a;", importResponse.getCode() ,"返回信息&＃xff1a;",importResponse.getMessage());

122. throw new RuntimeException("导入报表类型:"&＃43;importRequest.getReportType()&＃43;"失败"&＃43;"返回码为&＃xff1a;"&＃43; importResponse.getCode() &＃43;"返回信息&＃xff1a;"&＃43;importResponse.getMessage());

123. }

124. // if(importResponse.data !&＃61; null && importResponse.data.get().get("batchImportFlag")!&＃61;null) {

125. // LOGGER.info("eywa-service请求batchImportFlag不为空");

126. // }

127. importRequest.setData(importResponse.data);

129. }

130. }

注意&＃xff1a;第53行代码&＃xff1a;long maxThreadNum &＃61; &＃xff13;L;
就是设置分批读取磁盘文件的线程数&＃xff0c;我设置为&＃xff13;&＃xff0c;大家不要设置太大&＃xff0c;不然多个线程读取到内存&＃xff0c;也会造成服务器内存溢出。

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

数据库分批插入数据&＃xff1a;

1. /**

2. * 批量插入非联机第三方导入账单

3. * &＃64;param dataList

4. */

5. def insertNonOnlinePayment(dataList: List[NonOnlineSourceData]) : Unit &＃61; {

6. if (dataList.nonEmpty) {

7. CheckAccountDataSource.mysqlData.withConnection { conn &＃61;>

8. val sql &＃61;

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())"""

23. conn.setAutoCommit(false)

24. var stmt &＃61; conn.prepareStatement(sql)

25. var i &＃61; 0

26. dataList.foreach { x &＃61;>

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 &＃61;&＃61; 0) && (i !&＃61; 0)) { //分批提交

38. stmt.executeBatch

39. conn.commit

40. conn.setAutoCommit(false)

41. stmt &＃61; conn.prepareStatement(sql)

43. }

44. i &＃43;&＃61; 1

45. }

46. stmt.executeBatch()

47. conn.commit()

48. }

49. }

50. }

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

☆

往期精彩

☆

01 漫谈发版哪些事&＃xff0c;好课程推荐

02 Linux的常用最危险的命令

03 精讲Spring Boot—入门&＃43;进阶&＃43;实例

04 优秀的Java程序员必须了解的GC哪些

05 互联网支付系统整体架构详解

关注我

每天进步一点点