1 /*
2 * Copyright 2004-2007 Brian McCallister
3 *
4 * Licensed under the Apache License, Version 2.0 (the "License");
5 * you may not use this file except in compliance with the License.
6 * You may obtain a copy of the License at
7 *
8 * http://www.apache.org/licenses/LICENSE-2.0
9 *
10 * Unless required by applicable law or agreed to in writing, software
11 * distributed under the License is distributed on an "AS IS" BASIS,
12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13 * See the License for the specific language governing permissions and
14 * limitations under the License.
15 */
16
17 package org.skife.jdbi.v2;
18
19 import org.skife.jdbi.v2.exceptions.ResultSetException;
20 import org.skife.jdbi.v2.exceptions.UnableToCreateStatementException;
21 import org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException;
22 import org.skife.jdbi.v2.tweak.ResultSetMapper;
23 import org.skife.jdbi.v2.tweak.SQLLog;
24 import org.skife.jdbi.v2.tweak.StatementBuilder;
25 import org.skife.jdbi.v2.tweak.StatementCustomizer;
26 import org.skife.jdbi.v2.tweak.StatementLocator;
27 import org.skife.jdbi.v2.tweak.StatementRewriter;
28
29 import java.sql.Connection;
30 import java.sql.PreparedStatement;
31 import java.sql.ResultSet;
32 import java.sql.SQLException;
33 import java.sql.Statement;
34 import java.util.ArrayList;
35 import java.util.Collection;
36 import java.util.Collections;
37 import java.util.List;
38 import java.util.concurrent.atomic.AtomicReference;
39
40 /**
41 * Statement prviding convenience result handling for SQL queries.
42 */
43 public class Query<ResultType> extends SQLStatement<Query<ResultType>> implements Iterable<ResultType>
44 {
45 private final ResultSetMapper<ResultType> mapper;
46
47 Query(Binding params,
48 ResultSetMapper<ResultType> mapper,
49 StatementLocator locator,
50 StatementRewriter statementRewriter,
51 Connection connection,
52 StatementBuilder cache,
53 String sql,
54 StatementContext ctx,
55 SQLLog log,
56 TimingCollector timingCollector,
57 Collection<StatementCustomizer> customizers)
58 {
59 super(params, locator, statementRewriter, connection, cache, sql, ctx, log, timingCollector, customizers);
60 this.mapper = mapper;
61 }
62
63 /**
64 * Executes the select
65 * <p/>
66 * Will eagerly load all results
67 *
68 * @throws UnableToCreateStatementException
69 * if there is an error creating the statement
70 * @throws UnableToExecuteStatementException
71 * if there is an error executing the statement
72 * @throws ResultSetException if there is an error dealing with the result set
73 */
74 public List<ResultType> list()
75 {
76 return this.internalExecute(QueryPreperator.NO_OP, new QueryResultMunger<List<ResultType>>()
77 {
78 public List<ResultType> munge(Statement stmt) throws SQLException
79 {
80 ResultSet rs = stmt.getResultSet();
81 List<ResultType> result_list = new ArrayList<ResultType>();
82 int index = 0;
83 while (rs.next()) {
84 result_list.add(mapper.map(index++, rs, getContext()));
85 }
86 return result_list;
87 }
88 }, QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY);
89 }
90
91 /**
92 * Executes the select
93 * <p/>
94 * Will eagerly load all results up to a maximum of <code>maxRows</code>
95 *
96 * @param maxRows The maximum number of results to include in the result, any
97 * rows in the result set beyond this number will be ignored.
98 *
99 * @throws UnableToCreateStatementException
100 * if there is an error creating the statement
101 * @throws UnableToExecuteStatementException
102 * if there is an error executing the statement
103 * @throws ResultSetException if there is an error dealing with the result set
104 */
105 public List<ResultType> list(final int maxRows)
106 {
107 return this.internalExecute(QueryPreperator.NO_OP, new QueryResultMunger<List<ResultType>>()
108 {
109 public List<ResultType> munge(Statement stmt) throws SQLException
110 {
111 ResultSet rs = stmt.getResultSet();
112 List<ResultType> result_list = new ArrayList<ResultType>();
113 int index = 0;
114 while (rs.next() && index < maxRows) {
115 result_list.add(mapper.map(index++, rs, getContext()));
116 }
117 return result_list;
118 }
119 }, QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY);
120 }
121
122 /**
123 * Used to execute the query and traverse the result set with a accumulator.
124 * <a href="http://en.wikipedia.org/wiki/Fold_(higher-order_function)">Folding</a> over the
125 * result involves invoking a callback for each row, passing into the callback the return value
126 * from the previous function invocation.
127 *
128 * @param accumulator The initial accumulator value
129 * @param folder Defines the function which will fold over the result set.
130 *
131 * @return The return value from the last invocation of {@link Folder#fold(Object, java.sql.ResultSet)}
132 *
133 * @see org.skife.jdbi.v2.Folder
134 */
135 public <AccumulatorType> AccumulatorType fold(AccumulatorType accumulator, final Folder2<AccumulatorType> folder)
136 {
137 final AtomicReference<AccumulatorType> acc = new AtomicReference<AccumulatorType>(accumulator);
138
139 this.internalExecute(QueryPreperator.NO_OP, new QueryResultMunger<Void>()
140 {
141 public Void munge(Statement stmt) throws SQLException
142 {
143 ResultSet rs = stmt.getResultSet();
144 while (rs.next()) {
145 acc.set(folder.fold(acc.get(), rs, getContext()));
146 }
147 return null;
148 }
149 }, QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY);
150 return acc.get();
151 }
152
153 /**
154 * Used to execute the query and traverse the result set with a accumulator.
155 * <a href="http://en.wikipedia.org/wiki/Fold_(higher-order_function)">Folding</a> over the
156 * result involves invoking a callback for each row, passing into the callback the return value
157 * from the previous function invocation.
158 *
159 * @param accumulator The initial accumulator value
160 * @param folder Defines the function which will fold over the result set.
161 *
162 * @return The return value from the last invocation of {@link Folder#fold(Object, java.sql.ResultSet)}
163 *
164 * @see org.skife.jdbi.v2.Folder
165 * @deprecated Use {@link Query#fold(Object, Folder2)}
166 */
167 public <AccumulatorType> AccumulatorType fold(AccumulatorType accumulator, final Folder<AccumulatorType> folder)
168 {
169 final AtomicReference<AccumulatorType> acc = new AtomicReference<AccumulatorType>(accumulator);
170
171 this.internalExecute(QueryPreperator.NO_OP, new QueryResultMunger<Void>()
172 {
173 public Void munge(Statement stmt) throws SQLException
174 {
175 ResultSet rs = stmt.getResultSet();
176 while (rs.next()) {
177 acc.set(folder.fold(acc.get(), rs));
178 }
179 return null;
180 }
181 }, QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY);
182 return acc.get();
183 }
184
185 /**
186 * Obtain a forward-only result set iterator. Note that you must explicitely close
187 * the iterator to close the underlying resources.
188 */
189 public ResultIterator<ResultType> iterator()
190 {
191 /**
192 * Okay, so this is a bit dodgy. It relies on the internal behavior so beware :-)
193 *
194 * Basically, the cleaner will be called right after execution and will *not* do anything
195 * except store the values. When the iterator is closed, it will be called again and will
196 * close the stored values
197 */
198 final QueryPostMungeCleanup cleaner = new QueryPostMungeCleanup()
199 {
200 private boolean skipNextClose = true;
201 private SQLStatement<?> query;
202 private Statement stmt;
203 private ResultSet rs;
204
205 public void cleanup(SQLStatement<?> query, Statement stmt, ResultSet rs)
206 {
207 if (skipNextClose) {
208 this.query = query;
209 this.stmt = stmt;
210 this.rs = rs;
211 skipNextClose = false;
212 }
213 else {
214 QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY.cleanup(this.query, this.stmt, this.rs);
215 }
216 }
217 };
218
219 return this.internalExecute(QueryPreperator.NO_OP, new QueryResultMunger<ResultIterator<ResultType>>()
220 {
221 public ResultIterator<ResultType> munge(Statement results) throws SQLException
222 {
223
224 return new ResultSetResultIterator<ResultType>(mapper,
225 cleaner,
226 results.getResultSet(),
227 getContext());
228 }
229 }, cleaner);
230 }
231
232 /**
233 * Executes the select.
234 * <p/>
235 * Specifies a maximum of one result on the JDBC statement, and map that one result
236 * as the return value, or return null if there is nothing in the results
237 *
238 * @return first result, mapped, or null if there is no first result
239 */
240 public ResultType first()
241 {
242 return this.internalExecute(QueryPreperator.MAX_ROWS_ONE, new QueryResultMunger<ResultType>()
243 {
244 public final ResultType munge(final Statement stt) throws SQLException
245 {
246 ResultSet rs = stt.getResultSet();
247 if (rs.next()) {
248 return mapper.map(0, rs, getContext());
249 }
250 else {
251 // no result matches
252 return null;
253 }
254 }
255 }, QueryPostMungeCleanup.CLOSE_RESOURCES_QUIETLY);
256 }
257
258 /**
259 * Provide basic JavaBean mapping capabilities. Will instantiate an instance of resultType
260 * for each row and set the JavaBean properties which match fields in the result set.
261 *
262 * @param resultType JavaBean class to map result set fields into the properties of, by name
263 *
264 * @return a Query which provides the bean property mapping
265 */
266 public <Type> Query<Type> map(Class<Type> resultType)
267 {
268 return this.map(new BeanMapper<Type>(resultType));
269 }
270
271 public <T> Query<T> map(ResultSetMapper<T> mapper)
272 {
273 return new Query<T>(getParameters(),
274 mapper,
275 getStatementLocator(),
276 getRewriter(),
277 getConnection(),
278 getStatementBuilder(),
279 getSql(),
280 getContext(),
281 getLog(),
282 getTimingCollector(),
283 getStatementCustomizers());
284 }
285
286 /**
287 * Specify the fetch size for the query. This should cause the results to be
288 * fetched from the underlying RDBMS in groups of rows equal to the number passed.
289 * This is useful for doing chunked streaming of results when exhausting memory
290 * could be a problem.
291 *
292 * @param i the number of rows to fetch in a bunch
293 *
294 * @return the modified query
295 */
296 public Query<ResultType> setFetchSize(final int i)
297 {
298 this.addStatementCustomizer(new StatementCustomizer()
299 {
300 public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
301 {
302 stmt.setFetchSize(i);
303 }
304
305 public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
306 {
307 }
308 });
309 return this;
310 }
311
312 /**
313 * Specify the maimum number of rows the query is to return. This uses the underlying JDBC
314 * {@link Statement#setMaxRows(int)}}.
315 *
316 * @param i maximum number of rows to return
317 *
318 * @return modified query
319 */
320 public Query<ResultType> setMaxRows(final int i)
321 {
322 this.addStatementCustomizer(new StatementCustomizer()
323 {
324 public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
325 {
326 stmt.setMaxRows(i);
327 }
328
329 public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
330 {
331 }
332 });
333 return this;
334 }
335
336 /**
337 * Specify the maimum field size in the result set. This uses the underlying JDBC
338 * {@link Statement#setMaxFieldSize(int)}
339 *
340 * @param i maximum field size
341 *
342 * @return modified query
343 */
344 public Query<ResultType> setMaxFieldSize(final int i)
345 {
346 this.addStatementCustomizer(new StatementCustomizer()
347 {
348 public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
349 {
350 stmt.setMaxFieldSize(i);
351 }
352
353 public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
354 {
355 }
356
357 });
358 return this;
359 }
360
361 /**
362 * Specify that the fetch order should be reversed, uses the underlying
363 * {@link Statement#setFetchDirection(int)}
364 *
365 * @return the modified query
366 */
367 public Query<ResultType> fetchReverse()
368 {
369 this.addStatementCustomizer(new StatementCustomizer()
370 {
371 public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
372 {
373 stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
374 }
375
376 public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
377 {
378 }
379
380 });
381 return this;
382 }
383
384 /**
385 * Specify that the fetch order should be forward, uses the underlying
386 * {@link Statement#setFetchDirection(int)}
387 *
388 * @return the modified query
389 */
390 public Query<ResultType> fetchForward()
391 {
392 this.addStatementCustomizer(new StatementCustomizer()
393 {
394 public void beforeExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
395 {
396 stmt.setFetchDirection(ResultSet.FETCH_FORWARD);
397 }
398
399 public void afterExecution(PreparedStatement stmt, StatementContext ctx) throws SQLException
400 {
401 }
402
403 });
404 return this;
405 }
406 }