<title>SqlQuery | Ebean</title>

<meta name="layout" content="_layout2/base-docs.html"/>

<meta name="bread1" content="Query" href="/docs/query/"/>

<meta name="bread2" content="SqlQuery" href="/docs/query/sqlquery"/>

<#assign n0_docs="true">

<#assign n1_query="true">

<#assign sqlquery= "true">

<h2>SqlQuery</h2>

With SqlQuery we can execute sql queries without any entity beans or dto beans.

Instead we can:

<li>Read JDBC <code>ResultSet</code> directly</li>

<li>Read as <code>SqlRow</code> objects</li>

<li>Use <code>mapToScalar()</code> and read simple scalar values like Long, BigDecimal, OffsetDateTime etc</li>

<li>use <code>mapTo()</code> and <code>RowMapper</code> to map and read objects</li>

<p>&nbsp;</p>

<h5>Example findOne with SqlRow</h5>

<pre content="java">

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

<p>&nbsp;</p>

<h5>Example findEachRow with ResultSet</h5>

<pre content="java">

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

...

});

<h5>Example mapToScalar()</h5>

<pre content="java">

String sql = "select mysequence.nextval";

Long nextVal = DB.sqlQuery(sql)

.mapToScalar(Long.class)

.findOne();

<h2 id="bind-positioned" class="art">Binding positioned parameters using ?</h2>

Positioned parameters use <code>?</code> as the placeholder.

<pre content="java">

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.

<pre content="java">

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 <code>setParameters()</code>.

<pre content="java">

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

List<|SqlRow> rows = DB.sqlQuery(sql)

.setParameters("NEW", lastWeek)

.findList();

<h2 id="bind-named" class="art">Binding named parameters using :name</h2>

Named parameters use the form <code>:foo</code> as the placeholder. Named parameters have the

advantages that we can use them to bind collections into an <code>IN</code> expression and

that the same parameter can appear multiple times in the SQL.

<h5>Binding using named parameters</h5>

<pre content="java">

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

<h2 id="collections" class="art">Binding collections</h2>

To bind collections of values into an IN clause we need to used named parameters

or indexed positioned parameters.

<h5>Named parameter example (:names)</h5>

<pre content="java">

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

<h5>Index parameter example - ?2</h5>

<pre content="java">

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

<h2 id="any" class="art">Postgres ANY</h2>

When using Postgres we can use Postgres <code>ANY</code> in SqlQuery with positioned parameters.

The benefit of using Postgres <code>ANY</code> 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.

<h5>Example</h5>

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

<pre content="java">

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 <code>IN</code> clause

like the example below. The reason we prefer <code>ANY</code> 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 <code>IN</code> 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.

<pre content="java">

List<|SqlRow> list = DB.sqlQuery("select id, name from o_customer where id in (:idList)")

.setParameter("idList", ids) // bind parameter expansion

.findList();

Using the <code>IN</code> clause will result in a different SQL query when we have different numbers of

id values. With 4 id values supplied then <code>in (:idList)</code> is turned into SQL <code>in (?, ?, ?, ?)</code>.

<h2 id="limit" class="art">firstRow / maxRows</h2>

We can specify <em>firstRow</em> and <em>maxRows</em> on a SqlQuery and Ebean will modify

the SQL based on the database platform to add the appropriate row limit clause.

<pre content="java">

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

<h2 id="scalar" class="art">mapToScalar() - single column scalar result</h2>

Use <code>mapToScalar()</code> when the query has a single column in the <em>select clause</em>.

This specifies the type to use to read the result.

<pre content="java">

String sql = "select mysequence.nextval";

Long nextVal = DB.sqlQuery(sql)

.mapToScalar(Long.class)

.findOne();

<pre content="java">

String sql = "select max(unit_price) from order_lines where order_qty > ?";

BigDecimal maxPrice = DB.sqlQuery(sql)

.setParameter(42)

.mapToScalar(BigDecimal.class)

.findOne();

<pre content="java">

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 <a href="/docs/mapping/type/">mapping types</a>.

<h2 id="rowmapper" class="art">mapTo() - RowMapper</h2>

We can implement a <em>RowMapper</em> to convert a ResultSet into a dto bean.

Note that this similar to <a href="dtoquery">DtoQuery</a> 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.

<pre content="java">

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

<p>&nbsp;</p>

Then we can use it to return DTO beans.

<pre content="java">

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

List<|CustomerDto> rob = DB.sqlQuery(sql)

.setParameter("Rob")

.mapTo(CUSTOMER_MAPPER)

.findList();

<@next_edit "SqlUpdate" "/docs/query/sqlupdate" "/docs/query/sqlquery.html"/>