Jump to: navigation, search

Difference between revisions of "EclipseLink/Examples/JPA/ORMQueries"

Line 4: Line 4:
  
 
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:
 
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
+
* 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.
 
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.
+
* The org.eclipse.persistence.expressions.ExpressionBuilder class is the factory for constructing new expressions.
  
 
== A Simple Expression Builder Expression ==
 
== A Simple Expression Builder Expression ==
 
This example uses the query key lastName to reference the field name L_NAME.
 
This example uses the query key lastName to reference the field name L_NAME.
  
<code><pre>
+
<source lang="java">
 
Expression expression = new ExpressionBuilder().get("lastName").equal("Young");
 
Expression expression = new ExpressionBuilder().get("lastName").equal("Young");
</pre></code>
+
</source>
  
 
== An Expression Using the and() Method ==
 
== An Expression Using the and() Method ==
<code><pre>
+
<source lang="java">
 
ExpressionBuilder emp = new ExpressionBuilder();
 
ExpressionBuilder emp = new ExpressionBuilder();
 
Expression exp1, exp2;
 
Expression exp1, exp2;
Line 22: Line 22:
 
exp2 = emp.get("lastName").equal("Young");
 
exp2 = emp.get("lastName").equal("Young");
 
return exp1.and(exp2);
 
return exp1.and(exp2);
</pre></code>
+
</source>
  
 
= Custom SQL =
 
= Custom SQL =
Line 32: Line 32:
  
 
=== A Session Read Object Call Query With Custom SQL ===
 
=== A Session Read Object Call Query With Custom SQL ===
<code><pre>
+
<source lang="java">
 
Employee employee = (Employee) session.readObjectCall(Employee.class), new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44");
 
Employee employee = (Employee) session.readObjectCall(Employee.class), new SQLCall("SELECT * FROM EMPLOYEE WHERE EMP_ID = 44");
</pre></code>
+
</source>
  
 
=== A Session Method with Custom SQL ===
 
=== A Session Method with Custom SQL ===
 
This example queries user and time information.
 
This example queries user and time information.
<code><pre>
+
<source lang="java">
 
Vector rows = session.executeSelectingCall(new SQLCall("SELECT USER, SYSDATE FROM DUAL"));
 
Vector rows = session.executeSelectingCall(new SQLCall("SELECT USER, SYSDATE FROM DUAL"));
</pre></code>
+
</source>
  
 
== SQL Data Queries ==
 
== SQL Data Queries ==
Line 50: Line 50:
  
 
=== DataReadQuery Example ===
 
=== DataReadQuery Example ===
<code><pre>
+
<source lang="java">
 
DataModifyQuery query = new DataModifyQuery();
 
DataModifyQuery query = new DataModifyQuery();
 
query.setSQLString("USE SALESDATABASE");
 
query.setSQLString("USE SALESDATABASE");
 
session.executeQuery(query);
 
session.executeQuery(query);
</pre></code>
+
</source>
  
 
=== DirectReadQuery Example ===
 
=== DirectReadQuery Example ===
<code><pre>
+
<source lang="java">
 
DirectReadQuery query = new DirectReadQuery();
 
DirectReadQuery query = new DirectReadQuery();
 
query.setSQLString("SELECT EMP_ID FROM EMPLOYEE");
 
query.setSQLString("SELECT EMP_ID FROM EMPLOYEE");
 
Vector ids = (Vector) session.executeQuery(query);
 
Vector ids = (Vector) session.executeQuery(query);
</pre></code>
+
</source>
  
 
= Stored Procedure Calls =
 
= Stored Procedure Calls =
Line 67: Line 67:
  
 
== A Read All Query With a Stored Procedure ==
 
== A Read All Query With a Stored Procedure ==
<code><pre>
+
<source lang="java">
 
ReadAllQuery readAllQuery = new ReadAllQuery();
 
ReadAllQuery readAllQuery = new ReadAllQuery();
 
call = new StoredProcedureCall();
 
call = new StoredProcedureCall();
Line 74: Line 74:
 
readAllQuery.setCall(call);
 
readAllQuery.setCall(call);
 
Vector employees = (Vector) session.executeQuery(readAllQuery);
 
Vector employees = (Vector) session.executeQuery(readAllQuery);
</pre></code>
+
</source>
  
 
= EJB QL =
 
= EJB QL =
Line 88: Line 88:
  
 
== A Simple ReadAllQuery Using EJB QL ==
 
== A Simple ReadAllQuery Using EJB QL ==
<code><pre>
+
<source lang="java">
 
ReadAllQuery theQuery = new ReadAllQuery();
 
ReadAllQuery theQuery = new ReadAllQuery();
 
theQuery.setReferenceClass(EmployeeBean.class);
 
theQuery.setReferenceClass(EmployeeBean.class);
Line 94: Line 94:
 
 
 
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
 
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);
</pre></code>
+
</source>
  
 
== A Simple ReadAllQuery Using EJB QL and Passing Arguments ==
 
== A Simple ReadAllQuery Using EJB QL and Passing Arguments ==
 
