Example usage for java.sql Connection rollback

List of usage examples for java.sql Connection rollback

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

Introduction

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

Prototype

void rollback(Savepoint savepoint) throws SQLException;

Source Link

Document

Undoes all changes made after the given Savepoint object was set.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);//  w  w w . j  av  a 2  s . c o m
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myURL CHAR);");
    st.executeUpdate("insert into survey(id) values(01)");
    st.executeUpdate("insert into survey(id) values(02)");

    Savepoint mySavepoint = conn.setSavepoint("MYSAVEPOINT");

    st.executeUpdate("insert into survey(id) values(03)");
    conn.commit();

    conn.rollback(mySavepoint);

    st.close();
    conn.close();
}

From source file:SetSavepoint.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    try {//from  ww  w  . j  a va 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:MainClass.java

public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;//  www .j  a va2 s  .  com
    try {
        Class.forName("org.hsqldb.jdbcDriver").newInstance();
        String url = "jdbc:hsqldb:hsqldb\\demoDatabase";
        connection = DriverManager.getConnection(url, "username", "password");
        connection.setAutoCommit(false);

        statement = connection.createStatement();

        String update1 = "UPDATE employees SET email = 'a@b.com' WHERE email = 'a@a.com'";
        statement.executeUpdate(update1);
        Savepoint savepoint1 = connection.setSavepoint("savepoint1");

        String update2 = "UPDATE employees SET email = 'b@b.com' WHERE email = 'b@c.com'";
        statement.executeUpdate(update2);
        Savepoint savepoint2 = connection.setSavepoint("savepoint2");

        String update3 = "UPDATE employees SET email = 'c@c.com' WHERE email = 'c@d.com'";
        statement.executeUpdate(update3);
        Savepoint savepoint3 = connection.setSavepoint("savepoint3");

        String update4 = "UPDATE employees SET email = 'd@d.com' WHERE email = 'd@e.com'";
        statement.executeUpdate(update4);
        Savepoint savepoint4 = connection.setSavepoint("savepoint4");

        String update5 = "UPDATE employees SET email = 'e@e.com' WHERE email = 'e@f.com'";
        statement.executeUpdate(update5);
        Savepoint savepoint5 = connection.setSavepoint("savepoint5");

        connection.rollback(savepoint3);
        connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
    }
}

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

/**
 * Rollback to the stored savepoint.//  w w  w  .ja v  a 2s.c  o  m
 * <p>
 * If {@link Connection#getAutoCommit()} is true, then this operation does nothing.
 * </p>
 *
 * @param svp the savepoint (if null, no rollback occurs).
 * @param sqlConnection the database connection.
 */
public static void rollback(Savepoint svp, Connection sqlConnection) {
    if (sqlConnection == null) {
        return;
    }
    try {
        if (sqlConnection.getAutoCommit()) {
            return;
        }
        if (svp != null) {
            sqlConnection.rollback(svp);
        }
    } catch (Exception ignoredIntentionally) {
    }
}

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

public void process(@Attribute(ATTRIBUTE_REF) java.sql.Connection conn,
        @Attribute(ATTRIBUTE_SAVE_POINT) java.sql.Savepoint savepoint) throws UnsupportedScriptException {
    try {/*from ww w.j av  a  2s .c om*/
        if (!conn.isClosed()) {
            if (!conn.getAutoCommit()) {
                if (savepoint != null) {
                    conn.rollback(savepoint);
                } else {
                    conn.rollback();
                }
            }
        }
    } catch (Exception e) {
        throw new UnsupportedScriptException(this, e);
    }
}

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

public void synchronize() throws VictimsException {
    Throwable throwable = null;/*from   w w  w  .ja  v  a  2s  . co 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:org.apache.eagle.alert.metadata.impl.JdbcMetadataHandler.java

public <T> OpResult addOrReplace(String clzName, T t) {
    String tb = getTableName(clzName);
    OpResult result = new OpResult();
    Savepoint savepoint = null;/*from  ww w  .  ja va  2  s  . c  o m*/
    String key = null;
    String value = null;
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        key = MetadataUtils.getKey(t);
        value = mapper.writeValueAsString(t);
        connection.setAutoCommit(false);
        savepoint = connection.setSavepoint("insertEntity");
        result = executeUpdate(connection, String.format(INSERT_STATEMENT, tb), key, value);
        connection.commit();
    } catch (SQLException e) {
        LOG.warn("fail to insert entity due to {}, and try to updated instead", e.getMessage());
        if (connection != null) {
            LOG.info("Detected duplicated entity");
            try {
                connection.rollback(savepoint);
                executeUpdate(connection, String.format(UPDATE_STATEMENT, tb), key, value);
                connection.commit();
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                LOG.warn("Rollback failed", e1);
            }
        }
    } catch (JsonProcessingException e) {
        LOG.error("Got JsonProcessingException: {}", e.getMessage(), e.getCause());
        result.code = OpResult.FAILURE;
        result.message = e.getMessage();
    } finally {
        closeResource(null, null, connection);
    }
    return result;
}

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  w w .j a va  2s  .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:mom.trd.opentheso.bdd.helper.RelationsHelper.java

/**
 * Cette fonction permet d'ajouter une relation MT ou domaine  un concept
 *
 * @param conn/*from   w w  w  .  j ava2 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;
}

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

/**
 * Cette fonction permet de rajouter une relation type Groupe ou domaine 
 * un concept//from  www. java 2s .c om
 *
 * @param conn
 * @param idConcept
 * @param idGroup
 * @param idThesaurus
 * @param idUser
 * @return boolean
 */
public boolean addRelationMT(Connection conn, String idConcept, String idThesaurus, String idGroup,
        int idUser) {

    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, "MT", idUser, "ADD")) {
                return false;
                }*/
                query = "Insert into concept" + "(id_concept, id_thesaurus, id_ark, top_concept, id_group)"
                        + " values (" + "'" + idConcept + "'" + ",'" + idThesaurus + "'" + ",''," + "false"
                        + ",'" + idGroup + "')";

                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;
}