Latest revision as of 16:20, 23 June 2008

Queries are the cornerstone of EclipseLink applications. Queries enable you to retrieve information or objects from the database, modify or delete those objects, and create new objects on the database.

Expressions

EclipseLink expressions enable you to specify query search criteria based on the object model. EclipseLink translates the resulting query into SQL and converts the results of the query into objects. EclipseLink provides two public classes to support expression:

• The org.eclipse.persistence.expressions.Expression class represents an expression, which can be anything from a

simple constant to a complex clause with boolean logic. The developer can manipulate, group, and integrate expressions in several ways.

• The org.eclipse.persistence.expressions.ExpressionBuilder class is the factory for constructing new expressions.

A Simple Expression Builder Expression

This example uses the query key lastName to reference the field name L_NAME.

Expression expression = new ExpressionBuilder().get("lastName").equal("Young");

An Expression Using the and() Method

ExpressionBuilder emp = new ExpressionBuilder();
Expression exp1, exp2;
exp1 = emp.get("firstName").equal("Ken");
exp2 = emp.get("lastName").equal("Young");
return exp1.and(exp2);

Custom SQL

The expression framework enables you to define complex queries at the object level. If your application requires a more complex query, use SQL or stored procedure calls to create custom database operations.

SQL Queries

You can provide a SQL string to any query instead of an expression, but the SQL string must return all data required to build an instance of the queried class. The SQL string can be a complex SQL query or a stored procedure call. You can invoke SQL queries through the session read methods or through a read query instance.

A Session Read Object Call Query With Custom SQL

Employee employee = (Employee) session.readObject(Employee.class, new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44"));

A Session Method with Custom SQL

This example queries user and time information.

List rows = session.executeSelectingCall(new SQLCall("SELECT USER, SYSDATE FROM DUAL"));

SQL Data Queries

EclipseLink offers the following data-level queries to read or modify data (but not objects) in the database.

• org.eclipse.persistence.queries.DataReadQuery: for reading rows of data
• org.eclipse.persistence.queries.DirectReadQuery: for reading a single column
• org.eclipse.persistence.queries.ValueReadQuery: for reading a single value
• org.eclipse.persistence.queries.DataModifyQuery: for modifying data

DataReadQuery Example

DataModifyQuery query = new DataModifyQuery();
query.setSQLString("USE SALESDATABASE");
session.executeQuery(query);

DirectReadQuery Example

DirectReadQuery query = new DirectReadQuery();
query.setSQLString("SELECT EMP_ID FROM EMPLOYEE");
List ids = (List) session.executeQuery(query);

Stored Procedure Calls

You can provide a StoredProcedureCall object to any query instead of an expression or SQL string, but the procedure must return all data required to build an instance of the class you query.

A Read All Query With a Stored Procedure

ReadAllQuery readAllQuery = new ReadAllQuery(Employee.class);
call = new StoredProcedureCall();
call.setProcedureName("Read_All_Employees");
readAllQuery.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
readAllQuery.setCall(call);
List employees = (List) session.executeQuery(readAllQuery);

JPQL

JPQL (was EJBQL) is a query language that is similar to SQL, but differs because it presents queries from an object model perspective and includes path expressions that enable navigation over the relationships defined for entity beans and dependent objects. Although JPQL is usually associated with Enterprise JavaBeans (EJBs), EclipseLink enables you to use JPQL with regular Java objects as well. In EclipseLink, JPQL enables users to declare queries, using the attributes of each abstract entity bean in the object model. This offers the following advantages:

• You do not need to know the database structure (tables, fields).
• You can use relationships in a query to provide navigation from attribute to

attribute.

• You can construct queries using the attributes of the entity beans instead of

using database tables and fields.

• JPQL queries are portable because they are database-independent.
• You can use SELECT to specify the query reference class (the class or entity

bean you are querying against).

A Simple ReadAllQuery Using JPQL

ReadAllQuery query = new ReadAllQuery(Employee.class);
query.setJPQLString("SELECT OBJECT(emp) FROM Employee emp");
…
List employees = (List)session.executeQuery(query);

A Simple ReadAllQuery Using JPQL and Passing Arguments

This example defines the query similarly to above, but creates, fills, and passes a vector of arguments to the executeQuery method.

// First define the query
ReadAllQuery theQuery = new ReadAllQuery(Employee.class);
query.setJPQLString("SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName = ?1");
...
// Next define the Arguments
List arguments = new ArrayList();
arguments.add("Bob");
...
// Finally execute the query passing in the arguments
List employees = (List)session.executeQuery(query, arguments);

Query By Example

Query by example enables you to specify queries when you provide sample instances of the persistent objects to be queried. 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 the query by example policy contains optional configuration settings, such as the operators to use and the attributes to consider or ignore.

Using Query by Example

This example queries the employee Bob Smith.

ReadObjectQuery query = new ReadObjectQuery();
Employee employee = new Employee();
employee.setFirstName("Bob");
employee.setLastName("Smith");
query.setExampleObject(employee);
Employee result = (Employee) session.executeQuery(query);

Using Query by Example

This example queries across the employee’s address.

ReadAllQuery query = new ReadAllQuery();
Employee employee = new Employee();
Address address = new Address();
address.setCity("Ottawa");
employee.setAddress(address);
query.setExampleObject(employee);
List results = (List) session.executeQuery(query);

Query by Example Policy Using Like

This example uses like for Strings and includes only objects whose salary is greater than zero.

ReadAllQuery query = new ReadAllQuery();
Employee employee = new Employee();
employee.setFirstName("B%");
employee.setLastName("S%");
employee.setSalary(0);
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);
 
List results = (List) session.executeQuery(query);

Query by Example Policy Using Key Words

This example uses key words for Strings and ignores -1.

ReadAllQuery query = new ReadAllQuery();
Employee employee = new Employee();
employee.setFirstName("bob joe fred");
employee.setLastName("smith mc mac");
employee.setSalary(-1);
query.setExampleObject(employee);
 
/* Query by example policy section */
QueryByExamplePolicy policy = new QueryByExamplePolicy();
policy.addSpecialOperation(String.class, "containsAnyKeyWords");
policy.excludeValue(-1);
query.setQueryByExamplePolicy(policy);
 
List results = (List) session.executeQuery(query);

Combining Query by Example with Expressions

ReadAllQuery query = new ReadAllQuery();
Employee employee = new Employee();
employee.setFirstName("Bob");
employee.setLastName("Smith");
query.setExampleObject(employee);
 
/* This section specifies the expression */
ExpressionBuilder builder = new ExpressionBuilder();
query.setSelectionCriteria(builder.get("salary").between(100000,200000);
List results = (List) session.executeQuery(query);