To create a Zip file while grouping database search results in Java

Environment: Java8

Description

ZipEntry is added for each key based on a sorted 2D table with repeating values by a simple SELECT statement, the contents of the entry are as a text file, and the contents of the text file are also grouped by indentation. Create a Zip file.

output

1. Create a Zip file entry for each key change.
2. Each entry contains a text file.
3. Group the contents of the text file as well. Group by indent.

input

1. The SELECT statement is a flat two-dimensional table with repeating same values.

The point

1. Use ByteArrayOutputStream as a temporary area for variable length text editing.
2. Use the Formatter (OutputStream, String) constructor to handle character code conversion and formatting at once.
3. After formatting the read record, call Formatter # flush () to transfer the edited text content to the buffer.
4. After transferring the contents of the buffer to ZipOutputStream, call ByteArrayOutputStream # reset () to return to the beginning of the buffer.
5. Do not close.

bonus

1. Create a simple Row class to represent rows, loop with for statements, and access column values by name. This is for clarity.
2. eq (cur.a, prev) even in the first process where the previous value does not exist as for (Row prev = new Row (null) so that the comparison between the current value (cur) and the previous value (prev) always holds. Make .a) valid.
3. Prepare a convenience method that allows nulls for character string comparison and can compare nulls with character strings. This is to prevent NullPointerException and IllegalArgumentException from occurring in null.equals (s) and s.equals (null).
4. The initial value of prev is consistent with the actual data. This time, it is assumed that there is no null as the data in the table. This should be fine in most cases. The column values for grouping should never be null.

Source code

Charset cs = Charset.defaultCharset();
try (ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(file), cs)) {
  try (ByteArrayOutputStream buffer = new ByteArrayOutputStream(1024)) {
    try (Formatter formatter = new Formatter(buffer, cs.name())) {
      try (PreparedStatement select = con.prepareStatement("SELECT DISTINCT a, b, c, d, e, f FROM foo ORDER BY a, b, c desc, d, e, f")) {
        try (ResultSet rs = select.executeQuery()) {
          for (Row prev = new Row(null), cur = Row.next(rs); cur != null; prev = cur, cur = Row.next(rs)) {
            if (!eq(cur.a, prev.a) || !eq(cur.b, prev.b)) {
              zos.putNextEntry(new ZipEntry(String.format("%s-%s.txt", cur.a, cur.b)));
              formatter.format("%s%n", cur.c);
              formatter.format("  %s%n", cur.d);
            } else if (!eq(cur.c, prev.c)) {
              formatter.format("%s%n", cur.c);
              formatter.format("  %s%n", cur.d);
            } else if (!eq(cur.d, prev.d)) {
              formatter.format("  %s%n", cur.d);
            }
            formatter.format("    %s: %s%n", cur.e, cur.f);
            formatter.flush();
            zos.write(buffer.toByteArray());
            buffer.reset();
          }
        }
      }
    }
  }
}

Convenience method that allows comparison with null

public static boolean eq(String l, String r) {
  if (l == r) {
    return true;
  } else if (l != null && r == null) {
    return false;
  } else if (l == null && r != null) {
    return false;
  } else {
    return l.equals(r);
  }
}

A simple mechanism for looping ResultSet

public class Row {
  public static Row next(ResultSet rs) throws SQLException {
    return (rs.next()) ? new Row(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)) : null;
  }

  String a;
  String b;
  String c;
  String d;
  String e;
  String f;
  
  public Row(String v) {
    this(v, v, v, v, v, v);
  }

  public Row(String a, String b, String c, String d, String e, String f) {
    this.a = a;
    this.b = b;
    this.c = c;
    this.d = d;
    this.e = e;
    this.f = f;
  }
}

important point

Both buffer and formatter need to be visible to the code inside them. If you write as below, you will be in trouble because you cannot retrieve the data obediently because of ZipOutputStream # write (byte []). There is a way to do it, but then you don't have to write it like this.

Formatter formatter = new Formatter(new ByteArrayOutputStream(1024), cs.name());
...
zos.write(???);//What to do?