Query

Search Steps

The basic steps for a search are as follows:

1. Create a class that inherits BasicSipServlet (or its subclass) and BasicSipAction.
2. Get necessary management components for your result. To do this, use getSubscriberAdmin, getSubscriberManager, getLocationManager, and getPresenceAccessManager, which inherit the BasicSipServlet method.
3. Generate a Query search condition object using an EquipsLookupTemplate.createUsersQuery method.
4. Set search conditions in Query.
5. Set a filter in Query.
6. Perform the search and get a result.

Here is an example code:

List 2.6.1 Sample Code

import java.util.*;
import com.oki.sip.equips.query.*;
import com.oki.sip.equips.signal.BasicSipServlet;
import com.oki.sip.equips.users.*;

public SampleServlet extends BasicSipServlet {

    /**
     * Search all subscribers that match partially with the specified subscriber name alias and
     * get a list that contains OkiSubscriberAdminInfo.
     */
    public List getOkiAdminInfos(String username, String alias) throws Exception {

        // 1. Create Query.
        Query q = EquipsLookupTemplate.createUsersQuery();

        // 2. AND expression.
        LogicalExpression andE = q.createAnd();

        // 3. Partial matching for username.
        ConditionalExpression likeE1 =
          q.createLike(OkiLookupUsers.USERNAME_KEY, "*" + username + "*");

        // 4. Partial matching for alias.
        ConditionalExpression likeE2 =
          q.createLike(OkiLookupUsers.ALIAS_KEY, "*" + alias + "*");

        // 5. AND username and alias conditions and set it in Query.
        andE.add(likeE1);
        andE.add(likeE2);
        q.setExpression(andE);

        // 6. Set descending sort by user name.
        SortKey skey = new SortKey(OkiLookupUsers.USERNAME_KEY, false);
        q.addSortKey(skey);

        // 7. Get QueryResult.
        QueryResult result = getUsersQueryResult(q);

        // 8. Return a result.
        List list = new Vector();
        while(result.hasNext()){
            OkiSubscriberAdminInfo info = (OkiSubscriberAdminInfo) result.next();
            list.add(info);
        }
        return list;
    }
}

1. Create Query. You can use EquipsLookupTemplate.createUsersQuery() for searching a user, or EquipsLookupTemplate.createLocationsQuery() for searching a location to create Query.
2. Create an AND condition expression.
3. Create a search condition used for partial matching for a user name.
4. Create a search condition used for partial matching for an alias
5. Put the conditions created in Step 3 and 4 in the AND expression and set it in Query.
6. Create a sort key to sort by user and add it in Query.
7. Get QueryResult, a stateless session Bean for user search, by specifying the query in its argument.
8. Add the search result to List and return it.

Writing Search Conditions

This section describes how to create search conditions. The Basic steps to do this are as follows:

1. Generate a necessary number of condition expressions (Expression objects).
2. Set the expressions in the Query object.

Search conditions are expressed with operators. The Query class in the com.oki.sip.equips.query package defines create methods (such as createAnd and createEqual) to create search conditions using Java operators. Use these create methods to generate search conditions and set them in the Query object. If no condition is set, the query returns all data. Each operator has its own priority. When an expression with multiple operators is evaluated, the operator with a higher priority is evaluated first. If operators have the same priority, the preceding operator is evaluated first. A description and example of each operator is shown in Table 2.6.1, priority of each operator is shown in Table 2.6.2, and attribute keys and object types available for the search are shown in Table 2.6.3. Attribute keys used for a user search is defined in the OkiLookupUsers class in the com.oki.sip.equips.query package. Attribute keys used for a location search is defined in the OkiLookupUsers class in the com.oki.sip.equips.query package.

Table 2.6.1 Description and Example of Each Operator

Equal Operator

Function

Evaluates whether an attribute value corresponding to a key is equal (=) to the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For an evaluation of a NULL value, refer to the section "<a href="#null">NULL Handling</a>."

Coding Example

Users whose user name (Username) matches "Oki".

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createEqual(OkiLookupUsers.USERNAME_KEY, "Oki"));

NotEqual Operator

Function

