Package com.ibm.fhir.persistence.jdbc.util

Class JDBCQueryBuilder

  • All Implemented Interfaces:
    QueryBuilder<SqlQueryData>
    public class JDBCQueryBuilder
extends AbstractQueryBuilder<SqlQueryData>
    This is the JDBC implementation of a query builder for the IBM FHIR Server JDBC persistence layer schema. Queries are built in SQL.

    For the new R4 schema, the search parameter tables (e.g. <resourceType>_STR_VALUES) are joined to their corresponding _LOGICAL_RESOURCES tables on LOGICAL_RESOURCE_ID. This is because the search parameters are not versioned, and are associated with the logical resource, not the resource version.
    Useful column reference:
     ------------------------
 RESOURCE_TYPE_NAME    the formal name of the resource type e.g. 'Patient'
 RESOURCE_TYPE_ID      FK to the RESOURCE_TYPES table
 LOGICAL_ID            the VARCHAR holding the logical-id of the resource. Unique for a given resource type
 LOGICAL_RESOURCE_ID   the database BIGINT
 CURRENT_RESOURCE_ID   the unique BIGINT id of the latest resource version for the logical resource
 VERSION_ID            INT resource version number incrementing by 1
 RESOURCE_ID           the PK of the version-specific resource. Now only used as the target for CURRENT_RESOURCE_ID

    • Constructor Detail

    • Method Detail

      • buildCountQuery

        public SqlQueryData buildCountQuery​(Class<?> resourceType,
                                    FHIRSearchContext searchContext)
                             throws Exception
        Builds a query that returns the count of the search results that would be found by applying the search parameters contained within the passed search context.
        Parameters:
        resourceType - - The type of resource being searched for.
        searchContext - - The search context containing the search parameters.
        Returns:
        String - A count query SQL string
        Throws:
        Exception

      • buildQuery

        public SqlQueryData buildQuery​(Class<?> resourceType,
                               FHIRSearchContext searchContext)
                        throws Exception
        Description copied from interface: QueryBuilder
        Build and return query for the passed resource type and search parameters.
        Parameters:
        resourceType - A FHIR Resource subclass.
        searchContext - A search context that contains a List of search parameters to be used for constructing the query.
        Returns:
        An instance of T representing the constructed query.
        Throws:
        FHIRPersistenceException - thrown for any non-recoverable failure that occurs during query construction.
        Exception

      • getOperator

        protected String getOperator​(QueryParameter queryParm,
                             String defaultOverride)
        Map the Modifier in the passed Parameter to a supported query operator. If the mapping results in the default operator, override the default operator with the passed operator if the passed operator is not null.
        Parameters:
        queryParm - - A valid query Parameter.
        defaultOverride - - An operator that should override the default operator.
        Returns:
        A supported operator.

      • buildQueryParm

        protected SqlQueryData buildQueryParm​(Class<?> resourceType,
                                      QueryParameter queryParm,
                                      String paramTableAlias,
                                      String logicalRsrcTableAlias,
                                      boolean endOfChain)
                               throws Exception
        Builds a query segment for the passed query parameter.
        Parameters:
        resourceType - - A valid FHIR Resource type
        queryParm - - A Parameter object describing the name, value and type of search parm
        paramTableAlias - - An alias for the parameter table for which this query parameter applies
        logicalRsrcTableAlias - - An alias for the logical resource table for which this query parameter applies
        endOfChain - - true if query parameter is at the end of a chain, otherwise false
        Returns:
        SqlQueryData - An object representing the selector query segment for the passed search parm.
        Throws:
        Exception

      • processChainedReferenceParm

        protected SqlQueryData processChainedReferenceParm​(QueryParameter queryParm)
                                            throws Exception
        Contains special logic for handling chained reference search parameters.

        Nested sub-selects are built to realize the chaining logic required. Here is a sample chained query for an Observation given this search parameter: device:Device.patient.family=Monella 

         SELECT R.RESOURCE_ID, R.LOGICAL_RESOURCE_ID, R.VERSION_ID, R.LAST_UPDATED, R.IS_DELETED, R.DATA, LR.LOGICAL_ID
 FROM Observation_LOGICAL_RESOURCES LR
 JOIN Observation_RESOURCES R ON R.LOGICAL_RESOURCE_ID = LR.LOGICAL_RESOURCE_ID AND R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID AND R.IS_DELETED = 'N'
 JOIN (SELECT DISTINCT LOGICAL_RESOURCE_ID FROM Observation_STR_VALUES
 WHERE(P1.PARAMETER_NAME_ID = 107 AND (p1.STR_VALUE IN
    (SELECT 'Device' || '/' || CLR1.LOGICAL_ID FROM Device_RESOURCES CR1, Device_LOGICAL_RESOURCES CLR1, Device_STR_VALUES CP1 WHERE
        CR1.RESOURCE_ID = CLR1.CURRENT_RESOURCE_ID AND CR1.IS_DELETED = 'N' AND CP1.RESOURCE_ID = CR1.RESOURCE_ID AND
          CP1.PARAMETER_NAME_ID = 17 AND CP1.STR_VALUE IN
                 (SELECT 'Patient' || '/' || CLR2.LOGICAL_ID FROM Patient_RESOURCES CR2, Patient_LOGICAL_RESOURCES CLR2, Patient_STR_VALUES CP2 WHERE
                     CR2.RESOURCE_ID = CLR2.CURRENT_RESOURCE_ID AND CR2.IS_DELETED = 'N' AND CP2.RESOURCE_ID = CR2.RESOURCE_ID AND
                     CP2.PARAMETER_NAME_ID = 5 AND CP2.STR_VALUE = 'Monella')))
 TMP0 ON TMP0.LOGICAL_RESOURCE_ID = R.LOGICAL_RESOURCE_ID;
        Specified by:
        processChainedReferenceParm in class AbstractQueryBuilder<SqlQueryData>
        Parameters:
        queryParm - - A Parameter representing a chained query.
        Returns:
        SqlQueryData - The query segment for a chained parameter reference search.
        Throws:
        Exception
        FHIRPersistenceException

      • processInclusionCriteria

        protected SqlQueryData processInclusionCriteria​(QueryParameter queryParm)
                                         throws Exception
        This method is the entry point for processing inclusion criteria, which define resources that are part of a compartment-based search. Example inclusion criteria for AuditEvent in the Patient compartment: TODO: revisit this method in light of https://jira.hl7.org/browse/FHIR-15906 (removal of chained inclusion criteria) TODO: consider leaving the chained inclusion in light of https://jira.hl7.org/browse/FHIR-17358 
         {
   "name": "AuditEvent",
   "inclusionCriteria": [
     "patient",                              This is a simple attribute inclusion criterion
     "participant.patient:Device",           This is a chained inclusion criterion
     "participant.patient:RelatedPerson",    This is a chained inclusion criterion
     "reference.patient:*"                   This is a chained inclusion criterion with wildcard. The wildcard means "any resource type".
   ]
 }

        Here is a sample generated query for this inclusion criteria:

      • PARAMETER_NAME_ID 13 = 'participant'
      • PARAMETER_NAME_ID 14 = 'patient'
      • PARAMETER_NAME_ID 16 = 'reference' 
            SELECT COUNT(R.RESOURCE_ID) FROM
    AuditEvent_RESOURCES R, AuditEvent_LOGICAL_RESOURCES LR , AuditEvent_STR_VALUES P1 WHERE
    R.RESOURCE_ID = LR.CURRENT_RESOURCE_ID AND
    R.IS_DELETED = 'N' AND
    P1.RESOURCE_ID = R.RESOURCE_ID AND
    ((P1.PARAMETER_NAME_ID=14 AND P1.STR_VALUE = ?) OR
     ((P1.PARAMETER_NAME_ID=13 AND
      (P1.STR_VALUE IN
        (SELECT 'Device' || '/' || CLR1.LOGICAL_ID FROM
            Device_RESOURCES CR1, Device_LOGICAL_RESOURCES CLR1, Device_STR_VALUES CP1 WHERE
            CR1.RESOURCE_ID = CLR1.CURRENT_RESOURCE_ID AND
            CR1.IS_DELETED = 'N' AND
            CP1.RESOURCE_ID = CR1.RESOURCE_ID AND
            CP1.PARAMETER_NAME_ID=14 AND CP1.STR_VALUE = ?)))) OR
    ((P1.PARAMETER_NAME_ID=13 AND
     (P1.STR_VALUE IN
        (SELECT 'RelatedPerson' || '/' || CLR1.LOGICAL_ID FROM
            RelatedPerson_RESOURCES CR1, RelatedPerson_LOGICAL_RESOURCES CLR1, RelatedPerson_STR_VALUES CP1 WHERE
            CR1.RESOURCE_ID = CLR1.CURRENT_RESOURCE_ID AND
            CR1.IS_DELETED = 'N' AND
            CP1.RESOURCE_ID = CR1.RESOURCE_ID AND
            CP1.PARAMETER_NAME_ID=14 AND CP1.STR_VALUE = ?)))) OR
     ((P1.PARAMETER_NAME_ID=16 AND
      (P1.STR_VALUE IN
        (SELECT 'AuditEvent' || '/' || CLR1.LOGICAL_ID FROM
            auditevent_RESOURCES CR1, auditevent_LOGICAL_RESOURCES CLR1, auditevent_STR_VALUES CP1 WHERE
            CR1.RESOURCE_ID = CLR1.CURRENT_RESOURCE_ID AND
            CR1.IS_DELETED = 'N' AND
            CP1.RESOURCE_ID = CR1.RESOURCE_ID AND
            CP1.PARAMETER_NAME_ID=14 AND CP1.STR_VALUE = ?
            UNION
            SELECT 'Device' || '/' || CLR1.LOGICAL_ID FROM
                device_RESOURCES CR1, device_LOGICAL_RESOURCES CLR1, device_STR_VALUES CP1 WHERE
                CR1.RESOURCE_ID = CLR1.CURRENT_RESOURCE_ID AND
                CR1.IS_DELETED = 'N' AND
                CP1.RESOURCE_ID = CR1.RESOURCE_ID AND
                CP1.PARAMETER_NAME_ID=14 AND CP1.STR_VALUE = ?)))));
Specified by:
processInclusionCriteria in class AbstractQueryBuilder<SqlQueryData>
Parameters:
queryParm - - The query parameter.
Returns:
T1 - An object containing a query segment.
Throws:
Exception
FHIRPersistenceException
See Also:
for the specificaiton of compartments, resources contained in each compartment, and the criteria for a resource to be included in a compartment.