# Class SQLTemplate

java.lang.Object
org.apache.cayenne.query.CacheableQuery
org.apache.cayenne.query.AbstractQuery
org.apache.cayenne.query.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 Summary

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

  ### Fields inherited from class AbstractQuery

  `root`

  ### Fields inherited from class CacheableQuery

  `LOGGER`

- ## Constructor Summary

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

- ## Method Summary

  Modifier and Type
  Method
  Description
  `PrefetchTreeNode`
  `addPrefetch(String prefetchPath)`
  Adds a prefetch.
  `void`
  `addPrefetch(PrefetchTreeNode prefetchElement)`
  Adds a prefetch with specified relationship path to the query.
  `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.
  `protected org.apache.cayenne.query.BaseQueryMetadata`
  `getBaseMetaData()`
  `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()`
  Utility method to get the first set of parameters, since most queries will only have one.
  `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()`
  `int`
  `getQueryTimeout()`
  `SQLResult`
  `getResult()`
  `List<Class<?>>`
  `getResultColumnsTypes()`
  `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()`
  `boolean`
  `isReturnGeneratedKeys()`
  `boolean`
  `isUseScalar()`
  `Iterator<Map<String,?>>`
  `parametersIterator()`
  Returns an iterator over parameter sets.
  `int`
  `parametersSize()`
  Returns the number of parameter sets.
  `SQLTemplate`
  `queryWithParameters(Map<String,?>... parameters)`
  Returns a new query built using this query as a prototype and a new set of parameters.
  `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`
  `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)`
  Utility method to initialize query with one or more sets of parameters.
  `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`
  `setQueryTimeout(int queryTimeout)`
  Sets query timeout.
  `void`
  `setResult(SQLResult resultSet)`
  Sets an optional explicit mapping of the result set.
  `void`
  `setResultColumnsTypes(Class<?>... types)`
  `void`
  `setResultColumnsTypes(List<Class<?>> resultColumnsTypes)`
  `void`
  `setResultMapper(Function<?,?> resultMapper)`
  `void`
  `setReturnGeneratedKeys(boolean returnGeneratedKeys)`
  Sets flag to return generated keys.
  `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`
  `setUseScalar(boolean useScalar)`
  Sets flag to use scalars.

  ### Methods inherited from class AbstractQuery

  `getRoot, toString`

  ### Methods inherited from class CacheableQuery

  `getCacheGroup, getCacheStrategy, setCacheGroup, setCacheStrategy, useLocalCache, useLocalCache, useSharedCache, useSharedCache`

  ### Methods inherited from class Object

  `clone, equals, finalize, getClass, hashCode, notify, notifyAll, wait, wait, wait`

- ## Field Details

  - ### COLUMN_NAME_CAPITALIZATION_PROPERTY

    public static final String COLUMN_NAME_CAPITALIZATION_PROPERTY
    See Also:
      - Constant Field Values

  - ### 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

- ## 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)

  - ### 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

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

  - ### 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