Object relational query for finding a List, Set, Map or single entity bean.
Example: Create the query using the API.
List<Order> orderList =
Ebean.find(Order.class)
.fetch("customer")
.fetch("details")
.where()
.like("customer.name","rob%")
.gt("orderDate",lastWeek)
.orderBy("customer.id, id desc")
.setMaxRows(50)
.findList();
...
Example: The same query using the query language
String oql =
" find order "
+" fetch customer "
+" fetch details "
+" where customer.name like :custName and orderDate > :minOrderDate "
+" order by customer.id, id desc "
+" limit 50 ";
Query<Order> query = Ebean.createQuery(Order.class, oql);
query.setParameter("custName", "Rob%");
query.setParameter("minOrderDate", lastWeek);
List<Order> orderList = query.findList();
...
Example: Using a named query called "with.cust.and.details"
Query<Order> query = Ebean.createNamedQuery(Order.class,"with.cust.and.details");
query.setParameter("custName", "Rob%");
query.setParameter("minOrderDate", lastWeek);
List<Order> orderList = query.findList();
...
Autofetch
Ebean has built in support for "Autofetch". This is a mechanism where a query
can be automatically tuned based on profiling information that is collected.
This is effectively the same as automatically using select() and fetch() to
build a query that will fetch all the data required by the application and no
more.
It is expected that Autofetch will be the default approach for many queries
in a system. It is possibly not as useful where the result of a query is sent
to a remote client or where there is some requirement for "Read Consistency"
guarantees.
Query Language
Partial Objects
The find and fetch clauses support specifying a list of
properties to fetch. This results in objects that are "partially populated".
If you try to get a property that was not populated a "lazy loading" query
will automatically fire and load the rest of the properties of the bean (This
is very similar behaviour as a reference object being "lazy loaded").
Partial objects can be saved just like fully populated objects. If you do
this you should remember to include the "Version" property in the
initial fetch. If you do not include a version property then optimistic
concurrency checking will occur but only include the fetched properties.
Refer to "ALL Properties/Columns" mode of Optimistic Concurrency checking.
[ find {bean type} [ ( * | {fetch properties} ) ] ]
[ fetch {associated bean} [ ( * | {fetch properties} ) ] ]
[ where {predicates} ]
[ order by {order by properties} ]
[ limit {max rows} [ offset {first row} ] ]
FIND {bean type} [ ( * | {fetch properties} ) ]
With the find you specify the type of beans to fetch. You can optionally
specify a list of properties to fetch. If you do not specify a list of
properties ALL the properties for those beans are fetched.
In object graph terms the find clause specifies the type of bean at
the root level and the fetch clauses specify the paths of the object
graph to populate.
FETCH {associated property} [ ( * | {fetch
properties} ) ]
With the fetch you specify the associated property to fetch and populate. The
associated property is a OneToOnem, ManyToOne, OneToMany or ManyToMany
property. When the query is executed Ebean will fetch the associated data.
For fetch of a path we can optionally specify a list of properties to fetch.
If you do not specify a list of properties ALL the properties for that bean
type are fetched.
WHERE {list of predicates}
The list of predicates which are joined by AND OR NOT ( and ). They can
include named (or positioned) bind parameters. These parameters will need to
be bound by
Query#setParameter(String,Object).
ORDER BY {order by properties}
The list of properties to order the result. You can include ASC (ascending)
and DESC (descending) in the order by clause.
LIMIT {max rows} [ OFFSET {first row} ]
The limit offset specifies the max rows and first row to fetch. The offset is
optional.
Examples of Ebean's Query Language
Find orders fetching all its properties
find order
Find orders fetching all its properties
find order (*)
Find orders fetching its id, shipDate and status properties. Note that the id
property is always fetched even if it is not included in the list of fetch
properties.
find order (shipDate, status)
Find orders with a named bind variable (that will need to be bound via
Query#setParameter(String,Object)).
find order
where customer.name like :custLike
Find orders and also fetch the customer with a named bind parameter. This
will fetch and populate both the order and customer objects.
find order
fetch customer
where customer.id = :custId
Find orders and also fetch the customer, customer shippingAddress, order
details and related product. Note that customer and product objects will be
"Partial Objects" with only some of their properties populated. The customer
objects will have their id, name and shipping address populated. The product
objects (associated with each order detail) will have their id, sku and name
populated.
find order
fetch customer (name)
fetch customer.shippingAddress
fetch details
fetch details.product (sku, name)
Early parsing of the Query
When you get a Query object from a named query, the query statement has
already been parsed. You can then add to that query (add fetch paths, add to
the where clause) or override some of its settings (override the order by
clause, first rows, max rows).
The thought is that you can use named queries as a 'starting point' and then
modify the query to suit specific needs.
Building the Where clause
You can add to the where clause using Expression objects or a simple String.
Note that the ExpressionList has methods to add most of the common
expressions that you will need.
- where(String addToWhereClause)
- where().add(Expression expression)
- where().eq(propertyName, value).like(propertyName , value)...
The full WHERE clause is constructed by appending together
original query where clause (Named query or query.setQuery(String oql))
clauses added via query.where(String addToWhereClause)
clauses added by Expression objects
The above is the order that these are clauses are appended to give the full
WHERE clause.
Design Goal
This query language is NOT designed to be a replacement for SQL. It is
designed to be a simple way to describe the "Object Graph" you want Ebean to
build for you. Each find/fetch represents a node in that "Object Graph" which
makes it easy to define for each node which properties you want to fetch.
Once you hit the limits of this language such as wanting aggregate functions
(sum, average, min etc) or recursive queries etc you use SQL. Ebean's goal is
to make it as easy as possible to use your own SQL to populate entity beans.
Refer to
RawSql .