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.glaf.core.execution.UpdateExecutionHandler.java

@Override
public void execute(String content) {
    FileExecutionHelper helper = new FileExecutionHelper();
    String currentSystemName = Environment.getCurrentSystemName();
    Connection conn = null;
    Statement stmt = null;/*from  w  w w .java  2s.  c o m*/
    try {
        conn = DBConnectionFactory.getConnection(currentSystemName);
        if (conn != null) {
            helper.createTable(conn);
            String path = SystemProperties.getConfigRootPath() + "/conf/bootstrap/update";
            File dir = new File(path);
            File contents[] = dir.listFiles();
            if (contents != null) {
                for (int i = 0; i < contents.length; i++) {
                    if (contents[i].isFile() && StringUtils.endsWith(contents[i].getName(), ".sql")) {
                        try {
                            if (!helper.exists(conn, "update_sql", contents[i])) {
                                long lastModified = helper.lastModified(conn, "update_sql", contents[i]);
                                if (contents[i].lastModified() > lastModified) {
                                    conn.setAutoCommit(false);
                                    String ddlStatements = FileUtils.readFile(contents[i].getAbsolutePath());
                                    DBUtils.executeSchemaResourceIgnoreException(conn, ddlStatements);
                                    helper.save(conn, "update_sql", contents[i]);
                                    conn.commit();
                                }
                            }
                        } catch (Exception ex) {
                            ex.printStackTrace();
                            logger.error(ex);
                        }
                    }
                }
            }
        }
    } catch (Exception ex) {
        ex.printStackTrace();
        logger.error(ex);
    } finally {
        JdbcUtils.close(stmt);
        JdbcUtils.close(conn);
    }
}

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

/**
 * Insert text into PostgreSQL//  w  w w . ja  v  a 2 s  .  co m
 * 
 * param posts List of Strings of text to insert
 * 
 * @return number of rows affected
 * @throws Exception
 * @throws Exception
 */
