|
|
| (One intermediate revision by one other user not shown) |
| Line 1: |
Line 1: |
| − | {{ScoutPage|cat=Server}}
| + | The Scout documentation has been moved to https://eclipsescout.github.io/. |
| − | | + | |
| − | A '''SQL lookup Service''' is a specific type of {{ScoutLink|Concepts|Lookup_Service|Lookup Service}} that works with a database.
| + | |
| − | | + | |
| − | * implements: {{ScoutJavadoc|ILookupService|I}}
| + | |
| − | * extends: {{ScoutJavadoc|AbstractSqlLookupService|C}}
| + | |
| − | | + | |
| − | | + | |
| − | == Description ==
| + | |
| − | A '''SQL lookup Service''' provide a way to implement a {{ScoutLink|Concepts|Lookup_Service|Lookup Service}} that is very efficient to configured, if the {{ScoutLink|Concepts|LookupCall|call}} is resolved with a database.
| + | |
| − | | + | |
| − | Instead of implementing the 4 methods (<code>getDataByKey(LookupCall call)</code>, <code>getDataByText(LookupCall call)</code>, <code>getDataByAll(LookupCall call)</code>, <code>getDataByRec(LookupCall call)</code>), it is possible to defined the behavior of the lookup service with some configuration properties and events.
| + | |
| − | | + | |
| − | | + | |
| − | == Properties ==
| + | |
| − | ''Defined with {{ScoutLink|Concepts|GetConfigured Methods|getConfiguredXxxxxx()}} methods''.
| + | |
| − | | + | |
| − | {{ScoutProp|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:
| + | |
| − | <source lang="sql">
| + | |
| − | select language_id, name, null, null, null, null, null, 1, null, 1
| + | |
| − | from languages
| + | |
| − | <key>where language_id = :key</key>
| + | |
| − | <text>where upper(name) like upper('%'||:text||'%')</text>
| + | |
| − | </source>
| + | |
| − | | + | |
| − | 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 <code>LookupCall</code> itself is bound to the SQL statement. Therefore every public property of the <code>LookupCall</code> can be used as binding variable in the query. (<code>:key</code> for <code>call.getKey()</code>, <code>:text</code> for <code>call.getText()</code>, <code>:master</code> for <code>call.getMaster()</code> ...)
| + | |
| − | | + | |
| − | 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.
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | 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> ";
| + | |
| − | }
| + | |
| − | }
| + | |
| − | </source>
| + | |
| − | | + | |
| − | {{note|'''AbstractSqlLookupService'''|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)
| + | |
| − | }}
| + | |
| − | | + | |
| − | When the SQL Statement is executed, the Lookup-call is passed as bind-variable. You can access every properties of your call (see {{ScoutLink|Concepts|LookupCall#Members|lookupCall members}}):
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | public String getConfiguredSqlSelect() {
| + | |
| − | return "language_id, name, null, null, null, null, null, 1, null, 1 " +
| + | |
| − | " from languages" +
| + | |
| − | " <key>where language_id = :key</key>" +
| + | |
| − | " <text>where upper(name) like upper('%'||:text||'%')</text> " +
| + | |
| − | " and nvl(start_date, to_date('19000101', 'yyyymmdd')) < NVL(:validityTo, to_date('99990101', 'yyyymmdd')) " +
| + | |
| − | " and nvl(end_date, to_date('99990101', 'yyyymmdd')) < NVL(:validityFrom, to_date('19000101', 'yyyymmdd')) ";
| + | |
| − | }
| + | |
| − | </source>
| + | |
| − | | + | |
| − | | + | |
| − | {{ScoutProp|SortColumn}}
| + | |
| − | | + | |
| − | {{note|TODO|TODO: SortColumn}}
| + | |
| − | | + | |
| − | == Events ==
| + | |
| − | ''Defined with {{ScoutLink|Concepts|Exec_Methods|execXxxxxx()}} methods''.
| + | |
| − | | + | |
| − | {{ScoutEvent|LoadLookupRows}}
| + | |
| − | | + | |
| − | {{note|TODO|TODO: LoadLookupRows}}
| + | |
| − | | + | |
| − | == See Also ==
| + | |
| − | * {{ScoutLink|Tutorial/Minicrm|Lookup Calls and Lookup Services| Minicrm Tutorial: Lookup Calls and Lookup Services}}
| + | |
| − | * {{ScoutLink|Concepts|Lookup_Service|LookupService}}
| + | |
| − | * {{ScoutLink|Concepts|LookupCall|LookupCall}}
| + | |
| − | * {{ScoutLink|Concepts|LookupRow|LookupRow}}
| + | |
| − | * {{ScoutLink|Concepts|Server Plug-In|Server Plug-In}}
| + | |
