Example usage for java.sql Connection setSavepoint

List of usage examples for java.sql Connection setSavepoint

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

Introduction

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

Prototype

Savepoint setSavepoint(String name) throws SQLException;

Source Link

Document

Creates a savepoint with the given name in the current transaction and returns the new Savepoint object that represents it.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);//from   w  w  w  .  j a  va  2s. co m
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int,myURL CHAR);");
    st.executeUpdate("insert into survey(id) values(01)");
    st.executeUpdate("insert into survey(id) values(02)");

    Savepoint mySavepoint = conn.setSavepoint("MYSAVEPOINT");

    st.executeUpdate("insert into survey(id) values(03)");
    conn.commit();

    conn.rollback(mySavepoint);

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

From source file:MainClass.java

public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;//from w  ww  .  ja  v a  2s  . c o m
    try {
        Class.forName("org.hsqldb.jdbcDriver").newInstance();
        String url = "jdbc:hsqldb:hsqldb\\demoDatabase";
        connection = DriverManager.getConnection(url, "username", "password");
        connection.setAutoCommit(false);

        statement = connection.createStatement();

        String update1 = "UPDATE employees SET email = 'a@b.com' WHERE email = 'a@a.com'";
        statement.executeUpdate(update1);
        Savepoint savepoint1 = connection.setSavepoint("savepoint1");

        String update2 = "UPDATE employees SET email = 'b@b.com' WHERE email = 'b@c.com'";
        statement.executeUpdate(update2);
        Savepoint savepoint2 = connection.setSavepoint("savepoint2");

        String update3 = "UPDATE employees SET email = 'c@c.com' WHERE email = 'c@d.com'";
        statement.executeUpdate(update3);
        Savepoint savepoint3 = connection.setSavepoint("savepoint3");

        String update4 = "UPDATE employees SET email = 'd@d.com' WHERE email = 'd@e.com'";
        statement.executeUpdate(update4);
        Savepoint savepoint4 = connection.setSavepoint("savepoint4");

        String update5 = "UPDATE employees SET email = 'e@e.com' WHERE email = 'e@f.com'";
        statement.executeUpdate(update5);
        Savepoint savepoint5 = connection.setSavepoint("savepoint5");

        connection.rollback(savepoint3);
        connection.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
            } // nothing we can do
        }
    }
}

From source file:org.apache.eagle.alert.metadata.impl.JdbcMetadataHandler.java

public <T> OpResult addOrReplace(String clzName, T t) {
    String tb = getTableName(clzName);
    OpResult result = new OpResult();
    Savepoint savepoint = null;/*from   w  w  w  .  j a  va 2  s.  com*/
    String key = null;
    String value = null;
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        key = MetadataUtils.getKey(t);
        value = mapper.writeValueAsString(t);
        connection.setAutoCommit(false);
        savepoint = connection.setSavepoint("insertEntity");
        result = executeUpdate(connection, String.format(INSERT_STATEMENT, tb), key, value);
        connection.commit();
    } catch (SQLException e) {
        LOG.warn("fail to insert entity due to {}, and try to updated instead", e.getMessage());
        if (connection != null) {
            LOG.info("Detected duplicated entity");
            try {
                connection.rollback(savepoint);
                executeUpdate(connection, String.format(UPDATE_STATEMENT, tb), key, value);
                connection.commit();
                connection.setAutoCommit(true);
            } catch (SQLException e1) {
                LOG.warn("Rollback failed", e1);
            }
        }
    } catch (JsonProcessingException e) {
        LOG.error("Got JsonProcessingException: {}", e.getMessage(), e.getCause());
        result.code = OpResult.FAILURE;
        result.message = e.getMessage();
    } finally {
        closeResource(null, null, connection);
    }
    return result;
}

From source file:com.cloud.utils.db.Transaction.java

public Savepoint setSavepoint(final String name) throws SQLException {
    _txn = true;//from w ww  .  j  ava2 s.  co m
    StackElement st = new StackElement(START_TXN, null);
    _stack.push(st);
    final Connection conn = getConnection();
    final Savepoint sp = conn.setSavepoint(name);
    st.ref = sp;

    return sp;
}

From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java

/**
 * Updates the definition of a view within the DB. This method should only
 * be used if updateViewDbActionWithDropAndCreate and
 * updateViewDbActionWithCreateOrReplace fails. Drops any dependent views so
 * that 'report' can be updated. Once report has been updated, all dependent
 * views are recreated./* w  w w.  j a va2 s .c o  m*/
 */
