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.archivas.clienttools.arcutils.utils.database.ManagedJobSchema.java

public void dropSchema() throws DatabaseException {
    synchronized (DatabaseResourceManager.DB_LOCK) {
        Connection conn = null;
        try {//  w w  w . j  av  a  2s  .c om
            conn = connPool.getConnection();
            dropTempTable(conn);
            dropTable(conn, qualifiedFilesTableName);
            Statement stmt = conn.createStatement();
            DBUtils.executeUpdate(stmt, "DROP SCHEMA " + schemaName + " RESTRICT");
            conn.commit();
        } catch (Exception e) {
            throw new DatabaseException(
                    DBUtils.getErrorMessage("An error occurred dropping schema " + schemaName, e), e);
        } finally {
            connPool.returnConnection(conn);
        }
    }
}

From source file:mx.com.pixup.portal.dao.EstadoMunicipioParserDaoJdbc.java

public void parserXML() {

    try {/*from  w w w . ja v  a  2  s  .c o  m*/

        //variables BD
        String sql = "INSERT INTO estado VALUES (?,?)";
        String sqlM = "INSERT INTO municipio VALUES (?,?,?)";
        PreparedStatement preparedStatement;
        Connection connection = dataSource.getConnection();
        connection.setAutoCommit(false);

        //se obtiene elemento raiz
        Element estado = this.xmlDocumento.getRootElement();
        //elementos 2do nivel
        Element nombre = estado.getChild("nombre");
        Element municipios = estado.getChild("municipios");
        Attribute id = estado.getAttribute("id");
        //construye parametros de la query
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id.getIntValue());
        preparedStatement.setString(2, nombre.getText());
        preparedStatement.execute();

        List<Element> listaMunicipios = municipios.getChildren("municipio");
        Iterator<Element> i = listaMunicipios.iterator();

        while (i.hasNext()) {
            Element municipio = i.next();
            //Elementos de tercer nivel
            Attribute idMunicipio = municipio.getAttribute("id");
            String nombreMunicipio = municipio.getText();
            //construye parametros de la query
            preparedStatement = connection.prepareStatement(sqlM);
            preparedStatement.setInt(1, idMunicipio.getIntValue());
            preparedStatement.setString(2, nombreMunicipio);
            preparedStatement.setInt(3, id.getIntValue());

            preparedStatement.execute();
        }

        connection.commit();
    } catch (Exception e) {
        //*** se quit el return porque el mtodo es void
        System.out.println(e.getMessage());
    }

}

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

/**
 * Executes a PostgresSQL copy command./*from   www .j a  v  a  2 s . c o m*/
 * 
 * @param query the copy command to execute
 * @param reader the containing the data to be copied
 */
private void executeCopyIn(String query, Reader reader) {
    Connection con = null;
    CopyManager manager = null;
    try {
        con = this.getConnection();
        con.setAutoCommit(false);
        if (con instanceof com.jolbox.bonecp.ConnectionHandle) {
            ConnectionHandle handle = (ConnectionHandle) con;
            manager = new CopyManager((BaseConnection) handle.getInternalConnection());
        } else {
            manager = new CopyManager((BaseConnection) con);
        }

        manager.copyIn(query, reader);
        con.commit();
    } catch (Exception e) {
        if (log.isErrorEnabled()) {
            log.error(query, e);
        }
        if (con != null) {
            try {
                con.rollback();
            } catch (SQLException e1) {
                if (log.isErrorEnabled()) {
                    log.error("Error during rollback.", e1);
                }
            }
        }
    } finally {
        try {
            if (con != null && !con.isClosed()) {
                con.setAutoCommit(true);
                con.close();
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("Error during close.", e);
            }
        }
    }
}

From source file:hoot.services.controllers.osm.ChangesetResource.java

/**
 * Service method endpoint for uploading OSM changeset diff data
 * //from  w w  w .  j a va  2s .c  o  m
 * @param changeset OSM changeset diff data
 * @param changesetId ID of the changeset being uploaded; changeset with the ID must already exist
 * @return response acknowledging the result of the update operation with updated entity ID 
 * information
 * @throws Exception
 * @see http://wiki.openstreetmap.org/wiki/API_0.6 and 
 * http://wiki.openstreetmap.org/wiki/OsmChange
 * @todo why can't I pass in changesetDiff as an XML doc instead of a string?
 */
