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.uber.stream.kafka.chaperone.collector.reporter.DbAuditReporter.java

public int removeOldRecord() {
    Connection conn = null;
    PreparedStatement stmt = null;
    int affected = 0;
    long ts = System.currentTimeMillis() - auditDbRetentionMs;
    try {//w  w  w .ja v a  2  s  .c  om
        logger.info("Start to remove old records per timestamp={} with retentionMs={}", ts, auditDbRetentionMs);

        conn = getConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(String.format(DELETE_METRICS_SQL, dataTableName));

        Timestamp dbTs = new Timestamp(ts);
        stmt.setTimestamp(1, dbTs);
        affected = stmt.executeUpdate();
        conn.commit();

        REMOVED_RECORDS_COUNTER.mark(affected);
        logger.info("Removed count={} old records per timestamp={} with retentionMs={}", affected, ts,
                auditDbRetentionMs);
    } catch (Exception e) {
        logger.warn("Got exception to remove old records", e);
        FAILED_TO_REMOVE_COUNTER.mark();
        rollback(conn);
    } finally {
        closeDbResource(null, stmt, conn);
    }

    return affected;
}

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

public ProgramProfilingSymbol deleteProfilingSymbol(int id) throws AdaptationException {
    ProgramProfilingSymbol profilingSymbol = null;
    Connection connection = null;
    Statement statement = null;/*from   www  .j  av  a 2  s  . com*/
    ResultSet resultSet = null;

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

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

        if (!resultSet.next()) {
            connection.rollback();
            String msg = "Attempt to delete program profiling " + "symbol failed.";
            log.error(msg);
            throw new AdaptationException(msg);
        }
        profilingSymbol = getProfilingSymbol(resultSet);
        query = "DELETE FROM ProgramProfilingSymbols " + "WHERE id = " + id;

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

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

    return profilingSymbol;

}

From source file:com.gtwm.pb.servlets.AppController.java

public void destroy() {
    super.destroy();
    this.databaseDefn.cancelScheduledEvents();
    Connection conn = null;
    try {// w w  w.  ja v  a2s  . co m
        conn = this.relationalDataSource.getConnection();
        UsageLogger.logDataOlderThan(conn, 0);
        conn.commit();
    } catch (SQLException sqlex) {
        logger.error("SQL exception while performing final logging during shutdown: " + sqlex);
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException sqlex) {
                logger.error("Error closing SQL connection at app close: " + sqlex);
            }
        }
    }
    // Release memory - still not sure if this is actually necessary
    this.databaseDefn = null;
    this.relationalDataSource = null;
    logger.info("agileBase shut down");
}

From source file:edu.lternet.pasta.dml.database.SimpleDatabaseLoader.java

/**
 * /*from  w  w  w .ja  v  a 2s. c  om*/
 */
public void run() {

    if (entity == null) {
        success = false;
        completed = true;
        return;
    }

    //don't reload data if we have it
    if (doesDataExist(entity.getEntityIdentifier())) {
        return;
    }

    AttributeList attributeList = entity.getAttributeList();
    String tableName = entity.getDBTableName();

    String insertSQL = "";
    Vector rowVector = new Vector();
    Connection connection = null;

    try {
        rowVector = this.dataReader.getOneRowDataVector();
        connection = DataManager.getConnection();
        if (connection == null) {
            success = false;
            exception = new Exception("The connection to db is null");
            completed = true;
            return;
        }
        connection.setAutoCommit(false);
        while (!rowVector.isEmpty()) {
            insertSQL = databaseAdapter.generateInsertSQL(attributeList, tableName, rowVector);
            if (insertSQL != null) {
                PreparedStatement statement = connection.prepareStatement(insertSQL);
                statement.execute();
            }
            rowVector = this.dataReader.getOneRowDataVector();
        }

        connection.commit();
        success = true;
    } catch (Exception e) {
        log.error("problem while loading data into table.  Error message: " + e.getMessage());
        e.printStackTrace();
        log.error("SQL string to insert row:\n" + insertSQL);

        success = false;
        exception = e;

        try {
            connection.rollback();
        } catch (Exception ee) {
            System.err.println(ee.getMessage());
        }
    } finally {
        try {
            connection.setAutoCommit(true);
        } catch (Exception ee) {
            log.error(ee.getMessage());
        }

        DataManager.returnConnection(connection);
    }
}

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

