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                    throw ExceptionUtils.runtimeException(e);
080                }
081            } catch (Exception e) { // deals with interrupt below
082                throw ExceptionUtils.runtimeException(e);
083            }
084        }
085    }
086
087    /**
088     * Tries to acquire a {@link Connection} through the {@link DriverManager} even when the server is overloaded.
089     *
090     * @param url a database url of the form <code>jdbc:<em>subprotocol</em>:<em>subname</em></code>
091     * @param user the database user on whose behalf the connection is being made
092     * @param password the user's password
093     * @return a connection to the URL
094     */
095    public static Connection getConnection(final String url, final String user, final String password)
096            throws SQLException {
097        return callWithRetry(new Callable<Connection>() {
098
099            @Override
100            public Connection call() throws SQLException {
101                return DriverManager.getConnection(url, user, password);
102            }
103        });
104    }
105
106    /**
107     * Tries to acquire a {@link Connection} through a {@link DataSource} even when the server is overloaded.
108     *
109     * @param dataSource the data source
110     * @return a connection to the data source
111     */
112    public static Connection getConnection(final DataSource dataSource) throws SQLException {
113        return callWithRetry(new Callable<Connection>() {
114
115            @Override
116            public Connection call() throws SQLException {
117                return dataSource.getConnection();
118            }
119        });
120    }
121
122    /**
123     * Tries to acquire a {@link XAConnection} through a {@link XADataSource} even when the server is overloaded.
124     *
125     * @param xaDataSource the XA data source
126     * @return a XA connection to the XA data source
127     */
128    public static XAConnection getXAConnection(final XADataSource xaDataSource) throws SQLException {
129        return callWithRetry(new Callable<XAConnection>() {
130
131            @Override
132            public XAConnection call() throws SQLException {
133                return xaDataSource.getXAConnection();
134            }
135        });
136    }
137
138}