在获取多行时使用spring-jdbctemplate的csv输出

Question

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

我试过几种方法-

或者我们可以通过任何其他方法来实现同样的目的，而不需要手动从ResultSet中获取每个记录并将其设置为逗号分隔的字符串并存储在文件中。

Answer 1

此代码保存查询结果并将其返回为csv文件（您可以使用endpoint下载）。但是，如果您不需要在控制器中返回给用户，则很容易将所有数据转发到FileOutputStream

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();
        }
    }

}