Example usage for java.sql Connection getTransactionIsolation

Introduction

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

Prototype

int getTransactionIsolation() throws SQLException;

Source Link

Document

Retrieves this Connection object's current transaction isolation level.

Usage

From source file:TXInfo.java

public static void main(String[] a) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:MusicVideo");
    int tx = con.getMetaData().getDefaultTransactionIsolation();
    String txtxt = null;//  www.  j a  v a  2  s .  c om
    switch (tx) {
    case Connection.TRANSACTION_NONE:
        txtxt = "TRANSACTION_NONE";
        break;
    case Connection.TRANSACTION_READ_COMMITTED:
        txtxt = "TRANSACTION_READ_COMMITTED";
        break;
    case Connection.TRANSACTION_READ_UNCOMMITTED:
        txtxt = "TRANSACTION_READ_UNCOMMITTED";
        break;
    case Connection.TRANSACTION_REPEATABLE_READ:
        txtxt = "TRANSACTION_REPEATABLE_READ";
        break;
    case Connection.TRANSACTION_SERIALIZABLE:
        txtxt = "TRANSACTION_SERIALIZABLE";
        break;
    default:
        txtxt = "UNKNOWN!!";
    }
    System.out.println(txtxt);
    con.setTransactionIsolation(tx);
    System.out.println("Done");
    con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    System.out.println("TX is now " + con.getTransactionIsolation());
}

---

From source file:com.vladmihalcea.service.impl.StoreServiceImpl.java

@Override
@Transactional(isolation = Isolation.SERIALIZABLE)
public void purchase(Long productId) {
    Product product = entityManager.find(Product.class, 1L);
    Session session = (Session) entityManager.getDelegate();
    session.doWork(new Work() {
        @Override/*  w w  w .j ava2  s. co m*/
        public void execute(Connection connection) throws SQLException {
            LOGGER.debug("Transaction isolation level is {}",
                    Environment.isolationLevelToString(connection.getTransactionIsolation()));
        }
    });
    product.setQuantity(product.getQuantity() - 1);
}

---

From source file:pl.com.bottega.testutils.HibernateExtendedJpaDialect.java

/**
 * This method is overridden to set custom isolation levels on the connection
 * @param entityManager/*from   w w  w  .  jav a 2 s. com*/
 * @param definition
 * @return
 * @throws PersistenceException
 * @throws SQLException
 * @throws TransactionException
 */
@Override
public Object beginTransaction(final EntityManager entityManager, final TransactionDefinition definition)
        throws PersistenceException, SQLException, TransactionException {
    Session session = (Session) entityManager.getDelegate();
    if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
        getSession(entityManager).getTransaction().setTimeout(definition.getTimeout());
    }

    entityManager.getTransaction().begin();
    logger.debug("Transaction started");

    session.doWork(new Work() {

        public void execute(Connection connection) throws SQLException {
            logger.debug("The connection instance is {}", connection);
            logger.debug(
                    "The isolation level of the connection is {} and the isolation level set on the transaction is {}",
                    connection.getTransactionIsolation(), definition.getIsolationLevel());
            DataSourceUtils.prepareConnectionForTransaction(connection, definition);
        }
    });

    return prepareTransaction(entityManager, definition.isReadOnly(), definition.getName());
}

---

From source file:net.sf.jasperreports.data.hibernate.HibernateConnectionProvider.java

@Override
public Connection getConnection() throws SQLException {

    if (log.isTraceEnabled())
        log.trace("total checked-out connections: " + checkedOut);

    synchronized (pool) {
        if (!pool.isEmpty()) {
            int last = pool.size() - 1;
            if (log.isTraceEnabled()) {
                log.trace("using pooled JDBC connection, pool size: " + last);
                checkedOut++;/*from  w ww.  j  av  a 2  s .c o  m*/
            }
            Connection pooled = pool.remove(last);
            if (isolation != null)
                pooled.setTransactionIsolation(isolation.intValue());
            if (pooled.getAutoCommit() != autocommit)
                pooled.setAutoCommit(autocommit);
            return pooled;
        }
    }

    log.debug("opening new JDBC connection");
    Connection conn = driver.connect(url, connectionProps);
    if (isolation != null)
        conn.setTransactionIsolation(isolation.intValue());
    if (conn.getAutoCommit() != autocommit)
        conn.setAutoCommit(autocommit);

    if (log.isDebugEnabled()) {
        log.debug("created connection to: " + url + ", Isolation Level: " + conn.getTransactionIsolation());
    }
    if (log.isTraceEnabled())
        checkedOut++;

    return conn;
}

