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:dbutils.DbUtilsTemplate.java

/**
 * ??//w  w w  .  j  a  v a2  s.  co m
 *
 * @param sql    sql?
 * @param params ?
 * @return (?, -1)
 * @throws SQLException
 */
public long insert(String sql, Object[] params) throws SQLException {
    long result = -1L;
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
        conn = dataSource.getConnection();
        stmt = conn.prepareStatement(sql);
        fillStatement(stmt, params);
        int affectCount = stmt.executeUpdate();
        if (affectCount <= 0)
            return -1L;
        rs = stmt.getGeneratedKeys();
        result = rs.next() ? rs.getLong(1) : -1;
        conn.commit();
    } catch (SQLException e) {
        LOG.error("Error occured while attempting to insert data", e);
        if (conn != null) {
            conn.rollback();
        }
        throw e;
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }
    return result;
}

From source file:com.tacitknowledge.util.migration.jdbc.SqlScriptMigrationTaskTest.java

/**
 * Test that sybase database patches are committed when illegal multi
 * statement transaction commands are used.
 * //from  w w w  .  j av  a 2 s. c o  m
 * @throws IOException
 *                 if an unexpected error occurs
 * @throws MigrationException
 *                 if an unexpected error occurs
 * @throws SQLException
 *                 if an unexpected error occurs
 */
public void testSybasePatchesCommitsOnEveryStatement() throws IOException, MigrationException, SQLException {
    InputStream is = getClass().getResourceAsStream("test/sybase_tsql.sql");
    assertNotNull(is);
    task = new SqlScriptMigrationTask("sybase_tsql.sql", 1, is);

    MockDatabaseType dbType = new MockDatabaseType("sybase");
    dbType.setMultipleStatementsSupported(false);
    context.setDatabaseType(dbType);
    int numStatements = task.getSqlStatements(context).size();

    // setup mocks to verify commits are called
    MockControl dataSourceControl = MockControl.createControl(DataSource.class);
    DataSource dataSource = (DataSource) dataSourceControl.getMock();
    context.setDataSource(dataSource);

    MockControl connectionControl = MockControl.createControl(Connection.class);
    Connection connection = (Connection) connectionControl.getMock();

    dataSourceControl.expectAndReturn(dataSource.getConnection(), connection);

    MockControl statementControl = MockControl.createControl(Statement.class);
    Statement statement = (Statement) statementControl.getMock();
    statement.execute("");
    statementControl.setMatcher(MockControl.ALWAYS_MATCHER);
    statementControl.setReturnValue(true, MockControl.ONE_OR_MORE);
    statement.close();
    statementControl.setVoidCallable(MockControl.ONE_OR_MORE);

    connectionControl.expectAndReturn(connection.isClosed(), false, MockControl.ONE_OR_MORE);
    connectionControl.expectAndReturn(connection.createStatement(), statement, numStatements);
    connectionControl.expectAndReturn(connection.getAutoCommit(), false, MockControl.ONE_OR_MORE);
    connection.commit();
    /*
     * Magic Number 4 derived from the assumption that the fixture sql
     * contains only one statement that is not allowed in a multi statement
     * transaction: commit at beginning of migrate method commit prior to
     * running the command not allowed in multi statement transaction to
     * clear the transaction state. commit after running the multi statement
     * transaction to clear transaction state for upcoming statements.
     * commit at end of migrate method once all statements executed.
     * 
     * Therefore, if you add more illegal statements to the fixture, add 2
     * more commit call's for each illegal statement.
     */
    connectionControl.setVoidCallable(4);

    dataSourceControl.replay();
    connectionControl.replay();
    statementControl.replay();

    // run tests
    task.migrate(context);
    dataSourceControl.verify();
    connectionControl.verify();
}

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

/**
 * Executes a prepared statement with no result.
 * /*from w w  w . ja v a 2  s  . co m*/
 * @param con the connection to the database
 * @param stmt the prepared statement to execute
 */
private void executePreparedStatementNoResult(Connection con, PreparedStatement stmt) {
    try {
        con.setAutoCommit(false);
        stmt.execute();
        con.commit();
    } catch (SQLException e) {
        if (log.isErrorEnabled()) {
            log.error("", 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);
            }
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error("Error setting auto commit.", e);
            }
        }
    }
}

From source file:desktop.olayinka.file.transfer.model.DerbyJDBCHelper.java

