Java: Streaming a JDBC ResultSet as CSV

streaming it in CSV format instead. Streaming allows you to transfer the data without having to load it all into the server’s memory.

    
        +---------+-----+
| Name    | Age |
+---------+-----+
| Alice   |  20 |
| Bob     |  35 |
| Charles |  50 |
+---------+-----+
    

The corresponding CSV is:

    
        name,age
Alice,20
Bob,35
Charles,50
    

The following class (also available in my GitHub Repository) can be used to convert the ResultSet to CSV. Note that this class implements Spring’s ResultSetExtractor, which can be used by a JdbcTemplate to extract results from a ResultSet.

    
        /**
 * Streams a ResultSet as CSV.
 */
public class StreamingCsvResultSetExtractor
                         implements ResultSetExtractor {

  private static char DELIMITER = ',';

  private final OutputStream os;

  /**
   * @param os the OutputStream to stream the CSV to
   */
  public StreamingCsvResultSetExtractor(final OutputStream os) {
    this.os = os;
  }

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

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

To use this in a web service with JAX-RS:

    
        import javax.ws.rs.core.StreamingOutput;

@GET
@Path("runQuery")
@Produces("text/csv")
public StreamingOutput runQuery() {
  return new StreamingOutput() {
    @Override
    public void write(final OutputStream os)
        throws IOException, WebApplicationException {
      jdbcTemplate.query("select name, age from person",
                   new StreamingCsvResultSetExtractor(os));
    }
  };
}
    





javacodegeeks is optimized for learning.© javacodegeeks .
All Right Reserved and you agree to have read and accepted our term and condition