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

Example 26 with DBUtils

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

the class NotificationModel method getServiceName.

protected static String getServiceName(Connection conn, Integer id) {
    if (id == null) {
        return null;
    }
    String serviceName = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    final DBUtils d = new DBUtils(NotificationModel.class);
    try {
        ps = conn.prepareStatement("SELECT servicename from service where serviceid = ?");
        d.watch(ps);
        ps.setInt(1, id);
        rs = ps.executeQuery();
        d.watch(rs);
        if (rs.next()) {
            serviceName = rs.getString("servicename");
        }
    } catch (SQLException e) {
        LOG.warn("unable to get service name for service ID '{}'", id, e);
    } finally {
        d.cleanUp();
    }
    return serviceName;
}

Example 27 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 {
        StringBuffer select = new StringBuffer("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 28 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 29 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 {
        StringBuffer select = new StringBuffer("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 30 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;
}