Example usage for java.sql Connection getAutoCommit

List of usage examples for java.sql Connection getAutoCommit

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

Introduction

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

Prototype

boolean getAutoCommit() throws SQLException;

Source Link

Document

Retrieves the current auto-commit mode for this Connection object.

Usage

From source file:org.apache.ddlutils.platform.PlatformImplBase.java

/**
 * {@inheritDoc}/*from  w ww.ja  v  a2  s.c  om*/
 */
public void insert(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException {
    SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean);
    SqlDynaProperty[] properties = getPropertiesForInsertion(model, dynaClass, dynaBean);
    Column[] autoIncrColumns = getRelevantIdentityColumns(model, dynaClass, dynaBean);

    if ((properties.length == 0) && (autoIncrColumns.length == 0)) {
        _log.warn("Cannot insert instances of type " + dynaClass + " because it has no usable properties");
        return;
    }

    String insertSql = createInsertSql(model, dynaClass, properties, null);
    String queryIdentitySql = null;

    if (_log.isDebugEnabled()) {
        _log.debug("About to execute SQL: " + insertSql);
    }

    if (autoIncrColumns.length > 0) {
        if (!getPlatformInfo().isLastIdentityValueReadable()) {
            _log.warn("The database does not support querying for auto-generated column values");
        } else {
            queryIdentitySql = createSelectLastInsertIdSql(model, dynaClass);
        }
    }

    boolean autoCommitMode = false;
    PreparedStatement statement = null;

    try {
        if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) {
            autoCommitMode = connection.getAutoCommit();
            connection.setAutoCommit(false);
        }

        beforeInsert(connection, dynaClass.getTable());

        statement = connection.prepareStatement(insertSql);

        for (int idx = 0; idx < properties.length; idx++) {
            setObject(statement, idx + 1, dynaBean, properties[idx]);
        }

        int count = statement.executeUpdate();

        afterInsert(connection, dynaClass.getTable());

        if (count != 1) {
            _log.warn("Attempted to insert a single row " + dynaBean + " in table " + dynaClass.getTableName()
                    + " but changed " + count + " row(s)");
        }
    } catch (SQLException ex) {
        throw new DatabaseOperationException("Error while inserting into the database: " + ex.getMessage(), ex);
    } finally {
        closeStatement(statement);
    }
    if (queryIdentitySql != null) {
        Statement queryStmt = null;
        ResultSet lastInsertedIds = null;

        try {
            if (getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) {
                // we'll commit the statement(s) if no auto-commit is enabled because
                // otherwise it is possible that the auto increment hasn't happened yet
                // (the db didn't actually perform the insert yet so no triggering of
                // sequences did occur)
                if (!connection.getAutoCommit()) {
                    connection.commit();
                }
            }

            queryStmt = connection.createStatement();
            lastInsertedIds = queryStmt.executeQuery(queryIdentitySql);

            lastInsertedIds.next();

            for (int idx = 0; idx < autoIncrColumns.length; idx++) {
                // we're using the index rather than the name because we cannot know how
                // the SQL statement looks like; rather we assume that we get the values
                // back in the same order as the auto increment columns
                Object value = getObjectFromResultSet(lastInsertedIds, autoIncrColumns[idx], idx + 1);

                PropertyUtils.setProperty(dynaBean, autoIncrColumns[idx].getName(), value);
            }
        } catch (NoSuchMethodException ex) {
            // Can't happen because we're using dyna beans
        } catch (IllegalAccessException ex) {
            // Can't happen because we're using dyna beans
        } catch (InvocationTargetException ex) {
            // Can't happen because we're using dyna beans
        } catch (SQLException ex) {
            throw new DatabaseOperationException(
                    "Error while retrieving the identity column value(s) from the database", ex);
        } finally {
            if (lastInsertedIds != null) {
                try {
                    lastInsertedIds.close();
                } catch (SQLException ex) {
                    // we ignore this one
                }
            }
            closeStatement(statement);
        }
    }
    if (!getPlatformInfo().isAutoCommitModeForLastIdentityValueReading()) {
        try {
            // we need to do a manual commit now
            connection.commit();
            connection.setAutoCommit(autoCommitMode);
        } catch (SQLException ex) {
            throw new DatabaseOperationException(ex);
        }
    }
}

From source file:ips1ap101.lib.core.db.xdp.RecursoCachedRowSetDataProvider.java

private void afterCommitException() {
    boolean b1 = listaRastroFuncion != null && !listaRastroFuncion.isEmpty()
            && (testing || loggingEnabledForFailure());
    if (b1) {//from  w w  w.j  ava  2 s  . c  om
        Connection connection = TLC.getConnection();
        if (connection != null) {
            try {
                boolean autoCommit = connection.getAutoCommit();
                if (DB.setAutoCommit(connection, false)) {
                    grabarRastroFilasConflictivas();
                    DB.commit(connection);
                    if (autoCommit) {
                        DB.setAutoCommit(connection, true);
                    }
                }
            } catch (SQLException ex) {
                TLC.getBitacora().error(ex);
                DB.rollback(connection);
            }
        }
    }
}

From source file:org.apache.cocoon.acting.DatabaseCookieAuthenticatorAction.java

