Example usage for java.sql Connection setAutoCommit

List of usage examples for java.sql Connection setAutoCommit

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. setAutoCommit

Introduction

In this page you can find the example usage for java.sql Connection setAutoCommit.

Prototype

void setAutoCommit(boolean autoCommit) throws SQLException;

Source Link

Document

Sets this connection's auto-commit mode to the given state.

Usage

From source file:corner.migration.services.impl.MigrationServiceImpl.java

/**
 * Execute the given schema script on the given JDBC Connection.
 * <p>// w  w w.j  av a 2s  .  c  o  m
 * Note that the default implementation will log unsuccessful statements and
 * continue to execute. Override the <code>executeSchemaStatement</code>
 * method to treat failures differently.
 * 
 * @param con
 *            the JDBC Connection to execute the script on
 * @param sql
 *            the SQL statements to execute
 * @throws SQLException
 *             if thrown by JDBC methods
 * @see #executeSchemaStatement
 * @param con
 *            the JDBC Connection to execute the script on
 * @param sql
 *            the SQL statements to execute
 * @throws SQLException
 *             if thrown by JDBC methods
 */
protected void executeSchemaScript(Connection con, String... sql) throws SQLException {
    if (sql != null && sql.length > 0) {
        boolean oldAutoCommit = con.getAutoCommit();
        if (!oldAutoCommit) {
            con.setAutoCommit(false);
        }
        try {
            Statement stmt = con.createStatement();
            try {
                for (int i = 0; i < sql.length; i++) {
                    try {
                        logger.info("[db-upgrade] " + sql[i]);
                        executeSchemaStatement(stmt, sql[i]);
                    } catch (SQLException se) {
                        logger.error("[db-upgrade]" + se.toString(), se);
                        throw se;
                    }
                }
            } finally {
                JdbcUtils.closeStatement(stmt);
            }
        } finally {
            if (!oldAutoCommit) {
                con.setAutoCommit(false);
            }
        }
    }
}

From source file:com.mirth.connect.server.migration.Migrate3_0_0.java

