BASE Development information Query API Query elements

Query elements: restrictions, joins, ordering, etc.

If the query implementation allows it, you can customize any part of a query except for the root-entity:

SELECT <selection-list>
FROM <root-entity>
[JOIN <joined-entity>]
[WHERE <restrictions>]
[GROUP BY <groupby-list]
[HAVING <restrictions>]
[ORDER BY <orderby-list>]

A query is customized by adding QueryElement:s to it. There are five types of query elements:

Select

Goes into the selection-list part of the query. Typically this is a column/property name and an alias. It can also be a more complex expression:

// Select the name of a user
Query q = ...
q.select(Select.expression(Hql.property("usr", "name"), "username"));

// Generated QL
SELECT usr.name AS username

// Select the log ratio of foreground intensities
Query q = ...
Expression logRatio = 
  Expressions.divide(
    Expression.log(Hql.property("ch1fg")),
    Expressions.log(Hql.property(ch2fg"))
  )
);
q.select(Select.expression(logRatio, "logratio"));

// Generated QL
SELECT LOG(ch1fg)/LOG(ch2fg) AS logratio

The above examples should be seen as exemples only. As of the current implementation it is only the dynamic queries that allows you to specify what to select. The other queries always selects a specific item.

Join

Goes into the joined-entity part of the query. We are very restrictive about what can be joined. For entity queries we can join any other entity associated with the root entity. Joins are normally used together with restrictions, for example to find all user in a group:

// Select all users in group with id=4
Query q = User.getQuery();
q.join(Hql.innerJoin("groups", "grp"));
q.restrict(
   Restrictions.eq(
      Hql.alias("grp"),
      Expressions.integer(4)
   )
);

// Generated HQL
SELECT usr
FROM UserData usr
JOIN usr.groups grp
WHERE grp = 4

Restriction

Goes into the restrictions parts of the query. Restrictions are used to apply filters to a query and only return results matching a certain criteria. For example we can find all users with the name "Nicklas".

// Find all uses named "Nicklas"
Query q = User.getQuery();
q.restrict(
   Restrictions.eq(
      Hql.property("name"),
      Expressions.parameter("name")
   )
);
q.setParameter("name", "Nicklas");

// Generated HQL
SELECT usr
FROM UserData usr
WHERE usr.name = :name

When the query is executed the value of the name parameter is set to Nicklas and only users with that name are returned.

Restrictions can be applied before (WHERE) or after (HAVING) grouping of the results. As of the current implementation it is only the dynamic queries that allows you to group the results.

Order

Goes into the orderby-list part of the query. Typically this is the name of a column/property that should be used when sorting the results. For example we can sort the user by name:

// Sort the users by name
Query q = User.getQuery();
q.order(Orders.asc(HqlExpressions.property("name"))):

// Generated HQL
SELECT usr
FROM UserData usr
ORDER BY usr.name ASC

Expression

Expressions are used to build the other types of query elements. The Expressions class contains mostly mathematical expressions. The Aggregations class contains aggregate functions that are only useful in the selection list together with grouping. The Hql class contains expressions based on the properties of an item. See above and the API javadoc for more code examples.