Query functionality in the Java Server Framework

This page contains a number of query examples starting with simple queries and moving on to more advanced queries.

A few guidelines:

Examples

  1. Basic usage of conditions etc
  2. Including a detail
  3. Filtering details using detail conditions
  4. Limit number of masters using EXISTS_IN on detail
  5. Advanced conditions
  6. Combining multiple advanced conditions
  7. Using functions COUNT, SUM, AVG, MIN and MAX
  8. Limit number of master views returned using EXISTS_IN on a referenced view

 

Example 1: Basic usage of conditions etc

Simple condition on an attribute, example of using include/exclude flags, order by clause and maxRows.

Include only a few of the available attributes. Set simple conditions (attribute set to value) on country and title.
Limit the result set to 10 rows (maxRows) and order by the attribute name.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.objId.include();
person.objVersion.include();
person.personId.include();
person.name.include();
person.creationDate.include();

person.country.setValue("US");
person.title.setNull();

FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.OBJID, A.OBJVERSION, A.PERSON_ID, A.NAME, A.CREATION_DATE
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE A.COUNTRY_DB = :1
AND A.TITLE IS NULL
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
    1: (TEXT) = US

Example 2: Including a detail

The same query as the first example, but also including a detail.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.objId.include();
person.objVersion.include();
person.personId.include();
person.name.include();
person.creationDate.include();
person.addresses.include();

person.country.setValue("US");
person.title.setNull();

FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.OBJID, A.OBJVERSION, A.PERSON_ID, A.NAME, A.CREATION_DATE
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE A.COUNTRY_DB = :1
AND A.TITLE IS NULL
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
   1: (TEXT) = US
SELECT A.OBJID, A.OBJVERSION, A.PERSON_ID, A.ADDRESS_ID, A.ADDRESS, ...
FROM ifsapp.PERSON_INFO_ADDRESS A
WHERE A.PERSON_ID = :1

FndStatement: Binding parameters:
    1: (TEXT) = ALBO

Every record in the record structure is fetched in a separate database call. In this case a master record with PERSON_ID = 'ALBO' was found in database. It is very important to understand the implications of the fact that every view is fetched in a separate call.

This is the reason why detail views should be excluded if not used. You can easily do a hundreds of database calls if you're not careful.

Example 3: Filtering details using detail conditions

Same as example 2, but with a condition on an attribute in a detail record as well. Note that since records are fetched separately from the database, we will still get the same master records. This type of query can only be used to filter out detail records, it has no effect on the master records returned. 

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.objId.include();
person.objVersion.include();
person.personId.include();
person.name.include();
person.creationDate.include();
person.addresses.include();

person.country.setValue("US");
person.title.setNull();

//Create a detail record to set conditions on
PersonInfoAddress address = new PersonInfoAddress();
address.setNonExistent();
address.excludeQueryResults();
address.address.include();
address.zipCode.include();
address.city.include();

//Set condition on the city attribute
address.city.setValue("Chicago");

//Add detail record to master
person.addresses.add(address);

FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.OBJID, A.OBJVERSION, A.PERSON_ID, A.NAME, A.CREATION_DATE
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE A.COUNTRY_DB = :1
AND A.TITLE IS NULL
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
    1: (TEXT) = US
SELECT A.ADDRESS, A.ZIP_CODE, A.CITY
FROM ifsapp.PERSON_INFO_ADDRESS A
WHERE A.PERSON_ID = :1
AND A.CITY = :2

FndStatement: Binding parameters:
    1: (TEXT) = ALBO
    2: (TEXT) = Chicago

Example 4: Limit number of masters using EXISTS_IN on detail

Condition on detail record is used to limit the number of master records returned.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.firstName.include();
person.lastName.include();

PersonInfoAddress address = new PersonInfoAddress();
address.city.setValue("Chicago");
person.addresses.add(address);

//Add an EXISTS_IN condition between master and detail
person.addCondition(
   person.addresses.createDetailCondition(address, FndQueryReferenceCategory.EXISTS_IN));

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);

PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.FIRST_NAME, A.LAST_NAME
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE EXISTS (SELECT 1 FROM ifsapp.PERSON_INFO_ADDRESS B
WHERE A.PERSON_ID = B.PERSON_ID
AND B.CITY = :1)

Max rows set to 10
FndStatement: Binding parameters:
    1: (TEXT) = Chicago

Example 5: Advanced conditions

Advanced conditions are conditions where a condition object is created rather than simply setting a value on an attribute. Advanced conditions can be combined in a more flexible way than simple conditions (where AND is the only operator between attribute conditions).
Advanced conditions can also be used together with simple conditions, but note that you end up with two conflicting conditions on the same attribute.

Different attribute types have different query functionality. This functionality is reflected in the createXXXCondition-methods available on the attribute (you can for instance not do a ATTRIBUTE LIKE 'H%' on a binary attribute).