private void migrateAlertTable() {
    Logger logger = Logger.getLogger(getClass());
    PreparedStatement statement = null;
    ResultSet results = null;/* www  .j a  v a2  s  . co  m*/

    try {
        Map<String, List<String>> alertEmails = new HashMap<String, List<String>>();
        Map<String, List<String>> alertChannels = new HashMap<String, List<String>>();

        /*
         * MIRTH-1667: Derby fails if autoCommit is set to true and there are a large number of
         * results. The following error occurs: "ERROR 40XD0: Container has been closed"
         */
        Connection connection = getConnection();
        connection.setAutoCommit(false);

        // Build a list of emails for each alert
        statement = connection.prepareStatement("SELECT ALERT_ID, EMAIL FROM OLD_ALERT_EMAIL");
        results = statement.executeQuery();

        while (results.next()) {
            String alertId = results.getString(1);
            String email = results.getString(2);

            List<String> emailSet = alertEmails.get(alertId);

            if (emailSet == null) {
                emailSet = new ArrayList<String>();
                alertEmails.put(alertId, emailSet);
            }

            emailSet.add(email);
        }

        DbUtils.closeQuietly(results);
        DbUtils.closeQuietly(statement);

        // Build a list of applied channels for each alert
        statement = connection.prepareStatement("SELECT CHANNEL_ID, ALERT_ID FROM OLD_CHANNEL_ALERT");
        results = statement.executeQuery();

        while (results.next()) {
            String channelId = results.getString(1);
            String alertId = results.getString(2);

            List<String> channelSet = alertChannels.get(alertId);

            if (channelSet == null) {
                channelSet = new ArrayList<String>();
                alertChannels.put(alertId, channelSet);
            }

            channelSet.add(channelId);
        }

        DbUtils.closeQuietly(results);
        DbUtils.closeQuietly(statement);

        statement = connection
                .prepareStatement("SELECT ID, NAME, IS_ENABLED, EXPRESSION, TEMPLATE, SUBJECT FROM OLD_ALERT");
        results = statement.executeQuery();

        while (results.next()) {
            String alertId = "";

            try {
                alertId = results.getString(1);
                String name = results.getString(2);
                boolean enabled = results.getBoolean(3);
                String expression = results.getString(4);
                String template = results.getString(5);
                String subject = results.getString(6);

                /*
                 * Create a new document with alertModel as the root node
                 */
                Document document = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
                Element alertNode = document.createElement("alert");
                document.appendChild(alertNode);

                Element node = document.createElement("id");
                node.setTextContent(alertId);
                alertNode.appendChild(node);

                node = document.createElement("name");
                node.setTextContent(name);
                alertNode.appendChild(node);

                node = document.createElement("expression");
                node.setTextContent(expression);
                alertNode.appendChild(node);

                node = document.createElement("template");
                node.setTextContent(template);
                alertNode.appendChild(node);

                node = document.createElement("enabled");
                node.setTextContent(Boolean.toString(enabled));
                alertNode.appendChild(node);

                node = document.createElement("subject");
                node.setTextContent(subject);
                alertNode.appendChild(node);

                // Add each applied channel to the document
                Element channelNode = document.createElement("channels");
                alertNode.appendChild(channelNode);
                List<String> channelList = alertChannels.get(alertId);
                if (channelList != null) {
                    for (String channelId : channelList) {
                        Element stringNode = document.createElement("string");
                        stringNode.setTextContent(channelId);
                        channelNode.appendChild(stringNode);
                    }
                }

                // Add each email address to the document
                Element emailNode = document.createElement("emails");
                alertNode.appendChild(emailNode);
                List<String> emailList = alertEmails.get(alertId);
                if (emailList != null) {
                    for (String email : emailList) {
                        Element stringNode = document.createElement("string");
                        stringNode.setTextContent(email);
                        emailNode.appendChild(stringNode);
                    }
                }

                String alert = new DonkeyElement(alertNode).toXml();

                PreparedStatement updateStatement = null;

                try {
                    updateStatement = connection.prepareStatement("INSERT INTO ALERT VALUES (?, ?, ?)");
                    updateStatement.setString(1, alertId);
                    updateStatement.setString(2, name);
                    updateStatement.setString(3, alert);
                    updateStatement.executeUpdate();
                    updateStatement.close();
                } finally {
                    DbUtils.closeQuietly(updateStatement);
                }
            } catch (Exception e) {
                logger.error("Error migrating alert " + alertId + ".", e);
            }
        }

        connection.commit();
    } catch (SQLException e) {
        logger.error("Error migrating alerts.", e);
    } finally {
        DbUtils.closeQuietly(results);
        DbUtils.closeQuietly(statement);
    }
}

From source file:com.insprise.common.db.DefaultConnectionPool.java

/**
* Gets a connection./*from   w  ww.j  av  a  2  s .  c  o m*/
* A shortcut for: getDataSource().getConnection(). After using the connection, always remember to close it.
* <p>
* <code><pre>
* Connection conn = null;
* try {
*   conn = pool.getConnection();
*   ...
* }catch(Exception e) {
*   ...
* }finally{
*   &lt;b&gt;conn.close();&lt;/b&gt; // return the connection to the pool.
* }
* </pre></code>
* @param readOnly Whether the connection should be read only or not.
* @return getDataSource().getConnection().
* @throws SQLException
*/
public Connection getConnection(boolean readOnly) throws SQLException {
    if (sourceType == SourceType.DRIVER_MANAGER) {
        if (!initialized) {
            synchronized (this) {
                if (!initialized) {
                    try {
                        setupPool();
                    } catch (ClassNotFoundException e) {
                        throw new SQLException(e.getMessage(), e);
                    }
                }
                initialized = true;
            }
        }
    }

    Connection conn = dataSource.getConnection();
    conn.setReadOnly(readOnly);
    if (readOnly) {
        conn.setAutoCommit(true); // auto commit read only.
    }

    return conn;
}

From source file:edu.umd.cs.marmoset.modelClasses.Project.java