---

From source file:org.cfr.capsicum.datasource.DataSourceUtils.java

/**
 * Prepare the given Connection with the given transaction semantics.
 * @param con the Connection to prepare/*  w  ww .  ja v  a  2 s. c o  m*/
 * @param definition the transaction definition to apply
 * @return the previous isolation level, if any
 * @throws SQLException if thrown by JDBC methods
 * @see #resetConnectionAfterTransaction
 */
public static Integer prepareConnectionForTransaction(Connection con, 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 (Throwable ex) {
            // SQLException or UnsupportedOperationException
            // -> 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());
        }
        previousIsolationLevel = new Integer(con.getTransactionIsolation());
        con.setTransactionIsolation(definition.getIsolationLevel());
    }

    return previousIsolationLevel;
}

---

From source file:org.opendatakit.persistence.engine.pgres.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;//from   ww w  .ja v a  2s  . co  m

    stringBuilder.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = stringBuilder.toString();
    stringBuilder.setLength(0);

    stringBuilder.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        stringBuilder.append("INSERT INTO ");
        stringBuilder.append(tableName);
        stringBuilder.append(" (");
        first = true;
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(dataField.getName());
            stringBuilder.append(K_BQ);
        }
        first = true;
        stringBuilder.append(") VALUES ( ");
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            if (dataField.equals(entity.creationDate) || dataField.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (dataField.equals(entity.creatorUriUser) || dataField.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (dataField.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (dataField.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (dataField.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (dataField.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + dataField.getName());
            }
        }
        stringBuilder.append(")");
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    } else {
        // update existing record (prospective lock)
        stringBuilder.append("UPDATE ");
        stringBuilder.append(tableName);
        stringBuilder.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(f.getName());
            stringBuilder.append(K_BQ);
            stringBuilder.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        stringBuilder.append(" WHERE ");
        stringBuilder.append(K_BQ);
        stringBuilder.append(entity.primaryKey.getName());
        stringBuilder.append(K_BQ);
        stringBuilder.append(" = ");
        stringBuilder.append(uriLockInline);
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    stringBuilder.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ")
            .append(uriLockInline).append(" AND ");
    stringBuilder.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (PSQLException e) {
                    e.printStackTrace();
                    conn.rollback();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

---

From source file:DbServletTrans.java

public void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, java.io.IOException {

    Connection conn = null;
    Statement stmt = null;//from  w  w w . j a v  a  2s  .  c o  m

    response.setContentType("text/html");
    java.io.PrintWriter out = response.getWriter();
    out.println("<html><head><title>Using transactions</title></head><body>");
    out.println("<h2>These SQL statements are part of a transaction</h2>");
    out.println("CallableStatement.executeUpdate()");
    out.println("<br><br>");
    out.println("Statement.executeUpdate()");
    out.println("<br><br>");

    try {

        conn = pool.getConnection();

        out.println("AutoCommit before setAutoCommit(): " + conn.getAutoCommit() + "<br><br>");

        out.println("Transaction isolation level: ");

        switch (conn.getTransactionIsolation()) {

        case 0:
            out.println("TRANSACTION_NONE<br><br>");
            break;
        case 1:
            out.println("TRANSACTION_READ_UNCOMMITTED<br><br>");
            break;
        case 2:
            out.println("TRANSACTION_READ_COMMITTED<br><br>");
            break;
        case 4:
            out.println("TRANSACTION_REPEATABLE_READ<br><br>");
            break;
        case 8:
            out.println("TRANSACTION_SERIALIZABLE<br><br>");
            break;
        default:
            out.println("UNKNOWN<br><br>");

        }
        conn.setAutoCommit(false);

        CallableStatement cs = null;

        //Create an instance of the CallableStatement
        cs = conn.prepareCall("{call addEvent (?,?,?)}");

        cs.setString(1, "Salisbury Beach 5-Miler");
        cs.setString(2, "Salisbury MA");
        cs.setString(3, "14-Aug-2003");

        //Call the inherited PreparedStatement.executeUpdate() method
        cs.executeUpdate();

        String sql = "update raceevent set racedate='13-Aug-2003' " + "where name='Salisbury Beach 5-Miler'";

        int res = 0;

        stmt = conn.createStatement();

        res = stmt.executeUpdate(sql);

        //commit the two SQL statements
        conn.commit();

    } catch (Exception e) {

        try {
            //rollback the transaction in case of a problem
            conn.rollback();

        } catch (SQLException sqle) {
        }

        throw new ServletException(e.getMessage());

    } finally {

        try {

            if (stmt != null)
                stmt.close();

            if (conn != null)
                conn.close();//this returns the Connection to the
                             // Connection pool

        } catch (SQLException sqle) {
        }

    }
    out.println("</table></body></html>");
    out.close();

}

---

From source file:com.adaptris.jdbc.connection.FailoverDatasourceTest.java

@Test
public void testInfo() throws Exception {
    Connection conn = new MyProxy();

    try {// w  w  w.j ava 2 s.  co  m
        try {
            conn.getMetaData();
        } catch (SQLException e) {

        }
        try {
            conn.setCatalog(conn.getCatalog());
        } catch (SQLException e) {

        }
        try {
            conn.setReadOnly(conn.isReadOnly());
        } catch (SQLException e) {

        }
        try {
            conn.setTransactionIsolation(conn.getTransactionIsolation());
        } catch (SQLException e) {

        }
        try {
            conn.setTransactionIsolation(conn.getTransactionIsolation());
        } catch (SQLException e) {

        }
        try {
            conn.getWarnings();
        } catch (SQLException e) {

        }
        try {
            conn.clearWarnings();
        } catch (SQLException e) {

        }
        try {
            conn.setHoldability(conn.getHoldability());
        } catch (SQLException e) {

        }

        try {
            conn.setSchema(conn.getSchema());
        } catch (SQLException e) {

        }

    } finally {
        JdbcUtil.closeQuietly(conn);
    }

}

---

From source file:com.evolveum.midpoint.repo.sql.SqlRepositoryServiceImpl.java

private String getTransactionIsolation(Connection connection, SqlRepositoryConfiguration config) {
    String value = config.getTransactionIsolation() != null
            ? config.getTransactionIsolation().name() + "(read from repo configuration)"
            : null;/* www .j av a2 s. c o  m*/

    try {
        switch (connection.getTransactionIsolation()) {
        case Connection.TRANSACTION_NONE:
            value = "TRANSACTION_NONE (read from connection)";
            break;
        case Connection.TRANSACTION_READ_COMMITTED:
            value = "TRANSACTION_READ_COMMITTED (read from connection)";
            break;
        case Connection.TRANSACTION_READ_UNCOMMITTED:
            value = "TRANSACTION_READ_UNCOMMITTED (read from connection)";
            break;
        case Connection.TRANSACTION_REPEATABLE_READ:
            value = "TRANSACTION_REPEATABLE_READ (read from connection)";
            break;
        case Connection.TRANSACTION_SERIALIZABLE:
            value = "TRANSACTION_SERIALIZABLE (read from connection)";
            break;
        default:
            value = "Unknown value in connection.";
        }
    } catch (Exception ex) {
        //nowhere to report error (no operation result available)
    }

    return value;
}

---

From source file:fll.db.Queries.java

/**
 * Insert or update a performance score.
 * /*from www  .j a  v  a 2  s .c o m*/
 * @throws SQLException on a database error.
 * @throws RuntimeException if a parameter is missing.
 * @throws ParseException if the team number cannot be parsed
 */
public static void insertOrUpdatePerformanceScore(final ChallengeDescription description,
        final Connection connection, final HttpServletRequest request)
        throws SQLException, ParseException, RuntimeException {
    final int oldTransactionIsolation = connection.getTransactionIsolation();
    final boolean oldAutoCommit = connection.getAutoCommit();
    try {
        // make sure that we don't get into a race with another thread
        connection.setAutoCommit(false);
        connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

        final int rowsUpdated = updatePerformanceScore(description, connection, request);
        if (rowsUpdated < 1) {
            insertPerformanceScore(description, connection, request);
        }
        connection.commit();
    } finally {
        connection.setTransactionIsolation(oldTransactionIsolation);
        connection.setAutoCommit(oldAutoCommit);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• »