@POST
@Path("/{changesetId}/upload")
@Consumes(MediaType.TEXT_XML)
@Produces(MediaType.TEXT_XML)
public Response upload(final String changeset, @PathParam("changesetId") final long changesetId,
        @QueryParam("mapId") final String mapId) throws Exception {
    Connection conn = DbUtils.createConnection();
    Document changesetUploadResponse = null;
    try {
        log.debug("Intializing database connection...");

        log.debug("Intializing changeset upload transaction...");
        TransactionStatus transactionStatus = transactionManager
                .getTransaction(new DefaultTransactionDefinition(TransactionDefinition.PROPAGATION_REQUIRED));
        conn.setAutoCommit(false);

        try {
            if (mapId == null) {
                throw new Exception("Invalid map id.");
            }
            long mapid = Long.parseLong(mapId);
            changesetUploadResponse = (new ChangesetDbWriter(conn)).write(mapid, changesetId, changeset);
        } catch (Exception e) {
            log.error("Rolling back transaction for changeset upload...");
            transactionManager.rollback(transactionStatus);
            conn.rollback();
            handleError(e, changesetId, StringUtils.abbreviate(changeset, 100));
        }

        log.debug("Committing changeset upload transaction...");
        transactionManager.commit(transactionStatus);
        conn.commit();
    } finally {
        conn.setAutoCommit(true);
        DbUtils.closeConnection(conn);
    }

    log.debug("Returning changeset upload response: "
            + StringUtils.abbreviate(XmlDocumentBuilder.toString(changesetUploadResponse), 100) + " ...");
    return Response.ok(new DOMSource(changesetUploadResponse), MediaType.TEXT_XML)
            .header("Content-type", MediaType.TEXT_XML).build();
}

From source file:com.mmnaseri.dragonfly.dialect.impl.Mysql5Dialect.java

@Override
public synchronized <E> Map<String, Object> loadTableValues(final TableMetadata<?> generatorMetadata,
        TableMetadata<E> tableMetadata, final DataAccessSession session) {
    final HashMap<String, Object> result = new HashMap<String, Object>();
    with(tableMetadata.getColumns()).forThose(new Filter<ColumnMetadata>() {
        @Override//www.j ava2  s  .c om
        public boolean accepts(ColumnMetadata item) {
            return ValueGenerationType.TABLE.equals(item.getGenerationType());
        }
    }, new Processor<ColumnMetadata>() {
        @Override
        public void process(ColumnMetadata column) {
            final String valueGenerator;
            if (column.getValueGenerator() == null || column.getValueGenerator().isEmpty()) {
                valueGenerator = column.getTable().getEntityType().getCanonicalName() + "."
                        + column.getPropertyName();
            } else {
                valueGenerator = column.getValueGenerator();
            }
            initializeGenerator(session, generatorMetadata, valueGenerator);
            final Connection connection = session.getConnection();
            try {
                connection.setAutoCommit(false);
                final Statement statement = connection.createStatement();
                final String escapedGenerator = DatabaseUtils.escapeString(valueGenerator,
                        session.getDatabaseDialect().getStringEscapeCharacter());
                final String table = DatabaseUtils.qualifyTable(generatorMetadata,
                        session.getDatabaseDialect());
                final String query = "SELECT `value` FROM " + table + " WHERE `name` = \"" + escapedGenerator
                        + "\" FOR UPDATE;";
                log.trace("Querying for key: " + query);
                final ResultSet resultSet = statement.executeQuery(query);
                resultSet.next();
                final Map<String, Object> map = rowHandler.handleRow(resultSet);
                resultSet.close();
                final String update = "UPDATE " + table + " SET `value` = `value` + 1 WHERE `name` = \""
                        + escapedGenerator + "\"";
                log.trace("Updating key: " + update);
                statement.executeUpdate(update);
                result.put(column.getName(), map.get("value"));
                connection.commit();
                connection.close();
            } catch (Exception e) {
                throw new UnsuccessfulOperationError("Failed to load generated key for " + column.getName(), e);
            }
        }
    });
    return result;
}

From source file:org.meerkat.services.WebApp.java

/**
 * removeAllEvents/*ww  w. j  ava  2 s.c o m*/
 */
