当前位置：首页 > news >正文

从CSV到数据库(简易)

news 来源：原创 2024/7/3 2:38:43

需求：客户上传CSV文档，要求CSV文档内容查重/插入/更新相关数据。
框架：jdbcTemplate、commons-io、
DB：oracle

相关依赖：
这里本来打算用的2.11.0，无奈正式项目那边用老版本1.3.1，新版本对类型支持和转换好一点。不过无伤大雅。

        <dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>1.3.1</version></dependency>

CSV文档格式：

Xxx Code,Yerial,OP
600001,2024082400305, OP20240818_XDFD
600001,2024082400306, OP20240818_XDFD
600001,2024082400307, OP20240818_XDFD
600001,2024082400308, OP20240818_XDFD
600001,2024082400309, OP20240818_XDFD
600001,2024082400310, OP20240818_XDFD
600001,2024082400311, OP20240818_XDFD
600001,2024082400312, OP20240818_XDFD
600001,2024082400313, OP20240818_XDFD
600001,2024082400314, OP20240818_XDFD
600001,2024082400315, OP20240818_XDFD
600001,2024082400316, OP20240818_XDFD
600001,2024082400317, OP20240818_XDFD
600001,2024082400318, OP20240818_XDFD
600001,2024082400319, OP20240818_XDFD
600001,2024082400320, OP20240818_XDFD
600001,2024082400321, OP20240818_XDFD
600001,2024082400322, OP20240818_XDFD
600001,2024082400323, OP20240818_XDFD
600001,2024082400324, OP20240818_XDFD
600001,2024082400325, OP20240818_XDFD

接口：

用MultipartFile接受CSV文件

    @PostMapping("/import")public ResponseEntity<BaseResponse<?>> importCSV(@RequestBody MultipartFile files) {xxxService.importSerial(files);return new ResponseEntity<>(new BaseResponse<>(), HttpStatus.OK);}

前段需要在Request-Body中以form-data的形式上传文档
在这里插入图片描述

CSV解析

简单转成Json

    private String convertCsvToJson(MultipartFile multipartFile) throws IOException {// read csv as listList<String> lines = IOUtils.readLines(multipartFile.getInputStream(), "UTF-8");List<List<String>> data = lines.stream().skip(1) // skip label line.filter(line -> !line.trim().isEmpty()) // filtering empty line.map(line -> Arrays.asList(line.split(","))).collect(Collectors.toList());return objectMapper.writeValueAsString(data);}

转换后是这样的：
注意这是print出来的json对象，本来应该是json字符串。

[["600001","2024082400305","OP20240818_XDFD"],["600001","2024082400306","OP20240818_XDFD"],["600001","2024082400307","OP20240818_XDFD"],["600001","2024082400308","OP20240818_XDFD"],["600001","2024082400309","OP20240818_XDFD"],["600001","2024082400310","OP20240818_XDFD"],["600001","2024082400311","OP20240818_XDFD"],["600001","2024082400312","OP20240818_XDFD"],["600001","2024082400313","OP20240818_XDFD"],["600001","2024082400314","OP20240818_XDFD"],["600001","2024082400315","OP20240818_XDFD"],["600001","2024082400316","OP20240818_XDFD"],["600001","2024082400317","OP20240818_XDFD"],["600001","2024082400318","OP20240818_XDFD"],["600001","2024082400319","OP20240818_XDFD"],["600001","2024082400320","OP20240818_XDFD"],["600001","2024082400321","OP20240818_XDFD"],["600001","2024082400322","OP20240818_XDFD"],["600001","2024082400323","OP20240818_XDFD"],["600001","2024082400324","OP20240818_XDFD"],["600001","2024082400325","OP20240818_XDFD"]
]

以clob参数的形式传到oracle存储过程中处理

    public void import(MultipartFile files) {final int[] status = new int[1];Object result = jdbcTemplate.execute(new ConnectionCallback<Object>() {@Overridepublic Object doInConnection(Connection con) throws SQLException, DataAccessException {CallableStatement cs = con.prepareCall("{call TEST_PACKAGE.pro_add_csv_data(?, ?)}");Clob clob = con.createClob(); // 创建一个Clob对象try {String s = convertCsvToJson(files); // csv 转 json字符串clob.setString(1, s); // 把json字符串封装进clob对象中cs.setClob(1, clob);  // 入参cs.registerOutParameter(2, Types.INTEGER); // 出参cs.execute();status[0] = cs.getInt(2); // 取结果} catch (IOException e) {throw new RuntimeException("Import failed.");}return null;}});}

存储过程

PROCEDURE pro_add_csv_data(v_data_list IN CLOB,v_status OUT NUMBER) 
AS v_code VARCHAR2(10);  v_yerial VARCHAR2(20);v_op VARCHAR2(20);v_cur SYS_REFCURSOR;EXC_EXIST EXCEPTION;v_count NUMBER;v_ref VARCHAR2(30) := TO_CHAR(SYSDATE, 'YYYYMMDDHH24MI');
BEGIN    -- 解析成表OPEN v_cur FOR    SELECT jt.*      FROM (    SELECT j.*    FROM JSON_TABLE(    v_data_list , '$[*]' COLUMNS (code VARCHAR2(10) PATH '$[0]',        yerial VARCHAR2(20) PATH '$[1]',        op VARCHAR2(20) PATH '$[2]'        )              ) j    ) jt;  -- 遍历插入LOOP FETCH v_cur INTO v_code, v_yerial, v_op;  EXIT WHEN v_cur%NOTFOUND;INSERT INTO table_oneVALUES( v_code,v_yerial,v_op);END LOOP;COMMIT;CLOSE v_cur;v_status := 0;
EXCEPTION  WHEN EXC_EXIST THEN ROLLBACK;v_status:=2;WHEN OTHERS THENROLLBACK;v_status:=1;
END pro_add_csv_data;