Package net.pricefx.domain
Class DMSqlQuery
- Object
-
- DomainObject
-
- DMSqlQuery
-
public class DMSqlQuery extends DomainObject
Query object for building SQL statements from source queries, with clauses and parameter bindings. Query must be ANSI compliant SQL SELECT statement in the PriceAnalyzer DB. New instance can be created usingDatamartContext.newSqlQuery()
, follow the link for an example of join query.
-
-
Nested Class Summary
Nested Classes Modifier and Type Class Description static class
DMSqlQuery.ParameterizedQuery
-
Field Summary
Fields Modifier and Type Field Description protected Boolean
isValid
protected static Marker
LOG_MARKER
-
Constructor Summary
Constructors Constructor Description DMSqlQuery()
-
Method Summary
All Methods Instance Methods Concrete Methods Modifier and Type Method Description DMSqlQuery
addSource(DMQuery sourceQuery)
Adds aDMQuery
as a source for this SQL query.DMSqlQuery
addSource(DMQuery sourceQuery, String alias)
Adds aDMSqlQuery
as a source for this SQL query.DMSqlQuery
addSource(DatamartContext.Query sourceQuery)
Adds aDatamartContext.Query
as a source for this SQL query.DMSqlQuery
addSource(DatamartContext.Query sourceQuery, String alias)
Adds aDatamartContext.Query
as a source for this SQL query.DMSqlQuery
addSource(Table table)
DMSqlQuery
addSource(Table table, String alias)
DMSqlQuery
addWith(String withClause, Object... bindings)
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters.DMSqlQuery
addWith(String withClause, String alias, Object... bindings)
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters.DMSqlQuery.ParameterizedQuery
getQuery()
Map<String,DMQuery>
getSourceDmQueries()
Map<String,DatamartContext.Query>
getSourceQueries()
boolean
getUseCache()
Collection<DMSqlQuery.ParameterizedQuery>
getWithClauses()
boolean
hasSources()
boolean
hasWiths()
boolean
isPrivileged()
boolean
isValid()
void
preprocessForExecution(PersistedObjectService pos)
performs validation of the sql query, and crucially, converts the sources to DMQueries, required for subsequent executionboolean
revalidate(ValidationVisitor validationVisitor, PersistedObjectService objService)
DMSqlQuery
setHash(String hash)
protected void
setIsValid(Boolean isValid)
DMSqlQuery
setQuery(String sql, Object... bindings)
Sets the main SQL SELECT clause of this query, where every "?" char gets replaced by the values provided as additional parameters.void
setUseCache(Boolean useCache)
If caching is disabled, the query will always be executed in the DBString
toString()
boolean
validate(ValidationVisitor validationVisitor, PersistedObjectService objService)
boolean
validate(PersistedObjectService objService)
-
Methods inherited from class DomainObject
deepCopy, deepCopyUsingJavaSerialization, getNewMidnightDate, getNewMidnightDateTime, isMidnight, isMidnight, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonDeserialize, jsonSerialize, jsonSerialize, jsonSerialize, jsonSerialize, jsonSerialize, jsonSerialize, jsonSerializedSizeOK, setToDayEnd, setToDayEnd, setToMidnight, setToMidnight, setToMidnightAsLocalDateTime, toPropertyMap
-
-
-
-
Field Detail
-
LOG_MARKER
protected static final Marker LOG_MARKER
-
isValid
protected transient Boolean isValid
-
-
Method Detail
-
getUseCache
public boolean getUseCache()
-
setUseCache
public void setUseCache(Boolean useCache)
If caching is disabled, the query will always be executed in the DB- Parameters:
useCache
-
-
addSource
public DMSqlQuery addSource(DatamartContext.Query sourceQuery)
Adds aDatamartContext.Query
as a source for this SQL query. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.- Parameters:
sourceQuery
- query to use as a source for this sql query- Returns:
- this query
-
addSource
public DMSqlQuery addSource(DatamartContext.Query sourceQuery, @NotNull String alias)
Adds aDatamartContext.Query
as a source for this SQL query.- Parameters:
sourceQuery
- query to use as a source for this sql queryalias
- the alias to assign to this source query- Returns:
- this query
-
addSource
public DMSqlQuery addSource(DMQuery sourceQuery)
Adds aDMQuery
as a source for this SQL query. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.- Parameters:
sourceQuery
- query to use as a source for this sql query- Returns:
- this query
-
addSource
public DMSqlQuery addSource(DMQuery sourceQuery, @NotNull String alias)
Adds aDMSqlQuery
as a source for this SQL query.- Parameters:
sourceQuery
- query to use as a source for this sql queryalias
- the alias to assign to this source query- Returns:
- this query
-
addSource
public DMSqlQuery addSource(Table table)
-
addSource
public DMSqlQuery addSource(Table table, @NotNull String alias)
-
addWith
public DMSqlQuery addWith(String withClause, Object... bindings)
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String. Convenience method which generates the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.Example:
def with = """SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) WHERE T1.PG = ?""" sqlQuery.addWith(with, "PG-ABC") // binding some product group value; with-clause gets assigned the T3 alias
- Parameters:
withClause
- sql SELECT code to use as WITH clause in this querybindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
addWith
public DMSqlQuery addWith(String withClause, @NotNull String alias, Object... bindings)
Adds a with clause to the SQL query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String. Convenience method which generate the alias for you, starting with "T1", and then incrementing for each additional source query and with clause.Example:
def with = """SELECT T1.product, T1.revenue AS ActualRevenue, T2.revenue AS ForecastRevenue T1.volume AS ActualVolume, T2.volume AS ForecastVolume FROM T1 LEFT OUTER JOIN T2 USING (product) WHERE T1.PG = ?""" sqlQuery.addWith(with, "W1", "PG-ABC") // binding some product group value
- Parameters:
withClause
- sql SELECT code to use as WITH clause in this queryalias
- the alias to assign to this with clausebindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
setQuery
public DMSqlQuery setQuery(String sql, Object... bindings)
Sets the main SQL SELECT clause of this query, where every "?" char gets replaced by the values provided as additional parameters. This avoids type conversions that would typically happen if the whole query was constructed as a single String.Example:
def sql = "SELECT SUM(ActualRevenue) - SUM(ForecastRevenue) FROM T3" sqlQuery.setQuery(sql)
- Parameters:
sql
- main SELECT clause of this querybindings
- parameters to insert for every "?" that appears in the with clause- Returns:
- this query
-
setHash
public DMSqlQuery setHash(String hash)
-
getSourceQueries
public Map<String,DatamartContext.Query> getSourceQueries()
-
getWithClauses
public Collection<DMSqlQuery.ParameterizedQuery> getWithClauses()
-
getQuery
public DMSqlQuery.ParameterizedQuery getQuery()
-
hasSources
public boolean hasSources()
-
hasWiths
public boolean hasWiths()
-
isPrivileged
public boolean isPrivileged()
-
isValid
public boolean isValid()
-
setIsValid
protected void setIsValid(Boolean isValid)
-
revalidate
public boolean revalidate(ValidationVisitor validationVisitor, PersistedObjectService objService)
-
validate
public boolean validate(PersistedObjectService objService)
-
validate
public boolean validate(ValidationVisitor validationVisitor, PersistedObjectService objService)
-
preprocessForExecution
public void preprocessForExecution(PersistedObjectService pos)
performs validation of the sql query, and crucially, converts the sources to DMQueries, required for subsequent execution
-
-