ORM Query

There are several forms of ORM query. For detailed information on queries goto docs / query

"Pure" ORM Query

Below are examples of pure ORM queries. We specify no SQL functions explicitly and all the SQL is generated.

In typical recent applications around 85% of queries were "pure" ORM queries. 

Customer rob =
  new QCustomer()
    .name.equalTo("Rob")
    .findOne();

List<Customer> customers =
  new QCustomer()
    .status.equalTo(Status.NEW)
    .billingAddress.city.equalTo("Auckland")
    .findList();

val rob =
  QCustomer()
    .name.equalTo("Rob")
    .findOne()

val customers =
  QCustomer()
    .status.equalTo(Status.NEW)
    .billingAddress.city.equalTo("Auckland")
    .findList()

ORM Query with SQL in Select

We can use SQL in the select clause. Some examples of this type of query are: 

// using sql functions in the select clause

List<String> names =
  new QContact()
    .select("concat(lastName,', ',firstName)")
    .lastName.startsWith("A")
    .findSingleAttributeList();

BigDecimal routeDistance =
  new QTrip()
    .select("ST_Distance(ST_StartPoint(route), ST_EndPoint(route))::BigDecimal")
    .id.equalTo(tripId)
    .findSingleAttribute();

  

// using sql functions in the select clause

var names: List<String> =
  QContact()
    .select("concat(lastName,' ',firstName)")
    .lastName.startsWith("A")
    .findSingleAttributeList()

val routeDistance: BigDecimal =
  QTrip()
    .select("ST_Distance(ST_StartPoint(route), ST_EndPoint(route))::BigDecimal")
    .id.equalTo(tripId)
    .findSingleAttribute()

ORM Query with SQL in Where

We can also use SQL in the where clause. Frequently this is for specifying sql sub-queries. 

// sql functions used in predicates
List<Order> orders =
  new QOrder()
  .raw("add_days(orderDate, 10) < ?", someDate)
  .findList();

// SQL sub-query - often easiest to specify the subquery in sql form
String subQuery
    = "t0.customer_id in " +
      "(select customer_id from customer_group where group_id = any(?::uuid[]))";

List<Order> orders =
  new QOrder()
   .status.equalTo(Status.NEW)
   .raw(subQuery, groupIds)   // use raw SQL in where clause
   .findList();

  

// sql functions used in predicates
var orders =
  QOrder()
  .raw("add_days(orderDate, 10) < ?", someDate)
  .findList()

// SQL sub-query - often easiest to specify the subquery in sql form
val subQuery =
    "t0.customer_id in " +
    "(select customer_id from customer_group where group_id = any(?::uuid[]))"

var orders =
  QOrder()
   .status.equalTo(Status.NEW)
   .raw(subQuery, groupIds)   // use raw SQL in where clause
   .findList()

In typical recent applications around 5% of queries were ORM queries with some SQL (mostly subqueries).

ORM to DTO Query

We can define an ORM query and then use asDto to turn it into a DTO query. We are using the ORM to generate the SQL but then want that mapped directly into a DTO bean. 

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

  

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

val contacts =
  new QContact()
    .select("email, concat(lastName, ', ', firstName) as fullName")
    .lastName.startsWith("A")
    .orderBy()
      .lastName.asc()
    .setMaxRows(10)
    .asDto(ContactDto::class.java)
    .findList()

SQL - aka find native

We can also specify the query in SQL and have that automatically mapped to entity beans (like this) or DTO beans. 

String sql = "select id, name from customer where name like ?";

Customer customer = DB.findNative(Customer.class, sql)
    .setParameter(1, "Jo%")
    .findOne();

  

String sql = "select id, name from customer where name like ?";

val customer = DB.findNative(Customer::class.java, sql)
    .setParameter(1, "Jo%")
    .findOne()