cdc.impex.demos.DbExportDemo Maven / Gradle / Ivy
The newest version!
package cdc.impex.demos;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.function.Function;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import cdc.impex.ImpExCatalog;
import cdc.impex.ImpExFactory;
import cdc.impex.ImpExFactoryFeatures;
import cdc.impex.db.DbSheetExporter;
import cdc.impex.db.mapping.DbColumn;
import cdc.impex.db.mapping.DbMapper;
import cdc.impex.db.mapping.DbTable;
import cdc.impex.exports.Exporter;
import cdc.impex.exports.VerboseExporter;
import cdc.impex.templates.ColumnTemplate;
import cdc.impex.templates.SheetTemplate;
import cdc.impex.templates.SheetTemplateInstance;
import cdc.issues.Issue;
import cdc.issues.IssuesCollector;
import cdc.issues.VoidIssuesHandler;
import cdc.issues.impl.IssuesAndAnswersImpl;
import cdc.issues.io.IssuesIoFactoryFeatures;
import cdc.issues.io.IssuesWriter;
import cdc.issues.io.OutSettings;
import cdc.rdb.RdbColumnOrder;
import cdc.util.events.ProgressController;
class DbExportDemo {
private static final Logger LOGGER = LogManager.getLogger(DbExportDemo.class);
static final ColumnTemplate NUM =
ColumnTemplate.builder(int.class)
.name("Number")
.importConverter(Integer::valueOf)
.build();
static final ColumnTemplate ADDR =
ColumnTemplate.builder(String.class)
.name("Address")
.importConverter(Function.identity())
.build();
static final SheetTemplate LOC =
SheetTemplate.builder()
.domain("Impex")
.name("Location")
.column(NUM)
.column(ADDR)
.build();
static final ImpExCatalog CATALOG = new ImpExCatalog();
static final DbColumn DB_NUM =
DbColumn.builder("NUM", int.class)
.exportConverter(o -> ((Integer) o).intValue())
.build();
static final DbColumn DB_ADDR =
DbColumn.builder("ADDR", String.class)
.build();
static final DbTable DB_LOC =
DbTable.builder()
.name("LOCATION")
.schema("CDC")
.column(DB_NUM, RdbColumnOrder.ASCENDING)
.column(DB_ADDR)
.build();
static final DbMapper MAPPER_LOC =
DbMapper.builder()
.table(DB_LOC)
.template(LOC)
.map(DB_NUM, NUM)
.map(DB_ADDR, ADDR)
.build();
private static class ConnectionHolder {
private Connection connection;
public Connection getConnection() {
return connection;
}
public void setConnection(Connection connection) {
this.connection = connection;
}
}
static final ConnectionHolder CONNECTION_HOLDER = new ConnectionHolder();
static final DbSheetExporter EXPORT_LOC = DbSheetExporter.create(MAPPER_LOC, CONNECTION_HOLDER::getConnection);
static {
CATALOG.register(LOC, EXPORT_LOC);
}
static void demo() throws SQLException, IOException {
LOGGER.info("Create DB");
System.setProperty("derby.system.home", "target/derby");
final String protocol = "jdbc:derby:";
final Properties props = new Properties();
props.put("user", "cdc");
props.put("password", "cdc");
final String dbName = "ImpEx";
try (final Connection conn = DriverManager.getConnection(protocol + dbName + ";create=true", props)) {
LOGGER.info("Created DB");
CONNECTION_HOLDER.setConnection(conn);
conn.setAutoCommit(false);
try (final Statement s = conn.createStatement()) {
s.execute("CREATE TABLE location(num int, addr varchar(40))");
LOGGER.info("Created Table");
try (final PreparedStatement psInsert = conn.prepareStatement("insert into location values (?, ?)")) {
psInsert.setInt(1, 1);
psInsert.setString(2, "Hello");
psInsert.executeUpdate();
LOGGER.info("Inserted row");
psInsert.setInt(1, 2);
psInsert.setString(2, "World");
psInsert.executeUpdate();
LOGGER.info("Inserted row");
}
conn.commit();
LOGGER.info("Query data");
try (ResultSet rs = s.executeQuery("SELECT * FROM location ORDER BY num")) {
final ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
for (int column = 1; column <= meta.getColumnCount(); column++) {
LOGGER.info("{}/{}/{}: {}",
meta.getSchemaName(column),
meta.getTableName(column),
meta.getColumnName(column),
rs.getObject(column));
}
}
}
final IssuesCollector issuesHandler =
new IssuesCollector<>(VoidIssuesHandler.INSTANCE);
final File file = new File("target/impex.csv");
final ImpExFactory factory = new ImpExFactory(ImpExFactoryFeatures.FASTEST);
final Exporter exporter = new VerboseExporter(factory.createExporter(file));
try {
exporter.exportData(file,
CATALOG.getTemplatesAsList(LOC.getName())
.stream()
.map(SheetTemplateInstance::replace)
.toList(),
CATALOG.createWorkbookExporterFor(LOC.getName()),
issuesHandler,
ProgressController.VERBOSE);
} catch (final RuntimeException e) {
LOGGER.catching(e);
}
LOGGER.info("Save issues");
IssuesWriter.save(new IssuesAndAnswersImpl().addIssues(issuesHandler.getIssues()),
OutSettings.ALL_DATA_ANSWERS,
new File("target/export-issues.xlsx"),
ProgressController.VERBOSE,
IssuesIoFactoryFeatures.UTC_FASTEST);
LOGGER.info("Drop Table");
s.execute("DROP TABLE location");
conn.commit();
LOGGER.info("Dropped Table");
}
try {
DriverManager.getConnection(protocol + ";shutdown=true");
} catch (final SQLException se) {
if (((se.getErrorCode() == 50000)
&& ("XJ015".equals(se.getSQLState())))) {
// we got the expected exception
LOGGER.info("Derby shut down normally");
// Note that for single database shutdown, the expected
// SQL state is "08006", and the error code is 45000.
} else {
// if the error code or SQLState is different, we have
// an unexpected exception (shutdown failed)
LOGGER.error("Derby did not shut down normally");
LOGGER.catching(se);
}
}
}
}
public static void main(String[] args) throws SQLException, IOException {
demo();
}
}