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:gridool.replication.ReplicationManager.java

public boolean configureReplicaDatabase(@Nonnull Connection conn, @Nonnull GridNode masterNode,
        boolean addReplicas) throws SQLException {
    if (!addReplicas) {
        throw new UnsupportedOperationException();
    }//from ww w.  jav a 2 s . c om
    final String masterNodeId = masterNode.getKey();
    synchronized (lock) {
        if (replicaDbMappingCache.containsKey(masterNodeId)) {
            return true;
        }
        if (!replicaNameStack.isEmpty()) {
            String replicaDbName = replicaNameStack.pop();
            if (replicaDbName != null) {
                Object[] params = new Object[2];
                params[0] = masterNodeId;
                params[1] = replicaDbName;
                final int rows = JDBCUtils.update(conn,
                        "UPDATE \"" + replicaTableName + "\" SET nodeinfo = ? WHERE dbname = ?", params);
                if (rows == 1) {
                    conn.commit();
                    if (replicaDbMappingCache.put(masterNodeId, replicaDbName) != null) {
                        throw new IllegalStateException();
                    }
                    return true;
                } else {
                    return false;
                }
            }
        }
    }
    LOG.error("There is no avialable replica database");
    return false;
}

From source file:com.ibm.bluemix.samples.PostgreSQLReportedErrors.java

/**
 * Insert text into PostgreSQL//ww  w .  jav  a  2 s .  co m
 * 
 * @param files 
 *           List of Strings of text to insert
 * 
 * @return number of rows affected
 * 
 * @throws Exception TODO describe exception
 */
