001/*
002 * (C) Copyright 2015 Nuxeo SA (http://nuxeo.com/) and contributors.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the GNU Lesser General Public License
006 * (LGPL) version 2.1 which accompanies this distribution, and is available at
007 * http://www.gnu.org/licenses/lgpl-2.1.html
008 *
009 * This library is distributed in the hope that it will be useful,
010 * but WITHOUT ANY WARRANTY; without even the implied warranty of
011 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
012 * Lesser General Public License for more details.
013 *
014 * Contributors:
015 *     Florent Guillaume
016 */
017package org.nuxeo.common.utils;
018
019import java.sql.Connection;
020import java.sql.DriverManager;
021import java.sql.SQLException;
022import java.util.concurrent.Callable;
023
024import javax.sql.DataSource;
025import javax.sql.XAConnection;
026import javax.sql.XADataSource;
027
028import org.apache.commons.logging.Log;
029import org.apache.commons.logging.LogFactory;
030
031/**
032 * Helper for common JDBC-related operations.
033 *
034 * @since 7.3
035 */
036public class JDBCUtils {
037
038    private static final Log log = LogFactory.getLog(JDBCUtils.class);
039
040    /**
041     * Maximum number of times we retry a call if the server says it's overloaded.
042     */
043    public static final int MAX_TRIES = 5;
044
045    /**
046     * Tries to do a JDBC call even when the server is overloaded.
047     * <p>
048     * Oracle has problems opening and closing many connections in a short time span (ORA-12516, ORA-12519). It seems to
049     * have something to do with how closed sessions are not immediately accounted for by Oracle's PMON (process
050     * monitor). When we get these errors, we retry a few times with exponential backoff.
051     *
052     * @param callable the callable
053     * @return the returned value
054     */
055    public static <V> V callWithRetry(Callable<V> callable) throws SQLException {
056        for (int tryNo = 1;; tryNo++) {
057            try {
058                return callable.call();
059            } catch (SQLException e) {
060                if (tryNo >= MAX_TRIES) {
061                    throw e;
062                }
063                int errorCode = e.getErrorCode();
064                if (errorCode != 12516 && errorCode != 12519) {
065                    throw e;
066                }
067                // Listener refused the connection with the following error:
068                // ORA-12519, TNS:no appropriate service handler found
069                // ORA-12516, TNS:listener could not find available handler with matching protocol stack
070                if (log.isDebugEnabled()) {
071                    log.debug(String.format("Connections open too fast, retrying in %ds: %s", Integer.valueOf(tryNo),
072                            e.getMessage().replace("\n", " ")));
073                }
074                try {
075                    Thread.sleep(1000 * tryNo);
076                } catch (InterruptedException ie) { // deals with interrupt below
077                    throw ExceptionUtils.runtimeException(e);
078                }
079            } catch (Exception e) { // deals with interrupt below
080                throw ExceptionUtils.runtimeException(e);
081            }
082        }
083    }
084
085    /**
086     * Tries to acquire a {@link Connection} through the {@link DriverManager} even when the server is overloaded.
087     *
088     * @param url a database url of the form <code>jdbc:<em>subprotocol</em>:<em>subname</em></code>
089     * @param user the database user on whose behalf the connection is being made
090     * @param password the user's password
091     * @return a connection to the URL
092     */
093    public static Connection getConnection(final String url, final String user, final String password)
094            throws SQLException {
095        return callWithRetry(new Callable<Connection>() {
096
097            @Override
098            public Connection call() throws SQLException {
099                return DriverManager.getConnection(url, user, password);
100            }
101        });
102    }
103
104    /**
105     * Tries to acquire a {@link Connection} through a {@link DataSource} even when the server is overloaded.
106     *
107     * @param dataSource the data source
108     * @return a connection to the data source
109     */
110    public static Connection getConnection(final DataSource dataSource) throws SQLException {
111        return callWithRetry(new Callable<Connection>() {
112
113            @Override
114            public Connection call() throws SQLException {
115                return dataSource.getConnection();
116            }
117        });
118    }
119
120    /**
121     * Tries to acquire a {@link XAConnection} through a {@link XADataSource} even when the server is overloaded.
122     *
123     * @param xaDataSource the XA data source
124     * @return a XA connection to the XA data source
125     */
126    public static XAConnection getXAConnection(final XADataSource xaDataSource) throws SQLException {
127        return callWithRetry(new Callable<XAConnection>() {
128
129            @Override
130            public XAConnection call() throws SQLException {
131                return xaDataSource.getXAConnection();
132            }
133        });
134    }
135
136}