From 7556fa9a1600a1b1de1aed4fe4524d55b20236f3 Mon Sep 17 00:00:00 2001 From: James Ahlborn Date: Mon, 7 Dec 2009 03:35:44 +0000 Subject: [PATCH] Refactor import/copy methods from Database into ImportUtil. Allow reuse of existing tables. make behavior/naming of copy and import methods consistent. (ideas from use submitted patch). git-svn-id: https://svn.code.sf.net/p/jackcess/code/jackcess/trunk@421 f203690c-595d-4dc9-a70b-905162fa7fd2 --- src/changes/changes.xml | 5 + .../jackcess/Database.java | 181 ++++------ .../jackcess/ImportUtil.java | 340 ++++++++++++++++++ 3 files changed, 407 insertions(+), 119 deletions(-) create mode 100644 src/java/com/healthmarketscience/jackcess/ImportUtil.java diff --git a/src/changes/changes.xml b/src/changes/changes.xml index 0760dc4..a9ba17b 100644 --- a/src/changes/changes.xml +++ b/src/changes/changes.xml @@ -12,6 +12,11 @@ Handler more binary/character input types (Blob, Clob, InputStream, Reader), based on user submitted patch. + + Refactor import/copy methods from Database into ImportUtil. Allow + reuse of existing tables. make behavior/naming of copy and import + methods consistent. (ideas from use submitted patch). + diff --git a/src/java/com/healthmarketscience/jackcess/Database.java b/src/java/com/healthmarketscience/jackcess/Database.java index d0aa45b..8609b65 100644 --- a/src/java/com/healthmarketscience/jackcess/Database.java +++ b/src/java/com/healthmarketscience/jackcess/Database.java @@ -31,7 +31,6 @@ import java.io.BufferedReader; import java.io.Closeable; import java.io.File; import java.io.FileNotFoundException; -import java.io.FileReader; import java.io.Flushable; import java.io.IOException; import java.io.RandomAccessFile; @@ -39,7 +38,6 @@ import java.nio.ByteBuffer; import java.nio.channels.Channels; import java.nio.channels.FileChannel; import java.sql.ResultSet; -import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; @@ -50,7 +48,6 @@ import java.util.EnumSet; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; -import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.NoSuchElementException; @@ -119,9 +116,6 @@ public class Database } }; - /** Batch commit size for copying other result sets into this database */ - private static final int COPY_TABLE_BATCH_SIZE = 200; - /** System catalog always lives on page 2 */ private static final int PAGE_SYSTEM_CATALOG = 2; /** Name of the system catalog */ @@ -280,7 +274,13 @@ public class Database * Open an existing Database. If the existing file is not writeable, the * file will be opened read-only. Auto-syncing is enabled for the returned * Database. + *

+ * Equivalent to: + * {@code open(mdbFile, false);} + * * @param mdbFile File containing the database + * + * @see #open(File,boolean) */ public static Database open(File mdbFile) throws IOException { return open(mdbFile, false); @@ -290,9 +290,15 @@ public class Database * Open an existing Database. If the existing file is not writeable or the * readOnly flag is true, the file will be opened read-only. * Auto-syncing is enabled for the returned Database. + *

+ * Equivalent to: + * {@code open(mdbFile, readOnly, DEFAULT_AUTO_SYNC);} + * * @param mdbFile File containing the database * @param readOnly iff true, force opening file in read-only * mode + * + * @see #open(File,boolean,boolean) */ public static Database open(File mdbFile, boolean readOnly) throws IOException @@ -328,8 +334,14 @@ public class Database /** * Create a new Database + *

