How to execute a sql gzipped script for postgresql database in java using jdbc API?

Question

I'd like to execute a sql script gzipped to a postgresql database using java jdbc API.

I know how to do this in H2 database using the sql run script statement.

Answer 1

I manage to do this for any database using this code

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.zip.GZIPInputStream;

/**
 * Created on 09/01/16.
 *
 * @author Tony Chemit - chemit@codelutin.com
 */
public class RunSqlScript {


    public void executeSqlScript(Connection connection, boolean gzip, int batchSize, byte... content) throws SQLException, IOException {

        boolean autoCommit = connection.getAutoCommit();

        connection.setAutoCommit(false);
        try (BufferedReader reader = createReader(gzip, content)) {

            try (Statement statement = connection.createStatement()) {

                int currentBatchSize = 0;
                String command = null;
                String line;
                while ((line = reader.readLine()) != null) {

                    String trimLine = line.trim();

                    if (trimLine.startsWith("--")) {
                        continue;
                    }

                    command = command == null ? line : command + ' ' + line;
                    if (trimLine.endsWith(";")) {
                        statement.addBatch(command);
                        batchSize++;
                        command = null;
                        if (currentBatchSize % batchSize == 0) {
                            flushStatement(statement);
                        }
                    }

                }

                flushStatement(statement);

            }

        } finally {
            connection.setAutoCommit(autoCommit);
        }

    }

    protected BufferedReader createReader(boolean gzip, byte... content) throws IOException {
        return new BufferedReader(new InputStreamReader(new BufferedInputStream(gzip ? new GZIPInputStream(new ByteArrayInputStream(content)) : new ByteArrayInputStream(content))));

    }

    protected void flushStatement(Statement statement) throws SQLException {
        statement.executeBatch();
        statement.clearBatch();
    }
}