Package com.isomorphic.sql

# Class SQLDataSource

java.lang.Object
com.isomorphic.base.Base
com.isomorphic.datasource.DataSource
com.isomorphic.datasource.BasicDataSource
com.isomorphic.sql.SQLDataSource

All Implemented Interfaces:
  `com.isomorphic.base.IAutoConfigurable`, `com.isomorphic.datasource.Committable`, `com.isomorphic.datasource.FreeResourcesHandler`, `com.isomorphic.datasource.IType`, `com.isomorphic.interfaces.ISQLDataSource`, `IToJSON`, `Serializable`

public class SQLDataSource extends BasicDataSource implements com.isomorphic.interfaces.ISQLDataSource
A DataSource implementation for SQL database engines. SmartClient Server supports all major database products and allows you to connect a SmartClient databound widget to an existing database table with a few clicks in a simple wizard. DataSources created in this way require zero code and support all four CRUD operations out of the box.

SmartClient Server's SQL support is mature, feature-rich and very customizable. See the client reference documentation for details of configuring and using SQL DataSources - search for "sqlDataSource". For a wider general discussion of data integration options with SmartClient Server, search the client documentation for "serverDataIntegration"

Instances of this class are not safe to use by multiple concurrent threads.

See Also:
  - Serialized Form

- ## Field Summary

  ### Fields inherited from class com.isomorphic.datasource.DataSource

  `OP_ADD, OP_CLIENT_EXPORT, OP_CUSTOM, OP_DOWNLOAD_FILE, OP_FETCH, OP_GET_FILE, OP_GET_FILE_VERSION, OP_HAS_FILE, OP_HAS_FILE_VERSION, OP_LIST_FILE_VERSIONS, OP_LIST_FILES, OP_LOAD_SCHEMA, OP_REMOVE, OP_REMOVE_FILE, OP_REMOVE_FILE_VERSION, OP_RENAME_FILE, OP_SAVE_FILE, OP_UNIQUE_NAME, OP_UPDATE, OP_VALIDATE, OP_VIEW_FILE`

