# Class SQLSelect<T>

java.lang.Object
org.apache.cayenne.query.IndirectQuery
org.apache.cayenne.query.SQLSelect<T>

All Implemented Interfaces:
  `Serializable, Query, Select<T>`

public class SQLSelect<T> extends IndirectQuery implements Select<T>
A selecting query based on raw SQL and featuring fluent API.

Since:
  4.0
See Also:
  - Serialized Form

- ## Field Summary

  Fields
  Modifier and Type
  Field
  Description
  `protected String`
  `cacheGroup`
  `protected QueryCacheStrategy`
  `cacheStrategy`
  `protected CapsStrategy`
  `columnNameCaps`
  `protected String`
  `dataMapName`
  `protected int`
  `limit`
  `protected int`
  `offset`
  `protected int`
  `pageSize`
  `protected Map<String,Object>`
  `params`
  `protected Class<T>`
  `persistentType`
  `protected List<Object>`
  `positionalParams`
  `protected PrefetchTreeNode`
  `prefetches`
  `protected int`
  `queryTimeout`
  `protected StringBuilder`
  `sqlBuffer`
  `protected int`
  `statementFetchSize`

  ### Fields inherited from class IndirectQuery

  `dataMap, lastResolver, name, replacementQuery`

- ## Constructor Summary

  Constructors
  Constructor
  Description
  `SQLSelect(Class<T> persistentType, String sql)`
  `SQLSelect(String sql)`

