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:org.apache.hadoop.hive.metastore.txn.TxnHandler.java

/**
 * This would have been made simpler if all locks were associated with a txn.  Then only txn needs to
 * be heartbeated, committed, etc.  no need for client to track individual locks.
 * When removing locks not associated with txn this potentially conflicts with
 * heartbeat/performTimeout which are update/delete of HIVE_LOCKS thus will be locked as needed by db.
 * since this only removes from HIVE_LOCKS at worst some lock acquire is delayed
 *//*www  . j  a v  a2  s . co m*/
@RetrySemantics.Idempotent
public void unlock(UnlockRequest rqst) throws NoSuchLockException, TxnOpenException, MetaException {
    try {
        Connection dbConn = null;
        Statement stmt = null;
        long extLockId = rqst.getLockid();
        try {
            /**
             * This method is logically like commit for read-only auto commit queries.
             * READ_COMMITTED since this only has 1 delete statement and no new entries with the
             * same hl_lock_ext_id can be added, i.e. all rows with a given hl_lock_ext_id are
             * created in a single atomic operation.
             * Theoretically, this competes with {@link #lock(org.apache.hadoop.hive.metastore.api.LockRequest)}
             * but hl_lock_ext_id is not known until that method returns.
             * Also competes with {@link #checkLock(org.apache.hadoop.hive.metastore.api.CheckLockRequest)}
             * but using SERIALIZABLE doesn't materially change the interaction.
             * If "delete" stmt misses, additional logic is best effort to produce meaningful error msg.
             */
            dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED);
            stmt = dbConn.createStatement();
            //hl_txnid <> 0 means it's associated with a transaction
            String s = "delete from HIVE_LOCKS where hl_lock_ext_id = " + extLockId + " AND (hl_txnid = 0 OR"
                    + " (hl_txnid <> 0 AND hl_lock_state = '" + LOCK_WAITING + "'))";
            //(hl_txnid <> 0 AND hl_lock_state = '" + LOCK_WAITING + "') is for multi-statement txns where
            //some query attempted to lock (thus LOCK_WAITING state) but is giving up due to timeout for example
            LOG.debug("Going to execute update <" + s + ">");
            int rc = stmt.executeUpdate(s);
            if (rc < 1) {
                LOG.debug("Going to rollback");
                dbConn.rollback();
                LockInfo info = getTxnIdFromLockId(dbConn, extLockId);
                if (info == null) {
                    //didn't find any lock with extLockId but at ReadCommitted there is a possibility that
                    //it existed when above delete ran but it didn't have the expected state.
                    LOG.info("No lock in " + LOCK_WAITING + " mode found for unlock("
                            + JavaUtils.lockIdToString(rqst.getLockid()) + ")");
                    //bail here to make the operation idempotent
                    return;
                }
                if (info.txnId != 0) {
                    String msg = "Unlocking locks associated with transaction not permitted.  " + info;
                    //if a lock is associated with a txn we can only "unlock" if if it's in WAITING state
                    // which really means that the caller wants to give up waiting for the lock
                    LOG.error(msg);
                    throw new TxnOpenException(msg);
                }
                if (info.txnId == 0) {
                    //we didn't see this lock when running DELETE stmt above but now it showed up
                    //so should "should never happen" happened...
                    String msg = "Found lock in unexpected state " + info;
                    LOG.error(msg);
                    throw new MetaException(msg);
                }
            }
            LOG.debug("Going to commit");
            dbConn.commit();
        } catch (SQLException e) {
            LOG.debug("Going to rollback");
            rollbackDBConn(dbConn);
            checkRetryable(dbConn, e, "unlock(" + rqst + ")");
            throw new MetaException("Unable to update transaction database "
                    + JavaUtils.lockIdToString(extLockId) + " " + StringUtils.stringifyException(e));
        } finally {
            closeStmt(stmt);
            closeDbConn(dbConn);
        }
    } catch (RetryException e) {
        unlock(rqst);
    }
}

From source file:com.iana.boesc.dao.BOESCDaoImpl.java

