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