在获取多行期间使用spring-jdbctemplate进行csv输出 [英] csv output using spring-jdbctemplate during fetching multiple rows

问题描述

我正在使用spring jdbctemplate与Oracle数据库进行交互. 为了获取给定条件的多行，我想将结果存储在一个以列名作为标题的csv文件中.

I am using spring jdbctemplate to interact with Oracle database. For fetching multiple rows for a given condition, I want to store the result in a csv file having Column names as the header.

我尝试了几种方法-

List<Bean> list = jdbcTemplate.query('query','parameters',customizedrowmapper);

在customrowmapper中，我从结果集中获取行并设置Bean类中的字段.我找不到从bean转换为csv文件的任何简单方法.由于我无权访问bean类代码.

In customizedrowmapper, I am fetching the rows from result set and setting the fields in Bean class. I could not find any easy way to convert from bean to csv file. As I dont have access to bean class code.

我发现的第二种方法是使用opencsv库将ResultSet直接写入csv文件，如下所示.

2nd way I found was to writer ResultSet directly to csv file like below using opencsv library.

CSVWriter wr = new CSVWriter(new FileWriter("Report.csv"), ','); wr.writeAll(rs, true); wr.flush(); wr.close();

CSVWriter wr = new CSVWriter(new FileWriter("Report.csv"), ','); wr.writeAll(rs, true); wr.flush(); wr.close();

这可以正常工作，但是在写入csv时跳过了一行.我尝试了几次查询.手动地，我可以在结果中看到4行，但是在CSV中，它仅存储三行.有人遇到过类似的问题吗?

This is working okay, but it is skipping one row while writing to csv. I tried few queries. Manually, I can see 4 rows in the result, but in the CSV, it stores only three rows. Anyone faced a similar issue?

或者通过其他任何方法，我们也可以实现相同的目的，而无需从ResultSet中手动获取每条记录，并使其成为逗号分隔的字符串并存储在文件中.

Or any other way we can achieve the same, without manually getting each record from ResultSet and making it comma-separated string and storing in file.

推荐答案

此代码保存查询结果并将其作为csv文件返回(您可以使用端点下载它).但是，如果您不需要将其返回给控制器中的用户，则可以轻松地将所有数据转发到FileOutputStream

This code saving query result and returning it as csv file(you can download it using endpoint). But if you do not need to return it to user in controller its easy to forward all data to FileOutputStream

package io.intertec.sudor.web.rest.report;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

@RestController
@RequestMapping("/api/report")
public class UserReportController {
    private JdbcTemplate jdbcTemplate;

    public UserReportController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    private static String ALL_USERS_QUERY = "select * from users";

    @GetMapping("/all-users")
    public void allUsers(HttpServletResponse response) throws IOException {
        response.setContentType("text/csv");
        jdbcTemplate.query(ALL_USERS_QUERY, new StreamingCsvResultSetExtractor(response.getOutputStream()));
    }


    class StreamingCsvResultSetExtractor
        implements ResultSetExtractor<Void> {

        private char DELIMITER = ',';

        private final OutputStream os;


        public StreamingCsvResultSetExtractor(final OutputStream os) {
            this.os = os;
        }

        @Override
        public Void extractData(final ResultSet rs) {
            try (PrintWriter pw = new PrintWriter(os, true)) {
                final ResultSetMetaData rsmd = rs.getMetaData();
                final int columnCount = rsmd.getColumnCount();
                writeHeader(rsmd, columnCount, pw);
                while (rs.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        final Object value = rs.getObject(i);
                        String strValue = value == null ? "" : value.toString();
                        pw.write(strValue.contains(",") ? "\"" + strValue+ "\"" : strValue);
                        if (i != columnCount) {
                            pw.append(DELIMITER);
                        }
                    }
                    pw.println();
                }
                pw.flush();
            } catch (final SQLException e) {
                throw new RuntimeException(e);
            }
            return null;
        }

        private void writeHeader(final ResultSetMetaData rsmd,
                                 final int columnCount, final PrintWriter pw) throws SQLException {
            for (int i = 1; i <= columnCount; i++) {
                pw.write(rsmd.getColumnName(i));
                if (i != columnCount) {
                    pw.append(DELIMITER);
                }
            }
            pw.println();
        }
    }

}

这篇关于在获取多行期间使用spring-jdbctemplate进行csv输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！