/**
 *  Main invocation routine.//from w w w.jav  a 2 s . c o m
 *
 * @param  redirector     Description of Parameter
 * @param  resolver       Description of Parameter
 * @param  objectModel    Description of Parameter
 * @param  src            Description of Parameter
 * @param  parameters     Description of Parameter
 * @return                Description of the Returned Value
 * @exception  Exception  Description of Exception
 */
public Map act(Redirector redirector, SourceResolver resolver, Map objectModel, String src,
        Parameters parameters) throws Exception {
    DataSourceComponent datasource = null;
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;

    // read global parameter settings
    boolean reloadable = Constants.DESCRIPTOR_RELOADABLE_DEFAULT;

    if (this.settings.containsKey("reloadable")) {
        reloadable = Boolean.valueOf((String) this.settings.get("reloadable")).booleanValue();
    }

    // read local settings
    try {
        Configuration conf = this.getConfiguration(
                parameters.getParameter("descriptor", (String) this.settings.get("descriptor")), resolver,
                parameters.getParameterAsBoolean("reloadable", reloadable));
        String create_session = parameters.getParameter("create-session",
                (String) this.settings.get("create-session"));
        String append_session = parameters.getParameter("append-session",
                (String) this.settings.get("append-session"));
        boolean cs = true;
        if (create_session != null) {
            cs = BooleanUtils.toBoolean(create_session.trim());
        }
        boolean as = BooleanUtils.toBoolean(append_session.trim());

        datasource = this.getDataSource(conf);
        conn = datasource.getConnection();
        Request req = ObjectModelHelper.getRequest(objectModel);

        /*
         *  check request validity
         */
        if (req == null) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: no request object");
            }
            return null;
        }

        String query = this.getAuthQuery(objectModel, conf, req);
        if (query == null) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: have not got query");
            }
            req.setAttribute("message", "The authenticator is misconfigured");
            return null;
        }

        if (getLogger().isDebugEnabled()) {
            getLogger().debug("DBCOOKIEAUTH: query is: " + query);
        }
        st = conn.createStatement();
        rs = st.executeQuery(query);

        if (rs.next()) {
            if (getLogger().isDebugEnabled()) {
                getLogger().debug("DBCOOKIEAUTH: authorized successfully");
            }
            Session session = null;

            if (cs) {
                session = req.getSession(false);
                if (session != null) {
                    if (as == false) {
                        session.invalidate();
                        session = req.getSession(true);
                        if (getLogger().isDebugEnabled()) {
                            getLogger().debug("DBCOOKIEAUTH: session invalidated");
                        }
                    }
                } else {
                    session = req.getSession(true);
                }

                if (session == null) {
                    return null;
                }

                if (getLogger().isDebugEnabled()) {
                    if (as) {
                        getLogger().debug("DBCOOKIEAUTH: appending to session");
                    } else {
                        getLogger().debug("DBCOOKIEAUTH: session created");
                    }
                }
            } else {
                if (getLogger().isDebugEnabled()) {
                    getLogger().debug("DBCOOKIEAUTH: leaving session untouched");
                }
            }

            HashMap actionMap = this.propagateParameters(conf, rs, session);
            if (!conn.getAutoCommit()) {
                conn.commit();
            }
            return Collections.unmodifiableMap(actionMap);
        }
        if (!conn.getAutoCommit()) {
            conn.rollback();
        }

        req.setAttribute("message",
                "The username or password were incorrect, please check your CAPS LOCK key and try again.");
        if (getLogger().isDebugEnabled()) {
            getLogger().debug("DBCOOKIEAUTH: no results for query");
        }
    } catch (Exception e) {
        if (conn != null) {
            try {
                if (!conn.getAutoCommit()) {
                    conn.rollback();
                }
            } catch (Exception se) {
                // ignore
            }
        }
        getLogger().error("Exception: ", e);
        return null;
    } finally {
        if (rs != null) {
            rs.close();
        }
        if (st != null) {
            st.close();
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                // ignore
            }
        }
    }
    return null;
}

From source file:ips1ap101.lib.core.db.xdp.RecursoCachedRowSetDataProvider.java

private void afterCommit() {
    Long maestro = getIdentificacionMaestro();
    String procedure = maestro == null ? null : getAfterCommitProcedure();
    boolean b0 = procedure != null;
    boolean b1 = listaRastroFuncion != null && !listaRastroFuncion.isEmpty()
            && (testing || loggingEnabledForSuccess());
    boolean b2 = listaTareasUsuario != null && !listaTareasUsuario.isEmpty();
    if (b0 || b1 || b2) {
        Connection connection = TLC.getConnection();
        if (connection != null) {
            try {
                boolean autoCommit = connection.getAutoCommit();
                if (DB.setAutoCommit(connection, false)) {
                    if (b0 && TLC.getAgenteSql().isStoredProcedure(procedure)) {
                        Object[] args = new Object[] { maestro };
                        TLC.getAgenteSql().executeProcedure(procedure, args);
                    }//from   ww  w .j  ava2 s.  co m
                    if (b1) {
                        for (RastroFuncion rastro : listaRastroFuncion.values()) {
                            TLC.getControlador().grabarRastroFuncion(rastro);
                        }
                    }
                    if (b2) {
                        for (SeudoTareaUsuario seudo : listaTareasUsuario) {
                            TLC.getControlador().grabarTareasUsuario(seudo);
                        }
                    }
                    DB.commit(connection);
                    if (autoCommit) {
                        DB.setAutoCommit(connection, true);
                    }
                }
            } catch (SQLException ex) {
                TLC.getBitacora().error(ex);
                DB.rollback(connection);
            }
        }
    }
    listaRastroFuncion.clear();
    listaTareasUsuario.clear();
}

