如何从Spring JdbcTemplate获取CSV列表？无需指定列名提取数据

Hey there! Let's break down your two JdbcTemplate questions with practical, no-fuss solutions:

To get your query output as a list of valid CSV rows (including a dynamic header), you can use a ResultSetExtractor to iterate through the result set, build rows on the fly, and handle edge cases like values containing commas or quotes. Here's a ready-to-use implementation:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CsvResultGenerator {
    private final JdbcTemplate jdbcTemplate;

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

    public List<String> getQueryResultsAsCsv(String sql) {
        return jdbcTemplate.query(sql, (ResultSetExtractor<List<String>>) rs -> {
            List<String> csvRows = new ArrayList<>();
            int columnCount = rs.getMetaData().getColumnCount();

            // Build CSV header from auto-detected column names
            StringBuilder header = new StringBuilder();
            for (int i = 1; i <= columnCount; i++) {
                if (i > 1) header.append(",");
                header.append(escapeCsvValue(rs.getMetaData().getColumnName(i)));
            }
            csvRows.add(header.toString());

            // Build data rows by iterating columns via index
            while (rs.next()) {
                StringBuilder row = new StringBuilder();
                for (int i = 1; i <= columnCount; i++) {
                    if (i > 1) row.append(",");
                    row.append(escapeCsvValue(rs.getString(i)));
                }
                csvRows.add(row.toString());
            }
            return csvRows;
        });
    }

    // Helper to escape values that would break CSV formatting
    private String escapeCsvValue(String value) {
        if (value == null) return "";
        boolean needsQuotes = value.contains(",") || value.contains("\"") || value.contains("\n");
        if (!needsQuotes) return value;
        return "\"" + value.replace("\"", "\"\"") + "\"";
    }
}

How it works: #

• We use ResultSetMetaData to auto-detect column names for the header, so no hardcoding required.
• Columns are accessed via their index (not names), making this flexible for any query.
• The escapeCsvValue method ensures your CSV stays valid even if values contain special characters.

To mimic the "run SQL and see all results" behavior of tools like pgAdmin or MySQL Workbench, you can use RowCallbackHandler (for immediate output) or a ResultSetExtractor (to collect results first), accessing columns via their index instead of names.

Option 1: Print results directly to console #

This prints each row with columns separated by tabs (adjust the separator to commas or spaces if you prefer):

public void printQueryResults(String sql) {
    jdbcTemplate.query(sql, (RowCallbackHandler) rs -> {
        int columnCount = rs.getMetaData().getColumnCount();
        StringBuilder rowOutput = new StringBuilder();
        for (int i = 1; i <= columnCount; i++) {
            if (i > 1) rowOutput.append("\t");
            // Use getObject to handle all data types (String, Integer, etc.)
            rowOutput.append(rs.getObject(i) != null ? rs.getObject(i) : "NULL");
        }
        System.out.println(rowOutput.toString());
    });
}

Option 2: Collect results into a generic list #

If you need to process results later, collect them into a List<Object[]> where each array represents a row:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public List<Object[]> getGenericQueryResults(String sql) {
    return jdbcTemplate.query(sql, (ResultSetExtractor<List<Object[]>>) rs -> {
        List<Object[]> results = new ArrayList<>();
        int columnCount = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            Object[] row = new Object[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                row[i-1] = rs.getObject(i);
            }
            results.add(row);
        }
        return results;
    });
}

Key notes: #

• rs.getObject(i) automatically adapts to the column's data type, so you don't need to use getString or getInt explicitly.
• ResultSetMetaData.getColumnCount() tells us how many columns to process, so this works for any query—no hardcoded column names needed.

内容的提问来源于stack exchange，提问作者membersound