- ## Method Summary

  Modifier and Type
  Method
  Description
  `static int[]`
  `executeAsBatch(DSRequest[] dsRequests)`
  Executes an array of DSRequests in a single JDBC `Statement.executeBatch()` call.
  `static List<Integer>`
  `executeAsBatch(List<DSRequest> dsRequests)`
  Convenience implementation of `executeAsBatch(DSRequest[])`, if you prefer to pass and return Lists instead of arrays.
  `static String`
  `getHavingWithout(DSRequest dsRequest, String... fieldNames)`
  Generates SQL condition expression without the `fieldNames` requested to skip from `groupWhereClause` (aka having).
  `static String`
  `getPartialHaving(DSRequest dsRequest, String... fieldNames)`
  Generates partial SQL condition expression for the `fieldNames` requested for use in the `groupWhereClause` (aka having).
  `static String`
  `getPartialWhere(DSRequest dsRequest, String... fieldNames)`
  Generates partial SQL condition expression for the `fieldNames` requested for use in the regular `whereClause`.
  `static String`
  `getSQLClause(SQLClauseType clauseType, DSRequest dsRequest)`
  Returns the text of the generated SQL we will use to implement a clause of the supplied type for the supplied DSRequest.
  `protected String`
  `getSQLPrefix(DSRequest req)`
  Override this method if you want to programmatically provide a prefix to SQL generated for this DataSource.
  `protected String`
  `getSQLSuffix(DSRequest req)`
  Override this method if you want to programmatically provide a suffix to SQL generated for this DataSource.
  `List<String>`
  `getTableColumns()`
  Returns List of column names from the underlying database table DataSource is bound to.
  `static String`
  `getWhereWithout(DSRequest dsRequest, String... fieldNames)`
  Generates SQL condition expression without the `fieldNames` requested to skip from the regular `whereClause`.

  ### Methods inherited from class com.isomorphic.datasource.BasicDataSource

  `getAuditDataSource, getAuditRecord, getAuditRecord, getFTSEligibleFields, getFTSProviderForField, getFullTextSearchConfig, hasCustomDefaultFetchOperation, hasCustomLogic, isFieldFTSEligible, isFullTextSearchEnabled, shouldAutoJoinTransaction, shouldAutoStartTransaction, transformMultipleFields, transformMultipleFields, writeMultiAudits`

  ### Methods inherited from class com.isomorphic.datasource.DataSource

  `add, add, addDynamicDSGenerator, addDynamicDSGenerator, addDynamicDSGenerator, clearDynamicDSGenerators, convertRelativeDates, convertRelativeDates, convertRelativeDates, convertRelativeDates, convertRelativeDates, convertRelativeDates, execute, executeFileSource, fetch, fetch, fetchById, fetchById, fetchById, fetchSingle, fetchSingle, filter, filter, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, get1ManyRelationFields, getAuditChangedFieldsFieldName, getAuditRevisionFieldName, getAuditTimestampFieldName, getAuditTypeFieldName, getAuditUserFieldName, getDefaultDynamicDSGenerator, getDirectFields, getDynamicDSGenerators, getEnumConstantProperty, getEnumOrdinalProperty, getFieldNames, getFile, getFileAsInputStream, getFileAsString, getFileContentsField, getFileFormatField, getFileNameField, getFileTypeField, getID, getJoinDSName, getJoinDSName, getListProperties, getListProperties, getListProperty, getManyManyRelationFields, getMapProperty, getName, getNonIncludedFields, getObjectProperty, getOperationProperty, getPrimaryKey, getProperties, getProperties, getProperties, getProperty, getPropertyJavaClass, getRecordXPath, getRelatedDisplayRecord, getRelatedDisplayRecord, getRelatedDSName, getRelatedDSName, getRelatedTableAlias, getRelationFields, getStringInBrowser, getTableName, getTransactionObject, getTransactionObject, has1ManyRelationFields, hasFile, hasManyManyRelationFields, hasRecord, hasRecord, hasRelationFields, initialized, is1ManyRelationField, isExpensive, isExpensive, isManyManyRelationField, isModificationOperation, isRelationField, isServerOnly, listFiles, listFiles, listFiles, listFiles, listFiles, listFiles, remove, removeDynamicDSGenerator, removeDynamicDSGenerator, removeDynamicDSGenerator, removeFile, renameFile, saveFile, setEnumConstantProperty, setEnumOrdinalProperty, setEnumTranslateStrategy, setOmitNullMapValuesInResponse, setProperties, transformImportValue, transformImportValue, transformResponse, update, update, validate, validate, validate, validateRecord`

