Example usage for java.sql Connection setSavepoint

Introduction

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

Prototype

Savepoint setSavepoint() throws SQLException;

Source Link

Document

Creates an unnamed savepoint in the current transaction and returns the new Savepoint object that represents it.

Usage

From source file:SetSavepoint.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    try {/*from w  w  w.j  a  v  a 2  s. c om*/

        Class.forName("myDriver.className");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        String query = "SELECT COF_NAME, PRICE FROM COFFEES " + "WHERE TOTAL > ?";
        String update = "UPDATE COFFEES SET PRICE = ? " + "WHERE COF_NAME = ?";

        PreparedStatement getPrice = con.prepareStatement(query);
        PreparedStatement updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 7000);
        ResultSet rs = getPrice.executeQuery();

        Savepoint save1 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save1);
            }

        }

        getPrice = con.prepareStatement(query);
        updatePrice = con.prepareStatement(update);

        getPrice.setInt(1, 8000);

        rs = getPrice.executeQuery();
        System.out.println();

        Savepoint save2 = con.setSavepoint();

        while (rs.next()) {
            String cof = rs.getString("COF_NAME");
            float oldPrice = rs.getFloat("PRICE");
            float newPrice = oldPrice + (oldPrice * .05f);
            updatePrice.setFloat(1, newPrice);
            updatePrice.setString(2, cof);
            updatePrice.executeUpdate();
            System.out.println("New price of " + cof + " is " + newPrice);
            if (newPrice > 11.99) {
                con.rollback(save2);
            }
        }

        con.commit();

        Statement stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT COF_NAME, " + "PRICE FROM COFFEES");

        System.out.println();
        while (rs.next()) {
            String name = rs.getString("COF_NAME");
            float price = rs.getFloat("PRICE");
            System.out.println("Current price of " + name + " is " + price);
        }

        con.close();

    } catch (Exception e) {
        e.printStackTrace();
    }

}

---

From source file:com.adaptris.core.util.JdbcUtil.java

/**
 * Create a Savepoint on the connection//from ww  w .  j ava2 s.c  o m
 * <p>
 * If {@link Connection#getAutoCommit()} is true, then this operation returns null.
 * </p>
 *
 * @param sqlConnection the SQL Connection
 * @return a created Savepoint or null if the connection does not require it.
 * @throws SQLException if the operation fails.
 */
public static Savepoint createSavepoint(Connection sqlConnection) throws SQLException {
    if (sqlConnection == null) {
        return null;
    }
    if (!sqlConnection.getAutoCommit()) {
        return sqlConnection.setSavepoint();
    }
    return null;
}

---

From source file:org.openconcerto.sql.utils.SQLUtils.java

/**
 * Execute <code>h</code> in a transaction. Only the outer most call to
 * <code>executeAtomic()</code> commit or roll back a transaction, for recursive calls if
 * <code>continueTx</code> is <code>true</code> then nothing happens, else a save point is
 * created and rolled back if an exception occurs (allowing the caller to catch the exception
 * without loosing the current transaction).
 * <p>//ww w  .  j  av  a2 s  . co m
 * NOTE : if <code>continueTx</code> is <code>true</code> and an exception is thrown, the
 * connection might be aborted. So you should notify the caller, e.g. propagate the exception so
 * that he can roll back the transaction.
 * </p>
 * 
 * @param <T> type of return
 * @param <X> type of exception of <code>h</code>
 * @param ds the data source where h should be executed.
 * @param h the code to execute.
 * @param continueTx only relevant if already in a transaction : if <code>true</code> the
 *        handler will just be executed and the connection won't be modified (i.e. the existing
 *        transaction will neither be committed nor rolled back) ; if <code>false</code> a save
 *        point will be used.
 * @return what h returns.
 * @throws SQLException if a problem occurs.
 * @throws X if <code>h</code> throw it.
 */
