DtoQuery

DtoQuery is where we use SQL and map it into plain beans. These plain beans are just ordinary beans with public constructor(s) and public getters/setters.

public static class CustomerDto {

  Integer id;
  String name;

  ... // getters & setters
}

// using positioned parameters

List<CustomerDto> beans =
  database.findDto(CustomerDto.class, "select id, name from customer where name = ?")
  .setParameter(1, "Rob")
  .findList();

// using named parameters

List<CustomerDto> beans =
  database.findDto(CustomerDto.class, "select id, name from customer where name = :name")
  .setParameter("name", "Rob")
  .findList();

Mapping

When mapping the SQL resultSet to the DTO beans the following is used.

• Firstly we look for a constructor with the same number of arguments as columns in the resultSet. If we have such a constructor we use it for mapping (assuming the correct types).
• Secondly see if there are more columns in the resultSet than our largest constructor. If so we the largest constructor to read the first columns and then look to use setter methods to map the remaining columns.
• Finally we use the default constructor and setter methods.

Note that we perform the mapping using MethodHandles rather than reflection. That does mean it expects the Constructor and Setter methods to have public access.

firstRow / maxRows

We can apply firstRows/maxRows to the sql query.

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

List<CustomerDto> robs = server().findDto(CustomerDto.class, sql)
  .setParameter(1, "Rob%")
  .setMaxRows(10)
  .findList();

The appropriate sql will be added to apply firstRows/maxRows for the database platform.

select id, name from o_customer where name like ?
limit 10

RelaxedMode

By default when performing the mapping, if we are not able to map a column to a property (setter method) then an exception is thrown.

Instead we can set relaxed mode on the query via query.setRelaxedMode() and that means it will effectively ignore/skip any column that it can't map.

This is expected to be useful when there is potentially a large existing query and there is a desire to only map some of the columns into bean properties.

Postgres ANY

When using Postgres we can use Postgres ANY in DtoQuery using positioned parameters or by using setArrayParameter().

The benefit of using Postgres ANY is that we end up using a single SQL query regardless of the number of parameters - a single JDBC PreparedStatement and the database has a single SQL query to parse. This results in the database doing less hard parsing and better use of PreparedStatement cache, less query plans to cache, less memory consumed.

Example: Positioned parameter

When using index positioned parameters and binding a collection that will bind as a JDBC ARRAY.

List<Integer> ids = List.of(1, 2);
String sql = "select id, name from customer where id = any(?)"

List<CustomerDto> list = DB.findDto(CustomerDto.class, sql)
  .setParameter(ids) // bind as JDBC ARRAY
  .findList();

Example: Using setArrayParameter() for named parameters

When using named parameters by default Ebean expects use of an IN clause and collections are "expanded". For example, a collection of 3 values is expanded into 3 separate JDBC bind values ?,?,? rather than JDBC binding a single ARRAY. To bind collections as a Postgres ARRAY for an ANY clause we need to use setArrayParameter().

List<Integer> ids = List.of(1, 2);
String sql = "select id, name from o_customer where id = any(:idList)"

List<CustomerDto> list2 = DB.findDto(CustomerDto.class, sql)
  .setArrayParameter("idList", ids) // bind as JDBC ARRAY
  .findList();

findEach

We use findEach when processing a large query where we do not wish to hold all the beans in a list but instead process them one at a time.

String sql = "select id, name from o_customer where id > :id order by id desc";

database.findDto(CustomerDto.class, sql)
  .setParameter("id", 0)
  .findEach(customer -> {

    log.debug("got " + customer.getId() + " " + customer.getName());
    ...

  });

findStream

findStream is similar to findEach where we process the result one at a time. We should use a try with resources block to ensure the underlying resources are closed.

String sql = "select id, name from o_customer where id > ? order by id desc";

try (Stream<CustomerDto> customers =
  database.findDto(CustomerDto.class, sql)
    .setParameter(0)
    .findStream()) {

    // use the stream of customers ...
    ...
}

findIterate

findIterate is similar to findStream and findEach where we process the result one at a time. We should use a try with resources block to ensure the underlying resources are closed.

String sql = "select id, name from o_customer where id > :id order by id desc";

try (QueryIterator<CustomerDto> customers =
  database.findDto(CustomerDto.class, sql)
    .setParameter(0)
    .findIterate()) {

    // iterate through the customers
    ...
}

Single column

If we are looking to select a single column we look to use SqlQuery.mapToScalar() for that.

String sql = "select mysequence.nextval";

Long nextVal = DB.sqlQuery(sql)
  .mapToScalar(Long.class)
  .findOne();