Scout/Concepts/Sql Lookup Service
| Scout |
| Wiki Home |
| Website |
| Download • Git |
| Community |
| Forums • Blog • Twitter • G+ |
| Bugzilla |
| Bugzilla |
A SQL lookup Service is a specific type of Lookup Service that works with a database.
- implements:
ILookupService - extends:
AbstractSqlLookupService
Contents
Description
A SQL lookup Service provide a way to implement a Lookup Service that is very efficient to configured, if the call is resolved with a database.
Instead of implementing the 4 methods (getDataByKey(LookupCall call), getDataByText(LookupCall call), getDataByAll(LookupCall call), getDataByRec(LookupCall call)), it is possible to defined the behavior of the lookup service with some configuration properties and events.
Properties
Defined with getConfiguredXxxxxx() methods.
SqlSelect
The property SqlSelect expects a SQL query which is used to load the records for the lookup call.
Here is an example of a such SQL query:
SELECT language_id, name, NULL, NULL, NULL, NULL, NULL, 1, NULL, 1 FROM LANGUAGE <key>WHERE language_id = :key</key> <text>WHERE UPPER(name) LIKE UPPER('%'||:text||'%')</text>
Looking at this implementation we see that for each record returned by our lookup service we provide both a (unique) key and a text, which is a general characteristic of lookup services. Further we see that parts of the SQL statement are enclosed in tags. This is because a lookup can be performed in several ways:
- Key-Lookup: Single-result lookup based on a unique key (e.g. when loading a form with a smartfield containing a value).
- Text-Lookup: Multi-result lookup based on a textual search-term (e.g. when entering text into a smartfield).
- All-Lookup: Unrestricted lookup that returns all available key-text pairs (e.g. when clicking the magnifier button on a smartfield).
Depending on the way the lookup is performed, only one SQL part in tags is used. If for example a Text-Lookup is performed, only the SQL code in the corresponding <text> tag is used, whereas the SQL code in the other tags is ignored.
As you might have noticed, the SQL statement contains two binding variables :key and :text. These bindings are available because the LookupCall itself is bound to the SQL statement. Therefore every public property of the LookupCall can be used as binding variable in the query. (:key for call.getKey(), :text for call.getText(), :master for call.getMaster() ...)
The above example showed a SQL statement with a complete variable where part. In a more complex query you probably have an additional fix where part which you do not want to add in the key, text and all section. If that's the case you should move the WHERE key word out of the tags like in the following example.
public class CompanyLookupService extends AbstractSqlLookupService implements ICompanyLookupService { @Override public String getConfiguredSqlSelect() { return "SELECT C.COMPANY_NR, " + " C.NAME " + "FROM COMPANY C " + "WHERE C.ASSET > 10000 " + "<key> AND C.COMPANY_NR = :key </key> " + "<text> AND UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " + "<all> </all> "; } }
In the above section we have shown you how to return key-text pairs in your lookup service implementation. There is nothing wrong about that, but it is also not the whole truth. The AbstractSqlLookupService actually allows you to return additional meta-data that controls how the returned data is presented to the users. You can for example return icon IDs, color codes, tooltip texts or font descriptions. The complete list and order of all columns supported by the AbstractSqlLookupService is as follows:
- Object key
- String text
- String iconId
- String tooltip
- String background color
- String foreground color
- String font
- Boolean enabled
- Object parentKey used in hierarchical structures to point to the parents primary key
- Boolean active (0,1)
SortColumn
TODO: SortColumn
Events
Defined with execXxxxxx() methods.
LoadLookupRows
TODO: LoadLookupRows