Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. executeQuery

Introduction

In this page you can find the example usage for java.sql CallableStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get AlarmMetricAlarm by alarm name or metricnamespace and/or metricname
 * /*from  www.ja  va2  s  .  co  m*/
 * @param alarmName
 * @param metricNamespace
 * @param metricName
 * @return
 */
@Override
public ArrayList<AlarmMetricAlarm> getMetricAlarmByMetricNamespace(String metricNamespace, String metricName) {
    ArrayList<AlarmMetricAlarm> alarmMetricAlarmList = new ArrayList<AlarmMetricAlarm>();

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricAlarm_GetByMetricNamespace(?,?)");

        stmt.setString(1, metricNamespace);
        stmt.setString(2, metricName);

        rs = stmt.executeQuery();

        while (rs.next()) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            AlarmComparisonOperator alarmComparisonOperator = AlarmComparisonOperator.None;
            MetricStatistic metricStatistic = MetricStatistic.None;
            MetricUnit metricUnit = MetricUnit.None;
            AlarmState alarmState = AlarmState.NONE;

            AlarmMetricAlarm metricAlarm = new AlarmMetricAlarm();
            metricAlarm.setAlarmMetricAlarmId(rs.getInt("MetricAlarmId"));
            metricAlarm.setComparisonOperator(
                    alarmComparisonOperator.findByValue(rs.getInt("AlarmComparisonOperatorId")));
            metricAlarm.setMetricSatistic(metricStatistic.findByValue(rs.getInt("MetricStatisticId")));
            metricAlarm.setMetricUnit(metricUnit.findByValue(rs.getInt("MetricUnitId")));
            metricAlarm.setAlarmName(rs.getString("Name"));
            metricAlarm.setAlarmDescription(rs.getString("Description"));
            metricAlarm.setMetricNamespace(rs.getString("MetricNamespace"));
            metricAlarm.setMetricName(rs.getString("MetricName"));
            metricAlarm.setThreshold(rs.getDouble("Threshold"));
            metricAlarm.setStateReason(rs.getString("StateReason"));
            metricAlarm.setStateReasonData(rs.getString("StateReasonData"));
            metricAlarm.setStateValue(alarmState.findByValue(rs.getInt("AlarmStateId")));
            metricAlarm.setDateModified(sdf.parse(rs.getString("DateModified")));
            metricAlarm.setDateCreated(sdf.parse(rs.getString("DateCreated")));
            alarmMetricAlarmList.add(metricAlarm);
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }
    return alarmMetricAlarmList;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * List all the metrics for a namespace// ww  w.  ja  v  a  2  s .c o  m
 * 
 * @param metricNamespace
 * @return ArrayList<HashMap<String, String>>
 */
@Override
public ArrayList<HashMap<String, String>> getMetrics(String metricNamespace) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    ArrayList<HashMap<String, String>> retData = new ArrayList<HashMap<String, String>>();

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricData_GetMetrics(?)");
        stmt.setString(1, metricNamespace);
        rs = stmt.executeQuery();

        while (rs.next()) {
            // Create a new container
            HashMap<String, String> metricNameContainer = new HashMap<String, String>();

            metricNameContainer.put("metricnamespace", rs.getString("MetricNamespace"));
            metricNameContainer.put("metricname", rs.getString("MetricName"));

            // Add the name
            retData.add(metricNameContainer);
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }
    return retData;
}

From source file:weave.utils.SQLUtils.java

/**
 * @param conn An existing SQL Connection
 * @param selectColumns The list of columns in the SELECT statement.
 * @param fromSchema The schema containing the table to perform the SELECT statement on.
 * @param fromTable The table to perform the SELECT statement on.
 * @param whereParams A map of column names to String values used to construct a WHERE clause.
 * @return The resulting rows returned by the query.
 * @throws SQLException If the query fails.
 *//*from  w w w. j  ava  2 s .co m*/
