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

Example 71 with DBUtils

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

the class EventFactory method getEvent.

/**
 * Return a specific event.
 *
 * @param eventId a int.
 * @return a {@link org.opennms.web.event.Event} object.
 * @throws java.sql.SQLException if any.
 */
public static Event getEvent(int eventId) throws SQLException {
    Event event = null;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(EventFactory.class, conn);
    try {
        PreparedStatement stmt = conn.prepareStatement("SELECT events.*, monitoringsystems.id AS systemId, monitoringsystems.label AS systemLabel, monitoringsystems.location AS location, node.nodeLabel, service.serviceName FROM events LEFT OUTER JOIN monitoringsystems ON events.systemId=monitoringsystems.id LEFT OUTER JOIN node USING (nodeId) LEFT OUTER JOIN service USING (serviceId) WHERE eventId=? ");
        d.watch(stmt);
        stmt.setInt(1, eventId);
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        Event[] events = rs2Events(rs);
        // what do I do if this actually returns more than one service?
        if (events.length > 0) {
            event = events[0];
        }
    } finally {
        d.cleanUp();
    }
    return event;
}

Example 72 with DBUtils

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

the class EventFactory method getEvents.

/**
 * Return all events (optionally only unacknowledged events) 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.event.SortStyle} object.
 * @param ackType a {@link org.opennms.web.event.AcknowledgeType} object.
 * @param filters an array of org$opennms$web$filter$Filter objects.
 * @return an array of {@link org.opennms.web.event.Event} objects.
 * @throws java.sql.SQLException if any.
 */
public static Event[] getEvents(SortStyle sortStyle, AcknowledgeType ackType, Filter[] filters, int limit, int offset) throws SQLException {
    if (sortStyle == null || ackType == null || filters == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    boolean useLimits = false;
    if (limit > 0 && offset > -1) {
        useLimits = true;
    }
    Event[] events = null;
    final Connection conn = DataSourceFactory.getInstance().getConnection();
    final DBUtils d = new DBUtils(EventFactory.class, conn);
    try {
        final StringBuilder select = new StringBuilder("" + "          SELECT events.*, node.nodelabel, service.servicename, " + "                 monitoringsystems.id AS systemId, " + "                 monitoringsystems.label AS systemLabel, " + "                 monitoringsystems.location AS location " + "            FROM node " + "RIGHT OUTER JOIN events " + "              ON (events.nodeid = node.nodeid) " + " LEFT OUTER JOIN monitoringsystems " + "              ON (events.systemid = monitoringsystems.id) " + " LEFT OUTER JOIN service " + "              ON (service.serviceid = events.serviceid) " + "           WHERE ");
        select.append(getAcknowledgeTypeClause(ackType));
        for (Filter filter : filters) {
            select.append(" AND");
            select.append(filter.getParamSql());
        }
        select.append(" AND EVENTDISPLAY='Y' ");
        select.append(getOrderByClause(sortStyle));
        if (useLimits) {
            select.append(" LIMIT ");
            select.append(limit);
            select.append(" OFFSET ");
            select.append(offset);
        }
        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);
        events = rs2Events(rs);
    } finally {
        d.cleanUp();
    }
    return events;
}

Example 73 with DBUtils

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

the class NoticeFactory method getNotices.

/**
 * 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.notification.SortStyle} object.
 * @param ackType a {@link org.opennms.web.notification.AcknowledgeType} object.
 * @param filters an array of org$opennms$web$filter$Filter objects.
 * @return an array of {@link org.opennms.web.notification.Notification} objects.
 * @throws java.sql.SQLException if any.
 */
