Example usage for java.sql Connection setTransactionIsolation

List of usage examples for java.sql Connection setTransactionIsolation

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. setTransactionIsolation

Introduction

In this page you can find the example usage for java.sql Connection setTransactionIsolation.

Prototype

void setTransactionIsolation(int level) throws SQLException;

Source Link

Document

Attempts to change the transaction isolation level for this Connection object to the one given.

Usage

From source file:org.executequery.datasource.ConnectionPoolImpl.java

private PooledConnection createConnection() {

    PooledConnection connection = null;

    try {//w w w  .ja v a2  s .  co m

        if (dataSource == null) {

            DatabaseConnection _databaseConnection = databaseConnection;
            if (databaseConnection.isSshTunnel()) {

                if (sshTunnel == null) {

                    createSshTunnel();
                }

                _databaseConnection = databaseConnection.copy();
                _databaseConnection.setHost("localhost");
                _databaseConnection.setPort(String.valueOf(sshTunnel.getTunnelPort()));
            }

            dataSource = new SimpleDataSource(_databaseConnection);
        }

        Connection realConnection = dataSource.getConnection();
        if (realConnection == null) {

            destroySshTunnel();
            throw new DataSourceException("A connection to the database could not be "
                    + "established.\nPlease ensure that the details "
                    + "are correct and the supplied host is available.");
        }

        if (defaultTxIsolation == -1) {

            configureTransactionIsolationLevel(realConnection);
        }

        int transactionIsolation = databaseConnection.getTransactionIsolation();
        if (transactionIsolation != -1) {

            try {

                realConnection.setTransactionIsolation(databaseConnection.getTransactionIsolation());

            } catch (SQLException e) {

                Log.warning("Error setting transaction isolation level: " + e.getMessage());
            }
        }

        connection = new PooledConnection(realConnection);
        connection.addPooledConnectionListener(this);

        openConnections.add(connection);

        if (Log.isDebugEnabled()) {

            Log.debug("Added new connection to the pool - " + connection.getId());
        }

    } catch (SQLException e) {

        destroySshTunnel();
        rethrowAsDataSourceException(e);
    }

    return connection;
}

From source file:org.wso2.carbon.registry.core.jdbc.dao.JDBCLogsDAO.java