- ## Method Summary

  Modifier and Type
  Method
  Description
  `SQLSelect<T>`
  `addPrefetch(String path, int semantics)`
  Merges a prefetch path with specified semantics into the query prefetch tree.
  `SQLSelect<T>`
  `addPrefetch(PrefetchTreeNode node)`
  Merges a prefetch into the query prefetch tree.
  `SQLSelect<T>`
  `append(String sqlChunk)`
  Appends a piece of SQL to the previously stored SQL template.
  `ResultBatchIterator<T>`
  `batchIterator(ObjectContext context, int size)`
  Creates a ResultBatchIterator based on the provided context and batch size.
  `SQLSelect<T>`
  `cacheGroup(String cacheGroup)`
  `SQLSelect<T>`
  `cacheStrategy(QueryCacheStrategy strategy)`
  `SQLSelect<T>`
  `cacheStrategy(QueryCacheStrategy strategy, String cacheGroup)`
  `SQLSelect<T>`
  `columnNameCaps(CapsStrategy columnNameCaps)`
  Sets a column name capitalization policy applied to selecting queries.
  `static SQLSelect<Object[]>`
  `columnQuery(String sql)`
  Creates query that selects scalar values (as Object[]) and uses default routing
  `static SQLSelect<Object[]>`
  `columnQuery(String sql, Class<?>... types)`
  Creates query that selects scalar values (as Object[]) and uses default routing
  `static SQLSelect<Object[]>`
  `columnQuery(String sql, String dataMapName)`
  Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
  `static SQLSelect<Object[]>`
  `columnQuery(String sql, String dataMapName, Class<?>... types)`
  Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
  `protected Query`
  `createReplacementQuery(EntityResolver resolver)`
  Creates a substitute query.
  `static SQLSelect<DataRow>`
  `dataRowQuery(String sql)`
  Creates a query that selects DataRows and uses default routing.
  `static SQLSelect<DataRow>`
  `dataRowQuery(String sql, Class<?>... types)`
  Creates a query that selects DataRows and uses default routing.
  `static SQLSelect<DataRow>`
  `dataRowQuery(String dataMapName, String sql)`
  Creates a query that selects DataRows and uses routing based on the provided DataMap name.
  `static SQLSelect<DataRow>`
  `dataRowQuery(String dataMapName, String sql, Class<?>... types)`
  Creates a query that selects DataRows and uses routing based on the provided DataMap name.
  `String`
  `getCacheGroup()`
  `QueryCacheStrategy`
  `getCacheStrategy()`
  `CapsStrategy`
  `getColumnNameCaps()`
  Returns a column name capitalization policy applied to selecting queries.
  `int`
  `getLimit()`
  `int`
  `getOffset()`
  `int`
  `getPageSize()`
  `Map<String,Object>`
  `getParams()`
  Returns a potentially immmutable map of named parameters that will be bound to SQL.
  `List<Object>`
  `getPositionalParams()`
  Returns a potentially immmutable list of positional parameters that will be bound to SQL.
  `int`
  `getQueryTimeout()`
  `String`
  `getSql()`
  `int`
  `getStatementFetchSize()`
  `boolean`
  `isFetchingDataRows()`
  `void`
  `iterate(ObjectContext context, ResultIteratorCallback<T> callback)`
  Creates a ResultIterator based on the provided context and passes it to a callback for processing.
  `ResultIterator<T>`
  `iterator(ObjectContext context)`
  Creates a ResultIterator based on the provided context.
  `SQLSelect<T>`
  `limit(int fetchLimit)`
  `SQLSelect<T>`
  `localCache()`
  Instructs Cayenne to look for query results in the "local" cache when running the query.
  `SQLSelect<T>`
  `localCache(String cacheGroup)`
  Instructs Cayenne to look for query results in the "local" cache when running the query.
  `SQLSelect<T>`
  `lowerColumnNames()`
  Equivalent of setting `CapsStrategy.LOWER`
  `<E> SQLSelect<E>`
  `map(Function<T,E> mapper)`
  Map result of this query by processing with a given function.
  `SQLSelect<T>`
  `offset(int fetchOffset)`
  `SQLSelect<T>`
  `pageSize(int pageSize)`
  `SQLSelect<T>`
  `param(String name, Object value)`
  `SQLSelect<T>`
  `params(Map<String,?> parameters)`
  `SQLSelect<T>`
  `paramsArray(Object... params)`
  Initializes positional parameters of the query.
  `SQLSelect<T>`
  `paramsList(List<Object> params)`
  Initializes positional parameters of the query.
  `static <T> SQLSelect<T>`
  `query(Class<T> type, String sql)`
  Creates a query that selects Persistent objects.
  `SQLSelect<T>`
  `queryTimeout(int queryTimeout)`
  Sets query timeout
  `static <T> SQLSelect<T>`
  `scalarQuery(String sql, Class<T> type)`
  Creates query that selects scalar value and uses default routing
  `static <T> SQLSelect<T>`
  `scalarQuery(String sql, String dataMapName, Class<T> type)`
  Creates query that selects scalar value and uses default routing
  `List<T>`
  `select(ObjectContext context)`
  Selects objects using provided context.
  `T`
  `selectFirst(ObjectContext context)`
  Selects a single object using provided context.
  `T`
  `selectOne(ObjectContext context)`
  Selects a single object using provided context.
  `SQLSelect<T>`
  `sharedCache()`
  Instructs Cayenne to look for query results in the "shared" cache when running the query.
  `SQLSelect<T>`
  `sharedCache(String cacheGroup)`
  Instructs Cayenne to look for query results in the "shared" cache when running the query.
  `SQLSelect<T>`
  `statementFetchSize(int size)`
  Sets JDBC statement's fetch size (0 for no default size)
  `SQLSelect<T>`
  `upperColumnNames()`
  Equivalent of setting `CapsStrategy.UPPER`

  ### Methods inherited from class IndirectQuery

  `createSQLAction, getMetaData, getReplacementQuery, route`

  ### Methods inherited from class Object

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

  ### Methods inherited from interface Query

  `createSQLAction, getMetaData, route`

- ## Field Details

  - ### persistentType

    protected Class<T> persistentType

  - ### dataMapName

    protected String dataMapName

  - ### sqlBuffer

    protected StringBuilder sqlBuffer

  - ### cacheStrategy

    protected QueryCacheStrategy cacheStrategy

  - ### cacheGroup

    protected String cacheGroup

  - ### params

    protected Map<String,Object> params

  - ### positionalParams

    protected List<Object> positionalParams

  - ### columnNameCaps

    protected CapsStrategy columnNameCaps

  - ### limit

    protected int limit

  - ### offset

    protected int offset

  - ### pageSize

    protected int pageSize

  - ### statementFetchSize

    protected int statementFetchSize

  - ### queryTimeout

    protected int queryTimeout

  - ### prefetches

    protected PrefetchTreeNode prefetches

- ## Constructor Details

  - ### SQLSelect

    public SQLSelect(String sql)

  - ### SQLSelect

    public SQLSelect(Class<T> persistentType, String sql)