From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java

/**
 * Upgrade database for Roller 3.0.0//from w  w w  . jav a  2  s.  c o  m
 */
private void upgradeTo300(Connection con, boolean runScripts) throws StartupException {
    SQLScriptRunner runner = null;
    try {
        if (runScripts) {
            String handle = getDatabaseHandle(con);
            String scriptPath = handle + "/240-to-300-migration.sql";
            successMessage("Running database upgrade script: " + scriptPath);
            runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
            runner.runScript(con, true);
            messages.addAll(runner.getMessages());
        }

        /*
         * For Roller 3.0.0 we are allowing each weblogentry to track a
         * locale now so that we can support multi-lingual blogs.  As part
         * of the upgrade process we want to do 2 things ..
         *
         * 1. make sure all weblogs have a locale
         * 2. set the locale on all entries to the locale for the weblog
         */

        successMessage("Doing upgrade to 300 ...");

        // get system default language
        String locale = java.util.Locale.getDefault().getLanguage();

        successMessage("Setting website locale to " + locale + " for websites with no locale");

        // update all weblogs where locale is "null"
        PreparedStatement updateNullWeblogLocale = con
                .prepareStatement("update website set locale = ? where locale is NULL");
        // update all weblogs where locale is empty string ""
        PreparedStatement updateEmptyWeblogLocale = con
                .prepareStatement("update website set locale = ? where locale = ''");
        updateNullWeblogLocale.setString(1, locale);
        updateEmptyWeblogLocale.setString(1, locale);
        updateNullWeblogLocale.executeUpdate();
        updateEmptyWeblogLocale.executeUpdate();

        successMessage("Setting weblogentry locales to website locale");

        // get all entries and the locale of its website
        PreparedStatement selectWeblogsLocale = con.prepareStatement("select weblogentry.id,website.locale "
                + "from weblogentry,website " + "where weblogentry.websiteid = website.id");

        // set the locale for an entry
        PreparedStatement updateWeblogLocale = con
                .prepareStatement("update weblogentry set locale = ? where id = ?");

        ResultSet websiteSet = selectWeblogsLocale.executeQuery();
        while (websiteSet.next()) {
            String entryid = websiteSet.getString(1);
            String entrylocale = websiteSet.getString(2);

            // update entry locale
            updateWeblogLocale.clearParameters();
            updateWeblogLocale.setString(1, entrylocale);
            updateWeblogLocale.setString(2, entryid);
            updateWeblogLocale.executeUpdate();
        }

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Upgrade to 300 complete.");

    } catch (Exception e) {
        log.error("ERROR running 310 database upgrade script", e);
        if (runner != null)
            messages.addAll(runner.getMessages());

        errorMessage("Problem upgrading database to version 300", e);
        throw new StartupException("Problem upgrading database to version 300", e);
    }

    updateDatabaseVersion(con, 300);
}

From source file:herddb.cli.HerdDBCLI.java

private static void executeSqlFile(int autotransactionbatchsize, final Connection connection, String file,
        final boolean verbose, final boolean async, final boolean ignoreerrors, final boolean frommysqldump,
        final boolean rewritestatements, final Statement statement, TableSpaceMapper tableSpaceMapper,
        boolean pretty, String filter, final HerdDBDataSource datasource) throws Exception, SQLException {
    if (autotransactionbatchsize > 0) {
        connection.setAutoCommit(false);
    }//from  w  w w.  j  av  a2s  .  co  m
    long _start = System.currentTimeMillis();

    final IntHolder totalDoneCount = new IntHolder();
    File f = new File(file);
    long fileSize = f.length();
    boolean allowDDL = filter.equals("all") || filter.equals("ddl");
    boolean allowDML = filter.equals("all") || filter.equals("dml");
    SqlFileStatus fileStatus = new SqlFileStatus(verbose, async, ignoreerrors, frommysqldump, rewritestatements,
            pretty, tableSpaceMapper, datasource);
    try (FileInputStream rawStream = new FileInputStream(file);
            BufferedInputStream buffer = new BufferedInputStream(rawStream);
            CounterInputStream counter = new CounterInputStream(buffer);
            InputStream fIn = wrapStream(f.getName(), counter);
            CounterInputStream counterUnzipped = new CounterInputStream(fIn);
            InputStreamReader ii = new InputStreamReader(counterUnzipped, "utf-8");) {
        int _autotransactionbatchsize = autotransactionbatchsize;
        SQLFileParser.parseSQLFile(ii, (st) -> {
            if (!st.comment) {
                boolean isDDL = isDDL(st.content.toUpperCase());
                int totalBefore = fileStatus.executedOperations;
                if ((allowDDL && isDDL) || (allowDML && !isDDL)) {
                    executeStatementInSqlFile(st.content, statement, fileStatus);
                }
                int count = fileStatus.executedOperations - totalBefore;

                if (_autotransactionbatchsize > 0 && fileStatus.pendingOperations > _autotransactionbatchsize) {
                    totalDoneCount.value += fileStatus.pendingOperations;
                    fileStatus.flushAndCommit(connection);
                    long _now = System.currentTimeMillis();
                    long countZipped = counter.count;
                    int percent = (int) (counter.count * 100.0 / fileSize);
                    long delta = (_now - _start);
                    long countUnzipped = counterUnzipped.count;
                    double speed = ((countUnzipped * 60000.0) / (1.0 * delta));

                    double speedZipped = ((countZipped * 60000.0) / (1.0 * delta));

                    if (countUnzipped != counter.count) {
                        System.out.println(new java.sql.Timestamp(System.currentTimeMillis()) + " COMMIT after "
                                + totalDoneCount.value + " ops, read " + formatBytes(counter.count) + " ("
                                + formatBytes(countUnzipped) + " unzipped) over " + formatBytes(fileSize) + ". "
                                + percent + "%, " + formatBytes(speedZipped) + "/min (UNZIPPED "
                                + formatBytes(speed) + "/min)");
                    } else {
                        System.out.println(new java.sql.Timestamp(System.currentTimeMillis()) + " COMMIT after "
                                + totalDoneCount.value + " ops, read " + formatBytes(counter.count) + " over "
                                + formatBytes(fileSize) + ". " + percent + "%, " + formatBytes(speed)
                                + " /min");
                    }
                }
            }
        });
    }
    if (!connection.getAutoCommit()) {
        totalDoneCount.value += fileStatus.pendingOperations;
        fileStatus.flushAndCommit(connection);
    }
    System.out.println("Import completed, " + totalDoneCount.value + " ops, in "
            + ((System.currentTimeMillis() - _start) / 60000) + " minutes");

}

