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:dk.netarkivet.archive.arcrepositoryadmin.ReplicaCacheHelpers.java

/**
 * Method for updating the checksum status of a replicafileinfo instance.
 * Updates the following fields for the entry in the replicafileinfo:
 * <br/> checksum_status = CORRUPT.
 * <br/> checksum_checkdatetime = current time.
 *
 * The replicafileinfo is in the filelist.
 *
 * @param replicafileinfoId The id of the replicafileinfo.
 * @param con An open connection to the archive database
 *//*w ww. j  a va  2 s.c  o m*/
protected static void updateReplicaFileInfoChecksumCorrupt(long replicafileinfoId, Connection con) {
    PreparedStatement statement = null;
    try {
        // The SQL statement
        final String sql = "UPDATE replicafileinfo SET checksum_status = ?, "
                + "checksum_checkdatetime = ?, upload_status = ? " + "WHERE replicafileinfo_guid = ?";

        Date now = new Date(Calendar.getInstance().getTimeInMillis());

        // complete the SQL statement.
        statement = DBUtils.prepareStatement(con, sql, ChecksumStatus.CORRUPT.ordinal(), now,
                ReplicaStoreState.UPLOAD_FAILED.ordinal(), replicafileinfoId);

        // execute the SQL statement
        statement.executeUpdate();
        con.commit();
    } catch (Exception e) {
        String msg = "Problems updating the replicafileinfo.";
        log.warn(msg);
        throw new IOFailure(msg, e);
    } finally {
        DBUtils.closeStatementIfOpen(statement);
    }
}

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

public void setUpData(String tableName, String schema, boolean nullEntry) {
    SqoopOptions options = new SqoopOptions(CONNECT_STRING, tableName);
    options.setUsername(DATABASE_USER);/*from w w w.ja v a2  s.  c  o m*/
    options.setPassword(PASSWORD);

    ConnManager manager = null;
    Connection connection = null;
    Statement st = null;

    try {
        manager = new PostgresqlManager(options);
        connection = manager.getConnection();
        connection.setAutoCommit(false);
        st = connection.createStatement();

        // Create schema if not exists in dummy way (always create and ignore
        // errors.
        try {
            st.executeUpdate("CREATE SCHEMA " + manager.escapeTableName(schema));
            connection.commit();
        } catch (SQLException e) {
            LOG.info(
                    "Couldn't create schema " + schema + " (is o.k. as long as" + "the schema already exists.");
            connection.rollback();
        }

        String fullTableName = manager.escapeTableName(schema) + "." + manager.escapeTableName(tableName);
        LOG.info("Creating table: " + fullTableName);

        try {
            // Try to remove the table first. DROP TABLE IF EXISTS didn't
            // get added until pg 8.3, so we just use "DROP TABLE" and ignore
            // any exception here if one occurs.
            st.executeUpdate("DROP TABLE " + fullTableName);
        } catch (SQLException e) {
            LOG.info("Couldn't drop table " + schema + "." + tableName + " (ok)");
            // Now we need to reset the transaction.
            connection.rollback();
        }

        st.executeUpdate("CREATE TABLE " + fullTableName + " (" + manager.escapeColName("id")
                + " INT NOT NULL PRIMARY KEY, " + manager.escapeColName("name") + " VARCHAR(24) NOT NULL, "
                + manager.escapeColName("start_date") + " DATE, " + manager.escapeColName("Salary") + " FLOAT, "
                + manager.escapeColName("Fired") + " BOOL, " + manager.escapeColName("dept") + " VARCHAR(32))");

        st.executeUpdate("INSERT INTO " + fullTableName
                + " VALUES(1,'Aaron','2009-05-14',1000000.00,TRUE,'engineering')");
        st.executeUpdate("INSERT INTO " + fullTableName + " VALUES(2,'Bob','2009-04-20',400.00,TRUE,'sales')");
        st.executeUpdate(
                "INSERT INTO " + fullTableName + " VALUES(3,'Fred','2009-01-23',15.00,FALSE,'marketing')");
        if (nullEntry) {
            st.executeUpdate("INSERT INTO " + fullTableName + " VALUES(4,'Mike',NULL,NULL,NULL,NULL)");

        }
        connection.commit();
    } catch (SQLException sqlE) {
        LOG.error("Encountered SQL Exception: " + sqlE);
        sqlE.printStackTrace();
        fail("SQLException when running test setUp(): " + sqlE);
    } finally {
        try {
            if (null != st) {
                st.close();
            }

            if (null != manager) {
                manager.close();
            }
        } catch (SQLException sqlE) {
            LOG.warn("Got SQLException when closing connection: " + sqlE);
        }
    }

    LOG.debug("setUp complete.");
}

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