public final void removeAllEvents() {
    // Remove DB events of this application
    Connection conn = embDB.getConnForUpdates();
    PreparedStatement statement = null;

    String queryDelete = "DELETE FROM MEERKAT.EVENTS WHERE APPNAME LIKE '" + this.name + "'";

    try {
        statement = conn.prepareStatement(queryDelete);
        statement.execute();

        statement.close();
        conn.commit();
    } catch (SQLException e) {
        log.error("Failed to remove events of " + this.name + " from DB! - " + e.getMessage());
    }

    // Remove from events response
    PreparedStatement statementResponse = null;

    String queryDeleteResponse = "DELETE FROM MEERKAT.EVENTS_RESPONSE WHERE APPNAME LIKE '" + this.name + "'";

    try {
        statementResponse = conn.prepareStatement(queryDeleteResponse);
        statementResponse.execute();

        statementResponse.close();
        conn.commit();
    } catch (SQLException e) {
        log.error("Failed to remove response events of " + this.name + " from DB! - " + e.getMessage());
    }
}

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.idea.IdeaDAO.java

@Override
public void insertIdea(IIdea idea) {
    Connection conn = null;
    PreparedStatement stat = null;
    try {/*  w  w  w.  j  a v  a  2 s .  com*/
        conn = this.getConnection();
        conn.setAutoCommit(false);
        stat = conn.prepareStatement(INSERT_IDEA);
        int index = 1;
        stat.setString(index++, idea.getId());
        stat.setString(index++, idea.getTitle());
        stat.setString(index++, idea.getDescr());
        stat.setTimestamp(index++, new Timestamp(idea.getPubDate().getTime()));
        stat.setString(index++, idea.getUsername());
        stat.setInt(index++, idea.getStatus());
        stat.setInt(index++, idea.getVotePositive());
        stat.setInt(index++, idea.getVoteNegative());
        stat.setString(index++, idea.getInstanceCode());
        stat.executeUpdate();

        this.updateTags(idea, conn);

        conn.commit();
    } catch (Throwable t) {
        this.executeRollback(conn);
        _logger.error("Error adding Idea", t);
        throw new RuntimeException("Error adding Idea", t);
    } finally {
        closeDaoResources(null, stat, conn);
    }
}

From source file:com.strider.datadefender.DatabaseAnonymizer.java

/**
 * Anonymization function for a single table.
 * //  w w  w.  j  av  a  2 s.com
 * Sets up queries, loops over columns and anonymizes columns for the passed
 * Table.
 * 
 * @param table 
 */
