Example usage for java.sql Connection 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:com.concursive.connect.web.modules.common.social.rating.dao.Rating.java

/**
 * Deletes just a specific user's rating, and updates the parent table with a proper calculation
 *
 * @param db//from   ww w .  jav a  2 s.  c om
 * @param userId
 * @param objectId
 * @param table
 * @param uniqueField
 * @throws SQLException
 */
public static synchronized void delete(Connection db, int userId, int objectId, String table,
        String uniqueField) throws SQLException {
    boolean commit = false;
    try {
        commit = db.getAutoCommit();
        if (commit) {
            db.setAutoCommit(false);
        }

        // Get the project's rating
        int ratingCount = queryObjectRatingCount(db, objectId, table, uniqueField);

        // Get the user's rating
        int thisRating = queryUserRating(db, userId, objectId, table, uniqueField);

        // Delete the user's rating
        PreparedStatement pst = db.prepareStatement(
                "DELETE FROM " + table + "_rating " + "WHERE " + uniqueField + " = ? " + "AND enteredby = ? ");
        pst.setInt(1, objectId);
        pst.setInt(2, userId);
        int deleteCount = pst.executeUpdate();
        pst.close();

        if (deleteCount > 0 && thisRating != INAPPROPRIATE_COMMENT) {
            // Update the parent table's rating information
            // NOTE: make sure not to divide by 0
            pst = db.prepareStatement("UPDATE " + table + " "
                    + "SET rating_count = rating_count - ?, rating_value = rating_value - ?, "
                    + (ratingCount == 0 ? "rating_avg = 0 "
                            : "rating_avg = ((rating_value - ?) / (rating_count - ?)) ")
                    + "WHERE " + uniqueField + " = ? ");
            int i = 0;
            pst.setInt(++i, 1);
            pst.setInt(++i, thisRating);
            if (ratingCount > 1) {
                pst.setInt(++i, thisRating);
                pst.setInt(++i, 1);
            }
            pst.execute();
            pst.close();
        }
    } catch (Exception e) {
        if (commit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (commit) {
            db.setAutoCommit(true);
        }
    }
}

---

From source file:dao.CollModeratorDaoDb.java

/**
 * Add a member to the collabrum moderator list
 * @param colIdList the list of the collabrumid's
 * @param member member who is to be added as moderator 
 * @param userId admin of collabrum//from   w  w w  .j  av  a2 s.  co m
 * @param userLogin login of the admin of collabrum
 * @throws BaseDaoException If we have a problem interpreting the data or the data is missing or incorrect
 */
public void addModerator(ArrayList colIdList, String member, String userId, String userLogin)
        throws BaseDaoException {

    if ((colIdList == null) || RegexStrUtil.isNull(userId) || RegexStrUtil.isNull(member)
            || RegexStrUtil.isNull(userLogin)) {
        throw new BaseDaoException("params are null");
    }

    Hdlogin hdlogin = getLoginid(member);
    String memberId = hdlogin.getValue(DbConstants.LOGIN_ID);
    if (RegexStrUtil.isNull(memberId)) {
        throw new BaseDaoException("memberId is null");
    }

    /**
     *  check if this user has the permission to add the moderator - diaryAdmin or Organizer
     */
    for (int i = 0; i < colIdList.size(); i++) {
        if (!isOrganizer((String) colIdList.get(i), userLogin, userId)) {
            throw new BaseDaoException("User does not have permission to add moderators, collabrumId ="
                    + colIdList.get(i) + " userId = " + userId);
        }
    }

    /**
     *  Get scalability datasource for collabrum, colladmin (not partitioned)
     */
    String sourceName = scalabilityManager.getWriteZeroScalability();
    ds = scalabilityManager.getSource(sourceName);
    if (ds == null) {
        throw new BaseDaoException("ds null, addModerator() " + sourceName + " userId = " + userId);
    }

    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        addModeratorQuery.run(conn, colIdList, memberId);
        for (int i = 0; i < colIdList.size(); i++) {
            if (!isMember((String) colIdList.get(i), memberId)) {
                deleteBlockedMember.run(conn, (String) colIdList.get(i), memberId);
                addMemberQuery.run(conn, (String) colIdList.get(i), memberId);
            }
        }
    } catch (Exception e) {
        try {
            conn.rollback();
        } catch (Exception e1) {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                throw new BaseDaoException("conn.close() error, addModerator, memberid = " + memberId, e2);
            }
            throw new BaseDaoException("error occured while addModertor, memberId = " + memberId, e1);
        }
    }

    // connection commit
    try {
        conn.commit();
    } catch (Exception e3) {
        throw new BaseDaoException(" commit() exception, for addModerator() " + "memberId = " + memberId, e3);
    }

    try {
        if (conn != null) {
            conn.setAutoCommit(true);
            conn.close();
        }
    } catch (Exception e4) {
        throw new BaseDaoException(" conn.close() exception, for commit(), addModerator() " + "memberId = "
                + memberId + " userId = " + userId, e4);
    }

    /** Jboss methods
     * fqn - full qualified name
     * check if the userpage already set in the cache
     * If it exists, return the userpage from the cache.
     */
    Fqn fqn = cacheUtil.fqn(DbConstants.MEM_AS_MODERATOR_LIST);
    if (treeCache.exists(fqn, member)) {
        treeCache.remove(fqn, member);
    }

    fqn = cacheUtil.fqn(DbConstants.MEM_AS_ORGANIZER_LIST);
    if (treeCache.exists(fqn, member)) {
        treeCache.remove(fqn, member);
    }

    fqn = cacheUtil.fqn(DbConstants.USER_PAGE);
    if (treeCache.exists(fqn, member)) {
        treeCache.remove(fqn, member);
    }

    /**
     * remove this from the userlogin also
     */
    fqn = cacheUtil.fqn(DbConstants.MEM_AS_MODERATOR_LIST);
    if (treeCache.exists(fqn, userLogin)) {
        treeCache.remove(fqn, userLogin);
    }

    StringBuffer sb = new StringBuffer();
    for (int i = 0; i < colIdList.size(); i++) {
        sb.append(colIdList.get(i));
        sb.append("-");
        sb.append(memberId);
        fqn = cacheUtil.fqn(DbConstants.ORGANIZER);
        if (treeCache.exists(fqn, sb.toString())) {
            treeCache.remove(fqn, sb.toString());
        }
        sb.delete(0, sb.length());

        fqn = cacheUtil.fqn(DbConstants.COLLABRUM);
        if (treeCache.exists(fqn, colIdList.get(i))) {
            treeCache.remove(fqn, colIdList.get(i));
        }
    }

    fqn = cacheUtil.fqn(DbConstants.BLOCKED_COLLABRUM_LIST);
    Object obj = treeCache.get(fqn, memberId);
    if (treeCache.exists(fqn, memberId)) {
        treeCache.remove(fqn, memberId);
    }
}