From source file:org.apache.zeppelin.hopshive.HopsHiveInterpreter.java

private InterpreterResult executeSql(String propertyKey, String sql, InterpreterContext interpreterContext) {
    Connection connection;
    Statement statement;/*w w w  .ja  v a 2s  .com*/
    ResultSet resultSet = null;
    String paragraphId = interpreterContext.getParagraphId();
    String user = interpreterContext.getAuthenticationInfo().getUser();

    boolean splitQuery = false;
    String splitQueryProperty = getProperty(String.format("%s.%s", propertyKey, SPLIT_QURIES_KEY));
    if (StringUtils.isNotBlank(splitQueryProperty) && splitQueryProperty.equalsIgnoreCase("true")) {
        splitQuery = true;
    }

    InterpreterResult interpreterResult = new InterpreterResult(InterpreterResult.Code.SUCCESS);
    try {
        connection = getConnection(propertyKey, interpreterContext);
        if (connection == null) {
            return new InterpreterResult(Code.ERROR, "Prefix not found.");
        }

        List<String> sqlArray;
        if (splitQuery) {
            sqlArray = splitSqlQueries(sql);
        } else {
            sqlArray = Arrays.asList(sql);
        }

        for (int i = 0; i < sqlArray.size(); i++) {
            String sqlToExecute = sqlArray.get(i);
            statement = connection.createStatement();

            // fetch n+1 rows in order to indicate there's more rows available (for large selects)
            statement.setFetchSize(getMaxResult());
            statement.setMaxRows(getMaxResult() + 1);

            if (statement == null) {
                return new InterpreterResult(Code.ERROR, "Prefix not found.");
            }

            try {
                getJDBCConfiguration(user).saveStatement(paragraphId, statement);

                boolean isResultSetAvailable = statement.execute(sqlToExecute);
                getJDBCConfiguration(user).setConnectionInDBDriverPoolSuccessful(propertyKey);
                if (isResultSetAvailable) {
                    resultSet = statement.getResultSet();

                    // Regards that the command is DDL.
                    if (isDDLCommand(statement.getUpdateCount(), resultSet.getMetaData().getColumnCount())) {
                        interpreterResult.add(InterpreterResult.Type.TEXT, "Query executed successfully.");
                    } else {
                        String results = getResults(resultSet,
                                !containsIgnoreCase(sqlToExecute, EXPLAIN_PREDICATE));
                        interpreterResult.add(results);
                        if (resultSet.next()) {
                            interpreterResult.add(ResultMessages.getExceedsLimitRowsMessage(getMaxResult(),
                                    String.format("%s.%s", COMMON_KEY, MAX_LINE_KEY)));
                        }
                    }
                } else {
                    // Response contains either an update count or there are no results.
                    int updateCount = statement.getUpdateCount();
                    interpreterResult.add(InterpreterResult.Type.TEXT,
                            "Query executed successfully. Affected rows : " + updateCount);
                }
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
            }
        }
        //In case user ran an insert/update/upsert statement
        if (connection != null) {
            try {
                if (!connection.getAutoCommit()) {
                    connection.commit();
                }
            } catch (SQLException e) {
                /*ignored*/ }
        }
        getJDBCConfiguration(user).removeStatement(paragraphId);
    } catch (Throwable e) {
        logger.error("Cannot run " + sql, e);
        String errorMsg = Throwables.getStackTraceAsString(e);
        interpreterResult.add(errorMsg);
        return new InterpreterResult(Code.ERROR, interpreterResult.message());
    }
    return interpreterResult;
}