public static Notification[] getNotices(SortStyle sortStyle, AcknowledgeType ackType, org.opennms.web.filter.Filter[] filters, int limit, int offset, ServletContext servletContext) throws SQLException {
    if (sortStyle == null || ackType == null || filters == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    boolean useLimits = false;
    if (limit > 0 && offset > -1) {
        useLimits = true;
    }
    Notification[] notices = null;
    final DBUtils d = new DBUtils(NoticeFactory.class);
    try {
        Connection conn = DataSourceFactory.getInstance().getConnection();
        d.watch(conn);
        final StringBuilder select = new StringBuilder("SELECT * FROM NOTIFICATIONS WHERE");
        select.append(ackType.getAcknowledgeTypeClause());
        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);
        }
        PreparedStatement stmt = conn.prepareStatement(select.toString());
        d.watch(stmt);
        int parameterIndex = 1;
        for (Filter filter : filters) {
            parameterIndex += filter.bindParam(stmt, parameterIndex);
        }
        if (useLimits) {
            stmt.setInt(parameterIndex++, limit);
            stmt.setInt(parameterIndex, offset);
        }
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        // PreparedStatement ps = conn.prepareStatement(select.toString());
        notices = rs2Notices(rs, servletContext);
    } finally {
        d.cleanUp();
    }
    return notices;
}

Example 74 with DBUtils

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

the class NoticeFactory method getNoticesForInterface.

/**
 * Return all notices (optionally only unacknowledged notices) sorted by id
 * that have the given IP address, regardless of what node they belong to.
 *
 * @param ipAddress a {@link java.lang.String} object.
 * @param includeAcknowledged a boolean.
 * @return an array of {@link org.opennms.web.notification.Notification} objects.
 * @throws java.sql.SQLException if any.
 */
public static Notification[] getNoticesForInterface(String ipAddress, boolean includeAcknowledged, ServletContext servletContext) throws SQLException {
    if (ipAddress == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    Notification[] notices = null;
    DBUtils d = new DBUtils(NoticeFactory.class);
    try {
        Connection conn = DataSourceFactory.getInstance().getConnection();
        d.watch(conn);
        final StringBuilder select = new StringBuilder("SELECT * FROM NOTIFICATIONS WHERE INTERFACEID=?");
        if (!includeAcknowledged) {
            select.append(" AND RESPONDTIME IS NULL");
        }
        select.append(" ORDER BY NOTIFYID DESC");
        PreparedStatement stmt = conn.prepareStatement(select.toString());
        d.watch(stmt);
        stmt.setString(1, ipAddress);
        ResultSet rs = stmt.executeQuery();
        d.watch(rs);
        notices = rs2Notices(rs, servletContext);
    } finally {
        d.cleanUp();
    }
    return notices;
}

Example 75 with DBUtils

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

the class NoticeFactory method acknowledge.

/**
 * Acknowledge a list of notices with the given username and the given time.
 *
 * @param noticeIds an array of int.
 * @param user a {@link java.lang.String} object.
 * @param time a java$util$Date object.
 * @throws java.sql.SQLException if any.
 */
public static void acknowledge(int[] noticeIds, String user, Date time) throws SQLException {
    if (noticeIds == null || user == null || time == null) {
        throw new IllegalArgumentException("Cannot take null parameters.");
    }
    if (noticeIds.length > 0) {
        final StringBuilder update = new StringBuilder("UPDATE NOTIFICATIONS SET RESPONDTIME=?, ANSWEREDBY=?");
        update.append(" WHERE NOTIFYID IN (");
        update.append(noticeIds[0]);
        for (int i = 1; i < noticeIds.length; i++) {
            update.append(",");
            update.append(noticeIds[i]);
        }
        update.append(")");
        update.append(" AND RESPONDTIME IS NULL");
        DBUtils d = new DBUtils(NoticeFactory.class);
        try {
            Connection conn = DataSourceFactory.getInstance().getConnection();
            d.watch(conn);
            PreparedStatement stmt = conn.prepareStatement(update.toString());
            d.watch(stmt);
            stmt.setTimestamp(1, new Timestamp(time.getTime()));
            stmt.setString(2, user);
            stmt.executeUpdate();
        } finally {
            d.cleanUp();
        }
    }
}