SQL Query

Sometimes it is useful to not use entity beans or dto beans but instead execute sql queries producing SqlRow objects or reading directly from JDBC ResultSet.

In typical recent applications around 2% of queries were SqlQuery type queries.

SqlQuery and SqlRow

Note that with SqlQuery we can get Ebean to add limit/offset clause appropriate to the database platform and we can bind parameters using all the mapped types (Enums, java.time, array types etc).

For more information goto docs / query / sqlquery 

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

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

for (SqlRow sqlRow : rows) {
  Long id = row.getLong("id")
  String name = row.getString("name")
  Timestamp whenActivated = row.getTimestamp("when_activated")
}

  

val sql = """
  select id, name, when_activated
  from customer
  where name like ?
""".trimIndent()

val rows = DB.sqlQuery(sql)
  .setParameter(1, "Rob%")
  .setMaxRows(100)
  .findList()

for (row in rows) {
  val id = row.getLong("id")
  val name = row.getString("name")
  val whenActivated = row.getTimestamp("when_activated")
}

SqlQuery and RowMapper

Alternatively we can use a RowMapper to read from the JDBC ResultSet. 

String sql = "select id, name, status from customer order by name desc";

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

   // read directly from ResultSet

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

   // do something interesting with the data
 });

val sql = "select id, name, status from customer order by name desc"

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

    // read directly from ResultSet

    val id = resultSet.getLong(1)
    val name = resultSet.getString(2)

    // do something interesting with the data
  }