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.

The input streams are built up of comma separated values, where strings are quoted (and to include a quote in a string, it must be specified twice). NULL values should specify the string NULL only. All other values should respect their SQL representation type for the table in columns question.

The string quotation type may be modified using

invalid @link
#setStringPattern(Pattern)
where Pattern may the one of the following:
invalid @link
#quotedStringPattern
or
invalid @link
#escapedQuotedStringPattern
.
Author:
Christopher Mindus

  • Field Details

    • nullPattern

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

    • timePattern

      public static 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 static 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 static 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 one or two digits between 01 and 12, DD is one or 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. hh can be a single digit, but mm and nn must be two digits. '[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})?'.

    • dateTimeSeparatorPattern

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

    • DEFAULT_BATCH_COUNT

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

    • parser

      protected final CSVParser parser
      The CSV parser.

    • insertStatement

      protected final PreparedStatement insertStatement
      The prepared statement.

    • conn

      protected final Connection conn
      The connection.

    • columns

      protected final String[] columns
      The column names.

  • Constructor Details

    • DefaultTableLoader

      public DefaultTableLoader(InputStream in, Charset charSet, Connection conn, String tableName, boolean clearBefore, String... columns) throws SQLException, IOException
      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.
      IOException - For I/O errors.

    • DefaultTableLoader

      public DefaultTableLoader(Reader reader, Connection conn, String tableName, boolean clearBefore, String... columns) throws SQLException, IOException
      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.
      IOException - For I/O errors.

  • Method Details

    • createParser

      public CSVParser createParser(BufferedReader reader) throws IOException
      Creates the CSV parser.
      Parameters:
      reader - The buffered reader.
      Returns:
      The CSV parser, the default is new CSVParser(in,CSVFormat.ORACLE).
      Throws:
      IOException - For I/O errors.

    • 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.

    • 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(CSVRecord rec) throws IOException, SQLException
      Retrieves and sets all the column values in the prepared statement using the CSVRecord.

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

      Parameters:
      rec - The CSV record.
      Throws:
      IOException - For stream reader I/O error.
      SQLException - For SQL errors.