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

Environment: Java8


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.


  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.


  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.
  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, {
      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 =; cur != null; prev = cur, cur = {
            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);
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 ( ? 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),;
zos.write(???);//What to do?

Recommended Posts

To create a Zip file while grouping database search results in Java
Try to create a bulletin board in Java
How to ZIP a JAVA CSV file and manage it in a Byte array
How to create a Java environment in just 3 seconds
I tried to create a Clova skill in Java
How to create a data URI (base64) in Java
How to convert a file to a byte array in Java
[Java] Create a temporary file
Create a Java Servlet and JSP WAR file to deploy to Apache Tomcat 9 in Gradle
Create a method to return the tax rate in Java
I want to create a Parquet file even in Ruby
Create a database in a production environment
Unzip the zip file in Java
[Java] How to create a folder
Log output to file in Java
How to create a new Gradle + Java + Jar project in Intellij 2016.03
Create a TODO app in Java 7 Create Header
Sample to unzip gz file in Java
The story of forgetting to close a file in Java and failing
How to create a database for H2 Database anywhere
Two ways to start a thread in Java + @
Read a string in a PDF file with Java
Create a CSR with extended information in Java
[Rails] rails new to create a database with PostgreSQL
[Java] Create something like a product search API
[Android / Java] Operate a local database in Room
Code to escape a JSON string in Java
I wanted to make (a == 1 && a == 2 && a == 3) true in Java
Let's create a super-simple web framework in Java
A story about trying to operate JAVA File
How to create a theme in Liferay 7 / DXP
A bat file that uses Java in windows
[Note] Java: Create a simple project while learning how the configuration file works.
How to easily create a pull-down in Rails
Let's create a versatile file storage (?) Operation library by abstracting file storage / acquisition in Java
I tried to create Alexa skill in Java
How to save a file with the specified extension under the directory specified in Java to the list
[Java] Let's create a mod for Minecraft 1.14.4 [0. Basic file]
<java> Read Zip file and convert directly to string
3. Create a database to access from the web module
How to jump from Eclipse Java to a SQL file
How to create a JDBC URL (Oracle Database, Thin)
How to create a Spring Boot project in IntelliJ
[Java] Let's create a mod for Minecraft 1.16.1 [Basic file]
Java / Twitter clone / task management system (1) Create a database
I tried to make a login function in Java
How to write a date comparison search in Rails
How to convert A to a and a to A using AND and OR in Java
Try to solve a restricted FizzBuzz problem in Java
Java11: Run Java code in a single file as is
Gzip-compress byte array in Java and output to file
[Java] Create a filter
Create JSON in Java
How to read your own YAML file (*****. Yml) in Java
I tried to create a java8 development environment with Chocolatey
How to create a lightweight container image for Java apps
Create a jar file that can be executed in Gradle
Convert a Java byte array to a string in hexadecimal notation
[Beginner] I made a program to sell cakes in Java
How to create a placeholder part to use in the IN clause
Activate Excel file A1 cell of each sheet in Java