- ## Method Details

  - ### getTableColumns

    public List<String> getTableColumns()
    Returns List of column names from the underlying database table DataSource is bound to.

    This is based on the `dbName`, `schema` and `tableName` DataSource attributes to get the list of table columns. Note that the columns in returned list may differ from the DataSource fields as columns in database may have alternative names, there may be extra columns in underlying table or some fields may be missing in underlying table if they are using customSQL features at a DataSource level.

    If, for any reason, DataSource lacks the `tableName` attribute, `getTableColumns()` API falls back to returning the `getDirectFields()`.

    Returns:
      List of column names from the underlying database table DataSource is bound to.

  - ### getPartialWhere

    public static String getPartialWhere(DSRequest dsRequest, String... fieldNames) throws Exception
    Generates partial SQL condition expression for the `fieldNames` requested for use in the regular `whereClause`.

    The result is based on the criteria provided in `DSRequest` parameter and only criteria based on fields that are listed in `fieldNames` parameter will be generated, everything else will be skipped. If all fields are happen to be requested, then this method returns same SQL expression as is used in $defaultWhereClause Velocity context variable. If there's no criteria for the requested fields, then `('1'='1')` will be returned.

    If you want a partial WHERE clause that excludes certain fields instead of naming all included fields, see `getWhereWithout(DSRequest, String...)`. Also see OperationBinding.excludeCriteriaFields for excluding fields from automatic generation.

    If you are using aggregation, and you want to create a WHERE clause that applies to aggregated value, see `getPartialHaving(DSRequest, String...)` or `getHavingWithout(DSRequest, String...)`.;

    Parameters:
      `dsRequest` - `DSRequest` to pick criteria from
      `fieldNames` - requested field names
    Returns:
      SQL expression filtered by requested fields
    Throws:
      `Exception`
    See Also:
      - `getWhereWithout(DSRequest, String...)`
      - `getPartialHaving(DSRequest, String...)`
      - `getHavingWithout(DSRequest, String...)`

  - ### getWhereWithout

    public static String getWhereWithout(DSRequest dsRequest, String... fieldNames) throws Exception
    Generates SQL condition expression without the `fieldNames` requested to skip from the regular `whereClause`.

    The result is based on the criteria provided in `DSRequest` parameter and only criteria based on fields that are not listed in `fieldNames` parameter will be generated, everything on that list will skipped. If no fields are requested to skip, then this method returns same SQL expression as is used in $defaultWhereClause Velocity context variable. If all fields are requested to be skipped or there's no criteria after the skipping, then `('1'='1')` will be returned.

    If you want a partial WHERE clause that includes certain fields instead of naming all excluded fields, see `getPartialWhere(DSRequest, String...)`.

    If you are using aggregation, and you want to create a WHERE clause that applies to aggregated value, see `getPartialHaving(DSRequest, String...)` or `getHavingWithout(DSRequest, String...)`.;

    Parameters:
      `dsRequest` - `DSRequest` to pick criteria from
      `fieldNames` - field names to skip
    Returns:
      SQL expression filtered by requested fields
    Throws:
      `Exception`
    See Also:
      - `getPartialWhere(DSRequest, String...)`
      - `getPartialHaving(DSRequest, String...)`
      - `getHavingWithout(DSRequest, String...)`

  - ### getPartialHaving

    public static String getPartialHaving(DSRequest dsRequest, String... fieldNames) throws Exception
    Generates partial SQL condition expression for the `fieldNames` requested for use in the `groupWhereClause` (aka having).

    This is the same as `getPartialWhere(DSRequest, String...)` except that depending on `operationBinding.useHavingClause` setting it refers fields by either aliases or full SQL expressions, see `OperationBinding.useHavingClause` in the SmartClient Reference for details.

    Note that only fields that are selected in the main query may be used.

    Parameters:
      `dsRequest` - `DSRequest` to pick criteria from
      `fieldNames` - requested field names
    Returns:
      SQL expression filtered by requested fields
    Throws:
      `Exception`
    See Also:
      - `getPartialWhere(DSRequest, String...)`
      - `getWhereWithout(DSRequest, String...)`
      - `getHavingWithout(DSRequest, String...)`

  - ### getHavingWithout

    public static String getHavingWithout(DSRequest dsRequest, String... fieldNames) throws Exception
    Generates SQL condition expression without the `fieldNames` requested to skip from `groupWhereClause` (aka having).

    This is the same as `getWhereWithout(DSRequest, String...)` except that depending on `operationBinding.useHavingClause` setting it refers fields by either aliases or full SQL expressions, see `OperationBinding.useHavingClause` in the SmartClient Reference for details.

    Note that only fields that are selected in the main query may be used.

    Parameters:
      `dsRequest` - `DSRequest` to pick criteria from
      `fieldNames` - field names to skip
    Returns:
      SQL expression filtered by requested fields
    Throws:
      `Exception`
    See Also:
      - `getWhereWithout(DSRequest, String...)`
      - `getPartialHaving(DSRequest, String...)`
      - `getPartialWhere(DSRequest, String...)`

  - ### getSQLPrefix

    protected String getSQLPrefix(DSRequest req) throws Exception
    Override this method if you want to programmatically provide a prefix to SQL generated for this DataSource. Scan the client-side docs for `sqlPrefix` for details. Note, the default implementation of this method returns any operationBinding-level or dataSource-level declared `sqlPrefix`
    Parameters:
      `req` - Context `DSRequest`
    Returns:
      Query prefix text
    Throws:
      `Exception`

  - ### getSQLSuffix

    protected String getSQLSuffix(DSRequest req) throws Exception
    Override this method if you want to programmatically provide a suffix to SQL generated for this DataSource. Scan the client-side docs for `sqlSuffix` for details. Note, the default implementation of this method returns any operationBinding-level or dataSource-level declared `sqlSuffix`
    Parameters:
      `req` - Context `DSRequest`
    Returns:
      Query suffix text
    Throws:
      `Exception`

  - ### getSQLClause

    public static String getSQLClause(SQLClauseType clauseType, DSRequest dsRequest) throws Exception
    Returns the text of the generated SQL we will use to implement a clause of the supplied type for the supplied DSRequest. The clause text will be exactly the same as the value of the corresponding `$defaultXxxxxClause` Velocity variable that you can use when developing your own `customSQL` queries. Clauses consist of just the variable part and exclude the SQL keywords. The one exception to this is the special clause type "All"; if you specify this clause type, we return a full-formed SQL query suitable for passing straight to the executeQuery() or executeUpdate() method of a JDBC driver or equivalent.

    `getSQLClause` is not limited to the current DSRequest being processed: you can create a new DSRequest server-side and call getSQLClause() to retrieve the SQL that such a request would generate, which might be used as a sub-select in a larger overall query. This can be particularly useful with `aggregated queries`.

    Note that we ordinarily generate SQL that is self-contained and context-free, so you can store the clauses that this API returns and use them to construct a query for later execution without any dependence on the DSRequest. However, we are not always able to do this; binary columns and CLOBs cannot be assigned inline, so we have to generate SQL containing '?' markers, which can only be executed as parameterized queries. This in turn means that you need to store the values of any such fields in order to provide them as parameters when you come to execute the query.

    Also note that the query clauses produced by SQLDataSource are database-specific and will only work if run against the same database product they were generated for.

    Parameters:
      `clauseType` - The type of clause to generate and return
      `dsRequest` - The DSRequest embodying the fetch or update operation
    Returns:
      The text of the generated SQL we will use to implement a clause of `clauseType` for the supplied `dsRequest`
    Throws:
      `Exception`

  - ### executeAsBatch

    public static List<Integer> executeAsBatch(List<DSRequest> dsRequests) throws Exception
    Convenience implementation of `executeAsBatch(DSRequest[])`, if you prefer to pass and return Lists instead of arrays.
    Parameters:
      `dsRequests` - List of DSRequests to execute as a batch
    Returns:
      List of update counts, each entry pertains to the corresponding (by ordinal position) DSRequest
    Throws:
      `Exception`

  - ### executeAsBatch

    public static int[] executeAsBatch(DSRequest[] dsRequests) throws Exception
    Executes an array of DSRequests in a single JDBC `Statement.executeBatch()` call. The implementation builds up the batch by calling `getSQLClause(SQLClauseType,DSRequest)` to obtain the query text to execute for each DSRequest, and then adding each query with calls to `Statement.addBatch(java.lang.String)`. This means that many of the sophistications of DSRequest processing are bypassed. Note the following:

    - No DMI, security checks, or other normal consequences of dsRequest.execute() will occur, because the DSRequests are not executed in the normal way
    - executeAsBatch() allocates and frees its own SQL connection, and does not participate in automatic transctions
    - However, the entire batch is committed or rolled back in a single transaction that is specific to the batch, assuming that the underlying database is capaable of transactions

    This method is intended to be used for edge cases where a large number of records must be updated, and the performance overhead of updating them singly makes normal DSRequest processing impractical. exeuteAsBatch() can give dramatic performance improvements in these circumstances, but at the cost of losing much of the functionality of a DSRequest.

    Parameters:
      `dsRequests` - Array of DSRequests to execute as a batch
    Returns:
      Array of update counts, each entry pertains to the corresponding (by ordinal position) DSRequest
    Throws:
      `Exception`