From 67560e68f22c9745a7fe11876224a170798b05d5 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Fri, 25 Nov 2011 15:06:47 +0000 Subject: [PATCH] Add option to import file without headers to existing table; Add ImportUtil.Builder and ExportUtil.Builder to simplify import/export operations git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@599 f203690c-595d-4dc9-a70b-905162fa7fd2 --- src/changes/changes.xml | 7 + .../jackcess/ExportUtil.java | 108 +++++++++++ .../jackcess/ImportUtil.java | 178 +++++++++++++++++- .../jackcess/ExportTest.java | 16 +- .../jackcess/ImportTest.java | 60 +++++- 5 files changed, 352 insertions(+), 17 deletions(-) diff --git a/src/changes/changes.xml b/src/changes/changes.xml index 2fa0e8d..2b1132a 100644 --- a/src/changes/changes.xml +++ b/src/changes/changes.xml @@ -24,6 +24,13 @@ Allow ImportFilter and ExportFilter to return null from filterRow() to indicate that a row should be skipped. + + Add option to import file without headers to existing table. + + + Add ImportUtil.Builder and ExportUtil.Builder to simplify + import/export operations. + diff --git a/src/java/com/healthmarketscience/jackcess/ExportUtil.java b/src/java/com/healthmarketscience/jackcess/ExportUtil.java index 472d328..ad8d502 100644 --- a/src/java/com/healthmarketscience/jackcess/ExportUtil.java +++ b/src/java/com/healthmarketscience/jackcess/ExportUtil.java @@ -68,6 +68,7 @@ public class ExportUtil { * The directory where the new files will be created * * @see #exportAll(Database,File,String) + * @see Builder */ public static void exportAll(Database db, File dir) throws IOException { @@ -87,6 +88,7 @@ public class ExportUtil { * The file extension of the new files * * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportAll(Database db, File dir, String ext) throws IOException { @@ -111,6 +113,7 @@ public class ExportUtil { * If true the first line contains the column names * * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportAll(Database db, File dir, String ext, boolean header) @@ -142,6 +145,7 @@ public class ExportUtil { * valid export filter * * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportAll(Database db, File dir, String ext, boolean header, String delim, @@ -166,6 +170,7 @@ public class ExportUtil { * New file to create * * @see #exportFile(Database,String,File,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportFile(Database db, String tableName, File f) throws IOException { @@ -194,6 +199,7 @@ public class ExportUtil { * valid export filter * * @see #exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportFile(Database db, String tableName, File f, boolean header, String delim, char quote, @@ -227,6 +233,7 @@ public class ExportUtil { * Writer to export to * * @see #exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportWriter(Database db, String tableName, BufferedWriter out) throws IOException { @@ -254,6 +261,7 @@ public class ExportUtil { * valid export filter * * @see #exportWriter(Cursor,BufferedWriter,boolean,String,char,ExportFilter) + * @see Builder */ public static void exportWriter(Database db, String tableName, BufferedWriter out, boolean header, String delim, @@ -279,6 +287,8 @@ public class ExportUtil { * The quote character * @param filter * valid export filter + * + * @see Builder */ public static void exportWriter(Cursor cursor, BufferedWriter out, boolean header, String delim, @@ -390,4 +400,102 @@ public class ExportUtil { out.write(quote); } + + /** + * Builder which simplifies configuration of an export operation. + */ + public static class Builder + { + private Database _db; + private String _tableName; + private String _ext = DEFAULT_FILE_EXT; + private Cursor _cursor; + private String _delim = DEFAULT_DELIMITER; + private char _quote = DEFAULT_QUOTE_CHAR; + private ExportFilter _filter = SimpleExportFilter.INSTANCE; + private boolean _header; + + public Builder(Database db) { + this(db, null); + } + + public Builder(Database db, String tableName) { + _db = db; + _tableName = tableName; + } + + public Builder(Cursor cursor) { + _cursor = cursor; + } + + public Builder setDatabase(Database db) { + _db = db; + return this; + } + + public Builder setTableName(String tableName) { + _tableName = tableName; + return this; + } + + public Builder setCursor(Cursor cursor) { + _cursor = cursor; + return this; + } + + public Builder setDelimiter(String delim) { + _delim = delim; + return this; + } + + public Builder setQuote(char quote) { + _quote = quote; + return this; + } + + public Builder setFilter(ExportFilter filter) { + _filter = filter; + return this; + } + + public Builder setHeader(boolean header) { + _header = header; + return this; + } + + public Builder setFileNameExtension(String ext) { + _ext = ext; + return this; + } + + /** + * @see ExportUtil#exportAll(Database,File,String,boolean,String,char,ExportFilter) + */ + public void exportAll(File dir) throws IOException { + ExportUtil.exportAll(_db, dir, _ext, _header, _delim, _quote, _filter); + } + + /** + * @see ExportUtil#exportFile(Database,String,File,boolean,String,char,ExportFilter) + */ + public void exportFile(File f) throws IOException { + ExportUtil.exportFile(_db, _tableName, f, _header, _delim, _quote, + _filter); + } + + /** + * @see ExportUtil#exportWriter(Database,String,BufferedWriter,boolean,String,char,ExportFilter) + * @see ExportUtil#exportWriter(Cursor,BufferedWriter,boolean,String,char,ExportFilter) + */ + public void exportWriter(BufferedWriter writer) throws IOException { + if(_cursor != null) { + ExportUtil.exportWriter(_cursor, writer, _header, _delim, + _quote, _filter); + } else { + ExportUtil.exportWriter(_db, _tableName, writer, _header, _delim, + _quote, _filter); + } + } + } + } diff --git a/src/java/com/healthmarketscience/jackcess/ImportUtil.java b/src/java/com/healthmarketscience/jackcess/ImportUtil.java index b647570..571ebfa 100644 --- a/src/java/com/healthmarketscience/jackcess/ImportUtil.java +++ b/src/java/com/healthmarketscience/jackcess/ImportUtil.java @@ -73,6 +73,7 @@ public class ImportUtil * @return the name of the copied table * * @see #importResultSet(ResultSet,Database,String,ImportFilter) + * @see Builder */ public static String importResultSet(ResultSet source, Database db, String name) @@ -94,6 +95,7 @@ public class ImportUtil * @return the name of the imported table * * @see #importResultSet(ResultSet,Database,String,ImportFilter,boolean) + * @see Builder */ public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter) @@ -114,6 +116,8 @@ public class ImportUtil * name * * @return the name of the imported table + * + * @see Builder */ public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter, @@ -194,6 +198,7 @@ public class ImportUtil * @return the name of the imported table * * @see #importFile(File,Database,String,String,ImportFilter) + * @see Builder */ public static String importFile(File f, Database db, String name, String delim) @@ -216,6 +221,7 @@ public class ImportUtil * @return the name of the imported table * * @see #importReader(BufferedReader,Database,String,String,ImportFilter) + * @see Builder */ public static String importFile(File f, Database db, String name, String delim, ImportFilter filter) @@ -229,7 +235,7 @@ public class ImportUtil * Copy a delimited text file into a new table in this database. *

* Equivalent to: - * {@code importReader(new BufferedReader(new FileReader(f)), db, name, delim, "'", filter, false);} + * {@code importReader(new BufferedReader(new FileReader(f)), db, name, delim, "'", filter, useExistingTable, true);} * * @param name Name of the new table to create * @param f Source file to import @@ -242,19 +248,51 @@ public class ImportUtil * * @return the name of the imported table * - * @see #importReader(BufferedReader,Database,String,String,ImportFilter) + * @see #importReader(BufferedReader,Database,String,String,ImportFilter,boolean) + * @see Builder */ public static String importFile(File f, Database db, String name, String delim, char quote, ImportFilter filter, boolean useExistingTable) throws IOException + { + return importFile(f, db, name, delim, quote, filter, useExistingTable, true); + } + + /** + * Copy a delimited text file into a new table in this database. + *

+ * Equivalent to: + * {@code importReader(new BufferedReader(new FileReader(f)), db, name, delim, "'", filter, useExistingTable, header);} + * + * @param name Name of the new table to create + * @param f Source file to import + * @param delim Regular expression representing the delimiter string. + * @param quote the quote character + * @param filter valid import filter + * @param useExistingTable if {@code true} use current table if it already + * exists, otherwise, create new table with unique + * name + * @param header if {@code false} the first line is not a header row, only + * valid if useExistingTable is {@code true} + * @return the name of the imported table + * + * @see #importReader(BufferedReader,Database,String,String,char,ImportFilter,boolean,boolean) + * @see Builder + */ + public static String importFile(File f, Database db, String name, + String delim, char quote, + ImportFilter filter, + boolean useExistingTable, + boolean header) + throws IOException { BufferedReader in = null; try { in = new BufferedReader(new FileReader(f)); return importReader(in, db, name, delim, quote, filter, - useExistingTable); + useExistingTable, header); } finally { if (in != null) { try { @@ -279,6 +317,7 @@ public class ImportUtil * @return the name of the imported table * * @see #importReader(BufferedReader,Database,String,String,ImportFilter) + * @see Builder */ public static String importReader(BufferedReader in, Database db, String name, String delim) @@ -301,6 +340,7 @@ public class ImportUtil * @return the name of the imported table * * @see #importReader(BufferedReader,Database,String,String,ImportFilter,boolean) + * @see Builder */ public static String importReader(BufferedReader in, Database db, String name, String delim, @@ -326,6 +366,8 @@ public class ImportUtil * name * * @return the name of the imported table + * + * @see Builder */ public static String importReader(BufferedReader in, Database db, String name, String delim, @@ -340,6 +382,9 @@ public class ImportUtil /** * Copy a delimited text file into a new (or optionally exixsting) table in * this database. + *

+ * Equivalent to: + * {@code importReader(in, db, name, delim, '"', filter, useExistingTable, true);} * * @param name Name of the new table to create * @param in Source reader to import @@ -351,12 +396,43 @@ public class ImportUtil * name * * @return the name of the imported table + * + * @see Builder */ public static String importReader(BufferedReader in, Database db, String name, String delim, char quote, ImportFilter filter, boolean useExistingTable) throws IOException + { + return importReader(in, db, name, delim, quote, filter, useExistingTable, + true); + } + + /** + * Copy a delimited text file into a new (or optionally exixsting) table in + * this database. + * + * @param name Name of the new table to create + * @param in Source reader to import + * @param delim Regular expression representing the delimiter string. + * @param quote the quote character + * @param filter valid import filter + * @param useExistingTable if {@code true} use current table if it already + * exists, otherwise, create new table with unique + * name + * @param header if {@code false} the first line is not a header row, only + * valid if useExistingTable is {@code true} + * + * @return the name of the imported table + * + * @see Builder + */ + public static String importReader(BufferedReader in, Database db, + String name, String delim, char quote, + ImportFilter filter, + boolean useExistingTable, boolean header) + throws IOException { String line = in.readLine(); if (line == null || line.trim().length() == 0) { @@ -381,11 +457,23 @@ public class ImportUtil } table = createUniqueTable(db, name, columns, null, filter); + + // the first row was a header row + header = true; } List rows = new ArrayList(COPY_TABLE_BATCH_SIZE); int numColumns = table.getColumnCount(); + if(!header) { + // first line is _not_ a header line + Object[] data = splitLine(line, delimPat, quote, in, numColumns); + data = filter.filterRow(data); + if(data != null) { + rows.add(data); + } + } + while ((line = in.readLine()) != null) { Object[] data = splitLine(line, delimPat, quote, in, numColumns); @@ -509,6 +597,88 @@ public class ImportUtil return db.getTable(name); } - + /** + * Builder which simplifies configuration of an import operation. + */ + public static class Builder + { + private Database _db; + private String _tableName; + private String _delim = ExportUtil.DEFAULT_DELIMITER; + private char _quote = ExportUtil.DEFAULT_QUOTE_CHAR; + private ImportFilter _filter = SimpleImportFilter.INSTANCE; + private boolean _useExistingTable; + private boolean _header = true; + + public Builder(Database db) { + this(db, null); + } + + public Builder(Database db, String tableName) { + _db = db; + _tableName = tableName; + } + + public Builder setDatabase(Database db) { + _db = db; + return this; + } + + public Builder setTableName(String tableName) { + _tableName = tableName; + return this; + } + + public Builder setDelimiter(String delim) { + _delim = delim; + return this; + } + + public Builder setQuote(char quote) { + _quote = quote; + return this; + } + + public Builder setFilter(ImportFilter filter) { + _filter = filter; + return this; + } + + public Builder setUseExistingTable(boolean useExistingTable) { + _useExistingTable = useExistingTable; + return this; + } + + public Builder setHeader(boolean header) { + _header = header; + return this; + } + + /** + * @see ImportUtil#importResultSet(ResultSet,Database,String,ImportFilter,boolean) + */ + public String importResultSet(ResultSet source) + throws SQLException, IOException + { + return ImportUtil.importResultSet(source, _db, _tableName, _filter, + _useExistingTable); + } + + /** + * @see ImportUtil#importFile(File,Database,String,String,char,ImportFilter,boolean,boolean) + */ + public String importFile(File f) throws IOException { + return ImportUtil.importFile(f, _db, _tableName, _delim, _quote, _filter, + _useExistingTable, _header); + } + + /** + * @see ImportUtil#importReader(BufferedReader,Database,String,String,char,ImportFilter,boolean,boolean) + */ + public String importReader(BufferedReader reader) throws IOException { + return ImportUtil.importReader(reader, _db, _tableName, _delim, _quote, + _filter, _useExistingTable, _header); + } + } } diff --git a/test/src/java/com/healthmarketscience/jackcess/ExportTest.java b/test/src/java/com/healthmarketscience/jackcess/ExportTest.java index 4e22683..0499277 100644 --- a/test/src/java/com/healthmarketscience/jackcess/ExportTest.java +++ b/test/src/java/com/healthmarketscience/jackcess/ExportTest.java @@ -81,7 +81,8 @@ public class ExportTest extends TestCase StringWriter out = new StringWriter(); - ExportUtil.exportWriter(db, "test", new BufferedWriter(out)); + new ExportUtil.Builder(db, "test") + .exportWriter(new BufferedWriter(out)); String expected = "some text||some more,13,13.25,\"61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F 70 71 72 73 74 75 76 77 78" + NL + @@ -93,8 +94,11 @@ public class ExportTest extends TestCase out = new StringWriter(); - ExportUtil.exportWriter(db, "test", new BufferedWriter(out), - true, "||", '\'', SimpleExportFilter.INSTANCE); + new ExportUtil.Builder(db, "test") + .setHeader(true) + .setDelimiter("||") + .setQuote('\'') + .exportWriter(new BufferedWriter(out)); expected = "col1||col2||col3||col4||col5||col6" + NL + @@ -117,9 +121,9 @@ public class ExportTest extends TestCase out = new StringWriter(); - ExportUtil.exportWriter(db, "test", new BufferedWriter(out), false, - ExportUtil.DEFAULT_DELIMITER, - ExportUtil.DEFAULT_QUOTE_CHAR, oddFilter); + new ExportUtil.Builder(db, "test") + .setFilter(oddFilter) + .exportWriter(new BufferedWriter(out)); expected = "some text||some more,13,13.25,\"61 62 63 64 65 66 67 68 69 6A 6B 6C 6D 6E 6F 70 71 72 73 74 75 76 77 78" + NL + diff --git a/test/src/java/com/healthmarketscience/jackcess/ImportTest.java b/test/src/java/com/healthmarketscience/jackcess/ImportTest.java index 5f75de0..0be36e1 100644 --- a/test/src/java/com/healthmarketscience/jackcess/ImportTest.java +++ b/test/src/java/com/healthmarketscience/jackcess/ImportTest.java @@ -58,8 +58,9 @@ public class ImportTest extends TestCase { for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) { Database db = create(fileFormat); - String tableName = db.importFile( - "test", new File("test/data/sample-input.tab"), "\\t"); + String tableName = new ImportUtil.Builder(db, "test") + .setDelimiter("\\t") + .importFile(new File("test/data/sample-input.tab")); Table t = db.getTable(tableName); List colNames = new ArrayList(); @@ -94,6 +95,48 @@ public class ImportTest extends TestCase ); assertTable(expectedRows, t); + t = new TableBuilder("test2") + .addColumn(new ColumnBuilder("T1", DataType.TEXT)) + .addColumn(new ColumnBuilder("T2", DataType.TEXT)) + .addColumn(new ColumnBuilder("T3", DataType.TEXT)) + .toTable(db); + + new ImportUtil.Builder(db, "test2") + .setDelimiter("\\t") + .setUseExistingTable(true) + .setHeader(false) + .importFile(new File("test/data/sample-input.tab")); + + expectedRows = + createExpectedTable( + createExpectedRow( + "T1", "Test1", + "T2", "Test2", + "T3", "Test3"), + createExpectedRow( + "T1", "Foo", + "T2", "Bar", + "T3", "Ralph"), + createExpectedRow( + "T1", "S", + "T2", "Mouse", + "T3", "Rocks"), + createExpectedRow( + "T1", "", + "T2", "Partial line", + "T3", null), + createExpectedRow( + "T1", " Quoted Value", + "T2", " bazz ", + "T3", " Really \"Crazy" + ImportUtil.LINE_SEPARATOR + + "value\""), + createExpectedRow( + "T1", "buzz", + "T2", "embedded\tseparator", + "T3", "long") + ); + assertTable(expectedRows, t); + ImportFilter oddFilter = new SimpleImportFilter() { private int _num; @@ -106,8 +149,10 @@ public class ImportTest extends TestCase } }; - tableName = db.importFile( - "test2", new File("test/data/sample-input.tab"), "\\t", oddFilter); + tableName = new ImportUtil.Builder(db, "test3") + .setDelimiter("\\t") + .setFilter(oddFilter) + .importFile(new File("test/data/sample-input.tab")); t = db.getTable(tableName); colNames = new ArrayList(); @@ -141,8 +186,9 @@ public class ImportTest extends TestCase { for (final FileFormat fileFormat : JetFormatTest.SUPPORTED_FILEFORMATS) { Database db = create(fileFormat); - String tableName = db.importFile( - "test", new File("test/data/sample-input-only-headers.tab"), "\\t"); + String tableName = new ImportUtil.Builder(db, "test") + .setDelimiter("\\t") + .importFile(new File("test/data/sample-input-only-headers.tab")); Table t = db.getTable(tableName); @@ -273,7 +319,7 @@ public class ImportTest extends TestCase _precisions.add(precision); } - public T getValue(List values, Object index) { + private static T getValue(List values, Object index) { return values.get((Integer)index - 1); } } -- 2.39.5