private static void InOutMoreRecordsStoreIntoDB(Map<String, AlarmMore> iniOutMoreRecords,
        ApplicationContext context) {//from   w ww  .jav a2s . co  m
    Connection conn = null;
    String sql = "";
    try {
        SqlMapClient sc = (SqlMapClient) context.getBean("sqlMapClientLybc");
        conn = sc.getDataSource().getConnection();
        conn.setAutoCommit(false);
        Statement st = conn.createStatement();
        Iterator<String> it = iniOutMoreRecords.keySet().iterator();
        while (it.hasNext()) {
            String key = it.next();
            AlarmMore pos = iniOutMoreRecords.get(key);
            sql = "insert into TAB_ALARM_MORE " + " (LICENCE,denoter,fisrt_Time,BEGIN_TIME,END_TIME,road) "
                    + " values (" + "'" + pos.getLicense() + "'," + "'" + pos.getDenoter() + "'," + "'"
                    + pos.getFirstTime() + "'," + "'" + pos.getBeginTime() + "'," + "'" + pos.getEndTime()
                    + "'," + "'" + pos.getRoad() + "')";
            log.info(sql);
            st.addBatch(sql);
        }
        st.executeBatch();
        conn.commit();
        log.info("[insertIntoDB TAB_ALARM_MORE success!!!]");
    } catch (Exception e) {
        e.printStackTrace();
        log.error(sql);
    } finally {
        iniOutMoreRecords.clear();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

From source file:com.agiletec.plugins.jpcontentfeedback.aps.system.services.contentfeedback.comment.CommentDAO.java

@Override
public void addComment(IComment comment) {
    Connection conn = null;
    PreparedStatement stat = null;
    try {//w w w. j av a 2 s  . c  o m
        conn = this.getConnection();
        conn.setAutoCommit(false);
        stat = conn.prepareStatement(ADD_COMMENT);
        stat.setInt(1, comment.getId());
        stat.setString(2, comment.getContentId());
        stat.setTimestamp(3, new Timestamp(new Date().getTime()));
        stat.setString(4, comment.getComment());
        stat.setInt(5, comment.getStatus());
        stat.setString(6, comment.getUsername());
        stat.executeUpdate();
        conn.commit();
    } catch (Throwable t) {
        this.executeRollback(conn);
        _logger.error("Error adding a comment", t);
        throw new RuntimeException("Error adding a comment", t);
    } finally {
        closeDaoResources(null, stat, conn);
    }

}

From source file:com.concursive.connect.web.modules.profile.utils.ProjectCopier.java

public static Requirement cloneRequirement(CloneBean bean, Connection db, int groupId, int userId,
        int requirementId) throws SQLException {
    Requirement requirement = null;/*from  w  w  w .  j a  v a 2s . co m*/
    try {
        db.setAutoCommit(false);

        // Load permissions and resources for this member
        LOG.debug("ProjectCopier-> RequirementId: " + requirementId);

        Project project = new Project(db, bean.getProjectId());
        TeamMember member = new TeamMember(db, project.getId(), userId);
        User user = UserUtils.loadUser(userId);

        LookupList roleList = new LookupList(db, "lookup_project_role");

        // Load old requirement, change some values, save as new requirement
        requirement = new Requirement(db, requirementId);

        // Outlines, Activities, Activity Folders (no notes yet)
        if (hasPermission(db, project, user, member, "project-plan-view", roleList)) {
            requirement.setEnteredBy(userId);
            requirement.setModifiedBy(userId);
            requirement.setEntered((Timestamp) null);
            requirement.setModified((Timestamp) null);
            requirement.setShortDescription(requirement.getShortDescription() + " (copy)");
            boolean resetStatus = false;
            long offset = 0;
            if (bean.getResetActivityStatus()) {
                resetStatus = true;
            }
            requirement.clone(db, project.getId(), offset, resetStatus);
        }
        db.commit();
    } catch (Exception e) {
        LOG.error("ProjectCopier-> cloneRequirement", e);
        db.rollback();
    } finally {
        db.setAutoCommit(true);
    }
    return requirement;
}

From source file:com.octo.captcha.engine.bufferedengine.buffer.DatabaseCaptchaBuffer.java

/**
 * Get the size of the buffer for all locales
 *
 * @return The size of the buffer/*from w w w. j a va 2s.c o m*/
 */
public int size() {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    int size = 0;

    try {
        con = datasource.getConnection();
        ps = con.prepareStatement("select count(*) from " + table);
        rs = ps.executeQuery();
        if (rs.next()) {
            size = rs.getInt(1);
        }
        rs.close();
        con.commit();
    } catch (SQLException e) {
        log.error(DB_ERROR, e);
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
            }
        }
    } finally {
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
            }
        }
    }

    return size;

}

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

