Select

select allows us to control the properties that are fetched. We can specify only selected properties to fetch.

List<Customer> customers =
  query()
    .select("name, registered, version")
    .findList();

With the above we specify the properties we want to fetch. We still get Customer entity beans returned but they are partially populated.

The SQL generated by the above query has:

select t0.id, t0.name, t0.registered, t0.version from customer t0

Note that the @Id property is automatically included for most queries and it is automatically exludeded for distinct, findSingleAttribute and aggregation queries.

Query beans

Query beans provide a type safe way to define what part of the object graph to fetch in addition to the approaches provided via the normal Query.

Each query bean provides an alias bean that can be used in select and fetch.

In the example below we specify which properties to include in the select and only these properties are loaded into the entity beans. This is described as "partial object".

// "alias" bean that can be used in select and fetch clauses
QCustomer cust = QCustomer.alias();

List<Customer> customers =
  new QCustomer()
  // only fetch some properties of customer (partial objects)
  .select(cust.name, cust.version, cust.whenCreated)
  .name.istartsWith("Rob")
  .findList();

The above query produces the following sql:

select t0.id, t0.name, t0.version, t0.when_created
from customer t0
where lower(t0.name) like ? escape'|'; --bind(rob%)

Formula

We need to use this string select clause when we want to specify a formula.

// java
List<String> names =
  new QContact()
    .select("concat(lastName,', ',firstName)")
    .lastName.startsWith("A")
    .findSingleAttributeList();
// kotlin
var names: List<String> =
  QContact()
    .select("concat(lastName,' ',firstName)")
    .lastName.startsWith("A")
    .findSingleAttributeList()
Example - Postgis ST_Distance formula

With this example we explicitly cast to a Java BigDecimal type using the ::BigDecimal cast at the end of the formula.

// given route is a Postgis geometry(linestring,4326)
// return the distance between the start and end points

BigDecimal routeDistance = query()
  .select("ST_Distance(ST_StartPoint(route), ST_EndPoint(route))::BigDecimal")
  .where()
  .idEq(tripId)
  .findSingleAttribute();

asDto

When we use a select formula we sometimes want to return the result into a plain DTO bean rather than an entity bean. We do this using .asDto(Class<D>) to turn the ORM query into a DtoQuery.

For more details refer to DTO queries.

// ContactDto is a plain bean with email and fullName properties

List<ContactDto> contacts =
  new QContact()
    .select("email, concat(lastName, ', ', firstName) as fullName")
    .lastName.startsWith("A")
    .orderBy()
       .lastName.asc()
    .setMaxRows(10)
    .asDto(ContactDto.class)
    .findList();

Aggregation

Similar to formula queries we can use the standard aggregations of SUM, MAX, MIN, AVG and COUNT in the select clause.

For more details refer to Aggregation queries.

Single aggregation queries
// java
Timestamp maxWhen  =
  new QContact()
    .select("max(whenModified)")
    .findSingleAttribute()
// kotlin
var maxWhen: Timestamp =
  QContact()
    .select("max(whenModified)")
    .findSingleAttribute()
select max(t0.when_modified) from contact t0
Group by aggregation queries

When multiple column aggregation query we use the string select clause.

List<MachineStats> result =
  new QMachineStats()
  .select("machine, date, max(rate)")
  .date.gt(LocalDate.now().minusDays(10))
  .query().having().gt("max(rate)", 4)
  .findList();

The above query produces the following sql:

select t0.machine_id, t0.date, max(t0.rate)
from d_machine_stats t0
where t0.date > ?
group by t0.machine_id, t0.date
having max(t0.rate) > ?

Edit Page