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.

Author:
Christopher Mindus

  • Field Details Link icon

    • nullPattern Link icon

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

    • timePattern Link icon

      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 Link icon

      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 Link icon

      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 Link icon

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

    • DEFAULT_BATCH_COUNT Link icon

      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 Link icon

      protected final CSVParser parser
      The CSV parser.

    • insertStatement Link icon

      protected final PreparedStatement insertStatement
      The prepared statement.

    • conn Link icon

      protected final Connection conn
      The connection.

    • columns Link icon

      protected final String[] columns
      The column names.

  • Constructor Details Link icon

    • DefaultTableLoader Link icon

      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 Link icon

      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 Link icon

    • createParser Link icon

      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 Link icon

      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 Link icon

      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 Link icon

      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 Link icon

      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.