public int addPosts(List<String> posts) throws Exception {
    String sql = "INSERT INTO posts (text) VALUES (?)";
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = getConnection();
        connection.setAutoCommit(false);
        statement = connection.prepareStatement(sql);

        for (String s : posts) {
            statement.setString(1, s);
            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:edu.clemson.cs.nestbed.server.adaptation.sql.ProgramSqlAdapter.java

public Program createNewProgram(int projectID, String name, String description) throws AdaptationException {
    Program program = null;//from   w ww . j  a v a 2 s  . c o  m
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {
        connection = DriverManager.getConnection(CONN_STR);
        connection.setAutoCommit(false);
        statement = connection.createStatement();

        String query = "INSERT INTO Programs(projectID, name, " + "description, sourcePath) VALUES ( "
                + projectID + ", " + "'" + name + "', " + "'" + description + "', " + "'" + "[unknown]" + "')";

        log.debug("SQL Query:\n" + query);
        statement.executeUpdate(query);

        query = "SELECT * FROM Programs WHERE " + " projectID   =  " + projectID + "  AND " + " name        = '"
                + name + "' AND " + " description = '" + description + "'";

        resultSet = statement.executeQuery(query);

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

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

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

From source file:tools.datasync.db2db.dao.GenericJDBCDao.java

public void saveOrUpdate(String entityName, JSON json, String keyColumn) {

    logger.finest("saveOrUpdate() - entityName=" + entityName + ", json=" + json + ", keyColumn=" + keyColumn);
    Connection connection = null;
    Statement statement = null;/* www  . java2  s  . co m*/
    try {
        // Try insert statement first...
        String insert = SQLGenUtil.getInsertStatement(entityName, json);
        connection = dataSource.getConnection();
        statement = connection.createStatement();
        logger.finest("saveOrUpdate() - " + insert);
        statement.execute(insert);
    } catch (SQLException ex) {
        // May be primary key violation, try update statement...
        if (SQLGenUtil.isConstraintViolation(ex)) {
            try {
                String update = SQLGenUtil.getUpdateStatement(entityName, json, keyColumn);
                logger.finest("saveOrUpdate() - " + update);
                statement.execute(update);
            } catch (SQLException e) {
                exceptionHandler.handle(e, Level.WARNING, "Failed to update record", json);
            }
        } else {
            exceptionHandler.handle(ex, Level.SEVERE, "Failed to insert record", json);
        }
    } finally {
        if (statement != null) {
            try {
                logger.finest("saveOrUpdate() - commiting changes.");
                connection.commit();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                exceptionHandler.handle(e, Level.WARNING, "Failed to close connection.");
            }
        }
    }
}

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

@Before
public void setUp() {
    super.setUp();

    SqoopOptions options = new SqoopOptions(MySQLTestUtils.CONNECT_STRING, getTableName());
    options.setUsername(MySQLTestUtils.getCurrentUser());

    LOG.debug("Setting up another DirectMySQLTest: " + MySQLTestUtils.CONNECT_STRING);

    manager = new DirectMySQLManager(options);

    Connection connection = null;
    Statement st = null;//from  ww w . j  a  v a2s. c o m

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

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

        st.executeUpdate("INSERT INTO " + getTableName() + " VALUES("
                + "NULL,'Aaron','2009-05-14',1000000.00,'engineering')");
        st.executeUpdate(
                "INSERT INTO " + getTableName() + " VALUES(" + "NULL,'Bob','2009-04-20',400.00,'sales')");
        st.executeUpdate(
                "INSERT INTO " + getTableName() + " VALUES(" + "NULL,'Fred','2009-01-23',15.00,'marketing')");
        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 != connection) {
                connection.close();
            }
        } catch (SQLException sqlE) {
            LOG.warn("Got SQLException when closing connection: " + sqlE);
        }
    }
}

From source file:com.clustercontrol.platform.infra.InfraJdbcExecutorSupport.java

public static void execInsertFileContent(String fileId, DataHandler handler)
        throws HinemosUnknown, InfraFileTooLarge {
    Connection conn = null;

    JpaTransactionManager tm = null;/*from www. j  a va  2 s .c  o m*/
    PGCopyOutputStream pgStream = null;
    FileOutputStream fos = null;
    BufferedInputStream bis = null;
    File tempFile = null;
    try {
        tm = new JpaTransactionManager();
        conn = tm.getEntityManager().unwrap(java.sql.Connection.class);
        conn.setAutoCommit(false);

        pgStream = new PGCopyOutputStream((PGConnection) conn,
                "COPY binarydata.cc_infra_file_content(file_id, file_content) FROM STDIN WITH (FORMAT BINARY)");

        String exportDirectory = HinemosPropertyUtil.getHinemosPropertyStr("infra.export.dir",
                HinemosPropertyDefault.getString(HinemosPropertyDefault.StringKey.INFRA_EXPORT_DIR));
        tempFile = new File(exportDirectory + fileId);
        fos = new FileOutputStream(tempFile);
        handler.writeTo(fos);

        long fileLength = tempFile.length();
        int maxSize = HinemosPropertyUtil.getHinemosPropertyNum(MAX_FILE_KEY, Long.valueOf(1024 * 1024 * 64))
                .intValue(); // 64MB
        if (fileLength > maxSize) {
            throw new InfraFileTooLarge(String.format("File size is larger than the limit size(%d)", maxSize));
        }

        pgStream.write(HEADER_SIGN_PART);
        pgStream.write(HEADER_FLG_FIELD_PART);
        pgStream.write(HEADER_EX_PART);
        pgStream.write(TUPLE_FIELD_COUNT_PART);
        pgStream.write(ByteBuffer.allocate(4).putInt(fileId.getBytes().length).array());
        pgStream.write(fileId.getBytes());
        pgStream.write(ByteBuffer.allocate(4).putInt((int) fileLength).array());

        bis = new BufferedInputStream(new FileInputStream(tempFile));
        byte[] buf = new byte[1024 * 1024];
        int read;
        while ((read = bis.read(buf)) != -1) {
            pgStream.write(buf, 0, read);
        }
        pgStream.write(FILETRAILER);
        pgStream.flush();

        if (!tm.isNestedEm()) {
            conn.commit();
        }
    } catch (InfraFileTooLarge e) {
        log.warn(e.getMessage());
        try {
            pgStream.close();
        } catch (IOException e1) {
            log.warn(e1);
        }
        try {
            conn.rollback();
        } catch (SQLException e1) {
            log.warn(e1);
        }
        throw e;
    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        try {
            if (pgStream != null)
                pgStream.close();
        } catch (IOException e1) {
            log.warn(e1);
        }
        try {
            if (conn != null)
                conn.rollback();
        } catch (SQLException e1) {
            log.warn(e1);
        }
        throw new HinemosUnknown(e.getMessage(), e);
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (IOException e) {
                log.warn(e.getMessage(), e);
                throw new HinemosUnknown(e.getMessage(), e);
            }
        }
        if (bis != null) {
            try {
                bis.close();
            } catch (IOException e) {
                log.warn(e.getMessage(), e);
                throw new HinemosUnknown(e.getMessage(), e);
            }
        }
        if (pgStream != null) {
            try {
                pgStream.close();
            } catch (IOException e) {
                log.warn(e.getMessage(), e);
                throw new HinemosUnknown(e.getMessage(), e);
            }
        }
        if (tm != null) {
            tm.close();
        }
        if (tempFile == null) {
            log.debug("Fail to delete. tempFile is null");
        } else if (!tempFile.delete()) {
            log.debug("Fail to delete " + tempFile.getAbsolutePath());
        }
    }
}

