Example usage for org.apache.commons.dbutils DbUtils closeQuietly

List of usage examples for org.apache.commons.dbutils DbUtils closeQuietly

  1. HOME
  2. Java
  3. org.apache.commons
  4. org.apache.commons.dbutils.*
  5. DbUtils
  6. closeQuietly

Introduction

In this page you can find the example usage for org.apache.commons.dbutils DbUtils closeQuietly.

Prototype

public static void closeQuietly(Statement stmt)

Source Link

Document

Close a Statement, avoid closing if null and hide any SQLExceptions that occur.

Usage

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;/*from   www.j  a v  a 2  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:azkaban.migration.scheduler.JdbcScheduleLoader.java

@Override
public void updateNextExecTime(Schedule s) throws ScheduleManagerException {
    logger.info("Update schedule " + s.getScheduleName() + " into db. ");
    Connection connection = getConnection();
    QueryRunner runner = new QueryRunner();
    try {//w  ww  .j av a 2s .  co m

        runner.update(connection, UPDATE_NEXT_EXEC_TIME, s.getNextExecTime(), s.getProjectId(),
                s.getFlowName());
    } catch (SQLException e) {
        e.printStackTrace();
        logger.error(UPDATE_NEXT_EXEC_TIME + " failed.", e);
        throw new ScheduleManagerException("Update schedule " + s.getScheduleName() + " into db failed. ", e);
    } finally {
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.gs.obevo.dbmetadata.impl.DbMetadataManagerImpl.java

/**
 * We have some special logic in here to interact w/ Schema crawler, as the different RDBMS types have
 * different parameters to the JDBC metadata
 *
 * @param searchAllProcedures @return//from  w w  w  . ja  v  a  2s .  c  o m
 */