public void onStart(Connection mConnection) {
    Statement statement = null;/* w  w w . j ava  2  s . c o  m*/
    ResultSet resultSet = null;
    try {
        statement = mConnection.createStatement();
        resultSet = statement.executeQuery("SELECT * FROM  app_info");
        resultSet.next();
        mCurrentVersion = resultSet.getLong(1);
        cleanUp(statement, resultSet);
    } catch (SQLException e) {
        e.printStackTrace();
        cleanUp(statement, resultSet);
        try {
            statement = mConnection.createStatement();

            InputStream in = DerbyJDBCHelper.class.getResourceAsStream("/raw/db.sql");
            String[] queries = IOUtils.toString(in).split(";");
            for (String query : queries) {
                query = query.trim();
                if (!query.isEmpty()) {
                    statement.execute(query);
                }
            }

            mConnection.commit();

            mCurrentVersion = 1;

            cleanUp(statement, null);
        } catch (SQLException | IOException e1) {
            e1.printStackTrace();
            cleanUp(statement, null);
            System.exit(1);
        }
    }

}

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

/**
 * The concept of database in Oracle is mapped to schemas. Each schema
 * is identified by the corresponding username.
 *//* www. ja  va  2 s  .co  m*/
@Override
public String[] listDatabases() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    List<String> databases = new ArrayList<String>();

    try {
        conn = getConnection();
        stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        rset = stmt.executeQuery(QUERY_LIST_DATABASES);

        while (rset.next()) {
            databases.add(rset.getString(1));
        }
        conn.commit();
    } catch (SQLException e) {
        try {
            conn.rollback();
        } catch (Exception ex) {
            LOG.error("Failed to rollback transaction", ex);
        }

        if (e.getErrorCode() == ERROR_TABLE_OR_VIEW_DOES_NOT_EXIST) {
            LOG.error("The catalog view DBA_USERS was not found. "
                    + "This may happen if the user does not have DBA privileges. "
                    + "Please check privileges and try again.");
            LOG.debug("Full trace for ORA-00942 exception", e);
        } else {
            LOG.error("Failed to list databases", e);
        }
    } finally {
        if (rset != null) {
            try {
                rset.close();
            } catch (SQLException ex) {
                LOG.error("Failed to close resultset", ex);
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception ex) {
                LOG.error("Failed to close statement", ex);
            }
        }

        try {
            close();
        } catch (SQLException ex) {
            LOG.error("Unable to discard connection", ex);
        }
    }

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

From source file:com.skilrock.lms.web.scratchService.orderMgmt.common.RequestApproveAction.java

/**
 * /*w ww  .ja  v  a 2  s  .  c o  m*/
 * This method is used to Deny the request from the Agent
 * 
 * @author SkilRock Technologies
 * @Param
 * @Return String(SUCCESS or ERROR) throws LMSException
 */