This example defines the query similarly to Example 6–30, but creates, fills, and passes a vector of arguments to the executeQuery method.
 
This example defines the query similarly to Example 6–30, but creates, fills, and passes a vector of arguments to the executeQuery method.
  
<code><pre>
+
<source lang="java">
 
// First define the query
 
// First define the query
 
ReadAllQuery theQuery = new ReadAllQuery();
 
ReadAllQuery theQuery = new ReadAllQuery();
Line 112: Line 112:
 
// Finally execute the query passing in the arguments
 
// Finally execute the query passing in the arguments
 
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);
 
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);
</pre></code>
+
</source>
  
 
= Query By Example =
 
= Query By Example =
Line 121: Line 121:
 
This example queries the employee Bob Smith.
 
This example queries the employee Bob Smith.
  
<code><pre>
+
<source lang="java">
 
ReadObjectQuery query = new ReadObjectQuery();
 
ReadObjectQuery query = new ReadObjectQuery();
 
Employee employee = new Employee();
 
Employee employee = new Employee();
Line 128: Line 128:
 
query.setExampleObject(employee);
 
query.setExampleObject(employee);
 
Employee result = (Employee) session.executeQuery(query);
 
Employee result = (Employee) session.executeQuery(query);
</pre></code>
+
</source>
  
 
== Using Query by Example ==
 
== Using Query by Example ==
 
This example queries across the employee’s address.
 
This example queries across the employee’s address.
  
<code><pre>
+
<source lang="java">
 
ReadAllQuery query = new ReadAllQuery();
 
ReadAllQuery query = new ReadAllQuery();
 
Employee employee = new Employee();
 
Employee employee = new Employee();
Line 141: Line 141:
 
query.setExampleObject(employee);
 
query.setExampleObject(employee);
 
Vector results = (Vector) session.executeQuery(query);
 
Vector results = (Vector) session.executeQuery(query);
</pre></code>
+
</source>
  
 
== Query by Example Policy Using Like ==
 
== Query by Example Policy Using Like ==
 
This example uses like for Strings and includes only objects whose salary is greater than zero.
 
This example uses like for Strings and includes only objects whose salary is greater than zero.
  
<code><pre>
+
<source lang="java">
 
ReadAllQuery query = new ReadAllQuery();
 
ReadAllQuery query = new ReadAllQuery();
 
Employee employee = new Employee();
 
Employee employee = new Employee();
Line 162: Line 162:
  
 
Vector results = (Vector) session.executeQuery(query);
 
Vector results = (Vector) session.executeQuery(query);
</pre></code>
+
</source>
  
 
== Query by Example Policy Using Key Words ==
 
== Query by Example Policy Using Key Words ==
 
This example uses key words for Strings and ignores -1.
 
This example uses key words for Strings and ignores -1.
  
<code><pre>
+
<source lang="java">
 
ReadAllQuery query = new ReadAllQuery();
 
ReadAllQuery query = new ReadAllQuery();
 
Employee employee = new Employee();
 
Employee employee = new Employee();
Line 182: Line 182:
  
 
Vector results = (Vector) session.executeQuery(query);
 
Vector results = (Vector) session.executeQuery(query);
</pre></code>
+
</source>
  
 
== Combining Query by Example with Expressions ==
 
== Combining Query by Example with Expressions ==
<code><pre>
+
<source lang="java">
 
ReadAllQuery query = new ReadAllQuery();
 
ReadAllQuery query = new ReadAllQuery();
 
Employee employee = new Employee();
 
Employee employee = new Employee();
Line 196: Line 196:
 
query.setSelectionCriteria(builder.get("salary").between(100000,200000);
 
query.setSelectionCriteria(builder.get("salary").between(100000,200000);
 
Vector results = (Vector) session.executeQuery(query);
 
Vector results = (Vector) session.executeQuery(query);
</pre></code>
+
</source>

Revision as of 15:07, 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.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.

A Read All Query With a Stored Procedure

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

EJB QL

EJB QL 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 EJB QL is usually associated with Enterprise JavaBeans (EJBs), EclipseLink enables you to use EJB QL with regular Java objects as well. In EclipseLink, EJB QL 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.

  • EJB QL 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 EJB QL

ReadAllQuery theQuery = new ReadAllQuery();
theQuery.setReferenceClass(EmployeeBean.class);
theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp");Vector returnedObjects = (Vector)aSession.executeQuery(theQuery);

A Simple ReadAllQuery Using EJB QL and Passing Arguments

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

// First define the query
ReadAllQuery theQuery = new ReadAllQuery();
theQuery.setReferenceClass(EmployeeBean.class);
theQuery.setEJBQLString("SELECT OBJECT(emp) FROM EmployeeBean emp WHERE
emp.firstName = ?1");
...
// Next define the Arguments
Vector theArguments = new Vector();
theArguments.add("Bob");
...
// Finally execute the query passing in the arguments
Vector returnedObjects = (Vector)aSession.executeQuery(theQuery, theArguments);

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);
Vector results = (Vector) 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);
 
Vector results = (Vector) 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);
 
Vector results = (Vector) 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);
Vector results = (Vector) session.executeQuery(query);