001 package com.alexkasko.springjdbc.iterable;
002
003 import org.apache.commons.logging.Log;
004 import org.apache.commons.logging.LogFactory;
005 import org.springframework.dao.DataAccessException;
006 import org.springframework.jdbc.core.*;
007 import org.springframework.jdbc.datasource.DataSourceUtils;
008 import org.springframework.jdbc.support.JdbcUtils;
009 import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
010 import org.springframework.util.Assert;
011
012 import javax.sql.DataSource;
013 import java.sql.*;
014 import java.util.Map;
015
016 /**
017 * {@code JdbcTemplate} extension. All methods, that return {@code List}
018 * mirrored with {@code queryForIter} methods that return {@link CloseableIterator}.
019 *
020 * @author alexkasko
021 * Date: 11/7/12
022 */
023 public class IterableJdbcTemplate extends JdbcTemplate implements IterableJdbcOperations {
024 private static final Log logger = LogFactory.getLog(IterableJdbcTemplate.class);
025
026 /**
027 * Constructor
028 *
029 * @param dataSource data source
030 */
031 public IterableJdbcTemplate(DataSource dataSource) {
032 super(dataSource);
033 }
034
035 /**
036 * Constructor, takes {@code fetchSize}
037 *
038 * @param dataSource data source
039 * @param fetchSize <a href=http://static.springsource.org/spring/docs/3.0.x/api/org/springframework/jdbc/core/JdbcTemplate.html#setFetchSize%28int%29>fetchSize</a> property value
040 */
041 public IterableJdbcTemplate(DataSource dataSource, int fetchSize) {
042 this(dataSource);
043 setFetchSize(fetchSize);
044 }
045
046 /**
047 * Static method to use in finally methods for closing
048 * {@link CloseableIterator}s. Writes warning into log on exception.
049 * Since 1.2 <a href="http://commons.apache.org/proper/commons-io//javadocs/api-2.4/org/apache/commons/io/IOUtils.html#closeQuietly%28java.io.Closeable%29">IOUtils.closeQuietly()</a>
050 * may be used instead this one.
051 *
052 * @param iter iterator to close
053 */
054 @Deprecated // use IOUtils.closeQuietly() instead
055 public static void closeQuetly(CloseableIterator<?> iter) {
056 try {
057 if (iter != null) {
058 iter.close();
059 }
060 } catch (Exception e) {
061 logger.warn("Error on closing iterator: [" + iter + "]", e);
062 }
063 }
064
065 /**
066 * {@inheritDoc}
067 */
068 @Override
069 public <T> CloseableIterator<T> queryForIter(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException {
070 return queryForIter(psc, null, rowMapper);
071 }
072
073 /**
074 * {@inheritDoc}
075 */
076 @Override
077 // see {@code JdbcTemplate#query(org.springframework.jdbc.core.PreparedStatementCreator, org.springframework.jdbc.core.PreparedStatementSetter, org.springframework.jdbc.core.ResultSetExtractor)}
078 // see {@code JdbcTemplate#execute(org.springframework.jdbc.core.PreparedStatementCreator, org.springframework.jdbc.core.PreparedStatementCallback)}
079 // see {@code JdbcTemplate#query(org.springframework.jdbc.core.PreparedStatementCreator, org.springframework.jdbc.core.PreparedStatementSetter, org.springframework.jdbc.core.ResultSetExtractor)}
080 public <T> CloseableIterator<T> queryForIter(PreparedStatementCreator psc, PreparedStatementSetter pss,
081 RowMapper<T> rowMapper) throws DataAccessException {
082 Assert.notNull(psc, "PreparedStatementCreator must not be null");
083 Assert.notNull(rowMapper, "RowMapper must not be null");
084 if(logger.isDebugEnabled()) {
085 String sql = getSql(psc);
086 logger.debug("Executing prepared SQL statement" + (sql != null ? " [" + sql + "]" : ""));
087 }
088 DataSource ds = getDataSource();
089 Connection con = DataSourceUtils.getConnection(ds);
090 PreparedStatement ps = null;
091 ResultSet rs = null;
092 try {
093 Connection conToUse = con;
094 NativeJdbcExtractor nje = getNativeJdbcExtractor();
095 if(nje != null && nje.isNativeConnectionNecessaryForNativePreparedStatements()) {
096 conToUse = nje.getNativeConnection(con);
097 }
098 ps = psc.createPreparedStatement(conToUse);
099 applyStatementSettings(ps);
100 PreparedStatement psToUse = ps;
101 if(nje != null) {
102 psToUse = nje.getNativePreparedStatement(ps);
103 }
104 if(pss != null) {
105 pss.setValues(psToUse);
106 }
107 rs = psToUse.executeQuery();
108 ResultSet rsToUse = rs;
109 if(nje != null) {
110 rsToUse = nje.getNativeResultSet(rs);
111 }
112 // warnings are handled after query execution but before data access
113 handleWarnings(ps);
114 return new PreparedStatementCloseableIterator<T>(ds, con, psc, pss, ps, rs, rsToUse, rowMapper);
115 } catch(SQLException ex) {
116 // Release Connection early, to avoid potential connection pool deadlock
117 // in the case when the exception translator hasn't been initialized yet.
118 if(psc instanceof ParameterDisposer) {
119 ((ParameterDisposer) psc).cleanupParameters();
120 }
121 String sql = getSql(psc);
122 psc = null;
123 JdbcUtils.closeResultSet(rs);
124 rs = null;
125 JdbcUtils.closeStatement(ps);
126 ps = null;
127 DataSourceUtils.releaseConnection(con, getDataSource());
128 con = null;
129 throw getExceptionTranslator().translate("PreparedStatementCallback", sql, ex);
130 }
131 // resources will be closed in iterator
132 // finally {
133 // if(psc instanceof ParameterDisposer) {
134 // ((ParameterDisposer) psc).cleanupParameters();
135 // }
136 // JdbcUtils.closeStatement(ps);
137 // DataSourceUtils.releaseConnection(con, getDataSource());
138 // }
139 }
140
141 /**
142 * {@inheritDoc}
143 */
144 @Override
145 public <T> CloseableIterator<T> queryForIter(String sql, RowMapper<T> rowMapper) throws DataAccessException {
146 Assert.hasText(sql, "Provided sql query is blank");
147 Assert.notNull(rowMapper, "RowMapper must not be null");
148 DataSource ds = getDataSource();
149 Connection con = DataSourceUtils.getConnection(ds);
150 Statement stmt = null;
151 ResultSet rs = null;
152 try {
153 Connection conToUse = con;
154 NativeJdbcExtractor nje = getNativeJdbcExtractor();
155 if(nje != null && nje.isNativeConnectionNecessaryForNativeStatements()) {
156 conToUse = nje.getNativeConnection(con);
157 }
158 stmt = conToUse.createStatement();
159 applyStatementSettings(stmt);
160 Statement stmtToUse = stmt;
161 if(nje != null) {
162 stmtToUse = nje.getNativeStatement(stmt);
163 }
164 rs = stmtToUse.executeQuery(sql);
165 ResultSet rsToUse = rs;
166 if(nje != null) {
167 rsToUse = nje.getNativeResultSet(rs);
168 }
169 // warnings are handled after query execution but before data access
170 handleWarnings(stmt);
171 return new StatementCloseableIterator<T>(ds, con, stmt, rs, rsToUse, rowMapper);
172 } catch(SQLException ex) {
173 JdbcUtils.closeResultSet(rs);
174 rs = null;
175 // Release Connection early, to avoid potential connection pool deadlock
176 // in the case when the exception translator hasn't been initialized yet.
177 JdbcUtils.closeStatement(stmt);
178 stmt = null;
179 DataSourceUtils.releaseConnection(con, getDataSource());
180 con = null;
181 throw getExceptionTranslator().translate("StatementCallback", getSql(sql), ex);
182 }
183 // resources will be closed in iterator
184 // finally {
185 // JdbcUtils.closeStatement(stmt);
186 // DataSourceUtils.releaseConnection(con, getDataSource());
187 // }
188 }
189
190 /**
191 * {@inheritDoc}
192 */
193 @Override
194 public <T> CloseableIterator<T> queryForIter(String sql, Class<T> elementType) throws DataAccessException {
195 return queryForIter(sql, getSingleColumnRowMapper(elementType));
196 }
197
198 /**
199 * {@inheritDoc}
200 */
201 @Override
202 public CloseableIterator<Map<String, Object>> queryForIter(String sql) throws DataAccessException {
203 return queryForIter(sql, getColumnMapRowMapper());
204 }
205
206 /**
207 * {@inheritDoc}
208 */
209 @Override
210 public <T> CloseableIterator<T> queryForIter(String sql, PreparedStatementSetter pss, RowMapper<T> rowMapper) throws DataAccessException {
211 return queryForIter(new SimplePreparedStatementCreator(sql), pss, rowMapper);
212 }
213
214 /**
215 * {@inheritDoc}
216 */
217 @Override
218 public <T> CloseableIterator<T> queryForIter(String sql, Object[] args, int[] argTypes, RowMapper<T> rowMapper) throws DataAccessException {
219 return queryForIter(sql, newArgTypePreparedStatementSetter(args, argTypes), rowMapper);
220 }
221
222 /**
223 * {@inheritDoc}
224 */
225 @Override
226 public <T> CloseableIterator<T> queryForIter(String sql, Object[] args, RowMapper<T> rowMapper) throws DataAccessException {
227 return queryForIter(sql, newArgPreparedStatementSetter(args), rowMapper);
228 }
229
230 /**
231 * {@inheritDoc}
232 */
233 @Override
234 public <T> CloseableIterator<T> queryForIter(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException {
235 return queryForIter(sql, newArgPreparedStatementSetter(args), rowMapper);
236 }
237
238 /**
239 * {@inheritDoc}
240 */
241 @Override
242 public <T> CloseableIterator<T> queryForIter(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException {
243 return queryForIter(sql, args, argTypes, getSingleColumnRowMapper(elementType));
244 }
245
246 /**
247 * {@inheritDoc}
248 */
249 @Override
250 public <T> CloseableIterator<T> queryForIter(String sql, Object[] args, Class<T> elementType) throws DataAccessException {
251 return queryForIter(sql, args, getSingleColumnRowMapper(elementType));
252 }
253
254 /**
255 * {@inheritDoc}
256 */
257 @Override
258 public <T> CloseableIterator<T> queryForIter(String sql, Class<T> elementType, Object... args) throws DataAccessException {
259 return queryForIter(sql, args, getSingleColumnRowMapper(elementType));
260 }
261
262 /**
263 * {@inheritDoc}
264 */
265 @Override
266 public CloseableIterator<Map<String, Object>> queryForIter(String sql, Object[] args, int[] argTypes) throws DataAccessException {
267 return queryForIter(sql, args, argTypes, getColumnMapRowMapper());
268 }
269
270 /**
271 * {@inheritDoc}
272 */
273 @Override
274 public CloseableIterator<Map<String, Object>> queryForIter(String sql, Object... args) throws DataAccessException {
275 return queryForIter(sql, args, getColumnMapRowMapper());
276 }
277
278 /**
279 * Determine SQL from potential provider object.
280 * Borrowed from {@code JdbcTemplate}
281 *
282 * @param sqlProvider object that's potentially a SqlProvider
283 * @return the SQL string, or <code>null</code>
284 * @see SqlProvider
285 */
286 private static String getSql(Object sqlProvider) {
287 if(sqlProvider instanceof SqlProvider) {
288 return ((SqlProvider) sqlProvider).getSql();
289 } else {
290 return null;
291 }
292 }
293
294 /**
295 * Simple adapter for PreparedStatementCreator, allowing to use a plain SQL statement.
296 * Borrowed from {@code JdbcTemplate}
297 */
298 private static class SimplePreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
299 private final String sql;
300 public SimplePreparedStatementCreator(String sql) {
301 Assert.notNull(sql, "SQL must not be null");
302 this.sql = sql;
303 }
304 public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
305 return con.prepareStatement(this.sql);
306 }
307 public String getSql() {
308 return this.sql;
309 }
310 }
311 }