SqlQuery

With SqlQuery we can execute sql queries without any entity beans or dto beans. Instead we can:

  • Read JDBC ResultSet directly
  • Read as SqlRow objects
  • Use mapToScalar() and read simple scalar values like Long, BigDecimal, OffsetDateTime etc
  • use mapTo() and RowMapper to map and read objects

 

Example findOne with SqlRow
String sql = "select id, name, when_active from customer where id = :id"

SqlRow row = DB.sqlQuery(sql)
    .setParameter("id", 42)
    .findOne();

String name = row.getString("name");
Timestamp active = row.getTimestamp("when_active");

 

Example findEachRow with ResultSet
String sql = "select id, name, status from customer order by name desc";

DB.sqlQuery(sql)
  .findEachRow((resultSet, rowNum) -> {

    long id = resultSet.getLong(1);
    String name = resultSet.getString(2);
    ...
  });
Example mapToScalar()
String sql = "select mysequence.nextval";

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

Binding positioned parameters using ?

Positioned parameters use ? as the placeholder.

String sql = "select id, name, when_active from customer where status = ? and when_created > ?";

List<SqlRow> rows = DB.sqlQuery(sql)
    .setParameter(1, "NEW")
    .setParameter(2, lastWeek)
    .findList();

We can omit the index positions like the next example.

String sql = "select id, name, when_active from customer where status = ? and when_created > ?";

List<SqlRow> rows = DB.sqlQuery(sql)
    .setParameter("NEW")
    .setParameter(lastWeek)
    .findList();

We can alternatively set multiple positioned parameters using setParameters().

String sql = "select id, name, when_active from customer where status = ? and when_created > ?";

List<SqlRow> rows = DB.sqlQuery(sql)
    .setParameters("NEW", lastWeek)
    .findList();

Binding named parameters using :name

Named parameters use the form :foo as the placeholder. Named parameters have the advantages that we can use them to bind collections into an IN expression and that the same parameter can appear multiple times in the SQL.

Binding using named parameters
String sql =
  "select id, name, when_active " +
  "from customer " +
  "where status = :status and when_created > :created";

List<SqlRow> rows = DB.sqlQuery(sql)
    .setParameter("status", "NEW")
    .setParameter("created", lastWeek)
    .findList();

Binding collections

To bind collections of values into an IN clause we need to used named parameters or indexed positioned parameters.

Named parameter example (:names)
String sql = "select c.id, c.name from customer c where c.name in (:names)";

List<SqlRow> rows = DB.sqlQuery(sql)
  .setParameter("names", asList("Rob", "Fiona", "Jack"))
  .findList();
Index parameter example - ?2
String sql = "select c.id, c.name from customer c " +
  "where c.status = ? and c.name in (?2) and c.when_created > ?";

List<SqlRow> rows = DB.sqlQuery(sql)
  .setParameter("NEW")
  .setParameter(asList("Rob", "Fiona", "Jack"))
  .setParameter(lastWeek)
  .findList();

firstRow / maxRows

We can specify firstRow and maxRows on a SqlQuery and Ebean will modify the SQL based on the database platform to add the appropriate row limit clause.

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

List<SqlRow> rows = DB.sqlQuery(sql)
    .setParameter(1, "NEW")
    .setFirstRow(10)
    .setMaxRows(10)
    .findList();

mapToScalar() - single column scalar result

Use mapToScalar() when the query has a single column in the select clause. This specifies the type to use to read the result.

String sql = "select mysequence.nextval";

Long nextVal = DB.sqlQuery(sql)
  .mapToScalar(Long.class)
  .findOne();
String sql = "select max(unit_price) from order_lines where order_qty > ?";

BigDecimal maxPrice = DB.sqlQuery(sql)
  .setParameter(42)
  .mapToScalar(BigDecimal.class)
  .findOne();
String sql = "select max(order_date) from orders where customer_id = ?";

OffsetDateTime maxDate = DB.sqlQuery(sql)
  .setParameter(42)
  .mapToScalar(OffsetDateTime.class)
  .findOne();

All scalar types that Ebean supports can be used - refer to mapping types.

mapTo() - RowMapper

We can implement a RowMapper to convert a ResultSet into a dto bean.

Note that this similar to DtoQuery except that the mapping is here is explicit and with DtoQuery the mapping is effectively automatic (no effort) based on naming convention mapping. In that sense the expectation is that people will use DtoQuery first and only use this explicit RowMapping when necessary.

static class CustomerDtoMapper implements RowMapper<CustomerDto> {

  @Override
  public CustomerDto map(ResultSet resultSet, int rowNum) throws SQLException {

    long id = resultSet.getLong(1);
    String name = resultSet.getString(2);
    String status = resultSet.getString(3);

    return new CustomerDto(id, name, status);
  }
}

...
static final CustomerDtoMapper CUSTOMER_MAPPER = new CustomerDtoMapper()

 

Then we can use it to return DTO beans.

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

List<CustomerDto> rob = DB.sqlQuery(sql)
  .setParameter("Rob")
  .mapTo(CUSTOMER_MAPPER)
  .findList();