Created
January 19, 2021 06:27
-
-
Save johndevs/810261060dcac97905d89327d0a30274 to your computer and use it in GitHub Desktop.
H2 to CSV export
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
///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 -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.200 | |
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; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment