SQLTemplate (Cayenne Server 4.0.M2 API)

java.lang.Object
- org.apache.cayenne.query.AbstractQuery
- - org.apache.cayenne.query.SQLTemplate

All Implemented Interfaces:

Serializable, ConfigurationNode, ParameterizedQuery, Query, XMLSerializable
```
public class SQLTemplate
extends AbstractQuery
implements ParameterizedQuery, XMLSerializable
```
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 Summary

Fields
Modifier and Type	Field and Description
`protected CapsStrategy`	`columnNamesCapitalization`
`protected String`	`defaultTemplate`
`protected Map<String,?>[]`	`parameters`
`protected List<Object>`	`positionalParams`
`protected SQLResult`	`result`
`protected Map<String,String>`	`templates`

Fields inherited from class org.apache.cayenne.query.AbstractQuery
dataMap, name, root

Constructor Summary

Constructors
Constructor and Description
`SQLTemplate()` Creates an empty SQLTemplate.
`SQLTemplate(Class<?> rootClass, String defaultTemplate)`
`SQLTemplate(DataMap rootMap, String defaultTemplate, boolean isFetchingDataRows)`
`SQLTemplate(DbEntity rootEntity, String defaultTemplate)`
`SQLTemplate(ObjEntity rootEntity, String defaultTemplate)`
`SQLTemplate(String defaultTemplate, boolean isFetchingDataRows)` Creates a SQLTemplate without an explicit root.
`SQLTemplate(String objEntityName, String defaultTemplate)`

Method Summary

Methods
Modifier and Type	Method and Description
`PrefetchTreeNode`	`addPrefetch(String prefetchPath)` Adds a prefetch.
`void`	`addPrefetches(Collection<String> prefetches)` Adds all prefetches from a provided collection.
`void`	`clearPrefetches()` Clears all prefetches.
`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.
`SQLAction`	`createSQLAction(SQLActionVisitor visitor)` Calls sqlAction(this) on the visitor.
`void`	`encodeAsXML(XMLEncoder encoder)` Prints itself as XML to the provided PrintWriter.
`String[]`	`getCacheGroups()`
`QueryCacheStrategy`	`getCacheStrategy()`
`CapsStrategy`	`getColumnNamesCapitalization()` Returns a column name capitalization policy applied to selecting queries.
`String`	`getCustomTemplate(String key)` Returns template for key, or null if there is no template configured for this key.
`String`	`getDataNodeName()` Returns a name of the DataNode to use with this SQLTemplate.
`String`	`getDefaultTemplate()` Returns default SQL template for this query.
`int`	`getFetchLimit()`
`int`	`getFetchOffset()`
`QueryMetadata`	`getMetaData(EntityResolver resolver)` Returns default select parameters.
`int`	`getPageSize()`
`Map<String,?>`	`getParameters()` Deprecated. since 4.0 in favor of `getParams()`, as multiple batches of parameters are superseded by the use of `QueryChain`.
`Map<String,?>`	`getParams()` Returns a map of named parameters that will be bound to SQL.
`List<Object>`	`getPositionalParams()` Returns a list of positional parameters that will be bound to SQL.
`PrefetchTreeNode`	`getPrefetchTree()`
`SQLResult`	`getResult()`
`int`	`getStatementFetchSize()`
`String`	`getTemplate(String key)` Returns a template for key, or a default template if a template for key is not found.
`Collection<String>`	`getTemplateKeys()` Returns a collection of configured template keys.
`void`	`initWithProperties(Map<String,?> properties)` Initializes query parameters using a set of properties.
`boolean`	`isFetchingDataRows()`
`Iterator<Map<String,?>>`	`parametersIterator()` Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of `QueryChain`.
`int`	`parametersSize()` Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of `QueryChain`.
`SQLTemplate`	`queryWithParameters(Map<String,?>... parameters)` Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of `QueryChain`. For an alternative use `createQuery(Map)`.
`void`	`removePrefetch(String prefetch)`
`void`	`removeTemplate(String key)`
`void`	`route(QueryRouter router, EntityResolver resolver, Query substitutedQuery)` Implements default routing mechanism relying on the EntityResolver to find DataMap based on the query root.
`void`	`setCacheGroups(String... cacheGroups)`
`void`	`setCacheStrategy(QueryCacheStrategy strategy)`
`void`	`setColumnNamesCapitalization(CapsStrategy columnNameCapitalization)` Sets a column name capitalization policy applied to selecting queries.
`void`	`setDataNodeName(String dataNodeName)` Sets a name of the DataNode to use with this SQLTemplate.
`void`	`setDefaultTemplate(String string)` Sets default SQL template for this query.
`void`	`setFetchingDataRows(boolean flag)`
`void`	`setFetchLimit(int fetchLimit)`
`void`	`setFetchOffset(int fetchOffset)`
`void`	`setPageSize(int pageSize)`
`void`	`setParameters(Map<String,?>... parameters)` Deprecated. since 4.0 in favor of `setParams(Map)`, as multiple batches of parameters are superseded by the use of `QueryChain`.
`void`	`setParams(Map<String,?> params)` Initializes named parameter of this query.
`void`	`setParamsArray(Object... params)` Initializes positional parameters of the query.
`void`	`setParamsList(List<Object> params)` Initializes positional parameters of the query.
`void`	`setResult(SQLResult resultSet)` Sets an optional explicit mapping of the result set.
`void`	`setRoot(Object value)` Sets the root of the query
`void`	`setStatementFetchSize(int size)` Sets statement's fetch size (0 for no default size)
`void`	`setTemplate(String key, String template)` Adds a SQL template string for a given key.
`void`	`useLocalCache(String... cacheGroups)` Instructs Cayenne to look for query results in the "local" cache when running the query.
`void`	`useSharedCache(String... cacheGroups)` Instructs Cayenne to look for query results in the "shared" cache when running the query.

Methods inherited from class org.apache.cayenne.query.AbstractQuery
acceptVisitor, getDataMap, getName, getRoot, setDataMap, setName, toString

Methods inherited from class java.lang.Object
clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait

Methods inherited from interface org.apache.cayenne.query.Query
getDataMap, getName

Methods inherited from interface org.apache.cayenne.configuration.ConfigurationNode
acceptVisitor

- Field Detail
  - 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
```
- 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
  - 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
  - getMetaData
```
public QueryMetadata getMetaData(EntityResolver resolver)
```
    Description copied from class: AbstractQuery
    
    Returns default select parameters.
    
    Specified by:
    
    getMetaData in interface Query
    
    Overrides:
    
    getMetaData in class AbstractQuery
    
    Since:
    
    1.2
  - createSQLAction
```
public SQLAction createSQLAction(SQLActionVisitor visitor)
```
    Calls sqlAction(this) on the visitor.
    
    Specified by:
    
    createSQLAction in interface Query
    
    Specified by:
    
    createSQLAction in class AbstractQuery
    
    Since:
    
    1.2
  - encodeAsXML
```
public void encodeAsXML(XMLEncoder encoder)
```
    Prints itself as XML to the provided PrintWriter.
    
    Specified by:
    
    encodeAsXML in interface XMLSerializable
    
    Since:
    
    1.1
  - initWithProperties
```
public void initWithProperties(Map<String,?> properties)
```
    Initializes query parameters using a set of properties.
    
    Since:
    
    1.1
  - parametersIterator
```
@Deprecated
public Iterator<Map<String,?>> parametersIterator()
```
    Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of QueryChain.
    
    Returns an iterator over parameter sets. Each element returned from the iterator is a java.util.Map.
  - parametersSize
```
@Deprecated
public int parametersSize()
```
    Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of QueryChain.
    
    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
```
@Deprecated
public SQLTemplate queryWithParameters(Map<String,?>... parameters)
```
    Deprecated. since 4.0 as multiple batches of parameters are superseded by the use of QueryChain. For an alternative use createQuery(Map).
    
    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
  - getCacheStrategy
```
public QueryCacheStrategy getCacheStrategy()
```
    Since:
    
    3.0
  - setCacheStrategy
```
public void setCacheStrategy(QueryCacheStrategy strategy)
```
    Since:
    
    3.0
  - getCacheGroups
```
public String[] getCacheGroups()
```
    Since:
    
    3.0
  - setCacheGroups
```
public void setCacheGroups(String... cacheGroups)
```
    Since:
    
    3.0
  - useLocalCache
```
public void useLocalCache(String... cacheGroups)
```
    Instructs Cayenne to look for query results in the "local" cache when running the query. This is a short-hand notation for:
```
 query.setCacheStrategy(QueryCacheStrategy.LOCAL_CACHE);
 query.setCacheGroups("group1", "group2");
 
```
    Since:
    
    4.0
  - useSharedCache
```
public void useSharedCache(String... cacheGroups)
```
    Instructs Cayenne to look for query results in the "shared" cache when running the query. This is a short-hand notation for:
```
 query.setCacheStrategy(QueryCacheStrategy.SHARED_CACHE);
 query.setCacheGroups("group1", "group2");
 
```
    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:
    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
```
@Deprecated
public Map<String,?> getParameters()
```
    Deprecated. since 4.0 in favor of getParams(), as multiple batches of parameters are superseded by the use of QueryChain.
    
    Utility method to get the first set of parameters, since most queries will only have one.
  - setParameters
```
@Deprecated
public void setParameters(Map<String,?>... parameters)
```
    Deprecated. since 4.0 in favor of setParams(Map), as multiple batches of parameters are superseded by the use of QueryChain.
    
    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
  - 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

Class SQLTemplate

Template Script

Per-Database Template Customization

Field Summary

Fields inherited from class org.apache.cayenne.query.AbstractQuery

Constructor Summary

Method Summary

Methods inherited from class org.apache.cayenne.query.AbstractQuery

Methods inherited from class java.lang.Object

Methods inherited from interface org.apache.cayenne.query.Query

Methods inherited from interface org.apache.cayenne.configuration.ConfigurationNode

Field Detail

defaultTemplate

templates

parameters

positionalParams

columnNamesCapitalization

result

Constructor Detail

SQLTemplate

SQLTemplate

SQLTemplate

SQLTemplate

SQLTemplate

SQLTemplate

SQLTemplate

Method Detail

setRoot

route

getMetaData

createSQLAction

encodeAsXML

initWithProperties

parametersIterator

parametersSize

setParams

setParamsArray

setParamsList

queryWithParameters

createQuery

getCacheStrategy

setCacheStrategy

getCacheGroups

setCacheGroups

useLocalCache

useSharedCache

getFetchLimit

setFetchLimit

getFetchOffset

setFetchOffset

getPageSize

setPageSize

setFetchingDataRows

isFetchingDataRows

getDefaultTemplate

setDefaultTemplate

getTemplate

getCustomTemplate

setTemplate

removeTemplate

getTemplateKeys

getParams

getPositionalParams

getParameters

setParameters

getPrefetchTree

addPrefetch

removePrefetch

addPrefetches

clearPrefetches

getColumnNamesCapitalization

setColumnNamesCapitalization

setResult

getResult

setStatementFetchSize

getStatementFetchSize

getDataNodeName

setDataNodeName