001: /*
002: * Copyright 2002-2007 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: * http://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:
017: package org.springframework.jdbc.core.simple;
018:
019: import java.sql.Connection;
020: import java.sql.PreparedStatement;
021: import java.sql.SQLException;
022: import java.sql.Statement;
023: import java.sql.ResultSet;
024: import java.util.Arrays;
025: import java.util.ArrayList;
026: import java.util.Collections;
027: import java.util.HashMap;
028: import java.util.List;
029: import java.util.Map;
030:
031: import javax.sql.DataSource;
032:
033: import org.apache.commons.logging.Log;
034: import org.apache.commons.logging.LogFactory;
035:
036: import org.springframework.dao.DataAccessException;
037: import org.springframework.dao.InvalidDataAccessApiUsageException;
038: import org.springframework.dao.InvalidDataAccessResourceUsageException;
039: import org.springframework.dao.DataIntegrityViolationException;
040: import org.springframework.jdbc.core.BatchPreparedStatementSetter;
041: import org.springframework.jdbc.core.ConnectionCallback;
042: import org.springframework.jdbc.core.JdbcTemplate;
043: import org.springframework.jdbc.core.PreparedStatementCreator;
044: import org.springframework.jdbc.core.SqlTypeValue;
045: import org.springframework.jdbc.core.StatementCreatorUtils;
046: import org.springframework.jdbc.core.metadata.TableMetaDataContext;
047: import org.springframework.jdbc.core.namedparam.SqlParameterSource;
048: import org.springframework.jdbc.support.GeneratedKeyHolder;
049: import org.springframework.jdbc.support.JdbcUtils;
050: import org.springframework.jdbc.support.KeyHolder;
051:
052: /**
053: * Abstract class to provide base functionality for easy inserts
054: * based on configuration options and database metadata.
055: * This class provides the base SPI for {@link SimpleJdbcInsert}.
056: *
057: * @author Thomas Risberg
058: * @since 2.5
059: */
060: public abstract class AbstractJdbcInsert {
061:
062: /** Logger available to subclasses */
063: protected final Log logger = LogFactory.getLog(getClass());
064:
065: /** Lower-level class used to execute SQL */
066: private JdbcTemplate jdbcTemplate = new JdbcTemplate();
067:
068: /** List of columns objects to be used in insert statement */
069: private List<String> declaredColumns = new ArrayList<String>();
070:
071: /**
072: * Has this operation been compiled? Compilation means at
073: * least checking that a DataSource or JdbcTemplate has been provided,
074: * but subclasses may also implement their own custom validation.
075: */
076: private boolean compiled = false;
077:
078: /** the generated string used for insert statement */
079: private String insertString;
080:
081: /** the SQL Type information for the insert columns */
082: private int[] insertTypes;
083:
084: /** the names of the columns holding the generated key */
085: private String[] generatedKeyNames = new String[] {};
086:
087: /** context used to retrieve and manage database metadata */
088: private TableMetaDataContext tableMetaDataContext = new TableMetaDataContext();
089:
090: /**
091: * Constructor for sublasses to delegate to for setting the DataSource.
092: */
093: protected AbstractJdbcInsert(DataSource dataSource) {
094: jdbcTemplate = new JdbcTemplate(dataSource);
095: }
096:
097: /**
098: * Constructor for sublasses to delegate to for setting the JdbcTemplate.
099: */
100: protected AbstractJdbcInsert(JdbcTemplate jdbcTemplate) {
101: this .jdbcTemplate = jdbcTemplate;
102: }
103:
104: //-------------------------------------------------------------------------
105: // Methods dealing with configuaration properties
106: //-------------------------------------------------------------------------
107:
108: /**
109: * Get the name of the table for this insert
110: */
111: public String getTableName() {
112: return tableMetaDataContext.getTableName();
113: }
114:
115: /**
116: * Set the name of the table for this insert
117: */
118: public void setTableName(String tableName) {
119: checkIfConfigurationModificationIsAllowed();
120: tableMetaDataContext.setTableName(tableName);
121: }
122:
123: /**
124: * Get the name of the schema for this insert
125: */
126: public String getSchemaName() {
127: return tableMetaDataContext.getSchemaName();
128: }
129:
130: /**
131: * Set the name of the schema for this insert
132: */
133: public void setSchemaName(String schemaName) {
134: checkIfConfigurationModificationIsAllowed();
135: tableMetaDataContext.setSchemaName(schemaName);
136: }
137:
138: /**
139: * Get the name of the catalog for this insert
140: */
141: public String getCatalogName() {
142: return tableMetaDataContext.getCatalogName();
143: }
144:
145: /**
146: * Set the name of the catalog for this insert
147: */
148: public void setCatalogName(String catalogName) {
149: checkIfConfigurationModificationIsAllowed();
150: tableMetaDataContext.setCatalogName(catalogName);
151: }
152:
153: /**
154: * Set the names of the columns to be used
155: */
156: public void setColumnNames(List<String> columnNames) {
157: checkIfConfigurationModificationIsAllowed();
158: declaredColumns.clear();
159: declaredColumns.addAll(columnNames);
160: }
161:
162: /**
163: * Get the names of the columns used
164: */
165: public List<String> getColumnNames() {
166: return Collections.unmodifiableList(declaredColumns);
167: }
168:
169: /**
170: * Get the names of any generated keys
171: */
172: public String[] getGeneratedKeyNames() {
173: return generatedKeyNames;
174: }
175:
176: /**
177: * Set the names of any generated keys
178: */
179: public void setGeneratedKeyNames(String[] generatedKeyNames) {
180: checkIfConfigurationModificationIsAllowed();
181: this .generatedKeyNames = generatedKeyNames;
182: }
183:
184: /**
185: * Specify the name of a single generated key column
186: */
187: public void setGeneratedKeyName(String generatedKeyName) {
188: checkIfConfigurationModificationIsAllowed();
189: this .generatedKeyNames = new String[] { generatedKeyName };
190: }
191:
192: /**
193: * Get the insert string to be used
194: */
195: public String getInsertString() {
196: return insertString;
197: }
198:
199: /**
200: * Get the array of {@link java.sql.Types} to be used for insert
201: */
202: public int[] getInsertTypes() {
203: return insertTypes;
204: }
205:
206: /**
207: * Get the {@link JdbcTemplate} that is configured to be used
208: */
209: protected JdbcTemplate getJdbcTemplate() {
210: return jdbcTemplate;
211: }
212:
213: //-------------------------------------------------------------------------
214: // Methods handling compilation issues
215: //-------------------------------------------------------------------------
216:
217: /**
218: * Compile this JdbcInsert using provided parameters and meta data plus other settings. This
219: * finalizes the configuration for this object and subsequent attempts to compile are ignored.
220: * This will be implicitly called the first time an un-compiled insert is executed.
221: * @throws org.springframework.dao.InvalidDataAccessApiUsageException if the object hasn't
222: * been correctly initialized, for example if no DataSource has been provided
223: */
224: public final void compile()
225: throws InvalidDataAccessApiUsageException {
226: if (!isCompiled()) {
227: if (getTableName() == null) {
228: throw new InvalidDataAccessApiUsageException(
229: "Table name is required");
230: }
231:
232: try {
233: this .jdbcTemplate.afterPropertiesSet();
234: } catch (IllegalArgumentException ex) {
235: throw new InvalidDataAccessApiUsageException(ex
236: .getMessage());
237: }
238:
239: compileInternal();
240: this .compiled = true;
241:
242: if (logger.isDebugEnabled()) {
243: logger.debug("JdbcInsert for table [" + getTableName()
244: + "] compiled");
245: }
246: }
247: }
248:
249: /**
250: * Method to perform the actual compilation. Subclasses can override this template method to perform
251: * their own compilation. Invoked after this base class's compilation is complete.
252: */
253: protected void compileInternal() {
254:
255: tableMetaDataContext.processMetaData(getJdbcTemplate()
256: .getDataSource(), getColumnNames(),
257: getGeneratedKeyNames());
258:
259: insertString = tableMetaDataContext
260: .createInsertString(getGeneratedKeyNames());
261:
262: insertTypes = tableMetaDataContext.createInsertTypes();
263:
264: if (logger.isDebugEnabled()) {
265: logger.debug("Compiled JdbcInsert. Insert string is ["
266: + getInsertString() + "]");
267: }
268:
269: onCompileInternal();
270: }
271:
272: /**
273: * Hook method that subclasses may override to react to compilation.
274: * This implementation does nothing.
275: */
276: protected void onCompileInternal() {
277: }
278:
279: /**
280: * Is this operation "compiled"?
281: * @return whether this operation is compiled, and ready to use.
282: */
283: public boolean isCompiled() {
284: return this .compiled;
285: }
286:
287: /**
288: * Check whether this operation has been compiled already;
289: * lazily compile it if not already compiled.
290: * <p>Automatically called by <code>validateParameters</code>.
291: */
292: protected void checkCompiled() {
293: if (!isCompiled()) {
294: logger
295: .debug("JdbcInsert not compiled before execution - invoking compile");
296: compile();
297: }
298: }
299:
300: /**
301: * Method to check whether we are allowd to make any configuration changes at this time. If the class has been
302: * compiled, then no further changes to the configuration are allowed.
303: */
304: protected void checkIfConfigurationModificationIsAllowed() {
305: if (isCompiled()) {
306: throw new InvalidDataAccessApiUsageException(
307: "Configuration can't be altered once the class has been compiled or used.");
308: }
309: }
310:
311: //-------------------------------------------------------------------------
312: // Methods handling execution
313: //-------------------------------------------------------------------------
314:
315: /**
316: * Method that provides execution of the insert using the passed in Map of parameters
317: *
318: * @param args Map with parameter names and values to be used in insert
319: * @return number of rows affected
320: */
321: protected int doExecute(Map<String, Object> args) {
322: checkCompiled();
323: List<Object> values = matchInParameterValuesWithInsertColumns(args);
324: return executeInsertInternal(values);
325: }
326:
327: /**
328: * Method that provides execution of the insert using the passed in {@link SqlParameterSource}
329: *
330: * @param parameterSource parameter names and values to be used in insert
331: * @return number of rows affected
332: */
333: protected int doExecute(SqlParameterSource parameterSource) {
334: checkCompiled();
335: List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
336: return executeInsertInternal(values);
337: }
338:
339: /**
340: * Method to execute the insert
341: */
342: private int executeInsertInternal(List<Object> values) {
343: if (logger.isDebugEnabled()) {
344: logger
345: .debug("The following parameters are used for insert "
346: + getInsertString() + " with: " + values);
347: }
348: int updateCount = jdbcTemplate.update(getInsertString(), values
349: .toArray());
350: return updateCount;
351: }
352:
353: /**
354: * Method that provides execution of the insert using the passed in Map of parameters
355: * and returning a generated key
356: *
357: * @param args Map with parameter names and values to be used in insert
358: * @return the key generated by the insert
359: */
360: protected Number doExecuteAndReturnKey(Map<String, Object> args) {
361: checkCompiled();
362: List<Object> values = matchInParameterValuesWithInsertColumns(args);
363: return executeInsertAndReturnKeyInternal(values);
364: }
365:
366: /**
367: * Method that provides execution of the insert using the passed in {@link SqlParameterSource}
368: * and returning a generated key
369: *
370: * @param parameterSource parameter names and values to be used in insert
371: * @return the key generated by the insert
372: */
373: protected Number doExecuteAndReturnKey(
374: SqlParameterSource parameterSource) {
375: checkCompiled();
376: List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
377: return executeInsertAndReturnKeyInternal(values);
378: }
379:
380: /**
381: * Method that provides execution of the insert using the passed in Map of parameters
382: * and returning all generated keys
383: *
384: * @param args Map with parameter names and values to be used in insert
385: * @return the KeyHolder containing keys generated by the insert
386: */
387: protected KeyHolder doExecuteAndReturnKeyHolder(
388: Map<String, Object> args) {
389: checkCompiled();
390: List<Object> values = matchInParameterValuesWithInsertColumns(args);
391: return executeInsertAndReturnKeyHolderInternal(values);
392: }
393:
394: /**
395: * Method that provides execution of the insert using the passed in {@link SqlParameterSource}
396: * and returning all generated keys
397: *
398: * @param parameterSource parameter names and values to be used in insert
399: * @return the KeyHolder containing keys generated by the insert
400: */
401: protected KeyHolder doExecuteAndReturnKeyHolder(
402: SqlParameterSource parameterSource) {
403: checkCompiled();
404: List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
405: return executeInsertAndReturnKeyHolderInternal(values);
406: }
407:
408: /**
409: * Method to execute the insert generating single key
410: */
411: private Number executeInsertAndReturnKeyInternal(
412: final List<Object> values) {
413: KeyHolder kh = executeInsertAndReturnKeyHolderInternal(values);
414: if (kh != null && kh.getKey() != null) {
415: return kh.getKey();
416: } else {
417: throw new DataIntegrityViolationException(
418: "Unable to retreive the generated key for the insert: "
419: + getInsertString());
420: }
421: }
422:
423: /**
424: * Method to execute the insert generating any number of keys
425: */
426: private KeyHolder executeInsertAndReturnKeyHolderInternal(
427: final List<Object> values) {
428: if (logger.isDebugEnabled()) {
429: logger.debug("The following parameters are used for call "
430: + getInsertString() + " with: " + values);
431: }
432: final KeyHolder keyHolder = new GeneratedKeyHolder();
433: if (!this .tableMetaDataContext.isGetGeneratedKeysSupported()) {
434: if (!this .tableMetaDataContext
435: .isGetGeneratedKeysSimulated()) {
436: throw new InvalidDataAccessResourceUsageException(
437: "The getGeneratedKeys feature is not supported by this database");
438: }
439: if (getGeneratedKeyNames().length < 1) {
440: throw new InvalidDataAccessApiUsageException(
441: "Generated Key Name(s) not specificed. "
442: + "Using the generated keys features requires specifying the name(s) of the generated column(s)");
443: }
444: if (getGeneratedKeyNames().length > 1) {
445: throw new InvalidDataAccessApiUsageException(
446: "Current database only supports retreiving the key for a single column. There are "
447: + getGeneratedKeyNames().length
448: + " columns specified: "
449: + Arrays.asList(getGeneratedKeyNames()));
450: }
451: // This is a hack to be able to get the generated key from a database that doesn't support
452: // get generated keys feature. HSQL is one, PostgreSQL is another. Has to be done with the same
453: // connection.
454: jdbcTemplate.execute(new ConnectionCallback() {
455: public Object doInConnection(Connection con)
456: throws SQLException, DataAccessException {
457: // Do the insert
458: PreparedStatement ps = null;
459: try {
460: ps = con.prepareStatement(getInsertString());
461: setParameterValues(ps, values, null);
462: ps.executeUpdate();
463: } finally {
464: JdbcUtils.closeStatement(ps);
465: }
466: //Get the key
467: String keyQuery = tableMetaDataContext
468: .getSimulationQueryForGetGeneratedKey(
469: tableMetaDataContext.getTableName(),
470: getGeneratedKeyNames()[0]);
471: Statement keyStmt = null;
472: ResultSet rs = null;
473: HashMap keys = new HashMap(1);
474: try {
475: keyStmt = con.createStatement();
476: rs = keyStmt.executeQuery(keyQuery);
477: if (rs.next()) {
478: long key = rs.getLong(1);
479: keys.put(getGeneratedKeyNames()[0], key);
480: keyHolder.getKeyList().add(keys);
481: }
482: } finally {
483: JdbcUtils.closeResultSet(rs);
484: JdbcUtils.closeStatement(keyStmt);
485: }
486: return null;
487: }
488: });
489: return keyHolder;
490: } else {
491: //TODO Add support for SQL Type info
492: int updateCount = jdbcTemplate.update(
493: new PreparedStatementCreator() {
494: public PreparedStatement createPreparedStatement(
495: Connection con) throws SQLException {
496: PreparedStatement ps = prepareStatementForGeneratedKeys(con);
497: setParameterValues(ps, values, null);
498: return ps;
499: }
500: }, keyHolder);
501: }
502: return keyHolder;
503: }
504:
505: /**
506: * Create the PreparedStatement to be used for insert that have generated keys
507: *
508: * @param con the connection used
509: * @return PreparedStatement to use
510: * @throws SQLException
511: */
512: private PreparedStatement prepareStatementForGeneratedKeys(
513: Connection con) throws SQLException {
514: if (getGeneratedKeyNames().length < 1) {
515: throw new InvalidDataAccessApiUsageException(
516: "Generated Key Name(s) not specificed. "
517: + "Using the generated keys features requires specifying the name(s) of the generated column(s)");
518: }
519: PreparedStatement ps;
520: if (this .tableMetaDataContext
521: .isGeneratedKeysColumnNameArraySupported()) {
522: if (logger.isDebugEnabled()) {
523: logger
524: .debug("Using generated keys support with array of column names.");
525: }
526: ps = con.prepareStatement(getInsertString(),
527: getGeneratedKeyNames());
528: } else {
529: if (logger.isDebugEnabled()) {
530: logger
531: .debug("Using generated keys support with Statement.RETURN_GENERATED_KEYS.");
532: }
533: ps = con.prepareStatement(getInsertString(),
534: Statement.RETURN_GENERATED_KEYS);
535: }
536: return ps;
537: }
538:
539: /**
540: * Method that provides execution of a batch insert using the passed in Maps of parameters
541: *
542: * @param batch array of Maps with parameter names and values to be used in batch insert
543: * @return array of number of rows affected
544: */
545: protected int[] doExecuteBatch(Map<String, Object>[] batch) {
546: checkCompiled();
547: List[] batchValues = new ArrayList[batch.length];
548: int i = 0;
549: for (Map<String, Object> args : batch) {
550: List<Object> values = matchInParameterValuesWithInsertColumns(args);
551: batchValues[i++] = values;
552: }
553: return executeBatchInternal(batchValues);
554: }
555:
556: /**
557: * Method that provides execution of a batch insert using the passed in array of {@link SqlParameterSource}
558: *
559: * @param batch array of SqlParameterSource with parameter names and values to be used in insert
560: * @return array of number of rows affected
561: */
562: protected int[] doExecuteBatch(SqlParameterSource[] batch) {
563: checkCompiled();
564: List[] batchValues = new ArrayList[batch.length];
565: int i = 0;
566: for (SqlParameterSource parameterSource : batch) {
567: List<Object> values = matchInParameterValuesWithInsertColumns(parameterSource);
568: batchValues[i++] = values;
569: }
570: return executeBatchInternal(batchValues);
571: }
572:
573: /**
574: * Method to execute the batch insert
575: */
576: //TODO synchronize parameter setters with the SimpleJdbcTemplate
577: private int[] executeBatchInternal(final List<Object>[] batchValues) {
578: if (logger.isDebugEnabled()) {
579: logger.debug("Executing statement " + getInsertString()
580: + " with batch of size: " + batchValues.length);
581: }
582: final int[] columnTypes = getInsertTypes();
583: int[] updateCounts = jdbcTemplate.batchUpdate(
584: getInsertString(), new BatchPreparedStatementSetter() {
585:
586: public void setValues(PreparedStatement ps, int i)
587: throws SQLException {
588: List<Object> values = batchValues[i];
589: setParameterValues(ps, values, columnTypes);
590: }
591:
592: public int getBatchSize() {
593: return batchValues.length;
594: }
595: });
596: return updateCounts;
597: }
598:
599: /**
600: * Internal implementation for setting parameter values
601: * @param preparedStatement the PreparedStatement
602: * @param values the values to be set
603: */
604: private void setParameterValues(
605: PreparedStatement preparedStatement, List<Object> values,
606: int[] columnTypes) throws SQLException {
607: int colIndex = 0;
608: for (Object value : values) {
609: colIndex++;
610: if (columnTypes == null || colIndex < columnTypes.length) {
611: StatementCreatorUtils.setParameterValue(
612: preparedStatement, colIndex,
613: SqlTypeValue.TYPE_UNKNOWN, value);
614: } else {
615: StatementCreatorUtils.setParameterValue(
616: preparedStatement, colIndex,
617: columnTypes[colIndex - 1], value);
618: }
619: }
620: }
621:
622: /**
623: * Match the provided in parameter values with regitered parameters and parameters defined via metedata
624: * processing.
625: *
626: * @param parameterSource the parameter vakues provided as a {@link SqlParameterSource}
627: * @return Map with parameter names and values
628: */
629: protected List<Object> matchInParameterValuesWithInsertColumns(
630: SqlParameterSource parameterSource) {
631: return tableMetaDataContext
632: .matchInParameterValuesWithInsertColumns(parameterSource);
633: }
634:
635: /**
636: * Match the provided in parameter values with regitered parameters and parameters defined via metedata
637: * processing.
638: *
639: * @param args the parameter values provided in a Map
640: * @return Map with parameter names and values
641: */
642: protected List<Object> matchInParameterValuesWithInsertColumns(
643: Map<String, Object> args) {
644: return tableMetaDataContext
645: .matchInParameterValuesWithInsertColumns(args);
646: }
647:
648: }
|