解析入库csv格式数据

实现解析入库CSV格式文件的接口任务

1.对数据格式的掌握：

1.数据有效字段的掌握，主要是对其中字段的转变，实现数据的实时入库（MYSQL数据库）。提供解析入库的接口。
2.数据量和数据内容的掌握了解。
3.有效数据的提取。
4.对csv格式文件的认识（文本型的数据表格）。

2.JAVA实现代码编写

​ 2.1选择SpringBoot框架

​ 2.2 配置相关的配置类

​ 2.3完成读取和入库的类

​ 2.4完成接口中全部的类

//实体类entity
public class RainGagueData implements Serializable {
    private static final long serialVersionUID = -25691313024944937L; 
    private String id;
    private String datatime;
    private String temp;
    private String event;  
    private String maxTemp;
    private String sumEvent;
    private String coupler;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getDatatime() {
        return datatime;
    }
    public void setDatatime(String datatime) {
        this.datatime = datatime;
    }
    public String getTemp() {
        return temp;
    }
    public void setTemp(String temp) {
        this.temp = temp;
    }
    public String getEvent() {
        return event;
    }
    public void setEvent(String event) {
        this.event = event;
    }
    public String getMaxTemp() {
        return maxTemp;
    }
    public void setMaxTemp(String maxTemp) {
        this.maxTemp = maxTemp;
    }
    public String getSumEvent() {
        return sumEvent;
    }
    public void setSumEvent(String sumEvent) {
        this.sumEvent = sumEvent;
    }
    public String getCoupler() {
        return coupler;
    }
    public void setCoupler(String coupler) {
        this.coupler = coupler;
    }
}

//读取csv格式的数据
public class DecodeRainGaugeData {
  //读取的方法readCsvByBufferedReader
    public static List<Object> readCsvByBufferedReader(String filePath, String outPath) {
        File csv = new File(filePath);
        csv.setReadable(true);
        csv.setWritable(true);
        InputStreamReader isr = null;
        BufferedReader br = null;
        try {
            isr = new InputStreamReader(new FileInputStream(csv), "UTF-8");
            br = new BufferedReader(isr);
        } catch (Exception e) {
            e.printStackTrace();
        }
        String line = "";//字符串
        ArrayList<Object> records = new ArrayList<>();//对象集合
        try {
            while ((line = br.readLine()) != null) {
                System.out.println(line);
                records.add(line);
            }
            System.out.println("csv表格读取行数：" + records.size());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return records;
    }
}

//入库的接口类
public interface RainGaugeService {
    void insertRainGaugeData(String filePath , String outPath);
}

//入口接口及实现类
@Slf4j
@Service
public class RainGaugeServiceImpl implements RainGaugeService {
    @Autowired
    private RainGaugeMapper mapper;
    @Override
    public void insertRainGaugeData(String filePath, String outPath){
        DecodeRainGaugeData data = new DecodeRainGaugeData();
        List<Object> ll = data.readCsvByBufferedReader(filePath,outPath);
        ll.subList(0, 2).clear();
//        System.out.println(ll);
        ll.forEach(o ->{
            String[] arr = o.toString().split(",");
            System.out.println(arr);
            RainGagueData rain = new RainGagueData();
            rain.setId(null);
            rain.setDatatime(null);
            rain.setTemp(null);
            rain.setEvent(null);
            rain.setMaxTemp(null);
            rain.setSumEvent(null);
            rain.setCoupler(null);
            for (int i=0;i<arr.length;i++){
                switch (i){
                    case 0:
                        rain.setId(arr[i]);
                        break;
                    case 1:
                        rain.setDatatime(arr[i]);
                        break;
                    case 2:
                        rain.setTemp(arr[i]);
                        break;
                    case 3:
                        rain.setEvent(arr[i]);
                        break;
                    case 4:
                        rain.setMaxTemp(arr[i]);
                        break;
                    case 5:
                        rain.setSumEvent(arr[i]);
                        break;
                    case 6:
                        rain.setCoupler(arr[i]);
                        break;
                }
            }
            mapper.insertRainGaugeData(rain);
        });
    }
}

//控制层
@RestController
public class RainGaugeController {
    @Autowired
    private RainGaugeService rainGaugeService;
    @RequestMapping("/RainGauge")
    public AjaxResult list(String filePath, String outPath) throws Exception{
        File fileDir = new File(filePath);
        if(fileDir.exists() && fileDir.isDirectory()){
            for (File item:fileDir.listFiles()) {
                rainGaugeService.insertRainGaugeData(String.valueOf(item),outPath);
            }
        }else{
            rainGaugeService.insertRainGaugeData(filePath,outPath);
        }
        return new AjaxResult(200,"入库成功");
    }
}

//数据库操作语句
<?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="com.dao.RainGaugeMapper">
    <insert id="insertRainGaugeData" parameterType="com.entity.RainGagueData">
        INSERT INTO rain_gague_data_copy1 (
        `id`,
        `datatime`,
        `temp`,
        `event`,
        `max_temp`,
        `sum_event`,
        `coupler`
        )
        VALUES
            (
            #{id},
            #{datatime},
            #{temp},
            #{event},
            #{maxTemp},
            #{sumEvent},
            #{coupler}
            )
    </insert>
</mapper>