private void addLogRecords(LogRecord[] logRecords, JDBCDataAccessManager dataAccessManager)
        throws RegistryException {
    PreparedStatement s = null;//from w w  w.j  a  va 2 s .  co  m
    Connection conn = null;
    try {
        conn = dataAccessManager.getDataSource().getConnection();
        if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        conn.setAutoCommit(false);
        String sql = "INSERT INTO REG_LOG (REG_PATH, REG_USER_ID, REG_LOGGED_TIME, "
                + "REG_ACTION, REG_ACTION_DATA, REG_TENANT_ID) " + "VALUES (?, ?, ?, ?, ?, ?)";

        s = conn.prepareStatement(sql);
        for (LogRecord logRecord : logRecords) {
            s.clearParameters();
            s.setString(1, logRecord.getResourcePath());
            s.setString(2, logRecord.getUserName());
            s.setTimestamp(3, new Timestamp(logRecord.getTimestamp().getTime()));
            s.setInt(4, logRecord.getAction());
            s.setString(5, logRecord.getActionData());
            s.setInt(6, logRecord.getTenantId());
            s.addBatch();
        }
        int[] status = s.executeBatch();
        if (log.isDebugEnabled()) {
            log.debug("Successfully added " + status.length + " log records.");
        }
        conn.commit();

    } catch (SQLException e) {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException e1) {
            log.error("Failed to rollback log insertion.", e);
        }
        String msg = "Failed to update log batch records " + ". " + e.getMessage();
        log.error(msg, e);
        throw new RegistryException(msg, e);
    } finally {
        try {
            if (s != null) {
                s.close();
            }
            if (conn != null && !(conn.isClosed())) {
                conn.close();
            }
        } catch (SQLException ex) {
            String msg = RegistryConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:org.cloudgraph.rdb.service.RDBGraphService.java

public int[] count(Query[] queries) {
    if (queries == null)
        throw new IllegalArgumentException("expected non-null 'queries' argument");
    Connection con = null;
    try {// w ww .j a va  2  s . c o m
        if (log.isDebugEnabled())
            log.debug("getting connection");
        con = ProviderManager.instance().getConnection();
        if (con.getAutoCommit()) {
            if (log.isDebugEnabled())
                log.debug("turning off connection autocommit for multi count query");
            con.setAutoCommit(false);
        }
        // TODO: make transaction isolation configurable
        RDBMSVendorName vendor = PlasmaRuntime.getInstance()
                .getRDBMSProviderVendor(DataAccessProviderName.JDBC);
        switch (vendor) {
        case ORACLE:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        case MYSQL:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        default:
        }
        if (log.isDebugEnabled())
            log.debug("using transaction isolation level " + con.getTransactionIsolation()
                    + " for multi count query");
    } catch (SQLException e2) {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        throw new DataAccessException(e2);
    }
    GraphQuery dispatcher = new GraphQuery(con);
    int[] counts = new int[queries.length];
    try {
        for (int i = 0; i < queries.length; i++)
            counts[i] = dispatcher.count(queries[i]);
        return counts;
    } finally {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
    }
}

From source file:org.cloudgraph.rdb.service.RDBGraphService.java

public DataGraph[] find(Query query, int maxResults) {
    if (query == null)
        throw new IllegalArgumentException("expected non-null 'query' argument");
    validate(query);//w w w  . j av a2s.c o  m
    if (log.isDebugEnabled()) {
        log(query);
    }
    Connection con = null;
    try {
        if (log.isDebugEnabled())
            log.debug("getting connection");
        con = ProviderManager.instance().getConnection();
        if (con.getAutoCommit()) {
            if (log.isDebugEnabled())
                log.debug("turning off connection autocommit for graph query");
            con.setAutoCommit(false);
        }

        // TODO: make transaction isolation configurable
        RDBMSVendorName vendor = PlasmaRuntime.getInstance()
                .getRDBMSProviderVendor(DataAccessProviderName.JDBC);
        switch (vendor) {
        case ORACLE:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        case MYSQL:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        default:
        }
        if (log.isDebugEnabled())
            log.debug(
                    "using transaction isolation level " + con.getTransactionIsolation() + " for graph query");
    } catch (SQLException e2) {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        throw new DataAccessException(e2);
    }
    GraphQuery dispatcher = new GraphQuery(con);
    try {
        DataGraph[] results = null;

        if (maxResults > 0)
            results = dispatcher.find(query, maxResults, new Timestamp((new Date()).getTime()));
        else
            results = dispatcher.find(query, new Timestamp((new Date()).getTime()));
        return results;
    } finally {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
    }
}

From source file:org.cloudgraph.rdb.service.RDBGraphService.java

public List<DataGraph[]> find(Query[] queries) {
    if (queries == null)
        throw new IllegalArgumentException("expected non-null 'queries' argument");
    Connection con = null;
    try {//  w  ww .  java2s  .c  o  m
        if (log.isDebugEnabled())
            log.debug("getting connection");
        con = ProviderManager.instance().getConnection();
        if (con.getAutoCommit()) {
            if (log.isDebugEnabled())
                log.debug("turning off connection autocommit for multi graph query");
            con.setAutoCommit(false);
        }

        // TODO: make transaction isolation configurable
        RDBMSVendorName vendor = PlasmaRuntime.getInstance()
                .getRDBMSProviderVendor(DataAccessProviderName.JDBC);
        switch (vendor) {
        case ORACLE:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        case MYSQL:
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            break;
        default:
        }
        if (log.isDebugEnabled())
            log.debug("using transaction isolation level " + con.getTransactionIsolation()
                    + " for multi graph query");
    } catch (SQLException e2) {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        throw new DataAccessException(e2);
    }
    GraphQuery dispatcher = new GraphQuery(con);
    List<DataGraph[]> list = new ArrayList<DataGraph[]>();
    Timestamp snapshotDate = new Timestamp((new Date()).getTime());
    try {
        for (int i = 0; i < queries.length; i++) {
            validate(queries[i]);
            if (log.isDebugEnabled()) {
                log(queries[i]);
            }
            DataGraph[] results = dispatcher.find(queries[i], snapshotDate);
            list.add(results);
        }
        return list;
    } finally {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
    }
}

From source file:org.springframework.jdbc.datasource.DataSourceUtils.java

/**
 * Prepare the given Connection with the given transaction semantics.
 * @param con the Connection to prepare/*w w  w  .  java  2s  .  c  om*/
 * @param definition the transaction definition to apply
 * @return the previous isolation level, if any
 * @throws SQLException if thrown by JDBC methods
 * @see #resetConnectionAfterTransaction
 */
@Nullable
public static Integer prepareConnectionForTransaction(Connection con,
        @Nullable TransactionDefinition definition) throws SQLException {

    Assert.notNull(con, "No Connection specified");

    // Set read-only flag.
    if (definition != null && definition.isReadOnly()) {
        try {
            if (logger.isDebugEnabled()) {
                logger.debug("Setting JDBC Connection [" + con + "] read-only");
            }
            con.setReadOnly(true);
        } catch (SQLException | RuntimeException ex) {
            Throwable exToCheck = ex;
            while (exToCheck != null) {
                if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
                    // Assume it's a connection timeout that would otherwise get lost: e.g. from JDBC 4.0
                    throw ex;
                }
                exToCheck = exToCheck.getCause();
            }
            // "read-only not supported" SQLException -> ignore, it's just a hint anyway
            logger.debug("Could not set JDBC Connection read-only", ex);
        }
    }

    // Apply specific isolation level, if any.
    Integer previousIsolationLevel = null;
    if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
        if (logger.isDebugEnabled()) {
            logger.debug("Changing isolation level of JDBC Connection [" + con + "] to "
                    + definition.getIsolationLevel());
        }
        int currentIsolation = con.getTransactionIsolation();
        if (currentIsolation != definition.getIsolationLevel()) {
            previousIsolationLevel = currentIsolation;
            con.setTransactionIsolation(definition.getIsolationLevel());
        }
    }

    return previousIsolationLevel;
}

