org.apache.cayenne.query

## Class SQLTemplate

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

  ### Parameter Sets

  SQLTemplate supports multiple sets of parameters, so a single query can be executed multiple times with different parameters. "Scrolling" through parameter list is done by calling `parametersIterator()`. This iterator goes over parameter sets, returning a Map on each call to "next()"

  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 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)` Deprecated. since 3.1, use SQLTemplate(DataMap rootMap, String defaultTemplate, boolean isFetchingDataRows) instead
    `SQLTemplate(DataMap rootMap, String defaultTemplate, boolean isFetchingDataRows)`
    `SQLTemplate(DbEntity rootEntity, String defaultTemplate)`
    `SQLTemplate(ObjEntity rootEntity, String defaultTemplate)`
    `SQLTemplate(String objEntityName, String defaultTemplate)`

  - ### Method Summary

    All Methods Instance Methods Concrete 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` | `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.
    `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<?>` | `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` | `setCacheGroups(String... cacheGroups)`
    `void` | `setCacheStrategy(QueryCacheStrategy strategy)`
    `void` | `setColumnNamesCapitalization(CapsStrategy columnNameCapitalization)` Sets a column name capitalization policy applied to selecting queries.
    `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` | `setResult(SQLResult resultSet)` Sets an optional explicit mapping of the result set.
    `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.

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

      `acceptVisitor, getDataMap, getName, getRoot, route, setDataMap, setName, setRoot, 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, route`

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

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

          @Deprecated
          public SQLTemplate(DataMap rootMap,
                                          String defaultTemplate)

      Deprecated. since 3.1, use SQLTemplate(DataMap rootMap, String defaultTemplate, boolean isFetchingDataRows) instead
      Since:
        1.2

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

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

          public Iterator<?> 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.

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

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

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

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

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

Copyright © 2001–2017 Apache Cayenne. All rights reserved.