Package io.ebean

Interface SqlQuery

  • All Superinterfaces:
    Serializable
    public interface SqlQuery
extends Serializable
    Query object for performing native SQL queries that return SqlRow or directly read ResultSet using a RowMapper.

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

    • Method Detail

      • findEach

        void findEach​(Consumer<SqlRow> consumer)
        Execute the SqlQuery iterating a row at a time.

        This streaming type query is useful for large query execution as only 1 row needs to be held in memory.

      • findEachWhile

        void findEachWhile​(Predicate<SqlRow> consumer)
        Execute the SqlQuery iterating a row at a time with the ability to stop consuming part way through.

        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.

      • findOne

        @Nullable
SqlRow findOne()
        Execute the query returning a single row or null.

        If this query finds 2 or more rows then it will throw a PersistenceException.

      • findEachRow

        void findEachRow​(RowConsumer consumer)
        Execute the query reading each row from ResultSet using the RowConsumer.

        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

    });
        Parameters:
        consumer - Used to read and process each ResultSet row.

      • findSingleAttribute

        @Deprecated
<T> T findSingleAttribute​(Class<T> attributeType)
        Deprecated.
        Deprecated - migrate to .mapToScalar(attributeType).findOne(). 
        

    .mapToScalar(BigDecimal.class)
    .findOne();

      • findSingleLong

        @Deprecated
Long findSingleLong()
        Deprecated.
        Deprecated - migrate to .mapToScalar(Long.class).findOne(). 
        

    .mapToScalar(Long.class)
    .findOne();

      • setParameters

        SqlQuery setParameters​(Object... values)
        Set one of more positioned parameters.

        This is a convenient alternative to multiple calls to setParameter(Object). 

        

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

   List<SqlRow> list =
     DB.sqlQuery(sql)
       .setParameters("Rob", Status.NEW)
       .findList();


   // effectively the same as ...

       .setParameter("Rob")
       .setParameter("Status.NEW)

   // and ...

       .setParameter(1, "Rob")
       .setParameter(2, "Status.NEW)

      • setParameter

        SqlQuery setParameter​(Object value)
        Set the next bind parameter by position. 
        

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

   List<SqlRow> list =
     DB.sqlQuery(sql)
       .setParameter("Rob")
       .setParameter("Status.NEW)
       .findList();

   // the same as ...

       .setParameters("Rob", Status.NEW)

   // and ...

       .setParameter(1, "Rob")
       .setParameter(2, "Status.NEW)

        When binding a collection of values into a IN expression we should use indexed parameters like ?1, ?2, ?3 etc rather than just ?. 

        

   String sql = "select c.id, c.name from customer c where c.name in (?1)";

   List<SqlRow> rows = DB.sqlQuery(sql)
       .setParameter(asList("Rob", "Fiona", "Jack"))
       .findList();


   List<SqlRow> rows = DB.sqlQuery(sql)
       .setParameter(1, asList("Rob", "Fiona", "Jack"))
       .findList();
        Parameters:
        value - The value to bind

      • setParameter

        SqlQuery setParameter​(int position,
                      Object value)
        Bind the parameter by its index position (1 based like JDBC).

        When binding a collection of values into a IN expression we should use indexed parameters like ?1, ?2, ?3 etc rather than just ?. 

        

   String sql = "select c.id, c.name from customer c where c.name in (?1)";

   List<SqlRow> rows = DB.sqlQuery(sql)
       .setParameter(asList("Rob", "Fiona", "Jack"))
       .findList();


   List<SqlRow> rows = DB.sqlQuery(sql)
       .setParameter(1, asList("Rob", "Fiona", "Jack"))
       .findList();

      • setFirstRow

        SqlQuery setFirstRow​(int firstRow)
        Set the index of the first row of the results to return.

      • setMaxRows

        SqlQuery setMaxRows​(int maxRows)
        Set the maximum number of query results to return.

      • setTimeout

        SqlQuery setTimeout​(int secs)
        Set a timeout on this query.

        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.

        Parameters:
        secs - the query timeout limit in seconds. Zero means there is no limit.

      • setLabel

        SqlQuery setLabel​(String label)
        Set a label that can be put on performance metrics that are collected.

      • setBufferFetchSizeHint

        SqlQuery setBufferFetchSizeHint​(int bufferFetchSizeHint)
        A hint which for JDBC translates to the Statement.fetchSize().

        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.

      • mapToScalar

        <T> SqlQuery.TypeQuery<T> mapToScalar​(Class<T> attributeType)
        The query result maps to a single scalar value like Long, BigDecimal, String, UUID, OffsetDateTime etc.

        Any scalar type Ebean is aware of can be used including java time types like Instant, LocalDate, OffsetDateTime, UUID, Inet, Cdir etc. 

        

   String sql = " select min(updtime) from o_order_detail " +
                " where unit_price > ? and updtime is not null ";

   OffsetDateTime minCreated = DB.sqlQuery(sql)
     .setParameter(42)
     .mapToScalar(OffsetDateTime.class)
     .findOne();
        Parameters:
        attributeType - The type the result is returned as
        Returns:
        The query to execute via findOne() findList() etc

      • mapTo

        <T> SqlQuery.TypeQuery<T> mapTo​(RowMapper<T> mapper)
        Use a RowMapper to map the result to beans.
        Type Parameters:
        T - The type of beans mapped to
        Parameters:
        mapper - Maps rows to beans
        Returns:
        The query to execute by findOne() findList() etc