From source file:org.sakaiproject.tool.assessment.facade.AssessmentGradingFacadeQueries.java

public void removeMediaById(Long mediaId, Long itemGradingId) {
    String mediaLocation = null;/*from ww w. j av  a2s.  co  m*/
    Session session = null;
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement statement = null;
    PreparedStatement statement0 = null;
    try {
        session = getSessionFactory().openSession();
        conn = session.connection();
        log.debug("****Connection=" + conn);
        String query0 = "select LOCATION from SAM_MEDIA_T where MEDIAID=?";
        statement0 = conn.prepareStatement(query0);
        statement0.setLong(1, mediaId.longValue());
        rs = statement0.executeQuery();
        if (rs.next()) {
            mediaLocation = rs.getString("LOCATION");
        }
        log.debug("****mediaLocation=" + mediaLocation);

        String query = "delete from SAM_MEDIA_T where MEDIAID=?";
        statement = conn.prepareStatement(query);
        statement.setLong(1, mediaId.longValue());
        statement.executeUpdate();
        if (!conn.getAutoCommit()) {
            conn.commit();
        }
    } catch (Exception e) {
        log.warn(e.getMessage());
    } finally {
        if (session != null) {
            try {
                session.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        if (statement0 != null) {
            try {
                statement0.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e1) {
                e1.printStackTrace();
            }
        }
    }
    if (mediaLocation != null) {
        File mediaFile = new File(mediaLocation);
        if (mediaFile.delete()) {
            log.warn("problem removing file. mediaLocation = " + mediaLocation);
        }
    }

    if (itemGradingId != null) {
        ItemGradingData itemGradingData = getItemGrading(itemGradingId);
        itemGradingData.setAutoScore(Double.valueOf(0));
        saveItemGrading(itemGradingData);
    }
}

From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java

/**
 * Upgrade database for Roller 2.0.0/*from ww  w .j  a va 2s  . c  om*/
 */
private void upgradeTo200(Connection con, boolean runScripts) throws StartupException {
    SQLScriptRunner runner = null;
    try {
        if (runScripts) {
            String handle = getDatabaseHandle(con);
            String scriptPath = handle + "/130-to-200-migration.sql";
            successMessage("Running database upgrade script: " + scriptPath);
            runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
            runner.runScript(con, true);
            messages.addAll(runner.getMessages());
        }

        successMessage("Doing upgrade to 200 ...");
        successMessage("Populating roller_user_permissions table");

        PreparedStatement websitesQuery = con
                .prepareStatement("select w.id as wid, u.id as uid, u.username as uname from "
                        + "website as w, rolleruser as u where u.id=w.userid");
        PreparedStatement websiteUpdate = con.prepareStatement("update website set handle=? where id=?");
        PreparedStatement entryUpdate = con.prepareStatement("update weblogentry set userid=?, status=?, "
                + "pubtime=pubtime, updatetime=updatetime " + "where publishentry=? and websiteid=?");
        PreparedStatement permsInsert = con.prepareStatement("insert into roller_permissions "
                + "(id, username, actions, objectid, objecttype, pending, datecreated) "
                + "values (?,?,?,?,?,?,?)");

        // loop through websites, each has a user
        java.sql.Date now = new java.sql.Date(new Date().getTime());
        ResultSet websiteSet = websitesQuery.executeQuery();
        while (websiteSet.next()) {
            String websiteid = websiteSet.getString("wid");
            String userid = websiteSet.getString("uid");
            String username = websiteSet.getString("uname");
            successMessage("Processing website: " + username);

            // use website user's username as website handle
            websiteUpdate.clearParameters();
            websiteUpdate.setString(1, username);
            websiteUpdate.setString(2, websiteid);
            websiteUpdate.executeUpdate();

            // update all of pubished entries to include userid and status
            entryUpdate.clearParameters();
            entryUpdate.setString(1, userid);
            entryUpdate.setString(2, "PUBLISHED");
            entryUpdate.setBoolean(3, true);
            entryUpdate.setString(4, websiteid);
            entryUpdate.executeUpdate();

            // update all of draft entries to include userid and status
            entryUpdate.clearParameters();
            entryUpdate.setString(1, userid);
            entryUpdate.setString(2, "DRAFT");
            entryUpdate.setBoolean(3, false);
            entryUpdate.setString(4, websiteid);
            entryUpdate.executeUpdate();

            // add  permission for user in website
            permsInsert.clearParameters();
            permsInsert.setString(1, websiteid + "p");
            permsInsert.setString(2, username);
            permsInsert.setString(3, WeblogPermission.ADMIN);
            permsInsert.setString(4, websiteid);
            permsInsert.setString(5, "Weblog");
            permsInsert.setBoolean(6, false);
            permsInsert.setDate(7, now);
            permsInsert.setBoolean(5, false);
            permsInsert.executeUpdate();
        }

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Upgrade to 200 complete.");

    } catch (Exception e) {
        log.error("ERROR running 310 database upgrade script", e);
        if (runner != null)
            messages.addAll(runner.getMessages());

        errorMessage("Problem upgrading database to version 200", e);
        throw new StartupException("Problem upgrading database to version 200", e);
    }

    updateDatabaseVersion(con, 200);
}

From source file:org.apache.roller.weblogger.business.startup.DatabaseInstaller.java

/**
 * Upgrade database for Roller 4.0.0//from  w w  w. j  a v  a2  s  .  c  o m
 */
private void upgradeTo400(Connection con, boolean runScripts) throws StartupException {

    successMessage("Doing upgrade to 400 ...");

    // first we need to run upgrade scripts 
    SQLScriptRunner runner = null;
    try {
        if (runScripts) {
            String handle = getDatabaseHandle(con);
            String scriptPath = handle + "/310-to-400-migration.sql";
            successMessage("Running database upgrade script: " + scriptPath);
            runner = new SQLScriptRunner(scripts.getDatabaseScript(scriptPath));
            runner.runScript(con, true);
            messages.addAll(runner.getMessages());
        }
    } catch (Exception ex) {
        log.error("ERROR running 400 database upgrade script", ex);
        if (runner != null)
            messages.addAll(runner.getMessages());

        errorMessage("Problem upgrading database to version 400", ex);
        throw new StartupException("Problem upgrading database to version 400", ex);
    }

    // now upgrade hierarchical objects data model
    try {
        successMessage("Populating parentid columns for weblogcategory and folder tables");

        // Populate parentid in weblogcategory and folder tables.
        //
        // We'd like to do something like the below, but few databases 
        // support multiple table udpates, which are part of SQL-99
        //
        // update weblogcategory, weblogcategoryassoc 
        //   set weblogcategory.parentid = weblogcategoryassoc.ancestorid 
        //   where 
        //      weblogcategory.id = weblogcategoryassoc.categoryid 
        //      and weblogcategoryassoc.relation = 'PARENT';
        //
        // update folder,folderassoc 
        //   set folder.parentid = folderassoc.ancestorid 
        //   where 
        //      folder.id = folderassoc.folderid 
        //      and folderassoc.relation = 'PARENT';

        PreparedStatement selectParents = con.prepareStatement(
                "select categoryid, ancestorid from weblogcategoryassoc where relation='PARENT'");
        PreparedStatement updateParent = con
                .prepareStatement("update weblogcategory set parentid=? where id=?");
        ResultSet parentSet = selectParents.executeQuery();
        while (parentSet.next()) {
            String categoryid = parentSet.getString(1);
            String parentid = parentSet.getString(2);
            updateParent.clearParameters();
            updateParent.setString(1, parentid);
            updateParent.setString(2, categoryid);
            updateParent.executeUpdate();
        }

        selectParents = con
                .prepareStatement("select folderid, ancestorid from folderassoc where relation='PARENT'");
        updateParent = con.prepareStatement("update folder set parentid=? where id=?");
        parentSet = selectParents.executeQuery();
        while (parentSet.next()) {
            String folderid = parentSet.getString(1);
            String parentid = parentSet.getString(2);
            updateParent.clearParameters();
            updateParent.setString(1, parentid);
            updateParent.setString(2, folderid);
            updateParent.executeUpdate();
        }

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Done populating parentid columns.");

    } catch (Exception e) {
        errorMessage("Problem upgrading database to version 320", e);
        throw new StartupException("Problem upgrading database to version 320", e);
    }

    try {
        successMessage("Populating path columns for weblogcategory and folder tables.");

        // Populate path in weblogcategory and folder tables.
        //
        // It would be nice if there was a simple sql solution for doing
        // this, but sadly the only real way to do it is through brute
        // force walking the hierarchical trees.  Luckily, it seems that
        // most people don't create multi-level hierarchies, so hopefully
        // this won't be too bad

        // set path to '/' for nodes with no parents (aka root nodes)
        PreparedStatement setRootPaths = con
                .prepareStatement("update weblogcategory set path = '/' where parentid is NULL");
        setRootPaths.clearParameters();
        setRootPaths.executeUpdate();

        // select all nodes whose parent has no parent (aka 1st level nodes)
        PreparedStatement selectL1Children = con
                .prepareStatement("select f.id, f.name from weblogcategory f, weblogcategory p "
                        + "where f.parentid = p.id and p.parentid is NULL");
        // update L1 nodes with their path (/<name>)
        PreparedStatement updateL1Children = con
                .prepareStatement("update weblogcategory set path=? where id=?");
        ResultSet L1Set = selectL1Children.executeQuery();
        while (L1Set.next()) {
            String id = L1Set.getString(1);
            String name = L1Set.getString(2);
            updateL1Children.clearParameters();
            updateL1Children.setString(1, "/" + name);
            updateL1Children.setString(2, id);
            updateL1Children.executeUpdate();
        }

        // now for the complicated part =(
        // we need to keep iterating over L2, L3, etc nodes and setting
        // their path until all nodes have been updated.

        // select all nodes whose parent path has been set, excluding L1 nodes
        PreparedStatement selectLxChildren = con
                .prepareStatement("select f.id, f.name, p.path from weblogcategory f, weblogcategory p "
                        + "where f.parentid = p.id and p.path <> '/' "
                        + "and p.path is not NULL and f.path is NULL");
        // update Lx nodes with their path (<parentPath>/<name>)
        PreparedStatement updateLxChildren = con
                .prepareStatement("update weblogcategory set path=? where id=?");

        // this loop allows us to run this part of the upgrade process as
        // long as is necessary based on the depth of the hierarchy, and
        // we use the do/while construct to ensure it's run at least once
        int catNumCounted = 0;
        do {
            log.debug("Doing pass over Lx children for categories");

            // reset count for each iteration of outer loop
            catNumCounted = 0;

            ResultSet LxSet = selectLxChildren.executeQuery();
            while (LxSet.next()) {
                String id = LxSet.getString(1);
                String name = LxSet.getString(2);
                String parentPath = LxSet.getString(3);
                updateLxChildren.clearParameters();
                updateLxChildren.setString(1, parentPath + "/" + name);
                updateLxChildren.setString(2, id);
                updateLxChildren.executeUpdate();

                // count the updated rows
                catNumCounted++;
            }

            log.debug("Updated " + catNumCounted + " Lx category paths");
        } while (catNumCounted > 0);

        // set path to '/' for nodes with no parents (aka root nodes)
        setRootPaths = con.prepareStatement("update folder set path = '/' where parentid is NULL");
        setRootPaths.clearParameters();
        setRootPaths.executeUpdate();

        // select all nodes whose parent has no parent (aka 1st level nodes)
        selectL1Children = con.prepareStatement("select f.id, f.name from folder f, folder p "
                + "where f.parentid = p.id and p.parentid is NULL");
        // update L1 nodes with their path (/<name>)
        updateL1Children = con.prepareStatement("update folder set path=? where id=?");
        L1Set = selectL1Children.executeQuery();
        while (L1Set.next()) {
            String id = L1Set.getString(1);
            String name = L1Set.getString(2);
            updateL1Children.clearParameters();
            updateL1Children.setString(1, "/" + name);
            updateL1Children.setString(2, id);
            updateL1Children.executeUpdate();
        }

        // now for the complicated part =(
        // we need to keep iterating over L2, L3, etc nodes and setting
        // their path until all nodes have been updated.

        // select all nodes whose parent path has been set, excluding L1 nodes
        selectLxChildren = con.prepareStatement("select f.id, f.name, p.path from folder f, folder p "
                + "where f.parentid = p.id and p.path <> '/' " + "and p.path is not NULL and f.path is NULL");
        // update Lx nodes with their path (/<name>)
        updateLxChildren = con.prepareStatement("update folder set path=? where id=?");

        // this loop allows us to run this part of the upgrade process as
        // long as is necessary based on the depth of the hierarchy, and
        // we use the do/while construct to ensure it's run at least once
        int folderNumUpdated = 0;
        do {
            log.debug("Doing pass over Lx children for folders");

            // reset count for each iteration of outer loop
            folderNumUpdated = 0;

            ResultSet LxSet = selectLxChildren.executeQuery();
            while (LxSet.next()) {
                String id = LxSet.getString(1);
                String name = LxSet.getString(2);
                String parentPath = LxSet.getString(3);
                updateLxChildren.clearParameters();
                updateLxChildren.setString(1, parentPath + "/" + name);
                updateLxChildren.setString(2, id);
                updateLxChildren.executeUpdate();

                // count the updated rows
                folderNumUpdated++;
            }

            log.debug("Updated " + folderNumUpdated + " Lx folder paths");
        } while (folderNumUpdated > 0);

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Done populating path columns.");

    } catch (SQLException e) {
        log.error("Problem upgrading database to version 320", e);
        throw new StartupException("Problem upgrading database to version 320", e);
    }

    // 4.0 changes the planet data model a bit, so we need to clean that up
    try {
        successMessage("Merging planet groups 'all' and 'external'");

        // Move all subscriptions in the planet group 'external' to group 'all'

        String allGroupId = null;
        PreparedStatement selectAllGroupId = con
                .prepareStatement("select id from rag_group where handle = 'all'");
        ResultSet rs = selectAllGroupId.executeQuery();
        if (rs.next()) {
            allGroupId = rs.getString(1);
        }

        String externalGroupId = null;
        PreparedStatement selectExternalGroupId = con
                .prepareStatement("select id from rag_group where handle = 'external'");
        rs = selectExternalGroupId.executeQuery();
        if (rs.next()) {
            externalGroupId = rs.getString(1);
        }

        // we only need to merge if both of those groups already existed
        if (allGroupId != null && externalGroupId != null) {
            PreparedStatement updateGroupSubs = con
                    .prepareStatement("update rag_group_subscription set group_id = ? where group_id = ?");
            updateGroupSubs.clearParameters();
            updateGroupSubs.setString(1, allGroupId);
            updateGroupSubs.setString(2, externalGroupId);
            updateGroupSubs.executeUpdate();

            // we no longer need the group 'external'
            PreparedStatement deleteExternalGroup = con
                    .prepareStatement("delete from rag_group where handle = 'external'");
            deleteExternalGroup.executeUpdate();

            // if we only have group 'external' then just rename it to 'all'
        } else if (allGroupId == null && externalGroupId != null) {

            // rename 'external' to 'all'
            PreparedStatement renameExternalGroup = con
                    .prepareStatement("update rag_group set handle = 'all' where handle = 'external'");
            renameExternalGroup.executeUpdate();
        }

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Planet group 'external' merged into group 'all'.");

    } catch (Exception e) {
        errorMessage("Problem upgrading database to version 400", e);
        throw new StartupException("Problem upgrading database to version 400", e);
    }

    // update local planet subscriptions to use new local feed format
    try {
        successMessage("Upgrading local planet subscription feeds to new feed url format");

        // need to start by looking up absolute site url
        PreparedStatement selectAbsUrl = con
                .prepareStatement("select value from roller_properties where name = 'site.absoluteurl'");
        String absUrl = null;
        ResultSet rs = selectAbsUrl.executeQuery();
        if (rs.next()) {
            absUrl = rs.getString(1);
        }

        if (absUrl != null && absUrl.length() > 0) {
            PreparedStatement selectSubs = con
                    .prepareStatement("select id,feed_url,author from rag_subscription");

            PreparedStatement updateSubUrl = con.prepareStatement(
                    "update rag_subscription set last_updated=last_updated, feed_url = ? where id = ?");

            ResultSet rset = selectSubs.executeQuery();
            while (rset.next()) {
                String id = rset.getString(1);
                String feed_url = rset.getString(2);
                String handle = rset.getString(3);

                // only work on local feed urls
                if (feed_url.startsWith(absUrl)) {
                    // update feed_url to 'weblogger:<handle>'
                    updateSubUrl.clearParameters();
                    updateSubUrl.setString(1, "weblogger:" + handle);
                    updateSubUrl.setString(2, id);
                    updateSubUrl.executeUpdate();
                }
            }
        }

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Comments successfully updated to use new comment plugins.");

    } catch (Exception e) {
        errorMessage("Problem upgrading database to version 400", e);
        throw new StartupException("Problem upgrading database to version 400", e);
    }

    // upgrade comments to use new plugin mechanism
    try {
        successMessage("Upgrading existing comments with content-type & plugins");

        // look in db and see if comment autoformatting is enabled
        boolean autoformatEnabled = false;
        String autoformat = null;
        PreparedStatement selectIsAutoformtEnabled = con.prepareStatement(
                "select value from roller_properties where name = 'users.comments.autoformat'");
        ResultSet rs = selectIsAutoformtEnabled.executeQuery();
        if (rs.next()) {
            autoformat = rs.getString(1);
            if (autoformat != null && "true".equals(autoformat)) {
                autoformatEnabled = true;
            }
        }

        // look in db and see if comment html escaping is enabled
        boolean htmlEnabled = false;
        String escapehtml = null;
        PreparedStatement selectIsEscapehtmlEnabled = con.prepareStatement(
                "select value from roller_properties where name = 'users.comments.escapehtml'");
        ResultSet rs1 = selectIsEscapehtmlEnabled.executeQuery();
        if (rs1.next()) {
            escapehtml = rs1.getString(1);
            // NOTE: we allow html only when html escaping is OFF
            if (escapehtml != null && !"true".equals(escapehtml)) {
                htmlEnabled = true;
            }
        }

        // first lets set the new 'users.comments.htmlenabled' property
        PreparedStatement addCommentHtmlProp = con
                .prepareStatement("insert into roller_properties(name,value) values(?,?)");
        addCommentHtmlProp.clearParameters();
        addCommentHtmlProp.setString(1, "users.comments.htmlenabled");
        if (htmlEnabled) {
            addCommentHtmlProp.setString(2, "true");
        } else {
            addCommentHtmlProp.setString(2, "false");
        }
        addCommentHtmlProp.executeUpdate();

        // determine content-type for existing comments
        String contentType = "text/plain";
        if (htmlEnabled) {
            contentType = "text/html";
        }

        // determine plugins for existing comments
        String plugins = "";
        if (htmlEnabled && autoformatEnabled) {
            plugins = "HTMLSubset,AutoFormat";
        } else if (htmlEnabled) {
            plugins = "HTMLSubset";
        } else if (autoformatEnabled) {
            plugins = "AutoFormat";
        }

        // set new comment plugins configuration property 'users.comments.plugins'
        PreparedStatement addCommentPluginsProp = con
                .prepareStatement("insert into roller_properties(name,value) values(?,?)");
        addCommentPluginsProp.clearParameters();
        addCommentPluginsProp.setString(1, "users.comments.plugins");
        addCommentPluginsProp.setString(2, plugins);
        addCommentPluginsProp.executeUpdate();

        // set content-type for all existing comments
        PreparedStatement updateCommentsContentType = con
                .prepareStatement("update roller_comment set posttime=posttime, contenttype = ?");
        updateCommentsContentType.clearParameters();
        updateCommentsContentType.setString(1, contentType);
        updateCommentsContentType.executeUpdate();

        // set plugins for all existing comments
        PreparedStatement updateCommentsPlugins = con
                .prepareStatement("update roller_comment set posttime=posttime, plugins = ?");
        updateCommentsPlugins.clearParameters();
        updateCommentsPlugins.setString(1, plugins);
        updateCommentsPlugins.executeUpdate();

        if (!con.getAutoCommit())
            con.commit();

        successMessage("Comments successfully updated to use new comment plugins.");

    } catch (Exception e) {
        errorMessage("Problem upgrading database to version 400", e);
        throw new StartupException("Problem upgrading database to version 400", e);
    }

    // finally, upgrade db version string to 400
    updateDatabaseVersion(con, 400);
}