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

Postgres ANY

When using Postgres we can use Postgres ANY in SqlQuery with positioned parameters.

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

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

List<Integer> ids = List.of(1, 2);

List<SqlRow> list = DB.sqlQuery("select id, name from o_customer where id = any(?)")
    .setParameter(1, ids) // bind as JDBC ARRAY
    .findList();

When using Postgres we would often prefer to use that over using an IN clause like the example below. The reason we prefer ANY is that it does not matter how many elements we have in the ARRAY we end up with a single SQL statement. This gives us a single query plan, single PrepareStatement and on the database end a single query statement to parse regardless of the number of id values we are binding.

In comparison to using an IN clause, if we have 3 ids that is a different SQL query to one that has 4 ids for example - we end up with more distinct PreparedStatements, potentially a lot more depending on the number of id values we have.

List<SqlRow> list = DB.sqlQuery("select id, name from o_customer where id in (:idList)")
    .setParameter("idList", ids) // bind parameter expansion
    .findList();

Using the IN clause will result in a different SQL query when we have different numbers of id values. With 4 id values supplied then in (:idList) is turned into SQL in (?, ?, ?, ?).

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