001/* 002 * Copyright 2015-2022 Transmogrify LLC, 2022-2023 Revetware LLC. 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 017package com.pyranid; 018 019import javax.annotation.Nonnull; 020import javax.annotation.Nullable; 021import javax.annotation.concurrent.NotThreadSafe; 022import javax.annotation.concurrent.ThreadSafe; 023import javax.sql.DataSource; 024import java.sql.Connection; 025import java.sql.PreparedStatement; 026import java.sql.ResultSet; 027import java.sql.SQLException; 028import java.time.Duration; 029import java.time.ZoneId; 030import java.util.ArrayDeque; 031import java.util.ArrayList; 032import java.util.Arrays; 033import java.util.Deque; 034import java.util.List; 035import java.util.Optional; 036import java.util.concurrent.atomic.AtomicInteger; 037import java.util.function.Consumer; 038import java.util.logging.Logger; 039import java.util.stream.Collectors; 040 041import static java.lang.String.format; 042import static java.lang.System.nanoTime; 043import static java.util.Objects.requireNonNull; 044import static java.util.logging.Level.WARNING; 045 046/** 047 * Main class for performing database access operations. 048 * 049 * @author <a href="https://www.revetkn.com">Mark Allen</a> 050 * @since 1.0.0 051 */ 052@ThreadSafe 053public class Database { 054 @Nonnull 055 private static final ThreadLocal<Deque<Transaction>> TRANSACTION_STACK_HOLDER; 056 057 static { 058 TRANSACTION_STACK_HOLDER = ThreadLocal.withInitial(() -> new ArrayDeque<>()); 059 } 060 061 @Nonnull 062 private final DataSource dataSource; 063 @Nonnull 064 private final DatabaseType databaseType; 065 @Nonnull 066 private final ZoneId timeZone; 067 @Nonnull 068 private final InstanceProvider instanceProvider; 069 @Nonnull 070 private final PreparedStatementBinder preparedStatementBinder; 071 @Nonnull 072 private final ResultSetMapper resultSetMapper; 073 @Nonnull 074 private final StatementLogger statementLogger; 075 @Nonnull 076 private final AtomicInteger defaultIdGenerator; 077 @Nonnull 078 private final Logger logger; 079 080 @Nonnull 081 private volatile DatabaseOperationSupportStatus executeLargeBatchSupported; 082 @Nonnull 083 private volatile DatabaseOperationSupportStatus executeLargeUpdateSupported; 084 085 protected Database(@Nonnull Builder builder) { 086 requireNonNull(builder); 087 088 this.dataSource = requireNonNull(builder.dataSource); 089 this.databaseType = requireNonNull(builder.databaseType); 090 this.timeZone = builder.timeZone == null ? ZoneId.systemDefault() : builder.timeZone; 091 this.instanceProvider = builder.instanceProvider == null ? new DefaultInstanceProvider() : builder.instanceProvider; 092 this.preparedStatementBinder = builder.preparedStatementBinder == null ? new DefaultPreparedStatementBinder(this.databaseType, this.timeZone) : builder.preparedStatementBinder; 093 this.resultSetMapper = builder.resultSetMapper == null ? new DefaultResultSetMapper(this.databaseType, this.timeZone) : builder.resultSetMapper; 094 this.statementLogger = builder.statementLogger == null ? new DefaultStatementLogger() : builder.statementLogger; 095 this.defaultIdGenerator = new AtomicInteger(); 096 this.logger = Logger.getLogger(getClass().getName()); 097 this.executeLargeBatchSupported = DatabaseOperationSupportStatus.UNKNOWN; 098 this.executeLargeUpdateSupported = DatabaseOperationSupportStatus.UNKNOWN; 099 } 100 101 /** 102 * Provides a {@link Database} builder for the given {@link DataSource}. 103 * 104 * @param dataSource data source used to create the {@link Database} builder 105 * @return a {@link Database} builder 106 */ 107 @Nonnull 108 public static Builder forDataSource(@Nonnull DataSource dataSource) { 109 requireNonNull(dataSource); 110 return new Builder(dataSource); 111 } 112 113 /** 114 * Gets a reference to the current transaction, if any. 115 * 116 * @return the current transaction 117 */ 118 @Nonnull 119 public Optional<Transaction> currentTransaction() { 120 Deque<Transaction> transactionStack = TRANSACTION_STACK_HOLDER.get(); 121 return Optional.ofNullable(transactionStack.size() == 0 ? null : transactionStack.peek()); 122 } 123 124 /** 125 * Performs an operation transactionally. 126 * <p> 127 * The transaction will be automatically rolled back if an exception bubbles out of {@code transactionalOperation}. 128 * 129 * @param transactionalOperation the operation to perform transactionally 130 */ 131 public void transaction(@Nonnull TransactionalOperation transactionalOperation) { 132 requireNonNull(transactionalOperation); 133 134 transaction(() -> { 135 transactionalOperation.perform(); 136 return Optional.empty(); 137 }); 138 } 139 140 /** 141 * Performs an operation transactionally with the given isolation level. 142 * <p> 143 * The transaction will be automatically rolled back if an exception bubbles out of {@code transactionalOperation}. 144 * 145 * @param transactionIsolation the desired database transaction isolation level 146 * @param transactionalOperation the operation to perform transactionally 147 */ 148 public void transaction(@Nonnull TransactionIsolation transactionIsolation, 149 @Nonnull TransactionalOperation transactionalOperation) { 150 requireNonNull(transactionIsolation); 151 requireNonNull(transactionalOperation); 152 153 transaction(transactionIsolation, () -> { 154 transactionalOperation.perform(); 155 return Optional.empty(); 156 }); 157 } 158 159 /** 160 * Performs an operation transactionally and optionally returns a value. 161 * <p> 162 * The transaction will be automatically rolled back if an exception bubbles out of {@code transactionalOperation}. 163 * 164 * @param transactionalOperation the operation to perform transactionally 165 * @param <T> the type to be returned 166 * @return the result of the transactional operation 167 */ 168 @Nonnull 169 public <T> Optional<T> transaction(@Nonnull ReturningTransactionalOperation<T> transactionalOperation) { 170 requireNonNull(transactionalOperation); 171 return transaction(TransactionIsolation.DEFAULT, transactionalOperation); 172 } 173 174 /** 175 * Performs an operation transactionally with the given isolation level, optionally returning a value. 176 * <p> 177 * The transaction will be automatically rolled back if an exception bubbles out of {@code transactionalOperation}. 178 * 179 * @param transactionIsolation the desired database transaction isolation level 180 * @param transactionalOperation the operation to perform transactionally 181 * @param <T> the type to be returned 182 * @return the result of the transactional operation 183 */ 184 @Nonnull 185 public <T> Optional<T> transaction(@Nonnull TransactionIsolation transactionIsolation, 186 @Nonnull ReturningTransactionalOperation<T> transactionalOperation) { 187 requireNonNull(transactionIsolation); 188 requireNonNull(transactionalOperation); 189 190 Transaction transaction = new Transaction(dataSource, transactionIsolation); 191 TRANSACTION_STACK_HOLDER.get().push(transaction); 192 boolean committed = false; 193 194 try { 195 Optional<T> returnValue = transactionalOperation.perform(); 196 197 // Safeguard in case user code accidentally returns null instead of Optional.empty() 198 if (returnValue == null) 199 returnValue = Optional.empty(); 200 201 if (transaction.isRollbackOnly()) { 202 transaction.rollback(); 203 } else { 204 transaction.commit(); 205 committed = true; 206 } 207 208 return returnValue; 209 } catch (RuntimeException e) { 210 try { 211 transaction.rollback(); 212 } catch (Exception rollbackException) { 213 logger.log(WARNING, "Unable to roll back transaction", rollbackException); 214 } 215 216 throw e; 217 } catch (Throwable t) { 218 try { 219 transaction.rollback(); 220 } catch (Exception rollbackException) { 221 logger.log(WARNING, "Unable to roll back transaction", rollbackException); 222 } 223 224 throw new RuntimeException(t); 225 } finally { 226 TRANSACTION_STACK_HOLDER.get().pop(); 227 228 try { 229 try { 230 if (transaction.getInitialAutoCommit().isPresent() && transaction.getInitialAutoCommit().get()) 231 // Autocommit was true initially, so restoring to true now that transaction has completed 232 transaction.setAutoCommit(true); 233 } finally { 234 if (transaction.hasConnection()) 235 closeConnection(transaction.getConnection()); 236 } 237 } finally { 238 // Execute any user-supplied post-execution hooks 239 for (Consumer<TransactionResult> postTransactionOperation : transaction.getPostTransactionOperations()) 240 postTransactionOperation.accept(committed ? TransactionResult.COMMITTED : TransactionResult.ROLLED_BACK); 241 } 242 } 243 } 244 245 protected void closeConnection(@Nonnull Connection connection) { 246 requireNonNull(connection); 247 248 try { 249 connection.close(); 250 } catch (SQLException e) { 251 throw new DatabaseException("Unable to close database connection", e); 252 } 253 } 254 255 /** 256 * Performs an operation in the context of a pre-existing transaction. 257 * <p> 258 * No commit or rollback on the transaction will occur when {@code transactionalOperation} completes. 259 * <p> 260 * However, if an exception bubbles out of {@code transactionalOperation}, the transaction will be marked as rollback-only. 261 * 262 * @param transaction the transaction in which to participate 263 * @param transactionalOperation the operation that should participate in the transaction 264 */ 265 public void participate(@Nonnull Transaction transaction, 266 @Nonnull TransactionalOperation transactionalOperation) { 267 requireNonNull(transaction); 268 requireNonNull(transactionalOperation); 269 270 participate(transaction, () -> { 271 transactionalOperation.perform(); 272 return Optional.empty(); 273 }); 274 } 275 276 /** 277 * Performs an operation in the context of a pre-existing transaction, optionall returning a value. 278 * <p> 279 * No commit or rollback on the transaction will occur when {@code transactionalOperation} completes. 280 * <p> 281 * However, if an exception bubbles out of {@code transactionalOperation}, the transaction will be marked as rollback-only. 282 * 283 * @param transaction the transaction in which to participate 284 * @param transactionalOperation the operation that should participate in the transaction 285 * @param <T> the type to be returned 286 * @return the result of the transactional operation 287 */ 288 @Nonnull 289 public <T> Optional<T> participate(@Nonnull Transaction transaction, 290 @Nonnull ReturningTransactionalOperation<T> transactionalOperation) { 291 requireNonNull(transaction); 292 requireNonNull(transactionalOperation); 293 294 TRANSACTION_STACK_HOLDER.get().push(transaction); 295 296 try { 297 Optional<T> returnValue = transactionalOperation.perform(); 298 return returnValue == null ? Optional.empty() : returnValue; 299 } catch (RuntimeException e) { 300 transaction.setRollbackOnly(true); 301 throw e; 302 } catch (Throwable t) { 303 transaction.setRollbackOnly(true); 304 throw new RuntimeException(t); 305 } finally { 306 TRANSACTION_STACK_HOLDER.get().pop(); 307 } 308 } 309 310 /** 311 * Performs a SQL query that is expected to return 0 or 1 result rows. 312 * 313 * @param sql the SQL query to execute 314 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 315 * @param parameters {@link PreparedStatement} parameters, if any 316 * @param <T> the type to be returned 317 * @return a single result (or no result) 318 * @throws DatabaseException if > 1 row is returned 319 */ 320 @Nonnull 321 public <T> Optional<T> queryForObject(@Nonnull String sql, 322 @Nonnull Class<T> resultSetRowType, 323 @Nullable Object... parameters) { 324 requireNonNull(sql); 325 requireNonNull(resultSetRowType); 326 327 return queryForObject(Statement.of(generateId(), sql), resultSetRowType, parameters); 328 } 329 330 /** 331 * Performs a SQL query that is expected to return 0 or 1 result rows. 332 * 333 * @param statement the SQL statement to execute 334 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 335 * @param parameters {@link PreparedStatement} parameters, if any 336 * @param <T> the type to be returned 337 * @return a single result (or no result) 338 * @throws DatabaseException if > 1 row is returned 339 */ 340 public <T> Optional<T> queryForObject(@Nonnull Statement statement, 341 @Nonnull Class<T> resultSetRowType, 342 @Nullable Object... parameters) { 343 requireNonNull(statement); 344 requireNonNull(resultSetRowType); 345 346 List<T> list = queryForList(statement, resultSetRowType, parameters); 347 348 if (list.size() > 1) 349 throw new DatabaseException(format("Expected 1 row in resultset but got %s instead", list.size())); 350 351 return Optional.ofNullable(list.size() == 0 ? null : list.get(0)); 352 } 353 354 /** 355 * Performs a SQL query that is expected to return any number of result rows. 356 * 357 * @param sql the SQL query to execute 358 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 359 * @param parameters {@link PreparedStatement} parameters, if any 360 * @param <T> the type to be returned 361 * @return a list of results 362 */ 363 @Nonnull 364 public <T> List<T> queryForList(@Nonnull String sql, 365 @Nonnull Class<T> resultSetRowType, 366 @Nullable Object... parameters) { 367 requireNonNull(sql); 368 requireNonNull(resultSetRowType); 369 370 return queryForList(Statement.of(generateId(), sql), resultSetRowType, parameters); 371 } 372 373 /** 374 * Performs a SQL query that is expected to return any number of result rows. 375 * 376 * @param statement the SQL statement to execute 377 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 378 * @param parameters {@link PreparedStatement} parameters, if any 379 * @param <T> the type to be returned 380 * @return a list of results 381 */ 382 @Nonnull 383 public <T> List<T> queryForList(@Nonnull Statement statement, 384 @Nonnull Class<T> resultSetRowType, 385 @Nullable Object... parameters) { 386 requireNonNull(statement); 387 requireNonNull(resultSetRowType); 388 389 List<T> list = new ArrayList<>(); 390 StatementContext<T> statementContext = new StatementContext.Builder<T>(statement) 391 .resultSetRowType(resultSetRowType) 392 .parameters(parameters) 393 .build(); 394 395 List<Object> parametersAsList = parameters == null ? List.of() : Arrays.asList(parameters); 396 397 performDatabaseOperation(statementContext, parametersAsList, (PreparedStatement preparedStatement) -> { 398 long startTime = nanoTime(); 399 400 try (ResultSet resultSet = preparedStatement.executeQuery()) { 401 Duration executionDuration = Duration.ofNanos(nanoTime() - startTime); 402 startTime = nanoTime(); 403 404 while (resultSet.next()) { 405 T listElement = getResultSetMapper().map(statementContext, resultSet, statementContext.getResultSetRowType().get(), getInstanceProvider()).orElse(null); 406 list.add(listElement); 407 } 408 409 Duration resultSetMappingDuration = Duration.ofNanos(nanoTime() - startTime); 410 return new DatabaseOperationResult(executionDuration, resultSetMappingDuration); 411 } 412 }); 413 414 return list; 415 } 416 417 /** 418 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}; 419 * or a SQL statement that returns nothing, such as a DDL statement. 420 * 421 * @param sql the SQL to execute 422 * @param parameters {@link PreparedStatement} parameters, if any 423 * @return the number of rows affected by the SQL statement 424 */ 425 @Nonnull 426 public Long execute(@Nonnull String sql, 427 @Nullable Object... parameters) { 428 requireNonNull(sql); 429 return execute(Statement.of(generateId(), sql), parameters); 430 } 431 432 /** 433 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}; 434 * or a SQL statement that returns nothing, such as a DDL statement. 435 * 436 * @param statement the SQL statement to execute 437 * @param parameters {@link PreparedStatement} parameters, if any 438 * @return the number of rows affected by the SQL statement 439 */ 440 @Nonnull 441 public Long execute(@Nonnull Statement statement, 442 @Nullable Object... parameters) { 443 requireNonNull(statement); 444 445 ResultHolder<Long> resultHolder = new ResultHolder<>(); 446 StatementContext<Void> statementContext = new StatementContext.Builder<>(statement) 447 .parameters(parameters) 448 .build(); 449 450 List<Object> parametersAsList = parameters == null ? List.of() : Arrays.asList(parameters); 451 452 performDatabaseOperation(statementContext, parametersAsList, (PreparedStatement preparedStatement) -> { 453 long startTime = nanoTime(); 454 455 DatabaseOperationSupportStatus executeLargeUpdateSupported = getExecuteLargeUpdateSupported(); 456 457 // Use the appropriate "large" value if we know it. 458 // If we don't know it, detect it and store it. 459 if (executeLargeUpdateSupported == DatabaseOperationSupportStatus.YES) { 460 resultHolder.value = preparedStatement.executeLargeUpdate(); 461 } else if (executeLargeUpdateSupported == DatabaseOperationSupportStatus.NO) { 462 resultHolder.value = (long) preparedStatement.executeUpdate(); 463 } else { 464 // If the driver doesn't support executeLargeUpdate, then UnsupportedOperationException is thrown. 465 try { 466 resultHolder.value = preparedStatement.executeLargeUpdate(); 467 setExecuteLargeUpdateSupported(DatabaseOperationSupportStatus.YES); 468 } catch (UnsupportedOperationException e) { 469 setExecuteLargeUpdateSupported(DatabaseOperationSupportStatus.NO); 470 resultHolder.value = (long) preparedStatement.executeUpdate(); 471 } 472 } 473 474 Duration executionDuration = Duration.ofNanos(nanoTime() - startTime); 475 return new DatabaseOperationResult(executionDuration, null); 476 }); 477 478 return resultHolder.value; 479 } 480 481 /** 482 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}, 483 * which returns 0 or 1 rows, e.g. with Postgres/Oracle's {@code RETURNING} clause. 484 * 485 * @param sql the SQL query to execute 486 * @param resultSetRowType the type to which the {@link ResultSet} row should be marshaled 487 * @param parameters {@link PreparedStatement} parameters, if any 488 * @param <T> the type to be returned 489 * @return a single result (or no result) 490 * @throws DatabaseException if > 1 row is returned 491 */ 492 @Nonnull 493 public <T> Optional<T> executeForObject(@Nonnull String sql, 494 @Nonnull Class<T> resultSetRowType, 495 @Nullable Object... parameters) { 496 requireNonNull(sql); 497 requireNonNull(resultSetRowType); 498 499 return executeForObject(Statement.of(generateId(), sql), resultSetRowType, parameters); 500 } 501 502 /** 503 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}, 504 * which returns 0 or 1 rows, e.g. with Postgres/Oracle's {@code RETURNING} clause. 505 * 506 * @param statement the SQL statement to execute 507 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 508 * @param parameters {@link PreparedStatement} parameters, if any 509 * @param <T> the type to be returned 510 * @return a single result (or no result) 511 * @throws DatabaseException if > 1 row is returned 512 */ 513 public <T> Optional<T> executeForObject(@Nonnull Statement statement, 514 @Nonnull Class<T> resultSetRowType, 515 @Nullable Object... parameters) { 516 requireNonNull(statement); 517 requireNonNull(resultSetRowType); 518 519 // Ultimately we just delegate to queryForObject. 520 // Having `executeForList` is to allow for users to explicitly express intent 521 // and make static analysis of code easier (e.g. maybe you'd like to hook all of your "execute" statements for 522 // logging, or delegation to a writable master as opposed to a read replica) 523 return queryForObject(statement, resultSetRowType, parameters); 524 } 525 526 /** 527 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}, 528 * which returns any number of rows, e.g. with Postgres/Oracle's {@code RETURNING} clause. 529 * 530 * @param sql the SQL to execute 531 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 532 * @param parameters {@link PreparedStatement} parameters, if any 533 * @param <T> the type to be returned 534 * @return a list of results 535 */ 536 @Nonnull 537 public <T> List<T> executeForList(@Nonnull String sql, 538 @Nonnull Class<T> resultSetRowType, 539 @Nullable Object... parameters) { 540 requireNonNull(sql); 541 requireNonNull(resultSetRowType); 542 543 return executeForList(Statement.of(generateId(), sql), resultSetRowType, parameters); 544 } 545 546 /** 547 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE}, 548 * which returns any number of rows, e.g. with Postgres/Oracle's {@code RETURNING} clause. 549 * 550 * @param statement the SQL statement to execute 551 * @param resultSetRowType the type to which {@link ResultSet} rows should be marshaled 552 * @param parameters {@link PreparedStatement} parameters, if any 553 * @param <T> the type to be returned 554 * @return a list of results 555 */ 556 @Nonnull 557 public <T> List<T> executeForList(@Nonnull Statement statement, 558 @Nonnull Class<T> resultSetRowType, 559 @Nullable Object... parameters) { 560 requireNonNull(statement); 561 requireNonNull(resultSetRowType); 562 563 // Ultimately we just delegate to queryForList. 564 // Having `executeForList` is to allow for users to explicitly express intent 565 // and make static analysis of code easier (e.g. maybe you'd like to hook all of your "execute" statements for 566 // logging, or delegation to a writable master as opposed to a read replica) 567 return queryForList(statement, resultSetRowType, parameters); 568 } 569 570 /** 571 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE} 572 * in "batch" over a set of parameter groups. 573 * <p> 574 * Useful for bulk-inserting or updating large amounts of data. 575 * 576 * @param sql the SQL to execute 577 * @param parameterGroups Groups of {@link PreparedStatement} parameters 578 * @return the number of rows affected by the SQL statement per-group 579 */ 580 @Nonnull 581 public List<Long> executeBatch(@Nonnull String sql, 582 @Nonnull List<List<Object>> parameterGroups) { 583 requireNonNull(sql); 584 requireNonNull(parameterGroups); 585 586 return executeBatch(Statement.of(generateId(), sql), parameterGroups); 587 } 588 589 /** 590 * Executes a SQL Data Manipulation Language (DML) statement, such as {@code INSERT}, {@code UPDATE}, or {@code DELETE} 591 * in "batch" over a set of parameter groups. 592 * <p> 593 * Useful for bulk-inserting or updating large amounts of data. 594 * 595 * @param statement the SQL statement to execute 596 * @param parameterGroups Groups of {@link PreparedStatement} parameters 597 * @return the number of rows affected by the SQL statement per-group 598 */ 599 @Nonnull 600 public List<Long> executeBatch(@Nonnull Statement statement, 601 @Nonnull List<List<Object>> parameterGroups) { 602 requireNonNull(statement); 603 requireNonNull(parameterGroups); 604 605 ResultHolder<List<Long>> resultHolder = new ResultHolder<>(); 606 StatementContext<List<Long>> statementContext = new StatementContext.Builder<>(statement) 607 .parameters((List) parameterGroups) 608 .resultSetRowType(List.class) 609 .build(); 610 611 performDatabaseOperation(statementContext, (preparedStatement) -> { 612 for (List<Object> parameterGroup : parameterGroups) { 613 if (parameterGroup != null && parameterGroup.size() > 0) 614 getPreparedStatementBinder().bind(statementContext, preparedStatement, parameterGroup); 615 616 preparedStatement.addBatch(); 617 } 618 }, (PreparedStatement preparedStatement) -> { 619 long startTime = nanoTime(); 620 List<Long> result; 621 622 DatabaseOperationSupportStatus executeLargeBatchSupported = getExecuteLargeBatchSupported(); 623 624 // Use the appropriate "large" value if we know it. 625 // If we don't know it, detect it and store it. 626 if (executeLargeBatchSupported == DatabaseOperationSupportStatus.YES) { 627 long[] resultArray = preparedStatement.executeLargeBatch(); 628 result = Arrays.stream(resultArray).boxed().collect(Collectors.toList()); 629 } else if (executeLargeBatchSupported == DatabaseOperationSupportStatus.NO) { 630 int[] resultArray = preparedStatement.executeBatch(); 631 result = Arrays.stream(resultArray).asLongStream().boxed().collect(Collectors.toList()); 632 } else { 633 // If the driver doesn't support executeLargeBatch, then UnsupportedOperationException is thrown. 634 try { 635 long[] resultArray = preparedStatement.executeLargeBatch(); 636 result = Arrays.stream(resultArray).boxed().collect(Collectors.toList()); 637 setExecuteLargeBatchSupported(DatabaseOperationSupportStatus.YES); 638 } catch (UnsupportedOperationException e) { 639 setExecuteLargeBatchSupported(DatabaseOperationSupportStatus.NO); 640 int[] resultArray = preparedStatement.executeBatch(); 641 result = Arrays.stream(resultArray).asLongStream().boxed().collect(Collectors.toList()); 642 } 643 } 644 645 resultHolder.value = result; 646 Duration executionDuration = Duration.ofNanos(nanoTime() - startTime); 647 return new DatabaseOperationResult(executionDuration, null); 648 }); 649 650 return resultHolder.value; 651 } 652 653 protected <T> void performDatabaseOperation(@Nonnull StatementContext<T> statementContext, 654 @Nonnull List<Object> parameters, 655 @Nonnull DatabaseOperation databaseOperation) { 656 requireNonNull(statementContext); 657 requireNonNull(parameters); 658 requireNonNull(databaseOperation); 659 660 performDatabaseOperation(statementContext, (preparedStatement) -> { 661 if (parameters.size() > 0) 662 getPreparedStatementBinder().bind(statementContext, preparedStatement, parameters); 663 }, databaseOperation); 664 } 665 666 protected <T> void performDatabaseOperation(@Nonnull StatementContext<T> statementContext, 667 @Nonnull PreparedStatementBindingOperation preparedStatementBindingOperation, 668 @Nonnull DatabaseOperation databaseOperation) { 669 requireNonNull(statementContext); 670 requireNonNull(preparedStatementBindingOperation); 671 requireNonNull(databaseOperation); 672 673 long startTime = nanoTime(); 674 Duration connectionAcquisitionDuration = null; 675 Duration preparationDuration = null; 676 Duration executionDuration = null; 677 Duration resultSetMappingDuration = null; 678 Exception exception = null; 679 Connection connection = null; 680 681 try { 682 boolean alreadyHasConnection = currentTransaction().isPresent() && currentTransaction().get().hasConnection(); 683 connection = acquireConnection(); 684 connectionAcquisitionDuration = alreadyHasConnection ? null : Duration.ofNanos(nanoTime() - startTime); 685 startTime = nanoTime(); 686 687 try (PreparedStatement preparedStatement = connection.prepareStatement(statementContext.getStatement().getSql())) { 688 preparedStatementBindingOperation.perform(preparedStatement); 689 preparationDuration = Duration.ofNanos(nanoTime() - startTime); 690 691 DatabaseOperationResult databaseOperationResult = databaseOperation.perform(preparedStatement); 692 executionDuration = databaseOperationResult.getExecutionDuration().orElse(null); 693 resultSetMappingDuration = databaseOperationResult.getResultSetMappingDuration().orElse(null); 694 } 695 } catch (DatabaseException e) { 696 exception = e; 697 throw e; 698 } catch (Exception e) { 699 exception = e; 700 throw new DatabaseException(e); 701 } finally { 702 try { 703 // If this was a single-shot operation (not in a transaction), close the connection 704 if (connection != null && !currentTransaction().isPresent()) 705 closeConnection(connection); 706 } finally { 707 StatementLog statementLog = 708 StatementLog.forStatementContext(statementContext) 709 .connectionAcquisitionDuration(connectionAcquisitionDuration) 710 .preparationDuration(preparationDuration) 711 .executionDuration(executionDuration) 712 .resultSetMappingDuration(resultSetMappingDuration) 713 .exception(exception) 714 .build(); 715 716 getStatementLogger().log(statementLog); 717 } 718 } 719 } 720 721 @Nonnull 722 protected Connection acquireConnection() { 723 Optional<Transaction> transaction = currentTransaction(); 724 725 if (transaction.isPresent()) 726 return transaction.get().getConnection(); 727 728 try { 729 return getDataSource().getConnection(); 730 } catch (SQLException e) { 731 throw new DatabaseException("Unable to acquire database connection", e); 732 } 733 } 734 735 @Nonnull 736 protected DataSource getDataSource() { 737 return this.dataSource; 738 } 739 740 @Nonnull 741 protected InstanceProvider getInstanceProvider() { 742 return this.instanceProvider; 743 } 744 745 @Nonnull 746 protected PreparedStatementBinder getPreparedStatementBinder() { 747 return this.preparedStatementBinder; 748 } 749 750 @Nonnull 751 protected ResultSetMapper getResultSetMapper() { 752 return this.resultSetMapper; 753 } 754 755 @Nonnull 756 protected StatementLogger getStatementLogger() { 757 return this.statementLogger; 758 } 759 760 @Nonnull 761 protected DatabaseOperationSupportStatus getExecuteLargeBatchSupported() { 762 return this.executeLargeBatchSupported; 763 } 764 765 protected void setExecuteLargeBatchSupported(@Nonnull DatabaseOperationSupportStatus executeLargeBatchSupported) { 766 requireNonNull(executeLargeBatchSupported); 767 this.executeLargeBatchSupported = executeLargeBatchSupported; 768 } 769 770 @Nonnull 771 protected DatabaseOperationSupportStatus getExecuteLargeUpdateSupported() { 772 return this.executeLargeUpdateSupported; 773 } 774 775 protected void setExecuteLargeUpdateSupported(@Nonnull DatabaseOperationSupportStatus executeLargeUpdateSupported) { 776 requireNonNull(executeLargeUpdateSupported); 777 this.executeLargeUpdateSupported = executeLargeUpdateSupported; 778 } 779 780 @Nonnull 781 protected Object generateId() { 782 // "Unique" keys 783 return format("com.pyranid.%s", this.defaultIdGenerator.incrementAndGet()); 784 } 785 786 @FunctionalInterface 787 protected interface DatabaseOperation { 788 @Nonnull 789 DatabaseOperationResult perform(@Nonnull PreparedStatement preparedStatement) throws Exception; 790 } 791 792 @FunctionalInterface 793 protected interface PreparedStatementBindingOperation { 794 void perform(@Nonnull PreparedStatement preparedStatement) throws Exception; 795 } 796 797 /** 798 * Builder used to construct instances of {@link Database}. 799 * <p> 800 * This class is intended for use by a single thread. 801 * 802 * @author <a href="https://www.revetkn.com">Mark Allen</a> 803 * @since 1.0.0 804 */ 805 @NotThreadSafe 806 public static class Builder { 807 @Nonnull 808 private final DataSource dataSource; 809 @Nonnull 810 private final DatabaseType databaseType; 811 @Nullable 812 private ZoneId timeZone; 813 @Nullable 814 private InstanceProvider instanceProvider; 815 @Nullable 816 private PreparedStatementBinder preparedStatementBinder; 817 @Nullable 818 private ResultSetMapper resultSetMapper; 819 @Nullable 820 private StatementLogger statementLogger; 821 822 private Builder(@Nonnull DataSource dataSource) { 823 this.dataSource = requireNonNull(dataSource); 824 this.databaseType = DatabaseType.fromDataSource(dataSource); 825 } 826 827 @Nonnull 828 public Builder timeZone(@Nullable ZoneId timeZone) { 829 this.timeZone = timeZone; 830 return this; 831 } 832 833 @Nonnull 834 public Builder instanceProvider(@Nullable InstanceProvider instanceProvider) { 835 this.instanceProvider = instanceProvider; 836 return this; 837 } 838 839 @Nonnull 840 public Builder preparedStatementBinder(@Nullable PreparedStatementBinder preparedStatementBinder) { 841 this.preparedStatementBinder = preparedStatementBinder; 842 return this; 843 } 844 845 @Nonnull 846 public Builder resultSetMapper(@Nullable ResultSetMapper resultSetMapper) { 847 this.resultSetMapper = resultSetMapper; 848 return this; 849 } 850 851 @Nonnull 852 public Builder statementLogger(@Nullable StatementLogger statementLogger) { 853 this.statementLogger = statementLogger; 854 return this; 855 } 856 857 @Nonnull 858 public Database build() { 859 return new Database(this); 860 } 861 } 862 863 @ThreadSafe 864 static class DatabaseOperationResult { 865 @Nullable 866 private final Duration executionDuration; 867 @Nullable 868 private final Duration resultSetMappingDuration; 869 870 public DatabaseOperationResult(@Nullable Duration executionDuration, 871 @Nullable Duration resultSetMappingDuration) { 872 this.executionDuration = executionDuration; 873 this.resultSetMappingDuration = resultSetMappingDuration; 874 } 875 876 @Nonnull 877 public Optional<Duration> getExecutionDuration() { 878 return Optional.ofNullable(this.executionDuration); 879 } 880 881 @Nonnull 882 public Optional<Duration> getResultSetMappingDuration() { 883 return Optional.ofNullable(this.resultSetMappingDuration); 884 } 885 } 886 887 @NotThreadSafe 888 static class ResultHolder<T> { 889 T value; 890 } 891 892 enum DatabaseOperationSupportStatus { 893 UNKNOWN, 894 YES, 895 NO 896 } 897}