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:mom.trd.opentheso.bdd.helper.ConceptHelper.java

/**
 * Cette fonction permet d'ajouter une relation  la table
 * hierarchicalRelationship/*from  w ww.  ja  v  a 2s .c om*/
 *
 * @param conn
 * @param hierarchicalRelationship
 * @param idUser
 * @return
 */
public boolean addLinkHierarchicalRelation(Connection conn, HierarchicalRelationship hierarchicalRelationship,
        int idUser) {

    //     Connection conn;
    Statement stmt;

    try {
        //conn.setAutoCommit(false);
        // Get connection from pool
        //       conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {

                if (!new RelationsHelper().addRelationHistorique(conn, hierarchicalRelationship.getIdConcept1(),
                        hierarchicalRelationship.getIdThesaurus(), hierarchicalRelationship.getIdConcept2(),
                        hierarchicalRelationship.getRole(), idUser, "ADD")) {
                    conn.rollback();
                    conn.close();
                    return false;
                }

                String query = "Insert into hierarchical_relationship"
                        + "(id_concept1, id_thesaurus, role, id_concept2)" + " values (" + "'"
                        + hierarchicalRelationship.getIdConcept1() + "'" + ",'"
                        + hierarchicalRelationship.getIdThesaurus() + "'" + ",'"
                        + hierarchicalRelationship.getRole() + "'" + ",'"
                        + hierarchicalRelationship.getIdConcept2() + "')";
                stmt.executeUpdate(query);
                //  conn.commit();
            } finally {
                stmt.close();
            }
        } finally {
            // conn.close();
        }
    } catch (SQLException sqle) {
        // To avoid dupplicate Key
        //   System.out.println(sqle.toString());
        if (!sqle.getSQLState().equalsIgnoreCase("23505")) {
            return false;
        }
    }
    return true;
}

From source file:edu.isi.pfindr.servlets.QueryServlet.java

/**
 * get the Excel string of the mappings to be saved
 * /*from  w w  w  .j  av a 2  s .  c  om*/
 * @param request
 *            the servlet request
 * @param conn
 *            the DB connection
 * @return the CSV string to be saved in a file
 */