public static <T, X extends Exception> T executeAtomic(final SQLDataSource ds,
        final ConnectionHandlerNoSetup<T, X> h, final boolean continueTx) throws SQLException, X {
    return ds.useConnection(new ConnectionHandler<T, X>() {

        private Boolean autoCommit = null;
        private Savepoint savePoint = null;

        @Override
        public boolean canRestoreState() {
            return true;
        }

        @Override
        public void setup(Connection conn) throws SQLException {
            this.autoCommit = conn.getAutoCommit();
            if (this.autoCommit) {
                conn.setAutoCommit(false);
            } else if (!continueTx) {
                this.savePoint = conn.setSavepoint();
            }
        }

        @Override
        public T handle(final SQLDataSource ds) throws X, SQLException {
            return h.handle(ds);
        }

        @Override
        public void restoreState(Connection conn) throws SQLException {
            // can be null if getAutoCommit() failed, in that case nothing to do
            final boolean hasStoppedAutoCommit = Boolean.TRUE.equals(this.autoCommit);
            final boolean hasSavePoint = this.savePoint != null;
            // at most one is enough (otherwise change if/else below)
            assert !(hasStoppedAutoCommit && hasSavePoint) : "Begun a transaction and created a save point";
            if (hasStoppedAutoCommit || hasSavePoint) {
                // true if the exception was thrown by get()
                boolean getExn = true;
                try {
                    this.get();
                    getExn = false;
                    if (hasStoppedAutoCommit)
                        conn.commit();
                    // MS SQL cannot release save points
                    // http://technet.microsoft.com/en-us/library/ms378791.aspx
                    else if (ds.getSystem() != SQLSystem.MSSQL)
                        conn.releaseSavepoint(this.savePoint);
                } catch (Exception e) {
                    if (hasStoppedAutoCommit)
                        conn.rollback();
                    else
                        conn.rollback(this.savePoint);
                    // if the exception wasn't generated by get() the caller must be notified
                    if (!getExn)
                        throw new SQLException("Couldn't " + (hasSavePoint ? "release save point" : "commit"),
                                e);
                } finally {
                    if (hasStoppedAutoCommit)
                        conn.setAutoCommit(true);
                }
            }
        }
    });
}

---

From source file:kenh.xscript.database.elements.Savepoint.java

public void process(@Attribute(ATTRIBUTE_VARIABLE) String var,
        @Attribute(ATTRIBUTE_REF) java.sql.Connection conn) throws UnsupportedScriptException {
    var = StringUtils.trimToEmpty(var);

    if (StringUtils.isBlank(var)) {
        UnsupportedScriptException ex = new UnsupportedScriptException(this, "Variable name is empty.");
        throw ex;
    }/*  w  w  w .  ja va 2  s  .c  o m*/

    java.sql.Savepoint sp = null;
    try {
        sp = conn.setSavepoint();
    } catch (Exception e) {
        throw new UnsupportedScriptException(this, e);
    }

    this.saveVariable(var, sp, null);
}

---

From source file:com.redhat.victims.database.VictimsSqlDB.java

public void synchronize() throws VictimsException {
    Throwable throwable = null;/*from w  w w.j  a  va  2s  . c  o  m*/
    try {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
        Savepoint savepoint = connection.setSavepoint();

        try {
            VictimsService service = new VictimsService();
            Date since = lastUpdated();

            int removed = remove(connection, service.removed(since));
            int updated = update(connection, service.updates(since));

            if (removed > 0 || updated > 0) {
                cache.purge();
            }

            setLastUpdate(new Date());
        } catch (IOException e) {
            throwable = e;
        } catch (SQLException e) {
            throwable = e;
        } finally {
            if (throwable != null) {
                connection.rollback(savepoint);
            }
            connection.releaseSavepoint(savepoint);
            connection.commit();
            connection.close();
        }
    } catch (SQLException e) {
        throwable = e;
    }

    if (throwable != null) {
        throw new VictimsException("Failed to sync database", throwable);
    }
}

---

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

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

    try {// w  w  w  . j a  v a2s. c o m
        try {
            conn.setAutoCommit(conn.getAutoCommit());
        } catch (SQLException e) {

        }
        try {
            conn.setAutoCommit(false);
            conn.commit();
        } catch (SQLException e) {

        }

        try {
            conn.setAutoCommit(false);
            conn.rollback();
        } catch (SQLException e) {

        }

        try {
            conn.setAutoCommit(false);
            conn.rollback(conn.setSavepoint());
        } catch (SQLException e) {

        }
        try {
            conn.setAutoCommit(false);
            conn.rollback(conn.setSavepoint("test"));
        } catch (SQLException e) {

        }
        try {
            conn.setAutoCommit(false);
            conn.releaseSavepoint(conn.setSavepoint("test2"));
        } catch (SQLException e) {

        }
    } finally {
        JdbcUtil.closeQuietly(conn);

    }
}

---

From source file:com.cloud.utils.db.Transaction.java