private void updateViewDbActionWithDropAndCreateDependencies(Connection conn, BaseReportInfo report,
        HttpServletRequest request)
        throws SQLException, ObjectNotFoundException, CodingErrorException, CantDoThatException {
    Savepoint savepoint = null;
    PreparedStatement statement = null;
    try {
        savepoint = conn.setSavepoint("dropAndCreateDependenciesSavepoint");
        Map<String, List<String>> reportDependencyMap = new HashMap<String, List<String>>();
        this.fillViewDependencyMap(conn, report.getInternalReportName(), reportDependencyMap, true);
        // Remove reports...
        List<String> deletedReports = new ArrayList<String>();
        while (deletedReports.size() < reportDependencyMap.size()) {
            for (String reportInternalName : reportDependencyMap.keySet()) {
                if (!deletedReports.contains(reportInternalName)) {
                    boolean cannotDelete = false;
                    for (String dependentReportInternalName : reportDependencyMap.get(reportInternalName)) {
                        if (!deletedReports.contains(dependentReportInternalName)) {
                            cannotDelete = true;
                        }
                    }
                    if (!cannotDelete) {
                        PreparedStatement dropViewStatement = conn
                                .prepareStatement("DROP VIEW " + reportInternalName);
                        dropViewStatement.execute();
                        dropViewStatement.close();
                        deletedReports.add(reportInternalName);
                    }
                }
            }
        }
        // Recreate reports...
        Collections.reverse(deletedReports);
        for (String reportInternalName : deletedReports) {
            TableInfo table = this.findTableContainingReportWithoutChecks(reportInternalName, request);
            HibernateUtil.activateObject(table);
            BaseReportInfo reportToRecreate = table.getReport(reportInternalName);
            String CreateViewSQL = "CREATE VIEW " + reportInternalName + " AS ("
                    + reportToRecreate.getSQLForDetail() + ")";
            statement = conn.prepareStatement(CreateViewSQL);
            statement.execute();
            statement.close();
        }
    } catch (SQLException sqlex) {
        conn.rollback(savepoint);
        throw new SQLException("The requested change would cause an error in the report: " + sqlex.getMessage()
                + ". SQL = " + statement, sqlex.getSQLState(), sqlex);
    }
}

From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java

/**
 * Updates the definition of a view within the DB. This method should only
 * be used if updateViewDbActionWithCreateOrReplace fails. Drops the view
 * and recreates it.//from  w  w  w  .  j  a  va  2s.c o  m
 */
private boolean updateViewDbActionWithDropAndCreate(Connection conn, BaseReportInfo report)
        throws SQLException, CantDoThatException, CodingErrorException, ObjectNotFoundException {
    String CreateViewSQL = "CREATE VIEW " + report.getInternalReportName() + " AS (" + report.getSQLForDetail()
            + ")";
    boolean dropAndCreateWorked = true;
    Savepoint savepoint = null;
    try {
        savepoint = conn.setSavepoint("dropAndCreateSavepoint");
        PreparedStatement dropViewStatement = conn
                .prepareStatement("DROP VIEW " + report.getInternalReportName());
        dropViewStatement.execute();
        dropViewStatement.close();
        PreparedStatement statement = conn.prepareStatement(CreateViewSQL);
        statement.execute();
        statement.close();
    } catch (SQLException sqlex) {
        conn.rollback(savepoint);
        dropAndCreateWorked = false;
    }
    return dropAndCreateWorked;
}

From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java

/**
 * Updates the definition of a view within the DB. This method will not work
 * if the number of columns within the view are being changed.
 *//* w  ww  .  jav a 2 s  .co m*/
private boolean updateViewDbActionWithCreateOrReplace(Connection conn, BaseReportInfo report,
        boolean viewExists)
        throws SQLException, CantDoThatException, CodingErrorException, ObjectNotFoundException {
    String SQLCode = "CREATE OR REPLACE VIEW " + report.getInternalReportName() + " AS ("
            + report.getSQLForDetail() + ")";
    boolean createOrReplaceWorked = true;
    Savepoint savepoint = null;
    PreparedStatement statement = null;
    try {
        savepoint = conn.setSavepoint("createOrReplaceSavepoint");
        statement = conn.prepareStatement(SQLCode);
        statement.execute();
        statement.close();
    } catch (SQLException sqlex) {
        if (viewExists) {
            createOrReplaceWorked = false;
            conn.rollback(savepoint);
        } else {
            // if view didn't exist already, the error must be more serious
            // than just the CREATE OR REPLACE not working
            // logger.error("Requested change to report " +
            // report.getReportName()
            // + " would break view. Error = " + sqlex);
            // logger.error("SQL = " + report.getSQLForDetail());
            throw new SQLException("The requested change would cause an error in the report: "
                    + sqlex.getMessage() + ". SQL = " + statement, sqlex.getSQLState(), sqlex);
        }
    }
    return createOrReplaceWorked;
}