private synchronized void initializeGenerator(DataAccessSession session,
        TableMetadata<?> generatorTableMetadata, String valueGenerator) {
    final Connection connection = session.getConnection();
    try {/*from w ww . j av  a2s .  c  om*/
        connection.setAutoCommit(false);
        final Statement statement = connection.createStatement();
        final String update = "INSERT IGNORE INTO "
                + DatabaseUtils.qualifyTable(generatorTableMetadata, session.getDatabaseDialect())
                + " (`name`, `value`) VALUES(\"" + DatabaseUtils.escapeString(valueGenerator,
                        session.getDatabaseDialect().getStringEscapeCharacter())
                + "\", 0);";
        log.trace("Initializing generator: " + update);
        statement.executeUpdate(update);
        statement.close();
        connection.commit();
        connection.close();
    } catch (SQLException e) {
        throw new UnsuccessfulOperationError("Failed to initialize generator: " + valueGenerator, e);
    }
}

From source file:jm.web.Archivo.java

/**
 * Guarda el registro del nombre y el archivo binario en una tabla de la base de datos.
 * @param nombre. Nombre del archivo subido.
 * @param archivo. Ruta del archivo subido.
 * @return Retorna true o false si se guarda o no el archivo en la DB.
 *///from   w  ww.j  a  v a2  s .c  om
public boolean setArchivoDB(String tabla, String campoNombre, String campoBytea, String clave, String nombre,
        File archivo) {
    boolean r = false;
    try {
        Connection conexion = this.getConexion();
        PreparedStatement ps = conexion.prepareStatement("UPDATE " + tabla + " SET " + campoNombre + "='"
                + nombre + "', " + campoBytea + "=? WHERE " + tabla.replace("tbl_", "id_") + "=" + clave + ";");
        conexion.setAutoCommit(false);
        FileInputStream archivoIS = new FileInputStream(this._archivo);
        try {
            /*ps.setBinaryStream(1, archivoIS, (int)archivo.length());*/
            byte buffer[] = new byte[(int) archivo.length()];
            archivoIS.read(buffer);
            ps.setBytes(1, buffer);

            ps.executeUpdate();
            conexion.commit();
            r = true;
        } catch (Exception e) {
            this._error = e.getMessage();
            e.printStackTrace();
        } finally {
            archivoIS.close();
            ps.close();
        }

    } catch (Exception e) {
        this._error = e.getMessage();
        e.printStackTrace();
    }
    return r;
}

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

/**
 * Creates the prioritization related to the given hierarchy.
 * /*from w  ww . j a v  a  2s  .  co  m*/
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param hierarchyId the given hierarchy id.
 * @since July, 2011.
 * @throws PrioritizationCreationDAOException if an error occurs during the
 *         creation.
 */
