OracleAS TopLink - HowTo read objects using Expression Builder

Date: 09/04/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. TopLink provides a querying mechanism called an expression that allows queries based on the object model. TopLink translates these queries into SQL and converts the results of the queries into objects. In this document we will try to understand simple read and write operations using Toplink expressions.

Prerequisites 

• You must have basic knowledge of Toplink.

Software Requirements 

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

HowTo read objects using Expression Builder 

Expression support is provided by two public classes. The Expression class represents an expression, which can be anything from a single constant to a complex clause with boolean logic. Expressions can be manipulated, grouped together and integrated in very flexible ways. The ExpressionBuilder serves as the factory for constructing new expressions.

Expression objects should always be created by calling get() or its related methods on an Expression or ExpressionBuilder. The ExpressionBuilder acts as a stand-in for the objects being queried. A query is constructed by sending it messages that correspond to the attributes of the objects. ExpressionBuilder objects are typically named according to the type of objects that they are used to query against.

We will now see how to create expressions based on boolean logic and various database functions.

Boolean Logic

Expressions use standard boolean operators such as AND, OR and NOT. Multiple expressions can be combined to form more complex expressions. For example, the following code snippet queries for all employees with job id 'ST_MAN' and who earn more than $1000.

   ExpressionBuilder builder = new ExpressionBuilder();
   Expression expression = builder.get("jobId").equal("ST_MAN").and((builder.get("salary").greaterThan(1000));
   Employees emp = (Employees) uow.readObject(Employees.class, expression);
   Vector emps = uow.readAllObjects(Employees.class, expression);

Database Functions

TopLink supports a wide variety of database functions and operators, including like(), in(), notLike(), toUpperCase(), toLowerCase(), toDate(),
rightPad() and so on. Database functions allow you to define more flexible queries. For example, the following code snippet reads all employees with job ids 'HR_REP' or 'ST_MAN'

   String[] jobs = {"HR_REP","ST_MAN"};      
   ExpressionBuilder builder = new ExpressionBuilder();
   Expression expression  = builder.get("jobId").in(jobs);
   Vector emps = new Vector();
   emps = uow.readAllObjects(Employees.class, expression);

The following code snippet queries for all employees with salaries between $20,000 and $30,000.

   ExpressionBuilder builder = new ExpressionBuilder(); 
   Expression expression  = builder.get("salary").between(20000, 30000);
   Vector emps = new Vector();
   emps = uow.readAllObjects(Employees.class, expression);

The following code snippet queries for all employees whose first name contains the substring 'be'.

   ExpressionBuilder builder = new ExpressionBuilder(); 
   Expression expression = builder.get("firstName").likeIgnoreCase("%be%");
   Vector emps = new Vector();
   emps = uow.readAllObjects(Employees.class, expression);

The following code snippet queries for all employees whose first name is 'ELIZABETH'. This expression would match all employees with first name 'ELIZABETH', 'Elizabeth' and 'elizabeth'.

   ExpressionBuilder builder = new ExpressionBuilder(); 
   Expression expression = builder.get("firstName").getFunction(ExpressionOperator.ToUpperCase).equal("ELIZABETH");
   Vector emps = new Vector();
   emps = uow.readAllObjects(Employees.class, expression);

Steps to run the sample 

• Download the jar from here
  
• Extract the jar file
• Open project.xml, set your database connection parameters in <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