Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

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

(Query By Example)
Line 24: Line 24:
 
</pre></code>
 
</pre></code>
  
== Custom SQL ==
+
= 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.
 
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 ===
+
== 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
 
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.
 
query instance.
  
==== A Session Read Object Call Query With Custom SQL ====
+
=== A Session Read Object Call Query With Custom SQL ===
 
<code><pre>
 
<code><pre>
 
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>
 
</pre></code>
  
==== 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>
 
<code><pre>
Line 63: Line 63:
 
</pre></code>
 
</pre></code>
  
== Stored Procedure Calls ==
+
= 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.
 
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 ===
+
== A Read All Query With a Stored Procedure ==
 
<code><pre>
 
<code><pre>
 
ReadAllQuery readAllQuery = new ReadAllQuery();
 
ReadAllQuery readAllQuery = new ReadAllQuery();
Line 76: Line 76:
 
</pre></code>
 
</pre></code>
  
== EJB QL ==
+
= 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:
 
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 do not need to know the database structure (tables, fields).
Line 87: Line 87:
 
bean you are querying against).
 
bean you are querying against).
  
=== A Simple ReadAllQuery Using EJB QL ===
+
== A Simple ReadAllQuery Using EJB QL ==
 
<code><pre>
 
<code><pre>
 
ReadAllQuery theQuery = new ReadAllQuery();
 
ReadAllQuery theQuery = new ReadAllQuery();
Line 96: Line 96:
 
</pre></code>
 
</pre></code>
  
=== 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.
  
Line 114: Line 114:
 
</pre></code>
 
</pre></code>
  
== Query By Example ==
+
= Query By Example =
 
Query by example enables you to specify queries when you provide sample instances of the persistent objects to be queried.
 
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.
 
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 ===
+
== Using Query by Example ==
 
This example queries the employee Bob Smith.
 
This example queries the employee Bob Smith.
  
Line 130: Line 130:
 
</pre></code>
 
</pre></code>
  
=== Using Query by Example ===
+
== Using Query by Example ==
 
This example queries across the employee’s address.
 
This example queries across the employee’s address.
  
Line 143: Line 143:
 
</pre></code>
 
</pre></code>
  
=== 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.
  
Line 164: Line 164:
 
</pre></code>
 
</pre></code>
  
=== 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.
  
Line 184: Line 184:
 
</pre></code>
 
</pre></code>
  
=== Combining Query by Example with Expressions ===
+
== Combining Query by Example with Expressions ==
 
<code><pre>
 
<code><pre>
 
ReadAllQuery query = new ReadAllQuery();
 
ReadAllQuery query = new ReadAllQuery();

Revision as of 11:26, 25 October 2007

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.

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);

Back to the top