private void anonymizeTable(final int batchSize, final IDBFactory dbFactory, final Table table)
        throws DatabaseAnonymizerException {

    log.info("Table [" + table.getName() + "]. Start ...");

    final List<Column> tableColumns = table.getColumns();
    // colNames is looked up with contains, and iterated over.  Using LinkedHashSet means
    // duplicate column names won't be added to the query, so a check in the column loop
    // below was created to ensure a reasonable warning message is logged if that happens.
    final Set<String> colNames = new LinkedHashSet<>(tableColumns.size());
    // keyNames is only iterated over, so no need for a hash set
    final List<String> keyNames = new LinkedList<>();

    fillColumnNames(table, colNames);
    fillPrimaryKeyNamesList(table, keyNames);

    // required in this scope for 'catch' block
    PreparedStatement selectStmt = null;
    PreparedStatement updateStmt = null;
    ResultSet rs = null;
    final Connection updateCon = dbFactory.getUpdateConnection();

    try {
        selectStmt = getSelectQueryStatement(dbFactory, table, keyNames, colNames);
        rs = selectStmt.executeQuery();

        final List<MatchMetaData> columnMetaData = dbFactory.fetchMetaData().getMetaDataForRs(rs);

        final String updateString = getUpdateQuery(table, colNames, keyNames);
        updateStmt = updateCon.prepareStatement(updateString);

        int batchCounter = 0;
        int rowCount = 0;

        while (rs.next()) {
            anonymizeRow(updateStmt, tableColumns, keyNames, updateCon, rs, columnMetaData,
                    dbFactory.getVendorName());
            batchCounter++;
            if (batchCounter == batchSize) {
                updateStmt.executeBatch();
                updateCon.commit();
                batchCounter = 0;
            }
            rowCount++;
        }
        log.debug("Rows processed: " + rowCount);

        updateStmt.executeBatch();
        log.debug("Batch executed");
        updateCon.commit();
        log.debug("Commit");
        selectStmt.close();
        updateStmt.close();
        rs.close();
        log.debug("Closing open resources");

    } catch (SQLException | NoSuchMethodException | SecurityException | IllegalAccessException
            | IllegalArgumentException | InvocationTargetException | DatabaseDiscoveryException ex) {
        log.error(ex.toString());
        if (ex.getCause() != null) {
            log.error(ex.getCause().toString());
        }
        try {
            if (selectStmt != null) {
                selectStmt.close();
            }
            if (updateStmt != null) {
                updateStmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException sqlex) {
            log.error(sqlex.toString());
        }
    } finally {
        try {
            if (selectStmt != null) {
                selectStmt.close();
            }
            if (updateStmt != null) {
                updateStmt.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException sqlex) {
            log.error(sqlex.toString());
        }
    }

    log.info("Table " + table.getName() + ". End ...");
    log.info("");
}

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

/**
 * Processes requests for both HTTP/*from   ww  w  . j av  a 2  s . c  o  m*/
 * <code>GET</code> and
 * <code>POST</code> methods.
 *
 * @param request servlet request
 * @param response servlet response
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws IOException {
    response.setContentType("text/html;charset=UTF-8");
    String doAction = request.getParameter("do");
    Connection conn = null;
    String result = KWLErrorMsgs.rsSuccessFalse;
    String res = "";
    try {
        conn = DbPool.getConnection();
        if (doAction.compareToIgnoreCase("export") == 0) {
            doExport(conn, request, response);
        } else if (doAction.compareToIgnoreCase("import") == 0) {
            res = doImport(request, conn);
        } else if (doAction.compareToIgnoreCase("getheaders") == 0) {
            res = getFileHeaders(request);
        }

        if (doAction.compareToIgnoreCase("export") == 0) {
            result = KWLErrorMsgs.rsSuccessTrue;
        } else if (doAction.compareToIgnoreCase("import") == 0
                || doAction.compareToIgnoreCase("getheaders") == 0) {
            result = res;
        }
        conn.commit();
    } catch (IOException ex) {
        DbPool.quietRollback(conn);
        Logger.getLogger(ExportImportContactsServlet.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
    } catch (ServiceException ex) {
        DbPool.quietRollback(conn);
        Logger.getLogger(ExportImportContactsServlet.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
    } catch (SessionExpiredException ex) {
        DbPool.quietRollback(conn);
        Logger.getLogger(ExportImportContactsServlet.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
    } catch (Exception ex) {
        DbPool.quietRollback(conn);
        Logger.getLogger(ExportImportContactsServlet.class.getName()).log(Level.SEVERE, ex.getMessage(), ex);
    } finally {
        DbPool.quietClose(conn);
        response.getWriter().write(result);
        response.getWriter().close();
    }
}

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

/**
 * Creates variants for a given test case. Is called after the variants
 * generation.//from  w  w w .j  a v  a  2  s .  c  om
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param testCaseId the test case id.
 * @param variants the list of generated variants.
 * @since July, 2011.
 * @throws VariantCreationDAOException if an error occurs during the
 *         creation.
 */
@Override
public void createVariantsFromTestCaseId(int testCaseId, List<VariantCreation> variants)
        throws VariantCreationDAOException {
    LOGGER.debug("createVariantsFromTestCaseId(" + testCaseId + "," + variants.size() + " variants)");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        connection.setAutoCommit(false);
        for (VariantCreation variant : variants) {
            getQueryRunner().update(connection,
                    "INSERT INTO variant(variant_id, variant_type, test_case_id, label, quantitative_criterion) VALUES(nextval('variant_id_seq'),?,?,NULL,NULL) ",
                    new Object[] { variant.getVariantType().name(), testCaseId });
            Integer createdVariantId = (Integer) getQueryRunner().query(connection,
                    "SELECT MAX(variant_id)::int AS result FROM variant ", new ScalarHandler("result"));
            for (Element element : variant.getElements()) {
                getQueryRunner().update(connection,
                        "INSERT INTO variant_element(element_id, variant_id) VALUES(?,?) ",
                        new Object[] { element.getElementId(), createdVariantId });
            }
            for (ProceduralArrow transition : variant.getTransitions()) {
                getQueryRunner().update(connection,
                        "INSERT INTO variant_transition(transition_id, variant_id) VALUES(?,?) ",
                        new Object[] { transition.getTransitionId(), createdVariantId });
            }
        }
        connection.commit();
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            throw new VariantCreationDAOException(e1);
        }
        throw new VariantCreationDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}