From source file:eionet.cr.dao.virtuoso.VirtuosoPostHarvestScriptDAO.java

/**
 * @see eionet.cr.dao.PostHarvestScriptDAO#insert(eionet.cr.dto.PostHarvestScriptDTO.TargetType, java.lang.String,
 *      java.lang.String, java.lang.String, boolean, boolean)
 *///  w  w  w  .j a v  a 2 s.  c o  m
@Override
public int insert(TargetType targetType, String targetUrl, String title, String script, boolean active,
        boolean runOnce) throws DAOException {

    String sourceUrl = targetType != null && targetType.equals(TargetType.SOURCE) ? targetUrl : null;
    String typeUrl = targetType != null && targetType.equals(TargetType.TYPE) ? targetUrl : null;

    Connection conn = null;
    try {
        conn = getSQLConnection();
        conn.setAutoCommit(false);

        ArrayList<Object> values = new ArrayList<Object>();
        values.add(sourceUrl == null ? "" : sourceUrl);
        values.add(typeUrl == null ? "" : typeUrl);

        Object o = SQLUtil.executeSingleReturnValueQuery(GET_LAST_POSITION_SQL, values, conn);
        int position = o == null ? 1 : Integer.parseInt(o.toString()) + 1;

        values = new ArrayList<Object>();
        values.add(sourceUrl);
        values.add(typeUrl);
        values.add(title);
        values.add(script);
        values.add(Integer.valueOf(position));
        values.add(YesNoBoolean.format(active));
        values.add(YesNoBoolean.format(runOnce));

        int result = SQLUtil.executeUpdateReturnAutoID(INSERT_SQL, values, conn);
        conn.commit();
        return result;
    } catch (Exception e) {
        SQLUtil.rollback(conn);
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(conn);
    }
}

From source file:updatePledge.java

private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
    String PledgeID = jComboBox4.getSelectedItem().toString();
    String PaymentFrequency, LastPaymentDate;
    PaymentFrequency = jComboBox5.getSelectedItem().toString();
    LastPaymentDate = jTextField2.getText();
    String Pay = jTextField1.getText();

    System.out.println(Pay);/*from w  ww  .j a  va  2s .  c  o m*/

    StringCC str = new StringCC();
    Float PayToDate = Float.parseFloat(str.CleanUp(Pay));

    try {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(viewDonors.class.getName()).log(Level.SEVERE, null, ex);
        }
        Connection con;
        con = DriverManager.getConnection(DBcon.Connect(), DBcon.Login(), DBcon.Pass()); //(file path, db login, db password) - since it doesnt have a login, leave it blank

        Statement s = con.createStatement();

        System.out.println("Connection to DB established...");

        s.executeUpdate("UPDATE [Pledges] SET " //also remember to space everything correctly, program was reading SETIND instead of SET IND
                + "Pledges.PaymentFrequency = " + "'" + PaymentFrequency + "'," + "Pledges.PayToDate = " + "'"
                + PayToDate + "'," + "Pledges.LastPaymentDate = " + "'" + LastPaymentDate + "' "
                + "WHERE (Pledges.PledgeID = '" + PledgeID + "')");

        System.out.println("Is connection closed: " + con.isClosed());
        System.out.println("Connection to DB established...");
        System.out.println("Update Executed.");

        con.commit();
        con.close();
        System.out.println("Is connection closed: " + con.isClosed());
    } catch (SQLException ex) {
        Logger.getLogger(userLogin.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(updatePledge.class.getName()).log(Level.SEVERE, null, ex);
    }
    jLabel4.setText("Updated.");

}

From source file:azkaban.project.JdbcProjectLoader.java

@Override
public void updateProjectSettings(Project project) throws ProjectManagerException {
    Connection connection = getConnection();
    try {/*  w w w  .j a  v a2 s  . co  m*/
        updateProjectSettings(connection, project, defaultEncodingType);
        connection.commit();
    } catch (SQLException e) {
        throw new ProjectManagerException("Error updating project settings", e);
    } finally {
        DbUtils.closeQuietly(connection);
    }
}

From source file:azkaban.project.JdbcProjectLoader.java

@Override
public void uploadProjectProperty(Project project, Props props) throws ProjectManagerException {
    Connection connection = getConnection();
    try {/*from   w ww  .ja  v a 2  s  .  c o m*/
        uploadProjectProperty(connection, project, props.getSource(), props);
        connection.commit();
    } catch (SQLException e) {
        throw new ProjectManagerException("Error uploading project property files", e);
    } catch (IOException e) {
        throw new ProjectManagerException("Error uploading project property file", e);
    } finally {
        DbUtils.closeQuietly(connection);
    }
}