@Test
public void testJdbcEscapedColumnName() throws Exception {
    // Test a JDBC-based import of a table with a column whose name is
    // a reserved sql keyword (and is thus `quoted`).
    final String TABLE_NAME = "mysql_escaped_col_table";
    setCurTableName(TABLE_NAME);//from   w  w w  .  j a  v  a 2s. c  om
    SqoopOptions options = new SqoopOptions(MySQLTestUtils.CONNECT_STRING, TABLE_NAME);
    options.setUsername(MySQLTestUtils.getCurrentUser());
    ConnManager mgr = new MySQLManager(options);

    Connection connection = null;
    Statement st = null;

    try {
        connection = mgr.getConnection();
        connection.setAutoCommit(false);
        st = connection.createStatement();

        // create the database table and populate it with data.
        st.executeUpdate("DROP TABLE IF EXISTS " + TABLE_NAME);
        st.executeUpdate("CREATE TABLE " + TABLE_NAME + " (" + "id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, "
                + "`table` VARCHAR(24) NOT NULL, " + "`CREATE` DATE, " + "salary FLOAT, "
                + "dept VARCHAR(32))");

        st.executeUpdate(
                "INSERT INTO " + TABLE_NAME + " VALUES(" + "2,'Aaron','2009-05-14',1000000.00,'engineering')");
        connection.commit();
    } finally {
        if (null != st) {
            st.close();
        }

        if (null != connection) {
            connection.close();
        }
    }

    String[] expectedResults = { "2,Aaron,2009-05-14,1000000.0,engineering", };

    doImport(false, false, TABLE_NAME, expectedResults, null);
}

From source file:com.moss.schematrax.SchemaUpdater.java

private void recordUpdate(Connection sqlConnection, String schema, SchemaUpdate update) throws SQLException {
    PreparedStatement preparedStatement = sqlConnection
            .prepareStatement("INSERT INTO " + schema + ".SCHEMA_UPDATES values (?,?)");
    preparedStatement.setString(1, update.getId());
    preparedStatement.setTimestamp(2, new java.sql.Timestamp(new Date().getTime()));
    preparedStatement.execute();/*from   ww w.java  2 s . com*/
    if (manageTransactions)
        sqlConnection.commit();
}

From source file:eagle.storage.jdbc.entity.impl.JdbcEntityUpdaterImpl.java

@Override
public int update(List<E> entities) throws Exception {
    ConnectionManager cm = ConnectionManagerFactory.getInstance();
    TorqueStatementPeerImpl<E> peer = cm.getStatementExecutor(this.jdbcEntityDefinition.getJdbcTableName());
    Connection connection = cm.getConnection();
    connection.setAutoCommit(false);/*from  www  .  j  ava 2s.  co m*/

    StopWatch stopWatch = new StopWatch();
    stopWatch.start();
    int num = 0;
    try {
        for (E entity : entities) {
            String primaryKey = entity.getEncodedRowkey();
            PrimaryKeyCriteriaBuilder pkBuilder = new PrimaryKeyCriteriaBuilder(Arrays.asList(primaryKey),
                    this.jdbcEntityDefinition.getJdbcTableName());
            Criteria selectCriteria = pkBuilder.build();
            if (LOG.isDebugEnabled())
                LOG.debug("Updating by query: " + SqlBuilder.buildQuery(selectCriteria).getDisplayString());
            ColumnValues columnValues = JdbcEntitySerDeserHelper.buildColumnValues(entity,
                    this.jdbcEntityDefinition);
            num += peer.delegate().doUpdate(selectCriteria, columnValues, connection);
        }
        if (LOG.isDebugEnabled())
            LOG.debug("Committing updates");
        connection.commit();
    } catch (Exception ex) {
        LOG.error("Failed to update, rolling back", ex);
        connection.rollback();
        throw ex;
    } finally {
        stopWatch.stop();
        if (LOG.isDebugEnabled())
            LOG.debug("Closing connection");
        connection.close();
    }
    LOG.info(String.format("Updated %s records in %s ms", num, stopWatch.getTime()));
    return num;
}

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

