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}