Evaluates whether an attribute value corresponding to a key is not equal to (!=) the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For an evaluation of a NULL value, refer to the section "<a href="#null">NULL Handling</a>."

Coding Example

Users whose user name (Username) does not match "Oki".

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createNotEqual(OkiLookupUsers.USERNAME_KEY, "Oki"));

GreaterThan Operator

Function

Evaluates whether an attribute value corresponding to a key is greater than (>) the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For String and Address types, evaluation is made based on the character code. Null can not be specified.

Coding Example

Users whose authentication failed (AuthFailure) more than three times.

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createGreaterThan(OkiLookupUsers.AUTHFAILURE_KEY, new Integer(3)));

LessThan Operator

Function

Evaluates whether an attribute value corresponding to a key is less than (<) the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For String and Address types, evaluation is made based on the character code. Null can not be specified.

Coding Example

Users whose authentication failed (AuthFailure) less than three times.

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createLessThan(OkiLookupUsers.AUTHFAILURE_KEY, new Integer(3)));

GreaterEqual Operator

Function

Evaluates whether an attribute value corresponding to a key is greater than or equal to (>=) the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For String and Address types, evaluation is made based on the character code. Null can not be specified.

Coding Example

Users whose authentication failed (AuthFailure) three times or more.

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createGreaterEqual(OkiLookupUsers.AUTHFAILURE_KEY, new Integer(3)));

LessEqual Operator

Function

Evaluates whether an attribute value corresponding to a key is less than or equal to (<=) the specified value.

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For String and Address types, evaluation is made based on the character code. Null can not be specified.

Coding Example

Users whose authentication failed (AuthFailure) three times or less.

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createLessEqual(OkiLookupUsers.AUTHFAILURE_KEY, new Integer(3)));

Between Operator

Function

Evaluates whether an attribute value corresponding to a key is greater than or equal to the specified value x, and less than or equal to the value y (x <= key <= y).

Note

It can be specified for String, Integer, Long, Boolean, and Address types of attributes. For String and Address types, evaluation is made based on the character code. Null can not be specified.

Coding Example

Users whose authentication failed (AuthFailure) between once and three times.

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createBetween(OkiLookupUsers.AUTHFAILURE_KEY, new Integer(1), new Integer(3)));

Like Operator

Function

Performs pattern matching for a string. Evaluates whether an attribute value corresponding to a key matches the specified pattern. A question mark and an asterisk are treated as a wildcard and matched as follows. If there is an asterisk or a question mark in the string you want the user to match, prepend a backslash ('\') character to the question mark or the asterisk. If there is a backslash in the string, append another backslash to the backslash ('\\').

• A question mark ('?'): a single character. (e.g. "so?n" -> "soon")
• An asterisk ('*'): zero or more number of characters. (e.g. "so*n" -> "son", "soon", and "solution")

Note

It can be specified for a String attribute value.

Coding Example

Users whose user name (Username) contains "user".

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createLike(OkiLookupUsers.USERNAME_KEY, "*user*"));

NotLike Operator

Function

Performs pattern matching for a string. Evaluates whether an attribute value corresponding to a key does not match the specified pattern. For a pattern string, see the previous section "Like Operator."

Note

It can be specified for a String attribute value.

Coding Example

Users whose user name (Username) does not contain "user".

Query q = EquipsLookupTemplate.createUsersQuery();
q.setExpression(q.createNotLike(OkiLookupUsers.USERNAME_KEY, "*user*"));

And Operator

Function

ANDs all specified search conditions. Evaluates to true only if all contained conditions evaluate to true. Evaluates to false if at least one contained condition evaluates to false.

Note

It must contain at least one search condition.

Coding Example

Users whose alias (Alias) begins with "Oki", and host name (Host) is "oki.com".

Query q = EquipsLookupTemplate.createUsersQuery();
LogicalExpression andE = q.createAnd();
andE.add(q.createLike(OkiLookupUsers.ALIAS_KEY, "Oki*"));
andE.add(q.createEqual(OkiLookupUsers.HOST_KEY, "oki.com"));
q.setExpression(andE);

Or Operator

Function