public static Project importProject(InputStream in, Course course,
        StudentRegistration canonicalStudentRegistration, Connection conn)
        throws SQLException, IOException, ClassNotFoundException {
    Project project = new Project();
    ZipInputStream zipIn = new ZipInputStream(in);

    // Start transaction
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

    byte[] canonicalBytes = null;
    byte[] testSetupBytes = null;
    byte[] projectStarterFileBytes = null;

    while (true) {
        ZipEntry entry = zipIn.getNextEntry();
        if (entry == null)
            break;
        if (entry.getName().contains("project.out")) {
            // Found the serialized project!
            ObjectInputStream objectInputStream = new ObjectInputStream(zipIn);

            project = (Project) objectInputStream.readObject();

            // Set the PKs to null, the values that get serialized are actually from
            // a different database with a different set of keys
            project.setProjectPK(0);//w  w w .  j a  v  a2 s.  c  o m
            project.setTestSetupPK(0);
            project.setArchivePK(null);
            project.setVisibleToStudents(false);

            // These two PKs need to be passed in when we import/create the project
            project.setCoursePK(course.getCoursePK());
            project.setCanonicalStudentRegistrationPK(canonicalStudentRegistration.getStudentRegistrationPK());

            // Insert the project so that we have a projectPK for other methods
            project.insert(conn);

        } else if (entry.getName().contains("canonical")) {
            // Found the canonical submission...
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            CopyUtils.copy(zipIn, baos);
            canonicalBytes = baos.toByteArray();
        } else if (entry.getName().contains("test-setup")) {
            // Found the test-setup!
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            CopyUtils.copy(zipIn, baos);
            testSetupBytes = baos.toByteArray();
        } else if (entry.getName().contains("project-starter-files")) {
            // Found project starter files
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            CopyUtils.copy(zipIn, baos);
            projectStarterFileBytes = baos.toByteArray();
        }
    }

    Timestamp submissionTimestamp = new Timestamp(System.currentTimeMillis());

    // Now "upload" bytes as an archive for the project starter files, if it exists
    if (projectStarterFileBytes != null) {
        project.setArchiveForUpload(projectStarterFileBytes);
        project.uploadCachedArchive(conn);
    }

    // Now "submit" these bytes as a canonical submission
    // TODO read the submissionTimestamp from the serialized project in the archive
    Submission submission = Submission.submit(canonicalBytes, canonicalStudentRegistration, project,
            "t" + submissionTimestamp.getTime(), "ProjectImportTool, serialMinorVersion",
            Integer.toString(serialMinorVersion, 100), submissionTimestamp, conn);

    // Now "upload" the test-setup bytes as an archive
    String comment = "Project Import Tool uploaded at " + submissionTimestamp;
    TestSetup testSetup = TestSetup.submit(testSetupBytes, project, comment, conn);
    project.setTestSetupPK(testSetup.getTestSetupPK());
    testSetup.setTestRunPK(submission.getCurrentTestRunPK());

    testSetup.update(conn);

    return project;
}

From source file:com.globalsight.everest.permission.Permission.java

/**
 * Update Table permissiongroup. If permission id is greater than 300, the
 * id should plus 1. Then update permission_set to new string.
 *///from w  w  w . ja  v a 2 s .  c o  m
private static void updateUnbalancedPermissionGroupSet() {
    Connection c = null;
    PreparedStatement stmt = null;
    PreparedStatement stmt1 = null;
    ResultSet rs = null;
    try {
        c = ConnectionPool.getConnection();
        c.setAutoCommit(false);
        stmt = c.prepareStatement(SQL_SELECT_PERMISSION_SET_FROM_PERMISSION_GROUP);
        stmt1 = c.prepareStatement(SQL_UPDATE_PERMISSION_SET);
        rs = stmt.executeQuery();

        while (rs.next()) {
            long id = rs.getLong(1);
            String permissionSet = rs.getString(2);
            String[] permissionIdArray = permissionSet.split("\\|");

            StringBuffer newPermissionSet = new StringBuffer();
            for (String permissionId : permissionIdArray) {
                if (StringUtils.isNotEmpty(permissionId)) {
                    long lId = Long.parseLong(permissionId);
                    if (lId >= 300) {
                        lId += 1;
                    }
                    newPermissionSet.append("|").append(lId);
                }
            }
            newPermissionSet.append("|");
            stmt1.setString(1, newPermissionSet.toString());
            stmt1.setLong(2, id);
            stmt1.addBatch();
        }

        stmt1.executeBatch();
        c.commit();
    } catch (Exception e) {
        logger.error("Failed to update permission_group from database.", e);
    } finally {
        ConnectionPool.silentClose(rs);
        ConnectionPool.silentClose(stmt);
        ConnectionPool.silentClose(stmt1);
        ConnectionPool.silentReturnConnection(c);
    }
}

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

