OracleAS TopLink - HowTo Read objects using Query By Example

Date: 19/Apr/2004

Table of Contents

Introduction 

Oracle Application Server TopLink is an advanced object-to-relational persistence. It helps to build high performance applications that store persistent data in a relational database. Query by example allows for queries to be specified by providing sample instances of the persistent objects to be queried. Query by example is an intuitive form of expressing a query, but limited in the complexity of queries that can be defined.

Prerequisites 

• You must have basic knowledge of Toplink.

Software Requirements 

• Oracle9i Database installed with HR Schema
• OracleAS TopLink 10g

HowTo read objects using Query By Example

To define a query by example, a Read Object or a Read All Query is provided with a sample persistent object instance and an optional Query By Example Policy. The sample instance contains the data to be queried on and the query by example policy contains optional configuration settings.

To create a sample instance (example object), any valid constructor can be used. Only the attributes on which the query is to be based should be set. All other attributes must be not set, or set to null. A default set of values other than null is ignored; these include zero, empty string, and false. Any attribute that uses a direct mapping can be queried on. One-to-one relationships can also be queried on, including nesting. However, other relationship mappings cannot be queried on. The query is composed using AND to tie the attribute comparisons together. The following code snippet queries for all employees whose job id is ST_MAN.

   
   UnitOfWork uow = aSession.acquireUnitOfWork();
   ReadAllQuery query = new ReadAllQuery(Employees.class);

   // Create an employee with job id ST_MAN to be used as an example for query
   Employees employee = new Employees();
   employee.setJobId("ST_MAN");
   query.setExampleObject(employee);

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

Providing a sample instance (example object) allows for a large set of queries to be defined, but is limited to using equals and only ignoring null and default primitive values. The query by example policy allows for a larger set of queries to be defined. The query by example policy provides a number of options including usage of like or other operations per class type of the attribute values compared.

To specify a query by example policy, an instance of QueryByExamplePolicy is provided to the query. The following code snippet queries for all employees whose first name starts with 'S', last name starts with 'M' and who earn more than $3000.

   UnitOfWork uow = aSession.acquireUnitOfWork();
   ReadAllQuery query = new ReadAllQuery(Employees.class);
   Employees employee = new Employees();
   employee.setFirstName("S%");
   employee.setLastName("M%"); 
   employee.setSalary(new Double(3000));
   query.setExampleObject(employee);

   // Define a query by example policy
   QueryByExamplePolicy policy = new QueryByExamplePolicy();

   // The policy uses 'like' operation for all String attributes
   policy.addSpecialOperation(String.class, "like");
      
   // The policy uses 'greaterThan' operation for Double attributes
   policy.addSpecialOperation(Double.class, "greaterThan");  
  
   query.setQueryByExamplePolicy(policy);
   Vector results = (Vector) uow.executeQuery(query);

Steps to run the sample 

• Download the jar file from here and extract it.
• Edit <SAMPLE_HOME>/src/META-INF/EmployeeDescriptor.xml, set database parameters in the tag connection-url.
• Add toplink.jar, xmlparserv2.jar, connector.jar and classes12.jar to the classpath. Also ensure that <SAMPLE_HOME>/src/META-INF directory is added to your classpath
• From <SAMPLE_HOME>/src/oracle/otnsamples/toplink directory, Compile the java sources as shown below
  javac -d . *.java
• From <SAMPLE_HOME>/src/oracle/otnsamples/toplink directory, execute the java class as shown below
  java oracle.otnsamples.toplink.EmployeeManagement

Useful References 

• OracleAS TopLink 10g
• OracleAS TopLink Discussion Forum