public interface SqlQuery extends Serializable
The returned SqlRow objects are similar to a LinkedHashMap with some type conversion support added.
Refer to DtoQuery
for native sql queries returning DTO beans.
Refer to Database.findNative(Class, String)
for native sql queries returning entity beans.
// example using named parameters
String sql = "select id, name from customer where name like :name and status_code = :status";
List<SqlRow> list =
DB.sqlQuery(sql)
.setParameter("name", "Acme%")
.setParameter("status", "ACTIVE")
.findList();
Modifier and Type | Method and Description |
---|---|
void |
findEach(java.util.function.Consumer<SqlRow> consumer)
Execute the SqlQuery iterating a row at a time.
|
void |
findEachRow(RowConsumer consumer)
Execute the query reading each row from ResultSet using the RowConsumer.
|
void |
findEachWhile(java.util.function.Predicate<SqlRow> consumer)
Execute the SqlQuery iterating a row at a time with the ability to stop consuming part way through.
|
List<SqlRow> |
findList()
Execute the query returning a list.
|
<T> List<T> |
findList(RowMapper<T> mapper)
Execute the query returning a list using the mapper.
|
SqlRow |
findOne()
Execute the query returning a single row or null.
|
<T> T |
findOne(RowMapper<T> mapper)
Execute the query returning a single result using the mapper.
|
Optional<SqlRow> |
findOneOrEmpty()
Execute the query returning an optional row.
|
<T> T |
findSingleAttribute(Class<T> attributeType)
Execute the query returning a single scalar attribute.
|
<T> List<T> |
findSingleAttributeList(Class<T> attributeType)
Execute the query returning a list of scalar attribute values.
|
BigDecimal |
findSingleDecimal()
Execute the query returning a single BigDecimal value.
|
Long |
findSingleLong()
Execute the query returning a single Long value.
|
SqlQuery |
setBufferFetchSizeHint(int bufferFetchSizeHint)
A hint which for JDBC translates to the Statement.fetchSize().
|
SqlQuery |
setFirstRow(int firstRow)
Set the index of the first row of the results to return.
|
SqlQuery |
setLabel(String label)
Set a label that can be put on performance metrics that are collected.
|
SqlQuery |
setMaxRows(int maxRows)
Set the maximum number of query results to return.
|
SqlQuery |
setParameter(int position,
Object value)
The same as bind for positioned parameters.
|
SqlQuery |
setParameter(String name,
Object value)
The same as bind for named parameters.
|
SqlQuery |
setParams(Object... values)
Set one of more positioned parameters.
|
SqlQuery |
setTimeout(int secs)
Set a timeout on this query.
|
void findEach(java.util.function.Consumer<SqlRow> consumer)
This streaming type query is useful for large query execution as only 1 row needs to be held in memory.
void findEachWhile(java.util.function.Predicate<SqlRow> consumer)
Returning false after processing a row stops the iteration through the query results.
This streaming type query is useful for large query execution as only 1 row needs to be held in memory.
@Nullable SqlRow findOne()
If this query finds 2 or more rows then it will throw a PersistenceException.
<T> T findOne(RowMapper<T> mapper)
mapper
- Used to map each ResultSet row into the result object.<T> List<T> findList(RowMapper<T> mapper)
mapper
- Used to map each ResultSet row into the result object.void findEachRow(RowConsumer consumer)
This provides a low level option that reads directly from the JDBC ResultSet and is good for processing very large results where (unlike findList) we don't hold all the results in memory but instead can process row by row.
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
});
consumer
- Used to read and process each ResultSet row.@Nonnull Optional<SqlRow> findOneOrEmpty()
<T> T findSingleAttribute(Class<T> attributeType)
@{code String sql = "select max(unit_price) from o_order_detail where order_qty > ?"; BigDecimal maxPrice = DB.sqlQuery(sql) .setParameter(1, 2) .findSingleAttribute(BigDecimal.class); }
The attributeType can be any scalar type that Ebean supports (includes javax time types, Joda types etc).
attributeType
- The type of the returned valueBigDecimal findSingleDecimal()
This is an alias for findSingleAttribute(BigDecimal.class)
Long findSingleLong()
This is an alias for findSingleAttribute(Long.class)
<T> List<T> findSingleAttributeList(Class<T> attributeType)
String sql =
" select (unit_price * order_qty) " +
" from o_order_detail " +
" where unit_price > ? " +
" order by (unit_price * order_qty) desc";
//
List<BigDecimal> lineAmounts =
DB.sqlQuery(sql)
.setParameter(1, 3)
.findSingleAttributeList(BigDecimal.class);
The attributeType can be any scalar type that Ebean supports (includes javax time types, Joda types etc).
attributeType
- The type of the returned valueSqlQuery setParameter(String name, Object value)
SqlQuery setParams(Object... values)
This is a convenient alternative to multiple calls setParameter().
String sql = "select id, name from customer where name like ? and status = ?";
List<SqlRow> list =
DB.sqlQuery(sql)
.setParams("Rob", Status.NEW)
.findList();
// is the same as ...
List<SqlRow> list =
DB.sqlQuery(sql)
.setParameter(1, "Rob")
.setParameter(2, "Status.NEW)
.findList();
SqlQuery setParameter(int position, Object value)
SqlQuery setFirstRow(int firstRow)
SqlQuery setMaxRows(int maxRows)
SqlQuery setTimeout(int secs)
This will typically result in a call to setQueryTimeout() on a preparedStatement. If the timeout occurs an exception will be thrown - this will be a SQLException wrapped up in a PersistenceException.
secs
- the query timeout limit in seconds. Zero means there is no limit.SqlQuery setLabel(String label)
SqlQuery setBufferFetchSizeHint(int bufferFetchSizeHint)
Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet.
Copyright © 2019. All rights reserved.