Example usage for java.sql Connection commit

List of usage examples for java.sql Connection commit

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

Introduction

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

Prototype

void commit() throws SQLException;

Source Link

Document

Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.

Usage

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmFobbidenStoreIntoDB(Map<String, DgmForbidden> map, ApplicationContext context) {
    /**//from w  w  w .j ava2 s .  c om
     * INSERT INTO TAB_GPSEVENT_AREA SELECT
     * CODE,ALARMTYPE,BEGINTIME,ENDTIME,'' FROM ALARMFOBBIDEN_REA
     */
    Connection conn = null;
    String sql = "";
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmForbidden pos = map.get(key);
            /*
             * String sql = "insert into ALARMFOBBIDEN_REA " +
             * " (CODE,LICENSE,LICENSECOLOR,ALARMTYPE,BEGINTIME,ENDTIME) " +
             * " values (" + "'" + pos.getCode() + "'," + "'" +
             * pos.getLicense() + "'," + "'" + pos.getLicenseColor() + "',"
             * + "'" + pos.getAlarmType() + "'," + "'" + pos.getBeginTime()
             * + "','" + pos.getEndTime() + "')";
             */
            sql = "insert into TAB_GPSEVENT_AREA " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL) " + " values ("
                    + "'" + pos.getCode() + "'," + "'" + pos.getAlarmType() + "'," + "'" + pos.getBeginTime()
                    + "'," + "'" + pos.getEndTime() + "'," + "'" + "')";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmForbidden success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getForbiddeningMap().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:com.funambol.json.coredb.dao.DBManager.java

public boolean deleteItem(String table, Map<String, String> item, String... keys) throws Exception {
    if (ds == null)
        throw new Exception("Data source is null.");

    Connection connection = null;
    Statement stmt = null;/*w  ww  .  jav a2  s  . co m*/
    ResultSet rsltSet = null;
    ResultSetMetaData metadata = null;

    try {
        connection = ds.getConnection();
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred retrieving connection.", ex);
    }

    if (connection == null) {
        throw new Exception("Connection is null.");
    }

    try {
        stmt = connection.createStatement();
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred creating statement.", ex);
    }

    String query = prepareDeleteQuery(table, item, keys);

    try {
        int affectedRows = stmt.executeUpdate(query);
        if (affectedRows != 1) {
            connection.rollback();
            return false;
        }

        connection.commit();
        return true;
    } catch (SQLException ex) {
        release(connection, stmt, rsltSet);
        throw new Exception("An error occurred executing query [" + query + "].", ex);
    } finally {
        release(connection, stmt, rsltSet);
    }
}

From source file:dk.netarkivet.archive.arcrepositoryadmin.ReplicaCacheHelpers.java

/**
 * Method for updating an entry in the replicafileinfo table.
 * This method does not update the 'checksum_checkdatetime'
 * and 'filelist_checkdatetime'.// w w  w.j  a  v  a  2 s.  co m
 *
 * @param replicafileinfoGuid The guid to update.
 * @param checksum The new checksum for the entry.
 * @param state The state for the upload.
 * @param con An open connection to the archive database
 * @throws IOFailure If an error occurs in the database connection.
 */
protected static void updateReplicaFileInfo(long replicafileinfoGuid, String checksum, ReplicaStoreState state,
        Connection con) throws IOFailure {
    PreparedStatement statement = null;
    try {
        final String sql = "UPDATE replicafileinfo SET checksum = ?, "
                + "upload_status = ?, filelist_status = ?, "
                + "checksum_status = ? WHERE replicafileinfo_guid = ?";

        FileListStatus fls;
        ChecksumStatus cs;

        if (state == ReplicaStoreState.UPLOAD_COMPLETED) {
            fls = FileListStatus.OK;
            cs = ChecksumStatus.OK;
        } else if (state == ReplicaStoreState.UPLOAD_FAILED) {
            fls = FileListStatus.MISSING;
            cs = ChecksumStatus.UNKNOWN;
        } else {
            fls = FileListStatus.NO_FILELIST_STATUS;
            cs = ChecksumStatus.UNKNOWN;
        }

        // complete the SQL statement.
        statement = DBUtils.prepareStatement(con, sql, checksum, state.ordinal(), fls.ordinal(), cs.ordinal(),
                replicafileinfoGuid);
        statement.executeUpdate();
        con.commit();
    } catch (Exception e) {
        String errMsg = "Problems with updating a ReplicaFileInfo";
        log.warn(errMsg);
        throw new IOFailure(errMsg, e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
    }
}

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmEntryExitStoreIntoDB(Map<String, DgmEntryExit> map, ApplicationContext context) {
    /**/*from   w w w  .j  a va 2 s .c om*/
     * INSERT INTO TAB_GPSEVENT_ILLEGALENTRYEXIT SELECT
     * CODE,'illegalEntryExit',BEGIN_TIME,END_TIME,DETAIL,ROAD FROM
     * ALARMILLEGALEXIT_REA
     */
    String sql = "";
    Connection conn = null;
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmEntryExit pos = map.get(key);
            /*
             * String sql = "insert into ALARMILLEGALEXIT_REA " +
             * " (CODE,TYPE,BEGIN_TIME,END_TIME,DETAIL,ROAD) " + " values ("
             * + "'" + pos.getCode() + "'," + "'" + pos.getType() + "'," +
             * "'" + pos.getBegin_time() + "'," + "'" + pos.getEnd_time() +
             * "'," + "'" + pos.getDetail() + "','" + pos.getRoad() + "')";
             */
            sql = "insert into TAB_GPSEVENT_ILLEGALENTRYEXIT " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL,ROAD) "
                    + " values (" + "'" + pos.getCode() + "'," + "'illegalEntryExit'," + "'"
                    + pos.getBegin_time() + "'," + "'" + pos.getEnd_time() + "'," + "'" + pos.getDetail()
                    + "','" + pos.getRoad() + "')";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmEntryExit success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getExitMap().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

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

@Override
public String getPrimaryKey(String tableName) {
    Connection conn = null;
    PreparedStatement pStmt = null;
    ResultSet rset = null;//from   ww  w  .  jav  a2s.com
    List<String> columns = new ArrayList<String>();

    try {
        conn = getConnection();

        pStmt = conn.prepareStatement(QUERY_PRIMARY_KEY_FOR_TABLE, ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        pStmt.setString(1, tableName);
        rset = pStmt.executeQuery();

        while (rset.next()) {
            columns.add(rset.getString(1));
        }
        conn.commit();
    } catch (SQLException e) {
        try {
            conn.rollback();
        } catch (Exception ex) {
            LOG.error("Failed to rollback transaction", ex);
        }
        LOG.error("Failed to list columns", e);
    } finally {
        if (rset != null) {
            try {
                rset.close();
            } catch (SQLException ex) {
                LOG.error("Failed to close resultset", ex);
            }
        }
        if (pStmt != null) {
            try {
                pStmt.close();
            } catch (Exception ex) {
                LOG.error("Failed to close statement", ex);
            }
        }

        try {
            close();
        } catch (SQLException ex) {
            LOG.error("Unable to discard connection", ex);
        }
    }

    if (columns.size() == 0) {
        // Table has no primary key
        return null;
    }

    if (columns.size() > 1) {
        // The primary key is multi-column primary key. Warn the user.
        // TODO select the appropriate column instead of the first column based
        // on the datatype - giving preference to numerics over other types.
        LOG.warn("The table " + tableName + " " + "contains a multi-column primary key. Sqoop will default to "
                + "the column " + columns.get(0) + " only for this job.");
    }

    return columns.get(0);
}

From source file:dk.netarkivet.archive.arcrepositoryadmin.ReplicaCacheHelpers.java

/**
 * When a new file is inserted into the database, each replica gets
 * a new entry in the replicafileinfo table for this file.
 * The fields for this new entry are set to the following:
 * - file_id = argument./*from   w  w  w .  ja  v a 2s .  c o  m*/
 * - replica_id = The id of the current replica.
 * - filelist_status = NO_FILELIST_STATUS.
 * - checksum_status = UNKNOWN.
 * - upload_status = NO_UPLOAD_STATUS.
 *
 * The replicafileinfo_guid is automatically created by the database,
 * and the dates are set to null.
 *
 * @param fileId The id for the file.
 * @param con An open connection to the archive database
 * @throws IllegalState If the file could not be entered into the database.
 */
protected static void createReplicaFileInfoEntriesInDB(long fileId, Connection con) throws IllegalState {
    PreparedStatement statement = null;
    try {
        // init variables
        List<String> repIds = ReplicaCacheHelpers.retrieveIdsFromReplicaTable(con);

        // Make a entry for each replica.
        for (String repId : repIds) {
            // create if it does not exists already.
            if (!existsReplicaFileInfoInDB(fileId, repId, con)) {
                // Insert with the known values (no dates).
                statement = DBUtils.prepareStatement(con,
                        "INSERT INTO replicafileinfo " + "(file_id, replica_id, filelist_status, "
                                + "checksum_status, upload_status ) VALUES " + "( ?, ?, ?, ?, ? )",
                        fileId, repId, FileListStatus.NO_FILELIST_STATUS.ordinal(),
                        ChecksumStatus.UNKNOWN.ordinal(), ReplicaStoreState.UNKNOWN_UPLOAD_STATE.ordinal());

                // execute the SQL statement
                statement.executeUpdate();
                con.commit();
                statement.close(); // Important to cleanup!
            }
        }
    } catch (SQLException e) {
        throw new IllegalState("Cannot add replicafileinfo to the " + "database.", e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
    }
}

From source file:com.china317.gmmp.gmmp_report_analysis.App.java

private static void DgmIllegalParkingStoreIntoDB(Map<String, DgmIllegalParking> map,
        ApplicationContext context) {// w  w w.  j av  a2 s .  co  m
    /*
     * INSERT INTO TAB_GPSEVENT_ILLEGALPARKING SELECT
     * CODE,'illegalParking',BEGIN_TIME,END_TIME,'',FLAG FROM
     * ALARMILLEGALPARKING_REA
     */
    Connection conn = null;
    String sql = "";
    try {

        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientDgm");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = map.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            DgmIllegalParking pos = map.get(key);
            /*
             * String sql = "insert into TAB_GPSEVENT_ILLEGALPARKING " +
             * " (CODE,LICENSE,TYPE,BEGINTIME,ENDTIME,FLAG) " + " values ("
             * + "'" + pos.getCode() + "'," + "'" + pos.getLicense() + "',"
             * + "'" + pos.getType() + "'," + "'" + pos.getBeginTime() +
             * "'," + "'" + pos.getEndTime() + "','" + pos.getFlag() + "')";
             */
            sql = "insert into TAB_GPSEVENT_ILLEGALPARKING " + " (VID,TYPE,BEGIN_TIME,END_TIME,DETAIL,IS_END) "
                    + " values (" + "'" + pos.getCode() + "'," + "'" + pos.getType() + "'," + "'"
                    + pos.getBeginTime() + "'," + "'" + pos.getEndTime() + "'," + "'" + "'," + pos.getFlag()
                    + ")";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB DgmIllegalParking success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        DgmAnalysisImp.getInstance().getIllegalParking().clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

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

public void synchronize() throws VictimsException {
    Throwable throwable = null;/*from   www.  ja v a 2  s.co m*/
    try {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
        Savepoint savepoint = connection.setSavepoint();

        try {
            VictimsService service = new VictimsService();
            Date since = lastUpdated();

            int removed = remove(connection, service.removed(since));
            int updated = update(connection, service.updates(since));

            if (removed > 0 || updated > 0) {
                cache.purge();
            }

            setLastUpdate(new Date());
        } catch (IOException e) {
            throwable = e;
        } catch (SQLException e) {
            throwable = e;
        } finally {
            if (throwable != null) {
                connection.rollback(savepoint);
            }
            connection.releaseSavepoint(savepoint);
            connection.commit();
            connection.close();
        }
    } catch (SQLException e) {
        throwable = e;
    }

    if (throwable != null) {
        throw new VictimsException("Failed to sync database", throwable);
    }
}

From source file:com.globalsight.everest.permission.Permission.java

/**
 * Adds the given permission to the database PERMISSION table if it does not
 * already exist in the map. This does update one by one, but this method
 * should almost never be called except on startup and if new permissions
 * were actually added, so there is no reason to batch.
 * /*www .j  a va 2  s.c  om*/
 * @param p_added
 *            set to true if anything was added
 * @param p_perm
 *            permission name (should be above defined constant)
 */
static private boolean addPermission(long id, String p_perm) {
    boolean added = false;

    Connection c = null;
    PreparedStatement stmt = null;

    try {
        c = ConnectionPool.getConnection();
        c.setAutoCommit(false);

        if (s_idMap.isEmpty()) {
            stmt = c.prepareStatement(SQL_INSERT_FIRST_PERM);
            stmt.executeUpdate();
            s_idMap.put(p_perm, new Long(1));

            if (logger.isDebugEnabled()) {
                logger.debug("Added " + p_perm + " to the table.");
            }
        } else if (!s_idMap.containsKey(p_perm)) {
            stmt = c.prepareStatement(SQL_INSERT_PERM);
            stmt.setLong(1, id);
            stmt.setString(2, p_perm);
            stmt.executeUpdate();

            added = true;

            if (logger.isDebugEnabled()) {
                logger.debug("Added " + p_perm + " to the table.");
            }
        }

        c.commit();
    } catch (Exception ex) {
        logger.error("Failed to add permission" + p_perm + " to the database.", ex);
        added = false;
    } finally {
        ConnectionPool.silentClose(stmt);
        ConnectionPool.silentReturnConnection(c);
    }

    return added;
}

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

/**
 * @see edu.uga.cs.fluxbuster.db.DBInterface#executeQueryWithResult(java.lang.String)
 */// w  w  w  . jav a 2s  . c  om
@Override
public ResultSet executeQueryWithResult(String query) {
    ResultSet retval = null;
    Connection con = null;
    Statement stmt = null;
    try {
        con = this.getConnection();
        con.setAutoCommit(false);
        stmt = con.createStatement();
        retval = stmt.executeQuery(query);
        con.commit();
    } catch (SQLException e) {
        retval = null;
        if (log.isErrorEnabled()) {
            log.error(query, e);
        }
        try {
            if (con != null && !con.isClosed()) {
                con.rollback();
            }
        } catch (SQLException e1) {
            if (log.isErrorEnabled()) {
                log.error("Error during rollback.", e1);
            }
        }
    } finally {
        try {
            if (con != null && !con.isClosed()) {
                con.setAutoCommit(true);
                con.close();
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("Error during close.", e);
            }
        }
    }
    return retval;
}