public int addFile(String action_number, String make, String model, String year, String compname,
        String mfr_name, String odate, String cdate, String campno, String subject, String summary)
        throws Exception {

    String sql = "INSERT INTO reportedErrors (action_number, make, model, year, compname, mfr_name, odate, cdate, campno, subject, summary) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,)";
    Connection connection = null;
    PreparedStatement statement = null;

    try {
        connection = getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);

        statement.setString(1, action_number);
        statement.setString(2, make);
        statement.setString(3, model);
        statement.setString(4, year);
        statement.setString(5, compname);
        statement.setString(6, mfr_name);
        statement.setString(7, odate);
        statement.setString(8, cdate);
        statement.setString(9, campno);
        statement.setString(10, subject);
        statement.setString(11, summary);

        statement.addBatch();

        int[] rows = statement.executeBatch();
        connection.commit();

        return rows.length;

    } catch (SQLException e) {
        SQLException next = e.getNextException();

        if (next != null) {
            throw next;
        }

        throw e;
    } finally {
        if (statement != null) {
            statement.close();
        }

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

From source file:org.opendatakit.persistence.engine.pgres.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/*from  ww w  . j a  v a2s .co m*/

    stringBuilder.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = stringBuilder.toString();
    stringBuilder.setLength(0);

    stringBuilder.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        stringBuilder.append("INSERT INTO ");
        stringBuilder.append(tableName);
        stringBuilder.append(" (");
        first = true;
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(dataField.getName());
            stringBuilder.append(K_BQ);
        }
        first = true;
        stringBuilder.append(") VALUES ( ");
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            if (dataField.equals(entity.creationDate) || dataField.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (dataField.equals(entity.creatorUriUser) || dataField.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (dataField.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (dataField.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (dataField.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (dataField.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + dataField.getName());
            }
        }
        stringBuilder.append(")");
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    } else {
        // update existing record (prospective lock)
        stringBuilder.append("UPDATE ");
        stringBuilder.append(tableName);
        stringBuilder.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(f.getName());
            stringBuilder.append(K_BQ);
            stringBuilder.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        stringBuilder.append(" WHERE ");
        stringBuilder.append(K_BQ);
        stringBuilder.append(entity.primaryKey.getName());
        stringBuilder.append(K_BQ);
        stringBuilder.append(" = ");
        stringBuilder.append(uriLockInline);
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    stringBuilder.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ")
            .append(uriLockInline).append(" AND ");
    stringBuilder.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (PSQLException e) {
                    e.printStackTrace();
                    conn.rollback();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

From source file:dk.netarkivet.harvester.datamodel.RunningJobsInfoDBDAO.java

/**
 * Removes all records pertaining to the given job ID from the persistent
 * storage.//from   w ww . j  ava  2s  .com
 * @param jobId the job id.
 * @return the number of deleted records.
 */
@Override
public int removeInfoForJob(long jobId) {
    ArgumentNotValid.checkNotNull(jobId, "jobId");

    Connection c = HarvestDBConnection.get();
    PreparedStatement stm = null;

    int deleteCount = 0;
    try {
        // Delete from monitor table
        c.setAutoCommit(false);
        stm = c.prepareStatement("DELETE FROM runningJobsMonitor WHERE jobId=?");
        stm.setLong(1, jobId);
        deleteCount = stm.executeUpdate();
        c.commit();
        stm.close();
        // Delete from history table
        c.setAutoCommit(false);
        stm = c.prepareStatement("DELETE FROM runningJobsHistory WHERE jobId=?");
        stm.setLong(1, jobId);
        deleteCount += stm.executeUpdate();
        c.commit();
    } catch (SQLException e) {
        String message = "SQL error deleting from history records for job ID " + jobId + "\n"
                + ExceptionUtils.getSQLExceptionCause(e);
        log.warn(message, e);
        throw new IOFailure(message, e);
    } finally {
        DBUtils.closeStatementIfOpen(stm);
        DBUtils.rollbackIfNeeded(c, "removeInfoForJob", jobId);
        HarvestDBConnection.release(c);
    }

    return deleteCount;

}

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

public ProgramProfilingSymbol updateProgramProfilingSymbol(int id, int configID, int programSymbolID)
        throws AdaptationException {
    ProgramProfilingSymbol pps = null;//from  w  w  w . jav a2 s .  c om
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {
        String query = "UPDATE ProgramProfilingSymbols SET " + "projectDeploymentConfigurationID = " + configID
                + ", " + "programSymbolID                  = " + programSymbolID + ", " + "WHERE id = " + id;

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

        query = "SELECT * from ProgramProfilingSymbols WHERE " + "id = " + id;
        resultSet = statement.executeQuery(query);

        if (!resultSet.next()) {
            connection.rollback();
            String msg = "Attempt to update program profiling " + "symbol failed.";
            log.error(msg);
            throw new AdaptationException(msg);
        }

        pps = getProfilingSymbol(resultSet);
        connection.commit();
    } catch (SQLException ex) {
        try {
            connection.rollback();
        } catch (Exception e) {
        }

        String msg = "SQLException in updateProgramProfilingSymbol";
        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 pps;
}

From source file:com.trackplus.ddl.DataWriter.java

private static int executeUpdateScript(String fileName, Connection con) throws DDLException {
    String endOfStatement = ";" + DataReader.LINE_SEPARATOR;

    BufferedReader bufferedReader = createBufferedReader(fileName);
    String line;/* w  ww. ja va2 s . c o  m*/
    StringBuilder sql = new StringBuilder();

    Statement stmt = MetaDataBL.createStatement(con);

    int idx = 0;
    Map<String, Integer> columnSizeMap = new HashMap<String, Integer>();
    try {
        while ((line = bufferedReader.readLine()) != null) {
            sql.append(line);
            if (line.endsWith(endOfStatement)) {
                String s = sql.substring(0, sql.length() - endOfStatement.length() + 1);
                Integer maxSize = getMaxColumnSize(con, columnSizeMap, s);
                int idxStart = s.indexOf("'");
                int idxEnd = s.lastIndexOf("'");
                String value = s.substring(idxStart + 1, idxEnd);
                if (value.length() > maxSize) {
                    LOGGER.warn("Value bigger found for:" + s.substring(0, idxStart) + "..."
                            + s.substring(idxEnd + 1));
                    value = value.substring(0, maxSize - 1);
                    s = s.substring(0, idxStart + 1) + value + s.substring(idxEnd, s.length());
                }
                idx++;
                executeUpdate(con, stmt, s);
                sql.setLength(0);
            } else {
                sql.append("\n");
            }
        }
    } catch (IOException e) {
        throw new DDLException(e.getMessage(), e);
    }
    try {
        bufferedReader.close();
    } catch (IOException e) {
        throw new DDLException(e.getMessage(), e);
    }
    try {
        stmt.close();
        con.commit();
        con.setAutoCommit(true);
    } catch (SQLException e) {
        throw new DDLException(e.getMessage(), e);
    }
    return idx;
}

From source file:com.aurel.track.DBScriptTest.java

/**
 * Run an SQL script/*from  w w  w .ja v a2  s.c om*/
 * @param script
 */
private void runSQLScript(String scriptToRunWithPath, Connection cono) {
    int line = 0;
    try {
        cono.setAutoCommit(false);
        Statement ostmt = cono.createStatement();
        InputStream in = new FileInputStream(scriptToRunWithPath);//populateURL.openStream();
        java.util.Scanner s = new java.util.Scanner(in, "UTF-8").useDelimiter(";");
        String st = null;
        StringBuffer stb = new StringBuffer();
        System.out.println("Running SQL script " + scriptToRunWithPath);
        while (s.hasNext()) {
            stb.append(s.nextLine().trim());
            st = stb.toString();
            ++line;
            if (!st.isEmpty() && !st.startsWith("--") && !st.startsWith("/*") && !st.startsWith("#")) {
                if (st.trim().equalsIgnoreCase("go")) {
                    try {
                        cono.commit();
                    } catch (Exception ex) {
                        System.err.println(org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(ex));
                    }
                    stb = new StringBuffer();
                } else {
                    if (st.endsWith(";")) {
                        stb = new StringBuffer(); // 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);
                                // LOGGER.info(st);
                            }
                        } catch (Exception exc) {
                            if (!(scriptToRunWithPath.contains("Derby")
                                    && exc.getMessage().contains("DROP TABLE")
                                    && exc.getMessage().contains("not exist"))) {
                                System.err.println("Problem executing DDL statements: " + exc.getMessage());
                                System.err.println("Line " + line + ": " + st);
                            }
                        }
                    } else {
                        stb.append(" ");
                    }
                }
            } else {
                stb = new StringBuffer();
            }
        }
        in.close();
        cono.commit();
        cono.setAutoCommit(true);

    } catch (Exception e) {
        System.err.println("Problem upgrading database schema in line " + line + " of file "
                + scriptToRunWithPath + "  " + e);
    }
}

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

public MoteDeploymentConfiguration updateMoteDeploymentConfiguration(int mdConfigID, int programID,
        int radioPowerLevel) throws AdaptationException {
    MoteDeploymentConfiguration mdc = null;
    Connection connection = null;
    Statement statement = null;// w w w  .  j a v a2s.c om
    ResultSet resultSet = null;

    try {
        String query = "UPDATE MoteDeploymentConfigurations SET " + "programID       = " + programID + ", "
                + "radioPowerLevel = " + radioPowerLevel + "  " + "WHERE id = " + mdConfigID;

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

        query = "SELECT * from MoteDeploymentConfigurations WHERE " + "id = " + mdConfigID;
        resultSet = statement.executeQuery(query);

        if (!resultSet.next()) {
            connection.rollback();
            String msg = "Unable to select updated config.";
            log.error(msg);
            ;
            throw new AdaptationException(msg);
        }

        mdc = getMoteDeploymentConfiguration(resultSet);
        connection.commit();
    } catch (SQLException ex) {
        try {
            connection.rollback();
        } catch (Exception e) {
        }

        String msg = "SQLException in updateMoteDeploymentConfiguration";
        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 mdc;
}

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

@Override
public String[] getColumnNames(String tableName) {
    Connection conn = null;
    PreparedStatement pStmt = null;
    ResultSet rset = null;/*from w  w w  .  ja  v  a2s . c  o m*/
    List<String> columns = new ArrayList<String>();

    try {
        conn = getConnection();

        pStmt = conn.prepareStatement(QUERY_COLUMNS_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);
        }
    }

    return columns.toArray(new String[columns.size()]);
}

From source file:edu.ncsa.sstde.indexing.postgis.PostgisIndexer.java

private void clearTable() throws SQLException {
    Connection connection = getConnection();
    java.sql.Statement statement = connection.createStatement();
    statement.execute("delete from " + this.getSettings().getTableName());
    connection.commit();
    statement.close();//  www  .ja va  2 s.  co  m
}