001/*
002 * (C) Copyright 2015 Nuxeo SA (http://nuxeo.com/) and others.
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 * Contributors:
017 *     Florent Guillaume
018 */
019package org.nuxeo.common.utils;
020
021import java.sql.Connection;
022import java.sql.DriverManager;
023import java.sql.SQLException;
024import java.util.concurrent.Callable;
025
026import javax.sql.DataSource;
027import javax.sql.XAConnection;
028import javax.sql.XADataSource;
029
030import org.apache.commons.logging.Log;
031import org.apache.commons.logging.LogFactory;
032
033/**
034 * Helper for common JDBC-related operations.
035 *
036 * @since 7.3
037 */
038public class JDBCUtils {
039
040    private static final Log log = LogFactory.getLog(JDBCUtils.class);
041
042    /**
043     * Maximum number of times we retry a call if the server says it's overloaded.
044     */
045    public static final int MAX_TRIES = 5;
046
047    /**
048     * Tries to do a JDBC call even when the server is overloaded.
049     * <p>
050     * Oracle has problems opening and closing many connections in a short time span (ORA-12516, ORA-12519). It seems to
051     * have something to do with how closed sessions are not immediately accounted for by Oracle's PMON (process
052     * monitor). When we get these errors, we retry a few times with exponential backoff.
053     *
054     * @param callable the callable
055     * @return the returned value
056     */
057    public static <V> V callWithRetry(Callable<V> callable) throws SQLException {
058        for (int tryNo = 1;; tryNo++) {
059            try {
060                return callable.call();
061            } catch (SQLException e) {
062                if (tryNo >= MAX_TRIES) {
063                    throw e;
064                }
065                int errorCode = e.getErrorCode();
066                if (errorCode != 12516 && errorCode != 12519) {
067                    throw e;
068                }
069                // Listener refused the connection with the following error:
070                // ORA-12519, TNS:no appropriate service handler found
071                // ORA-12516, TNS:listener could not find available handler with matching protocol stack
072                if (log.isDebugEnabled()) {
073                    log.debug(String.format("Connections open too fast, retrying in %ds: %s", Integer.valueOf(tryNo),
074                            e.getMessage().replace("\n", " ")));
075                }
076                try {
077                    Thread.sleep(1000 * tryNo);
078                } catch (InterruptedException ie) { // deals with interrupt below
079                    Thread.currentThread().interrupt();
080                    throw new RuntimeException(ie);
081                }
082            } catch (Exception e) { // deals with interrupt below
083                throw ExceptionUtils.runtimeException(e);
084            }
085        }
086    }
087
088    /**
089     * Tries to acquire a {@link Connection} through the {@link DriverManager} even when the server is overloaded.
090     *
091     * @param url a database url of the form <code>jdbc:<em>subprotocol</em>:<em>subname</em></code>
092     * @param user the database user on whose behalf the connection is being made
093     * @param password the user's password
094     * @return a connection to the URL
095     */
096    public static Connection getConnection(final String url, final String user, final String password)
097            throws SQLException {
098        return callWithRetry(() -> DriverManager.getConnection(url, user, password));
099    }
100
101    /**
102     * Tries to acquire a {@link Connection} through a {@link DataSource} even when the server is overloaded.
103     *
104     * @param dataSource the data source
105     * @return a connection to the data source
106     */
107    public static Connection getConnection(final DataSource dataSource) throws SQLException {
108        return callWithRetry(dataSource::getConnection);
109    }
110
111    /**
112     * Tries to acquire a {@link XAConnection} through a {@link XADataSource} even when the server is overloaded.
113     *
114     * @param xaDataSource the XA data source
115     * @return a XA connection to the XA data source
116     */
117    public static XAConnection getXAConnection(final XADataSource xaDataSource) throws SQLException {
118        return callWithRetry(xaDataSource::getXAConnection);
119    }
120
121}