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 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 org.apache.cayenne.query.AbstractQuery
root
-
Fields inherited from class org.apache.cayenne.query.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
All Methods Instance Methods Concrete Methods 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 queryvoid
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 org.apache.cayenne.query.AbstractQuery
getRoot, toString
-
Methods inherited from class org.apache.cayenne.query.CacheableQuery
getCacheGroup, getCacheStrategy, setCacheGroup, setCacheStrategy, useLocalCache, useLocalCache, useSharedCache, useSharedCache
-
-
-
-
Field Detail
-
COLUMN_NAME_CAPITALIZATION_PROPERTY
public static final String COLUMN_NAME_CAPITALIZATION_PROPERTY
- See Also:
- Constant Field Values
-
defaultTemplate
protected String defaultTemplate
-
columnNamesCapitalization
protected CapsStrategy columnNamesCapitalization
-
result
protected SQLResult result
-
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
-
-
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 classAbstractQuery
- 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 interfaceQuery
- Overrides:
route
in classAbstractQuery
-
getMetaData
public QueryMetadata getMetaData(EntityResolver resolver)
Description copied from class:AbstractQuery
Returns default select parameters.- Specified by:
getMetaData
in interfaceQuery
- Overrides:
getMetaData
in classAbstractQuery
- Since:
- 1.2
-
createSQLAction
public SQLAction createSQLAction(SQLActionVisitor visitor)
Calls sqlAction(this) on the visitor.- Specified by:
createSQLAction
in interfaceQuery
- Specified by:
createSQLAction
in classAbstractQuery
- 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 interfaceParameterizedQuery
- Since:
- 1.1
-
getBaseMetaData
protected org.apache.cayenne.query.BaseQueryMetadata getBaseMetaData()
- Specified by:
getBaseMetaData
in classCacheableQuery
-
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. UnlikegetTemplate(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 theSQLResult
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
-
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
-
-