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() throws SQLException;

Source Link

Document

Undoes all changes made in the current transaction and releases any database locks currently held by this Connection object.

Usage

From source file:chh.utils.db.source.common.JdbcClient.java

public void executeInsertQuery(String query, List<List<Column>> columnLists) {
    Connection connection = null;
    try {/*from  www . j av a  2s.  co m*/
        connection = connectionProvider.getConnection();
        boolean autoCommit = connection.getAutoCommit();
        if (autoCommit) {
            connection.setAutoCommit(false);
        }

        LOG.debug("Executing query {}", query);

        PreparedStatement preparedStatement = connection.prepareStatement(query);
        if (queryTimeoutSecs > 0) {
            preparedStatement.setQueryTimeout(queryTimeoutSecs);
        }

        for (List<Column> columnList : columnLists) {
            setPreparedStatementParams(preparedStatement, columnList);
            preparedStatement.addBatch();
        }

        int[] results = preparedStatement.executeBatch();
        if (Arrays.asList(results).contains(Statement.EXECUTE_FAILED)) {
            connection.rollback();
            throw new RuntimeException(
                    "failed at least one sql statement in the batch, operation rolled back.");
        } else {
            try {
                connection.commit();
            } catch (SQLException e) {
                throw new RuntimeException("Failed to commit insert query " + query, e);
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("Failed to execute insert query " + query, e);
    } finally {
        closeConnection(connection);
    }
}

From source file:io.kahu.hawaii.util.call.sql.AbortableQuery.java

@Override
protected void executeInternally(ResponseHandler responseHandler, Response response) throws ServerException {
    SqlParameterSource paramSource = new MapSqlParameterSource(params);
    PreparedStatementCreator psc = getPreparedStatementCreator(sql, paramSource);

    Connection connection = DataSourceUtils.getConnection(dataSource);
    try {//from  w  w  w .  ja  v  a  2  s .  co  m
        preparedStatement = psc.createPreparedStatement(connection);

        switch (callType) {
        case INSERT:
            preparedStatement.executeUpdate();
            new UpdateIdResponseHandler().addToResponse(preparedStatement, response);
            break;

        case DELETE:
            // fall though
        case UPDATE:
            response.set(preparedStatement.executeUpdate());
            break;

        case SELECT:
            ResultSet resultSet = preparedStatement.executeQuery();
            responseHandler.addToResponse(resultSet, response);
            break;

        default:
            throw new ServerException(ServerError.ILLEGAL_ARGUMENT, "Unknown call type '" + callType + "'.");
        }
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (Throwable t) {
            //
        }
        if (!aborted) {
            response.setStatus(ResponseStatus.BACKEND_FAILURE, e);
        }
        throw new ServerException(ServerError.UNEXPECTED_EXCEPTION, e);
    } finally {
        DataSourceUtils.releaseConnection(connection, dataSource);
    }
}

From source file:com.cloudera.sqoop.manager.PostgresqlTest.java

@Before
public void setUp() {
    super.setUp();

    LOG.debug("Setting up another postgresql test: " + CONNECT_STRING);

    SqoopOptions options = new SqoopOptions(CONNECT_STRING, TABLE_NAME);
    options.setUsername(DATABASE_USER);//  ww  w. j ava  2 s.  co  m

    ConnManager manager = null;
    Connection connection = null;
    Statement st = null;

    try {
        manager = new PostgresqlManager(options);
        connection = manager.getConnection();
        connection.setAutoCommit(false);
        st = connection.createStatement();

        // create the database table and populate it with data.

        try {
            // Try to remove the table first. DROP TABLE IF EXISTS didn't
            // get added until pg 8.3, so we just use "DROP TABLE" and ignore
            // any exception here if one occurs.
            st.executeUpdate("DROP TABLE " + TABLE_NAME);
        } catch (SQLException e) {
            LOG.info("Couldn't drop table " + TABLE_NAME + " (ok)");
            LOG.info(e.toString());
            // Now we need to reset the transaction.
            connection.rollback();
        }

        st.executeUpdate("CREATE TABLE " + TABLE_NAME + " (" + "id INT NOT NULL PRIMARY KEY, "
                + "name VARCHAR(24) NOT NULL, " + "start_date DATE, " + "salary FLOAT, " + "dept VARCHAR(32))");

        st.executeUpdate(
                "INSERT INTO " + TABLE_NAME + " VALUES(" + "1,'Aaron','2009-05-14',1000000.00,'engineering')");
        st.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES(" + "2,'Bob','2009-04-20',400.00,'sales')");
        st.executeUpdate("INSERT INTO " + TABLE_NAME + " VALUES(" + "3,'Fred','2009-01-23',15.00,'marketing')");
        connection.commit();
    } catch (SQLException sqlE) {
        LOG.error("Encountered SQL Exception: " + sqlE);
        sqlE.printStackTrace();
        fail("SQLException when running test setUp(): " + sqlE);
    } finally {
        try {
            if (null != st) {
                st.close();
            }

            if (null != manager) {
                manager.close();
            }
        } catch (SQLException sqlE) {
            LOG.warn("Got SQLException when closing connection: " + sqlE);
        }
    }

    LOG.debug("setUp complete.");
}

From source file:com.cloudera.sqoop.manager.PostgresqlImportTest.java

public void setUpData(String tableName, String schema, boolean nullEntry) {
    SqoopOptions options = new SqoopOptions(CONNECT_STRING, tableName);
    options.setUsername(DATABASE_USER);/*from  w w w .ja v  a2  s . c  o  m*/
    options.setPassword(PASSWORD);

    ConnManager manager = null;
    Connection connection = null;
    Statement st = null;

    try {
        manager = new PostgresqlManager(options);
        connection = manager.getConnection();
        connection.setAutoCommit(false);
        st = connection.createStatement();

        // Create schema if not exists in dummy way (always create and ignore
        // errors.
        try {
            st.executeUpdate("CREATE SCHEMA " + manager.escapeTableName(schema));
            connection.commit();
        } catch (SQLException e) {
            LOG.info(
                    "Couldn't create schema " + schema + " (is o.k. as long as" + "the schema already exists.");
            connection.rollback();
        }

        String fullTableName = manager.escapeTableName(schema) + "." + manager.escapeTableName(tableName);
        LOG.info("Creating table: " + fullTableName);

        try {
            // Try to remove the table first. DROP TABLE IF EXISTS didn't
            // get added until pg 8.3, so we just use "DROP TABLE" and ignore
            // any exception here if one occurs.
            st.executeUpdate("DROP TABLE " + fullTableName);
        } catch (SQLException e) {
            LOG.info("Couldn't drop table " + schema + "." + tableName + " (ok)");
            // Now we need to reset the transaction.
            connection.rollback();
        }

        st.executeUpdate("CREATE TABLE " + fullTableName + " (" + manager.escapeColName("id")
                + " INT NOT NULL PRIMARY KEY, " + manager.escapeColName("name") + " VARCHAR(24) NOT NULL, "
                + manager.escapeColName("start_date") + " DATE, " + manager.escapeColName("Salary") + " FLOAT, "
                + manager.escapeColName("Fired") + " BOOL, " + manager.escapeColName("dept") + " VARCHAR(32))");

        st.executeUpdate("INSERT INTO " + fullTableName
                + " VALUES(1,'Aaron','2009-05-14',1000000.00,TRUE,'engineering')");
        st.executeUpdate("INSERT INTO " + fullTableName + " VALUES(2,'Bob','2009-04-20',400.00,TRUE,'sales')");
        st.executeUpdate(
                "INSERT INTO " + fullTableName + " VALUES(3,'Fred','2009-01-23',15.00,FALSE,'marketing')");
        if (nullEntry) {
            st.executeUpdate("INSERT INTO " + fullTableName + " VALUES(4,'Mike',NULL,NULL,NULL,NULL)");

        }
        connection.commit();
    } catch (SQLException sqlE) {
        LOG.error("Encountered SQL Exception: " + sqlE);
        sqlE.printStackTrace();
        fail("SQLException when running test setUp(): " + sqlE);
    } finally {
        try {
            if (null != st) {
                st.close();
            }

            if (null != manager) {
                manager.close();
            }
        } catch (SQLException sqlE) {
            LOG.warn("Got SQLException when closing connection: " + sqlE);
        }
    }

    LOG.debug("setUp complete.");
}

From source file:com.concursive.connect.web.modules.badges.dao.BadgeLogoFile.java

public boolean insert(Connection db) throws SQLException {
    boolean result = false;
    // The required linkModuleId
    linkModuleId = Constants.BADGE_FILES;
    // Determine if the database is in auto-commit mode
    boolean doCommit = false;
    try {//from  w  w w  .j  ava2 s  .  c  o m
        if (doCommit = db.getAutoCommit()) {
            db.setAutoCommit(false);
        }
        // Insert the record
        result = super.insert(db);
        // Update the referenced pointer
        if (result) {
            int i = 0;
            PreparedStatement pst = db
                    .prepareStatement("UPDATE badge " + "SET logo_id = ? " + "WHERE badge_id = ? ");
            pst.setInt(++i, id);
            pst.setInt(++i, linkItemId);
            int count = pst.executeUpdate();
            result = (count == 1);
        }
        if (doCommit) {
            db.commit();
        }
    } catch (Exception e) {
        if (doCommit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (doCommit) {
            db.setAutoCommit(true);
        }
    }
    return result;
}

From source file:com.che.software.testato.domain.dao.jdbc.impl.ScriptDAO.java

/**
 * Creates the scripts for a given hierarchy.
 * //from w w w  . j  ava2s . c  om
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param hierarchyId the hierarchy id.
 * @param scripts the scripts to create.
 * @since July, 2011.
 * @throws ScriptCreationDAOException if an error occurs during the
 *         creation.
 */
@Override
public void createScriptsFromHierarchy(int hierarchyId, List<ScriptCreation> scripts)
        throws ScriptCreationDAOException {
    LOGGER.debug("createScriptsFromHierarchy(" + hierarchyId + "," + scripts.size() + " scripts).");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        connection.setAutoCommit(false);
        for (ScriptCreation script : scripts) {
            getQueryRunner().update(connection,
                    "INSERT INTO script(script_id, hierarchy_id, label, depth) VALUES(nextval('script_seq'),?,'', ?) ",
                    new Object[] { hierarchyId, 1 });
            Integer createdScript = (Integer) getQueryRunner().query(connection,
                    "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId"));
            createItems(connection, script.getScriptArrows(), createdScript, 1, hierarchyId);
        }
        connection.commit();
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            throw new ScriptCreationDAOException(e1);
        }
        throw new ScriptCreationDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

From source file:edu.clemson.cs.nestbed.server.adaptation.sql.MoteDeploymentConfigurationSqlAdapter.java

public MoteDeploymentConfiguration addMoteDeploymentConfiguration(int projectDepConfID, int moteID,
        int programID, int radioPowerLevel) throws AdaptationException {
    MoteDeploymentConfiguration mdc = null;
    Connection connection = null;
    Statement statement = null;//from  w  ww  .  java  2  s.  c o m
    ResultSet resultSet = null;

    try {
        String query = "INSERT INTO MoteDeploymentConfigurations(" + "projectDeploymentConfigurationID, "
                + "moteID, programID, radioPowerLevel) VALUES (" + projectDepConfID + ", " + moteID + ", "
                + programID + ", " + radioPowerLevel + ")";

        connection = DriverManager.getConnection(CONN_STR);
        statement = connection.createStatement();
        statement.executeUpdate(query);

        query = "SELECT * from MoteDeploymentConfigurations WHERE " + "projectDeploymentConfigurationID = "
                + projectDepConfID + " AND moteID = " + moteID;

        resultSet = statement.executeQuery(query);

        if (!resultSet.next()) {
            connection.rollback();
            String msg = "Unable to select newly added config.";
            log.error(msg);
            ;
            throw new AdaptationException(msg);
        }

        mdc = getMoteDeploymentConfiguration(resultSet);
        connection.commit();
    } catch (SQLException ex) {
        try {
            connection.rollback();
        } catch (Exception e) {
        }

        String msg = "SQLException in addMoteDeploymentConfiguration";
        log.error(msg, ex);
        throw new AdaptationException(msg, ex);
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
        }
        try {
            statement.close();
        } catch (Exception ex) {
        }
        try {
            connection.close();
        } catch (Exception ex) {
        }
    }

    return mdc;
}

From source file:com.concursive.connect.web.modules.profile.dao.ProjectCategoryLogoFile.java

public boolean insert(Connection db) throws SQLException {
    boolean result = false;
    // The required linkModuleId
    linkModuleId = Constants.PROJECT_CATEGORY_FILES;
    // Determine if the database is in auto-commit mode
    boolean doCommit = false;
    try {/*from  w w w.j  av  a  2s .  c om*/
        if (doCommit = db.getAutoCommit()) {
            db.setAutoCommit(false);
        }
        // Insert the record
        result = super.insert(db);
        // Update the referenced pointer
        if (result) {
            int i = 0;
            PreparedStatement pst = db.prepareStatement(
                    "UPDATE lookup_project_category " + "SET logo_id = ? " + "WHERE code = ? ");
            pst.setInt(++i, id);
            pst.setInt(++i, linkItemId);
            int count = pst.executeUpdate();
            result = (count == 1);
        }
        if (doCommit) {
            db.commit();
        }
    } catch (Exception e) {
        if (doCommit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (doCommit) {
            db.setAutoCommit(true);
        }
    }
    return result;
}

From source file:net.gcolin.simplerepo.search.SearchController.java

public void add(Model model, ResultType type) throws SQLException {
    Connection connection = null;
    try {//from w  w  w  . j  av a 2 s. co  m
        connection = datasource.getConnection();
        connection.setAutoCommit(false);
        Long versionId = new QueryRunner().query(connection,
                "select v.id from artifactversion v inner join artifact a on a.id = v.artifact_id "
                        + "where a.groupId = ? and a.artifactId = ? and v.version = ?",
                getLong, model.getGroupId(), model.getArtifactId(), model.getVersion());
        new QueryRunner().update(connection,
                "insert into artifacttype(version_id,packaging,classifier) VALUES (?,?,?)", versionId,
                type.getName(), type.getClassifier());
        connection.commit();
    } catch (SQLException ex) {
        connection.rollback();
        throw ex;
    } finally {
        DbUtils.close(connection);
    }
}

From source file:edu.clemson.cs.nestbed.server.adaptation.sql.ProgramMessageSymbolSqlAdapter.java

public ProgramMessageSymbol deleteProgramMessageSymbol(int id) throws AdaptationException {
    ProgramMessageSymbol pmt = null;//  www  .j av  a 2 s  .  co  m
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {
        String query = "SELECT * FROM ProgramMessageSymbols " + "WHERE id = " + id;

        connection = DriverManager.getConnection(CONN_STR);
        statement = connection.createStatement();
        resultSet = statement.executeQuery(query);

        if (!resultSet.next()) {
            String msg = "Attempt to delete program message type " + "failed.";
            log.error(msg);
            ;
            throw new AdaptationException(msg);
        }

        pmt = getProgramMessageSymbol(resultSet);
        query = "DELETE FROM ProgramMessageSymbols " + "WHERE id = " + id;

        statement.executeUpdate(query);
        connection.commit();
    } catch (SQLException ex) {
        try {
            connection.rollback();
        } catch (Exception e) {
        }

        String msg = "SQLException in deleteProgramMessageSymbol";
        log.error(msg, ex);
        throw new AdaptationException(msg, ex);
    } finally {
        try {
            resultSet.close();
        } catch (Exception ex) {
        }
        try {
            statement.close();
        } catch (Exception ex) {
        }
        try {
            connection.close();
        } catch (Exception ex) {
        }
    }

    return pmt;
}