-
Notifications
You must be signed in to change notification settings - Fork 418
zzz example db
Remko Popma edited this page Jan 21, 2020
·
1 revision
package picocli.example.db;
import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Model.CommandSpec;
import picocli.CommandLine.Option;
import picocli.CommandLine.ParameterException;
import picocli.CommandLine.Spec;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
@Command(name = "db", mixinStandardHelpOptions = true,
versionProvider = DbVersionProvider.class,
subcommands = {ListRows.class, AddRow.class, UpdateRow.class, DeleteRow.class},
description = "CRUD (Create, Retrieve, Update, Delete) utility for managing the example db.")
public class Db implements Runnable {
@Spec
CommandSpec spec;
@Option(names = {"--jdbc-driver"},
description = "Fully qualified class name of the JDBC Driver. Default: ${DEFAULT-VALUE}.",
defaultValue = "org.sqlite.JDBC")
private String jdbcDriver;
@Option(names = {"--jdbc-url"},
description = {"JDBC URL to the SQLite file.", " Default: ${DEFAULT-VALUE}: the file is expected to be in the current directory."},
defaultValue = "jdbc:sqlite:db.sqlite")
private String jdbcURL;
@Option(names = {"--table"},
description = {"Name of the table that the CRUD operations apply to. Default: ${DEFAULT-VALUE}."},
defaultValue = "mytable")
String tableName;
@Option(names = {"-v", "--verbose"},
description = {
"Specify multiple -v options to increase verbosity.",
"For example, `-v -v -v` or `-vvv`"})
boolean[] verbosity = new boolean[0];
@Override
public void run() {
throw new ParameterException(spec.commandLine(), "Missing required subcommand");
}
<R> R executeQuery(String sql, Function<ResultSet, R> resultSetConsumer)
throws SQLException, ClassNotFoundException {
if (verbosity.length > 1) {
err("Loading jdbc driver '%s'...%n", jdbcDriver);
}
Class.forName(jdbcDriver);
if (verbosity.length > 1) {
err("Connecting to '%s'...%n", jdbcURL);
}
try (Connection connection = DriverManager.getConnection(jdbcURL)) {
if (verbosity.length > 0) {
err("Executing SQL: %s%n", sql);
}
try (PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
R result = resultSetConsumer.apply(rs);
if (verbosity.length > 0) {
if (result instanceof int[]) {
err("Transformed result: %s%n", Arrays.toString((int[]) result));
} else {
err("Transformed result: %s%n", result);
}
}
return result;
}
}
}
List<String> selectPrimaryKeyColumns()
throws SQLException, ClassNotFoundException {
if (verbosity.length > 1) {
err("Loading jdbc driver '%s'...%n", jdbcDriver);
}
Class.forName(jdbcDriver);
if (verbosity.length > 1) {
err("Connecting to '%s'...%n", jdbcURL);
}
List<String> result = new ArrayList<>();
try (Connection connection = DriverManager.getConnection(jdbcURL)) {
DatabaseMetaData metaData = connection.getMetaData();
try (ResultSet rs = metaData.getPrimaryKeys(null, null, tableName)) {
while (rs.next()) {
result.add(rs.getString(4));
}
}
}
return result;
}
interface TriConsumer<A, B, C> {
void accept(A a, B b, C c);
}
<B, C> int executeUpdate(String sql, TriConsumer<PreparedStatement, B, C> preparedStatementConsumer, B b, C c)
throws SQLException, ClassNotFoundException {
if (verbosity.length > 1) {
err("Loading jdbc driver '%s'...%n", jdbcDriver);
}
Class.forName(jdbcDriver);
if (verbosity.length > 1) {
err("Connecting to '%s'...%n", jdbcURL);
}
try (Connection connection = DriverManager.getConnection(jdbcURL)) {
if (verbosity.length > 0) {
err("Executing SQL: %s%n", sql);
}
try (PreparedStatement ps = connection.prepareStatement(sql)) {
preparedStatementConsumer.accept(ps, b, c);
int result = ps.executeUpdate();
if (verbosity.length > 0) {
err("%d rows affected.%n", result);
}
return result;
}
}
}
int[] extractTypes(ResultSet rs) {
try {
ResultSetMetaData metaData = rs.getMetaData();
int[] result = new int[metaData.getColumnCount()];
for (int i = 1; i <= metaData.getColumnCount(); i++) {
result[i - 1] = metaData.getColumnType(i);
}
return result;
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
void prepareStatement(PreparedStatement ps, Map<String, String> columnValues, int[] types) {
try {
if (verbosity.length > 0) {
err("Setting values: %s%n", columnValues);
}
List<String> columnNames = new ArrayList<>(columnValues.keySet());
for (int i = 0; i < types.length; i++) {
String value = columnValues.get(columnNames.get(i));
switch (types[i]) {
case Types.ARRAY:
//ps.setArray(i + 1, createArray());
break;
case Types.BIGINT:
ps.setBigDecimal(i + 1, new BigDecimal(value));
break;
case Types.DECIMAL:
case Types.DOUBLE:
case Types.NUMERIC:
ps.setDouble(i + 1, new Double(value));
break;
case Types.FLOAT:
ps.setFloat(i + 1, new Float(value));
break;
case Types.INTEGER:
case Types.REAL:
case Types.SMALLINT:
case Types.TINYINT:
ps.setInt(i + 1, new Integer(value));
break;
case Types.BOOLEAN:
case Types.BIT:
ps.setBoolean(i + 1, new Boolean(value));
break;
case Types.CHAR:
case Types.LONGNVARCHAR:
case Types.LONGVARCHAR:
case Types.NCHAR:
case Types.NVARCHAR:
case Types.VARCHAR:
ps.setString(i + 1, value);
break;
case Types.DATE:
ps.setDate(i + 1, java.sql.Date.valueOf(value));
break;
case Types.TIME:
ps.setTime(i + 1, java.sql.Time.valueOf(value));
break;
case Types.TIMESTAMP:
ps.setTimestamp(i + 1, java.sql.Timestamp.valueOf(value));
break;
}
}
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
}
void out(Object value) {
spec.commandLine().getOut().println(value);
}
void err(String pattern, Object... params) {
spec.commandLine().getErr().printf(pattern, params);
}
public static void main(String[] args) {
System.exit(new CommandLine(new Db()).execute(args));
}
}package picocli.example.db;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.ParentCommand;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
@Command(name = "add", mixinStandardHelpOptions = true, //usageHelpAutoWidth = true,
versionProvider = DbVersionProvider.AddRow.class,
description = "Adds a row to the example db.",
footerHeading = "%nExample:%n",
footer = {
"# This adds a new row to the `mytable` table.",
"# Note that the `id` (primary key and auto-increment) column is omitted.",
"db -v --table=mytable add -Chostname=abc -Cserver_type=PHYS -Cclass=PROD -Cserial_no=12345 -Cmodel=DL380G9 -Cstatus=SOFF -Cdescription=\"This is a box\""
})
public class AddRow implements Callable<Integer> {
@ParentCommand
private Db db;
@Option(names = {"-C", "--column"}, paramLabel = "<column>=<value>", required = true,
description = {"Key-value pair specifying the value to insert in a column. " +
"This option may be specified multiple times " +
"(once for each column that you want to specify a value for). ",
"Columns of type DATE must be specified in \"yyyy-[m]m-[d]d\" format (the leading zero for mm and dd may be omitted);",
"Columns of type TIME must be specified in \"hh:mm:ss\" format;",
"Columns of type TIMESTAMP must be specified in format \"yyyy-[m]m-[d]d hh:mm:ss[.f...]\" " +
"(the fractional seconds may be omitted. The leading zero for mm and dd may also be omitted.)"})
Map<String, String> columnValues;
@Override
public Integer call() throws SQLException, ClassNotFoundException {
String columns = String.join(",", columnValues.keySet());
String[] tmp = new String[columnValues.size()];
Arrays.fill(tmp, "?");
String values = String.join(",", Arrays.asList(tmp));
String sql = "insert into " + db.tableName + " (" + columns + ") values (" + values + ")";
int[] types = db.executeQuery("select " + columns + " from " + db.tableName, db::extractTypes);
db.executeUpdate(sql, db::prepareStatement, columnValues, types);
return 0;
}
}package picocli.example.db;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.ParentCommand;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
@Command(name = "delete", mixinStandardHelpOptions = true, //usageHelpAutoWidth = true,
versionProvider = DbVersionProvider.DeleteRow.class,
description = "Deletes a row in the db.",
footerHeading = "%nExample:%n",
footer = {
"# This deletes a row from the `mytable` table where id=1.",
"db -v --table=mytable delete -Cid=1",
"",
"# This deletes all rows from the `mytable` table where server_type=PHYS and class=DEV.",
"db -v --table=mytable delete -Cserver_type=PHYS -Cclass=DEV",
})
public class DeleteRow implements Callable<Integer> {
@ParentCommand
private Db db;
@Option(names = {"-C", "--column"}, paramLabel = "<column>=<value>", required = true,
description = {"Key-value pair specifying a column and its value. " +
"This option may be specified multiple times " +
"(once for each column that you want to specify a value for). ",
"Columns of type DATE must be specified in \"yyyy-[m]m-[d]d\" format (the leading zero for mm and dd may be omitted);",
"Columns of type TIME must be specified in \"hh:mm:ss\" format;",
"Columns of type TIMESTAMP must be specified in format \"yyyy-[m]m-[d]d hh:mm:ss[.f...]\" " +
"(the fractional seconds may be omitted. The leading zero for mm and dd may also be omitted.)"})
private Map<String, String> columnValues;
@Override
public Integer call() throws SQLException, ClassNotFoundException {
String cols = String.join(",", columnValues.keySet());
int[] types = db.executeQuery("select " + cols + " from " + db.tableName, db::extractTypes);
String columns = String.join("=?,", columnValues.keySet());
String sql = "delete from " + db.tableName + " where " + columns + "=?";
db.executeUpdate(sql, db::prepareStatement, columnValues, types);
return 0;
}
}package picocli.example.db;
import picocli.CommandLine.IVersionProvider;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.Properties;
public class DbVersionProvider implements IVersionProvider {
private static final String INVENTORY_VERSION = "1.0";
@Override
public String[] getVersion() throws IOException {
return new String[] {
commandNameAndVersion(),
sqlLiteNameAndVersion(),
"Picocli " + picocli.CommandLine.VERSION,
"JVM: ${java.version} (${java.vendor} ${java.vm.name} ${java.vm.version})",
"OS: ${os.name} ${os.version} ${os.arch}"
};
}
private String sqlLiteNameAndVersion() throws IOException {
URL resource = getClass().getResource("/sqlite-jdbc.properties");
//System.out.println(resource);
try (InputStream in = resource.openStream()) {
Properties p = new Properties();
p.load(in);
return "Driver: "
+ p.getProperty("name", "UNKNOWN NAME") + " "
+ p.getProperty("version", "UNKNOWN VERSION");
}
}
protected String commandNameAndVersion() {
return "db " + INVENTORY_VERSION;
}
static class AddRow extends DbVersionProvider {
@Override
protected String commandNameAndVersion() {
return "db add " + INVENTORY_VERSION;
}
}
static class UpdateRow extends DbVersionProvider {
@Override
protected String commandNameAndVersion() {
return "db update " + INVENTORY_VERSION;
}
}
static class DeleteRow extends DbVersionProvider {
@Override
protected String commandNameAndVersion() {
return "db delete " + INVENTORY_VERSION;
}
}
static class ListRows extends DbVersionProvider {
@Override
protected String commandNameAndVersion() {
return "db list " + INVENTORY_VERSION;
}
}
}package picocli.example.db;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.ParentCommand;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.Callable;
@Command(name = "list", mixinStandardHelpOptions = true,
versionProvider = DbVersionProvider.ListRows.class,
description = "Lists all rows in the example db.")
public class ListRows implements Callable<Integer> {
@ParentCommand
private Db db;
@Option(names = {"-c", "--columns"}, paramLabel = "NAME", defaultValue = "*", split = ",",
description = "Comma-separated list of the columns to print. Default value: ${DEFAULT-VALUE}.")
private List<String> columnNames;
@Option(names = {"-s", "--separator"}, paramLabel = "STRING", defaultValue = "\t",
description = "Separator between column names and column values. Default value: '${DEFAULT-VALUE}'.")
private String separator;
@Option(names = {"--no-column-names"}, negatable = true,
description = "Whether to first output a row with column names. By default, column names are printed.")
private boolean firstPrintColumnNames = true;
@Override
public Integer call() throws SQLException, ClassNotFoundException {
String columns = String.join(",", columnNames);
String sql = "select " + columns + " from " + db.tableName;
db.executeQuery(sql, this::printAllColumns);
return 0;
}
private Void printAllColumns(ResultSet rs) {
try {
ResultSetMetaData metaData = rs.getMetaData();
StringBuilder sb = new StringBuilder();
if (firstPrintColumnNames) {
for (int i = 1; i <= metaData.getColumnCount(); i++) {
if (i > 1) {
sb.append(separator);
}
sb.append(metaData.getColumnName(i));
}
db.out(sb);
}
while (rs.next()) {
sb.setLength(0);
for (int i = 1; i <= metaData.getColumnCount(); i++) {
if (i > 1) {
sb.append(separator);
}
sb.append(rs.getObject(i));
}
db.out(sb);
}
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
return null;
}
}package picocli.example.db;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;
import picocli.CommandLine.ParentCommand;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.concurrent.Callable;
@Command(name = "update", mixinStandardHelpOptions = true, //usageHelpAutoWidth = true,
versionProvider = DbVersionProvider.UpdateRow.class,
description = "Updates a row in the example db.",
footerHeading = "%nExample:%n",
footer = {
"# This updates a row in the `mytable` table.",
"# Note that the primary key columns are mandatory.",
"db -v --table=mytable update -Cid=1 -Chostname=abc -Cserver_type=PHYS -Cclass=PROD -Cserial_no=12345 -Cmodel=DL380G9 -Cstatus=SOFF -Cdescription=\"This is a box\""
})
public class UpdateRow implements Callable<Integer> {
@ParentCommand
private Db db;
@Option(names = {"-C", "--column"}, paramLabel = "<column>=<value>", required = true,
description = {"Key-value pair specifying a column and its value. " +
"This option may be specified multiple times " +
"(once for each column that you want to specify a value for). ",
"Primary key columns are mandatory.",
"Columns of type DATE must be specified in \"yyyy-[m]m-[d]d\" format (the leading zero for mm and dd may be omitted);",
"Columns of type TIME must be specified in \"hh:mm:ss\" format;",
"Columns of type TIMESTAMP must be specified in format \"yyyy-[m]m-[d]d hh:mm:ss[.f...]\" " +
"(the fractional seconds may be omitted. The leading zero for mm and dd may also be omitted.)"})
private Map<String, String> columnValues;
@Override
public Integer call() throws SQLException, ClassNotFoundException {
List<String> keyColumnNames = db.selectPrimaryKeyColumns();
List<String> valueColumnNames = new ArrayList<>(columnValues.keySet());
valueColumnNames.removeAll(keyColumnNames);
String valueColumns = String.join("=?,", valueColumnNames);
String keyColumns = String.join("=?,", keyColumnNames);
String columns1 = String.join(",", valueColumnNames);
String columns2 = String.join(",", keyColumnNames);
int[] types = db.executeQuery("select " + columns1 + "," + columns2 + " from " + db.tableName, db::extractTypes);
String sql = "update " + db.tableName + " set " + valueColumns + "=? where " + keyColumns + "=?";
Map<String, String> reordered = new LinkedHashMap<>();
for (String key : valueColumnNames) { reordered.put(key, columnValues.get(key)); }
for (String key : keyColumnNames) { reordered.put(key, columnValues.get(key)); }
db.executeUpdate(sql, db::prepareStatement, reordered, types);
return 0;
}
}