public String Deny() throws LMSException {
    HttpSession session = getRequest().getSession();
    orderId = ((Integer) session.getAttribute("OrgId")).intValue();

    Connection conn = null;
    PreparedStatement pstmt1 = null;

    try {
        conn = DBConnect.getConnection();
        conn.setAutoCommit(false);

        String query = QueryManager.getST5OrderRequest5Query();
        // String query = "update st_se_bo_order set order_status='DENIED'
        // WHERE order_id=?";
        pstmt1 = conn.prepareStatement(query);
        System.out.println("Query1 from Request Deny Action  " + query);
        System.out.println("OrderId>>>>" + orderId);
        pstmt1.setInt(1, orderId);
        pstmt1.executeUpdate();
        conn.commit();
        return SUCCESS;
    } catch (SQLException se) {
        System.out.println("We got an exception while preparing a statement:" + "Probably bad SQL.");
        se.printStackTrace();
        setRequestApproval("No");
        throw new LMSException(se);
    } finally {

        try {

            if (pstmt1 != null) {
                pstmt1.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
            throw new LMSException(se);
        }

    }

}

From source file:com.iucosoft.eavertizare.dao.impl.ClientsDaoImpl.java

@Override
public void saveLocal(Firma firma, List<Client> clientsList) {

    String query = "INSERT INTO " + firma.getTabelaClientiLocal() + " VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
    Connection con = null;
    PreparedStatement ps = null;/*w w  w  .j av a 2s.com*/
    try {
        con = dataSource.getConnection();
        ps = con.prepareStatement(query);
        // Set auto-commit to false
        con.setAutoCommit(false);

        for (Client client : clientsList) {
            ps.setInt(1, client.getId());
            ps.setString(2, client.getNume());
            ps.setString(3, client.getPrenume());
            ps.setInt(4, client.getNrTelefon());
            ps.setString(5, client.getEmail());
            ps.setTimestamp(6, (new java.sql.Timestamp(client.getDateExpirare().getTime())));
            ps.setInt(7, firma.getId());
            ps.setInt(8, 0);

            ps.addBatch();
        }

        // Create an int[] to hold returned values
        int[] count = ps.executeBatch();
        //Explicitly commit statements to apply changes
        con.commit();

    } catch (SQLException e) {
        e.printStackTrace();
        try {
            con.rollback();
        } catch (SQLException ex) {
            Logger.getLogger(ClientsDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
    } finally {
        try {
            ps.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

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

private void saveBaseline(HttpServletRequest request) throws ServiceException, SessionExpiredException {
    Connection conn = null;
    try {/*  w  ww. java 2s.  co m*/
        conn = DbPool.getConnection();
        String projid = request.getParameter("projectid");
        String projName = projdb.getProjectName(conn, projid);
        String userid = AuthHandler.getUserid(request);
        boolean isUnderLimit = projdb.checkBaseline(conn, projid);
        if (isUnderLimit) {
            projdb.saveBaseline(conn, projid, userid,
                    projName.concat(" Baseline - ")
                            .concat(new SimpleDateFormat("yyyy-MM-dd").format(new Date())),
                    "Baseline created on importing MPP/MPX file.");
        }
        conn.commit();
    } catch (Exception ex) {
        DbPool.quietRollback(conn);
        throw ServiceException.FAILURE("FileImporterServlet.saveBaseline", ex);
    } finally {
        DbPool.quietClose(conn);
    }
}

From source file:dqyt.cy6.yutao.common.port.adapter.persistence.eventsourcing.mysql.MySQLJDBCEventStore.java

public EventStream eventStreamSince(EventStreamId anIdentity) {

    Connection connection = this.connection();

    ResultSet result = null;//from ww  w  .  j av  a 2  s  .  c  o m

    try {
        PreparedStatement statement = connection
                .prepareStatement("SELECT stream_version, event_type, event_body FROM tbl_es_event_store "
                        + "WHERE stream_name = ? AND stream_version >= ? " + "ORDER BY stream_version");

        statement.setString(1, anIdentity.streamName());
        statement.setInt(2, anIdentity.streamVersion());

        result = statement.executeQuery();

        EventStream eventStream = this.buildEventStream(result);

        if (eventStream.version() == 0) {
            throw new EventStoreException("There is no such event stream: " + anIdentity.streamName() + " : "
                    + anIdentity.streamVersion());
        }

        connection.commit();

        return eventStream;

    } catch (Throwable t) {
        throw new EventStoreException("Cannot query event stream for: " + anIdentity.streamName()
                + " since version: " + anIdentity.streamVersion() + " because: " + t.getMessage(), t);
    } finally {
        if (result != null) {
            try {
                result.close();
            } catch (SQLException e) {
                // ignore
            }
        }
        try {
            connection.close();
        } catch (SQLException e) {
            // ignore
        }
    }
}

From source file:com.ibm.soatf.component.database.StatementExecutor.java

/**
 * Runs an SQL script from the file specified by the <code>inputScriptFile</code> parameter
 * //from   w  w w .j a  v  a  2s.  c  o m
 * @param conn SQL connection on which you want to run this script
 * @param file script file
 * @throws StatementExecutorException if SQL or IO exception occurs
 */
public void runScript(Connection conn, File file) throws DatabaseComponentException {
    OperationResult cor = OperationResult.getInstance();
    String inputScriptFilePath = "";
    String inputScriptRelativePath = "";
    Statement stmt = null;
    try {
        ProgressMonitor.increment("Loading SQL script...");
        inputScriptFilePath = file.getAbsolutePath();
        inputScriptRelativePath = FileSystem.getRelativePath(file);
        String sql = FileUtils.readFileToString(file);
        if (sql.endsWith(";"))
            sql = sql.substring(0, sql.length() - 1);
        String msg = "Successfuly loaded script [FILE: %s]";
        logger.debug(String.format(msg, inputScriptFilePath));
        cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>",
                inputScriptRelativePath);

        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        ProgressMonitor.increment("Executing SQL script...");
        boolean hasResults = stmt.execute(sql);
        conn.commit();
        int updateCount = -1;
        if (!hasResults) {
            updateCount = stmt.getUpdateCount();
        }
        msg = "Script run successful, update count: " + updateCount;
        logger.debug(msg);
        cor.addMsg(msg);
        final String logMsg = "Record has been inserted into source database '" + conn.getMetaData().getURL()
                + "'.\n" + "Insert statement executed:\n%s";
        cor.addMsg(logMsg, sql, "[FILE: " + FileSystem.getRelativePath(file) + "]");
        cor.markSuccessful();
    } catch (IOException ex) {
        final String msg = "Failed to open statement [FILE: %s].";
        cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>",
                inputScriptRelativePath);
        throw new DatabaseComponentException(String.format(msg, inputScriptFilePath), ex);
    } catch (SQLException ex) {
        final String msg = String.format("Failed to execute INSERT statement: %s",
                Utils.getSQLExceptionMessage(ex));
        cor.addMsg(msg);
        throw new DatabaseComponentException(msg, ex);
    } finally {
        DatabaseComponent.closeStatement(stmt);
    }
}