+ * Equivalent to: + * {@code create(mdbFile, DEFAULT_AUTO_SYNC);} + * * @param mdbFile Location to write the new database to. If this file * already exists, it will be overwritten. + * + * @see #create(File,boolean) */ public static Database create(File mdbFile) throws IOException { return create(mdbFile, DEFAULT_AUTO_SYNC); @@ -888,117 +900,88 @@ public class Database /** * Copy an existing JDBC ResultSet into a new table in this database + * * @param name Name of the new table to create * @param source ResultSet to copy from + * + * @return the name of the copied table + * + * @see ImportUtil#importResultSet(ResultSet,Database,String) */ - public void copyTable(String name, ResultSet source) + public String copyTable(String name, ResultSet source) throws SQLException, IOException { - copyTable(name, source, SimpleImportFilter.INSTANCE); + return ImportUtil.importResultSet(source, this, name); } /** * Copy an existing JDBC ResultSet into a new table in this database + * * @param name Name of the new table to create * @param source ResultSet to copy from * @param filter valid import filter + * + * @return the name of the imported table + * + * @see ImportUtil#importResultSet(ResultSet,Database,String,ImportFilter) */ - public void copyTable(String name, ResultSet source, ImportFilter filter) + public String copyTable(String name, ResultSet source, ImportFilter filter) throws SQLException, IOException { - ResultSetMetaData md = source.getMetaData(); - List columns = new LinkedList(); - for (int i = 1; i <= md.getColumnCount(); i++) { - Column column = new Column(); - column.setName(escape(md.getColumnName(i))); - int lengthInUnits = md.getColumnDisplaySize(i); - column.setSQLType(md.getColumnType(i), lengthInUnits); - DataType type = column.getType(); - // we check for isTrueVariableLength here to avoid setting the length - // for a NUMERIC column, which pretends to be var-len, even though it - // isn't - if(type.isTrueVariableLength() && !type.isLongValue()) { - column.setLengthInUnits((short)lengthInUnits); - } - if(type.getHasScalePrecision()) { - int scale = md.getScale(i); - int precision = md.getPrecision(i); - if(type.isValidScale(scale)) { - column.setScale((byte)scale); - } - if(type.isValidPrecision(precision)) { - column.setPrecision((byte)precision); - } - } - columns.add(column); - } - createTable(escape(name), filter.filterColumns(columns, md)); - Table table = getTable(escape(name)); - List rows = new ArrayList(COPY_TABLE_BATCH_SIZE); - while (source.next()) { - Object[] row = new Object[md.getColumnCount()]; - for (int i = 0; i < row.length; i++) { - row[i] = source.getObject(i + 1); - } - rows.add(filter.filterRow(row)); - if (rows.size() == COPY_TABLE_BATCH_SIZE) { - table.addRows(rows); - rows.clear(); - } - } - if (rows.size() > 0) { - table.addRows(rows); - } + return ImportUtil.importResultSet(source, this, name, filter); } /** * Copy a delimited text file into a new table in this database + * * @param name Name of the new table to create * @param f Source file to import * @param delim Regular expression representing the delimiter string. + * + * @return the name of the imported table + * + * @see ImportUtil#importFile(File,Database,String,String) */ - public void importFile(String name, File f, String delim) + public String importFile(String name, File f, String delim) throws IOException { - importFile(name, f, delim, SimpleImportFilter.INSTANCE); + return ImportUtil.importFile(f, this, name, delim); } /** * Copy a delimited text file into a new table in this database + * * @param name Name of the new table to create * @param f Source file to import * @param delim Regular expression representing the delimiter string. * @param filter valid import filter + * + * @return the name of the imported table + * + * @see ImportUtil#importFile(File,Database,String,String,ImportFilter) */ - public void importFile(String name, File f, String delim, - ImportFilter filter) + public String importFile(String name, File f, String delim, + ImportFilter filter) throws IOException { - BufferedReader in = null; - try { - in = new BufferedReader(new FileReader(f)); - importReader(name, in, delim, filter); - } finally { - if (in != null) { - try { - in.close(); - } catch (IOException ex) { - LOG.warn("Could not close file " + f.getAbsolutePath(), ex); - } - } - } + return ImportUtil.importFile(f, this, name, delim, filter); } /** * Copy a delimited text file into a new 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. + * + * @return the name of the imported table + * + * @see ImportUtil#importReader(BufferedReader,Database,String,String) */ - public void importReader(String name, BufferedReader in, String delim) + public String importReader(String name, BufferedReader in, String delim) throws IOException { - importReader(name, in, delim, SimpleImportFilter.INSTANCE); + return ImportUtil.importReader(in, this, name, delim); } /** @@ -1007,56 +990,16 @@ public class Database * @param in Source reader to import * @param delim Regular expression representing the delimiter string. * @param filter valid import filter + * + * @return the name of the imported table + * + * @see ImportUtil#importReader(BufferedReader,Database,String,String,ImportFilter) */ - public void importReader(String name, BufferedReader in, String delim, - ImportFilter filter) + public String importReader(String name, BufferedReader in, String delim, + ImportFilter filter) throws IOException { - String line = in.readLine(); - if (line == null || line.trim().length() == 0) { - return; - } - - String tableName = escape(name); - int counter = 0; - while(getTable(tableName) != null) { - tableName = escape(name + (counter++)); - } - - List columns = new LinkedList(); - String[] columnNames = line.split(delim); - - for (int i = 0; i < columnNames.length; i++) { - columns.add(new ColumnBuilder(escape(columnNames[i]), DataType.TEXT) - .setLength((short)DataType.TEXT.getMaxSize()) - .toColumn()); - } - - try { - createTable(tableName, filter.filterColumns(columns, null)); - Table table = getTable(tableName); - List rows = new ArrayList(COPY_TABLE_BATCH_SIZE); - - while ((line = in.readLine()) != null) - { - // - // Handle the situation where the end of the line - // may have null fields. We always want to add the - // same number of columns to the table each time. - // - Object[] data = Table.dupeRow(line.split(delim), columnNames.length); - rows.add(filter.filterRow(data)); - if (rows.size() == COPY_TABLE_BATCH_SIZE) { - table.addRows(rows); - rows.clear(); - } - } - if (rows.size() > 0) { - table.addRows(rows); - } - } catch(SQLException e) { - throw (IOException)new IOException(e.getMessage()).initCause(e); - } + return ImportUtil.importReader(in, this, name, delim, filter); } /** @@ -1076,7 +1019,7 @@ public class Database /** * @return A table or column name escaped for Access */ - private String escape(String s) { + static String escape(String s) { if (isReservedWord(s)) { return ESCAPE_PREFIX + s; } diff --git a/src/java/com/healthmarketscience/jackcess/ImportUtil.java b/src/java/com/healthmarketscience/jackcess/ImportUtil.java new file mode 100644 index 0000000..8aa7ed6 --- /dev/null +++ b/src/java/com/healthmarketscience/jackcess/ImportUtil.java @@ -0,0 +1,340 @@ +// Copyright (c) 2009 Boomi, Inc. + +package com.healthmarketscience.jackcess; + +import java.io.BufferedReader; +import java.io.File; +import java.io.FileReader; +import java.io.IOException; +import java.sql.ResultSet; +import java.sql.ResultSetMetaData; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.LinkedList; +import java.util.List; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; + +/** + * + * @author James Ahlborn + */ +public class ImportUtil +{ + + private static final Log LOG = LogFactory.getLog(ImportUtil.class); + + /** Batch commit size for copying other result sets into this database */ + private static final int COPY_TABLE_BATCH_SIZE = 200; + + private ImportUtil() {} + + /** + * Copy an existing JDBC ResultSet into a new table in this database + *