---

From source file:com.wso2telco.dep.operatorservice.dao.BlackListWhiteListDAO.java

/**
 * blacklist list given msisdns/*from  w w  w.ja v a  2  s .c o m*/
 *
 * @param msisdns
 * @param apiID
 * @param apiName
 * @param userID
 * @throws Exception
 */
public void blacklist(MSISDNValidationDTO msisdns, final String apiID, final String apiName,
        final String userID) throws Exception {

    log.debug("BlackListWhiteListDAO.blacklist triggerd MSISDN["
            + StringUtils.join(msisdns.getValidProcessed().toArray(), ",") + "] apiID:" + apiID + " apiName:"
            + apiName + " userID:" + userID);

    StringBuilder sql = new StringBuilder();
    sql.append(" INSERT INTO ");
    sql.append(OparatorTable.BLACKLIST_MSISDN.getTObject());
    sql.append("(PREFIX,MSISDN,API_ID,API_NAME,USER_ID,VALIDATION_REGEX)");
    sql.append(" VALUES (?, ?, ?, ?, ?, ?)");

    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        ps = conn.prepareStatement(sql.toString());

        conn.setAutoCommit(false);

        for (MsisdnDTO msisdn : msisdns.getValidProcessed()) {

            ps.setString(1, msisdn.getPrefix());
            ps.setString(2, msisdn.getDigits());
            ps.setString(3, apiID);
            ps.setString(4, apiName);
            ps.setString(5, userID);
            ps.setString(6, msisdns.getValidationRegex());
            ps.addBatch();
        }

        ps.executeBatch();
        conn.commit();

    } catch (Exception e) {
        if (conn != null) {
            conn.rollback();
        }
        throw e;
    } finally {
        DbUtils.closeAllConnections(ps, conn, null);
    }

}