@Override
public void delete(List<Integer> ids) throws DAOException {

    if (ids == null || ids.isEmpty()) {
        return;/*w  w  w  .j  ava 2s . c o m*/
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = getSQLConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(DELETE_SQL);
        for (Integer id : ids) {
            stmt.setInt(1, id);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        SQLUtil.rollback(conn);
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(stmt);
        SQLUtil.close(conn);
    }
}

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

@Override
public void activateDeactivate(List<Integer> ids) throws DAOException {

    if (ids == null || ids.isEmpty()) {
        return;/*w  ww  . j  a va2s. co  m*/
    }

    Connection conn = null;
    PreparedStatement stmt = null;
    try {
        conn = getSQLConnection();
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(ACTIVATE_DEACTIVATE_SQL);
        for (Integer id : ids) {
            stmt.setInt(1, id);
            stmt.addBatch();
        }
        stmt.executeBatch();
        conn.commit();
    } catch (SQLException e) {
        SQLUtil.rollback(conn);
        throw new DAOException(e.getMessage(), e);
    } finally {
        SQLUtil.close(stmt);
        SQLUtil.close(conn);
    }
}

From source file:com.agiletec.plugins.jpcrowdsourcing.aps.system.services.ideainstance.IdeaInstanceDAO.java

@Override
public void removeIdeaInstance(String code) {
    PreparedStatement stat = null;
    Connection conn = null;
    try {/*  ww w  . j a  va  2 s  .  co m*/
        List<String> ideaList = this.getIdeaDAO().searchIdea(code, null, null, null, null);
        conn = this.getConnection();
        conn.setAutoCommit(false);
        this.getIdeaDAO().removeIdeas(ideaList, conn);
        this.removeIdeaInstanceGroups(code, conn);
        this.removeIdeaInstance(code, conn);
        conn.commit();
    } catch (Throwable t) {
        this.executeRollback(conn);
        _logger.error("Error deleting ideainstance", t);
        throw new RuntimeException("Error deleting ideainstance", t);
    } finally {
        this.closeDaoResources(null, stat, conn);
    }
}

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 www  .ja va2s .co  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:fitmon.WorkoutData.java

public void addData(String workout, String intensity, int minutes, double calories, String date, int userId)
        throws IOException, NoSuchAlgorithmException, InvalidKeyException, JSONException, SQLException,
        ClassNotFoundException {// w  w  w .j  a  v  a  2  s  .c om

    //ArrayList arr = new ArrayList(al);
    PreparedStatement st = null;
    Connection conn = null;

    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/fitmon", "root",
                "april-23");
        String query = "INSERT into workout (type,calories,date,intensity,duration,userId) values (?,?,?,?,?,?);";
        st = conn.prepareStatement(query);
        conn.setAutoCommit(false);

        //st.setInt(1,7);
        st.setString(1, workout);
        st.setDouble(2, calories);
        st.setString(3, date);
        st.setString(4, intensity);
        st.setInt(5, minutes);
        st.setInt(6, userId);
        st.addBatch();
        st.executeBatch();

        conn.commit();
        System.out.println("Record is inserted into workout table!");

        st.close();
        conn.close();

    } catch (SQLException e) {

        System.out.println(e.getMessage());
        conn.rollback();
    } finally {

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

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

    }

}