public static SQLResult getRowSetFromQuery(Connection conn, List<String> selectColumns, String fromSchema,
        String fromTable, Map<String, String> whereParams) throws SQLException {
    DebugTimer t = new DebugTimer();
    CallableStatement cstmt = null;
    ResultSet rs = null;
    SQLResult result = null;
    String query = null;
    try {
        // create list of columns to use in SELECT statement
        String columnQuery = "";
        for (int i = 0; selectColumns != null && i < selectColumns.size(); i++) {
            if (i > 0)
                columnQuery += ",";
            columnQuery += quoteSymbol(conn, selectColumns.get(i));
        }
        if (columnQuery.length() == 0)
            columnQuery = "*"; // select all columns

        // build WHERE clause
        String whereQuery = "";
        int i = 0;
        Iterator<Entry<String, String>> paramsIter = whereParams.entrySet().iterator();
        while (paramsIter.hasNext()) {
            Entry<String, String> pair = paramsIter.next();
            String key = pair.getKey();
            if (i > 0)
                whereQuery += " AND ";
            whereQuery += caseSensitiveCompare(conn, quoteSymbol(conn, key), "?"); // case-sensitive
            i++;
        }
        if (whereQuery.length() > 0)
            whereQuery = "WHERE " + whereQuery;

        // build complete query
        query = String.format("SELECT %s FROM %s %s", columnQuery,
                quoteSchemaTable(conn, fromSchema, fromTable), whereQuery);
        cstmt = conn.prepareCall(query);

        // set query parameters
        i = 1;
        paramsIter = whereParams.entrySet().iterator();
        while (paramsIter.hasNext()) {
            Map.Entry<String, String> pairs = (Map.Entry<String, String>) paramsIter.next();
            String value = pairs.getValue();
            cstmt.setString(i, value);
            i++;
        }

        t.lap("prepare query");
        rs = cstmt.executeQuery();
        t.lap(query);

        // make a copy of the query result
        result = new SQLResult(rs);
        t.lap("cache row set");
    } catch (SQLException e) {
        System.out.println("Query: " + query);
        throw e;
    } finally {
        // close everything in reverse order
        SQLUtils.cleanup(rs);
        SQLUtils.cleanup(cstmt);
    }

    t.report();
    // return the copy of the query result
    return result;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get metric data object by id//from ww w  .j  a v  a  2 s  .c o m
 * 
 * @param metricDataId
 * @return MetricData
 */
@Override
public MetricData getMetricData(Integer metricDataId) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    MetricData dataitem = null;
    MetricUnit metricUnit = MetricUnit.None;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("dbo.uspMetricData_Get(?)");
        stmt.setInt(1, metricDataId);

        rs = stmt.executeQuery();

        while (rs.next()) {
            dataitem = new MetricData();
            dataitem.setMetricId(rs.getInt("MetricDataId"));
            dataitem.setUnit(metricUnit.findByValue(rs.getInt("MetricUnitId")));
            dataitem.setMetricNamespace(rs.getString("MetricNamespace"));
            dataitem.setMetricName(rs.getString("MetricName"));
            dataitem.setValue(rs.getDouble("Value"));
            dataitem.setData(rs.getString("Data"));
            dataitem.setTimestamp(sdf.parse(rs.getString("DateCreated")));
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return dataitem;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get list of MetricSLAExclusionTime(s) by MetricSlaId
 * /*from  w  w w  .j a  v  a  2  s. com*/
 * @param metricSlaId
 * @return ArrayList<MetricSLAExclusionTime>
 */
@Override
public ArrayList<MetricSLAExclusionTime> getMetricSlaExclusionTime(Integer metricSlaId) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    // create a container for the data
    ArrayList<MetricSLAExclusionTime> metricSLAExclusionTimeList = new ArrayList<MetricSLAExclusionTime>();

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricSlaExclusionTime_GetBySlaId(?)");
        stmt.setInt(1, metricSlaId);

        rs = stmt.executeQuery();

        while (rs.next()) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DayOfWeek dayOfWeek = DayOfWeek.Monday;

            MetricSLA metricSla = new MetricSLA();
            metricSla = getMetricSla(rs.getInt("MetricSlaId"), null);

            MetricSLAExclusionTime dataitem = new MetricSLAExclusionTime();
            dataitem.setMetricSLAExclusionTimeId(rs.getInt("MetricSLAExclusionTimeId"));
            dataitem.setMetricSla(metricSla);
            dataitem.setDayOfWeek(dayOfWeek.findByValue(rs.getInt("DayOfWeekId")));
            dataitem.setStartTime(rs.getString("StartTime"));
            dataitem.setEndTime(rs.getString("EndTime"));
            dataitem.setTimestamp(sdf.parse(rs.getString("DateCreated")));

            metricSLAExclusionTimeList.add(dataitem);
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return metricSLAExclusionTimeList;
}

From source file:com.mmnaseri.dragonfly.data.impl.DefaultDataAccess.java

@Override
public <E> List<?> call(Class<E> entityType, final String procedureName, Object... parameters) {
    if (isInBatchMode()) {
        throw new BatchOperationInterruptedByProcedureError();
    }/*  w ww .  j  a  va  2 s  .  c  o m*/
    log.info("Calling to stored procedure " + entityType.getCanonicalName() + "." + procedureName);
    final TableMetadata<E> tableMetadata = session.getTableMetadataRegistry().getTableMetadata(entityType);
    //noinspection unchecked
    final StoredProcedureMetadata procedureMetadata = with(tableMetadata.getProcedures())
            .keep(new Filter<StoredProcedureMetadata>() {
                @Override
                public boolean accepts(StoredProcedureMetadata item) {
                    return item.getName().equals(procedureName);
                }
            }).first();
    if (procedureMetadata == null) {
        throw new NoSuchProcedureError(entityType, procedureName);
    }
    if (procedureMetadata.getParameters().size() != parameters.length) {
        throw new MismatchedParametersNumberError(entityType, procedureName,
                procedureMetadata.getParameters().size(), parameters.length);
    }
    for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
        ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
        if (metadata.getParameterMode().equals(ParameterMode.IN)) {
            if (parameters[i] != null
                    && !ReflectionUtils.mapType(metadata.getParameterType()).isInstance(parameters[i])) {
                throw new MismatchedParameterTypeError(entityType, procedureName, i,
                        metadata.getParameterType(), parameters[i].getClass());
            }
        } else {
            if (parameters[i] == null) {
                throw new NullPointerException(metadata.getParameterMode() + " parameter cannot be null");
            }
            if (!(parameters[i] instanceof Reference<?>)) {
                throw new ReferenceParameterExpectedError(entityType, procedureName, i);
            }
        }
    }
    final ProcedureCallStatement statement = (ProcedureCallStatement) getStatement(entityType,
            "call." + procedureName, null, StatementType.CALL);
    final Map<String, Object> values = new HashMap<String, Object>();
    for (int i = 0; i < parameters.length; i++) {
        values.put("value.parameter" + i,
                parameters[i] instanceof Reference ? ((Reference<?>) parameters[i]).getValue() : parameters[i]);
    }
    final CallableStatement callableStatement;
    final ArrayList<Object> result = new ArrayList<Object>();
    try {
        callableStatement = openStatement(statement.prepare(openConnection(), null, values));
        for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
            final ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
            if (!metadata.getParameterMode().equals(ParameterMode.IN)) {
                callableStatement.registerOutParameter(i + 1, metadata.getType());
            }
        }
        if (procedureMetadata.getResultType().equals(void.class)) {
            callableStatement.executeUpdate();
        } else {
            final ResultSet resultSet = callableStatement.executeQuery();
            final EntityHandler<Object> entityHandler;
            if (procedureMetadata.isPartial()) {
                entityHandler = null;
            } else {
                //noinspection unchecked
                entityHandler = (EntityHandler<Object>) entityHandlerContext
                        .getHandler(procedureMetadata.getResultType());
            }
            while (resultSet.next()) {
                final Map<String, Object> map = rowHandler.handleRow(resultSet);
                if (procedureMetadata.isPartial()) {
                    try {
                        result.add(entityHandlerContext.fromMap(
                                beanInitializer.initialize(procedureMetadata.getResultType(), new Class[0]),
                                getPartialEntityMetadata(procedureMetadata.getResultType()), map));
                    } catch (BeanInstantiationException e) {
                        throw new EntityInitializationError(procedureMetadata.getResultType(), e);
                    }
                } else {
                    assert entityHandler != null;
                    Object instance = entityContext.getInstance(entityHandler.getEntityType());
                    instance = entityHandler.fromMap(instance, map);
                    if (entityHandler.hasKey() && entityHandler.getKey(instance) != null) {
                        final Serializable key = entityHandler.getKey(instance);
                        if (initializationContext.contains(entityHandler.getEntityType(), key)) {
                            instance = initializationContext.get(entityHandler.getEntityType(), key);
                            result.add(instance);
                            continue;
                        }
                    }
                    prepareEntity(instance, map);
                    result.add(instance);
                }
            }
            resultSet.close();
        }
        for (int i = 0; i < procedureMetadata.getParameters().size(); i++) {
            final ParameterMetadata metadata = procedureMetadata.getParameters().get(i);
            if (!metadata.getParameterMode().equals(ParameterMode.IN)) {
                //noinspection unchecked
                ((Reference) parameters[i]).setValue(callableStatement.getObject(i + 1));
            }
        }
        cleanUpStatement(callableStatement);
    } catch (SQLException e) {
        throw new ProcedureExecutionFailureError("Failed to call procedure " + procedureName, e);
    }
    return result;
}