@Override
public void createHierarchyPrioritization(int hierarchyId) throws PrioritizationCreationDAOException {
    LOGGER.debug("createHierarchyPrioritization(" + hierarchyId + ").");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        connection.setAutoCommit(false);
        getQueryRunner().update(connection,
                "INSERT INTO prioritization(prioritization_id) VALUES(nextval('prioritization_seq')) ");
        Integer createdPrioritization = (Integer) getQueryRunner().query(connection,
                "SELECT MAX(prioritization_id)::int AS prioritizationId FROM prioritization ",
                new ScalarHandler("prioritizationId"));
        getQueryRunner().update(connection,
                "INSERT INTO hierarchy_prioritization(hierarchy_id, prioritization_id) VALUES(?,?) ",
                new Object[] { hierarchyId, createdPrioritization });
        connection.commit();
    } catch (SQLException e) {
        try {
            connection.rollback();
        } catch (SQLException e1) {
            throw new PrioritizationCreationDAOException(e);
        }
        throw new PrioritizationCreationDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

From source file:com.aurel.track.dbase.UpdateDbSchema.java

/**
 * Run an SQL script//w  w  w. j  a  v a2 s  .c o  m
 * @param script
 */
@SuppressWarnings("resource")
private static void runSQLScript(String script, int maxValue, String progressText) {
    String folderName = getDbScriptFolder();
    int line = 0;
    int noOfLines = 0;
    int progress = 0;
    Connection cono = null;
    try {
        long start = new Date().getTime();
        cono = InitDatabase.getConnection();
        cono.setAutoCommit(false);
        Statement ostmt = cono.createStatement();
        script = "/dbase/" + folderName + "/" + script;
        URL populateURL = ApplicationBean.getInstance().getServletContext().getResource(script);
        InputStream in = populateURL.openStream();
        java.util.Scanner s = new java.util.Scanner(in, "UTF-8").useDelimiter(";");
        while (s.hasNext()) {
            ++noOfLines;
            s.nextLine();
        }
        int mod = noOfLines / maxValue;
        in.close();
        in = populateURL.openStream();
        s = new java.util.Scanner(in, "UTF-8").useDelimiter(";");
        String st = null;
        StringBuilder stb = new StringBuilder();

        int modc = 0;
        progress = Math.round(new Float(mod) / new Float(noOfLines) * maxValue);

        LOGGER.info("Running SQL script " + script);
        while (s.hasNext()) {
            stb.append(s.nextLine().trim());
            st = stb.toString();
            ++line;
            ++modc;
            if (!st.isEmpty() && !st.startsWith("--") && !st.startsWith("/*") && !st.startsWith("#")) {
                if (st.trim().equalsIgnoreCase("go")) {
                    try {
                        cono.commit();
                    } catch (Exception ex) {
                        LOGGER.error(ExceptionUtils.getStackTrace(ex));
                    }
                    stb = new StringBuilder();
                } else {
                    if (st.endsWith(";")) {
                        stb = new StringBuilder(); // clear buffer
                        st = st.substring(0, st.length() - 1); // remove the semicolon
                        try {
                            if ("commit".equals(st.trim().toLowerCase())
                                    || "go".equals(st.trim().toLowerCase())) {
                                cono.commit();
                            } else {
                                ostmt.executeUpdate(st);
                                if (mod > 4 && modc >= mod) {
                                    modc = 0;
                                    ApplicationStarter.getInstance().actualizePercentComplete(progress,
                                            progressText);
                                }
                            }
                        } catch (Exception exc) {
                            if (!("Derby".equals(folderName) && exc.getMessage().contains("DROP TABLE")
                                    && exc.getMessage().contains("not exist"))) {
                                LOGGER.error("Problem executing DDL statements: " + exc.getMessage());
                                LOGGER.error("Line " + line + ": " + st);
                            }
                        }
                    } else {
                        stb.append(" ");
                    }
                }
            } else {
                stb = new StringBuilder();
            }
        }
        in.close();
        cono.commit();
        cono.setAutoCommit(true);

        long now = new Date().getTime();
        LOGGER.info("Database schema creation took " + (now - start) / 1000 + " seconds.");

    } catch (Exception e) {
        LOGGER.error("Problem upgrading database schema in line " + line + " of file " + script, e);
    } finally {
        try {
            if (cono != null) {
                cono.close();
            }
        } catch (Exception e) {
            LOGGER.info("Closing the connection failed with " + e.getMessage());
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
    }
}