private String saveSelectedResults(HttpServletRequest request, Connection conn) {
    String rows = request.getParameter("rows");
    String action = request.getParameter("predicate");
    String phenotypes = "";
    try {
        // get the query results
        JSONArray json = new JSONArray(rows);
        if (action.equals("markAsCorrect") || action.equals("markAsIncorrect")) {
            // extend the rows with the user_label, user and timestamp info
            String user_label = action.equals("markAsCorrect") ? "CORRECT" : "INCORRECT";
            String markedTimestamp = request.getParameter("timestamp");
            String user = (String) request.getSession(false).getAttribute("user");
            for (int i = 0; i < json.length(); i++) {
                JSONArray row = json.getJSONArray(i);
                int index = row.length() - 1;
                String dbGaP = row.getString(index);
                row.put(index++, user_label);
                row.put(index++, user);
                row.put(index++, dbGaP);
            }
            if (markedTimestamp == null) {
                Timestamp timestamp = new Timestamp((new Date()).getTime());
                for (int i = 0; i < json.length(); i++) {
                    JSONArray row = json.getJSONArray(i);
                    int index = row.length() - 1;
                    String dbGaP = row.getString(index);
                    row.put(index++, timestamp);
                    row.put(index++, dbGaP);
                }
                // save into the DB
                conn.setAutoCommit(false);
                String sqlQuery = Utils.getSQLsaveSelectedResults();
                logger.info(sqlQuery);
                PreparedStatement stmt = conn.prepareStatement(sqlQuery);
                int rowCount = Utils.executeUpdate(stmt, json);
                conn.commit();
                logger.info("Inserted " + rowCount + " rows.");
                stmt.close();
                conn.setAutoCommit(true);
                // convert the timestamp to a date format
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                markedTimestamp = df.format(timestamp);
                JSONObject data = new JSONObject();
                data.putOpt("markedTime", markedTimestamp);
                phenotypes = data.toString();
            } else {
                for (int i = 0; i < json.length(); i++) {
                    JSONArray row = json.getJSONArray(i);
                    int index = row.length() - 1;
                    String dbGaP = row.getString(index);
                    row.put(index++, markedTimestamp);
                    row.put(index++, dbGaP);
                }
                phenotypes = Utils.toText(json, Utils.MARKED_COLUMNS);
            }
        } else {
            phenotypes = Utils.toText(json, Utils.DOWNLOAD_COLUMNS);
        }
    } catch (JSONException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        try {
            conn.rollback();
            conn.setAutoCommit(true);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }

    return phenotypes;
}

From source file:org.apache.cocoon.acting.DatabaseCookieAuthenticatorAction.java

/**
 *  Main invocation routine.//  w w w  .  j  a va2  s  . c o m
 *
 * @param  redirector     Description of Parameter
 * @param  resolver       Description of Parameter
 * @param  objectModel    Description of Parameter
 * @param  src            Description of Parameter
 * @param  parameters     Description of Parameter
 * @return                Description of the Returned Value
 * @exception  Exception  Description of Exception
 */
public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String src,
        Parameters parameters) throws Exception {
    DataSourceComponent datasource = null;
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    // read global parameter settings
    boolean reloadable = Constants.DESCRIPTOR_RELOADABLE_DEFAULT;

    if (this.settings.containsKey("reloadable")) {
        reloadable = Boolean.valueOf((String) this.settings.get("reloadable")).booleanValue();
    }

    // read local settings
    try {
        Configuration conf = this.getConfiguration(
                parameters.getParameter("descriptor", (String) this.settings.get("descriptor")), resolver,
                parameters.getParameterAsBoolean("reloadable", reloadable));
        String create_session = parameters.getParameter("create-session",
                (String) this.settings.get("create-session"));
        String append_session = parameters.getParameter("append-session",
                (String) this.settings.get("append-session"));
        boolean cs = true;
        if (create_session != null) {
            cs = BooleanUtils.toBoolean(create_session.trim());
        }
        boolean as = BooleanUtils.toBoolean(append_session.trim());

        datasource = this.getDataSource(conf);
        conn = datasource.getConnection();
        Request req = ObjectModelHelper.getRequest(objectModel);

        /*
         *  check request validity
         */
        if (req == null) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: no request object");
            }
            return null;
        }

        String query = this.getAuthQuery(objectModel, conf, req);
        if (query == null) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: have not got query");
            }
            req.setAttribute("message", "The authenticator is misconfigured");
            return null;
        }

        if (getLogger().isDebugEnabled()) {
            getLogger().debug("DBCOOKIEAUTH: query is: " + query);
        }
        st = conn.createStatement();
        rs = st.executeQuery(query);

        if (rs.next()) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: authorized successfully");
            }
            Session session = null;

            if (cs) {
                session = req.getSession(false);
                if (session != null) {
                    if (as == false) {
                        session.invalidate();
                        session = req.getSession(true);
                        if (getLogger().isDebugEnabled()) {
                            getLogger().debug("DBCOOKIEAUTH: session invalidated");
                        }
                    }
                } else {
                    session = req.getSession(true);
                }

                if (session == null) {
                    return null;
                }

                if (getLogger().isDebugEnabled()) {
                    if (as) {
                        getLogger().debug("DBCOOKIEAUTH: appending to session");
                    } else {
                        getLogger().debug("DBCOOKIEAUTH: session created");
                    }
                }
            } else {
                if (getLogger().isDebugEnabled()) {
                    getLogger().debug("DBCOOKIEAUTH: leaving session untouched");
                }
            }

            HashMap actionMap = this.propagateParameters(conf, rs, session);
            if (!conn.getAutoCommit()) {
                conn.commit();
            }
            return Collections.unmodifiableMap(actionMap);
        }
        if (!conn.getAutoCommit()) {
            conn.rollback();
        }

        req.setAttribute("message",
                "The username or password were incorrect, please check your CAPS LOCK key and try again.");
        if (getLogger().isDebugEnabled()) {
            getLogger().debug("DBCOOKIEAUTH: no results for query");
        }
    } catch (Exception e) {
        if (conn != null) {
            try {
                if (!conn.getAutoCommit()) {
                    conn.rollback();
                }
            } catch (Exception se) {
                // ignore
            }
        }
        getLogger().error("Exception: ", e);
        return null;
    } finally {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                // ignore
            }
        }
    }
    return null;
}

From source file:com.krawler.esp.servlets.AdminServlet.java