From source file:com.mobiaware.auction.data.impl.MySqlDataServiceImpl.java

@Override
public double addFund(final Fund fund) {
    int uid = -1;

    Connection conn = null;/*from  ww  w  .ja v a2s . c om*/
    CallableStatement stmt = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_EDITFUND (?,?,?,?)}");
        stmt.registerOutParameter(1, Types.INTEGER);
        stmt.setInt(2, fund.getAuctionUid());
        stmt.setInt(3, fund.getUserUid());
        stmt.setDouble(4, fund.getBidPrice());

        stmt.execute();

        uid = stmt.getInt(1);
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, null);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "edit", uid });
    }

    double sum = 0.0;

    ResultSet rs = null;

    try {
        conn = _dataSource.getConnection();

        stmt = conn.prepareCall("{call SP_GETFUNDSUM (?)}");
        stmt.setInt(1, fund.getAuctionUid());

        rs = stmt.executeQuery();

        if (rs.next()) {
            sum = rs.getDouble(1);
        }
    } catch (SQLException e) {
        LOG.error(Throwables.getStackTraceAsString(e));
    } finally {
        DbUtils.closeQuietly(conn, stmt, rs);
    }

    if (LOG.isDebugEnabled()) {
        LOG.debug("FUND [method:{} result:{}]", new Object[] { "sum", sum });
    }

    return sum;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * List all metric sla's or by metric alarmid
 * /*from w  ww.j  av a2  s  . c  o  m*/
 * @param metricAlarmId
 * 
 * @return ArrayList<MetricSLA>
 */
