上海大型网站开发公司,产品推广公司,六安做网站,wordpress dux主题需求#xff1a;客户上传CSV文档#xff0c;要求CSV文档内容查重/插入/更新相关数据。 框架#xff1a;jdbcTemplate、commons-io、 DB#xff1a;oracle
相关依赖#xff1a; 这里本来打算用的2.11.0#xff0c;无奈正式项目那边用老版本1.3.1#xff0c;新版本对类型…需求客户上传CSV文档要求CSV文档内容查重/插入/更新相关数据。 框架jdbcTemplate、commons-io、 DBoracle
相关依赖 这里本来打算用的2.11.0无奈正式项目那边用老版本1.3.1新版本对类型支持和转换好一点。不过无伤大雅。 dependencygroupIdcommons-io/groupIdartifactIdcommons-io/artifactIdversion1.3.1/version/dependencyCSV文档格式
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 ResponseEntityBaseResponse? 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 listListString lines IOUtils.readLines(multipartFile.getInputStream(), UTF-8);ListListString 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 ConnectionCallbackObject() {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;完美