private DaCatalog getDatabase(DaSchemaInfoLevel schemaInfoLevel, String schemaName, String tableName,
        String procedureName, boolean searchAllTables, boolean searchAllProcedures) {
    // Many of the DB metadata drivers like IQ/ASE/DB2 don't support the function metadata lookups and
    // schemacrawler complains (though the library still does the job). We set the log level here to avoid
    // excessive log messages
    java.util.logging.Logger.getLogger("schemacrawler").setLevel(Level.WARNING);
    java.util.logging.Logger.getLogger("schemacrawler.crawl.RoutineRetriever").setLevel(Level.SEVERE);
    java.util.logging.Logger.getLogger("schemacrawler.crawl.SchemaCrawler").setLevel(Level.SEVERE);

    Validate.notNull(schemaName, "Schema must be specified");
    Connection conn = null;
    try {
        conn = this.ds.getConnection();
        if (schemaName != null) {
            this.dbMetadataDialect.setSchemaOnConnection(conn, schemaName);
        }

        SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        // Set what details are required in the schema - this affects the time taken to crawl the schema
        // Standard works for our use cases (e.g. columns, indices, pks)
        options.setSchemaInfoLevel(toInfoLevel(schemaInfoLevel));

        DatabaseSpecificOverrideOptionsBuilder dbSpecificOptionsBuilder = dbMetadataDialect
                .getDbSpecificOptionsBuilder(conn);
        DatabaseSpecificOverrideOptions dbSpecificOptions = dbSpecificOptionsBuilder.toOptions();
        this.enrichSchemaCrawlerOptions(conn, options, dbSpecificOptions, schemaName, tableName, procedureName);

        if (tableName == null && procedureName != null && !searchAllTables) {
            options.setTableInclusionRule(new ExcludeAll());
        }
        if (procedureName == null && tableName != null && !searchAllProcedures) {
            options.setRoutineInclusionRule(new ExcludeAll());
        }

        LOG.debug("Starting query for DB metadata for {}/{}/{}/{}", tableName, procedureName,
                searchAllTables ? "searching all tables" : "",
                searchAllProcedures ? "searching all procedures" : "");
        final SchemaCrawler schemaCrawler = new SchemaCrawler(conn, dbSpecificOptions);

        final Catalog database;
        try {
            database = schemaCrawler.crawl(options);
        } catch (SchemaCrawlerException e) {
            if ("No matching schemas found".equals(e.getMessage())) {
                return null; // This is to match SchemaCrawler 9.6 behavior; need to check w/ them on this
            } else {
                throw e;
            }
        }

        LOG.debug("Ending query for DB metadata for {}/{}/{}/{}", tableName, procedureName,
                searchAllTables ? "searching all tables" : "",
                searchAllProcedures ? "searching all procedures" : "");

        this.dbMetadataDialect.validateDatabase(database, schemaName);

        SchemaStrategy schemaStrategy = dbMetadataDialect.getSchemaStrategy();
        Schema schemaReference = database.getSchemas().isEmpty() ? null
                : database.getSchemas().iterator().next();
        DaSchema schema = new DaSchemaImpl(schemaReference, schemaStrategy);

        ImmutableCollection<DaRoutine> extraRoutines = Lists.immutable.empty();

        if (schemaInfoLevel.isRetrieveRoutines() && (searchAllProcedures || procedureName != null)) {
            extraRoutines = this.dbMetadataDialect.searchExtraRoutines(schema, procedureName, conn);
        }

        ImmutableCollection<ExtraIndexInfo> extraConstraintIndices = schemaInfoLevel
                .isRetrieveTableCheckConstraints()
                        ? dbMetadataDialect.searchExtraConstraintIndices(schema, tableName, conn)
                        : Lists.immutable.<ExtraIndexInfo>empty();
        Multimap<String, ExtraIndexInfo> constraintIndices = extraConstraintIndices
                .groupBy(new Function<ExtraIndexInfo, String>() {
                    @Override
                    public String valueOf(ExtraIndexInfo object) {
                        return object.getTableName();
                    }
                });

        ImmutableCollection<ExtraRerunnableInfo> extraViewInfo = schemaInfoLevel.isRetrieveViewDetails()
                ? dbMetadataDialect.searchExtraViewInfo(schema, tableName, conn)
                : Lists.immutable.<ExtraRerunnableInfo>empty();

        DaRoutineType routineOverrideValue = dbMetadataDialect.getRoutineOverrideValue();

        ImmutableCollection<RuleBinding> ruleBindings = schemaInfoLevel.isRetrieveRuleBindings()
                ? dbMetadataDialect.getRuleBindings(schema, conn)
                : Lists.immutable.<RuleBinding>empty();
        ImmutableCollection<DaRule> rules = schemaInfoLevel.isRetrieveRules()
                ? dbMetadataDialect.searchRules(schema, conn)
                : Lists.immutable.<DaRule>empty();
        ImmutableCollection<DaSequence> sequences = schemaInfoLevel.isRetrieveSequences()
                ? dbMetadataDialect.searchSequences(schema, conn)
                : Lists.immutable.<DaSequence>empty();
        ImmutableCollection<DaUserType> userTypes = schemaInfoLevel.isRetrieveUserDefinedColumnDataTypes()
                ? dbMetadataDialect.searchUserTypes(schema, conn)
                : Lists.immutable.<DaUserType>empty();

        return new DaCatalogImpl(database, schemaStrategy, sequences, userTypes, rules, ruleBindings,
                extraRoutines, constraintIndices, extraViewInfo, routineOverrideValue);
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } catch (SchemaCrawlerException e) {
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

From source file:com.che.software.testato.domain.dao.jdbc.impl.ElementDAO.java

/**
 * Element search from a bean of criterions.
 * /*from  w w  w . j a  va 2  s.c o m*/
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param searchBean the criterions to use for the search.
 * @return the resulting object list.
 * @since July, 2011.
 * @throws ElementSearchDAOException if an error occurs during the search.
 */
@Override
public List<Element> searchElement(ElementSearch searchBean) throws ElementSearchDAOException {
    LOGGER.debug("searchElement().");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        List<Object> params = new ArrayList<Object>();
        return getQueryRunner().query(connection, getElementSearchQueryFromCriterion(searchBean, params),
                new BeanListHandler<Element>(Element.class), params.toArray());
    } catch (SQLException e) {
        throw new ElementSearchDAOException(e);
    } finally {
        DbUtils.closeQuietly(connection);
    }
}

From source file:com.che.software.testato.domain.dao.jdbc.impl.ScriptDAO.java

/**
 * Script search from a bean of criterions.
 * //  w w w.j a  v  a  2  s .  c o  m
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param searchBean the criterions to use for the search.
 * @return the resulting object list.
 * @since July, 2011.
 * @throws ScriptSearchDAOException if an error occurs during the search.
 */
@Override
public List<Script> searchScript(ScriptSearch searchBean) throws ScriptSearchDAOException {
    LOGGER.debug("searchScript().");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        List<Object> params = new ArrayList<Object>();
        return getQueryRunner().query(connection, getScriptSearchQueryFromCriterion(searchBean, params),
                new BeanListHandler<Script>(Script.class), params.toArray());
    } catch (SQLException e) {
        throw new ScriptSearchDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

From source file:com.mirth.connect.server.userutil.DatabaseConnection.java

/**
 * Executes an INSERT/UPDATE statement on the database and returns a CachedRowSet containing any
 * generated keys./*from   ww  w .j a v a  2  s.co m*/
 * 
 * @param expression
 *            The statement to be executed.
 * @return A CachedRowSet containing any generated keys.
 * @throws SQLException
 */
public CachedRowSet executeUpdateAndGetGeneratedKeys(String expression) throws SQLException {
    Statement statement = null;

    try {
        statement = connection.createStatement();
        logger.debug("executing update:\n" + expression);
        statement.executeUpdate(expression, Statement.RETURN_GENERATED_KEYS);
        CachedRowSet crs = new MirthCachedRowSet();
        crs.populate(statement.getGeneratedKeys());
        return crs;
    } catch (SQLException e) {
        throw e;
    } finally {
        DbUtils.closeQuietly(statement);
    }
}

From source file:azkaban.project.JdbcProjectLoader.java

/**
 * Creates a Project in the db.//  w w w  .j  a v  a  2s  . c om
 *
 * It will throw an exception if it finds an active project of the same name,
 * or the SQL fails
 */
@Override
public Project createNewProject(String name, String description, User creator) throws ProjectManagerException {
    Connection connection = getConnection();

    Project project;
    try {
        // No need to commit, since createNewProject should commit.
        project = createNewProject(connection, name, description, creator);
    } finally {
        DbUtils.closeQuietly(connection);
    }

    return project;
}

From source file:dbutils.DbUtilsTemplate.java

/**
 * ??Map?Map?List//  ww w .j a v  a2 s.co  m
 *
 * @param sql    sql?
 * @param params ?
 * @return 
 */
public List<Map<String, Object>> find(String sql, Object[] params) {
    queryRunner = new QueryRunner();
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    Connection conn = null;
    try {
        conn = dataSource.getConnection();
        if (params == null) {
            list = queryRunner.query(conn, sql, new MapListHandler());
        } else {
            list = queryRunner.query(conn, sql, new MapListHandler(), params);
        }
    } catch (SQLException e) {
        LOG.error("Error occured while attempting to query data", e);
    } finally {
        if (conn != null) {
            DbUtils.closeQuietly(conn);
        }
    }
    return list;
}

From source file:com.che.software.testato.domain.dao.jdbc.impl.ActionDAO.java

/**
 * Action search from a bean of criterions.
 * /*from w ww.j av a 2 s  .  c o m*/
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @param searchBean the criterions to use for the search.
 * @return the resulting object list.
 * @since July, 2011.
 * @throws ActionSearchDAOException if an error occurs during the search.
 */
@Override
public List<Action> searchAction(ActionSearch searchBean) throws ActionSearchDAOException {
    LOGGER.debug("searchAction().");
    Connection connection = null;
    try {
        connection = getDataSource().getConnection();
        List<Object> params = new ArrayList<Object>();
        return getQueryRunner().query(connection, getActionSearchQueryFromCriterion(searchBean, params),
                new BeanListHandler<Action>(Action.class), params.toArray());
    } catch (SQLException e) {
        throw new ActionSearchDAOException(e);
    } finally {
        if (null != connection) {
            DbUtils.closeQuietly(connection);
        }
    }
}

From source file:com.splicemachine.derby.utils.SpliceAdminIT.java

@Test
@Category(SlowTest.class)
public void testGetSchemaInfoSplit() throws Exception {
    int size = 100;
    String TABLE_NAME = "SPLIT";
    SpliceUnitTest.MyWatcher tableWatcher = new SpliceUnitTest.MyWatcher(TABLE_NAME, CLASS_NAME,
            "(username varchar(40) unique not null,i int)");
    tableDAO.drop(CLASS_NAME, TABLE_NAME);
    tableWatcher.create(Description.createSuiteDescription(CLASS_NAME, "testGetSchemaInfoSplit"));
    try {/*  w w w .  j av  a 2 s .  co m*/
        PreparedStatement ps = spliceClassWatcher
                .prepareStatement(String.format("insert into %s.%s values (?,?)", CLASS_NAME, TABLE_NAME));
        for (int i = 0; i < size; i++) {
            ps.setInt(1, i);
            ps.setString(2, Integer.toString(i + 1));
            ps.executeUpdate();
        }
        //            spliceClassWatcher.splitTable(TABLE_NAME,CLASS_NAME,size/3);
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        spliceClassWatcher.closeAll();
    }

    CallableStatement cs = methodWatcher.prepareCall("call SYSCS_UTIL.SYSCS_GET_SCHEMA_INFO()");
    ResultSet rs = cs.executeQuery();
    TestUtils.FormattedResult fr = TestUtils.FormattedResult.ResultFactory
            .convert("call SYSCS_UTIL.SYSCS_GET_SCHEMA_INFO()", rs);
    System.out.println(fr.toString());
    DbUtils.closeQuietly(rs);
}