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

Example 31 with DBUtils

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

the class NotificationModel method allNotifications.

/**
 * Return all notifications, both outstanding and acknowledged.
 *
 * @param order a {@link java.lang.String} object.
 * @return an array of {@link org.opennms.web.notification.Notification} objects.
 * @throws java.sql.SQLException if any.
 */
public Notification[] allNotifications(String order) throws SQLException {
    Notification[] notices = null;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(getClass(), conn);
    try {
        final Statement stmt = conn.createStatement();
        d.watch(stmt);
        // oh man this is lame, but it'll be a DAO soon right?  right?  :P
        String query = SELECT;
        if (order != null) {
            if (order.equalsIgnoreCase("asc")) {
                query += " ORDER BY pagetime ASC";
            } else if (order.equalsIgnoreCase("desc")) {
                query += " ORDER BY pagetime DESC";
            }
        }
        query += ";";
        final ResultSet rs = stmt.executeQuery(query);
        d.watch(rs);
        notices = rs2NotifyBean(conn, rs);
    } catch (SQLException e) {
        LOG.error("allNotifications: Problem getting data from the notifications table: {}", e, e);
        throw e;
    } finally {
        d.cleanUp();
    }
    return (notices);
}

Example 32 with DBUtils

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

the class OutageFactory method getOutages.

/**
 * Return all notices (optionally only unacknowledged notices) sorted by the
 * given sort style.
 *
 * <p>
 * <strong>Note: </strong> This limit/offset code is <em>Postgres
 * specific!</em>
 * Per <a href="mailto:shaneo@opennms.org">Shane </a>, this is okay for now
 * until we can come up with an Oracle alternative too.
 * </p>
 *
 * @param limit
 *            if -1 or zero, no limit or offset is used
 * @param offset
 *            if -1, no limit or offset if used
 * @param sortStyle a {@link org.opennms.web.outage.SortStyle} object.
 * @param outType a {@link org.opennms.web.outage.OutageType} object.
 * @param filters an array of org$opennms$web$filter$Filter objects.
 * @return an array of {@link org.opennms.web.outage.Outage} objects.
 * @throws java.sql.SQLException if any.
 */
public static Outage[] getOutages(SortStyle sortStyle, OutageType outType, Filter[] filters, int limit, int offset) throws SQLException {
    if (sortStyle == null || outType == null || filters == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    boolean useLimits = false;
    if (limit > 0 && offset > -1) {
        useLimits = true;
    }
    Outage[] outages = null;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);
    try {
        final StringBuilder select = new StringBuilder("SELECT OUTAGES.*, NODE.NODELABEL, NODE.LOCATION, IPINTERFACE.IPHOSTNAME, SERVICE.SERVICENAME, NOTIFICATIONS.NOTIFYID, NOTIFICATIONS.ANSWEREDBY FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID " + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.EVENTID " + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') " + "AND ");
        select.append(outType.getClause());
        for (Filter filter : filters) {
            select.append(" AND ");
            select.append(filter.getParamSql());
        }
        select.append(sortStyle.getOrderByClause());
        if (useLimits) {
            select.append(" LIMIT ");
            select.append(limit);
            select.append(" OFFSET ");
            select.append(offset);
        }
        LOG.debug(select.toString());
        final PreparedStatement stmt = conn.prepareStatement(select.toString());
        d.watch(stmt);
        int parameterIndex = 1;
        for (Filter filter : filters) {
            parameterIndex += filter.bindParam(stmt, parameterIndex);
        }
        final ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        outages = rs2Outages(rs);
    } finally {
        d.cleanUp();
    }
    return outages;
}

Example 33 with DBUtils

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

the class OutageFactory method getOutageCount.

/**
 * Return the count of current outages.
 *
 * <p>
 * Note: This method has been optimized for the simplest query.
 * </p>
 *
 * @return a int.
 * @throws java.sql.SQLException if any.
 */
