Converting a JDBC ResultSet to CSV in Java

1. Introduction

When working with Java applications that interact with databases, we often need to export the query results into a format that can be easily consumed or shared. One common format for this purpose is CSV(Comma-Separated Values). To achieve this, we can convert a JDBC ResultSet (the data structure used to hold the results of a database query) into a CSV file.

In this article, we’ll explore two approaches to converting ResultSet into CSV format.

2. Setup

For testing purposes, we’ll use the Employees table which has the below schema:

CREATE TABLE EMPLOYEES (
    id SERIAL PRIMARY KEY ,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

3. Using Custom Logic

In this approach, first, we’re querying the Employees table for the records:

As we can see above, the Employees table records include special characters. We’re then iterating over ResultSet and converting each record to CSV:

List<String> toCsv(Connection connection) throws SQLException {
    List<String> csvRecords = new ArrayList<>();
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM employees");
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
        String eachRecord = formatForCsv(resultSet.getString("first_name")) + "," + 
          formatForCsv(resultSet.getString("last_name")) + "," + 
          "\"" + resultSet.getDouble("salary") + "\"";
        csvRecords.add(eachRecord);
    }
    return csvRecords;
}

For each record, we’re reading each column such as first_name, last_name, etc as a string. We created the formatForCSV() helper method which escapes newline (\n) with (\\n), carriage returns (\r) with (\\r), and double quotes(“) with (“”):

String formatForCsv(String value) {
    return "\"" + value
      .replace("\n", "\\n")
      .replace("\r", "\\r")
      .replace("\"", "\"\"")
      + "\"";
}

If we execute our logic, we’ll get the ResultSet converted CSV result:

We can write unit tests to verify if our logic works as expected:

@Test
void givenEmployeeRecordsInEmployeeTable_whenResultSetToCSVInvoked_thenReturnsCSV() throws SQLException, IOException {
    insertRecords();
    ResultSetToCSV logic = new ResultSetToCSV();
    List<String> csvRecords = logic.toCsv(connection);
    Files.write(Paths.get("/Users/surajmishra/Documents/work-space/employee.csv"), csvRecords);

    assertThat(csvRecords.size()).isEqualTo(3);

    for (int i = 1; i <= 2; i++) {
        assertThat(csvRecords.get(i - 1))
          .isEqualTo("\"first" + i + "\"," + "\"last" + i + "\"," + "\"" + String.format("%.1f", 100.00 * i) + "\"");
    }

    assertThat(csvRecords.get(2))
      .isEqualTo("\"\"\"first\\nfirst1\\nfirst2!\"\"1\"," + "\"\"\"last!\\nlast1!\"\"1\"," + "\"100.0\"");
}

4. Using Third-Party Dependency

There are different open-source libraries available that can help us convert ResultSet to CSV. One of the popular libraries is OpenCSV.

We can add it in the pom.xml file:

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.9</version>
</dependency>

In the below logic, we’re reading all the Employees records from the database as ResultSet, and then using OpenCSV, converting them to CSV:

String toCsvWithOpenCsv(Connection connection) throws SQLException {
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM employees");
    ResultSet resultSet = preparedStatement.executeQuery();
    StringWriter stringWriter = new StringWriter();
    CSVWriter csvWriter = new CSVWriter(stringWriter,
      CSVWriter.DEFAULT_SEPARATOR,
      CSVWriter.DEFAULT_QUOTE_CHARACTER,  // Default quote character is double quote
      CSVWriter.DEFAULT_ESCAPE_CHARACTER, // Default escape character is double quote
      CSVWriter.DEFAULT_LINE_END);

    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();
    String[] row = new String[columnCount-1];

    while (resultSet.next()) {
        row[0] = resultSet.getString("first_name")
          .replace("\n", "\\n")
          .replace("\r", "\\r");

        row[1] = resultSet.getString("last_name")
          .replace("\n", "\\n")
          .replace("\r", "\\r");

        row[2] = String.valueOf(resultSet.getDouble("salary"));

        csvWriter.writeNext(row);
    }
    
    return stringWriter.toString();
}

In the above logic, we also escape each row for any special character such as newline, carriage returns, or double quotes in the column value. We use csvWriter to write a row array for the CSV. Then to get String, we convert stringWriter, which contains CSV data, to String using the toString() method.

We can write unit tests to verify if our logic works as expected:

@Test
void givenEmployeeRecordsInEmployeeTable_whenResultSetToCSVWithOpenCsvInvoked_thenReturnsCSV() throws SQLException {
    insertRecords();
    ResultSetToCSV logic = new ResultSetToCSV();
    String csvRecords = logic.toCsvWithOpenCsv(connection);
    String[] split = csvRecords.split("\n");
    assertThat(split.length).isEqualTo(3);
    for (int i = 1; i <= 2; i++) {
        assertThat(split[i - 1])
          .isEqualTo("\"first" + i + "\"," + "\"last" + i + "\"," + "\"" + String.format("%.1f", 100.00 * i) + "\"");
    }

    assertThat(split[2])
      .isEqualTo("\"\"\"first\\nfirst1\\nfirst2!\"\"1\"," + "\"\"\"last!\\nlast1!\"\"1\"," + "\"100.0\"");
}

5. Conclusion

In this tutorial, we learned two options using OpenCSV and custom logic to convert JDBC ResultSet to CSV. While we can always use third-party dependency such as OpenCSV to perform the conversion, writing our custom logic isn’t that bad.