Package com.iizix.jdbc

Class DefaultTableLoader

java.lang.Object
com.iizix.jdbc.DefaultTableLoader
All Implemented Interfaces:
AutoCloseable
public class DefaultTableLoader extends Object implements AutoCloseable
Class used to process an input stream and fill a table with the data.
Author:
Christopher Mindus

  • Field Details

    • nullPattern

      public final Pattern nullPattern
      The "NULL" string pattern with whitespace ignored around it, case insensitive.

    • commaPattern

      public final Pattern commaPattern
      The comma delimiter string pattern "[whitespace],[whitespace]": '\s*,\s*'.

    • quotedStringPattern

      public final Pattern quotedStringPattern
      The Quoted String pattern '"string where ""doubled-double-quotes"" are made double-quotes"': '"((?:""|[^"])*)"'.

    • timePattern

      public final Pattern timePattern
      Matches a valid 24-hour time from 00:00:00-23:59:59 in hh:mm:ss or hh.mm.ss formats: '([01]?\d|2[0-3])[:.]([0-5]\d)[:.]([0-5]\d)', and allows time as e.g. 10:12:12, 13.12.59, 13:56:00 or 8.14.00.

    • datePattern

      public final Pattern datePattern
      Matches a date as YYYY-MM-DD or YYYY/MM/DD where YYYY is 4 digits between 1000 and 2999, MM is two digits between 01 and 12, DD is two digits between 01 and 31. '[12]\d{3}[-/](0[1-9]|1[0-2])[-/](0[1-9]|[12]\d|3[01])'.

    • timestampPattern

      public final Pattern timestampPattern
      Matches a timestamp as "YYYY-MM-DD hh:mm:ss.fff" or "YYYY/MM/DD hh.mm.ss.fffffffff" where YYYY is 4 digits between 1000 and 2999, MM is two digits between 01 and 12, DD is two digits between 01 and 31. The time part is a valid 24-hour time from 00:00:00-23:59:59 in hh:mm:ss or hh.mm.ss formats with fractional nanoseconds in 'fffffffff' that may be omitted. '[12]\d{3}[-/](0[1-9]|1[0-2])[-/](0[1-9]|[12]\d|3[01])\s+([01]?\d|2[0-3])[:.]([0-5]\d)[:.]([0-5]\d)(\.[0-9]{1,9})?'.

    • escapedQuotedStringPattern

      public final Pattern escapedQuotedStringPattern
      The Quoted String string pattern as:
      • 'single \'quoted\' string',
      • "double 'quoted' string",
      • "double \"quoted\" string"
      The pattern is: '(['"])(?:.*?)(?<!\\)(?>\\\\)*?\1'.

    • whitespacePattern

      public final Pattern whitespacePattern
      Whitespace pattern.

    • dateTimeSeparatorPattern

      public final Pattern dateTimeSeparatorPattern
      Pattern for date/time/timestamp separator characters '-/:.' and whitespace: '[-/:.\s]+'.

    • DEFAULT_BATCH_COUNT

      public final int DEFAULT_BATCH_COUNT
      The default batch count of transactions before a batch-commit is done when inserting multiple rows.
      See Also:

    • reader

      protected final BufferedReader reader
      The reader.

    • insertStatement

      protected final PreparedStatement insertStatement
      The prepared statement.

    • conn

      protected final Connection conn
      The connection.

    • metaData

      protected final ResultSetMetaData metaData
      The meta data for the table columns.

    • columns

      protected final String[] columns
      The column names.

    • replace

      protected Map<String,String> replace
      The replacement strings.

    • stringPattern

      protected Pattern stringPattern
      The pattern for quoted strings: MUST BE one of:
      • {@link #quotedStringPattern} or
      • {@link #escapedQuotedStringPattern}.
      The default is {@link #quotedStringPattern}.

  • Constructor Details

    • DefaultTableLoader

      public DefaultTableLoader(InputStream in, Charset charSet, Connection conn, String tableName, boolean clearBefore, String... columns) throws SQLException
      Creates the table loader.
      Parameters:
      in - The input stream.
      charSet - The character set to use.
      conn - The JDBC connection: the connection is never closed by this class. It is up to the called to close it. A final commit() will be performed however.
      tableName - The table name.
      clearBefore - Flag to clear the table of all rows prior to inserting the data. Please note that this flag sometime cannot be true if there are constraints on e.g. other tables.
      columns - The columns.
      Throws:
      NullPointerException - If any parameter is null.
      IllegalArgumentException - If the columns are not specified.
      SQLException - For SQL errors.

    • DefaultTableLoader

      public DefaultTableLoader(Reader reader, Connection conn, String tableName, boolean clearBefore, String... columns) throws SQLException
      Creates the table loader.
      Parameters:
      reader - The reader.
      conn - The JDBC connection: the connection is never closed by this class. It is up to the called to close it. A final commit() will be performed however.
      tableName - The table name.
      clearBefore - Flag to clear the table of all rows prior to inserting the data. Please note that this flag sometime cannot be true if there are constraints on e.g. other tables.
      columns - The field names, leave empty in case the data contains all fields in correct ordering, thus indicies can be used instead of the names.
      Throws:
      NullPointerException - If any parameter is null.
      IllegalArgumentException - If the columns are not specified.
      SQLException - For SQL errors.

  • Method Details

    • getMetaData

      public ResultSetMetaData getMetaData()
      Gets the meta data for the columns of the table.
      Returns:
      The meta data of the result set from SELECT column1, column2, ... FROM tableName requested before rows are inserted into the table, but after a potential clear of the table.

    • close

      public void close() throws IOException, SQLException
      Closes the input stream or reader and the prepared SQL statement for row insertion: both are attempted to be closed, and if the input stream or reader throws an IOException the SQL statement will be closed. If that one throws an SQLException, the potential IOException will be added as a suppressed exception. Otherwise the potential IOException is thrown.
      Specified by:
      close in interface AutoCloseable
      Throws:
      IOException - For I/O errors.
      SQLException - For SQL errors.

    • setStringReplacement

      public void setStringReplacement(Map<String,String> replace)
      Adds a potential string replacement, but default it is the two characters "\n" inside a double-quoted string. Doubled double-quotes inside such a string is replaced with a single double-quote.

      The default replace map would be as:

       replace=new HashMap<>(2);
 replace.put("\\n","\r\n");
 replace.put("\"\"","\"");

      Parameters:
      replace - The map for String replacement.
      Throws:
      NullPointerException - If replace is null.

    • setStringPattern

      public void setStringPattern(Pattern stringPattern)
      The pattern for quoted strings: MUST BE one of:
      • {@link #quotedStringPattern} or
      • {@link #escapedQuotedStringPattern}.
      The default is {@link #quotedStringPattern}.
      Parameters:
      stringPattern - The new String Pattern to use.
      Throws:
      IllegalArgumentException - If stringPattern is not one of the values {@link #quotedStringPattern} or {@link #escapedQuotedStringPattern}.

    • processReader

      public void processReader() throws IOException, SQLException
      Processes the reader.

      A subclass can override this method to perform specialized reader handling. The default is to read all lines until EOF and call {@link #processLine(line)} for each line, skipping empty lines. Each line produces a new row in the table, then the prepared insert statement's parameters are cleared (before next row is added).

      Throws:
      IOException - For stream reader I/O error.
      SQLException - For SQL errors.

    • processReader

      public void processReader(int batchCount) throws IOException, SQLException
      Processes the reader.

      A subclass can override this method to perform specialized reader handling. The default is to read all lines until EOF and call {@link #processLine(line)} for each line, skipping empty lines. Each line produces a new row in the table, then the prepared insert statement's parameters are cleared (before next row is added).

      Throws:
      IOException - For stream reader I/O error.
      SQLException - For SQL errors.

    • processLine

      protected void processLine(String line) throws IOException, SQLException
      Processes a line of data.

      A subclass can override this method to perform specialized line parsing to values.

      Parameters:
      line - The line, never empty string.
      Throws:
      IOException - For stream reader I/O error.
      SQLException - For SQL errors.

    • scanLineToStatement

      protected String scanLineToStatement(String line, Scanner scanner) throws IOException, SQLException
      Retrieves and sets all the column values in the prepared statement using the scanner.

      Subclasses can override this method to provide the line scanning to set the columns with its data using the indicies of the columns.

      Parameters:
      line - The line being scanned.
      scanner - The scanner.
      Returns:
      A warning string that will be logged, null for none.
      Throws:
      IOException - For stream reader I/O error.
      SQLException - For SQL errors.