001/*
002 * Copyright 2015-2022 Transmogrify LLC, 2022-2024 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}