This example keeps the conditions from the example 1 above, but adds an advanced condition on the name attribute.

When adding a condition to a record (using the addCondition method), the conditions will be combined using the AND operator. Naturally, this only occurs if there already are conditions added to the record. There is a second version of the addCondition method with two arguments where one can specify if the operator AND or OR should be used to combine the conditions.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.objId.include();
person.objVersion.include();
person.personId.include();
person.name.include();
person.creationDate.include();

person.country.setValue("US");
person.title.setNull();

//Create a condition on name
FndSimpleCondition cond = person.name.createLikeCondition("Al%");

//Add the condition to the record
person.addCondition(cond);

FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.OBJID, A.OBJVERSION, A.PERSON_ID, A.NAME, A.CREATION_DATE
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE A.NAME LIKE :1
AND A.COUNTRY_DB = :2
AND A.TITLE IS NULL
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
    1: (TEXT) = Al%
    2: (TEXT) = US

Example 6: Combining multiple advanced conditions

Combining advanced conditions using AND or OR operators.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.firstName.include();
person.lastName.include();
person.title.include();

//Create attribute-attribute condition
FndSimpleCondition c1 = person.firstName.createEqualCondition(person.lastName);

//Create condition on title attribute
FndSimpleCondition c2 = person.title.createIsNotNullCondition();

//Create an IN-condition, which requires a list of values.
//(Make sure the values are of the correct type!)
ArrayList partyTypes = new ArrayList();
partyTypes.add(PartyTypeEnumeration.PERSON);
partyTypes.add(PartyTypeEnumeration.CUSTOMER);
partyTypes.add(PartyTypeEnumeration.SUPPLIER);
FndSimpleCondition c3 = person.partyType.createInCondition(partyTypes);

//Combine conditions using AND and OR operators into one compound condition
FndCondition c = c1.and(c2.or(c3));

//Add the compound condition to the record
person.addCondition(c);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.FIRST_NAME, A.LAST_NAME, A.TITLE
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE (A.FIRST_NAME = A.LAST_NAME
AND (A.TITLE IS NOT NULL
OR A.PARTY_TYPE_DB IN (:1, :2, :3)))
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
    1: (STRING) = PERSON
    2: (STRING) = CUSTOMER
    3: (STRING) = SUPPLIER

Example 7: Using functions COUNT, SUM, AVG, MIN and MAX

When including an attribute using SUM, AVG, MIN or MAX, the retuned value will be set on the attribute just as for any other value fetched from the database (use getValue to get the result). COUNT, on the other hand, is a bit different; you need to use getCount to return the value. No value will be set on the attribute (getValue will return NULL).

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.country.include();
person.personId.include(FndQueryResultCategory.COUNT);
person.creationDate.include(FndQueryResultCategory.MIN);
person.partyType.setValue(PartyTypeEnumeration.PERSON);

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
FndQueryRecord qry = new FndQueryRecord(person);

PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT COUNT(A.PERSON_ID), MIN(A.CREATION_DATE), A.COUNTRY_DB
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE A.PARTY_TYPE_DB = :1
GROUP BY A.COUNTRY_DB

FndStatement: Binding parameters:
    1: (STRING) = PERSON

The following code retrieves values returned by the query.

for(int i = 0; i < arr.size(); i++) {
   person = arr.get(i);
   String country = person.country.getValue();
   int count = person.personId.getCount();
   java.util.Date date = person.creationDate.getValue();
   System.out.println(country+": "+count+", "+date);
}

Example 8: Detail condition on a referenced view

Create a detail condition on a referenced view (reference by value) to return only persons from countries having the attribute salesTax = true. The relation is modeled as shown in the image below.

PersonInfo person = new PersonInfo();
person.excludeQueryResults();
person.personId.include();
person.name.include();
person.country.include();

//Add a condition on the salesTax attribute to the ISOCountry view
ISOCountry country = new ISOCountry();
country.addCondition(country.salesTax.createEqualCondition(true));

//Create and add a detail condition to the referent view
person.addCondition(
   person.iSOCountry.createDetailCondition(country, FndQueryReferenceCategory.EXISTS_IN));

PersonInfoHandler handler = PersonInfoHandlerFactory.getHandler();
FndQueryRecord qry = new FndQueryRecord(person);
qry.maxRows.setValue(10);
qry.setOrderBy(person.name);

PersonInfoArray arr = (PersonInfoArray) handler.query(qry);

Server debug output:

SELECT A.PERSON_ID, A.NAME, A.COUNTRY_DB
FROM ifsapp.PERSON_INFO_PUBLIC A
WHERE EXISTS (SELECT 1 FROM ifsapp.ISO_COUNTRY_DEF B
WHERE A.COUNTRY_DB = B.COUNTRY_CODE
AND B.SALES_TAX = :1)
ORDER BY A.NAME

Max rows set to 10
FndStatement: Binding parameters:
    1: (STRING) = Y