Examples with DBUtils org.opennms.core.utils.DBUtils used on opensource projects

Example 1 with DBUtils

use of org.opennms.core.utils.DBUtils in project opennms by OpenNMS.

the class DeleteNodesServlet method getIpAddrsForNode.

private List<String> getIpAddrsForNode(Integer nodeId) throws ServletException {
    List<String> ipAddrs = new ArrayList<String>();
    final DBUtils d = new DBUtils(getClass());
    try {
        Connection conn = DataSourceFactory.getInstance().getConnection();
        d.watch(conn);
        PreparedStatement stmt = conn.prepareStatement("SELECT DISTINCT ipaddr FROM ipinterface WHERE nodeid=?");
        d.watch(stmt);
        stmt.setInt(1, nodeId);
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        while (rs.next()) {
            ipAddrs.add(rs.getString("ipaddr"));
        }
    } catch (SQLException e) {
        throw new ServletException("There was a problem with the database connection: " + e, e);
    } finally {
        d.cleanUp();
    }
    return ipAddrs;
}

Example 2 with DBUtils

use of org.opennms.core.utils.DBUtils in project opennms by OpenNMS.

the class GetNodesServlet method getAllNodes.

/**
     */
private List<ManagedInterface> getAllNodes(HttpSession userSession) throws SQLException {
    Connection connection = null;
    List<ManagedInterface> allNodes = new ArrayList<ManagedInterface>();
    int lineCount = 0;
    final DBUtils d = new DBUtils(getClass());
    try {
        connection = DataSourceFactory.getInstance().getConnection();
        d.watch(connection);
        PreparedStatement ifaceStmt = connection.prepareStatement(INTERFACE_QUERY);
        d.watch(ifaceStmt);
        ResultSet ifaceResults = ifaceStmt.executeQuery();
        d.watch(ifaceResults);
        if (ifaceResults != null) {
            while (ifaceResults.next()) {
                lineCount++;
                ManagedInterface newInterface = new ManagedInterface();
                allNodes.add(newInterface);
                newInterface.setNodeid(ifaceResults.getInt(1));
                newInterface.setAddress(ifaceResults.getString(2));
                newInterface.setStatus(ifaceResults.getString(3));
                PreparedStatement svcStmt = connection.prepareStatement(SERVICE_QUERY);
                d.watch(svcStmt);
                svcStmt.setInt(1, newInterface.getNodeid());
                svcStmt.setString(2, newInterface.getAddress());
                ResultSet svcResults = svcStmt.executeQuery();
                d.watch(svcResults);
                if (svcResults != null) {
                    while (svcResults.next()) {
                        lineCount++;
                        ManagedService newService = new ManagedService();
                        newService.setId(svcResults.getInt(1));
                        newService.setName(svcResults.getString(2));
                        newService.setStatus(svcResults.getString(3));
                        newInterface.addService(newService);
                    }
                }
            }
        }
        userSession.setAttribute("lineItems.manage.jsp", Integer.valueOf(lineCount));
    } finally {
        d.cleanUp();
    }
    return allNodes;
}

Example 3 with DBUtils

use of org.opennms.core.utils.DBUtils in project opennms by OpenNMS.

the class JdbcFilterDao method getNodeMap.

/**
     * {@inheritDoc}
     *
     * This method returns a map of all nodeids and nodelabels that match
     * the rule that is passed in, sorted by nodeid.
     * @exception FilterParseException
     *                if a rule is syntactically incorrect or failed in
     *                executing the SQL statement
     */
@Override
public SortedMap<Integer, String> getNodeMap(final String rule) throws FilterParseException {
    final SortedMap<Integer, String> resultMap = new TreeMap<Integer, String>();
    String sqlString;
    LOG.debug("Filter.getNodeMap({})", rule);
    // get the database connection
    Connection conn = null;
    final DBUtils d = new DBUtils(getClass());
    try {
        conn = getDataSource().getConnection();
        d.watch(conn);
        // parse the rule and get the sql select statement
        sqlString = getNodeMappingStatement(rule);
        LOG.debug("Filter.getNodeMap({}): SQL statement: {}", rule, sqlString);
        // execute query
        final Statement stmt = conn.createStatement();
        d.watch(stmt);
        final ResultSet rset = stmt.executeQuery(sqlString);
        d.watch(rset);
        if (rset != null) {
            // Iterate through the result and build the map
            while (rset.next()) {
                resultMap.put(Integer.valueOf(rset.getInt(1)), rset.getString(2));
            }
        }
    } catch (final FilterParseException e) {
        LOG.warn("Filter Parse Exception occurred getting node map.", e);
        throw new FilterParseException("Filter Parse Exception occurred getting node map: " + e.getLocalizedMessage(), e);
    } catch (final SQLException e) {
        LOG.warn("SQL Exception occurred getting node map.", e);
        throw new FilterParseException("SQL Exception occurred getting node map: " + e.getLocalizedMessage(), e);
    } catch (final Throwable e) {
        LOG.error("Exception getting database connection.", e);
        throw new UndeclaredThrowableException(e);
    } finally {
        d.cleanUp();
    }
    return Collections.unmodifiableSortedMap(resultMap);
}