---

From source file:com.wso2telco.dep.operatorservice.dao.BlackListWhiteListDAO.java

/**
 * when the subscription id is known//  ww w  .j a  va2 s  .c om
 *
 * @param userMSISDNs
 * @param subscriptionId
 * @param apiID
 * @param applicationID
 * @throws SQLException
 * @throws Exception
 */
public void whitelist(MSISDNValidationDTO msisdns, String subscriptionId, String apiID, String applicationID)
        throws Exception {

    StringBuilder sql = new StringBuilder();
    sql.append("INSERT INTO ");
    sql.append(OparatorTable.SUBSCRIPTION_WHITELIST.getTObject());
    sql.append(" (subscriptionID, prefix, msisdn, api_id, application_id, validation_regex)");
    sql.append(" VALUES (?,?,?,?,?,?);");

    Connection conn = null;
    PreparedStatement ps = null;

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB);
        ps = conn.prepareStatement(sql.toString());

        conn.setAutoCommit(false);
        for (MsisdnDTO msisdn : msisdns.getValidProcessed()) {

            ps.setString(1, subscriptionId);
            ps.setString(2, msisdn.getPrefix());
            ps.setString(3, msisdn.getDigits());
            ps.setString(4, apiID);
            ps.setString(5, applicationID);
            ps.setString(6, msisdns.getValidationRegex());

            ps.addBatch();
        }
        ps.executeBatch();
        conn.commit();

    } catch (Exception e) {
        if (conn != null) {
            conn.rollback();
        }
        log.error("", e);
        throw e;
    } finally {
        DbUtils.closeAllConnections(ps, conn, null);
    }

}

---

From source file:edu.uga.cs.fluxbuster.db.PostgresDBInterface.java

/**
 * Executes a prepared statement with a result.
 * //from  www . java 2 s .  c om
 * @param con the connection to the database
 * @param stmt the prepared statement to execute
 * @return the result of the query
 */
private ResultSet executePreparedStatementWithResult(Connection con, PreparedStatement stmt) {
    ResultSet retval = null;
    try {
        con.setAutoCommit(false);
        retval = stmt.executeQuery();
        con.commit();
    } catch (SQLException e) {
        if (log.isErrorEnabled()) {
            log.error("", e);
        }
        if (con != null) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                if (log.isErrorEnabled()) {
                    log.error("Error during rollback.", e1);
                }
            }
        }
    } finally {
        try {
            if (con != null && !con.isClosed()) {
                con.setAutoCommit(true);
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("Error setting auto commit.", e);
            }
        }
    }
    return retval;
}

---

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