ORs all specified search conditions. Evaluates to false only if all contained conditions evaluate to false. Evaluates to true if at least one contained condition evaluates to true.

Note

It must contain at least one search condition.

Coding Example

Users whose alias (Alias) begins with "Oki", or host name (Host) is "oki.com".

Query q = EquipsLookupTemplate.createUsersQuery();
LogicalExpression orE = q.createOr();
orE.add(q.createLike(OkiLookupUsers.ALIAS_KEY, "Oki*"));
orE.add(q.createEqual(OkiLookupUsers.HOST_KEY, "oki.com"));
q.setExpression(orE);

In expressions containing AND or OR, conditions can be nested as shown below:
Users whose alias (Alias) begins with "Oki" and host name (Host) is "oki.com", or whose alias (Alias) contains "user" and the subscriber portion (Subscriber) of the SIP URI does not begin with "user".

Query q = EquipsLookupTemplate.createUsersQuery();
LogicalExpression andE1 = q.createAnd();
LogicalExpression andE2 = q.createAnd();
LogicalExpression orE = q.createOr();
andE1.add(q.createLike(OkiLookupUsers.ALIAS_KEY, "Oki*"));
andE1.add(q.createEqual(OkiLookupUsers.HOST_KEY, "oki.com"));
andE2.add(q.createLike(OkiLookupUsers.ALIAS_KEY, "*user*"));
andE2.add(q.createNotLike(OkiLookupUsers.SUBSCRIBER_KEY, "user*"));
orE.add(andE1);
orE.add(andE2);
q.setExpression(orE);

Table 2.6.2 Priority of Operator

You can use attribute keys that are defined in OkiLookupUsers (for a user search) and OkiLookupLocations (for a location search).

Table 2.6.3 Attribute Keys and Object Types Available for a Search

Setting a Filter

Setting a filter in the Query object enables you to get a result in a different form. Currently, a sorting filter is provided to sort the search result.

The sorting filter sorts the result based on attribute values corresponding to each key. You must register these attributes with Query as a sort key. Any number of sort keys can be set in order of decreasing priority. If multiple sort keys are specified, when there are equivalent values corresponding to a key with a higher priority, a sort key with a lower priority is used for them. You can also set an ascending or descending order for each sort key. Specify true in the second constructor of SortKey to set an ascending order, specify false to set a descending order.

Here is an example description of the sort key.

Sort by use name (Username). If equivalent values are found, sort them by alias (Alias).

Query q = EquipsLookupTemplate.createUsersQuery();
q.addSortKey(new SortKey(OkiLookupUsers.USERNAME_KEY, true));
q.addSortKey(new SortKey(OkiLookupUsers.ALIAS_KEY, true));

Sort by authentication failure times (AuthFailure). If equivalent values are found, sort them by user name (Username) in ascending order, alias (Alias) in ascending order, and host name (Host) in ascending order.

Query q = EquipsLookupTemplate.createUsersQuery();
q.addSortKey(new SortKey(OkiLookupUsers.AUTHFAILURE_KEY, false));
q.addSortKey(new SortKey(OkiLookupUsers.USERNAME_KEY, true));
q.addSortKey(new SortKey(OkiLookupUsers.ALIAS_KEY, true));
q.addSortKey(new SortKey(OkiLookupUsers.HOST_KEY, true));

Notes

The Search process is implemented using the search engine of your database. It may behave differently than when you write conditions in general Java language.

NULL Handling

If you specify a search condition "an attribute value of the specified key is not A" (A is any value other than null) such as NotEqual and NotLike, null is not included in the result. For example, if subscriber information X, Y, and Z are stored and their ALIAS_KEY values are as follows:

• X : "OKI"
• Y : "SIP"
• Z : null (not set)

If a search condition "ALIAS_KEY value is not OKI" is specified, Y is returned but Z is not. If you want the search to return both Y and Z, you must explicitly specify "ALIAS_KEY value is either OKI or null".

Tips

You can use the following ways to increase search speed.

• In an AND search, write a search condition first that can produce a narrower result.
• In a Like or NotLike search, do not use a wildcard character ('?', '*') before the pattern string.

Last Modified:Thu Mar 25 04:57:50 PM JST 2004