Class SQLTemplate

  • All Implemented Interfaces:
    Serializable, ParameterizedQuery, Query

    public class SQLTemplate
    extends AbstractQuery
    implements ParameterizedQuery
    A query that executes unchanged (except for template preprocessing) "raw" SQL specified by the user.

    Template Script

    SQLTemplate stores a dynamic template for the SQL query that supports parameters and customization using Velocity scripting language. The most straightforward use of scripting abilities is to build parameterized queries. For example:

                      SELECT ID, NAME FROM SOME_TABLE WHERE NAME LIKE $a
     

    For advanced scripting options see "Scripting SQLTemplate" chapter in the User Guide.

    Per-Database Template Customization

    SQLTemplate has a default template script, but also it allows to configure multiple templates and switch them dynamically. This way a single query can have multiple "dialects" specific to a given database.

    Since:
    1.1
    See Also:
    Serialized Form
    • Field Detail

      • defaultTemplate

        protected String defaultTemplate
      • parameters

        protected Map<String,​?>[] parameters
      • positionalParams

        protected List<Object> positionalParams
      • columnNamesCapitalization

        protected CapsStrategy columnNamesCapitalization
      • returnGeneratedKeys

        protected boolean returnGeneratedKeys
    • Constructor Detail

      • SQLTemplate

        public SQLTemplate()
        Creates an empty SQLTemplate. Note this constructor does not specify the "root" of the query, so a user must call "setRoot" later to make sure SQLTemplate can be executed.
        Since:
        1.2
      • SQLTemplate

        public SQLTemplate​(String defaultTemplate,
                           boolean isFetchingDataRows)
        Creates a SQLTemplate without an explicit root.
        Since:
        4.0
      • SQLTemplate

        public SQLTemplate​(DataMap rootMap,
                           String defaultTemplate,
                           boolean isFetchingDataRows)
        Since:
        3.1
      • SQLTemplate

        public SQLTemplate​(ObjEntity rootEntity,
                           String defaultTemplate)
        Since:
        1.2
      • SQLTemplate

        public SQLTemplate​(Class<?> rootClass,
                           String defaultTemplate)
        Since:
        1.2
      • SQLTemplate

        public SQLTemplate​(DbEntity rootEntity,
                           String defaultTemplate)
        Since:
        1.2
      • SQLTemplate

        public SQLTemplate​(String objEntityName,
                           String defaultTemplate)
        Since:
        1.2
    • Method Detail

      • setResultColumnsTypes

        public void setResultColumnsTypes​(Class<?>... types)
      • setRoot

        public void setRoot​(Object value)
        Description copied from class: AbstractQuery
        Sets the root of the query
        Overrides:
        setRoot in class AbstractQuery
        Parameters:
        value - The new root
      • route

        public void route​(QueryRouter router,
                          EntityResolver resolver,
                          Query substitutedQuery)
        Description copied from class: AbstractQuery
        Implements default routing mechanism relying on the EntityResolver to find DataMap based on the query root. This mechanism should be sufficient for most queries that "know" their root.
        Specified by:
        route in interface Query
        Overrides:
        route in class AbstractQuery
      • initWithProperties

        public void initWithProperties​(Map<String,​?> properties)
        Initializes query parameters using a set of properties.
        Since:
        1.1
      • parametersIterator

        public Iterator<Map<String,​?>> parametersIterator()
        Returns an iterator over parameter sets. Each element returned from the iterator is a java.util.Map.
      • parametersSize

        public int parametersSize()
        Returns the number of parameter sets.
      • setParams

        public void setParams​(Map<String,​?> params)
        Initializes named parameter of this query. Note that calling this method will reset any positional parameters.
        Since:
        4.0
      • setParamsArray

        public void setParamsArray​(Object... params)
        Initializes positional parameters of the query. Parameters are bound in the order they are found in the SQL template. If a given parameter name is used more than once, only the first occurrence is treated as "position", subsequent occurrences are bound with the same value as the first one. If template parameters count is different from the array parameter count, an exception will be thrown.

        Note that calling this method will reset any previously set *named* parameters.

        Since:
        4.0
      • setParamsList

        public void setParamsList​(List<Object> params)
        Initializes positional parameters of the query. Parameters are bound in the order they are found in the SQL template. If a given parameter name is used more than once, only the first occurrence is treated as "position", subsequent occurrences are bound with the same value as the first one. If template parameters count is different from the list parameter count, an exception will be thrown.

        Note that calling this method will reset any previously set *named* parameters.

        Since:
        4.0
      • queryWithParameters

        public SQLTemplate queryWithParameters​(Map<String,​?>... parameters)
        Returns a new query built using this query as a prototype and a new set of parameters.
      • createQuery

        public Query createQuery​(Map<String,​?> parameters)
        Creates and returns a new SQLTemplate built using this query as a prototype and substituting template parameters with the values from the map.
        Specified by:
        createQuery in interface ParameterizedQuery
        Since:
        1.1
      • getBaseMetaData

        protected org.apache.cayenne.query.BaseQueryMetadata getBaseMetaData()
        Specified by:
        getBaseMetaData in class CacheableQuery
      • getFetchLimit

        public int getFetchLimit()
      • setFetchLimit

        public void setFetchLimit​(int fetchLimit)
      • getFetchOffset

        public int getFetchOffset()
        Since:
        3.0
      • setFetchOffset

        public void setFetchOffset​(int fetchOffset)
        Since:
        3.0
      • getPageSize

        public int getPageSize()
      • setPageSize

        public void setPageSize​(int pageSize)
      • setFetchingDataRows

        public void setFetchingDataRows​(boolean flag)
      • isFetchingDataRows

        public boolean isFetchingDataRows()
      • getDefaultTemplate

        public String getDefaultTemplate()
        Returns default SQL template for this query.
      • setDefaultTemplate

        public void setDefaultTemplate​(String string)
        Sets default SQL template for this query.
      • getTemplate

        public String getTemplate​(String key)
        Returns a template for key, or a default template if a template for key is not found.
      • getCustomTemplate

        public String getCustomTemplate​(String key)
        Returns template for key, or null if there is no template configured for this key. Unlike getTemplate(String)this method does not return a default template as a failover strategy, rather it returns null.
      • setTemplate

        public void setTemplate​(String key,
                                String template)
        Adds a SQL template string for a given key. Note the the keys understood by Cayenne must be fully qualified adapter class names. This way the framework can related current DataNode to the right template. E.g. "org.apache.cayenne.dba.oracle.OracleAdapter" is a key that should be used to setup an Oracle-specific template.
        See Also:
        setDefaultTemplate(String)
      • removeTemplate

        public void removeTemplate​(String key)
      • getTemplateKeys

        public Collection<String> getTemplateKeys()
        Returns a collection of configured template keys.
      • getParams

        public Map<String,​?> getParams()
        Returns a map of named parameters that will be bound to SQL.
        Since:
        4.0
      • getPositionalParams

        public List<Object> getPositionalParams()
        Returns a list of positional parameters that will be bound to SQL.
        Since:
        4.0
      • getParameters

        public Map<String,​?> getParameters()
        Utility method to get the first set of parameters, since most queries will only have one.
      • setParameters

        public void setParameters​(Map<String,​?>... parameters)
        Utility method to initialize query with one or more sets of parameters.
      • addPrefetch

        public void addPrefetch​(PrefetchTreeNode prefetchElement)
        Adds a prefetch with specified relationship path to the query.
        Since:
        4.0
      • removePrefetch

        public void removePrefetch​(String prefetch)
        Since:
        1.2
      • addPrefetches

        public void addPrefetches​(Collection<String> prefetches)
        Adds all prefetches from a provided collection.
        Since:
        1.2
      • clearPrefetches

        public void clearPrefetches()
        Clears all prefetches.
        Since:
        1.2
      • getColumnNamesCapitalization

        public CapsStrategy getColumnNamesCapitalization()
        Returns a column name capitalization policy applied to selecting queries. This is used to simplify mapping of the queries like "SELECT * FROM ...", ensuring that a chosen Cayenne column mapping strategy (e.g. all column names in uppercase) is portable across database engines that can have varying default capitalization. Default (null) value indicates that column names provided in result set are used unchanged.
        Since:
        3.0
      • setColumnNamesCapitalization

        public void setColumnNamesCapitalization​(CapsStrategy columnNameCapitalization)
        Sets a column name capitalization policy applied to selecting queries. This is used to simplify mapping of the queries like "SELECT * FROM ...", ensuring that a chosen Cayenne column mapping strategy (e.g. all column names in uppercase) is portable across database engines that can have varying default capitalization. Default (null) value indicates that column names provided in result set are used unchanged.

        Note that while a non-default setting is useful for queries that do not rely on a #result directive to describe columns, it works for all SQLTemplates the same way.

        Since:
        3.0
      • setResult

        public void setResult​(SQLResult resultSet)
        Sets an optional explicit mapping of the result set. If result set mapping is specified, the result of SQLTemplate may not be a normal list of Persistent objects or DataRows, instead it will follow the SQLResult rules.
        Since:
        3.0
      • getResult

        public SQLResult getResult()
        Since:
        3.0
      • setStatementFetchSize

        public void setStatementFetchSize​(int size)
        Sets statement's fetch size (0 for no default size)
        Since:
        3.0
      • getStatementFetchSize

        public int getStatementFetchSize()
        Returns:
        statement's fetch size
        Since:
        3.0
      • getDataNodeName

        public String getDataNodeName()
        Returns a name of the DataNode to use with this SQLTemplate. This information will be used during query execution if no other routing information is provided such as entity name or class, etc.
        Since:
        4.0
      • setDataNodeName

        public void setDataNodeName​(String dataNodeName)
        Sets a name of the DataNode to use with this SQLTemplate. This information will be used during query execution if no other routing information is provided such as entity name or class, etc.
        Since:
        4.0
      • isReturnGeneratedKeys

        public boolean isReturnGeneratedKeys()
        Returns:
        returnGeneratedKeys flag
        Since:
        4.1
      • setReturnGeneratedKeys

        public void setReturnGeneratedKeys​(boolean returnGeneratedKeys)
        Sets flag to return generated keys.
        Since:
        4.1
      • getResultColumnsTypes

        public List<Class<?>> getResultColumnsTypes()
      • setResultColumnsTypes

        public void setResultColumnsTypes​(List<Class<?>> resultColumnsTypes)
      • setUseScalar

        public void setUseScalar​(boolean useScalar)
        Sets flag to use scalars.
        Since:
        4.1
      • isUseScalar

        public boolean isUseScalar()