public Savepoint setSavepoint() throws SQLException {
    _txn = true;/*from  w w  w.  ja  va  2s .  co m*/
    StackElement st = new StackElement(START_TXN, null);
    _stack.push(st);
    final Connection conn = getConnection();
    final Savepoint sp = conn.setSavepoint();
    st.ref = sp;

    return sp;
}

---

From source file:net.solarnetwork.node.dao.jdbc.test.PreparedStatementCsvReaderTests.java

private void importData(final String tableName) {
    final Map<String, ColumnCsvMetaData> columnMetaData = new LinkedHashMap<String, ColumnCsvMetaData>(8);
    jdbcTemplate.execute(new ConnectionCallback<Object>() {

        @Override//from   w ww .j a  v a 2 s  . c  o  m
        public Object doInConnection(Connection con) throws SQLException, DataAccessException {
            columnMetaData.putAll(JdbcUtils.columnCsvMetaDataForDatabaseMetaData(con.getMetaData(), tableName));
            String sql = JdbcUtils.insertSqlForColumnCsvMetaData(tableName, columnMetaData);
            PreparedStatement ps = con.prepareStatement(sql);

            Reader in;
            PreparedStatementCsvReader reader = null;
            try {
                in = new InputStreamReader(getClass().getResourceAsStream("csv-data-01.csv"), "UTF-8");
                reader = new PreparedStatementCsvReader(in, CsvPreference.STANDARD_PREFERENCE);
                String[] header = reader.getHeader(true);
                Map<String, Integer> csvColumns = JdbcUtils.csvColumnIndexMapping(header);
                CellProcessor[] cellProcessors = JdbcUtils.parsingCellProcessorsForCsvColumns(header,
                        columnMetaData);
                while (reader.read(ps, csvColumns, cellProcessors, columnMetaData)) {
                    Savepoint sp = con.setSavepoint();
                    try {
                        ps.executeUpdate();
                    } catch (SQLException e) {

                        DataAccessException dae = jdbcTemplate.getExceptionTranslator().translate("Load CSV",
                                sql, e);
                        if (dae instanceof DataIntegrityViolationException) {
                            con.rollback(sp);
                        } else {
                            throw e;
                        }
                    }
                }
            } catch (IOException e) {
                throw new DataAccessResourceFailureException("CSV encoding error", e);
            } finally {
                if (reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e) {
                        // ignore
                    }
                }
            }
            return null;
        }

    });
}

---

From source file:com.amazon.carbonado.repo.jdbc.JDBCRepository.java

/**
 * @param name name to give repository instance
 * @param isMaster when true, storables in this repository must manage
 * version properties and sequence properties
 * @param dataSource provides JDBC database connections
 * @param catalog optional catalog to search for tables -- actual meaning
 * is database independent//ww w  . j  a va  2  s  .c  o m
 * @param schema optional schema to search for tables -- actual meaning is
 * is database independent
 * @param forceStoredSequence tells the repository to use a stored sequence
 * even if the database supports native sequences
 */
