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