Package com.basiscomponents.bc

Class SqlTableBC

  • All Implemented Interfaces:
    BusinessComponent
    public class SqlTableBC
extends Object
implements BusinessComponent

    SqlTableBC represents a table in a database.

    Data can be read/written from/into the database using a JDBC driver.
    setTable(String) sets the table, which should be used for reading/writing data.

    SqlTableBC is using DataRow for a single data row representation and ResultSet for a set of data rows.

    The developer has to work with DataRow's and ResultSet's only. No SQL knowledge is required.

    SqlTableBC is using the JDBC metadata to determine the table information (such as column names, column types, field lengths, and so on).

    Author:
    vkolarov, damore

    • Constructor Detail

      • SqlTableBC

        public SqlTableBC​(String url)
        Constructor.

        Creates a new SqlTableBC object using a database URL.

        Parameters:
        url - database URL to the database.

      • SqlTableBC

        public SqlTableBC​(String driver,
                  String url,
                  String user,
                  String password)
           throws ClassNotFoundException
        Constructor.

        Creates a new SqlTableBC object using driver, URL, user and password.

        Parameters:
        driver - the name/package of the JDBC driver.
        url - database URL to the database.
        user - the name of the database user.
        password - the password of the database user.
        Throws:
        ClassNotFoundException - thrown if the JDBC driver could not be found.

      • SqlTableBC

        public SqlTableBC​(Connection con)
           throws SQLException
        Constructor.

        Creates a new SqlTableBC object using a database connection.

        NOTE: SqlTableBC will not close the connection (after reading or writing).

        Parameters:
        con - the database connection to the database.
        Throws:
        SQLException - thrown if the connection status (con.isCloses()) cannot be determined

    • Method Detail

      • setRetrieveSql

        public void setRetrieveSql​(String sql)
        Sets a custom retrieve SQL statement.

        A custom retrieve SQL statement can be set when a more complex select statement is needed (f.g. to retrieve foreign key values). The SQL statement can be a prepared statement. Parameters can be set with setRetrieveParameters(DataRow).

        NOTE: if a custom retrieve SQL statement is used, a table name need to be set (using setTable(String)) for writing/removing data from the main table.

        Parameters:
        sql - the custom retrieve SQL statement.
        See Also:
        setRetrieveParameters(DataRow)

      • setRetrieveSql

        public void setRetrieveSql​(String sql,
                           DataRow retrieveDr)
        Sets a custom retrieve SQL prepared statement.

        A custom retrieve SQL statement can be set when a more complex select statement is needed (f.g. to retrieve foreign key values).

        Prepared values (question marks in the SQL statement) can be passed within a DataRow.
        The field count and field type in the DataRow should match the count and the type of the question mark in the prepared select statement.

        NOTE: if a custom retrieve SQL statement is used, a table name need to be set (using setTable(String)) for writing/removing data from the main table.

        Parameters:
        sql - the custom retrieve SQL statement.
        retrieveDr - the data row with values for the prepared statement.

      • setRetrieveParameters

        public void setRetrieveParameters​(DataRow retrieveDr)
        Sets prepared parameters when a custom retrieve SQL statement is used.
        Parameters:
        retrieveDr - the data row with values for the prepared statement.

      • getDbQuoteString

        public String getDbQuoteString()
        Get the database specific quote string character.
        NOTE: the quote string character is returned from the JDBC driver and it is determined in the setTable(String) method.
        Returns:
        the database specific quote string character.

      • getFilter

        public DataRow getFilter()
        Returns a previously set filter. May be null.
        Returns:
        a DataRow with filter fields.
        See Also:
        setFilter(DataRow)

      • setFilter

        public void setFilter​(DataRow filter)
        Set a filter for the search result.
        If the filter is set it will be used in the retrieve method.
        If no filter is set, the retrieve method will return all data.
        Specified by:
        setFilter in interface BusinessComponent
        Parameters:
        filter - a DataRow including field names and values to filter for. Filters are AND combined.
        See Also:
        BusinessComponent.retrieve()

      • getFieldSelection

        public DataRow getFieldSelection()
        Returns a previously set field selection. May be null.
        Returns:
        a DataRow with field names for selection.

      • setFieldSelection

        public void setFieldSelection​(DataRow fieldSelection)
        Set a field selection to retrieve a custom set of fields.
        All fields will be returned if no field selection is set.
        Specified by:
        setFieldSelection in interface BusinessComponent
        Parameters:
        fieldSelection - a DataRow containing the field names for retrieving. Only the field names in the DataRow are used.

      • setFieldSelection

        public void setFieldSelection​(Collection<String> fieldSelection)
        Set a field selection to retrieve a custom set of fields.
        The default scope will be used if no field selection is set.
        Specified by:
        setFieldSelection in interface BusinessComponent
        Parameters:
        fieldSelection - a String Collection containing the field names for retrieving.

      • getScope

        public String getScope()
        Get current scope (if scope is set).
        Returns:
        the selected scope.

      • setScope

        public void setScope​(String scope)
        Set a field selection scope (A, B, C, etc.).
        If no or a wrong scope is set then all fields will be returned.
        Specified by:
        setScope in interface BusinessComponent
        Parameters:
        scope - the scope to set.

      • getScopeDef

        public HashMap<String,​ArrayList<String>> getScopeDef()
        Get a HashMap with all defined scopes (A-Z).
        Returns:
        a HashMap with scope name as key and field names as ArrayList.

      • isFieldInScope

        public boolean isFieldInScope​(String fieldName)

      • isFieldInScope

        public boolean isFieldInScope​(String scope,
                              String fieldName)

      • setScopeDef

        public void setScopeDef​(HashMap<String,​ArrayList<String>> scopes)
        Sets the scope definition.
        The scope definition is a HashMap with the scope name as key and an ArrayList with field names as value.
        Parameters:
        scopes - the HashMap with the scope definitions

      • retrieve

        public ResultSet retrieve()
                   throws Exception
        Retrieves a ResultSet with DataRow's.
        If a filter is set, this will be applied to filter the result.
        If a scope and/or a field selection is set, it will be used to retrieve the desired fields.
        Specified by:
        retrieve in interface BusinessComponent
        Returns:
        a ResultSet with DataRow's (may be empty).
        Throws:
        Exception - may occur during reading.

      • retrieve

        public ResultSet retrieve​(int first,
                          int last)
                   throws Exception
        Retrieves a ResultSet containing a subset of DataRow's (for pagination f.g.).
        If a filter is set, this will be applied to filter the result.
        If a scope and/or a field selection is set, it will be used to retrieve the desired fields.
        Specified by:
        retrieve in interface BusinessComponent
        Parameters:
        first - the index to start from (0 based).
        last - the index of the last element in the subset.
        Returns:
        a ResultSet with DataRow's (may be empty).
        Throws:
        Exception - may occur during reading.

      • validateWrite

        public ResultSet validateWrite​(DataRow dr)
        Validates a DataRow object before it can be written.
        This method is internally used by the write method.
        But it can also be called from the frontend to check for required or missing data.
        Specified by:
        validateWrite in interface BusinessComponent
        Parameters:
        dr - a DataRow to validated.
        Returns:
        a ResultSet with validation messages (empty ResultSet means no validation errors).
        Each DataRow in the ResultSet should have following fields: FIELD_NAME, TYPE and MESSAGE.
        FIELD_NAME: the name of the validated field
        TYPE: INFO, WARNING or ERROR
        MESSAGE: the validation message

      • validateRemove

        public ResultSet validateRemove​(DataRow dr)
        Validates a DataRow object before it can be removed.
        This method is internally used by the remove method.
        But it can also be called from the frontend to check for dependencies before it can be removed.
        Specified by:
        validateRemove in interface BusinessComponent
        Parameters:
        dr - the DataRow that should be removed.
        Returns:
        a ResultSet with validation messages (empty ResultSet means no validation errors).
        Each DataRow in the ResultSet should have following fields: FIELD_NAME, TYPE and MESSAGE.
        FIELD_NAME: the name of the validated field
        TYPE: INFO, WARNING or ERROR
        MESSAGE: the validation message

      • getAttributesRecord

        public DataRow getAttributesRecord()
        Returns an empty DataRow including all field attributes (like field name, editable, label etc.), which the retrieve method could return.
        Specified by:
        getAttributesRecord in interface BusinessComponent
        Returns:
        an empty DataRow with field attributes.

      • getNewObjectTemplate

        public DataRow getNewObjectTemplate​(DataRow conditions)
        Returns a new (predefined) DataRow including all field attributes (like field name, editable, label etc.).
        Specified by:
        getNewObjectTemplate in interface BusinessComponent
        Parameters:
        conditions - a DataRow with predefined fields.
        Returns:
        a new predefined DataRow with the field attributes.

      • retrieve

        public ResultSet retrieve​(String sql,
                          DataRow params)
        Executes a SQL query statement and returns the result as a ResultSet.
        Parameters:
        sql - the query statement.
        params - if not null or empty the values from this DataRow will be used to set prepared parameters in the sql statement.
        Returns:
        the query result as ResultSet.

      • addMapping

        public void addMapping​(String bcFieldName,
                       String dbFieldName)
        Adds a field mapping. If a custom retrieve SQL statement with alias names is used, a field mapping can be added.
        The field mapping is internally used when the where clause (filters) are created (on retrieve) and on insert/update and deleting data.
        Parameters:
        bcFieldName - the alias name
        dbFieldName - the table field name

      • getMappings

        public Map<String,​String> getMappings()
        Returns all defined field mappings.

        TODO APICHANGE to Map instead of HasMap

        Returns:
        a HashMap with field mappings. Where key is the alias name and value is the table field name.

      • getMapping

        public String getMapping​(String bcFieldName)
        Gets the mapping for a field. If no mapping exists, then the alias field name (bcFieldName) is returned.
        Parameters:
        bcFieldName - the alias field name.
        Returns:
        the table field name.

      • setTruncateFieldValues

        public void setTruncateFieldValues​(boolean truncate)
        Sets if values of character fields should be automatically truncated on validation to the field length defined in the table.

        NOTE: only character fields that are not primary keys will be truncated!

        Parameters:
        truncate - true - all character fields (except primary keys) will be truncated in the validateWrite(DataRow) method. false - don't truncate character fields.

      • getLastSqlStatement

        public String getLastSqlStatement()
        Returns the last executed sql statement. The last executed sql statement is set after a retrieve, write or delete.

        Returns:
        the last executed sql statement.

      • getAllowedFilter

        public DataRow getAllowedFilter()
        Returns a DataRow with fields (the values are not used) which are allowed for filtering. This method returns a clone of the attributes record plus additionally added fields. Additional fields can be added using the registerFilterField method.
        Specified by:
        getAllowedFilter in interface BusinessComponent
        Returns:
        a DataRow with fields used for filtering.
        See Also:
        registerFilterField(String fieldName)

      • registerFilterField

        public void registerFilterField​(String fieldName)
        Add a field to the allowed filter DataRow.
        Parameters:
        fieldName -
        See Also:
        getAllowedFilter()

      • getLookup

        public BusinessComponent getLookup​(String fieldName,
                                   DataRow dr)
        Description copied from interface: BusinessComponent
        returns a bc that contains data providing predefined values for a field. for example a lookup for a field "country" could return a countryBC which can retrieve values to fill a ListButton with. other fields are ignored.
        Specified by:
        getLookup in interface BusinessComponent
        Parameters:
        fieldName - the field to retrieve the Lookup for
        dr - current datarow
        Returns:
        the lookup BC

      • getLookupData

        public ResultSet getLookupData​(String fieldName,
                               DataRow dr)
        Description copied from interface: BusinessComponent
        like getLookup, but instead returns a resultset containg the lookup data.
        Specified by:
        getLookupData in interface BusinessComponent
        Parameters:
        fieldName - the field to retrieve the Lookup for
        dr - current datarow
        Returns:
        the retrieved resultset from the lookup