From source file:org.wso2.carbon.repository.core.jdbc.dao.JDBCLogsDAO.java

private void addLogRecords(LogRecord[] logRecords, JDBCDataAccessManager dataAccessManager)
        throws RepositoryException {
    PreparedStatement s = null;//  ww  w  .j a v  a 2s.co m
    Connection conn = null;

    try {
        conn = dataAccessManager.getDataSource().getConnection();
        if (conn.getTransactionIsolation() != Connection.TRANSACTION_READ_COMMITTED) {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        }
        conn.setAutoCommit(false);
        String sql = "INSERT INTO REG_LOG (REG_PATH, REG_USER_ID, REG_LOGGED_TIME, "
                + "REG_ACTION, REG_ACTION_DATA, REG_TENANT_ID) " + "VALUES (?, ?, ?, ?, ?, ?)";

        s = conn.prepareStatement(sql);
        for (LogRecord logRecord : logRecords) {
            s.clearParameters();
            s.setString(1, logRecord.getResourcePath());
            s.setString(2, logRecord.getUserName());
            s.setTimestamp(3, new Timestamp(logRecord.getTimestamp().getTime()));
            s.setInt(4, logRecord.getAction().getId());
            s.setString(5, logRecord.getActionData());
            s.setInt(6, logRecord.getTenantId());
            s.addBatch();
        }
        int[] status = s.executeBatch();
        if (log.isDebugEnabled()) {
            log.debug("Successfully added " + status.length + " log records.");
        }
        conn.commit();

    } catch (SQLException e) {
        try {
            if (conn != null) {
                conn.rollback();
            }
        } catch (SQLException e1) {
            log.error("Failed to rollback log insertion.", e);
        }
        String msg = "Failed to update log batch records " + ". " + e.getMessage();
        log.error(msg, e);
        throw new RepositoryDBException(msg, e);
    } finally {
        try {
            if (s != null) {
                s.close();
            }
            if (conn != null && !(conn.isClosed())) {
                conn.close();
            }
        } catch (SQLException ex) {
            String msg = InternalConstants.RESULT_SET_PREPARED_STATEMENT_CLOSE_ERROR;
            log.error(msg, ex);
        }
    }
}

