john revised this gist . Go to revision
1 file changed, 94 insertions
h2tocsv.sh(file created)
| @@ -0,0 +1,94 @@ | |||
| 1 | + | ///usr/bin/env jbang "$0" "$@" ; exit $? | |
| 2 | + | // | |
| 3 | + | // This is a JBang script. You need JBang to run this script. | |
| 4 | + | // | |
| 5 | + | // To learn more go to https://www.jbang.dev and install JBang. | |
| 6 | + | // | |
| 7 | + | // Run this script: | |
| 8 | + | // ./h2tocsv.sh -d <database.mv.db> -t <table> [-f <output file>] [-u db user] [-p db password] | |
| 9 | + | // | |
| 10 | + | //DEPS info.picocli:picocli:4.5.0 com.h2database:h2:1.4.197 | |
| 11 | + | ||
| 12 | + | import static java.util.Optional.ofNullable; | |
| 13 | + | ||
| 14 | + | import java.io.File; | |
| 15 | + | import java.nio.charset.StandardCharsets; | |
| 16 | + | import java.sql.Connection; | |
| 17 | + | import java.sql.DriverManager; | |
| 18 | + | import java.sql.ResultSet; | |
| 19 | + | import java.sql.SQLException; | |
| 20 | + | import java.sql.Statement; | |
| 21 | + | import java.util.concurrent.Callable; | |
| 22 | + | ||
| 23 | + | import org.h2.Driver; | |
| 24 | + | import org.h2.tools.Csv; | |
| 25 | + | import org.h2.util.JdbcUtils; | |
| 26 | + | ||
| 27 | + | import picocli.CommandLine; | |
| 28 | + | import picocli.CommandLine.Command; | |
| 29 | + | import picocli.CommandLine.Option; | |
| 30 | + | ||
| 31 | + | /** | |
| 32 | + | * Converts a table in a H2 database file to CSV | |
| 33 | + | * | |
| 34 | + | * @author John Ahlroos / Devsoap 2021 | |
| 35 | + | */ | |
| 36 | + | @Command(name = "h2tocsv", mixinStandardHelpOptions = true, version = "0.1", description = "H2 database to CSV converter") | |
| 37 | + | class h2tocsv implements Callable<Integer> { | |
| 38 | + | ||
| 39 | + | @Option(names = { "--database", "-d" }, required = true, description = "H2 database to import") | |
| 40 | + | private File database; | |
| 41 | + | ||
| 42 | + | @Option(names = { "--table", "-t" }, required = true, description = "The database table to export") | |
| 43 | + | private String table; | |
| 44 | + | ||
| 45 | + | @Option(names = { "--user", "-u" }, defaultValue = "sa", description = "Database user name") | |
| 46 | + | private String user; | |
| 47 | + | ||
| 48 | + | @Option(names = { "--password", "-p" }, defaultValue = "", description = "Database password") | |
| 49 | + | private String password; | |
| 50 | + | ||
| 51 | + | @Option(names = { "--file", "-f" }, description = "CSV file path to output") | |
| 52 | + | private String csvFile; | |
| 53 | + | ||
| 54 | + | public static void main(final String... args) { | |
| 55 | + | Driver.load(); | |
| 56 | + | final int exitCode = new CommandLine(new h2tocsv()).execute(args); | |
| 57 | + | System.exit(exitCode); | |
| 58 | + | } | |
| 59 | + | ||
| 60 | + | @Override | |
| 61 | + | public Integer call() throws Exception { | |
| 62 | + | csvFile = ofNullable(csvFile).orElse( | |
| 63 | + | String.format("%s-%s.csv", database.getName().substring(0, database.getName().indexOf(".")), table)); | |
| 64 | + | ||
| 65 | + | if (!database.exists()) { | |
| 66 | + | System.err.println("Database file " + database.getCanonicalPath() + " does not exist!"); | |
| 67 | + | return -1; | |
| 68 | + | } | |
| 69 | + | ||
| 70 | + | final String path = database.getCanonicalPath().replace(".mv.db", ""); | |
| 71 | + | final String url = "jdbc:h2:file:" + path + ";DB_CLOSE_DELAY=-1"; | |
| 72 | + | Connection conn = null; | |
| 73 | + | try { | |
| 74 | + | conn = DriverManager.getConnection(url, user, password); | |
| 75 | + | try (Statement statement = conn.createStatement()) { | |
| 76 | + | final String sql = String.format("select * from %s", table); | |
| 77 | + | ||
| 78 | + | System.out.println(String.format("Exporting table %s...", table)); | |
| 79 | + | final ResultSet result = statement.executeQuery(sql); | |
| 80 | + | ||
| 81 | + | System.out.println("Writing database table to file..."); | |
| 82 | + | new Csv().write(csvFile, result, StandardCharsets.UTF_8.toString()); | |
| 83 | + | } | |
| 84 | + | } catch (SQLException e) { | |
| 85 | + | System.err.println("Failed to read database, error message was: " + e.getMessage()); | |
| 86 | + | return e.getErrorCode(); | |
| 87 | + | } finally { | |
| 88 | + | ofNullable(conn).ifPresent(JdbcUtils::closeSilently); | |
| 89 | + | } | |
| 90 | + | ||
| 91 | + | System.out.println("Done. CSV file exported to " + csvFile); | |
| 92 | + | return 0; | |
| 93 | + | } | |
| 94 | + | } | |
Newer
Older