public static String assignUserPermissions(Connection conn, HttpServletRequest request)
        throws ServiceException {
    String result = "success";
    String[] buf = null;//from w  w  w.  j  ava  2 s .c om
    try {
        Map arrParam = null;
        arrParam = request.getParameterMap();
        String[] selectedUsers = request.getParameter("users").split(",");

        int auditMode = 0;
        String ipAddress = AuthHandler.getIPAddress(request);
        String loginid = AuthHandler.getUserid(request);
        String companyid = AuthHandler.getCompanyid(request);

        PermissionManager pm = new PermissionManager();
        List<String> permissionNames = new ArrayList<String>();
        Set<String> keys = arrParam.keySet();
        Iterator itr = keys.iterator();
        while (itr.hasNext()) {
            permissionNames.add(itr.next().toString());
        }
        List<Feature> features = pm.updateFeatures(conn, permissionNames);
        for (int i = 0; i < selectedUsers.length; i++) {
            if (isCreator(conn, selectedUsers[i])) {
                java.lang.Throwable cause = new java.lang.Throwable();
                throw ServiceException.FAILURE("1", cause);
            }
            int row = pm.setUserPermissions(conn, features, selectedUsers[i]);
            String selUserName = AuthHandler.getAuthor(conn, selectedUsers[i]) + " ("
                    + AuthHandler.getUserName(conn, selectedUsers[i]) + ")";
            String author = AuthHandler.getAuthor(conn, loginid) + " (" + AuthHandler.getUserName(request)
                    + "), ";
            String params = author + selUserName;
            AuditTrail.insertLog(conn, "314", loginid, loginid, "", companyid, params, ipAddress, auditMode);
        }
        conn.commit();

    } catch (SessionExpiredException ex) {
        Logger.getLogger(AdminServlet.class.getName()).log(Level.SEVERE,
                "Session Expired Exception While Assigning Permissions To Users", ex);
        result = ex.getMessage();
        conn.rollback();

    } catch (ServiceException e) {
        Logger.getLogger(AdminServlet.class.getName()).log(Level.SEVERE,
                "Service Exception While Assigning Permissions To Users", e);

        if (StringUtil.equal(e.getMessage(), "system failure: 1")) {
            result = "Can not change the COMPANY CREATOR'S permissions";
        }
        conn.rollback();
    }
    return result;
}

From source file:it.fub.jardin.server.DbUtils.java