/**
 * Method for inserting a Replica into the replica table.
 * The replica_guid is automatically given by the database, and the
 * values in the fields replica_id, replica_name and replica_type is
 * created from the replica argument.//from   ww w .ja  v  a2 s.  c  om
 *
 * @param rep The Replica to insert into the replica table.
 * @param con An open connection to the archive database
 * @throws IOFailure If a SQLException is caught.
 */
protected static void insertReplicaIntoDB(Replica rep, Connection con) throws IOFailure {
    PreparedStatement statement = null;
    try {
        // Make the SQL statement for putting the replica into the database
        // and insert the variables for the entry to the replica table.
        statement = con.prepareStatement("INSERT INTO replica " + "(replica_id, replica_name, replica_type) "
                + "(SELECT ?,?,? from replica WHERE replica_id=? HAVING count(*) = 0)");
        statement.setString(1, rep.getId());
        statement.setString(2, rep.getName());
        statement.setInt(3, rep.getType().ordinal());
        statement.setString(4, rep.getId());
        log.debug("Executing insert, conditional on " + rep.getId() + " not already existing in the database.");
        int result = statement.executeUpdate();
        log.debug("Insert statement for " + rep.getId() + " returned " + result);
        con.commit();
    } catch (SQLException e) {
        throw new IOFailure("Cannot add replica '" + rep + "'to the database.", e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
    }
}

From source file:com.novartis.opensource.yada.QueryManager.java

/**
 * Executes a query-level commit on the connection stored in the YADAQuery
 * referenced by the parameter. If the connection object returned by the query
 * is not a JDBC connection, but, for instance, a SOAPConnection, the error
 * will be caught and handled gracefully.
 * //w w  w.ja  v  a  2s .c  o  m
 * @param yq
 *          the query containing the statements to commit
 * @throws YADAConnectionException
 *           when the commit fails
 */
public void commit(YADAQuery yq) throws YADAConnectionException {
    try {
        if (this.requiredCommits.contains(yq.getSource())) {
            Connection connection = (Connection) yq.getConnection();
            if (connection.getHoldability() == ResultSet.HOLD_CURSORS_OVER_COMMIT) {
                connection.commit();
                int count = yq.getResult().getTotalResultCount();
                String rows = count == 1 ? "row" : "rows";
                String msg = "\n------------------------------------------------------------\n";
                msg += "   Commit successful on connection to [" + yq.getSource() + "] (" + count + " " + rows
                        + ")\n";
                msg += "------------------------------------------------------------\n";
                l.debug(msg);
            } else {
                deferCommit(yq.getSource());
            }
        }
    } catch (SQLException e) {
        String msg = "Unable to commit transaction on [" + yq.getSource() + "].";
        throw new YADAConnectionException(msg, e);
    } catch (ClassCastException e) {
        l.info("Connection to [" + yq.getSource()
                + "] is not a JDBC connection (it's probably SOAP.) No commit was attempted.");
    }
}

From source file:com.netflix.metacat.usermetadata.mysql.MysqlUserMetadataService.java

@Override
public void deleteDefinitionMetadatas(@Nonnull final List<QualifiedName> names) {
    try {/*from  w  w w .  j  a  v  a  2s .  c  o m*/
        final Connection conn = poolingDataSource.getConnection();
        try {
            final List<List<QualifiedName>> subLists = Lists.partition(names,
                    config.getUserMetadataMaxInClauseItems());
            for (List<QualifiedName> subNames : subLists) {
                _deleteDefinitionMetadatas(conn, subNames);
            }
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.close();
        }
    } catch (SQLException e) {
        log.error("Sql exception", e);
        throw new UserMetadataServiceException(
                String.format("Failed deleting the definition metadata for %s", names), e);
    }
}