@Override
public boolean insertDVIRRecord(DVIR_EDI322Bean eb, DVIRRCDStatisticBean bean, String boescUserId,
        String userType, File file) throws Exception {
    logger.info("----- getPopulatedDataAndInsert ............" + " boescUserId ::" + boescUserId
            + " userType ::" + userType);
    QueryRunner qrun = new QueryRunner(getDataSource());
    Connection conn = getConnection();
    conn.setAutoCommit(false);//from  w  w w .  ja va 2s.com
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

        StringBuilder sbQuery = new StringBuilder(
                "INSERT INTO DVIR_TRAN_SET (ISA_HEADER, GS_HEADER, SENDER_ID, SENDER_TYPE, ISA_DATETIME, GS_CONTROL, ST_CONTROL,");
        sbQuery.append(
                " INSP_DATE, INSP_TIME, INSP_TIME_ZONE, EQ_PREFIX, EQ_NUMBER, CHASSIS_ID, IEP_SCAC, PORT_QUAL, PORT_ID,  ");
        sbQuery.append(
                " DRV_STATE_ABBR, DRV_LIC_NO, DRV_NAME, MC_SCAC, MC_NAME, RCD_INFO, IEP_DOT, MC_EIN, MC_DOT, IDD_PIN,   ");
        sbQuery.append(
                " Q5_SEG, N7_SEG, R4_SEG, N1_SEG, N1_DR, RCD_00, RCD_01, RCD_02, RCD_03, RCD_04, RCD_05, RCD_06, RCD_07, RCD_08, RCD_09,  ");
        if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_IEP)) {
            sbQuery.append(" IEP_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_MRV)) {
            sbQuery.append(" MRV_ID, STATUS   ");
        } else if (userType.equalsIgnoreCase(GlobalVariables.USER_TYPE_FO)) {
            sbQuery.append(" FO_ID, STATUS ");
        }

        sbQuery.append(
                " ,CREATED_DATE ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

        //get Additional Details from GIER DB
        GIERInfoDetails gierInfo = null;
        gierInfo = getDVIRAdditionaldetails(eb.getEqpInitial(), eb.getEqpNumber());
        System.out.println("Before UIIA Datasource");
        UIIAInfoDetails uiiaInfo = null;
        uiiaInfo = getUIIAdetailsforDVIR(eb);

        //logger.info("gierInfo ::"+gierInfo);

        pstmt = conn.prepareStatement(sbQuery.toString(), Statement.RETURN_GENERATED_KEYS);
        pstmt.setString(1, eb.getIsaheader());
        pstmt.setString(2, eb.getGsHeader());
        pstmt.setString(3, eb.getSenderId());
        pstmt.setString(4, userType);
        pstmt.setString(5, eb.getIsaDateTime());
        pstmt.setString(6, eb.getGsControl());
        pstmt.setString(7, eb.getStControl());
        pstmt.setString(8, eb.getInspDate());
        pstmt.setString(9, eb.getInspTime());
        pstmt.setString(10, eb.getInspTimeZone());
        pstmt.setString(11, eb.getEqpInitial());
        pstmt.setString(12, eb.getEqpNumber());
        pstmt.setString(13, eb.getChassisId());
        pstmt.setString(14, gierInfo.getCompanySCACCode());
        pstmt.setString(15, eb.getPortQualifier());
        pstmt.setString(16, eb.getPortIdentifier());
        pstmt.setString(17, eb.getDrvState());
        pstmt.setString(18, eb.getDrvLicNo());
        pstmt.setString(19, uiiaInfo.getDrvName());
        pstmt.setString(20, eb.getMcScac());
        pstmt.setString(21, eb.getMcName());
        pstmt.setString(22, eb.getRcdInfo());
        pstmt.setString(23, gierInfo.getUsDotNumber());
        pstmt.setString(24, uiiaInfo.getMcEin());
        pstmt.setString(25, uiiaInfo.getMcDot());
        pstmt.setString(26, uiiaInfo.getIddPin());
        pstmt.setString(27, eb.getQ5Details());
        pstmt.setString(28, eb.getN7Details());
        pstmt.setString(29, eb.getR4Details());
        pstmt.setString(30, eb.getN1Details());
        pstmt.setString(31, eb.getN1DrDetails());
        pstmt.setInt(32, bean.getNoDefectsCount());
        pstmt.setInt(33, bean.getBrakesCount());
        pstmt.setInt(34, bean.getLightsCount());
        pstmt.setInt(35, bean.getWheelCount());
        pstmt.setInt(36, bean.getAirlineCount());
        pstmt.setInt(37, bean.getCouplingCount());
        pstmt.setInt(38, bean.getFrameCount());
        pstmt.setInt(39, bean.getBolsterCount());
        pstmt.setInt(40, bean.getFastenerCount());
        pstmt.setInt(41, bean.getSliderCount());
        pstmt.setString(42, boescUserId);
        pstmt.setString(43, GlobalVariables.STATUS_PENDING);
        pstmt.setObject(44, DateTimeFormater.getSqlSysTimestamp());

        int dbStat = 0;
        int dvirKey = 0;
        dbStat = pstmt.executeUpdate();
        rs = pstmt.getGeneratedKeys();
        if (dbStat != 0) {
            if (rs != null) {
                while (rs.next()) {
                    dvirKey = rs.getInt(1);
                    logger.info("dvirKey: " + dvirKey);
                }
            }
        }
        if (dvirKey != 0) {
            conn.commit();
            //Update BOESC_UNIQUE_NO : using business logic
            String sql = "UPDATE DVIR_TRAN_SET SET DVIR_NO = ? WHERE DVIR_TRAN_ID = ? ";
            qrun.update(sql, new Object[] { Utility.addPadToUniqueNum(dvirKey, "DVIR-"), dvirKey });
            logger.info("Record Inserted successfully for DVIR..." + file.getName());
            return true;
        } else {
            conn.rollback();
            logger.error("Failure Data insertion in DVIR..");
        }
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex1) {
            logger.error(
                    "Caught SQL exception while closing prepared statement /resultset " + ex1.getMessage());
            ex1.printStackTrace();
            throw ex1;
        } catch (Exception e) {
            logger.error("Caught SQL exception in finally block " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
    return false;
}

From source file:com.mysql.stresstool.RunnableClusterQueryInsert.java

public void run() {

    BufferedReader d = null;//from   w  w w.j  av a 2 s  .  c o  m
    Connection conn = null;

    try {
        if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) {
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test");
        } else
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"));
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (conn != null) {

        try {

            Statement stmt = null;
            //                ResultSet rs = null;
            //                ResultSet rs2 = null;

            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.execute("SET AUTOCOMMIT=0");
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                if (repeat > 0 && lazyInterval < 500) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval++;
                //IMPLEMENTING lazy
                Vector v = this.getTablesValues(lazy);

                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");

                    //                  stmt.execute("SET GLOBAL max_allowed_packet=1073741824");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            intBlobInterval = 0;

                        }
                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();

                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    System.out.println("FAILED QUERY1==" + insert1);
                    System.out.println("FAILED QUERY2==" + insert2);
                    sqle.printStackTrace();
                    System.exit(1);
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

From source file:com.mysql.stresstool.RunnableSC2013QueryInsert.java

public void run() {

    BufferedReader d = null;//from w  w w  .  j a  v  a 2  s  . c o m
    Connection conn = null;

    try {
        if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) {
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test");
        } else
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"));
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (conn != null) {

        try {

            Statement stmt = null;
            //                ResultSet rs = null;
            //                ResultSet rs2 = null;

            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.execute("SET AUTOCOMMIT=0");
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                if (repeat > 0 && lazyInterval < 100) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval++;
                //IMPLEMENTING lazy
                Vector v = this.getTablesValues(lazy);

                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");

                    //                  stmt.execute("SET GLOBAL max_allowed_packet=1073741824");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            intBlobInterval = 0;

                        }
                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();

                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    System.out.println("FAILED QUERY1==" + insert1);
                    System.out.println("FAILED QUERY2==" + insert2);
                    sqle.printStackTrace();
                    System.exit(1);
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

From source file:com.mysql.stresstool.RunnableQueryInsert.java

public void run() {

    BufferedReader d = null;/*from ww w  .j  av  a 2s  .c  o  m*/
    Connection conn = null;

    try {
        if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) {
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test");
        } else
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"));
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (conn != null) {

        try {

            Statement stmt = null;
            //                ResultSet rs = null;
            //                ResultSet rs2 = null;

            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.execute("SET AUTOCOMMIT=0");
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                if (repeat > 0 && lazyInterval < 500) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval++;
                //IMPLEMENTING lazy
                Vector v = this.getTablesValues(lazy);

                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");
                    stmt.execute("SET GLOBAL max_allowed_packet=1073741824");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            intBlobInterval = 0;

                        }
                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();
                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    System.out.println("FAILED QUERY1==" + insert1);
                    System.out.println("FAILED QUERY2==" + insert2);
                    sqle.printStackTrace();
                    System.exit(1);
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

From source file:org.apache.hadoop.hive.metastore.txn.TxnHandler.java

@Override
@RetrySemantics.ReadOnly/* w w  w .  j ava  2 s .  co  m*/
public GetOpenTxnsResponse getOpenTxns() throws MetaException {
    try {
        // We need to figure out the current transaction number and the list of
        // open transactions.  To avoid needing a transaction on the underlying
        // database we'll look at the current transaction number first.  If it
        // subsequently shows up in the open list that's ok.
        Connection dbConn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            /**
             * This runs at READ_COMMITTED for exactly the same reason as {@link #getOpenTxnsInfo()}
             */
            dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED);
            stmt = dbConn.createStatement();
            String s = "select ntxn_next - 1 from NEXT_TXN_ID";
            LOG.debug("Going to execute query <" + s + ">");
            rs = stmt.executeQuery(s);
            if (!rs.next()) {
                throw new MetaException(
                        "Transaction tables not properly " + "initialized, no record found in next_txn_id");
            }
            long hwm = rs.getLong(1);
            if (rs.wasNull()) {
                throw new MetaException(
                        "Transaction tables not properly " + "initialized, null record found in next_txn_id");
            }
            close(rs);
            List<Long> openList = new ArrayList<Long>();
            //need the WHERE clause below to ensure consistent results with READ_COMMITTED
            s = "select txn_id, txn_state from TXNS where txn_id <= " + hwm + " order by txn_id";
            LOG.debug("Going to execute query<" + s + ">");
            rs = stmt.executeQuery(s);
            long minOpenTxn = Long.MAX_VALUE;
            BitSet abortedBits = new BitSet();
            while (rs.next()) {
                long txnId = rs.getLong(1);
                openList.add(txnId);
                char c = rs.getString(2).charAt(0);
                if (c == TXN_OPEN) {
                    minOpenTxn = Math.min(minOpenTxn, txnId);
                } else if (c == TXN_ABORTED) {
                    abortedBits.set(openList.size() - 1);
                }
            }
            LOG.debug("Going to rollback");
            dbConn.rollback();
            ByteBuffer byteBuffer = ByteBuffer.wrap(abortedBits.toByteArray());
            GetOpenTxnsResponse otr = new GetOpenTxnsResponse(hwm, openList, byteBuffer);
            if (minOpenTxn < Long.MAX_VALUE) {
                otr.setMin_open_txn(minOpenTxn);
            }
            return otr;
        } catch (SQLException e) {
            LOG.debug("Going to rollback");
            rollbackDBConn(dbConn);
            checkRetryable(dbConn, e, "getOpenTxns");
            throw new MetaException(
                    "Unable to select from transaction database, " + StringUtils.stringifyException(e));
        } finally {
            close(rs, stmt, dbConn);
        }
    } catch (RetryException e) {
        return getOpenTxns();
    }
}

From source file:dao.CollabrumDaoDb.java

private void deleteRBCollabrum(String directoryId, String collabrumId, String userId, String userLogin)
        throws BaseDaoException {

    if (RegexStrUtil.isNull(userId) || RegexStrUtil.isNull(collabrumId) || RegexStrUtil.isNull(directoryId)
            || RegexStrUtil.isNull(userLogin)) {
        throw new BaseDaoException("params are null");
    }/*from   w w w  . j  a va2s  . com*/

    List tidList = getTidList(collabrumId);
    List blobEntryList = getBlobEntryList(collabrumId);

    Vector ridVector = new Vector();
    for (int i = 0; i < tidList.size(); i++) {
        /* get list of rids from collmessages */
        List ridList = getRidList((String) ((ColTopic) tidList.get(i)).getValue(DbConstants.TID));
        ridVector.add(ridList);
    }

    /**
     * get the members list from collmembers, then access each record in this table
     * collblock (deleteAllColBlockQuery) partitioned on loginid
     * deleteColBlockQuery.run(conn, collabrumId);
     */
    deleteBlockedMembers(collabrumId);

    /**
     *  Get scalability datasource with no partitions for colladmin, collmembers, dircoll, collabrum
     */
    String sourceName = scalabilityManager.getWriteZeroScalability();
    ds = scalabilityManager.getSource(sourceName);
    if (ds == null) {
        StringBuffer sb = new StringBuffer("ds is null, deleteCollabrum() ");
        sb.append(sourceName);
        sb.append(" collabrumId = ");
        sb.append(collabrumId);
        throw new BaseDaoException(sb.toString());
    }

    HashSet result = null;
    Connection conn = null;

    /**
     * non partitioned tables
     */
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        result = listModeratorQuery.run(conn, collabrumId);

        /** 
         * Not partitioned
              * collabrum, (deleteQuery) 
         * colladmin (deleteAdminQuery)
         * dircoll (deleteDirColQuery)
              * collmembers (deleteColMembersQuery), 
         *
         * collblobtags (deleteColBlobTagsQuery)
         * collblogtags (deleteColBlogTagsQuery)
         * collabrum_ind, (deleteCollabrumIndexQuery)
              * collblob_ind,  (deleteColBlobIndexQuery)
              * collmessages_ind, (deleteColMessagesIndexQuery) 
              * colltopics_ind,  (deleteColTopicsIndexQuery)
         */

        deleteQuery.run(conn, collabrumId);
        deleteAdminQuery.run(conn, collabrumId);
        deleteDircollQuery.run(conn, collabrumId);
        deleteAllMembersQuery.run(conn, collabrumId);

        /* new ones */
        deleteColBlobTagsQuery.run(conn, collabrumId);
        deleteColBlogTagsQuery.run(conn, collabrumId);
        deleteCollabrumIndexQuery.run(conn, collabrumId);

        for (int i = 0; i < blobEntryList.size(); i++) {
            deleteColBlobIndexQuery.run(conn,
                    (String) ((Photo) blobEntryList.get(i)).getValue(DbConstants.ENTRYID));
        }

        for (int i = 0; i < tidList.size(); i++) {
            deleteColTopicsIndexQuery.run(conn, (String) ((ColTopic) tidList.get(i)).getValue(DbConstants.TID));
        }

        for (int i = 0; i < ridVector.size(); i++) {
            List ridList = (List) ridVector.elementAt(i);
            for (int j = 0; i < ridList.size(); j++) {
                deleteColMessagesIndexQuery.run(conn,
                        (String) ((ColMessage) ridList.get(j)).getValue(DbConstants.RID));
            }
        }

    } 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 deleteCollabrum() ");
                sb.append("collabrumId = ");
                sb.append(collabrumId);
                sb.append(" userId = ");
                sb.append(userId);
                throw new BaseDaoException(sb.toString(), e2);
            }
            StringBuffer sb = new StringBuffer(" rollback() exception, for deleteCollabrum() ");
            sb.append("collabrumId = ");
            sb.append(collabrumId);
            sb.append(" userId = ");
            sb.append(userId);
            throw new BaseDaoException(sb.toString(), e1);
        }
    }

    // connection commit
    try {
        conn.commit();
    } catch (Exception e3) {
        StringBuffer sb = new StringBuffer(" commit() exception, for deleteCollabrum() collabrumId = ");
        sb.append(collabrumId);
        sb.append(" userId = ");
        sb.append(userId);
        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(), deleteCollabrum() collabrumId = ");
        sb.append(collabrumId);
        sb.append(" userId = ");
        sb.append(userId);
        throw new BaseDaoException(sb.toString(), e4);
    }

    deleteCollMessages(collabrumId, tidList);
    deleteCollTopics(collabrumId, tidList);

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

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

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

    fqn = cacheUtil.fqn(DbConstants.DIRECTORY);
    if (treeCache.exists(fqn, directoryId)) {
        treeCache.remove(fqn, directoryId);
    }

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

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

    /**
     *  delete collabrum messages
     */
    for (int i = 0; i < tidList.size(); i++) {
        StringBuffer sb = new StringBuffer(collabrumId);
        sb.append("-");
        sb.append(tidList.get(i));
        String key = sb.toString();
        fqn = cacheUtil.fqn(DbConstants.COLMSGS);
        if (treeCache.exists(fqn, key)) {
            treeCache.remove(fqn, key);
        }
        fqn = cacheUtil.fqn(DbConstants.COLTOPIC);
        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);
    }

    // deleting user pages for each admin as we want them to be updated
    if ((result != null) && (result.size() > 0)) {
        Iterator it = result.iterator();
        StringBuffer sb = new StringBuffer();
        while (it.hasNext()) {
            Collabrum collabrum = (Collabrum) it.next();
            String adminUser = collabrum.getValue(DbConstants.LOGIN);
            if (!RegexStrUtil.isNull(adminUser)) {
                fqn = cacheUtil.fqn(DbConstants.USER_PAGE);
                if (treeCache.exists(fqn, adminUser)) {
                    treeCache.remove(fqn, adminUser);
                }

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

                fqn = cacheUtil.fqn(DbConstants.MEM_AS_MODERATOR_LIST);
                if (treeCache.exists(fqn, adminUser)) {
                    treeCache.remove(fqn, adminUser);
                }

                String adminId = collabrum.getValue(DbConstants.LOGIN_ID);
                fqn = cacheUtil.fqn(DbConstants.BLOCKED_COLLABRUM_LIST);
                if (treeCache.exists(fqn, adminId)) {
                    treeCache.remove(fqn, adminId);
                }

                // delete organizer key = collabrumid-memberid
                sb.delete(0, sb.length());
                sb.append(collabrumId);
                sb.append("-");
                sb.append(adminId);
                fqn = cacheUtil.fqn(DbConstants.ORGANIZER);
                if (treeCache.exists(fqn, sb.toString())) {
                    treeCache.remove(fqn, sb.toString());
                }
            }
        }
    }

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

    /**
     * Jboss methods - 
     * fqn - full qualified name
     * check if the streamblob already set in the cache
     * If it exists, remove the bean from the cache.
     */

    for (int i = 0; i < blobEntryList.size(); i++) {
        String entryId = (String) ((Photo) blobEntryList.get(i)).getValue(DbConstants.ENTRYID);
        fqn = cacheUtil.fqn(DbConstants.PHOTO);
        if (treeCache.exists(fqn, entryId)) {
            treeCache.remove(fqn, entryId);
        }
        StringBuffer buf = new StringBuffer(collabrumId);
        buf.append("-");
        buf.append(entryId);
        String key = buf.toString();
        fqn = cacheUtil.fqn(DbConstants.COL_STREAM_BLOB);
        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.COLL_CAT);
    StringBuffer sb = new StringBuffer(collabrumId);
    sb.append("-");
    sb.append(DbConstants.PHOTO_CATEGORY);
    if (treeCache.exists(fqn, sb.toString())) {
        treeCache.remove(fqn, sb.toString());
    }

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

From source file:org.apache.hadoop.hive.metastore.txn.TxnHandler.java

@Override
@RetrySemantics.SafeToRetry/*from w ww .ja  v a 2s.c o  m*/
public HeartbeatTxnRangeResponse heartbeatTxnRange(HeartbeatTxnRangeRequest rqst) throws MetaException {
    try {
        Connection dbConn = null;
        Statement stmt = null;
        HeartbeatTxnRangeResponse rsp = new HeartbeatTxnRangeResponse();
        Set<Long> nosuch = new HashSet<Long>();
        Set<Long> aborted = new HashSet<Long>();
        rsp.setNosuch(nosuch);
        rsp.setAborted(aborted);
        try {
            /**
             * READ_COMMITTED is sufficient since {@link #heartbeatTxn(java.sql.Connection, long)}
             * only has 1 update statement in it and
             * we only update existing txns, i.e. nothing can add additional txns that this operation
             * would care about (which would have required SERIALIZABLE)
             */
            dbConn = getDbConn(Connection.TRANSACTION_READ_COMMITTED);
            /*do fast path first (in 1 statement) if doesn't work, rollback and do the long version*/
            stmt = dbConn.createStatement();
            List<String> queries = new ArrayList<>();
            int numTxnsToHeartbeat = (int) (rqst.getMax() - rqst.getMin() + 1);
            List<Long> txnIds = new ArrayList<>(numTxnsToHeartbeat);
            for (long txn = rqst.getMin(); txn <= rqst.getMax(); txn++) {
                txnIds.add(txn);
            }
            TxnUtils.buildQueryWithINClause(conf, queries,
                    new StringBuilder("update TXNS set txn_last_heartbeat = " + getDbTime(dbConn)
                            + " where txn_state = " + quoteChar(TXN_OPEN) + " and "),
                    new StringBuilder(""), txnIds, "txn_id", true, false);
            int updateCnt = 0;
            for (String query : queries) {
                LOG.debug("Going to execute update <" + query + ">");
                updateCnt += stmt.executeUpdate(query);
            }
            if (updateCnt == numTxnsToHeartbeat) {
                //fast pass worked, i.e. all txns we were asked to heartbeat were Open as expected
                dbConn.commit();
                return rsp;
            }
            //if here, do the slow path so that we can return info txns which were not in expected state
            dbConn.rollback();
            for (long txn = rqst.getMin(); txn <= rqst.getMax(); txn++) {
                try {
                    heartbeatTxn(dbConn, txn);
                } catch (NoSuchTxnException e) {
                    nosuch.add(txn);
                } catch (TxnAbortedException e) {
                    aborted.add(txn);
                }
            }
            return rsp;
        } catch (SQLException e) {
            LOG.debug("Going to rollback");
            rollbackDBConn(dbConn);
            checkRetryable(dbConn, e, "heartbeatTxnRange(" + rqst + ")");
            throw new MetaException(
                    "Unable to select from transaction database " + StringUtils.stringifyException(e));
        } finally {
            close(null, stmt, dbConn);
        }
    } catch (RetryException e) {
        return heartbeatTxnRange(rqst);
    }
}

From source file:com.concursive.connect.web.modules.login.utils.UserUtils.java

/**
 * Creates a user's profile and sets the id on the user object
 *
 * @param db//from  w  w  w .  j  av  a2  s.co  m
 * @param user
 * @param prefs
 * @throws SQLException
 */
public static void addUserProfile(Connection db, User user, ApplicationPrefs prefs) throws SQLException {
    boolean autoCommit = db.getAutoCommit();
    try {
        if (autoCommit) {
            db.setAutoCommit(false);
        }
        // Determine the project features
        ProjectFeatures features = new ProjectFeatures();
        ArrayList<String> modules = new ArrayList<String>();
        modules.add("Profile");
        String enabledModules = prefs.get(ApplicationPrefs.DEFAULT_USER_PROFILE_TABS);
        //      if (enabledModules == null || enabledModules.contains("Reviews")) {
        //       modules.add("Reviews");
        //      }
        if (enabledModules == null || enabledModules.contains("Blog")) {
            modules.add("News=My Blog");
        }
        if (enabledModules == null || enabledModules.contains("Wiki")) {
            modules.add("Wiki=About Me");
        }
        if (enabledModules == null || enabledModules.contains("Classifieds")) {
            modules.add("My Classifieds");
        }
        if (enabledModules == null || enabledModules.contains("Documents")) {
            modules.add("Documents=My Documents");
        }
        if (enabledModules == null || enabledModules.contains("Lists")) {
            modules.add("My Lists");
        }
        if (enabledModules == null || enabledModules.contains("Badges")) {
            modules.add("My Badges");
        }
        if (enabledModules == null || enabledModules.contains("Friends")) {
            modules.add("Team=Friends");
        }
        if (enabledModules == null || enabledModules.contains("Messages")) {
            modules.add("Messages");
        }
        int count = 0;
        for (String modulePreference : modules) {
            String moduleName = null;
            String moduleLabel = null;
            if (modulePreference.indexOf("=") != -1) {
                moduleName = modulePreference.split("=")[0];
                moduleLabel = modulePreference.split("=")[1];
            } else {
                moduleName = modulePreference;
                moduleLabel = modulePreference;
            }
            ObjectUtils.setParam(features, "order" + moduleName, count + 1);
            ObjectUtils.setParam(features, "label" + moduleName, moduleLabel);
            ObjectUtils.setParam(features, "show" + moduleName, true);
        }
        // Determine the category id
        ProjectCategoryList projectCategoryList = new ProjectCategoryList();
        projectCategoryList.setCategoryDescription("People");
        projectCategoryList.buildList(db);
        ProjectCategory people = projectCategoryList.getFromValue("People");
        // Create a user project profile
        Project project = new Project();
        project.setInstanceId(user.getInstanceId());
        project.setGroupId(1);
        project.setApproved(user.getEnabled());
        project.setProfile(true);
        project.setCategoryId(people.getId());
        project.setOwner(user.getId());
        project.setEnteredBy(user.getId());
        project.setModifiedBy(user.getId());
        // Determine how to record the user's name in the profile
        if ("true".equals(prefs.get(ApplicationPrefs.USERS_ARE_ANONYMOUS))) {
            project.setTitle(user.getNameFirstLastInitial());
        } else {
            project.setTitle(user.getNameFirstLast());
        }
        project.setKeywords(user.getNameFirstLast());
        project.setShortDescription("My Profile");
        project.setCity(user.getCity());
        project.setState(user.getState());
        project.setCountry(user.getCountry());
        project.setPostalCode(user.getPostalCode());
        project.setFeatures(features);
        // Access rules will allow this profile to be searched and seen
        project.getFeatures().setUpdateAllowGuests(true);
        project.getFeatures().setAllowGuests(true);
        if ("true".equals(prefs.get(ApplicationPrefs.INFORMATION_IS_SENSITIVE))) {
            project.getFeatures().setAllowGuests(false);
        }
        // A join request can be made which requires approval by the profile owner
        project.getFeatures().setUpdateAllowParticipants(true);
        project.getFeatures().setAllowParticipants(true);
        // Anyone can see the profile page
        project.getFeatures().setUpdateMembershipRequired(true);
        project.getFeatures().setMembershipRequired(true);
        project.insert(db);
        project.getFeatures().setId(project.getId());
        project.getFeatures().update(db);
        updateProfileProjectId(db, user, project);
        // Determine which role level the user is for their own profile
        LookupList roleList = CacheUtils.getLookupList("lookup_project_role");
        int defaultUserLevel = roleList.getIdFromLevel(TeamMember.MANAGER);
        if (!user.getAccessAdmin() && prefs.has(ApplicationPrefs.DEFAULT_USER_PROFILE_ROLE)) {
            int userLevelPreference = roleList
                    .getIdFromValue(prefs.get(ApplicationPrefs.DEFAULT_USER_PROFILE_ROLE));
            if (userLevelPreference > -1) {
                defaultUserLevel = userLevelPreference;
            }
        }
        // Add the user as a member of the profile
        TeamMember member = new TeamMember();
        member.setUserId(user.getId());
        member.setProjectId(project.getId());
        member.setUserLevel(defaultUserLevel);
        member.setStatus(TeamMember.STATUS_ADDED);
        member.setNotification(true);
        member.setEnteredBy(user.getId());
        member.setModifiedBy(user.getId());
        member.insert(db);
        if (autoCommit) {
            db.commit();
        }
        // Success, now that the database is committed, invalidate the cache
        CacheUtils.invalidateValue(Constants.SYSTEM_USER_CACHE, user.getId());
        CacheUtils.invalidateValue(Constants.SYSTEM_PROJECT_CACHE, project.getId());
    } catch (Exception e) {
        LOG.error("addUserProfile", e);
        if (autoCommit) {
            db.rollback();
        }
        throw new SQLException(e.getMessage());
    } finally {
        if (autoCommit) {
            db.setAutoCommit(true);
        }
    }
}

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

public Integer updateObjects(final Integer resultsetId, final List<BaseModelData> newItemList,
        final String condition, String username) throws HiddenException {

    // JardinLogger.info("Updating records...");

    int result = 0;
    Connection connection = this.dbConnectionHandler.getConn();
    final String sep = ",";
    boolean defaultPrimaryKeys = condition.equalsIgnoreCase("$-notspec-$");

    try {/*from  w  ww .  j  a v  a2  s.c  o  m*/
        ResultSetMetaData metadata = this.dbProperties.getResultsetMetadata(connection, resultsetId);
        String tableName = metadata.getTableName(1);

        // TODO Creare un oggetto per la memorizzazione colonna->valore
        List<BaseModelData> PKs = this.dbProperties.getResultsetPrimaryKeys(resultsetId);

        String PKset = "";
        connection.setAutoCommit(false);
        for (BaseModelData record : newItemList) {

            boolean conditionFounded = false;
            if (defaultPrimaryKeys) {
                conditionFounded = true;

                // richiesta di update da griglia o dettaglio
                for (BaseModelData pk : PKs) {
                    PKset += (String) pk.get("PK_NAME") + "=? AND ";
                }
                PKset = PKset.substring(0, PKset.length() - 5); // Strips
                // " AND "

            } else {
                PKset = condition + "=? ";
            }

            String set = "";
            Collection<String> properties = record.getPropertyNames();
            for (String property : properties) {
                if (property.equalsIgnoreCase(condition)) {
                    conditionFounded = true;
                } else {
                    set += "`" + property + "`=? " + sep;
                }
            }

            if (!conditionFounded) {
                throw new VisibleException("condizione di UPDATE non trovata nel file");
            }

            set = set.substring(0, set.length() - sep.length());

            String query = "UPDATE `" + tableName + "` SET " + set + " WHERE " + PKset;

            PreparedStatement ps = (PreparedStatement) connection.prepareStatement(query);
            int i = 1;

            /* Set prepared statement values for changing fields */
            for (String property : properties) {
                if (!property.equalsIgnoreCase(condition)) {
                    i += this.putJavaObjectInPs(ps, i, record.get(property));
                }
            }

            /* Set prepared statement values for where condition fields */
            if (defaultPrimaryKeys) {
                for (BaseModelData pk : PKs) {
                    Object value = record.get((String) pk.get("PK_NAME"));
                    i += this.putJavaObjectInPs(ps, i, value);
                }
            } else {
                Object value = record.get(condition);
                i += this.putJavaObjectInPs(ps, i, value);
            }

            // Log.debug("Query UPDATE: " + ps);
            JardinLogger.debug(username, "UPDATE (" + ps.toString() + ")");
            int num = ps.executeUpdate();
            if (num > 0) {
                JardinLogger.debug(username, "UPDATE (" + ps.toString() + ")");
            }
            result += num;
        }
        connection.commit();
        connection.setAutoCommit(true);
        // JardinLogger.info("Records updated");
    } catch (Exception e) {
        try {
            connection.rollback();
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        // Log.warn("Errore SQL", e);
        e.printStackTrace();
        throw new HiddenException("Errore durante l'aggiornamento del record:\n" + e.getLocalizedMessage());
    } finally {

        this.dbConnectionHandler.closeConn(connection);
    }
    return result;
}