Select

select allows us to control the properties that are fetched at the root level of the graph. We can specify to only select some properties to fetch. The result are beans that are partially populated. 

// "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();

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.

Using query beans to define the select properties provides a type safe way to define what part of the object graph to fetch.

The same query written using the query expression API is: 

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

alias() - specify properties

Each query bean has a static alias() method that returns a query bean that we use to specify properties for fetch in select() and fetch(). 

// customer "alias"
QCustomer cust = QCustomer.alias();

// contact "alias"
QCustomer con = QContact.alias();

List<Customer> customers =
  new QCustomer()

    .select(cust.name, cust.version, cust.whenCreated)
    .contacts.fetch(con.firstName, con.LastName, con.email)

    .name.istartsWith("Rob")
    .findList();

In the above we have alias beans for customer and contact. We use those to specify which specific properties we want to fetch.

Note that we can assign alias() to a static final field. 

private static final QCustomer CUST = QCustomer.alias();
...
List<Customer> customers =
  new QCustomer()
    .select(CUST.name)
    .name.istartsWith("Rob")
    .findList();

Alias for static imports

As an alternative to the alias() method each query bean also provides a static Alias class for the case where we rather use static imports.

For example, we can use QCustomer.Alias to specify properties of customer like the below: 

List<Customer> customers =
  new QCustomer()

    .select(QCustomer.Alias.name, QCustomer.Alias.whenCreated)
    .findList();

We can then change these to use static imports to get: 

import static org.domain.query.QCustomer.Alias.name;
import static org.domain.query.QCustomer.Alias.whenCreated;
...


List<Customer> customers =
  new QCustomer()

    .select(name, whenCreated) // using static imports of QCustomer.Alias
    .findList();

The limitation of using Alias like this is that if we do this for multiple query bean types (like QCustomer.Alias and QContact.Alias) then we might get a name clash. For example, if both had a property called whenCreated we can only static import one of them. 

import static ...query.QCustomer.Alias.name;
import static ...query.QCustomer.Alias.version;
import static ...query.QCustomer.Alias.whenCreated;
import static ...query.QContact.Alias.firstName;
import static ...query.QContact.Alias.lastName;

// Use explicit QContact.Alias.whenCreated (as clash with QCustomer.Alias.whenCreated)

List<Customer> customers =
  new QCustomer()
    .select(name, version, whenCreated)
    .contacts.fetch(firstName, lastName, QContact.Alias.whenCreated)

    .name.istartsWith("Rob")
    .findList();

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