001package com.randomnoun.common.spring; 002 003import java.util.Collection; 004import java.util.List; 005import java.util.Map; 006 007import javax.sql.DataSource; 008 009import org.springframework.dao.DataAccessException; 010import org.springframework.jdbc.core.BatchPreparedStatementSetter; 011import org.springframework.jdbc.core.CallableStatementCallback; 012import org.springframework.jdbc.core.CallableStatementCreator; 013import org.springframework.jdbc.core.ConnectionCallback; 014import org.springframework.jdbc.core.JdbcTemplate; 015import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter; 016import org.springframework.jdbc.core.PreparedStatementCallback; 017import org.springframework.jdbc.core.PreparedStatementCreator; 018import org.springframework.jdbc.core.PreparedStatementSetter; 019import org.springframework.jdbc.core.ResultSetExtractor; 020import org.springframework.jdbc.core.RowCallbackHandler; 021import org.springframework.jdbc.core.RowMapper; 022import org.springframework.jdbc.core.SqlParameter; 023import org.springframework.jdbc.core.StatementCallback; 024import org.springframework.jdbc.support.KeyHolder; 025import org.springframework.jdbc.support.SQLExceptionTranslator; 026import org.springframework.jdbc.support.rowset.SqlRowSet; 027 028import com.randomnoun.common.db.SqlWithArguments; 029 030/** A JdbcTemplate that can take SqlWithArguments objects as parameters. 031 * 032 * <p>This provides a bit of syntactic sugar so that you can write 033 * 034 * <pre>jt.update(sqlWithArgs);</pre> 035 * 036 * instead of 037 * 038 * <pre>jt.update(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes());</pre> 039 * 040 * <p>All JdbcTemplate methods that take <code>String sql, Object[] args, int[] argTypes</code> 041 * parameters have an additional SqlWithArguments equivalent. 042 * 043 * <p>This class will also recognise SelectFromResultSetExtractors and SelectFromRowMappers and will 044 * add the appropriate SELECTs and FROMs to the SQL before it is executed. 045 * 046 * @author knoxg 047 */ 048public class JdbcTemplateWithArguments extends JdbcTemplate { 049 050 JdbcTemplate jt; 051 public JdbcTemplateWithArguments(JdbcTemplate jt) { 052 if (jt instanceof JdbcTemplateWithArguments) { 053 throw new IllegalStateException("Cannot wrap a JdbcTemplateWithArguments with a JdbcTemplateWithArguments"); 054 } 055 this.jt = jt; 056 } 057 058 /** Adds 'SELECT' and 'FROM' clauses to the supplied SQL if the caller has also supplied a SelectFromResultSetExtractor */ 059 private <T> String getSelectFromSql(String sql, ResultSetExtractor<T> rse) { 060 if (rse instanceof SelectFromResultSetExtractor) { 061 SelectFromResultSetExtractor<T> sfrse = (SelectFromResultSetExtractor<T>) rse; 062 sql = "SELECT " + sfrse.getSelect() + " FROM " + sfrse.getFrom() + " " + sql; 063 } 064 return sql; 065 } 066 067 /** Adds 'SELECT' and 'FROM' clauses to the supplied SQL if the caller has also supplied a SelectFromRowMapper */ 068 private <T> String getSelectFromSql(String sql, RowMapper<T> rowMapper) { 069 if (rowMapper instanceof SelectFromRowMapper) { 070 SelectFromRowMapper<T> sfRowMapper = (SelectFromRowMapper<T>) rowMapper; 071 sql = "SELECT " + sfRowMapper.getSelect() + " FROM " + sfRowMapper.getFrom() + " " + sql; 072 } 073 return sql; 074 } 075 076 077 public void setDataSource(DataSource dataSource) { 078 jt.setDataSource(dataSource); 079 } 080 public int hashCode() { 081 return jt.hashCode(); 082 } 083 public DataSource getDataSource() { 084 return jt.getDataSource(); 085 } 086 public void setDatabaseProductName(String dbName) { 087 jt.setDatabaseProductName(dbName); 088 } 089 public void setExceptionTranslator(SQLExceptionTranslator exceptionTranslator) { 090 jt.setExceptionTranslator(exceptionTranslator); 091 } 092 public SQLExceptionTranslator getExceptionTranslator() { 093 return jt.getExceptionTranslator(); 094 } 095 public boolean equals(Object obj) { 096 return jt.equals(obj); 097 } 098 public void setLazyInit(boolean lazyInit) { 099 jt.setLazyInit(lazyInit); 100 } 101 public boolean isLazyInit() { 102 return jt.isLazyInit(); 103 } 104 public void afterPropertiesSet() { 105 jt.afterPropertiesSet(); 106 } 107 public void setIgnoreWarnings(boolean ignoreWarnings) { 108 jt.setIgnoreWarnings(ignoreWarnings); 109 } 110 public boolean isIgnoreWarnings() { 111 return jt.isIgnoreWarnings(); 112 } 113 public void setFetchSize(int fetchSize) { 114 jt.setFetchSize(fetchSize); 115 } 116 public int getFetchSize() { 117 return jt.getFetchSize(); 118 } 119 public void setMaxRows(int maxRows) { 120 jt.setMaxRows(maxRows); 121 } 122 public String toString() { 123 return jt.toString(); 124 } 125 public int getMaxRows() { 126 return jt.getMaxRows(); 127 } 128 public void setQueryTimeout(int queryTimeout) { 129 jt.setQueryTimeout(queryTimeout); 130 } 131 public int getQueryTimeout() { 132 return jt.getQueryTimeout(); 133 } 134 public void setSkipResultsProcessing(boolean skipResultsProcessing) { 135 jt.setSkipResultsProcessing(skipResultsProcessing); 136 } 137 public boolean isSkipResultsProcessing() { 138 return jt.isSkipResultsProcessing(); 139 } 140 public void setSkipUndeclaredResults(boolean skipUndeclaredResults) { 141 jt.setSkipUndeclaredResults(skipUndeclaredResults); 142 } 143 public boolean isSkipUndeclaredResults() { 144 return jt.isSkipUndeclaredResults(); 145 } 146 public void setResultsMapCaseInsensitive(boolean resultsMapCaseInsensitive) { 147 jt.setResultsMapCaseInsensitive(resultsMapCaseInsensitive); 148 } 149 public boolean isResultsMapCaseInsensitive() { 150 return jt.isResultsMapCaseInsensitive(); 151 } 152 public <T> T execute(ConnectionCallback<T> action) throws DataAccessException { 153 return jt.execute(action); 154 } 155 public <T> T execute(StatementCallback<T> action) throws DataAccessException { 156 return jt.execute(action); 157 } 158 public void execute(String sql) throws DataAccessException { 159 jt.execute(sql); 160 } 161 public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException { 162 sql = getSelectFromSql(sql, rse); 163 return jt.query(sql, rse); 164 } 165 public void query(String sql, RowCallbackHandler rch) throws DataAccessException { 166 jt.query(sql, rch); 167 } 168 public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException { 169 sql = getSelectFromSql(sql, rowMapper); 170 return jt.query(sql, rowMapper); 171 } 172 public Map<String, Object> queryForMap(String sql) throws DataAccessException { 173 return jt.queryForMap(sql); 174 } 175 public <T> T queryForObject(String sql, RowMapper<T> rowMapper) throws DataAccessException { 176 sql = getSelectFromSql(sql, rowMapper); 177 return jt.queryForObject(sql, rowMapper); 178 } 179 public <T> T queryForObject(String sql, Class<T> requiredType) throws DataAccessException { 180 return jt.queryForObject(sql, requiredType); 181 } 182 public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException { 183 return jt.queryForList(sql, elementType); 184 } 185 public List<Map<String, Object>> queryForList(String sql) throws DataAccessException { 186 return jt.queryForList(sql); 187 } 188 public SqlRowSet queryForRowSet(String sql) throws DataAccessException { 189 return jt.queryForRowSet(sql); 190 } 191 public int update(String sql) throws DataAccessException { 192 return jt.update(sql); 193 } 194 public int[] batchUpdate(String... sql) throws DataAccessException { 195 return jt.batchUpdate(sql); 196 } 197 public <T> T execute(PreparedStatementCreator psc, PreparedStatementCallback<T> action) throws DataAccessException { 198 return jt.execute(psc, action); 199 } 200 public <T> T execute(String sql, PreparedStatementCallback<T> action) throws DataAccessException { 201 return jt.execute(sql, action); 202 } 203 204 public <T> T query(PreparedStatementCreator psc, PreparedStatementSetter pss, ResultSetExtractor<T> rse) 205 throws DataAccessException { 206 return jt.query(psc, pss, rse); 207 } 208 public <T> T query(PreparedStatementCreator psc, ResultSetExtractor<T> rse) throws DataAccessException { 209 return jt.query(psc, rse); 210 } 211 public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) throws DataAccessException { 212 sql = getSelectFromSql(sql, rse); 213 return jt.query(sql, pss, rse); 214 } 215 216 /** Calls {@link JdbcTemplate#query(String, Object[], ResultSetExtractor)} 217 * @see JdbcTemplate#query(String, Object[], ResultSetExtractor) 218 */ 219 public <T> T query(SqlWithArguments sqlWithArgs, ResultSetExtractor<T> rse) 220 throws DataAccessException { 221 String sql = getSelectFromSql(sqlWithArgs.getSql(), rse); 222 return jt.query(sql, sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes(), rse); 223 } 224 public <T> T query(String sql, Object[] args, int[] argTypes, ResultSetExtractor<T> rse) 225 throws DataAccessException { 226 sql = getSelectFromSql(sql, rse); 227 return jt.query(sql, args, argTypes, rse); 228 } 229 public <T> T query(String sql, Object[] args, ResultSetExtractor<T> rse) throws DataAccessException { 230 sql = getSelectFromSql(sql, rse); 231 return jt.query(sql, args, rse); 232 } 233 public <T> T query(String sql, ResultSetExtractor<T> rse, Object... args) throws DataAccessException { 234 sql = getSelectFromSql(sql, rse); 235 return jt.query(sql, rse, args); 236 } 237 public void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException { 238 jt.query(psc, rch); 239 } 240 public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) throws DataAccessException { 241 jt.query(sql, pss, rch); 242 } 243 /** Calls {@link JdbcTemplate#query(String, Object[], int[], RowCallbackHandler)} 244 * @see JdbcTemplate#query(String, Object[], int[], RowCallbackHandler) 245 */ 246 public void query(SqlWithArguments sqlWithArgs, RowCallbackHandler rch) throws DataAccessException { 247 jt.query(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes(), rch); 248 } 249 public void query(String sql, Object[] args, int[] argTypes, RowCallbackHandler rch) throws DataAccessException { 250 jt.query(sql, args, argTypes, rch); 251 } 252 public void query(String sql, Object[] args, RowCallbackHandler rch) throws DataAccessException { 253 jt.query(sql, args, rch); 254 } 255 public void query(String sql, RowCallbackHandler rch, Object... args) throws DataAccessException { 256 jt.query(sql, rch, args); 257 } 258 public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException { 259 return jt.query(psc, rowMapper); 260 } 261 public <T> List<T> query(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) 262 throws DataAccessException { 263 sql = getSelectFromSql(sql, rowMapper); 264 return jt.query(sql, pss, rowMapper); 265 } 266 267 /** Calls {@link JdbcTemplate#query(String, Object[], RowMapper)} 268 * @see JdbcTemplate#query(String, Object[], RowMapper) */ 269 public <T> List<T> query(SqlWithArguments sqlWithArgs, RowMapper<T> rowMapper) 270 throws DataAccessException { 271 String sql = getSelectFromSql(sqlWithArgs.getSql(), rowMapper); 272 return jt.query(sql, sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes(), rowMapper); 273 } 274 public <T> List<T> query(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) 275 throws DataAccessException { 276 sql = getSelectFromSql(sql, rowMapper); 277 return jt.query(sql, args, argTypes, rowMapper); 278 } 279 public <T> List<T> query(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException { 280 sql = getSelectFromSql(sql, rowMapper); 281 return jt.query(sql, args, rowMapper); 282 } 283 public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException { 284 sql = getSelectFromSql(sql, rowMapper); 285 return jt.query(sql, rowMapper, args); 286 } 287 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) 288 throws DataAccessException { 289 sql = getSelectFromSql(sql, rowMapper); 290 return jt.queryForObject(sql, args, argTypes, rowMapper); 291 } 292 public <T> T queryForObject(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException { 293 sql = getSelectFromSql(sql, rowMapper); 294 return jt.queryForObject(sql, args, rowMapper); 295 } 296 public <T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException { 297 sql = getSelectFromSql(sql, rowMapper); 298 return jt.queryForObject(sql, rowMapper, args); 299 } 300 /** Calls {@link JdbcTemplate#queryForObject(String, Object[], int[], Class)} 301 * @see JdbcTemplate#queryForObject(String, Object[], int[], Class) 302 */ 303 public <T> T queryForObject(SqlWithArguments sqlWithArgs, Class<T> requiredType) 304 throws DataAccessException { 305 return jt.queryForObject(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes(), requiredType); 306 } 307 public <T> T queryForObject(String sql, Object[] args, int[] argTypes, Class<T> requiredType) 308 throws DataAccessException { 309 return jt.queryForObject(sql, args, argTypes, requiredType); 310 } 311 public <T> T queryForObject(String sql, Object[] args, Class<T> requiredType) throws DataAccessException { 312 return jt.queryForObject(sql, args, requiredType); 313 } 314 public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException { 315 return jt.queryForObject(sql, requiredType, args); 316 } 317 /** Calls {@link JdbcTemplate#queryForMap(String, Object[], int[])} 318 * @see JdbcTemplate#queryForMap(String, Object[], int[]) 319 */ 320 public Map<String, Object> queryForMap(SqlWithArguments sqlWithArgs) throws DataAccessException { 321 return jt.queryForMap(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes()); 322 } 323 public Map<String, Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException { 324 return jt.queryForMap(sql, args, argTypes); 325 } 326 public Map<String, Object> queryForMap(String sql, Object... args) throws DataAccessException { 327 return jt.queryForMap(sql, args); 328 } 329 /** Calls {@link JdbcTemplate#queryForList(String, Object[], int[], Class)} 330 * @see JdbcTemplate#queryForList(String, Object[], int[], Class)) 331 */ 332 public <T> List<T> queryForList(SqlWithArguments sqlWithArgs, Class<T> elementType) 333 throws DataAccessException { 334 return jt.queryForList(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes(), elementType); 335 } 336 public <T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) 337 throws DataAccessException { 338 return jt.queryForList(sql, args, argTypes, elementType); 339 } 340 public <T> List<T> queryForList(String sql, Object[] args, Class<T> elementType) throws DataAccessException { 341 return jt.queryForList(sql, args, elementType); 342 } 343 public <T> List<T> queryForList(String sql, Class<T> elementType, Object... args) throws DataAccessException { 344 return jt.queryForList(sql, elementType, args); 345 } 346 /** Calls {@link JdbcTemplate#queryForList(String, Object[], int[])} 347 * @see JdbcTemplate#queryForList(String, Object[], int[])} 348 */ 349 public List<Map<String, Object>> queryForList(SqlWithArguments sqlWithArgs) 350 throws DataAccessException { 351 return jt.queryForList(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes()); 352 } 353 public List<Map<String, Object>> queryForList(String sql, Object[] args, int[] argTypes) 354 throws DataAccessException { 355 return jt.queryForList(sql, args, argTypes); 356 } 357 public List<Map<String, Object>> queryForList(String sql, Object... args) throws DataAccessException { 358 return jt.queryForList(sql, args); 359 } 360 /** Calls {@link JdbcTemplate#queryForRowSet(String, Object[], int[])} 361 * @see JdbcTemplate#queryForRowSet(String, Object[], int[]) 362 */ 363 public SqlRowSet queryForRowSet(SqlWithArguments sqlWithArgs) throws DataAccessException { 364 return jt.queryForRowSet(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes()); 365 } 366 public SqlRowSet queryForRowSet(String sql, Object[] args, int[] argTypes) throws DataAccessException { 367 return jt.queryForRowSet(sql, args, argTypes); 368 } 369 public SqlRowSet queryForRowSet(String sql, Object... args) throws DataAccessException { 370 return jt.queryForRowSet(sql, args); 371 } 372 public int update(PreparedStatementCreator psc) throws DataAccessException { 373 return jt.update(psc); 374 } 375 public int update(PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException { 376 return jt.update(psc, generatedKeyHolder); 377 } 378 public int update(String sql, PreparedStatementSetter pss) throws DataAccessException { 379 return jt.update(sql, pss); 380 } 381 /** Calls {@link JdbcTemplate#update(String, Object[], int[])} 382 * @see JdbcTemplate#update(String, Object[], int[]) 383 */ 384 public int update(SqlWithArguments sqlWithArgs) throws DataAccessException { 385 return jt.update(sqlWithArgs.getSql(), sqlWithArgs.getArgs(), sqlWithArgs.getArgTypes()); 386 } 387 public int update(String sql, Object[] args, int[] argTypes) throws DataAccessException { 388 return jt.update(sql, args, argTypes); 389 } 390 public int update(String sql, Object... args) throws DataAccessException { 391 return jt.update(sql, args); 392 } 393 public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) throws DataAccessException { 394 return jt.batchUpdate(sql, pss); 395 } 396 public int[] batchUpdate(String sql, List<Object[]> batchArgs) throws DataAccessException { 397 return jt.batchUpdate(sql, batchArgs); 398 } 399 /* could supply a list of SqlWithArguments and assume the sql & types are identical for all, but 400 * probably safer just to leave this 401 public int[] batchUpdate(List<SqlWithArguments> batchSqlWithArgs) throws DataAccessException { 402 return jt.batchUpdate(sql, batchArgs, argTypes); 403 } 404 */ 405 public int[] batchUpdate(String sql, List<Object[]> batchArgs, int[] argTypes) throws DataAccessException { 406 return jt.batchUpdate(sql, batchArgs, argTypes); 407 } 408 public <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, 409 ParameterizedPreparedStatementSetter<T> pss) throws DataAccessException { 410 return jt.batchUpdate(sql, batchArgs, batchSize, pss); 411 } 412 public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException { 413 return jt.execute(csc, action); 414 } 415 public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException { 416 return jt.execute(callString, action); 417 } 418 public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters) 419 throws DataAccessException { 420 return jt.call(csc, declaredParameters); 421 } 422 423}