From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java

public void addCalculationToReport(HttpServletRequest request, Connection conn, SimpleReportInfo report,
        ReportCalcFieldInfo calculationField) throws SQLException, DisallowedException,
        InconsistentStateException, CantDoThatException, CodingErrorException, ObjectNotFoundException {
    if (!(this.authManager.getAuthenticator().loggedInUserAllowedTo(request, PrivilegeType.MANAGE_TABLE,
            report.getParentTable()))) {
        throw new DisallowedException(this.authManager.getLoggedInUser(request), PrivilegeType.MANAGE_TABLE,
                report.getParentTable());
    }/*  w w w . ja v a  2s. c  o m*/
    HibernateUtil.activateObject(report);
    Savepoint savepoint = null;
    report.addCalculation(calculationField);
    savepoint = conn.setSavepoint("addCalculationSavepoint");
    try {
        this.updateViewDbAction(conn, report, request);
    } catch (SQLException sqlex) {
        // detect aggregate functions
        if (sqlex.getMessage().contains("must appear in the GROUP BY clause")
                || sqlex.getMessage().contains("aggregates not allowed in GROUP BY clause")) {
            conn.rollback(savepoint);
            calculationField.setAggregateFunction(true);
            this.updateViewDbAction(conn, report, request);
        } else {
            throw sqlex;
        }
    }
    UsageLogger usageLogger = new UsageLogger(this.relationalDataSource);
    AppUserInfo user = this.authManager.getUserByUserName(request, request.getRemoteUser());
    usageLogger.logReportSchemaChange(user, report, AppAction.ADD_CALCULATION_TO_REPORT,
            "calculation name: " + calculationField.getFieldName());
    UsageLogger.startLoggingThread(usageLogger);
}

From source file:com.gtwm.pb.model.manageSchema.DatabaseDefn.java

public void updateCalculationInReport(HttpServletRequest request, Connection conn, SimpleReportInfo report,
        ReportCalcFieldInfo calculationField, String calculationName, String calculationDefn,
        DatabaseFieldType dbFieldType, boolean isReportHidden) throws DisallowedException, SQLException,
        ObjectNotFoundException, CantDoThatException, CodingErrorException {
    if (!(this.authManager.getAuthenticator().loggedInUserAllowedTo(request, PrivilegeType.MANAGE_TABLE,
            report.getParentTable()))) {
        throw new DisallowedException(this.authManager.getLoggedInUser(request), PrivilegeType.MANAGE_TABLE,
                report.getParentTable());
    }//from  w  w w . ja v a2 s .  c  o  m
    HibernateUtil.activateObject(report);
    Savepoint savepoint = null;
    boolean definitionUpdate = false;
    if (!(calculationDefn.toLowerCase().equals(calculationField.getCalculationDefinition()))
            || !(dbFieldType.equals(calculationField.getDbType()))) {
        Map<TableInfo, Set<BaseReportInfo>> availableDataStores = this.getViewableDataStores(request);
        ((ReportCalcFieldDefn) calculationField).updateCalculationDefinition(calculationDefn, dbFieldType,
                availableDataStores);
        definitionUpdate = true;
    } else {
        ((ReportCalcFieldDefn) calculationField).setBaseFieldName(calculationName);
        calculationField.setReportHidden(isReportHidden);
    }
    if (definitionUpdate) {
        savepoint = conn.setSavepoint("updateCalculationSavepoint");
        try {
            this.updateViewDbAction(conn, report, request);
        } catch (SQLException sqlex) {
            // detect aggregate functions
            if (sqlex.getMessage().contains("must appear in the GROUP BY clause")
                    || sqlex.getMessage().contains("aggregates not allowed in GROUP BY clause")) {
                conn.rollback(savepoint);
                calculationField.setAggregateFunction(true);
                this.updateViewDbAction(conn, report, request);
            } else {
                throw sqlex;
            }
        }
    }
    UsageLogger usageLogger = new UsageLogger(this.relationalDataSource);
    AppUserInfo user = this.authManager.getUserByUserName(request, request.getRemoteUser());
    usageLogger.logReportSchemaChange(user, report, AppAction.UPDATE_CALCULATION_IN_REPORT,
            "calculation name: " + calculationField.getFieldName());
    UsageLogger.startLoggingThread(usageLogger);
}