读取远程上CSV文件，并存储于本地数据库

电玩女神 2021-09-03 05:00 372阅读 0赞

> 需求：读取远程http路径下的csv文件，将部分字段存储在数据库中

pom文件

<dependency>
    			<groupId>net.sourceforge.javacsv</groupId>
    			<artifactId>javacsv</artifactId>
    			<version>2.0</version>
    		</dependency>

csv文件读取并存储于数据库中

public void csvRead(String libCode, String httpUrl) {
            CsvReader reader = null;
            try {
                URL url = new URL(httpUrl);
                HttpURLConnection connection = (HttpURLConnection) url.openConnection();
                DataInputStream inputStream = new DataInputStream(connection.getInputStream());
                
                reader = new CsvReader(inputStream, ',', Charset.forName("GBK"));
                //跳过第一行
                reader.readHeaders();
                Integer weekDay = null;
                List<HistoryVehiclePassDO> historyVehiclePassDOList = new ArrayList<>();
                String lineTxt = null;
                String[] columns = null;
                String timeSlot = null;
                Date date = null;
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                while (reader.readRecord()) {
                    HistoryVehiclePassDO historyVehiclePassDO = new HistoryVehiclePassDO();
                    lineTxt = reader.getRawRecord();
                    lineTxt = lineTxt.replace("\t", "");
                    lineTxt = lineTxt.replace("\"", "");
                    columns = lineTxt.split(",");
                    if (!isTimeLegal(columns[2])) {
                        continue;
                    }
                    historyVehiclePassDO.setVehicleNo(columns[0]);
                    historyVehiclePassDO.setCrossName(columns[1]);
                    date = dateFormat.parse(columns[2]);
                    historyVehiclePassDO.setTimeDate(date);
                    weekDay = dateToWeek(columns[2]);
                    historyVehiclePassDO.setWeekDay(weekDay);
                    timeSlot = columns[2].substring(11, 13);
                    historyVehiclePassDO.setTimeSlot(Integer.parseInt(timeSlot));
                    historyVehiclePassDO.setLibCode(libCode);
                    historyVehiclePassDOList.add(historyVehiclePassDO);
    //BusinessConstant.BATCH_NUM为常量1000，意思是list集合每满1000，存储一次
    //JPA的saveAll方法
                    if (historyVehiclePassDOList.size() % BusinessConstant.BATCH_NUM == 0) {
                        historyVehiclePassRepository.saveAll(historyVehiclePassDOList);
                        historyVehiclePassDOList.clear();
                    }
                }
    //储存剩余的行
                historyVehiclePassRepository.saveAll(historyVehiclePassDOList);
                historyVehiclePassDOList.clear();
                reader.close();
                inputStream.close();
            } catch (FileNotFoundException e) {
                LOGGER.error(HikLog.toLog(ErrorCodes.FIlE_NOT_FIND.getCode(), ErrorCodes.FIlE_NOT_FIND.getMessage()));
                e.printStackTrace();
            } catch (IOException e) {
                LOGGER.error(HikLog.toLog(ErrorCodes.CSV_READ_ERROR.getCode(), ErrorCodes.CSV_READ_ERROR.getMessage()));
                LOGGER.error("IOException:" + e.getMessage());
                e.printStackTrace();
            } catch (ParseException e) {
                LOGGER.error(HikLog.toLog(ErrorCodes.TIME_PARSE_ERROR.getCode(), ErrorCodes.TIME_PARSE_ERROR.getMessage()));
                e.printStackTrace();
            }
        }