public Integer massiveUpdate(MassiveUpdateObject muo, String username)
        throws HiddenException, VisibleException {
    // TODO Auto-generated method stub
    Connection connection = this.dbConnectionHandler.getConn();

    int result = -1;
    String tableName = null;//  w ww  .  j av a  2s.c om

    ResultSetMetaData metadata;
    String transQueries = "";
    try {
        metadata = this.dbProperties.getResultsetMetadata(connection, muo.getResultsetId());
        tableName = metadata.getTableName(1);
        connection.setAutoCommit(false);

        // String[] transQueries = null;

        // int i = 0;
        for (String pkValue : muo.getPrimaryKeyValues()) {
            transQueries = "UPDATE `" + tableName + "` SET ";
            BaseModelData newValues = muo.getNewValues();
            for (String tableField : newValues.getPropertyNames()) {
                transQueries += tableField + " = '" + newValues.get(tableField) + "', ";
            }
            transQueries = transQueries.substring(0, transQueries.length() - 2);

            transQueries += " WHERE " + muo.getFieldName() + " = '" + pkValue + "'; ";
            // i++;
            Statement stmt = connection.createStatement();
            // System.out.println("query update massivo: " + transQueries);
            JardinLogger.debug(username, "query update massivo: " + transQueries);
            stmt.executeUpdate(transQueries);
        }

        connection.commit();
        connection.setAutoCommit(true);
        connection.close();

        return 1;

    } catch (SQLException e) {
        // TODO Auto-generated catch block
        try {
            connection.rollback();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        JardinLogger.debug(username, "query update massivo: " + transQueries);
        e.printStackTrace();
        throw new VisibleException("Impossibile eseguire update massivo");
    } finally {
        try {
            connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

From source file:jade.domain.DFDBKB.java

/**
 *  Insert a new DFD object.//w  ww .ja  va  2s .  c  o  m
 *  @return the previous DFD (if any) corresponding to the same AID
 */
protected Object insertSingle(Object name, Object fact) throws SQLException {
    DFAgentDescription dfd = (DFAgentDescription) fact;
    AID agentAID = dfd.getName();
    String agentName = agentAID.getName();
    DFAgentDescription dfdToReturn = null;
    String batchErrMsg = "";

    Connection conn = getConnectionWrapper().getConnection();
    PreparedStatements pss = getPreparedStatements();
    try {
        // -- Remove the previous DFD if any
        dfdToReturn = (DFAgentDescription) removeSingle(dfd.getName());

        // -- add new DFD

        // DF Agent Description
        Date leaseTime = dfd.getLeaseTime();
        long lt = (leaseTime != null ? leaseTime.getTime() : -1);
        String descrId = getGUID();

        pss.stm_insAgentDescr.setString(1, descrId);
        pss.stm_insAgentDescr.setString(2, agentName);
        pss.stm_insAgentDescr.setString(3, String.valueOf(lt));
        pss.stm_insAgentDescr.executeUpdate();

        // AID
        saveAID(agentAID);

        // Languages
        Iterator iter = dfd.getAllLanguages();
        if (iter.hasNext()) {
            pss.stm_insLanguage.clearBatch();
            while (iter.hasNext()) {
                pss.stm_insLanguage.setString(1, descrId);
                pss.stm_insLanguage.setString(2, (String) iter.next());
                pss.stm_insLanguage.addBatch();
            }
            pss.stm_insLanguage.executeBatch();
        }

        // Ontologies
        iter = dfd.getAllOntologies();
        if (iter.hasNext()) {
            pss.stm_insOntology.clearBatch();
            while (iter.hasNext()) {
                pss.stm_insOntology.setString(1, descrId);
                pss.stm_insOntology.setString(2, (String) iter.next());
                pss.stm_insOntology.addBatch();
            }
            pss.stm_insOntology.executeBatch();
        }

        // Protocols
        iter = dfd.getAllProtocols();
        if (iter.hasNext()) {
            pss.stm_insProtocol.clearBatch();
            while (iter.hasNext()) {
                pss.stm_insProtocol.setString(1, descrId);
                pss.stm_insProtocol.setString(2, (String) iter.next());
                pss.stm_insProtocol.addBatch();
            }
            pss.stm_insProtocol.executeBatch();
        }

        // Services
        saveServices(descrId, dfd.getAllServices());

        regsCnt++;
        // clear outdated entries after a certain number of new registrations
        if (regsCnt > MAX_REGISTER_WITHOUT_CLEAN) {
            regsCnt = 0;
            clean();
        }

        conn.commit();
    } catch (SQLException sqle) {
        // Rollback the transaction
        try {
            conn.rollback();
        } catch (SQLException se) {
            logger.log(Logger.SEVERE,
                    "Rollback for incomplete insertion of DFD for agent " + dfd.getName() + " failed.", se);
        }
        // Re-throw the exception
        throw sqle;
    }

    return dfdToReturn;
}

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

/**
 * Creates a map from an action plan id, a list of intentions and a list of
 * sections.// w  ww.  ja v  a2  s .  co m
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param actionPlanId the action plan id to be linked to the created
 *        actions.
 * @param intentions the intentions to add in the map.
 * @param sections the sections to add in the map.
 * @since July, 2011.
 * @throws ActionCreationDAOException if an error occurs during the
 *         creation.
 */
@Override
public void createMap(int actionPlanId, List<Intention> intentions, List<Section> sections)
        throws ActionCreationDAOException {
    LOGGER.debug("createMap(" + actionPlanId + "," + intentions.size() + " intentions," + sections.size()
            + " sections).");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        connection.setAutoCommit(false);
        List<Integer> createdIntentionsId = new ArrayList<Integer>();
        for (Intention intention : intentions) {
            getQueryRunner().update(connection,
                    "INSERT INTO intention(intention_id, label) VALUES(nextval('intention_seq'), ?) ",
                    new Object[] { intention.getLabel() });
            createdIntentionsId.add((Integer) getQueryRunner().query(connection,
                    "SELECT MAX(intention_id)::int AS intentionId FROM intention ",
                    new ScalarHandler("intentionId")));
        }
        List<Intention> createdIntentions = getQueryRunner().query(connection,
                "SELECT intention_id AS intentionId, label FROM intention WHERE intention_id IN("
                        + getInClauseFromIntentionsIds(createdIntentionsId) + ") ",
                new BeanListHandler<Intention>(Intention.class));
        LOGGER.debug(createdIntentions.size() + " intentions created with success...");
        for (Section section : sections) {
            boolean source = false, target = false;
            for (Intention intention : createdIntentions) {
                if (!source && intention.getLabel().equalsIgnoreCase(section.getSourceIntention())) {
                    section.setSourceIntentionId(intention.getIntentionId());
                    source = true;
                }
                if (!target && intention.getLabel().equalsIgnoreCase(section.getTargetIntention())) {
                    section.setTargetIntentionId(intention.getIntentionId());
                    target = true;
                }
                if (target && source) {
                    break;
                }
            }
            Integer actionId = (Integer) getQueryRunner().query(connection,
                    "SELECT action_id::int AS actionId FROM action WHERE action_plan_id = ? AND source_intention = ? AND target_intention = ? ",
                    new ScalarHandler("actionId"), new Object[] { actionPlanId, section.getSourceIntentionId(),
                            section.getTargetIntentionId() });
            if (null == actionId) {
                LOGGER.debug("Action creation...");
                getQueryRunner().update(connection,
                        "INSERT INTO action(action_id, target_intention, source_intention, action_plan_id) VALUES(nextval('action_seq'),?,?,?) ",
                        new Object[] { section.getTargetIntentionId(), section.getSourceIntentionId(),
                                actionPlanId });
                actionId = (Integer) getQueryRunner().query(connection,
                        "SELECT action_id::int AS actionId FROM action WHERE action_plan_id = ? AND source_intention = ? AND target_intention = ? ",
                        new ScalarHandler("actionId"), new Object[] { actionPlanId,
                                section.getSourceIntentionId(), section.getTargetIntentionId() });
            }
            getQueryRunner().update(connection,
                    "INSERT INTO item(item_id, label, iteration_max_number) VALUES(nextval('item_seq'), ?, ?) ",
                    new Object[] { section.getStrategyLabel(), section.getMaxIterationNumber() });
            Integer createdItemId = (Integer) getQueryRunner().query(connection,
                    "SELECT MAX(item_id)::int AS itemId FROM item ", new ScalarHandler("itemId"));
            getQueryRunner().update(connection,
                    (section.isExclusive())
                            ? "INSERT INTO action_exclusive_item(action_id, item_id) VALUES(?,?) "
                            : "INSERT INTO action_inclusive_item(action_id, item_id) VALUES(?,?) ",
                    new Object[] { actionId, createdItemId });
        }
        connection.commit();
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            throw new ActionCreationDAOException(e1);
        }
        throw new ActionCreationDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

From source file:de.whs.poodle.repositories.McQuestionRepository.java

public int save(McQuestion question) {

    if (question.getText().trim().isEmpty())
        throw new BadRequestException("noExerciseTextSpecified");

    for (Answer a : question.getAnswers()) {
        if (a.getText().trim().isEmpty())
            throw new BadRequestException("emptyAnswer");
    }//from   w w w  . ja va2 s  . c  om

    return jdbc.execute(new ConnectionCallback<Integer>() {

        @Override
        public Integer doInConnection(Connection con) throws SQLException, DataAccessException {
            try (PreparedStatement questionPs = con.prepareStatement(
                    "INSERT INTO mc_question(course_id,text,has_multiple_correct_answers,changed_by_id,root_id,visibility,comment) VALUES(?,?,?,?,?,?::exercise_visibility,?)",
                    PreparedStatement.RETURN_GENERATED_KEYS);

                    PreparedStatement answersPs = con.prepareStatement(
                            "INSERT INTO mc_question_to_answer(mc_question_id,correct,text) VALUES(?,?,?)");

                    PreparedStatement tagsPs = con.prepareStatement(
                            "INSERT INTO mc_question_to_tag(mc_question_id,tag_id) VALUES(?,?)");

                    /* If this is a new revision, this updates the revision in existing worksheets.
                     * Note that we only do this if no statistics exist yet, because we would screw
                     * those up otherwise (the answer IDs in mc_chosen_answer wouldn't match the
                     * question revision anymore). */
                    PreparedStatement updateWorksheetsPs = con
                            .prepareStatement("UPDATE mc_worksheet_to_question wtq SET mc_question_id = ? "
                                    + "WHERE mc_question_id IN (SELECT id FROM mc_question WHERE root_id = ?) "
                                    + "AND NOT EXISTS (SELECT 1 FROM mc_statistic WHERE mc_worksheet_to_question_id = wtq.id)");) {
                con.setAutoCommit(false);

                // inner try for rollback
                try {
                    // exercise
                    questionPs.setInt(1, question.getCourseId());
                    questionPs.setString(2, question.getText());
                    questionPs.setBoolean(3, question.isMultipleCorrectAnswers());

                    questionPs.setInt(4, question.getChangedBy().getId());

                    /*
                     * The root id is always the ID of the first revision. If this
                     * is a new exercise, this ID obviously doesn't exist yet. We set
                     * NULL in this case, but a trigger in the DB will automatically
                     * set the root_id to the generated id.
                     */
                    if (question.getRootId() == 0)
                        questionPs.setNull(5, Types.INTEGER);
                    else
                        questionPs.setInt(5, question.getRootId());

                    questionPs.setString(6, question.getVisibility().toString());

                    questionPs.setString(7, question.getComment());

                    questionPs.executeUpdate();

                    ResultSet genRs = questionPs.getGeneratedKeys();
                    genRs.next();
                    int questionId = genRs.getInt(1);

                    // answers
                    answersPs.setInt(1, questionId);

                    for (Answer a : question.getAnswers()) {
                        answersPs.setBoolean(2, a.isCorrect());
                        answersPs.setString(3, a.getText());
                        answersPs.addBatch();
                    }

                    answersPs.executeBatch();

                    // tag relations
                    tagsPs.setInt(1, questionId);

                    for (Tag t : question.getTags()) {
                        tagsPs.setInt(2, t.getId());
                        tagsPs.addBatch();
                    }

                    tagsPs.executeBatch();

                    // if this is new revision, update it in the worksheets
                    if (question.getRootId() != 0) {
                        updateWorksheetsPs.setInt(1, questionId);
                        updateWorksheetsPs.setInt(2, question.getRootId());
                        updateWorksheetsPs.executeUpdate();
                    }

                    con.commit();

                    return questionId;

                } catch (SQLException e) {
                    con.rollback();
                    throw e;
                } finally {
                    con.setAutoCommit(true);
                }
            }
        }
    });
}

From source file:dao.CollabrumDaoDb.java

/**
 * updates the stream blob (title, zoom)
 * @param entryId - entry id//w  ww  . j a  v a  2 s .c o m
 * @param collabrumId - collabrum id
 * @param userId - user Id
 * @param userLogin - user login
 * @param zoom - the zoom
 * @param btitle - the blob title
 * @param def - the default blob
 * @param caption - caption
 */
public void updateStreamBlob(String entryId, String collabrumId, String userId, String userLogin, String zoom,
        String btitle, boolean def, String caption) throws BaseDaoException {

    if (RegexStrUtil.isNull(entryId) || RegexStrUtil.isNull(collabrumId) || RegexStrUtil.isNull(userId)) {
        throw new BaseDaoException("params are null");
    }

    if (RegexStrUtil.isNull(userLogin) || RegexStrUtil.isNull(btitle)) {
        throw new BaseDaoException("params are null");
    }

    /**
    * check authority to update - diaryAdmin & isOrganizer are checked
    */
    if (!isOrganizer(collabrumId, userLogin, userId)) {
        StringBuffer sb = new StringBuffer("user does not have permission to update streamblobs in collabrum ");
        sb.append(collabrumId);
        sb.append(" userId ");
        sb.append(userId);
        throw new BaseDaoException(sb.toString());
    }

    /**
     *  Get scalability datasource, collblob is partitioned on collabrumId
     */
    String sourceName = scalabilityManager.getWriteBlobScalability(collabrumId);
    ds = scalabilityManager.getSource(sourceName);
    if (ds == null) {
        StringBuffer sb = new StringBuffer("ds null, in updateStreamBlob() in collabrumDaoDb ");
        sb.append(sourceName);
        sb.append(" collabrumId = ");
        sb.append(collabrumId);
        sb.append(" entryid = ");
        sb.append(entryId);
        throw new BaseDaoException(sb.toString());
    }

    boolean exists = false;
    try {
        Object[] params = { (Object) entryId };
        List result = defaultQuery.execute(params);
        if (result != null && result.size() > 0) {
            exists = true;
        }
    } catch (Exception e) {
        throw new BaseDaoException("error while" + defaultQuery.getSql());
    }

    if (RegexStrUtil.isNull(caption)) {
        caption = btitle;
    }

    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        updateStreamblobQuery.run(conn, entryId, collabrumId, zoom, btitle, caption);
        /** if this is the default photo and this photo does not exist, add this entry */
        if (def) {
            if (!exists) {
                deleteDefQuery.run(conn, collabrumId);
                addDefQuery.run(conn, entryId, collabrumId);
            }
        } else {
            /**  no more a default photo, delete this entry */
            if (exists) {
                deleteDefQuery.run(conn, collabrumId);
            }
        }
    } catch (Exception e) {
        try {
            conn.rollback();
        } catch (Exception e1) {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (Exception e2) {
                throw new BaseDaoException("connection close exception in add/delete default collabrum blob",
                        e2);
            }
            throw new BaseDaoException(
                    "error occured while rollingback entries from default collabrum stream blob", e1);
        }
    }
    try {
        conn.commit();
    } catch (Exception e3) {
        throw new BaseDaoException("commit exception", e3);
    }
    try {
        if (conn != null) {
            conn.setAutoCommit(true);
            conn.close();
        }
    } catch (Exception e4) {
        throw new BaseDaoException("connection close exception", e4);
    }

    /*
    try {
        if (conn != null) {
           conn.close();
        }
    } catch(Exception e2) {
        StringBuffer sb = new StringBuffer("updateBlob error, collabrumId ");
        sb.append(collabrumId);
        sb.append(" entryId = ");
        sb.append(entryId);
        sb.append(" btitle = ");
        sb.append(btitle);
        sb.append(" userId = ");
        sb.append(userId);
        throw new BaseDaoException(sb.toString(), e2);
    }
    StringBuffer sb = new StringBuffer("updateBlob error, collabrumId ");
    sb.append(collabrumId);
    sb.append(" entryId = ");
    sb.append(entryId);
    sb.append(" btitle = ");
    sb.append(btitle);
    sb.append(" userId = ");
    sb.append(userId);
               throw new BaseDaoException(sb.toString(), e);
             }
             try {
    if (conn != null) {
       conn.close();
    }
             } catch(Exception e) {
    StringBuffer sb = new StringBuffer("updateBlob error collabrumId ");
    sb.append(collabrumId);
    sb.append(" entryId = ");
    sb.append(entryId);
    sb.append( " btitle = ");
    sb.append(btitle);
    sb.append(" userId = ");
    sb.append(userId);
    throw new BaseDaoException(sb.toString(), e);
             }
            
    */

    /**
     * Jboss methods
     * fqn - full qualified name (key=collabrumId + entryId);
     * check if the entryId already set in the cache
     * If it exists, return the entryId from the cache.
     */
    Fqn fqn = cacheUtil.fqn(DbConstants.COL_STREAM_BLOB);
    StringBuffer sb = new StringBuffer(collabrumId);
    sb.append("-");
    sb.append(entryId);
    String key = sb.toString();
    if (treeCache.exists(fqn, key)) {
        treeCache.remove(fqn, key);
    }
    fqn = cacheUtil.fqn(DbConstants.DEFAULT_PHOTO);
    if (treeCache.exists(fqn, key)) {
        treeCache.remove(fqn, key);
    }

    fqn = cacheUtil.fqn(DbConstants.COLLABRUM_STREAM_BLOBS);
    if (treeCache.exists(fqn, collabrumId)) {
        treeCache.remove(fqn, collabrumId);
    }

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

    fqn = cacheUtil.fqn(DbConstants.COLL_CAT);
    sb.delete(0, sb.length());
    sb.append(collabrumId);
    sb.append("-");
    sb.append(DbConstants.PHOTO_CATEGORY);
    if (treeCache.exists(fqn, sb.toString())) {
        treeCache.remove(fqn, sb.toString());
    }
}

From source file:dao.CollabrumDaoDb.java

/**
 * Create a new collabrum. The path, createdate, moddate is automatically set by the create que
 * @param parentId this is the parent node, if root, parent is -1
 * @param name the name of the collabrum, need not be unique
 * @param userId this is the userId name on this server that is already authenticated, if required
 * @param desc this is the description of this collabrum
 * @param keywords this is the keywords of this collabrum
 * @throws BaseDaoException If we have a problem interpreting the data or the data is missing or incorrect
 *//*from w w w. j a v  a  2 s .c  om*/
public void createCollabrum(String parentId, String name, String userId, String desc, String keywords,
        String userLogin, String style) throws BaseDaoException {

    if (RegexStrUtil.isNull(userId) || RegexStrUtil.isNull(parentId) || RegexStrUtil.isNull(name)
            || RegexStrUtil.isNull(style)) {
        throw new BaseDaoException("params are null");
    }

    boolean isAddable = false;
    if (diaryAdmin.isDiaryAdmin(userLogin)) {
        isAddable = true;
    }

    /**
     * check global flag
     * operations: 1 (Designate Specific Members As Authors), 
    * 2 (Automatically Allow All Members To Be Authors)
     * status: 1 (Hidden), 2 (Ready) should we check for the status for operations (2)
     * if set to 1, check if this user is the designated author
     */
    if (!isAddable) {
        DirScope dirscope = getDirectoryScope(parentId);
        if (dirscope.getValue(DbConstants.OPERATIONS).equals((Object) "2")) {
            isAddable = true;
        } else {
            if (isAuthor(parentId, userId)) {
                isAddable = true;
            }
        }
    }
    if (!isAddable) {
        StringBuffer sb = new StringBuffer("Donot have the permission to add to this directory, ");
        sb.append(parentId);
        sb.append(" userId = ");
        sb.append(userId);
        throw new BaseDaoException(sb.toString());
    }

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

    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);

        //addQuery = new CollabrumAddQuery();
        addQuery.run(conn, name, userId, desc, keywords, style);

        /** add member query **/
        addMemberQuery.run(conn, "LAST_INSERT_ID()", userId);

        //addDircollQuery = new DirCollAddQuery();
        addDircollQuery.run(conn, "LAST_INSERT_ID()", parentId);

        /**
          * The table insertion order is important as LAST_INSERT_ID 
          * will change when we use entryId column here. For the tables below.
          *
         */
        addAdminQuery.run(conn, "LAST_INSERT_ID()", userId);

    } catch (Exception e) {
        try {
            conn.rollback();
        } catch (Exception e1) {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (Exception e2) {
                StringBuffer sb = new StringBuffer(
                        "conn.close exception for rollback(), for createCollabrum() ");
                sb.append("parentId = ");
                sb.append(parentId);
                sb.append(" name = ");
                sb.append(name);
                sb.append(" keywords = ");
                sb.append(keywords);
                sb.append(" userId = ");
                sb.append(userId);
                sb.append(" desc = ");
                sb.append(desc);
                throw new BaseDaoException(sb.toString(), e2);
            }
            StringBuffer sb = new StringBuffer(" rollback() exception, for createCollabrum() ");
            sb.append("parentId = ");
            sb.append(parentId);
            sb.append(" name = ");
            sb.append(name);
            sb.append(" keywords = ");
            sb.append(keywords);
            sb.append(" userId = ");
            sb.append(userId);
            sb.append(" desc = ");
            sb.append(desc);
            throw new BaseDaoException(sb.toString(), e1);
        }
        throw new BaseDaoException("conn error ", e);
    }
    // connection commit
    try {
        conn.commit();
    } catch (Exception e3) {
        StringBuffer sb = new StringBuffer(" commit() exception, for createCollabrum(), parentId = ");
        sb.append(parentId);
        sb.append(" name = ");
        sb.append(name);
        sb.append(" keywords = ");
        sb.append(keywords);
        sb.append(" userId = ");
        sb.append(userId);
        sb.append(" desc = ");
        sb.append(desc);
        throw new BaseDaoException(sb.toString(), e3);
    }
    try {
        if (conn != null) {
            conn.setAutoCommit(true);
            conn.close();
        }
    } catch (Exception e4) {
        StringBuffer sb = new StringBuffer(
                " conn.close() exception, for commit(), createCollabrum() parentId = ");
        sb.append(parentId);
        sb.append(" name = ");
        sb.append(name);
        sb.append(" keywords = ");
        sb.append(keywords);
        sb.append(" userId = ");
        sb.append(userId);
        sb.append(" desc = ");
        sb.append(desc);
        throw new BaseDaoException(sb.toString(), e4);
    }

    /**
     * remove this directory from cache
     */
    Fqn fqn = cacheUtil.fqn(DbConstants.DIRECTORY);
    if (treeCache.exists(fqn, parentId)) {
        treeCache.remove(fqn, parentId);
    }

    fqn = cacheUtil.fqn(DbConstants.COLLABRUM_LIST);
    if (treeCache.exists(fqn, parentId)) {
        treeCache.remove(fqn, parentId);
    }

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