From source file:edu.umd.cs.submitServer.servlets.ImportProject.java

/**
 * The doPost method of the servlet. <br>
 * //from  ww w .j  a  v  a2s.  com
 * This method is called when a form has its tag value method equals to
 * post.
 * 
 * @param request
 *            the request send by the client to the server
 * @param response
 *            the response send by the server to the client
 * @throws ServletException
 *             if an error occurred
 * @throws IOException
 *             if an error occurred
 */
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    Connection conn = null;
    FileItem fileItem = null;
    boolean transactionSuccess = false;
    try {
        conn = getConnection();

        // MultipartRequestFilter is required
        MultipartRequest multipartRequest = (MultipartRequest) request.getAttribute(MULTIPART_REQUEST);
        Course course = (Course) request.getAttribute(COURSE);
        StudentRegistration canonicalStudentRegistration = StudentRegistration.lookupByStudentRegistrationPK(
                multipartRequest.getIntParameter("canonicalStudentRegistrationPK", 0), conn);

        fileItem = multipartRequest.getFileItem();

        conn.setAutoCommit(false);
        /*
         * 20090608: changed TRANSACTION_READ_COMMITTED to
         * TRANSACTION_REPEATABLE_READ to make transaction compatible with
         * innodb in MySQL 5.1, which defines READ_COMMITTED as unsafe for
         * use with standard binary logging. For more information, see:
         * <http
         * ://dev.mysql.com/doc/refman/5.1/en/set-transaction.html#isolevel_read
         * -committed>
         */
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

        Project project = Project.importProject(fileItem.getInputStream(), course, canonicalStudentRegistration,
                conn);

        conn.commit();
        transactionSuccess = true;

        String redirectUrl = request.getContextPath() + "/view/instructor/projectUtilities.jsp?projectPK="
                + project.getProjectPK();
        response.sendRedirect(redirectUrl);

    } catch (ClassNotFoundException e) {
        throw new ServletException(e);
    } catch (SQLException e) {
        throw new ServletException(e);
    } finally {
        rollbackIfUnsuccessfulAndAlwaysReleaseConnection(transactionSuccess, request, conn);
    }
}

From source file:com.wabacus.system.dataset.update.action.rationaldb.AbsRationalDBUpdateAction.java

public void beginTransaction(ReportRequest rrequest) {
    Connection conn = rrequest.getConnection(this.datasource);
    String dsLevel = rrequest.getTransactionLevel(this.datasource);
    if (dsLevel != null && !dsLevel.trim().equals("")) {
        if (!Consts_Private.M_ALL_TRANSACTION_LEVELS.containsKey(dsLevel)) {
            throw new WabacusRuntimeException("?" + rrequest.getPagebean().getId() + "??"
                    + this.datasource + "" + dsLevel
                    + "????");
        }//from  w  w w.  j  a v  a  2  s.c om
        if (dsLevel.equals(Consts.TRANS_NONE))
            return;
    }
    try {
        if (!conn.getAutoCommit())
            return;
        conn.setAutoCommit(false);
        if (!Tools.isEmpty(dsLevel))
            conn.setTransactionIsolation(Consts_Private.M_ALL_TRANSACTION_LEVELS.get(dsLevel));
    } catch (SQLException e) {
        throw new WabacusRuntimeException(
                "?" + this.ownerUpdateBean.getOwner().getReportBean().getPath() + "??"
                        + this.datasource + "",
                e);
    }
}

From source file:org.wso2.carbon.dataservices.core.odata.RDBMSDataHandler.java

@Override
public void commitTransaction() throws ODataServiceFault {
    Connection connection = getTransactionalConnection();
    try {/*w ww  . ja va2  s  .  c  om*/
        connection.commit();
        connection.setTransactionIsolation(defaultTransactionalIsolation);
        connection.setAutoCommit(defaultAutoCommit);
    } catch (SQLException e) {
        throw new ODataServiceFault(e, "Connection Error occurred while committing. :" + e.getMessage());
    } finally {
        /* close the connection */
        try {
            connection.close();
            transactionalConnection.set(null);
        } catch (Exception ignore) {
            // ignore
        }
    }
}