public ProgramMessageSymbol addProgramMessageSymbol(int programID, String name, byte[] bytecode)
        throws AdaptationException {
    ProgramMessageSymbol programMessageSymbol = null;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement statement = null;//from   www  . ja va 2s  . co m
    ResultSet resultSet = null;
    InputStream stream = new ByteArrayInputStream(bytecode);
    try {
        String query = "INSERT INTO ProgramMessageSymbols(programID, name, " + "bytecode) VALUES ( ?, ?, ? )";

        connection = DriverManager.getConnection(CONN_STR);
        preparedStatement = connection.prepareStatement(query);

        preparedStatement.setInt(1, programID);
        preparedStatement.setString(2, name);
        preparedStatement.setBinaryStream(3, stream, bytecode.length);

        log.info("INSERT INTO ProgramMessageSymbols(programID, name, " + "bytecode) VALUES (" + programID
                + ", '" + name + "', " + "<bytecode>)");
        preparedStatement.executeUpdate();

        statement = connection.createStatement();
        query = "SELECT * FROM ProgramMessageSymbols WHERE " + "programID =  " + programID + " AND "
                + "name      = '" + name + "'";
        resultSet = statement.executeQuery(query);

        if (!resultSet.next()) {
            connection.rollback();
            String msg = "Attempt to add program message symbol failed.";
            log.error(msg);
            ;
            throw new AdaptationException(msg);
        }

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

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

    return programMessageSymbol;
}

---

From source file:com.l2jserver.service.database.sql.AbstractSQLDatabaseService.java

@Override
public int transaction(TransactionExecutor executor) {
    Preconditions.checkNotNull(executor, "executor");
    try {//from  w  w w . j  av a2 s  .c o m
        final Connection conn = dataSource.getConnection();
        log.debug("Executing transaction {} with {}", executor, conn);
        try {
            conn.setAutoCommit(false);

            transaction.set(new TransactionIsolatedConnection(conn));
            final int rows = executor.perform();

            conn.commit();
            return rows;
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            transaction.set(null);
            transaction.remove();
            conn.setAutoCommit(true);
            conn.close();
        }
    } catch (DatabaseException e) {
        throw e;
    } catch (Throwable e) {
        throw new DatabaseException(e);
    }
}

---

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

/**
 * Creates the matrix results for a given iteration assignment. The related
 * matrix must be completed before calling this method.
 * /* w  w w .  j  av a 2  s .  c o m*/
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param iterationId the given iteration id.
 * @since August, 2011.
 * @throws MatrixResultCreationDAOException if an error occurs during the
 *         creation.
 */
@Override
public void createMatrixResults(int iterationId) throws MatrixResultCreationDAOException {
    LOGGER.debug("createMatrixResults(" + iterationId + ").");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        connection.setAutoCommit(false);
        for (MatrixResult result : getQueryRunner().query(connection,
                "SELECT first_script AS scriptId, iteration_assignment_id AS iterationAssignmentId, ((SUM((value/( SELECT SUM(value) FROM comparisonMatrixItem WHERE iteration_assignment_id = ? AND second_script = base.second_script GROUP BY second_script ORDER BY second_script ))))/COUNT(*))::numeric(15,2) AS percentage FROM comparisonMatrixItem base WHERE iteration_assignment_id = ? GROUP BY first_script,iteration_assignment_id ORDER BY first_script ",
                new BeanListHandler<MatrixResult>(MatrixResult.class),
                new Object[] { iterationId, iterationId })) {
            getQueryRunner().update(connection,
                    "INSERT INTO iteration_assignment_source_script(script_id, iteration_assignment_id, percentage) VALUES(?, ?, ?) ",
                    new Object[] { result.getScriptId(), result.getIterationAssignmentId(),
                            result.getPercentage() });
        }
        connection.commit();
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            throw new MatrixResultCreationDAOException(e1);
        }
        throw new MatrixResultCreationDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

---

From source file:consultor.CSVLoader.java

/**
 * Parse CSV file using OpenCSV library and load in 
 * given database table. /*  w w  w . j a va2 s .c o m*/
 * @param csvFile Input CSV file
 * @param tableName Database table name to import data
 * @param truncateBeforeLoad Truncate the table before inserting 
 *          new records.
 * @throws Exception
 */
public void loadCSV(String csvFile, String tableName, boolean truncateBeforeLoad) throws Exception {

    CSVReader csvReader = null;
    if (null == this.connection) {
        throw new Exception("Not a valid connection.");
    }
    try {

        csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

    } catch (Exception e) {
        e.printStackTrace();
        throw new Exception("Error occured while executing file. " + e.getMessage());
    }

    String[] headerRow = csvReader.readNext();

    if (null == headerRow) {
        throw new FileNotFoundException(
                "No columns defined in given CSV file." + "Please check the CSV file format.");
    }

    String questionmarks = StringUtils.repeat("?,", headerRow.length);
    questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

    String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
    query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
    query = query.replaceFirst(VALUES_REGEX, questionmarks);

    System.out.println("Query: " + query);

    String[] nextLine;
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = this.connection;
        con.setAutoCommit(false);
        ps = con.prepareStatement(query);

        if (truncateBeforeLoad) {
            //delete data from table before loading csv
            con.createStatement().execute("DELETE FROM " + tableName);
        }

        final int batchSize = 1000;
        int count = 0;
        Date date = null;
        while ((nextLine = csvReader.readNext()) != null) {

            if (null != nextLine) {
                int index = 1;
                for (String string : nextLine) {
                    date = DateUtil.convertToDate(string);
                    if (null != date) {
                        ps.setDate(index++, new java.sql.Date(date.getTime()));
                    } else {
                        ps.setString(index++, string);
                    }
                }
                ps.addBatch();
            }
            if (++count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch(); // insert remaining records
        con.commit();
    } catch (Exception e) {
        con.rollback();
        e.printStackTrace();
        throw new Exception("Error occured while loading data from file to database." + e.getMessage());
    } finally {
        if (null != ps)
            ps.close();
        if (null != con)
            con.close();

        csvReader.close();
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• »