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