001/*
002 * Copyright 2002-2012 the original author or authors.
003 *
004 * Licensed under the Apache License, Version 2.0 (the "License");
005 * you may not use this file except in compliance with the License.
006 * You may obtain a copy of the License at
007 *
008 *      https://www.apache.org/licenses/LICENSE-2.0
009 *
010 * Unless required by applicable law or agreed to in writing, software
011 * distributed under the License is distributed on an "AS IS" BASIS,
012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013 * See the License for the specific language governing permissions and
014 * limitations under the License.
015 */
016
017package org.springframework.jdbc.object;
018
019import java.util.List;
020import java.util.Map;
021import javax.sql.DataSource;
022
023import org.springframework.dao.DataAccessException;
024import org.springframework.dao.support.DataAccessUtils;
025import org.springframework.jdbc.core.RowMapper;
026import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
027import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
028import org.springframework.jdbc.core.namedparam.ParsedSql;
029
030/**
031 * Reusable operation object representing a SQL query.
032 *
033 * <p>Subclasses must implement the {@link #newRowMapper} method to provide
034 * an object that can extract the results of iterating over the
035 * {@code ResultSet} created during the execution of the query.
036 *
037 * <p>This class provides a number of public {@code execute} methods that are
038 * analogous to the different convenient JDO query execute methods. Subclasses
039 * can either rely on one of these inherited methods, or can add their own
040 * custom execution methods, with meaningful names and typed parameters
041 * (definitely a best practice). Each custom query method will invoke one of
042 * this class's untyped query methods.
043 *
044 * <p>Like all {@code RdbmsOperation} classes that ship with the Spring
045 * Framework, {@code SqlQuery} instances are thread-safe after their
046 * initialization is complete. That is, after they are constructed and configured
047 * via their setter methods, they can be used safely from multiple threads.
048 *
049 * @author Rod Johnson
050 * @author Juergen Hoeller
051 * @author Thomas Risberg
052 * @see SqlUpdate
053 */
054public abstract class SqlQuery<T> extends SqlOperation {
055
056        /** The number of rows to expect; if 0, unknown. */
057        private int rowsExpected = 0;
058
059
060        /**
061         * Constructor to allow use as a JavaBean.
062         * <p>The {@code DataSource} and SQL must be supplied before
063         * compilation and use.
064         */
065        public SqlQuery() {
066        }
067
068        /**
069         * Convenient constructor with a {@code DataSource} and SQL string.
070         * @param ds the {@code DataSource} to use to get connections
071         * @param sql the SQL to execute; SQL can also be supplied at runtime
072         * by overriding the {@link #getSql()} method.
073         */
074        public SqlQuery(DataSource ds, String sql) {
075                setDataSource(ds);
076                setSql(sql);
077        }
078
079
080        /**
081         * Set the number of rows expected.
082         * <p>This can be used to ensure efficient storage of results. The
083         * default behavior is not to expect any specific number of rows.
084         */
085        public void setRowsExpected(int rowsExpected) {
086                this.rowsExpected = rowsExpected;
087        }
088
089        /**
090         * Get the number of rows expected.
091         */
092        public int getRowsExpected() {
093                return this.rowsExpected;
094        }
095
096
097        /**
098         * Central execution method. All un-named parameter execution goes through this method.
099         * @param params parameters, similar to JDO query parameters.
100         * Primitive parameters must be represented by their Object wrapper type.
101         * The ordering of parameters is significant.
102         * @param context contextual information passed to the {@code mapRow}
103         * callback method. The JDBC operation itself doesn't rely on this parameter,
104         * but it can be useful for creating the objects of the result list.
105         * @return a List of objects, one per row of the ResultSet. Normally all these
106         * will be of the same class, although it is possible to use different types.
107         */
108        public List<T> execute(Object[] params, Map<?, ?> context) throws DataAccessException {
109                validateParameters(params);
110                RowMapper<T> rowMapper = newRowMapper(params, context);
111                return getJdbcTemplate().query(newPreparedStatementCreator(params), rowMapper);
112        }
113
114        /**
115         * Convenient method to execute without context.
116         * @param params parameters for the query. Primitive parameters must
117         * be represented by their Object wrapper type. The ordering of parameters is
118         * significant.
119         */
120        public List<T> execute(Object... params) throws DataAccessException {
121                return execute(params, null);
122        }
123
124        /**
125         * Convenient method to execute without parameters.
126         * @param context the contextual information for object creation
127         */
128        public List<T> execute(Map<?, ?> context) throws DataAccessException {
129                return execute((Object[]) null, context);
130        }
131
132        /**
133         * Convenient method to execute without parameters nor context.
134         */
135        public List<T> execute() throws DataAccessException {
136                return execute((Object[]) null);
137        }
138
139        /**
140         * Convenient method to execute with a single int parameter and context.
141         * @param p1 single int parameter
142         * @param context the contextual information for object creation
143         */
144        public List<T> execute(int p1, Map<?, ?> context) throws DataAccessException {
145                return execute(new Object[] {p1}, context);
146        }
147
148        /**
149         * Convenient method to execute with a single int parameter.
150         * @param p1 single int parameter
151         */
152        public List<T> execute(int p1) throws DataAccessException {
153                return execute(p1, null);
154        }
155
156        /**
157         * Convenient method to execute with two int parameters and context.
158         * @param p1 first int parameter
159         * @param p2 second int parameter
160         * @param context the contextual information for object creation
161         */
162        public List<T> execute(int p1, int p2, Map<?, ?> context) throws DataAccessException {
163                return execute(new Object[] {p1, p2}, context);
164        }
165
166        /**
167         * Convenient method to execute with two int parameters.
168         * @param p1 first int parameter
169         * @param p2 second int parameter
170         */
171        public List<T> execute(int p1, int p2) throws DataAccessException {
172                return execute(p1, p2, null);
173        }
174
175        /**
176         * Convenient method to execute with a single long parameter and context.
177         * @param p1 single long parameter
178         * @param context the contextual information for object creation
179         */
180        public List<T> execute(long p1, Map<?, ?> context) throws DataAccessException {
181                return execute(new Object[] {p1}, context);
182        }
183
184        /**
185         * Convenient method to execute with a single long parameter.
186         * @param p1 single long parameter
187         */
188        public List<T> execute(long p1) throws DataAccessException {
189                return execute(p1, null);
190        }
191
192        /**
193         * Convenient method to execute with a single String parameter and context.
194         * @param p1 single String parameter
195         * @param context the contextual information for object creation
196         */
197        public List<T> execute(String p1, Map<?, ?> context) throws DataAccessException {
198                return execute(new Object[] {p1}, context);
199        }
200
201        /**
202         * Convenient method to execute with a single String parameter.
203         * @param p1 single String parameter
204         */
205        public List<T> execute(String p1) throws DataAccessException {
206                return execute(p1, null);
207        }
208
209        /**
210         * Central execution method. All named parameter execution goes through this method.
211         * @param paramMap parameters associated with the name specified while declaring
212         * the SqlParameters. Primitive parameters must be represented by their Object wrapper
213         * type. The ordering of parameters is not significant since they are supplied in a
214         * SqlParameterMap which is an implementation of the Map interface.
215         * @param context contextual information passed to the {@code mapRow}
216         * callback method. The JDBC operation itself doesn't rely on this parameter,
217         * but it can be useful for creating the objects of the result list.
218         * @return a List of objects, one per row of the ResultSet. Normally all these
219         * will be of the same class, although it is possible to use different types.
220         */
221        public List<T> executeByNamedParam(Map<String, ?> paramMap, Map<?, ?> context) throws DataAccessException {
222                validateNamedParameters(paramMap);
223                ParsedSql parsedSql = getParsedSql();
224                MapSqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
225                String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
226                Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, getDeclaredParameters());
227                RowMapper<T> rowMapper = newRowMapper(params, context);
228                return getJdbcTemplate().query(newPreparedStatementCreator(sqlToUse, params), rowMapper);
229        }
230
231        /**
232         * Convenient method to execute without context.
233         * @param paramMap parameters associated with the name specified while declaring
234         * the SqlParameters. Primitive parameters must be represented by their Object wrapper
235         * type. The ordering of parameters is not significant.
236         */
237        public List<T> executeByNamedParam(Map<String, ?> paramMap) throws DataAccessException {
238                return executeByNamedParam(paramMap, null);
239        }
240
241
242        /**
243         * Generic object finder method, used by all other {@code findObject} methods.
244         * Object finder methods are like EJB entity bean finders, in that it is
245         * considered an error if they return more than one result.
246         * @return the result object, or {@code null} if not found. Subclasses may
247         * choose to treat this as an error and throw an exception.
248         * @see org.springframework.dao.support.DataAccessUtils#singleResult
249         */
250        public T findObject(Object[] params, Map<?, ?> context) throws DataAccessException {
251                List<T> results = execute(params, context);
252                return DataAccessUtils.singleResult(results);
253        }
254
255        /**
256         * Convenient method to find a single object without context.
257         */
258        public T findObject(Object... params) throws DataAccessException {
259                return findObject(params, null);
260        }
261
262        /**
263         * Convenient method to find a single object given a single int parameter
264         * and a context.
265         */
266        public T findObject(int p1, Map<?, ?> context) throws DataAccessException {
267                return findObject(new Object[] {p1}, context);
268        }
269
270        /**
271         * Convenient method to find a single object given a single int parameter.
272         */
273        public T findObject(int p1) throws DataAccessException {
274                return findObject(p1, null);
275        }
276
277        /**
278         * Convenient method to find a single object given two int parameters
279         * and a context.
280         */
281        public T findObject(int p1, int p2, Map<?, ?> context) throws DataAccessException {
282                return findObject(new Object[] {p1, p2}, context);
283        }
284
285        /**
286         * Convenient method to find a single object given two int parameters.
287         */
288        public T findObject(int p1, int p2) throws DataAccessException {
289                return findObject(p1, p2, null);
290        }
291
292        /**
293         * Convenient method to find a single object given a single long parameter
294         * and a context.
295         */
296        public T findObject(long p1, Map<?, ?> context) throws DataAccessException {
297                return findObject(new Object[] {p1}, context);
298        }
299
300        /**
301         * Convenient method to find a single object given a single long parameter.
302         */
303        public T findObject(long p1) throws DataAccessException {
304                return findObject(p1, null);
305        }
306
307        /**
308         * Convenient method to find a single object given a single String parameter
309         * and a context.
310         */
311        public T findObject(String p1, Map<?, ?> context) throws DataAccessException {
312                return findObject(new Object[] {p1}, context);
313        }
314
315        /**
316         * Convenient method to find a single object given a single String parameter.
317         */
318        public T findObject(String p1) throws DataAccessException {
319                return findObject(p1, null);
320        }
321
322        /**
323         * Generic object finder method for named parameters.
324         * @param paramMap Map of parameter name to parameter object,
325         * matching named parameters specified in the SQL statement.
326         * Ordering is not significant.
327         * @param context contextual information passed to the {@code mapRow}
328         * callback method. The JDBC operation itself doesn't rely on this parameter,
329         * but it can be useful for creating the objects of the result list.
330         * @return a List of objects, one per row of the ResultSet. Normally all these
331         * will be of the same class, although it is possible to use different types.
332         */
333        public T findObjectByNamedParam(Map<String, ?> paramMap, Map<?, ?> context) throws DataAccessException {
334                List<T> results = executeByNamedParam(paramMap, context);
335                return DataAccessUtils.singleResult(results);
336        }
337
338        /**
339         * Convenient method to execute without context.
340         * @param paramMap Map of parameter name to parameter object,
341         * matching named parameters specified in the SQL statement.
342         * Ordering is not significant.
343         */
344        public T findObjectByNamedParam(Map<String, ?> paramMap) throws DataAccessException {
345                return findObjectByNamedParam(paramMap, null);
346        }
347
348
349        /**
350         * Subclasses must implement this method to extract an object per row, to be
351         * returned by the <cod>execute</code> method as an aggregated {@link List}.
352         * @param parameters the parameters to the {@code execute()} method,
353         * in case subclass is interested; may be {@code null} if there
354         * were no parameters.
355         * @param context contextual information passed to the {@code mapRow}
356         * callback method. The JDBC operation itself doesn't rely on this parameter,
357         * but it can be useful for creating the objects of the result list.
358         * @see #execute
359         */
360        protected abstract RowMapper<T> newRowMapper(Object[] parameters, Map<?, ?> context);
361
362}