001package io.ebean;
002
003import javax.annotation.Nonnull;
004import javax.annotation.Nullable;
005import java.io.Serializable;
006import java.math.BigDecimal;
007import java.util.List;
008import java.util.Optional;
009import java.util.function.Consumer;
010import java.util.function.Predicate;
011
012/**
013 * Query object for performing native SQL queries that return SqlRow or directly read
014 * ResultSet using a RowMapper.
015 * <p>
016 * The returned SqlRow objects are similar to a LinkedHashMap with some type
017 * conversion support added.
018 * </p>
019 * <p>
020 * Refer to {@link DtoQuery} for native sql queries returning DTO beans.
021 * </p>
022 * <p>
023 * Refer to {@link Database#findNative(Class, String)} for native sql queries returning entity beans.
024 * </p>
025 *
026 * <pre>{@code
027 *
028 *   // example using named parameters
029 *
030 *   String sql = "select id, name from customer where name like :name and status_code = :status";
031 *
032 *   List<SqlRow> list =
033 *     DB.sqlQuery(sql)
034 *       .setParameter("name", "Acme%")
035 *       .setParameter("status", "ACTIVE")
036 *       .findList();
037 *
038 * }</pre>
039 */
040public interface SqlQuery extends Serializable {
041
042  /**
043   * Execute the query returning a list.
044   */
045  @Nonnull
046  List<SqlRow> findList();
047
048  /**
049   * Execute the SqlQuery iterating a row at a time.
050   * <p>
051   * This streaming type query is useful for large query execution as only 1 row needs to be held in memory.
052   * </p>
053   */
054  void findEach(Consumer<SqlRow> consumer);
055
056  /**
057   * Execute the SqlQuery iterating a row at a time with the ability to stop consuming part way through.
058   * <p>
059   * Returning false after processing a row stops the iteration through the query results.
060   * </p>
061   * <p>
062   * This streaming type query is useful for large query execution as only 1 row needs to be held in memory.
063   * </p>
064   */
065  void findEachWhile(Predicate<SqlRow> consumer);
066
067  /**
068   * Execute the query returning a single row or null.
069   * <p>
070   * If this query finds 2 or more rows then it will throw a
071   * PersistenceException.
072   * </p>
073   */
074  @Nullable
075  SqlRow findOne();
076
077  /**
078   * Deprecated migrate to use {@link #mapTo(RowMapper)}
079   */
080  @Deprecated
081  <T> T findOne(RowMapper<T> mapper);
082
083  /**
084   * Deprecated migrate to use {@link #mapTo(RowMapper)}
085   */
086  @Deprecated
087  <T> List<T> findList(RowMapper<T> mapper);
088
089  /**
090   * Execute the query reading each row from ResultSet using the RowConsumer.
091   * <p>
092   * This provides a low level option that reads directly from the JDBC ResultSet
093   * and is good for processing very large results where (unlike findList) we don't
094   * hold all the results in memory but instead can process row by row.
095   * </p>
096   *
097   * <pre>{@code
098   *
099   *  String sql = "select id, name, status from customer order by name desc";
100   *
101   *  DB.sqlQuery(sql)
102   *    .findEachRow((resultSet, rowNum) -> {
103   *
104   *      // read directly from ResultSet
105   *
106   *      long id = resultSet.getLong(1);
107   *      String name = resultSet.getString(2);
108   *
109   *      // do something interesting with the data
110   *
111   *    });
112   *
113   * }</pre>
114   *
115   * @param consumer Used to read and process each ResultSet row.
116   */
117  void findEachRow(RowConsumer consumer);
118
119  /**
120   * Execute the query returning an optional row.
121   */
122  @Nonnull
123  Optional<SqlRow> findOneOrEmpty();
124
125  /**
126   * Deprecated - migrate to <code>.mapToScalar(attributeType).findOne()</code>.
127   * <pre>{@code
128   *
129   *    .mapToScalar(BigDecimal.class)
130   *    .findOne();
131   * }
132   */
133  @Deprecated
134  <T> T findSingleAttribute(Class<T> attributeType);
135
136  /**
137   * Deprecated - migrate to <code>.mapToScalar(BigDecimal.class).findOne()</code>.
138   * <pre>{@code
139   *
140   *    .mapToScalar(BigDecimal.class)
141   *    .findOne();
142   * }
143   */
144  @Deprecated
145  BigDecimal findSingleDecimal();
146
147  /**
148   * Deprecated - migrate to <code>.mapToScalar(Long.class).findOne()</code>.
149   * <pre>{@code
150   *
151   *    .mapToScalar(Long.class)
152   *    .findOne();
153   * }
154   */
155  @Deprecated
156  Long findSingleLong();
157
158  /**
159   * Deprecated - migrate to <code>.mapToScalar(Long.class).findList()</code>.
160   * <pre>{@code
161   *
162   *    .mapToScalar(Long.class)
163   *    .findList();
164   * }
165   */
166  @Deprecated
167  <T> List<T> findSingleAttributeList(Class<T> attributeType);
168
169  /**
170   * Set one of more positioned parameters.
171   * <p>
172   * This is a convenient alternative to multiple calls to {@link #setParameter(Object)}.
173   *
174   * <pre>{@code
175   *
176   *   String sql = "select id, name from customer where name like ? and status = ?";
177   *
178   *   List<SqlRow> list =
179   *     DB.sqlQuery(sql)
180   *       .setParameters("Rob", Status.NEW)
181   *       .findList();
182   *
183   *
184   *   // effectively the same as ...
185   *
186   *       .setParameter("Rob")
187   *       .setParameter("Status.NEW)
188   *
189   *   // and ...
190   *
191   *       .setParameter(1, "Rob")
192   *       .setParameter(2, "Status.NEW)
193   *
194   * }</pre>
195   */
196  SqlQuery setParameters(Object... values);
197
198  /**
199   * Deprecated migrate to setParameters(Object... values)
200   */
201  @Deprecated
202  SqlQuery setParams(Object... values);
203
204  /**
205   * Set the next bind parameter by position.
206   * <pre>{@code
207   *
208   *   String sql = "select id, name from customer where name like ? and status = ?";
209   *
210   *   List<SqlRow> list =
211   *     DB.sqlQuery(sql)
212   *       .setParameter("Rob")
213   *       .setParameter("Status.NEW)
214   *       .findList();
215   *
216   *   // the same as ...
217   *
218   *       .setParameters("Rob", Status.NEW)
219   *
220   *   // and ...
221   *
222   *       .setParameter(1, "Rob")
223   *       .setParameter(2, "Status.NEW)
224   *
225   * }</pre>
226   *
227   * @param value The value to bind
228   */
229  SqlQuery setParameter(Object value);
230
231  /**
232   * Bind the parameter by its index position (1 based like JDBC).
233   */
234  SqlQuery setParameter(int position, Object value);
235
236  /**
237   * Bind the named parameter value.
238   */
239  SqlQuery setParameter(String name, Object value);
240
241  /**
242   * Set the index of the first row of the results to return.
243   */
244  SqlQuery setFirstRow(int firstRow);
245
246  /**
247   * Set the maximum number of query results to return.
248   */
249  SqlQuery setMaxRows(int maxRows);
250
251  /**
252   * Set a timeout on this query.
253   * <p>
254   * This will typically result in a call to setQueryTimeout() on a
255   * preparedStatement. If the timeout occurs an exception will be thrown - this
256   * will be a SQLException wrapped up in a PersistenceException.
257   * </p>
258   *
259   * @param secs the query timeout limit in seconds. Zero means there is no limit.
260   */
261  SqlQuery setTimeout(int secs);
262
263  /**
264   * Set a label that can be put on performance metrics that are collected.
265   */
266  SqlQuery setLabel(String label);
267
268  /**
269   * A hint which for JDBC translates to the Statement.fetchSize().
270   * <p>
271   * Gives the JDBC driver a hint as to the number of rows that should be
272   * fetched from the database when more rows are needed for ResultSet.
273   * </p>
274   */
275  SqlQuery setBufferFetchSizeHint(int bufferFetchSizeHint);
276
277  /**
278   * The query result maps to a single scalar value like Long, BigDecimal,
279   * String, UUID, OffsetDateTime etc.
280   * <p>
281   * Any scalar type Ebean is aware of can be used including java time
282   * types like Instant, LocalDate, OffsetDateTime, UUID, Inet, Cdir etc.
283   *
284   * <pre>{@code
285   *
286   *   String sql = " select min(updtime) from o_order_detail " +
287   *                " where unit_price > ? and updtime is not null ";
288   *
289   *   OffsetDateTime minCreated = DB.sqlQuery(sql)
290   *     .setParameter(42)
291   *     .mapToScalar(OffsetDateTime.class)
292   *     .findOne();
293   *
294   * }</pre>
295   *
296   * @param attributeType The type the result is returned as
297   * @return The query to execute via findOne() findList() etc
298   */
299  <T> TypeQuery<T> mapToScalar(Class<T> attributeType);
300
301  /**
302   * Use a RowMapper to map the result to beans.
303   *
304   * @param mapper Maps rows to beans
305   * @param <T>    The type of beans mapped to
306   * @return The query to execute by findOne() findList() etc
307   */
308  <T> TypeQuery<T> mapTo(RowMapper<T> mapper);
309
310  /**
311   * Query mapping to single scalar values.
312   *
313   * @param <T> The type of the scalar values
314   */
315  interface TypeQuery<T> {
316
317    /**
318     * Return the single value.
319     */
320    T findOne();
321
322    /**
323     * Return the single value that is optional.
324     */
325    Optional<T> findOneOrEmpty();
326
327    /**
328     * Return the list of values.
329     */
330    List<T> findList();
331  }
332}