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
Stringsql="select id, name, when_active from customer where status = ?";List<SqlRow>rows=DB.sqlQuery(sql).setParameter(1,"NEW").setMaxRows(100).findList();for(SqlRowsqlRow:rows){Longid=row.getLong("id")Stringname=row.getString("name")TimestampwhenActivated=row.getTimestamp("when_activated")}valsql=""" select id, name, when_activated from customer where name like ?""".trimIndent()valrows=DB.sqlQuery(sql).setParameter(1,"Rob%").setMaxRows(100).findList()for(rowinrows){valid=row.getLong("id")valname=row.getString("name")valwhenActivated=row.getTimestamp("when_activated")}SqlQuery and RowMapper
Alternatively we can use a RowMapper to read from the JDBC ResultSet.
Stringsql="select id, name, status from customer order by name desc";DB.sqlQuery(sql).findEachRow((resultSet,rowNum)->{// read directly from ResultSetlongid=resultSet.getLong(1);Stringname=resultSet.getString(2);// do something interesting with the data});valsql="select id, name, status from customer order by name desc"DB.sqlQuery(sql).findEachRow{resultSet,rowNum->// read directly from ResultSetvalid=resultSet.getLong(1)valname=resultSet.getString(2)// do something interesting with the data}