Class DefaultTableLoader

  • All Implemented Interfaces:
    java.lang.AutoCloseable

    public class DefaultTableLoader
    extends java.lang.Object
    implements java.lang.AutoCloseable
    Class used to process an input stream and fill a table with the data.
    Author:
    Christopher Mindus
    • Field Summary

      Fields 
      Modifier and Type Field Description
      protected java.lang.String[] columns
      The column names.
      java.util.regex.Pattern commaPattern
      The comma delimiter string pattern "[whitespace],[whitespace]": '\s*,\s*'.
      protected java.sql.Connection conn
      The connection.
      java.util.regex.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.
      java.util.regex.Pattern dateTimeSeparatorPattern
      Pattern for date/time/timestamp separator characters '-/:.' and whitespace: '[-/:.\s]+'.
      int DEFAULT_BATCH_COUNT
      The default batch count of transactions before a batch-commit is done when inserting multiple rows.
      java.util.regex.Pattern escapedQuotedStringPattern
      The Quoted String string pattern as: 'single \'quoted\' string', "double 'quoted' string", "double \"quoted\" string" The pattern is: '(['"])(?:.*?)(?<!\\)(?>\\\\)*?\1'.
      protected java.sql.PreparedStatement insertStatement
      The prepared statement.
      protected java.sql.ResultSetMetaData metaData
      The meta data for the table columns.
      java.util.regex.Pattern nullPattern
      The "NULL" string pattern with whitespace ignored around it, case insensitive.
      java.util.regex.Pattern quotedStringPattern
      The Quoted String pattern '"string where ""doubled-double-quotes"" are made double-quotes"': '"((?:""|[^"])*)"'.
      protected java.io.BufferedReader reader
      The reader.
      protected java.util.Map<java.lang.String,​java.lang.String> replace
      The replacement strings.
      protected java.util.regex.Pattern stringPattern
      The pattern for quoted strings: MUST BE one of: {@link #quotedStringPattern} or {@link #escapedQuotedStringPattern}.
      java.util.regex.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.
      java.util.regex.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.
      java.util.regex.Pattern whitespacePattern
      Whitespace pattern.
    • Constructor Summary

      Constructors 
      Constructor Description
      DefaultTableLoader​(java.io.InputStream in, java.nio.charset.Charset charSet, java.sql.Connection conn, java.lang.String tableName, boolean clearBefore, java.lang.String... columns)
      Creates the table loader.
      DefaultTableLoader​(java.io.Reader reader, java.sql.Connection conn, java.lang.String tableName, boolean clearBefore, java.lang.String... columns)
      Creates the table loader.
    • Method Summary

      All Methods Instance Methods Concrete Methods 
      Modifier and Type Method Description
      void close()
      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.
      java.sql.ResultSetMetaData getMetaData()
      Gets the meta data for the columns of the table.
      protected void processLine​(java.lang.String line)
      Processes a line of data.
      void processReader()
      Processes the reader.
      void processReader​(int batchCount)
      Processes the reader.
      protected java.lang.String scanLineToStatement​(java.lang.String line, java.util.Scanner scanner)
      Retrieves and sets all the column values in the prepared statement using the scanner.
      void setStringPattern​(java.util.regex.Pattern stringPattern)
      The pattern for quoted strings: MUST BE one of: {@link #quotedStringPattern} or {@link #escapedQuotedStringPattern}.
      void setStringReplacement​(java.util.Map<java.lang.String,​java.lang.String> replace)
      Adds a potential string replacement, but default it is the two characters "\n" inside a double-quoted string.
      • Methods inherited from class java.lang.Object

        clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
    • Field Detail

      • nullPattern

        public final java.util.regex.Pattern nullPattern
        The "NULL" string pattern with whitespace ignored around it, case insensitive.
      • commaPattern

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

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

        public final java.util.regex.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 java.util.regex.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 java.util.regex.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 java.util.regex.Pattern escapedQuotedStringPattern
        The Quoted String string pattern as:
        • 'single \'quoted\' string',
        • "double 'quoted' string",
        • "double \"quoted\" string"
        The pattern is: '(['"])(?:.*?)(?<!\\)(?>\\\\)*?\1'.
      • whitespacePattern

        public final java.util.regex.Pattern whitespacePattern
        Whitespace pattern.
      • dateTimeSeparatorPattern

        public final java.util.regex.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:
        Constant Field Values
      • reader

        protected final java.io.BufferedReader reader
        The reader.
      • insertStatement

        protected final java.sql.PreparedStatement insertStatement
        The prepared statement.
      • conn

        protected final java.sql.Connection conn
        The connection.
      • metaData

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

        protected final java.lang.String[] columns
        The column names.
      • replace

        protected java.util.Map<java.lang.String,​java.lang.String> replace
        The replacement strings.
      • stringPattern

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

      • DefaultTableLoader

        public DefaultTableLoader​(java.io.InputStream in,
                                  java.nio.charset.Charset charSet,
                                  java.sql.Connection conn,
                                  java.lang.String tableName,
                                  boolean clearBefore,
                                  java.lang.String... columns)
                           throws java.sql.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:
        java.lang.NullPointerException - If any parameter is null.
        java.lang.IllegalArgumentException - If the columns are not specified.
        java.sql.SQLException - For SQL errors.
      • DefaultTableLoader

        public DefaultTableLoader​(java.io.Reader reader,
                                  java.sql.Connection conn,
                                  java.lang.String tableName,
                                  boolean clearBefore,
                                  java.lang.String... columns)
                           throws java.sql.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:
        java.lang.NullPointerException - If any parameter is null.
        java.lang.IllegalArgumentException - If the columns are not specified.
        java.sql.SQLException - For SQL errors.
    • Method Detail

      • getMetaData

        public java.sql.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 java.io.IOException,
                          java.sql.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 java.lang.AutoCloseable
        Throws:
        java.io.IOException - For I/O errors.
        java.sql.SQLException - For SQL errors.
      • setStringReplacement

        public void setStringReplacement​(java.util.Map<java.lang.String,​java.lang.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:
        java.lang.NullPointerException - If replace is null.
      • setStringPattern

        public void setStringPattern​(java.util.regex.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:
        java.lang.IllegalArgumentException - If stringPattern is not one of the values {@link #quotedStringPattern} or {@link #escapedQuotedStringPattern}.
      • processReader

        public void processReader()
                           throws java.io.IOException,
                                  java.sql.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:
        java.io.IOException - For stream reader I/O error.
        java.sql.SQLException - For SQL errors.
      • processReader

        public void processReader​(int batchCount)
                           throws java.io.IOException,
                                  java.sql.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:
        java.io.IOException - For stream reader I/O error.
        java.sql.SQLException - For SQL errors.
      • processLine

        protected void processLine​(java.lang.String line)
                            throws java.io.IOException,
                                   java.sql.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:
        java.io.IOException - For stream reader I/O error.
        java.sql.SQLException - For SQL errors.
      • scanLineToStatement

        protected java.lang.String scanLineToStatement​(java.lang.String line,
                                                       java.util.Scanner scanner)
                                                throws java.io.IOException,
                                                       java.sql.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:
        java.io.IOException - For stream reader I/O error.
        java.sql.SQLException - For SQL errors.