1. Scheduled tasks
package cn.com.dhcc.sspcsystem.data;
import cn.com.dhcc.sspcsystem.entity.ElectronicFenceInfo;
import cn.com.dhcc.sspcsystem.mapper.ElectronicFenceInfoMapper;
import cn.com.dhcc.sspcsystem.util.CsvImportUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import com.alibaba.fastjson.JSONArray;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.stereotype.Component;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author stb
* @date 2023-05-24
* @description Synchronize XXX data regularly
*/
@Slf4j
@Component
@Configuration
@EnableScheduling
public class EtcDataTask {
/**
* csv file disk address
*/
@Value("${csvFilePath}")
public String filePath;
@Autowired
private ElectronicFenceInfoMapper electronicFenceInfoMapper;
// @Scheduled(cron = "0/10 * * * * ? ")
public void insertData() {
// Get all .csv files in the target folder
File root = new File(filePath);
File[] fileList = root. listFiles();
if (fileList == null || fileList. length < 1) {
log.warn("There is no imported XXX data");
return;
}
for (File file : fileList) {
// Check if file is a folder
if (file. isDirectory()) {
continue;
}
// Determine whether the file content is empty, if it is empty, delete the file
if (!file.exists() || file.length() == 0) {
file.delete();
continue;
}
// file path + file name
try {
String csvFilePath = file. getAbsolutePath();
// Parse the content of the file and store it in the List container. List<String> is the collection of each line, and 20 is the total number of columns in each line of the CSV file
List<List<String>> lists = CsvImportUtil. readCSV(csvFilePath, 24);
List<Map<String, Object>> mapList = new ArrayList<>();
for (int i = 0; i < lists. size(); i ++ ) {
HashMap<String, Object> HaAap = new HashMap<>();
HaAap.put("jd", lists.get(i).get(12));
HaAap.put("wd", lists.get(i).get(13));
mapList.add(HaAap);
}
JSONArray array = JSONArray. parseArray(JSON. toJSONString(mapList));
List<ElectronicFenceInfo> electronicFenceInfos = JSONObject.parseArray(array.toJSONString(), ElectronicFenceInfo.class);
int insertElectronicFenceInfoListCount = electronicFenceInfoMapper.insertElectronicFenceInfoList(electronicFenceInfos);
if (insertElectronicFenceInfoListCount == lists. size()) {
file.delete();
log.info("Imported successfully {" + insertElectronicFenceInfoListCount + "} items");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
2. util
package cn.com.dhcc.sspcsystem.util;
import lombok.Data;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
@Data
public class CsvImportUtil {
//path to upload file
// private final static URL PATH = Thread.currentThread().getContextClassLoader().getResource("");
/**
* @return File general file type
* @Description The file type of the uploaded file
* @Param multipartFile
**/
// public static File uploadFile(MultipartFile multipartFile) {
// // Get upload path
// String path = PATH.getPath() + multipartFile.getOriginalFilename();
// try {
// // Create a new File instance by converting the given pathname string to an abstract pathname
// File file = new File(path);
// // Whether the file or directory represented by this abstract pathname exists
// if (!file. getParentFile(). exists()) {
// // Create the directory named by this abstract pathname, including any required but non-existing parent directories
// file.getParentFile().mkdirs();
// }
// // Convert to a normal file file
// multipartFile. transferTo(file);
//
// return file;
// } catch (IOException e) {
//
// e. printStackTrace();
// return null;
// }
//
// }
/**
* @return List<List<String>>
* @Description Read the content of the CSV file (without the header)
* @Param filePath file storage path, colNum column number
**/
public static List<List<String>> readCSV(String filePath, int colNum) {
BufferedReader bufferedReader = null;
InputStreamReader inputStreamReader = null;
FileInputStream fileInputStream = null;
try {
fileInputStream = new FileInputStream(filePath);
inputStreamReader = new InputStreamReader(fileInputStream);
bufferedReader = new BufferedReader(inputStreamReader);
CSVParser parser = CSVFormat. DEFAULT. parse(bufferedReader);
// Table content collection, the outer List is a collection of rows, and the inner List is a collection of fields
List<List<String>> values = new ArrayList<>();
int rowIndex = 0;
// read each line of the file
for (CSVRecord record : parser. getRecords()) {
// skip header
// if (rowIndex == 0) {
//rowIndex++;
// continue;
// }
// Determine whether the subscript is out of bounds
if(colNum>record. size()){
// return an empty collection
return values;
}
// content of each row
List<String> value = new ArrayList<>();
for (int i = 0; i < colNum; i ++ ) {
value. add(record. get(i));
}
values. add(value);
rowIndex++;
}
return values;
} catch (IOException e) {
e.printStackTrace();
} finally {
// close the stream
if (bufferedReader != null) {
try {
bufferedReader. close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (inputStreamReader != null) {
try {
inputStreamReader. close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fileInputStream != null) {
try {
fileInputStream. close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
}
3. entity
package cn.com.dhcc.sspcsystem.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
/**
* XXX
*
* @author stb
* @date 2023/5/23
*/
@Data
@ApiModel
@TableName(value = "test_dzwl")
public class ElectronicFenceInfo {
@TableId
@ApiModelProperty(value = "primary key ID")
private String id;
@ApiModelProperty(value = "longitude")
private String jd;
@ApiModelProperty(value = "latitude")
private String wd;
}
4. mapper
package cn.com.dhcc.sspcsystem.mapper;
import cn.com.dhcc.sspcsystem.entity.ElectronicFenceInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* XXX information Mapper
*/
@Mapper
public interface ElectronicFenceInfoMapper extends BaseMapper<ElectronicFenceInfo> {
int insertElectronicFenceInfoList(List<ElectronicFenceInfo> list);
}
5. mapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<! DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.com.dhcc.sspcsystem.mapper.ElectronicFenceInfoMapper">
<insert id="insertElectronicFenceInfoList" parameterType="cn.com.dhcc.sspcsystem.entity.ElectronicFenceInfo">
INSERT INTO
test_dzwl(jd, wd)
VALUES
<foreach collection="list" separator="," item="item">
(
#{item.jd}, #{item.wd}
)
</foreach>
</insert>
</mapper>
6. Dependence
<dependency> <groupId>net.sourceforge.javacsv</groupId> <artifactId>javacsv</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.8.1</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-csv</artifactId> <version>1.7</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.68</version> </dependency>
7. Configuration file
csvFilePath: D://upload1