@SuppressWarnings("unchecked")
JDBCRepository(AtomicReference<Repository> rootRef, String name, boolean isMaster,
        Iterable<TriggerFactory> triggerFactories, DataSource dataSource, boolean dataSourceClose,
        String catalog, String schema, Integer fetchSize, Map<String, Boolean> autoVersioningMap,
        Map<String, Boolean> suppressReloadMap, String sequenceSelectStatement, boolean forceStoredSequence,
        boolean primaryKeyCheckDisabled, SchemaResolver resolver) throws RepositoryException {
    super(name);
    if (dataSource == null) {
        throw new IllegalArgumentException("DataSource cannot be null");
    }
    mIsMaster = isMaster;
    mTriggerFactories = triggerFactories;
    mRootRef = rootRef;
    mDataSource = dataSource;
    mDataSourceClose = dataSourceClose;
    mCatalog = catalog;
    mSchema = schema;
    mFetchSize = fetchSize;
    mPrimaryKeyCheckDisabled = primaryKeyCheckDisabled;

    mAutoVersioningMap = autoVersioningMap;
    mSuppressReloadMap = suppressReloadMap;

    mResolver = resolver;

    mOpenConnections = new IdentityHashMap<Connection, Object>();
    mOpenConnectionsLock = new ReentrantLock(true);

    // Temporarily set to generic one, in case there's a problem during initialization.
    mExceptionTransformer = new JDBCExceptionTransformer();

    mTxnMgr = new JDBCTransactionManager(this);

    getLog().info("Opening repository \"" + getName() + '"');

    // Test connectivity and get some info on transaction isolation levels.
    Connection con = getConnection();
    try {
        DatabaseMetaData md = con.getMetaData();
        if (md == null || !md.supportsTransactions()) {
            throw new RepositoryException("Database does not support transactions");
        }

        mDatabaseProductName = md.getDatabaseProductName();

        boolean supportsSavepoints;
        try {
            supportsSavepoints = md.supportsSavepoints();
        } catch (AbstractMethodError e) {
            supportsSavepoints = false;
        }

        if (supportsSavepoints) {
            con.setAutoCommit(false);
            // Some JDBC drivers (HSQLDB) lie about their savepoint support.
            try {
                con.setSavepoint();
            } catch (SQLException e) {
                mLog.warn("JDBC driver for " + mDatabaseProductName + " reports supporting savepoints, but it "
                        + "doesn't appear to work: " + e);
                supportsSavepoints = false;
            } finally {
                con.rollback();
                con.setAutoCommit(true);
            }
        }

        mSupportsSavepoints = supportsSavepoints;
        mSupportsSelectForUpdate = md.supportsSelectForUpdate();
        mSupportsScrollInsensitiveReadOnly = md.supportsResultSetConcurrency(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);

        mJdbcDefaultIsolationLevel = md.getDefaultTransactionIsolation();
        mDefaultIsolationLevel = mapIsolationLevelFromJdbc(mJdbcDefaultIsolationLevel);

        mReadUncommittedLevel = selectIsolationLevel(md, IsolationLevel.READ_UNCOMMITTED);
        mReadCommittedLevel = selectIsolationLevel(md, IsolationLevel.READ_COMMITTED);
        mRepeatableReadLevel = selectIsolationLevel(md, IsolationLevel.REPEATABLE_READ);
        mSerializableLevel = selectIsolationLevel(md, IsolationLevel.SERIALIZABLE);
    } catch (SQLException e) {
        throw toRepositoryException(e);
    } finally {
        try {
            closeConnection(con);
        } catch (SQLException e) {
            // Don't care.
        }
    }

    mSupportStrategy = JDBCSupportStrategy.createStrategy(this);
    if (forceStoredSequence) {
        mSupportStrategy.setSequenceSelectStatement(null);
    } else if (sequenceSelectStatement != null && sequenceSelectStatement.length() > 0) {
        mSupportStrategy.setSequenceSelectStatement(sequenceSelectStatement);
    }
    mSupportStrategy.setForceStoredSequence(forceStoredSequence);
    mExceptionTransformer = mSupportStrategy.createExceptionTransformer();

    getLog().info("Opened repository \"" + getName() + '"');

    setAutoShutdownEnabled(true);
}

---

From source file:mom.trd.opentheso.bdd.helper.RelationsHelper.java

/**
 * Cette fonction permet d'ajouter une relation MT ou domaine  un concept
 *
 * @param conn//from   ww  w  .ja  v a2 s .  c  om
 * @param idConcept
 * @param idGroup
 * @param idThesaurus
 * @return boolean
 */
public boolean setRelationMT(Connection conn, String idConcept, String idGroup, String idThesaurus) {

    Statement stmt;
    boolean status = false;
    String query;
    Savepoint savepoint = null;

    try {
        // Get connection from pool
        savepoint = conn.setSavepoint();
        try {
            stmt = conn.createStatement();
            try {

                /*    if (!new RelationsHelper().addRelationHistorique(conn, idConcept, idThesaurus, idConcept, "TT", idUser, "DEL")) {
                return false;
                }*/
                query = "UPDATE concept set" + " id_group = '" + idGroup + "'," + " modified = now()"
                        + " WHERE id_concept ='" + idConcept + "'" + " AND id_thesaurus = '" + idThesaurus
                        + "'";

                stmt.executeUpdate(query);
                status = true;
            } finally {
                stmt.close();
            }
        } finally {
            //    conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        if (sqle.getSQLState().equalsIgnoreCase("23505")) {
            try {
                if (savepoint != null) {
                    conn.rollback(savepoint);
                    status = true;
                }
            } catch (SQLException ex) {
                Logger.getLogger(RelationsHelper.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            log.error("Error while adding relation MT of Concept : " + idConcept, sqle);
        }
    }
    return status;
}

---

• «
• 1
• 2
• »