Package org.apache.cayenne.query

## 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 org.apache.cayenne.query.IndirectQuery

      `dataMap, lastResolver, name, replacementQuery`

  - ### Constructor Summary

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

  - ### Method Summary

    All Methods Static Methods Instance Methods Concrete Methods Deprecated Methods
    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)` |
    `SQLSelect<T>` | `offset​(int fetchOffset)` |
    `SQLSelect<T>` | `pageSize​(int pageSize)` |
    `SQLSelect<T>` | `param​(String name, Object value)` |
    `SQLSelect<T>` | `params​(String name, Object value)` | Deprecated. since 4.2 Use `param(String, Object)`
    `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 DataObjects.
    `SQLSelect<T>` | `queryTimeout​(int queryTimeout)` | Sets query timeout
    `static <T> SQLSelect<T>` | `scalarQuery​(Class<T> type, String sql)` | Deprecated. since 4.2.
    `static <T> SQLSelect<T>` | `scalarQuery​(Class<T> type, String dataMapName, String sql)` | Deprecated. since 4.2.
    `static SQLSelect<Object[]>` | `scalarQuery​(String sql)` | Deprecated. since 4.2.
    `static SQLSelect<Object[]>` | `scalarQuery​(String sql, Class<?>... types)` | Deprecated. since 4.2.
    `static <T> SQLSelect<T>` | `scalarQuery​(String sql, Class<T> type)` | Creates query that selects scalar value and uses default routing
    `static SQLSelect<Object[]>` | `scalarQuery​(String sql, String dataMapName)` | Deprecated. since 4.2.
    `static SQLSelect<Object[]>` | `scalarQuery​(String sql, String dataMapName, Class<?>... types)` | Deprecated. since 4.2.
    `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 org.apache.cayenne.query.IndirectQuery

      `createSQLAction, getMetaData, getReplacementQuery, route`

    - ### Methods inherited from class java.lang.Object

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

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

      `createSQLAction, getMetaData, route`

- - ### Field Detail

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

    - #### SQLSelect

          public SQLSelect​(String sql)

    - #### SQLSelect

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

  - ### Method Detail

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

    - #### scalarQuery

          @Deprecated
          public static <T> SQLSelect<T> scalarQuery​(Class<T> type,
                                                     String sql)

      Deprecated.
      since 4.2. Use `#scalarQuery(String, Class)` method.
      Creates a query that selects scalar values and uses default routing.

    - #### scalarQuery

          @Deprecated
          public static <T> SQLSelect<T> scalarQuery​(Class<T> type,
                                                     String dataMapName,
                                                     String sql)

      Deprecated.
      since 4.2. Use `#scalarQuery(String, String, Class)` method.
      Creates a query that selects scalar values and uses routing based on the provided DataMap name.

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

    - #### scalarQuery

          @Deprecated
          public static SQLSelect<Object[]> scalarQuery​(String sql)

      Deprecated.
      since 4.2. Use `columnQuery(String)`
      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

    - #### scalarQuery

          @Deprecated
          public static SQLSelect<Object[]> scalarQuery​(String sql,
                                                        String dataMapName)

      Deprecated.
      since 4.2. Use `columnQuery(String, String)`
      Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
      Since:
        4.1

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

    - #### scalarQuery

          @Deprecated
          public static SQLSelect<Object[]> scalarQuery​(String sql,
                                                        Class<?>... types)

      Deprecated.
      since 4.2. Use `columnQuery(String, Class...)`
      Creates query that selects scalar values (as Object[]) and uses default routing
      Since:
        4.1

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

    - #### scalarQuery

          @Deprecated
          public static SQLSelect<Object[]> scalarQuery​(String sql,
                                                        String dataMapName,
                                                        Class<?>... types)

      Deprecated.
      since 4.2. Use `columnQuery(String, String, Class...)`
      Creates query that selects scalar values (as Object[]) and uses routing based on the provided DataMap name.
      Since:
        4.1

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

    - #### params

          @Deprecated
          public SQLSelect<T> params​(String name,
                                     Object value)

      Deprecated.
      since 4.2 Use `param(String, Object)`

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