SqlQuery

With SqlQuery we can execute sql queries without any entity beans or dto beans. Instead we can either read the ResultSet directly or get SqlRow 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);
    ...
  });

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

RowMapper

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

static class CustDtoMapper implements RowMapper<CustDto> {

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

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

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

...
static final CustDtoMapper CUST_MAPPER = new CustDtoMapper()

 

Then we can use it to return DTO beans.

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

CustDto rob = DB.sqlQuery(sql)
  .setParameter(1, "Rob")
  .findOne(CUST_MAPPER);

Edit Page