public static int getOutageCount() throws SQLException {
    int outageCount = 0;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);
    try {
        final Statement stmt = conn.createStatement();
        d.watch(stmt);
        final ResultSet rs = stmt.executeQuery("SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "WHERE IFREGAINEDSERVICE IS NULL " + "AND (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') ");
        d.watch(rs);
        if (rs.next()) {
            outageCount = rs.getInt("OUTAGECOUNT");
        }
    } finally {
        d.cleanUp();
    }
    return outageCount;
}

Example 34 with DBUtils

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

the class OutageFactory method getOutageCount.

/**
 * Count the number of outages for a given outage type.
 *
 * @param outageType a {@link org.opennms.web.outage.OutageType} object.
 * @param filters an array of org$opennms$web$filter$Filter objects.
 * @return a int.
 * @throws java.sql.SQLException if any.
 */
public static int getOutageCount(OutageType outageType, Filter[] filters) throws SQLException {
    if (outageType == null || filters == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    int outageCount = 0;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(OutageFactory.class, conn);
    try {
        final StringBuilder select = new StringBuilder("SELECT COUNT(OUTAGEID) AS OUTAGECOUNT FROM OUTAGES " + "JOIN NODE USING(NODEID) " + "JOIN IPINTERFACE ON OUTAGES.NODEID=IPINTERFACE.NODEID AND OUTAGES.IPADDR=IPINTERFACE.IPADDR " + "JOIN IFSERVICES ON OUTAGES.NODEID=IFSERVICES.NODEID AND OUTAGES.IPADDR=IFSERVICES.IPADDR AND OUTAGES.SERVICEID=IFSERVICES.SERVICEID " + "LEFT OUTER JOIN SERVICE ON OUTAGES.SERVICEID=SERVICE.SERVICEID " + "LEFT OUTER JOIN NOTIFICATIONS ON SVCLOSTEVENTID=NOTIFICATIONS.NOTIFYID " + "WHERE (NODE.NODETYPE != 'D' AND IPINTERFACE.ISMANAGED != 'D' AND IFSERVICES.STATUS != 'D') " + "AND ");
        select.append(outageType.getClause());
        for (Filter filter : filters) {
            select.append(" AND ");
            select.append(filter.getParamSql());
        }
        final PreparedStatement stmt = conn.prepareStatement(select.toString());
        d.watch(stmt);
        int parameterIndex = 1;
        for (Filter filter : filters) {
            parameterIndex += filter.bindParam(stmt, parameterIndex);
        }
        final ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        if (rs.next()) {
            outageCount = rs.getInt("OUTAGECOUNT");
        }
    } finally {
        d.cleanUp();
    }
    return outageCount;
}

Example 35 with DBUtils

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

the class OutageModel method getCurrentOutagesForNode.

/**
 * <p>getCurrentOutagesForNode</p>
 *
 * @param nodeId a int.
 * @return an array of {@link org.opennms.web.outage.Outage} objects.
 * @throws java.sql.SQLException if any.
 */
public static Outage[] getCurrentOutagesForNode(int nodeId) throws SQLException {
    Outage[] outages = new Outage[0];
    final DBUtils d = new DBUtils(OutageModel.class);
    try {
        Connection conn = DataSourceFactory.getInstance().getConnection();
        d.watch(conn);
        PreparedStatement stmt = conn.prepareStatement("" + "SELECT DISTINCT \n" + "         outages.outageid, outages.iflostservice, outages.ifregainedservice, node.nodeID, \n" + "         node.nodeLabel, \n" + "         node.location, \n" + "         ipinterface.ipaddr, \n" + "         ipinterface.iphostname, \n" + "         service.servicename, \n" + "         ifservices.serviceId \n" + "    from outages \n" + "    join ifservices \n" + "      on ifservices.id = outages.ifserviceid \n" + "    join ipinterface \n" + "      on ipinterface.id = ifservices.ipinterfaceid \n" + "    join node \n" + "       on node.nodeid = ipinterface.nodeid \n" + "    join service \n" + "      on ifservices.serviceid = service.serviceid \n" + "   where node.nodeid = ? \n" + "     and outages.ifregainedservice is null \n" + "     and outages.suppresstime is null \n" + "      or outages.suppresstime < now() \n" + "order by iflostservice desc");
        d.watch(stmt);
        stmt.setInt(1, nodeId);
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        outages = rs2Outages(rs, false);
    } finally {
        d.cleanUp();
    }
    return outages;
}