@Override
public ArrayList<MetricSLA> getMetricSlas(Integer metricAlarmId) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    // create a container for the data
    ArrayList<MetricSLA> metricSlaList = new ArrayList<MetricSLA>();

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricSla_GetByMetricAlarmId(?)");

        if (metricAlarmId == null)
            stmt.setNull(1, Types.NULL);
        else
            stmt.setInt(1, metricAlarmId);

        rs = stmt.executeQuery();

        while (rs.next()) {
            AlarmMetricAlarm alarmMetricAlarm = new AlarmMetricAlarm();
            alarmMetricAlarm = getMetricAlarm(rs.getInt("MetricAlarmId"), null);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            MetricSLA dataitem = new MetricSLA();
            dataitem.setMetricSLAId(rs.getInt("MetricSlaId"));
            dataitem.setAlarmMetricAlarm(alarmMetricAlarm);
            dataitem.setSLAName(rs.getString("Name"));
            dataitem.setSLADescription(rs.getString("Description"));
            dataitem.setPercentage(rs.getDouble("Percentage"));
            dataitem.setDateModified(sdf.parse(rs.getString("DateModified")));
            dataitem.setDateCreated(sdf.parse(rs.getString("DateCreated")));
            metricSlaList.add(dataitem);
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return metricSlaList;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get metric sla by MetricSlaId/Sla Name
 * //from   w  w  w .j  ava2s . c o m
 * @param metricSlaId
 * @param slaName
 * @return MetricSLA
 */
@Override
public MetricSLA getMetricSla(Integer metricSlaId, String slaName) {
    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    // create a container for the data
    MetricSLA dataitem = null;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricSla_Get(?,?)");

        if (metricSlaId == null)
            stmt.setNull(1, Types.NULL);
        else
            stmt.setInt(1, metricSlaId);

        if (slaName == null)
            stmt.setNull(2, Types.NULL);
        else
            stmt.setString(2, slaName);

        rs = stmt.executeQuery();

        while (rs.next()) {
            AlarmMetricAlarm alarmMetricAlarm = new AlarmMetricAlarm();
            alarmMetricAlarm = getMetricAlarm(rs.getInt("MetricAlarmId"), null);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            dataitem = new MetricSLA();
            dataitem.setMetricSLAId(rs.getInt("MetricSlaId"));
            dataitem.setAlarmMetricAlarm(alarmMetricAlarm);
            dataitem.setSLAName(rs.getString("Name"));
            dataitem.setSLADescription(rs.getString("Description"));
            dataitem.setPercentage(rs.getDouble("Percentage"));
            dataitem.setDateModified(sdf.parse(rs.getString("DateModified")));
            dataitem.setDateCreated(sdf.parse(rs.getString("DateCreated")));
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }

    return dataitem;
}

From source file:shnakkydoodle.measuring.provider.MetricsProviderSQLServer.java

/**
 * Get the history for a MetricAlarm by an alarm name and dates
 * /* w w w.jav  a 2  s .  co  m*/
 * @param alarmName
 * @param startDate
 * @param endDate
 */
@Override
public ArrayList<AlarmHistoryItem> getMetricAlarmHistory(String alarmName, Date startDate, Date endDate) {
    ArrayList<AlarmHistoryItem> alarmHistoryItems = new ArrayList<AlarmHistoryItem>();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    Connection conn = null;
    CallableStatement stmt = null;
    ResultSet rs = null;

    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        conn = DriverManager.getConnection(this.host + ";user=" + this.username + ";password=" + this.password);

        stmt = conn.prepareCall("uspMetricAlarmHistoryItem_GetByAlarmName(?,?,?)");
        stmt.setString(1, alarmName);

        if (startDate != null)
            stmt.setString(2, sdf.format(startDate));
        else
            stmt.setString(2, null);

        if (endDate != null)
            stmt.setString(3, sdf.format(endDate));
        else
            stmt.setString(3, null);

        rs = stmt.executeQuery();

        while (rs.next()) {
            AlarmState alarmState = AlarmState.NONE;
            AlarmMetricAlarm alarmMetricAlarm = new AlarmMetricAlarm();
            alarmMetricAlarm = getMetricAlarm(rs.getInt("MetricAlarmId"), null);

            AlarmHistoryItem alarmHistoryItem = new AlarmHistoryItem();
            alarmHistoryItem.setAlarmHistoryItemId(rs.getInt("MetricAlarmHistoryItemId"));
            alarmHistoryItem.setAlarmMetricAlarm(alarmMetricAlarm);
            alarmHistoryItem.setStateReason(rs.getString("StateReason"));
            alarmHistoryItem.setStateReasonData(rs.getString("StateReasonData"));
            alarmHistoryItem.setAlarmState(alarmState.findByValue(rs.getInt("AlarmStateId")));
            alarmHistoryItem.setTimestamp(sdf.parse(rs.getString("DateCreated")));

            alarmHistoryItems.add(alarmHistoryItem);
        }
    } catch (SQLException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ClassNotFoundException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } catch (ParseException e) {
        this.loggingManager.LogError("Error : " + e.getClass().getName(), e.getMessage());
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(conn);
    }
    return alarmHistoryItems;
}