public interface FlexibleSearchService
An overview on the FlexibleSearch syntax:
<query> = "SELECT" <selects> "FROM" <types> ( "WHERE" <conditions> ( "ORDER BY" <order> )? )?
<selects> = SQL <i>select</i> expression that contains attribute <field> sections
<types> = SQL <i>from</i> expression that contains item <type> and <typejoin> sections
<type> = "{" <single-type-clause> "}"
<typejoin> = "{" <single-type-clause> ( ( "LEFT" )? "JOIN" <single-type-clause> ( "ON" <join-conditions> )? )+ "}"
<single-type-clause> = code of type "!"? ( "AS" <type-alias> )? <type-modifiers>?
<type-alias> = a unique name of this type inside this query
<join-conditions> = SQL boolean condition including <field> sections ( "AND" SQL boolean condition including <field> sections )*
<conditions> = SQL <i>where</i> expression containing attribute <field> sections and <subselect> sections
<order> = SQL <i>order by </i> expression containing attribute <field> sections
<field> = "{" ( <type-alias> ":" )? <attribute-name> <language>( ":" <modifiers> )? "}"
<attribute-name> = the attribute name
<language> = "[" language PK string + "]"
<modifiers> = ( "c" | "l")? "o"? ... core field , localized property, outer joined property
<subselect> = "{{" <query> "}}"
A simple select to find all items of one type can be done like this:
final String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "}";
final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query);
Please note that items are always referenced as "{" + ItemModel.PK + "}" . The actual item type is
defined by the search result signature list.
It is highly recommended to use parametrized queries when using WHERE clauses as follows:
final String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} WHERE {" + UnitModel.CODE + "} = ?code ";
final Map<String, String> queryParams = new HashMap<String, String>();
queryParams.put("code", "foo");
final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query, queryParams);
If you know that a search query is likely to yield a lot of results (such as a search for all products and their
variants, for example), we discourage getting all of those search results in a single FlexibleSearch call: the
results will be stored in a single Collection, which may result in slow performance. A better way is to split the
query into several calls by limiting the number of search results per call. To use this paging mechanism, use the
search(...) method with FlexibleSearchQuery object as parameter. You have to set on FlexibleSearchQuery object the
setNeedTotal to true. If this parameter is set to true , the FlexibleSearch framework splits the number
of returned search results into pages. Via the start and range parameters, you can retrieve
pages of search results. The following code snippet, for example, iterates over all search results of the
FlexibleSearch query three at a time:
String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY " + UnitModel._TYPECODE;
final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
fQuery.setCount(range);
fQuery.setNeedTotal(true);
int start = 0;
final int range = 3;
int total;
do
{
fQuery.setStart(start);
final SearchResult<UnitModel> searchResult = flexibleSearchService.search(fQuery);
total = searchResult.getTotalCount();
start += range;
}
while (start < total);
FlexibleSearch is able of retrieving raw data as well as items. Furthermore, it is possible to select multiple attributes or columns. The result of multi-attribute selections is a list of lists.
String query = "SELECT {" + UnitModel.PK + "}, LOWER({" + UnitModel.CODE + "}), {" + UnitModel.NAME + "} FROM {"
+ UnitModel._TYPECODE + "}";
final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
fQuery.setResultClassList(Arrays.asList(UnitModel.class, String.class, String.class));
final SearchResult<List<?>> result = flexibleSearchService.search(fQuery);
final List<List<?>> resultList = result.getResult();
for (final List<?> list : result)
{
final UnitModel unit = (UnitModel) list.get(0);
final String code = (String) list.get(1);
final String name = (String) list.get(2);
// ....
}
As seen here it is possible to use any column based SQL functions around flexible search sections (
"LOWER({" + UnitModel.CODE + "}" ) since only these sections are replaced. The rest of the query is
left untouched. This way even database specific functionality is available (though this might break database
independence).
Ordering is as simple as selecting different attributes
String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY {" + UnitModel.CODE + "} ASC";
final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query);
A special note on properties: when order by contains properties which are stored in separate tables these tables are
outer joined automatically since non existence of the property would avoid the actual item to be found
otherwise.
Localized properties require that a locale is specified for searching. Normally search uses the current
session locale. FlexibleSearchService automatically will execute query in local session context with passed
locale.
String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY " + UnitModel._TYPECODE;
final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
fQuery(new Locale("en");
final SearchResult<UnitModel> searchResult = flexibleSearchService.search(fQuery);
Further it is possible to specify a fixed language for each localized attribute - even multiple different languages
per query. please note that this generates as many joins to localized property tables as languages are specified,
so be careful.
final LanguageModel en = commonI18NService.getLanguage("en");
final LanguageModel de = commonI18NService.getLanguage("de");
final String query = "SELECT {" + LanguageModel.PK + "} FROM {" + LanguageModel._TYPECODE + "} WHERE {name["
+ en.getPk().getLongValueAsString() + "]} = ?en AND {name[" + de.getPk().getLongValueAsString() + "]} = ?de";
final Map params = new HashMap<String, LanguageModel>();
params.put("en", "English");
params.put("de", "Englisch");
final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query, params);
final SearchResult<LanguageModel> search = flexibleSearchService.search(fQuery);
The fixed languages are passed as language pk strings.
Joins between types are allowed. Currently inner joins and left outer joins are allowed.
final String query = "SELECT {products:" + ProductModel.PK + "}, {carts:" + CartEntryModel.UNIT + "}, SUM({carts:"
+ CartEntryModel.QUANTITY + "}) AS amount " + "FROM {" + ProductModel._TYPECODE + " AS products JOIN "
+ CartEntryModel._TYPECODE + " AS carts ON {products:" + ProductModel.PK + "} = {carts:" + CartEntryModel.PRODUCT
+ "} } GROUP BY {products:" + ProductModel.PK + "}, {carts:" + CartEntryModel.UNIT + "}";
FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
fQuery.setResultClassList(Arrays.asList(ProductModel.class, UnitModel.class, Long.class))
final SearchResult> res = flexibleSearchService.search(fQuery);
final List> result = res.getResult();
for (final List> row : result)
{
final ProductModel product = (ProductModel) row.get(0);
final UnitModel unit = (UnitModel) row.get(1);
final Long quantity = (Long) row.get(2);
}
The above example produces a list of all products which are currently held in carts grouped by unit and show the
total quantity. Since this is no left (outer) join all products which are not inside a cart don't show up in the
result. There is a special syntax for subselects too. Please note that subselects are not available in case the underlying database doesn't support them!
String query = "SELECT {" + ProductModel.PK + "} FROM {" + ProductModel._TYPECODE + " AS p} WHERE " + " EXISTS ( {{" + "SELECT {"
+ OrderEntryModel.PK + "} FROM {" + OrderEntryModel._TYPECODE + "} WHERE {" + OrderEntryModel.PRODUCT + "} = {p:"
+ ProductModel.PK + "} " +
// be sure to have a space between the last '}' and '}}' - this is a know issue right now
"}} )";
Subselects may contain all features as normal queries including joins and nested subselects.
The result of a flexible query is always limited to the queried type, which means result items are instances of the
queried type or any of its subtypes. It is possible to select instances of one type exactly by appending a
! to the type code.
String query = "SELECT {PK} FROM {MyType!}";
FlexibleSearchQuery| Modifier and Type | Method and Description |
|---|---|
<T> T |
getModelByExample(T example)
Search for an existing object (in the database) by a given (not saved) example.
|
<T> List<T> |
getModelsByExample(T example)
Search for an existing objects (in the database) by a given (not saved) example.
|
<T> SearchResult<T> |
search(FlexibleSearchQuery searchQuery)
Search.
|
<T> SearchResult<T> |
search(String query)
Simplest search available.
|
<T> SearchResult<T> |
search(String query,
Map<String,? extends Object> queryParams)
Convenience method which internally delegates to
search(FlexibleSearchQuery). |
<T> SearchResult<T> |
searchRelation(ItemModel model,
String attribute,
int start,
int count)
This method is not implemented.
|
<T> SearchResult<T> |
searchRelation(RelationQuery query)
This method is not implemented.
|
<T> T |
searchUnique(FlexibleSearchQuery searchQuery)
Searches for exactly one model fitting given
FlexibleSearchQuery. |
TranslationResult |
translate(FlexibleSearchQuery searchQuery)
Translates
FlexibleSearchQuery into TranslationResult object which contains translated
FlexibleSearchQuery to real SQL query and all parameters which will be used in SQL query. |
<T> T getModelByExample(T example)
T - the type of the example and the return typeexample - the exampleModelNotFoundException - if nothing was foundAmbiguousIdentifierException - if more than one model is found for the given search parameters<T> List<T> getModelsByExample(T example)
T - the type of the example and the return typeexample - the example<T> SearchResult<T> search(FlexibleSearchQuery searchQuery)
searchQuery - the search query<T> SearchResult<T> search(String query)
query - the query<T> SearchResult<T> search(String query, Map<String,? extends Object> queryParams)
search(FlexibleSearchQuery).query - query string in flexible search syntaxqueryParams - additional query parameters; null permitted<T> SearchResult<T> searchRelation(ItemModel model, String attribute, int start, int count)
<T> SearchResult<T> searchRelation(RelationQuery query)
<T> T searchUnique(FlexibleSearchQuery searchQuery)
FlexibleSearchQuery. Throws an exception when more or none
results found.searchQuery - the search queryModelNotFoundException - if nothing was foundAmbiguousIdentifierException - if more than one model is found for the given search parametersTranslationResult translate(FlexibleSearchQuery searchQuery)
Translates FlexibleSearchQuery into TranslationResult object which contains translated
FlexibleSearchQuery to real SQL query and all parameters which will be used in SQL query.
Translation also respects SearchRestrictionModel based restrictions for types and users.
searchQuery - FlexibleSearchQuery to be translated.TranslationResult object containing SQL query with parameters.Copyright © 2017 SAP SE. All Rights Reserved.