Example 4 with DBUtils

use of org.opennms.core.utils.DBUtils in project opennms by OpenNMS.

the class JdbcFilterDao method getIPAddressServiceMap.

/** {@inheritDoc} */
@Override
public Map<InetAddress, Set<String>> getIPAddressServiceMap(final String rule) throws FilterParseException {
    final Map<InetAddress, Set<String>> ipServices = new TreeMap<InetAddress, Set<String>>(new InetAddressComparator());
    String sqlString;
    LOG.debug("Filter.getIPAddressServiceMap({})", rule);
    // get the database connection
    Connection conn = null;
    final DBUtils d = new DBUtils(getClass());
    try {
        conn = getDataSource().getConnection();
        d.watch(conn);
        // parse the rule and get the sql select statement
        sqlString = getIPServiceMappingStatement(rule);
        LOG.debug("Filter.getIPAddressServiceMap({}): SQL statement: {}", rule, sqlString);
        // execute query
        final Statement stmt = conn.createStatement();
        d.watch(stmt);
        final ResultSet rset = stmt.executeQuery(sqlString);
        d.watch(rset);
        // fill up the array list if the result set has values
        if (rset != null) {
            // Iterate through the result and build the array list
            while (rset.next()) {
                final InetAddress ipaddr = addr(rset.getString(1));
                if (ipaddr != null) {
                    if (!ipServices.containsKey(ipaddr)) {
                        ipServices.put(ipaddr, new TreeSet<String>());
                    }
                    ipServices.get(ipaddr).add(rset.getString(2));
                }
            }
        }
    } catch (final FilterParseException e) {
        LOG.warn("Filter Parse Exception occurred getting IP Service List.", e);
        throw new FilterParseException("Filter Parse Exception occurred getting IP Service List: " + e.getLocalizedMessage(), e);
    } catch (final SQLException e) {
        LOG.warn("SQL Exception occurred getting IP Service List.", e);
        throw new FilterParseException("SQL Exception occurred getting IP Service List: " + e.getLocalizedMessage(), e);
    } catch (final RuntimeException e) {
        LOG.error("Unexpected exception getting database connection.", e);
        throw e;
    } catch (final Error e) {
        LOG.error("Unexpected exception getting database connection.", e);
        throw e;
    } finally {
        d.cleanUp();
    }
    return ipServices;
}

Example 5 with DBUtils

use of org.opennms.core.utils.DBUtils in project opennms by OpenNMS.

the class CategoryModel method getServiceAvailability.

/**
     * Return the availability percentage for a managed service from the given
     * start time until the given end time. If the service is not managed, then
     * a value of -1.0 is returned.
     * 
     * @param nodeId a int.
     * @param ipAddr a {@link java.lang.String} object.
     * @param serviceId a int.
     * @param start a {@link java.util.Date} object.
     * @param end a {@link java.util.Date} object.
     * @return a double.
     * @throws java.sql.SQLException if any.
     */
static double getServiceAvailability(int nodeId, String ipAddr, int serviceId, Date start, Date end) throws SQLException {
    if (ipAddr == null || start == null || end == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    if (end.before(start)) {
        throw new IllegalArgumentException("Cannot have an end time before the start time.");
    }
    if (end.equals(start)) {
        throw new IllegalArgumentException("Cannot have an end time equal to the start time.");
    }
    final DBUtils d = new DBUtils(CategoryModel.class);
    try {
        Connection conn = DataSourceFactory.getInstance().getConnection();
        d.watch(conn);
        PreparedStatement stmt = conn.prepareStatement("select getPercentAvailabilityInWindow(ifservices.id, ?, ?) as avail from ifservices, ipinterface, node where ifservices.ipInterfaceId = ipinterface.id and ipInterface.nodeid = node.nodeid and ifservices.status='A' and ipinterface.ismanaged='M' and node.nodetype='A' and node.nodeid=? and ipInterface.ipaddr=? and ifServices.serviceid=?");
        d.watch(stmt);
        // yes, these are supposed to be backwards, the end time first
        stmt.setTimestamp(1, new Timestamp(end.getTime()));
        stmt.setTimestamp(2, new Timestamp(start.getTime()));
        stmt.setInt(3, nodeId);
        stmt.setString(4, ipAddr);
        stmt.setInt(5, serviceId);
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        if (rs.next()) {
            return rs.getDouble("avail");
        }
    } catch (final SQLException e) {
        LOG.warn("Failed to get service availability for nodeId {}, interface {}, serviceId {}", nodeId, ipAddr, serviceId, e);
    } finally {
        d.cleanUp();
    }
    return -1.0;
}