resitemresdao.java

sla性能采集

package com.asiainfo.ainx.slaconf.dao;


import java.util.ArrayList;

import org.apache.log4j.Logger;

import ainx.common.database.ConnectionPools;
import ainx.common.database.DaoBase;

import com.asiainfo.ainx.slaconf.util.DBEncodingUtil;
import com.asiainfo.ainx.slaconf.vo.ResItemVO;
/**
 * 
 * @author zhaotj
 *
 */
public class ResItemResDAO extends DaoBase {
	
	private static Logger log = Logger
	.getLogger("com.asiainfo.ainx.slaconf.dao.ResItemResDAO");
	
	private String RES_ITEMRES;
	
	private String LINK_ITEMS = "select b.itemid,b.itemname,a.resid,c.alias,c.bandwidth/1000000 as param, " +
			"to_char(a.curtime,'yyyy-mm-dd hh24:mi:ss') as curtime," +
			"a.curvalue,a.curstatus from sla_item_status a," +
			"item_define b,reslinks c,sla_config d where d.user_id=? and d.res_id=a.resid and a.itemid=d.item_type and a.itemid = b.itemid " +
			"and b.restype = 3 and c.id = a.resid and lower(c.alias) like lower(?) and  c.id in (select distinct e.res_id from sla_config e)";
  
	private String DEV_ITEMS = "select b.itemid,b.itemname,a.resid,c.alias,c.snmpaddr as param, " +
			"to_char(a.curtime,'yyyy-mm-dd hh24:mi:ss') as curtime," +
			"a.curvalue,a.curstatus from sla_item_status a," +
			"item_define b, resnodes c ,sla_config d where d.user_id=? and d.res_id=a.resid and a.itemid=d.item_type and  a.itemid = b.itemid " +
			"and b.restype = 5 and c.id = a.resid and lower(c.alias) like lower(?) and  c.id in (select distinct e.res_id from sla_config e)";
			
	public ArrayList getResItemResource() {
		/**
		 * 返回的数捄1�7
		 */
		ArrayList Res = new ArrayList();		

		ResItemVO resVO = null;

		/**
		 * 生成sql语句
		 */
		String sqlCmd = RES_ITEMRES;
		System.out.println("res_itemres="+sqlCmd);

		try {
			conn = ConnectionPools.getConnection("sla.oracle");
			stmt = conn.createStatement();
			/**
			 * 执行查询
			 */
			rs = stmt.executeQuery(sqlCmd);
			while (rs.next()) {
				resVO = new ResItemVO();
				resVO.setItemname(rs.getString(1));
				resVO.setResname(rs.getString(2));
				resVO.setLasttime(rs.getString(3));
				resVO.setLastvalue(rs.getString(4));
				resVO.setLaststatus(rs.getString(5));
				resVO.setCurtime(rs.getString(6));
				resVO.setCurvalue(rs.getString(7));
				resVO.setCurstatus(rs.getString(8));
				DBEncodingUtil.fromDBobj(resVO);
				Res.add(resVO);

			}

		} catch (Exception e) {
			System.out.println("error in get resitems:"+e.toString());
		} finally {
			System.out.println("Records : " + Res.size());
			/**
			 * 关闭数据庄1�7
			 */
			closeConnection(rs, stmt, conn);
		}
		return Res;

	}

	public ArrayList getResStatusList(int userid, String resType, String filterType) {
		/**
		 * 返回的数捄1�7
		 */
		ArrayList Res = new ArrayList();		

		ResItemVO resVO = null;

		/**
		 * 生成sql语句
		 */
		
		String sqlCmd = LINK_ITEMS;
		if(resType.equals("5"))
			sqlCmd = DEV_ITEMS;
		
		System.out.println("execut sql:"+sqlCmd);
        if(filterType==null || filterType.equals(""))
        	filterType = "";
        System.out.println("filterType="+filterType);
		try {
			conn = ConnectionPools.getConnection("sla.oracle");
			//NXContext cnt = NXContext.getInstance();
			//DataSource ds = cnt.getDBDataSource();
			//conn = ds.getConnection();
			
			pstmt = conn.prepareStatement(sqlCmd);
			if(filterType==null)
				filterType="";
			pstmt.setInt(1, userid);
			pstmt.setString(2,"%"+filterType+"%");
			/**
			 * 执行查询
			 */
			rs = pstmt.executeQuery();
			while (rs.next()) {
				resVO = new ResItemVO();
				
				resVO.setItemname(rs.getString("itemname"));
				resVO.setResname(rs.getString("alias"));
				resVO.setParam(rs.getString("param"));				
				resVO.setCurtime(rs.getString("curtime"));
				resVO.setCurvalue(rs.getString("curvalue"));
				resVO.setCurstatus(rs.getString("curstatus"));
				resVO.setResId(rs.getInt("resId"));
				resVO.setItemId(rs.getInt("itemId"));
				DBEncodingUtil.fromDBobj(resVO);
				Res.add(resVO);

			}

		} catch (Exception e) {
			System.out.println("error in get res status:"+e.toString());
			e.printStackTrace();
		} finally {
			System.out.println("Records : " + Res.size());
			/**
			 * 关闭数据庄1�7
			 */
			closeConnection(rs, stmt, conn);
		}
		return Res;

	}

	/*
	public static void main(String[] args) {
		ResItemResDAO aa = new ResItemResDAO();
		ArrayList smile = aa.getLinkStatusList("3");
	} */
}