+ * Equivalent to: + * {@code importResultSet(source, db, name, SimpleImportFilter.INSTANCE);} + * + * @param name Name of the new table to create + * @param source ResultSet to copy from + * + * @return the name of the copied table + * + * @see #importResultSet(ResultSet,Database,String,ImportFilter) + */ + public static String importResultSet(ResultSet source, Database db, + String name) + throws SQLException, IOException + { + return importResultSet(source, db, name, SimpleImportFilter.INSTANCE); + } + + /** + * Copy an existing JDBC ResultSet into a new table in this database + *

+ * Equivalent to: + * {@code importResultSet(source, db, name, filter, false);} + * + * @param name Name of the new table to create + * @param source ResultSet to copy from + * @param filter valid import filter + * + * @return the name of the imported table + * + * @see #importResultSet(ResultSet,Database,String,ImportFilter,boolean) + */ + public static String importResultSet(ResultSet source, Database db, + String name, ImportFilter filter) + throws SQLException, IOException + { + return importResultSet(source, db, name, filter, false); + } + + /** + * Copy an existing JDBC ResultSet into a new (or optionally existing) table + * in this database + * @param name Name of the new table to create + * @param source ResultSet to copy from + * @param filter valid import filter + * @param useExistingTable if {@code true} use current table if it already + * exists, otherwise, create new table with unique + * name + * + * @return the name of the imported table + */ + public static String importResultSet(ResultSet source, Database db, + String name, ImportFilter filter, + boolean useExistingTable) + throws SQLException, IOException + { + ResultSetMetaData md = source.getMetaData(); + + name = Database.escape(name); + Table table = null; + if(!useExistingTable || ((table = db.getTable(name)) == null)) { + + + List columns = new LinkedList(); + for (int i = 1; i <= md.getColumnCount(); i++) { + Column column = new Column(); + column.setName(Database.escape(md.getColumnName(i))); + int lengthInUnits = md.getColumnDisplaySize(i); + column.setSQLType(md.getColumnType(i), lengthInUnits); + DataType type = column.getType(); + // we check for isTrueVariableLength here to avoid setting the length + // for a NUMERIC column, which pretends to be var-len, even though it + // isn't + if(type.isTrueVariableLength() && !type.isLongValue()) { + column.setLengthInUnits((short)lengthInUnits); + } + if(type.getHasScalePrecision()) { + int scale = md.getScale(i); + int precision = md.getPrecision(i); + if(type.isValidScale(scale)) { + column.setScale((byte)scale); + } + if(type.isValidPrecision(precision)) { + column.setPrecision((byte)precision); + } + } + columns.add(column); + } + + table = createUniqueTable(db, name, columns, md, filter); + } + + List rows = new ArrayList(COPY_TABLE_BATCH_SIZE); + int numColumns = md.getColumnCount(); + + while (source.next()) { + Object[] row = new Object[numColumns]; + for (int i = 0; i < row.length; i++) { + row[i] = source.getObject(i + 1); + } + rows.add(filter.filterRow(row)); + if (rows.size() == COPY_TABLE_BATCH_SIZE) { + table.addRows(rows); + rows.clear(); + } + } + if (rows.size() > 0) { + table.addRows(rows); + } + + return table.getName(); + } + + /** + * Copy a delimited text file into a new table in this database + *

