Package chemaxon.jchem.db

Class UpdateHandler

java.lang.Object
chemaxon.util.ErrorHandler
chemaxon.jchem.db.UpdateHandler
All Implemented Interfaces:
TableTypeConstants
@PublicApi public class UpdateHandler extends ErrorHandler implements TableTypeConstants
Provides methods for handling structure tables, e.g.:
  • creating tables
  • updating and inserting rows
  • dropping tables
Both fix columns (the ones which have "cd_" prefix in their names) and additional columns can be set using the class.

WARNING!
For optimization reasons logs are buffered in UpdateHandler from JChem 5.4.
When you use transactions saveUpdateLogs() must be called in order to flush buffers before you commit!
(close() method performs a call to saveUpdateLogs(), no extra calls are needed.)

Example for insert/update: 

    int id;          // cd_id value of compound
    // Additional columns:
    String name;
    Float stock;
    String comments;
    ...
    UpdateHandler uh = new UpdateHandler(conh,
            (isInsertion? UdateHandler.INSERT : UpdateHandler.UPDATE),
            structureTableName, "name, stock, comments");
    try {
        uh.setStructure(molfile);
        if(!isInsertion) {
            uh.setID(id);
        }
        uh.setValueForAdditionalColumn(1, name);
        uh.setValueForAdditionalColumn(2, stock);
        uh.setValueForAdditionalColumn(3, comments);
        uh.execute();
    } finally {
        uh.close();
    }
    ...

  • Field Details

    • UPDATE

      public static final int UPDATE
      Row modification.
      See Also:

    • INSERT

      public static final int INSERT
      Row insertion with automatic cd_id value.
      See Also:

    • INSERT_WITH_ID

      public static final int INSERT_WITH_ID
      Row insertion with cd_id value set by the application.
      See Also:

    • DUPLICATE_FILTERING_ON

      public static final int DUPLICATE_FILTERING_ON
      Constant for 'duplicate filtering' option on import. This value switches on the option.
      See Also:

    • DUPLICATE_FILTERING_OFF

      public static final int DUPLICATE_FILTERING_OFF
      Constant for 'duplicate filtering' option on import. This value switches off the option.
      See Also:

    • DUPLICATE_FILTERING_TABLE_OPTION

      public static final int DUPLICATE_FILTERING_TABLE_OPTION
      Constant for 'duplicate filtering' option on import. This value sets this option as table option.
      See Also:

    • QUERY_TABLE_ENUMERATION_LIMIT

      public static final int QUERY_TABLE_ENUMERATION_LIMIT
      Upper limit for enumerating queries when inserting into QUERY tables
      See Also:

  • Constructor Details

    • UpdateHandler

      public UpdateHandler()
      Zero-arg constructor

    • UpdateHandler

      public UpdateHandler(ConnectionHandler conh, int mode, String tableName, String additionalColumns) throws SQLException
      Constructor.
      Parameters:
      conh - ConnectionHandler opened to the database, containing the name of the JChem property table name.
      mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
      tableName - name of structure table to be modified
      additionalColumns - specify columns that are not created automatically by JChemManager.
      Throws:
      SQLException - if read the table metadata encounters a problem.

    • UpdateHandler

      public UpdateHandler(ConnectionHandler conh, int mode, String tableName, String additionalColumns, String indexedTable, String indexedColumn) throws SQLException
      Constructor. FOR INTERNAL USE ONLY.
      Parameters:
      conh - ConnectionHandler opened to the database, containing the name of the JChem property table name of the property table.
      mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
      tableName - name of structure table to be modified
      additionalColumns - specify columns that are not created automatically by JChemManager.
      indexedTable - the name of indexed table when used with JChem Cartridge for regular structure tables. Must be null for JChem structure tables.
      indexedColumn - the name of indexed column when used with JChem Cartridge for regular structure tables. Must be null for JChem structure tables.
      Throws:
      SQLException - if an error occurs
      Since:
      JChem JChem 3.0

  • Method Details

    • init

      public void init(ConnectionHandler conHandler, int mode, String tableName, String additionalColumns) throws SQLException
      Initializes the object. Useful if UpdateHandler is applied as a JavaBean. It should be called immediately after constructing the object.
      Parameters:
      conHandler - connection handler object.
      mode - possible values UPDATE, INSERT, INSERT_WITH_ID. Not all RDBMS-s support INSERT_WITH_ID if cd_id is assigned to be auto incremented,
      tableName - name of structure table to be modified
      additionalColumns - specify columns that are not created automatically by JChemManager.
      Throws:
      SQLException - if an error occurs
      See Also:

    • isInsertable

      public static boolean isInsertable(Molecule molecule, int tableType, boolean isEmptyStructuresAllowed)
      Decides whether a molecule can be inserted into a table of a given type.
      Parameters:
      molecule - Molecule object to be inserted.
      tableType - The type of the table to be inserted into.
      isEmptyStructuresAllowed - Is empty structure allowed in the table?
      Returns:
      true if the molecule is allowed to be inserted into the given table type.
      Since:
      JChem 3.2.4
      See Also:

    • getMolecule

      public Molecule getMolecule()
      Returns the Molecule that was used during insert or update.
      Returns:
      the structure specified in setValuesForFixColumns( ... ) in standardized form, as it was inserted into the database.
      NOTE: Should be called only after execute().
      Since:
      JChem 2.2.1

    • setROWID

      public void setROWID(String rowID)
      Sets values for all fix columns in cartridge mode if the mode is INSERT_WITH_ID.
      FOR INTERNAL USE ONLY.
      Parameters:
      rowID - the ROWID if the original line in the non-jchem table.

    • setStructure

      public void setStructure(String source)
      Sets the source of the input molecule.
      Parameters:
      source - the source of the structure (molfile, SMILES, MRV, etc.)
      Since:
      JChem 3.2
      See Also:

    • setStructure

      public void setStructure(byte[] source)
      Sets the source of the input molecule.
      Parameters:
      source - the source of the structure (molfile, SMILES, MRV, etc.)
      Since:
      JChem 3.2
      See Also:

    • setID

      public void setID(int id)
      Sets the cd_id value for the structure. Effective in UPDATE or INSERT_WITH_ID mode.
      Parameters:
      id - the cd_id value
      Since:
      JChem 3.2

    • setIgnoreChemicalTermsExceptions

      public void setIgnoreChemicalTermsExceptions(boolean ignore)
      Specifies whether to ignore exceptions during the calculation of Chemical Terms columns.
      Parameters:
      ignore - if set to true, no exceptions will be thrown if a calculation for a Chemical Terms column fails. The affected column will have a value of NULL in the database. The default value is false.
      Since:
      JChem 3.2.6

    • setLoggingEnabled

      public void setLoggingEnabled(boolean value)
      Enables / disables logging structures during import.
      Parameters:
      value - If true (default value), logging is enabled.

    • isLoggingEnabled

      public boolean isLoggingEnabled()
      Shows whether logging is enabled during import.
      Returns:
      True if logging is enabled.

    • checkChemTermColValue

      public static Object checkChemTermColValue(Object result)

    • setStructureNameField

      public void setStructureNameField(String nameField)
      Set the name of that column where the structure name is stored.
      Parameters:
      nameField - Name of the column.

    • getStructureNameField

      public String getStructureNameField()
      Get the name of that column which stores the structure names.
      Returns:
      Name of the column.

    • setValueForAdditionalColumn

      public void setValueForAdditionalColumn(int index, Object value, int sqlType) throws SQLException
      Sets values for an additional column. Call setValuesForFixColumns before invoking this method.
      Parameters:
      index - the index value of the additional column as listed in the constructor starting at 1.
      value - the value of the column in the row. It can also be null if the column is declared as NULL.
      sqlType - the type of the column as defined in java.sql.Types
      Throws:
      SQLException - if an error occurs

    • setValueForAdditionalColumn

      public void setValueForAdditionalColumn(int index, Object value) throws SQLException
      Sets values for all additional columns Type is defined automatically.
      Parameters:
      index - the index value of the additional column as listed in the constructor starting at 1.
      value - the value of the column in the row.
      Throws:
      SQLException - if an error occurs

    • setEmptyStructuresAllowed

      public void setEmptyStructuresAllowed(boolean allowed)
      Sets if empty structures are allowed, default is false
      Parameters:
      allowed - set to true to enable empty structures

    • setDuplicateFiltering

      @Deprecated(forRemoval=true) @SubjectToRemoval(date=JUL_01_2025) public void setDuplicateFiltering(boolean filtering)
      Deprecated, for removal: This API element is subject to removal in a future version.
      since JChem 5.4. This import option has been table option, instead of this use setDuplicateFiltering(int) method
      Sets if duplicate structures are allowed to be inserted into the database.
      Parameters:
      filtering - set to true to enable filtering. Default setting is false
      See Also:

    • setDuplicateFiltering

      public void setDuplicateFiltering(int duplicateFilteringOption)
      Sets the duplicate filtering option on import. See Importer.setDuplicateImportAllowed(int)
      Parameters:
      duplicateFilteringOption -
      • If set to DUPLICATE_FILTERING_ON does not import molecules that already exist in the table with the same topology. Forces switching ON duplicate filtering regardless of table setting. This checking may slow down the import progress.
      • If set to DUPLICATE_FILTERING_OFF duplicates are allowed. Forces switching OFF duplicate filtering regardless of table setting.
      • If set to DUPLICATE_FILTERING_TABLE_OPTION the value of the table option ( StructureTableOptions.duplicateFiltering) controls the filtering of duplicates.
      See Also:

    • setSetChiralFlag

      public void setSetChiralFlag(boolean setChiralFlag)
      Sets if chiral flag should be set to true during import. Useful when importing MDL files with missing chiral flag.
      Parameters:
      setChiralFlag - if set to true, chiral flag is set to true for imported molecules. The default setting is false. since 2.3

    • getSetChiralFlag

      public boolean getSetChiralFlag()
      Getter for property setChiralFlag.
      Returns:
      the value of property setChiralFlag
      See Also:

    • execute

      public void execute() throws SQLException
      Executes the update or insert operation. Same as execute(false)
      Throws:
      SQLException - if an error occurs

    • execute

      public int execute(boolean returnLastId) throws SQLException
      Executes the update or insert operation.
      Stays in the same transaction if Connection.getAutoCommit()==false
      Parameters:
      returnLastId - if true, then gets the last inserted ID and returns it.
      NOTE: works with ORACLE, DB2, MySQL, MSSQLServer, InterBase, PostgreSQL and MSAccess
      Returns:
      the last inserted id, or zero if returnLastId is false or the operation is update. If duplicateFiltering is on, returns the cd_id of the already existing structure in the database as a negative value. (regardless of returnLastId).
      Throws:
      SQLException - if an error occurs

    • close

      public void close() throws SQLException
      Closes the handler (required to call). Closes the prepared statements applied and writes the changes to the log table (required for correct cache update).
      Throws:
      SQLException - if an error occurs

    • saveUpdateLogs

      public void saveUpdateLogs() throws SQLException
      Saves the current - possibly buffered - update log(s). Must be called before committing updates in transaction!
      Throws:
      SQLException - if an error occurs

    • deleteRows

      public static void deleteRows(ConnectionHandler conh, String tableName, String where) throws SQLException
      Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). This method is best suited to delete large number of rows. The IDs of the deleted structures will not be logged individually, therefore the cache update may take some time after the operation. For deleting a small amount of records using deleteRow(chemaxon.util.ConnectionHandler, String, int) is recommended.
      Parameters:
      conh - connection to the database
      tableName - name of structure table to be modified
      where - the WHERE part of the SQL statement deleting the rows. E.g. "WHERE cd_id IN (23, 247, 786) ". If null or empty string all rows will be deleted.
      Throws:
      SQLException - if an error occurs
      See Also:

    • deleteRows

      public static void deleteRows(ConnectionHandler conh, String tableName, String where, boolean enableLogging) throws SQLException
      Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). This method is best suited to delete large number of rows. The IDs of the deleted structures will not be logged individually, therefore the cache update may take some time after the operation. For deleting a small amount of records using deleteRow(chemaxon.util.ConnectionHandler, String, int) is recommended.
      Parameters:
      conh - connection to the database
      tableName - name of structure table to be modified
      where - the WHERE part of the SQL statement deleting the rows. E.g. "WHERE cd_id IN (23, 247, 786) ". If null or empty string all rows will be deleted.
      enableLogging - set to true if logging the deletion is enabled
      Throws:
      SQLException - if an error occurs
      See Also:

    • deleteRow

      public static void deleteRow(ConnectionHandler conh, String tableName, int cd_id) throws SQLException
      Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). Use this method to delete relatively small amount of records. The ID of each structure is logged in the update log table providing efficient cache update after the operation. For deleting a large amount of records using deleteRows(chemaxon.util.ConnectionHandler, String, String) is recommended.
      Parameters:
      conh - connection to the database
      tableName - the name of the structure table
      cd_id - the cd_id value of the row to be deleted (primary key)
      Throws:
      SQLException - if an error occurs
      Since:
      5.0
      See Also:

    • deleteRow

      public static void deleteRow(ConnectionHandler conh, String tableName, int cd_id, boolean enableLogging) throws SQLException
      Deletes the specified rows from a structure table. Also deletes the appropriate rows from the corresponding Molecular Descriptor tables, if present and needed. (Referential integrity is used to take care of that for most RDBMSs). Use this method to delete relatively small amount of records. if enableLoggingThe ID of each structure is logged in the update log table providing efficient cache update after the operation. For deleting a large amount of records using deleteRows(chemaxon.util.ConnectionHandler, String, String) is recommended.
      Parameters:
      conh - connection to the database
      tableName - the name of the structure table
      cd_id - the cd_id value of the row to be deleted (primary key)
      enableLogging - set to true if logging the deletion is enabled
      Throws:
      SQLException - if an error occurs
      Since:
      5.0
      See Also:

    • getStructureTables

      public static String[] getStructureTables(ConnectionHandler conh) throws SQLException
      Collects the names of all structures tables in the database (from the records of the JChemProperties table).
      Parameters:
      conh - connection to the database
      Returns:
      the names of all structure tables.
      Throws:
      SQLException - if an error occurs

    • isStructureTable

      public static boolean isStructureTable(ConnectionHandler conh, String tableName) throws SQLException
      Checks if a structure table exists.
      Parameters:
      conh - connection handler to the database
      tableName - name of structure table
      Returns:
      true if the table exists
      Throws:
      SQLException - if an error occurs

    • createStructureTable

      public static void createStructureTable(ConnectionHandler conh, StructureTableOptions options) throws SQLException
      Creates a structure table.
      Parameters:
      conh - connection to the database
      options - options for table creation
      Throws:
      SQLException - if an error occurs
      Since:
      JChem 5.0
      See Also:

    • dropStructureTable

      public static void dropStructureTable(ConnectionHandler conh, String tableName) throws SQLException
      Drops a structure table.
      Parameters:
      conh - connection to the database
      tableName - name of structure table
      Throws:
      SQLException - if an error occurs

    • recalculateTable

      public static boolean recalculateTable(ConnectionHandler ch, String tableName, boolean changeStandardization, String standardizerConfig, Map<String,String> chemTermColCfg, Boolean tautomerDuplicateFiltering, ProgressWriter pw) throws UpdateHandlerException
      Recalculates generated values in a structure table in normal mode.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      changeStandardization - changes default standardization if true
      standardizerConfig - new standardizer configuration, is set to
      chemTermColCfg - colname-to-chemterm map for columns autocalculated based on Chemical Terms. Specify null if there is no change in the column definitions.
      tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.0

    • recalculateTable

      public static boolean recalculateTable(ConnectionHandler ch, String tableName, boolean changeStandardization, String standardizerConfig, Map<String,String> chemTermColCfg, Boolean tautomerDuplicateFiltering, ProgressWriter pw, int regenerationMode) throws UpdateHandlerException
      Recalculates generated values in a structure table.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      changeStandardization - changes default standardization if true
      standardizerConfig - new standardizer configuration, is set to
      chemTermColCfg - colname-to-chemterm map for columns autocalculated based on Chemical Terms. Specify null if there is no change in the column definitions.
      tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.0

    • recalculateCTColumns

      public static boolean recalculateCTColumns(ConnectionHandler ch, String tableName, String[] columns, ProgressWriter pw) throws UpdateHandlerException
      Recalculates chemical terms in a structure table in normal mode.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      columns - array of chemical terms column names
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.1.2

    • recalculateCTColumns

      public static boolean recalculateCTColumns(ConnectionHandler ch, String tableName, String[] columns, ProgressWriter pw, int regenerationMode) throws UpdateHandlerException
      Recalculates chemical terms in a structure table.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      columns - array of chemical terms column names
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.1.2

    • recalculateMDTables

      public static boolean recalculateMDTables(ConnectionHandler ch, String tableName, ProgressWriter pw) throws UpdateHandlerException, SQLException
      Recalculates molecular descriptors for a structure table / for all structure tables.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated. If null, ALL structure tables are regenerated.
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      SQLException - if an error occurs
      Since:
      JChem 5.3.2

    • recalculateWithoutCTColumns

      public static boolean recalculateWithoutCTColumns(ConnectionHandler ch, String tableName, boolean changeStandardization, String standardizerConfig, Boolean tautomerDuplicateFiltering, ProgressWriter pw) throws UpdateHandlerException
      Recalculates generated values in a structure table but skips Chemical Terms calculations in normal mode.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      changeStandardization - changes default standardization if true
      standardizerConfig - new standardizer configuration, is set to
      tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.2.4

    • recalculateWithoutCTColumns

      public static boolean recalculateWithoutCTColumns(ConnectionHandler ch, String tableName, boolean changeStandardization, String standardizerConfig, Boolean tautomerDuplicateFiltering, ProgressWriter pw, int regenerationMode) throws UpdateHandlerException
      Recalculates generated values in a structure table but skips Chemical Terms calculations.
      Parameters:
      ch - ConnectionHandler opened to the database
      tableName - the name of the structure table to be regenerated
      changeStandardization - changes default standardization if true
      standardizerConfig - new standardizer configuration, is set to
      tautomerDuplicateFiltering - set to Boolean.TRUE to enable considering tautomers during duplicate filtering. Set to null to keep the current setting (default).
      pw - ProgressWriter for monitoring (and if needed canceling) the progress of regeneration. May be null if no monitoring is necessary.
      regenerationMode - Normal, precalculation or regenerate not precalculated rows (see: RegenerationConstants)
      Returns:
      whether any error occurred during the regeneration process (true, if not).
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      Since:
      JChem 5.2.4

    • createRowData

      public UpdateHandler.RowData createRowData(String molString) throws UpdateHandlerException, MolFormatException
      Calculates row data from molecule string.

      NOTE: Not thread-safe! Multiple threads should use multiple UpdateHandler instances to call this method.

      Parameters:
      molString - the source of the structure
      Returns:
      a rowdata object
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      MolFormatException - if molecule parsing encounters a problem
      Since:
      JChem 3.2
      See Also:

    • createRowData

      public UpdateHandler.RowData createRowData(byte[] molString) throws UpdateHandlerException, MolFormatException
      Calculates row data from molecule string.

      NOTE: Not thread safe ! Multiple threads should use multiple UpdateHandler instances to call this method.

      Parameters:
      molString - the source of the structure
      Returns:
      a rowdata object
      Throws:
      UpdateHandlerException - if an error occurs during processing.
      MolFormatException - if molecule parsing encounters a problem
      Since:
      JChem 3.2
      See Also:

    • setRowData

      public void setRowData(UpdateHandler.RowData rowData) throws SQLException
      Specifies pre-calculated row data values. Subtitutes setValueForFixColumns() and setValueForAdditionalColumn calls. Useful if the data is calculated in a multi-thread environment.
      Parameters:
      rowData - the RowData object
      Throws:
      SQLException - if an error occurs
      Since:
      JChem 3.2
      See Also:

    • setChemTermCalc

      public void setChemTermCalc(boolean isCalcNeeded)
      Enable or disable the chemical terms calculation during the update
      Parameters:
      isCalcNeeded - boolean

    • getPrecalculatedRowNumber

      public static int getPrecalculatedRowNumber(ConnectionHandler conh, String tableName) throws SQLException
      Throws:
      SQLException