- ## Method Details

  - ### dataRowQuery

    public static SQLSelect<DataRow> dataRowQuery(String sql)
    Creates a query that selects DataRows and uses default routing.

  - ### dataRowQuery

    public static SQLSelect<DataRow> dataRowQuery(String sql, Class<?>... types)
    Creates a query that selects DataRows and uses default routing.
    Since:
      4.1

  - ### dataRowQuery

    public static SQLSelect<DataRow> dataRowQuery(String dataMapName, String sql, Class<?>... types)
    Creates a query that selects DataRows and uses routing based on the provided DataMap name.
    Since:
      4.1

  - ### dataRowQuery

    public static SQLSelect<DataRow> dataRowQuery(String dataMapName, String sql)
    Creates a query that selects DataRows and uses routing based on the provided DataMap name.

  - ### query

    public static <T> SQLSelect<T> query(Class<T> type, String sql)
    Creates a query that selects Persistent objects.

  - ### scalarQuery

    public static <T> SQLSelect<T> scalarQuery(String sql, Class<T> type)
    Creates query that selects scalar value and uses default routing
    Since:
      4.1

  - ### scalarQuery

    public static <T> SQLSelect<T> scalarQuery(String sql, String dataMapName, Class<T> type)
    Creates query that selects scalar value and uses default routing
    Since:
      4.1

  - ### columnQuery

    public static SQLSelect<Object[]> columnQuery(String sql)
    Creates query that selects scalar values (as Object[]) and uses default routing
    Since:
      4.2

  - ### columnQuery

    public static SQLSelect<Object[]> columnQuery(String sql, String dataMapName)
    Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
    Since:
      4.2

  - ### columnQuery

    public static SQLSelect<Object[]> columnQuery(String sql, Class<?>... types)
    Creates query that selects scalar values (as Object[]) and uses default routing
    Since:
      4.2

  - ### columnQuery

    public static SQLSelect<Object[]> columnQuery(String sql, String dataMapName, Class<?>... types)
    Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
    Since:
      4.2

  - ### select

    public List<T> select(ObjectContext context)
    Description copied from interface: `Select`
    Selects objects using provided context.

    Essentially the inversion of "ObjectContext.select(Select)".

    Specified by:
      `select` in interface `Select<T>`

  - ### selectOne

    public T selectOne(ObjectContext context)
    Description copied from interface: `Select`
    Selects a single object using provided context. The query is expected to match zero or one object. It returns null if no objects were matched. If query matched more than one object, `CayenneRuntimeException` is thrown.

    Essentially the inversion of "ObjectContext.selectOne(Select)".

    Specified by:
      `selectOne` in interface `Select<T>`

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

  - ### iterate

    public void iterate(ObjectContext context, ResultIteratorCallback<T> callback)
    Description copied from interface: `Select`
    Creates a ResultIterator based on the provided context and passes it to a callback for processing. The caller does not need to worry about closing the iterator. This method takes care of it.

    Essentially the inversion of "ObjectContext.iterate(Select, ResultIteratorCallback)".

    Specified by:
      `iterate` in interface `Select<T>`

  - ### iterator

    public ResultIterator<T> iterator(ObjectContext context)
    Description copied from interface: `Select`
    Creates a ResultIterator based on the provided context. It is usually backed by an open result set and is useful for processing of large data sets, preserving a constant memory footprint. The caller must wrap iteration in try/finally (or try-with-resources for Java 1.7 and higher) and close the ResultIterator explicitly. Or use `Select.iterate(ObjectContext, ResultIteratorCallback)` as an alternative.

    Essentially the inversion of "ObjectContext.iterator(Select)".

    Specified by:
      `iterator` in interface `Select<T>`

  - ### batchIterator

    public ResultBatchIterator<T> batchIterator(ObjectContext context, int size)
    Description copied from interface: `Select`
    Creates a ResultBatchIterator based on the provided context and batch size. It is usually backed by an open result set and is useful for processing of large data sets, preserving a constant memory footprint. The caller must wrap iteration in try/finally (or try-with-resources for Java 1.7 and higher) and close the ResultBatchIterator explicitly.
    Specified by:
      `batchIterator` in interface `Select<T>`

  - ### isFetchingDataRows

    public boolean isFetchingDataRows()

  - ### getSql

    public String getSql()

  - ### append

    public SQLSelect<T> append(String sqlChunk)
    Appends a piece of SQL to the previously stored SQL template.

  - ### param

    public SQLSelect<T> param(String name, Object value)
    Since:
      4.2

  - ### params

    public SQLSelect<T> params(Map<String,?> parameters)

  - ### paramsArray

    public SQLSelect<T> paramsArray(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.

  - ### paramsList

    public SQLSelect<T> paramsList(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.

  - ### getParams

    public Map<String,Object> getParams()
    Returns a potentially immmutable map of named parameters that will be bound to SQL.

  - ### getPositionalParams

    public List<Object> getPositionalParams()
    Returns a potentially immmutable list of positional parameters that will be bound to SQL.

  - ### createReplacementQuery

    protected Query createReplacementQuery(EntityResolver resolver)
    Description copied from class: `IndirectQuery`
    Creates a substitute query. An implementor is free to provide an arbitrary replacement query.
    Specified by:
      `createReplacementQuery` in class `IndirectQuery`

  - ### localCache

    public SQLSelect<T> localCache()
    Instructs Cayenne to look for query results in the "local" cache when running the query. This is a short-hand notation for:

        query.cacheStrategy(QueryCacheStrategy.LOCAL_CACHE);

  - ### localCache

    public SQLSelect<T> localCache(String cacheGroup)
    Instructs Cayenne to look for query results in the "local" cache when running the query. This is a short-hand notation for:

        query.cacheStrategy(QueryCacheStrategy.LOCAL_CACHE, cacheGroup);

  - ### sharedCache

    public SQLSelect<T> sharedCache()
    Instructs Cayenne to look for query results in the "shared" cache when running the query. This is a short-hand notation for:

        query.cacheStrategy(QueryCacheStrategy.SHARED_CACHE);

  - ### sharedCache

    public SQLSelect<T> sharedCache(String cacheGroup)
    Instructs Cayenne to look for query results in the "shared" cache when running the query. This is a short-hand notation for:

        query.cacheStrategy(QueryCacheStrategy.SHARED_CACHE, cacheGroup);

  - ### getCacheStrategy

    public QueryCacheStrategy getCacheStrategy()

  - ### cacheStrategy

    public SQLSelect<T> cacheStrategy(QueryCacheStrategy strategy)

  - ### cacheStrategy

    public SQLSelect<T> cacheStrategy(QueryCacheStrategy strategy, String cacheGroup)

  - ### getCacheGroup

    public String getCacheGroup()

  - ### cacheGroup

    public SQLSelect<T> cacheGroup(String cacheGroup)

  - ### getColumnNameCaps

    public CapsStrategy getColumnNameCaps()
    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.

  - ### columnNameCaps

    public SQLSelect<T> columnNameCaps(CapsStrategy columnNameCaps)
    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.

  - ### upperColumnNames

    public SQLSelect<T> upperColumnNames()
    Equivalent of setting `CapsStrategy.UPPER`

  - ### lowerColumnNames

    public SQLSelect<T> lowerColumnNames()
    Equivalent of setting `CapsStrategy.LOWER`

  - ### getLimit

    public int getLimit()

  - ### limit

    public SQLSelect<T> limit(int fetchLimit)

  - ### getOffset

    public int getOffset()

  - ### offset

    public SQLSelect<T> offset(int fetchOffset)

  - ### getPageSize

    public int getPageSize()

  - ### pageSize

    public SQLSelect<T> pageSize(int pageSize)

  - ### statementFetchSize

    public SQLSelect<T> statementFetchSize(int size)
    Sets JDBC statement's fetch size (0 for no default size)

  - ### queryTimeout

    public SQLSelect<T> queryTimeout(int queryTimeout)
    Sets query timeout
    Since:
      4.2

  - ### getStatementFetchSize

    public int getStatementFetchSize()
    Returns:
      JBDC statement's fetch size

  - ### getQueryTimeout

    public int getQueryTimeout()

  - ### addPrefetch

    public SQLSelect<T> addPrefetch(String path, int semantics)
    Merges a prefetch path with specified semantics into the query prefetch tree.
    Parameters:
      `path` - Path expression
      `semantics` - Defines a strategy to prefetch relationships. See `PrefetchTreeNode`
    Returns:
      this object
    Since:
      4.1

  - ### addPrefetch

    public SQLSelect<T> addPrefetch(PrefetchTreeNode node)
    Merges a prefetch into the query prefetch tree.
    Parameters:
      `node` - Prefetch which will added to query prefetch tree
    Returns:
      this object
    Since:
      4.1

  - ### map

    public <E> SQLSelect<E> map(Function<T,E> mapper)
    Map result of this query by processing with a given function.
    Could be used to map plain Object[] to some domain-specific object.
    Note: this method could be called multiple time, result will be mapped by all functions in the call order.
    Type Parameters:
      `E` - new result type
    Parameters:
      `mapper` - function that maps result to the required type.
    Returns:
      this query with changed result type
    Since:
      4.2
    See Also:
      - `ColumnSelect.map(Function)`