opencsv的实现,用于读取csv文件并保存到数据库。
import com.opencsv.CSVParser;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import com.opencsv.bean.CsvBindByPosition;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.persistence.Column;
import java.io.*;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Service
@Slf4j
public class FileUploadService {
@Autowired
private InsertCSVContentToDB csvContentToDB;
/**
* @param csvFileName location of the physical file.
* @param type Employee.class
* @param delimiter can be , | # etc
* @param obj //new Employee();
*
* import com.opencsv.bean.CsvBindByPosition;
* import lombok.Data;
*
* import javax.persistence.Column;
*
* @Data
* public class Employee {
*
* @CsvBindByPosition(position = 0, required = true)
* @Column(name = "EMPLOYEE_NAME")
* private String employeeName;
*
* @CsvBindByPosition(position = 1)
* @Column(name = "Employee_ADDRESS_1")
* private String employeeAddress1;
* }
*
* @param sqlQuery query to save data to DB
* @param noOfLineSkip make it 0(Zero) so that it should not skip any line.
* @param auditId apart from regular column in csv we need to add more column for traking like file id or audit id
* @return
*/
public <T> void readCSVContentInArray(String csvFileName, Class<? extends T> type, char delimiter, Object obj,
String sqlQuery, int noOfLineSkip, Long auditId) {
List<T> lstCsvContent = new ArrayList<>();
Reader reader = null;
CSVReader csv = null;
try {
reader = new BufferedReader(new InputStreamReader(new FileInputStream(csvFileName), "utf-8"));
log.info("Buffer Reader : " + ((BufferedReader) reader).readLine().isEmpty());
CSVParser parser = new CSVParserBuilder().withSeparator(delimiter).withIgnoreQuotations(true).build();
csv = new CSVReaderBuilder(reader).withSkipLines(noOfLineSkip).withCSVParser(parser).build();
String[] nextLine;
int size = 0;
int chunkSize = 10000;
Class params[] = { Long.class };
Object paramsObj[] = { auditId };
long rowNumber = 0;
Field field[] = type.getDeclaredFields();
while ((nextLine = csv.readNext()) != null) {
rowNumber++;
try {
obj = type.newInstance();
for (Field f : field) {
if(!f.isSynthetic()){
f.setAccessible(true);
Annotation ann[] = f.getDeclaredAnnotations();
CsvBindByPosition csv1 = (CsvBindByPosition) ann[0];
Column c = (Column)ann[1];
try {
if (csv1.position() < nextLine.length) {
if (csv1.required() && (nextLine[csv1.position()] == null
|| nextLine[csv1.position()].trim().isEmpty())) {
String message = "Mandatory field is missing in row: " + rowNumber;
log.info("null value in " + rowNumber + ", " + csv1.position());
System.out.println(message);
}
if (f.getType().equals(String.class)) {
f.set(obj, nextLine[csv1.position()]);
}
if (f.getType().equals(Boolean.class)) {
f.set(obj, nextLine[csv1.position()]);
}
if (f.getType().equals(Integer.class)) {
f.set(obj, Integer.parseInt(nextLine[csv1.position()]));
}
if (f.getType().equals(Long.class)) {
f.set(obj, Long.parseLong(nextLine[csv1.position()]));
}
if (f.getType().equals(Double.class) && null!=nextLine[csv1.position()] && !nextLine[csv1.position()].trim().isEmpty() ) {
f.set(obj, Double.parseDouble(nextLine[csv1.position()]));
}if(f.getType().equals(Double.class) && ((nextLine[csv1.position()]==null) || nextLine[csv1.position()].isEmpty())){
f.set(obj, new Double("0.0"));
}
if (f.getType().equals(Date.class)) {
f.set(obj, nextLine[csv1.position()]);
}
}
} catch (Exception fttEx) {
log.info("Exception when parsing the file: " + fttEx.getMessage());
System.out.println(fttEx.getMessage());
}
}
}
lstCsvContent.add((T) obj);
if (lstCsvContent.size() > chunkSize) {
size = size + lstCsvContent.size();
//write code to save to data base of file system in chunk.
lstCsvContent = null;
lstCsvContent = new ArrayList<>();
}
} catch (Exception ex) {
log.info("Exception: " + ex.getMessage());
}
}
//write code to save list into DB or file system
System.out.println(lstCsvContent);
} catch (Exception ex) {
log.info("Exception:::::::: " + ex.getMessage());
} finally {
try {
if (csv != null) {
csv.close();
}
if (reader != null) {
reader.close();
}
} catch (IOException ioe) {
log.info("Exception when closing the file: " + ioe.getMessage());
}
}
log.info("File Processed successfully: ");
}
}
