Skip Headers

# Reading Objects Using Query-By-Example

Previous

Next

Query-by-example enables you to specify query selection criteria in the form of a sample object instance that you populate with only the attributes you want to use for the query.

To define a query-by-example, provide a `ReadObjectQuery` or a `ReadAllQuery` with a sample persistent object instance and an optional query-by-example policy. The sample instance contains the data to query, and, optionally, a `QueryByExamplePolicy` (see "Defining a QueryByExamplePolicy") that specifies configuration settings, such as the operators to use and the attribute values to ignore. You can also combine a query-by-example with an expression (see "Combining Query-by-Example and Expressions").

For more information, see "Query-by-Example".

Using Query-by-Example to Query an Employee

Example: Using Query-by-Example to Query an Employee queries the employee Bob Smith.

    Employee employee = new Employee();
    employee.setFirstName("Bob");
    employee.setLastName("Smith");

    // Create a query and set Employee as its reference class
    ReadObjectQuery query = new ReadObjectQuery(Employee.class);
    query.setExampleObject(employee);

    Employee result = (Employee) session.executeQuery(query);

Using Query-by-Example to Query an Employee's Address

Example: Using Query-by-Example to Query an Employee's Address queries across the employee's address.

    Employee employee = new Employee();
    Address address = new Address();
    address.setCity("Ottawa");
    employee.setAddress(address);

    // Create a query and set Employee as its reference class
    ReadObjectQuery query = new ReadObjectQuery(Employee.class);
    query.setExampleObject(employee);

    Vector results = (Vector) session.executeQuery(query);

Defining a QueryByExamplePolicy

TopLink support for query-by-example includes a query-by-example policy. You can edit the policy to modify query-by-example default behavior. You can modify the policy to do the following:

- Use `LIKE` or other operations to compare attributes. By default, query-by-example allows only `EQUALS`.

- Modify the set of values query-by-example ignores (the `IGNORE` set). The default ignored values are zero (0), empty strings, and `FALSE`.

- Force query-by-example to consider attribute values, even if the value is in the `IGNORE` set.

- Use `isNull` or `notNull` for attribute values.

To specify a query-by-example policy, include an instance of `QueryByExamplePolicy` with the query.

Query-by-Example Policy Using like Operator

Example: Query-by-Example Policy Using like Operator uses `like` operator for strings and includes only objects whose salary is greater than zero.

    Employee employee = new Employee();
    employee.setFirstName("B%");
    employee.setLastName("S%");
    employee.setSalary(0);

    // Create a query and set Employee as its reference class
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    query.setExampleObject(employee);
    // Query by example policy section adds like and greaterThan
    QueryByExamplePolicy policy = new QueryByExamplePolicy();
    policy.addSpecialOperation(String.class, "like");
    policy.addSpecialOperation(Integer.class, "greaterThan");
    policy.alwaysIncludeAttribute(Employee.class, "salary");
    query.setQueryByExamplePolicy(policy);
    Vector results = (Vector) session.executeQuery(query);

Query-by-Example Policy Using Keywords

Example: Query-by-Example Policy Using Keywords uses keywords for strings and ignores the value -1.

    Employee employee = new Employee();
    employee.setFirstName("bob joe fred");
    employee.setLastName("smith mc mac");
    employee.setSalary(-1);

    // Create a query and set Employee as its reference class
    ReadAllQuery query = new ReadAllQuery(Employee.class);
    query.setExampleObject(employee);
    // Query by example policy section
    QueryByExamplePolicy policy = new QueryByExamplePolicy();
    policy.addSpecialOperation(String.class, "containsAnyKeyWords");
    policy.excludeValue(-1);
    query.setQueryByExamplePolicy(policy);
    Vector results = (Vector) session.executeQuery(query);

Combining Query-by-Example and Expressions

To create more complex query-by-example queries, combine query-by-example with TopLink expressions, as shown in Example: Combining Query-by-Example with Expressions.

Combining Query-by-Example with Expressions

    Employee employee = new Employee();
    employee.setFirstName("Bob");
    employee.setLastName("Smith");

    // Create a query and set Employee as its reference class
    ReadAllQuery query = new ReadAllQuery(Employee.class);

    query.setExampleObject(employee);

    // Specify expression
    ExpressionBuilder builder = query.getExpressionBuilder();
    query.setSelectionCriteria(builder.get("salary").between(100000,200000);
    Vector results = (Vector) session.executeQuery(query);