+ * Equivalent to: + * {@code importFile(f, name, db, delim, SimpleImportFilter.INSTANCE);} + * + * @param name Name of the new table to create + * @param f Source file to import + * @param delim Regular expression representing the delimiter string. + * + * @return the name of the imported table + * + * @see #importFile(File,Database,String,String,ImportFilter) + */ + public static String importFile(File f, Database db, String name, + String delim) + throws IOException + { + return importFile(f, db, name, delim, SimpleImportFilter.INSTANCE); + } + + /** + * 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);} + * + * @param name Name of the new table to create + * @param f Source file to import + * @param delim Regular expression representing the delimiter string. + * @param filter valid import filter + * + * @return the name of the imported table + * + * @see #importReader(BufferedReader,Database,String,String,ImportFilter) + */ + public static String importFile(File f, Database db, String name, + String delim, ImportFilter filter) + throws IOException + { + BufferedReader in = null; + try { + in = new BufferedReader(new FileReader(f)); + return importReader(in, db, name, delim, filter); + } finally { + if (in != null) { + try { + in.close(); + } catch (IOException ex) { + LOG.warn("Could not close file " + f.getAbsolutePath(), ex); + } + } + } + } + + /** + * Copy a delimited text file into a new table in this database + *

+ * Equivalent to: + * {@code importReader(in, db, name, delim, SimpleImportFilter.INSTANCE);} + * + * @param name Name of the new table to create + * @param in Source reader to import + * @param delim Regular expression representing the delimiter string. + * + * @return the name of the imported table + * + * @see #importReader(BufferedReader,Database,String,String,ImportFilter) + */ + public static String importReader(BufferedReader in, Database db, + String name, String delim) + throws IOException + { + return importReader(in, db, name, delim, SimpleImportFilter.INSTANCE); + } + + /** + * Copy a delimited text file into a new table in this database + *

+ * Equivalent to: + * {@code importReader(in, db, name, delim, filter, false);} + * + * @param name Name of the new table to create + * @param in Source reader to import + * @param delim Regular expression representing the delimiter string. + * @param filter valid import filter + * + * @return the name of the imported table + * + * @see #importReader(BufferedReader,Database,String,String,ImportFilter,boolean) + */ + public static String importReader(BufferedReader in, Database db, + String name, String delim, + ImportFilter filter) + throws IOException + { + return importReader(in, db, name, delim, filter, false); + } + + /** + * 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 filter valid import filter + * @param useExistingTable if {@code true} use current table if it already + * exists, otherwise, create new table with unique + * name + * + * @return the name of the imported table + */ + public static String importReader(BufferedReader in, Database db, + String name, String delim, + ImportFilter filter, + boolean useExistingTable) + throws IOException + { + String line = in.readLine(); + if (line == null || line.trim().length() == 0) { + return null; + } + + try { + name = Database.escape(name); + Table table = null; + if(!useExistingTable || ((table = db.getTable(name)) == null)) { + + List columns = new LinkedList(); + String[] columnNames = line.split(delim); + + for (int i = 0; i < columnNames.length; i++) { + columns.add(new ColumnBuilder(Database.escape(columnNames[i]), + DataType.TEXT) + .setLength((short)DataType.TEXT.getMaxSize()) + .toColumn()); + } + + table = createUniqueTable(db, name, columns, null, filter); + } + + List rows = new ArrayList(COPY_TABLE_BATCH_SIZE); + int numColumns = table.getColumnCount(); + + while ((line = in.readLine()) != null) + { + // + // Handle the situation where the end of the line + // may have null fields. We always want to add the + // same number of columns to the table each time. + // + Object[] data = Table.dupeRow(line.split(delim), numColumns); + rows.add(filter.filterRow(data)); + if (rows.size() == COPY_TABLE_BATCH_SIZE) { + table.addRows(rows); + rows.clear(); + } + } + if (rows.size() > 0) { + table.addRows(rows); + } + + return table.getName(); + + } catch(SQLException e) { + throw (IOException)new IOException(e.getMessage()).initCause(e); + } + } + + /** + * Returns a new table with a unique name and the given table definition. + */ + private static Table createUniqueTable(Database db, String name, + List columns, + ResultSetMetaData md, + ImportFilter filter) + throws IOException, SQLException + { + // otherwise, find unique name and create new table + String baseName = name; + int counter = 2; + while(db.getTable(name) != null) { + name = baseName + (counter++); + } + + db.createTable(name, filter.filterColumns(columns, md)); + + return db.getTable(name); + } + + + +} -- 2.39.5