Class SQLTemplate<T>

java.lang.Object
org.apache.cayenne.query.SQLTemplate<T>
Type Parameters:
T - the type of the result elements. Depending on the query root and configuration, this may be a persistent object, a DataRow, a scalar or an Object[].
All Implemented Interfaces:
Query, Select<T>
public class SQLTemplate<T> extends Object implements Select<T>
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

  • Field Details

    • defaultTemplate

      protected String defaultTemplate

    • templates

      protected Map<String,String> templates

    • parameters

      protected Map<String,?>[] parameters

    • positionalParams

      protected List<Object> positionalParams

    • columnNamesCapitalization

      protected CapsStrategy columnNamesCapitalization

    • result

      protected SQLResult result

    • returnGeneratedKeys

      protected boolean returnGeneratedKeys

    • root

      protected Object root
      The root object of this query. May be an entity name, Java class, ObjEntity or DbEntity, depending on how the query was constructed. Null root is allowed.

  • Constructor Details

    • 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 Details

    • setResultColumnsTypes

      public void setResultColumnsTypes(Class<?>... types)

    • getRoot

      public Object getRoot()
      Returns the root of this query.

    • setRoot

      public void setRoot(Object value)
      Sets the root of the query.
      Parameters:
      value - The new root
      Throws:
      IllegalArgumentException - if value is not a String, ObjEntity, DbEntity, Procedure, DataMap, Class or null.

    • toString

      public String toString()
      Overrides:
      toString in class Object

    • route

      public void route(QueryRouter router, EntityResolver resolver, Query substitutedQuery)
      Description copied from interface: Query
      A callback method invoked by Cayenne during the routing phase of the query execution. Mapping of DataNodes is provided by QueryRouter. Query should use a QueryRouter.route(DataNode, Query, Query) callback method to route itself. Query can create one or more substitute queries or even provide its own DataNode to execute itself.

      The default implementation relies on the EntityResolver to find the DataMap based on the query metadata. This mechanism is sufficient for most queries that "know" their root.

      Specified by:
      route in interface Query

    • getMetaData

      public QueryMetadata getMetaData(EntityResolver resolver)
      Description copied from interface: Query
      Returns query runtime parameters. The method is called at various stages of the execution by Cayenne access stack to retrieve query parameters. EntityResolver instance is passed to this method, meaning that the query doesn't need to store direct references to Cayenne mapping objects and can resolve them at runtime.
      Specified by:
      getMetaData in interface Query
      Since:
      1.2

    • createSQLAction

      public SQLAction createSQLAction(SQLActionVisitor visitor)
      Calls sqlAction(this) on the visitor.
      Specified by:
      createSQLAction in interface Query
      Since:
      1.2

    • selectFirst

      public T selectFirst(ObjectContext context)
      Description copied from interface: Select
      Selects a single object using provided context. The query itself can match any number of objects, but will return only the first one. It returns null if no objects were matched.

      If it matched more than one object, the first object from the list is returned. This makes 'selectFirst' different from Select.selectOne(ObjectContext), which would throw in this situation. 'selectFirst' is useful e.g. when the query is ordered and we only want to see the first object (e.g. "most recent news article"), etc.

      Selecting the first object via "Select.selectFirst(ObjectContext)" is more comprehensible than selecting via "ObjectContext.selectFirst(Select)", because implementations of "Select" set fetch size limit to one.

      Specified by:
      selectFirst in interface Select<T>
      Since:
      5.0

    • 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<T> queryWithParameters(Map<String,?>... parameters)
      Returns a new query built using this query as a prototype and a new set of parameters.

    • getCacheStrategy

      public QueryCacheStrategy getCacheStrategy()
      Since:
      3.0

    • setCacheStrategy

      public void setCacheStrategy(QueryCacheStrategy strategy)
      Since:
      3.0

    • getCacheGroup

      public String getCacheGroup()
      Since:
      4.0

    • setCacheGroup

      public void setCacheGroup(String cacheGroup)
      Since:
      4.0

    • useLocalCache

      @Deprecated(since="5.0", forRemoval=true) public void useLocalCache()
      Deprecated, for removal: This API element is subject to removal in a future version.
      use setCacheStrategy(QueryCacheStrategy) with QueryCacheStrategy.LOCAL_CACHE
      Since:
      4.0

    • useLocalCache

      @Deprecated(since="5.0", forRemoval=true) public void useLocalCache(String cacheGroup)
      Deprecated, for removal: This API element is subject to removal in a future version.
      use setCacheStrategy(QueryCacheStrategy) with QueryCacheStrategy.LOCAL_CACHE and setCacheGroup(String)
      Since:
      4.0

    • useSharedCache

      @Deprecated(since="5.0", forRemoval=true) public void useSharedCache()
      Deprecated, for removal: This API element is subject to removal in a future version.
      use setCacheStrategy(QueryCacheStrategy) with QueryCacheStrategy.SHARED_CACHE
      Since:
      4.0

    • useSharedCache

      @Deprecated(since="5.0", forRemoval=true) public void useSharedCache(String cacheGroup)
      Deprecated, for removal: This API element is subject to removal in a future version.
      use setCacheStrategy(QueryCacheStrategy) with QueryCacheStrategy.SHARED_CACHE and setCacheGroup(String)
      Since:
      4.0

    • 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:

    • 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.

    • getPrefetchTree

      public PrefetchTreeNode getPrefetchTree()
      Since:
      1.2

    • addPrefetch

      public PrefetchTreeNode addPrefetch(String prefetchPath)
      Adds a prefetch.
      Since:
      1.2

    • 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

    • setQueryTimeout

      public void setQueryTimeout(int queryTimeout)
      Sets query timeout.
      Since:
      4.2

    • getQueryTimeout

      public int getQueryTimeout()
      Returns:
      query timeout
      Since:
      4.2

    • 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()

    • setResultMapper

      public void setResultMapper(Function<?,?> resultMapper)
      Since:
      4.2