Jump to: navigation, search

EclipseLink/Examples/JPA/ORMQueries

< EclipseLink‎ | Examples‎ | JPA
Revision as of 10:26, 25 October 2007 by Guy.pelletier.oracle.com (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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.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.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.readObjectCall(Employee.class), new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44");

A Session Method with Custom SQL

This example queries user and time information.

Vector 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");
Vector ids = (Vector) 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.