john / h2tocsv

///usr/bin/env jbang "$0" "$@" ; exit $? // // This is a JBang script. You need JBang to run this script. // // To learn more go to https://www.jbang.dev and install JBang. // // Run this script: // ./h2tocsv.sh -d <database.mv.db> -t <table> [-f <output file>] [-u db user] [-p db password] // //DEPS info.picocli:picocli:4.5.0 com.h2database:h2:1.4.197 import static java.util.Optional.ofNullable; import java.io.File; import java.nio.charset.StandardCharsets; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.concurrent.Callable; import org.h2.Driver; import org.h2.tools.Csv; import org.h2.util.JdbcUtils; import picocli.CommandLine; import picocli.CommandLine.Command; import picocli.CommandLine.Option; /** * Converts a table in a H2 database file to CSV * * @author John Ahlroos / Devsoap 2021 */ @Command(name = "h2tocsv", mixinStandardHelpOptions = true, version = "0.1", description = "H2 database to CSV converter") class h2tocsv implements Callable<Integer> { @Option(names = { "--database", "-d" }, required = true, description = "H2 database to import") private File database; @Option(names = { "--table", "-t" }, required = true, description = "The database table to export") private String table; @Option(names = { "--user", "-u" }, defaultValue = "sa", description = "Database user name") private String user; @Option(names = { "--password", "-p" }, defaultValue = "", description = "Database password") private String password; @Option(names = { "--file", "-f" }, description = "CSV file path to output") private String csvFile; public static void main(final String... args) { Driver.load(); final int exitCode = new CommandLine(new h2tocsv()).execute(args); System.exit(exitCode); } @Override public Integer call() throws Exception { csvFile = ofNullable(csvFile).orElse( String.format("%s-%s.csv", database.getName().substring(0, database.getName().indexOf(".")), table)); if (!database.exists()) { System.err.println("Database file " + database.getCanonicalPath() + " does not exist!"); return -1; } final String path = database.getCanonicalPath().replace(".mv.db", ""); final String url = "jdbc:h2:file:" + path + ";DB_CLOSE_DELAY=-1"; Connection conn = null; try { conn = DriverManager.getConnection(url, user, password); try (Statement statement = conn.createStatement()) { final String sql = String.format("select * from %s", table); System.out.println(String.format("Exporting table %s...", table)); final ResultSet result = statement.executeQuery(sql); System.out.println("Writing database table to file..."); new Csv().write(csvFile, result, StandardCharsets.UTF_8.toString()); } } catch (SQLException e) { System.err.println("Failed to read database, error message was: " + e.getMessage()); return e.getErrorCode(); } finally { ofNullable(conn).ifPresent(JdbcUtils::closeSilently); } System.out.println("Done. CSV file exported to " + csvFile); return 0; } }