ResultSet to CSV format string

Question

I am trying to convert a ResultSet object to a String object in CSV format. I've tried using OpenCV to convert it into CSV file but I need to have it as a string. There is an option to first convert it into a file and then convert the data into a string and then delete the file but that would be an extra overhead which I can't have.

Is there a way to achieve it, I tried searching but haven't anything so far.

Answer 1

Apache Commons CSV

Alternatively, you can use the CSVPrinter from Apache Commons CSV.

CSVPrinter::printRecords( ResultSet )

The method CSVPrinter::printRecords takes a ResultSet argument.

See the key line csvPrinter.printRecords(resultSet); in the following example code.

package org.myorg;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class QueryToCSV {

    public static void main(String[] args) throws IOException  {

        if ( args.length < 2)
            throw new IllegalArgumentException("Usage: QueryToCSV.jar <JDBCConnectionURL> <SQLForQuery> -> output to STDOUT and STDERR");

        // Create a variable for the connection string.
        String connectionUrl = args[0];
        String sqlQuery = args[1];
        try ( Connection conn = DriverManager.getConnection(connectionUrl); ) { 

            BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(System.out));

            try (Statement st = conn.createStatement();) {
                ResultSet resultSet = st.executeQuery(sqlQuery);
                CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(resultSet));
                csvPrinter.printRecords(resultSet);
                csvPrinter.close();
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }
        catch (IllegalArgumentException ie) {
            System.err.println(ie.getMessage());
            System.exit(1);
        }
    }
}

Answer 2

Do not write your own method, CSV is not just strings concatenated by commas. There are several pifails regarding quotas, their escaping etc. Use dedicated libraries. Personally I prefer univocity-parsers for "broken" csv files but if you deal with ResultSet you may use opencsv library.

    StringWriter stringWriter = new StringWriter();
    CSVWriter csvWriter = new CSVWriter(stringWriter);
    csvWriter.writeAll(resultSet, true); // including column names
    String result = stringWriter.toString();