restreedao.java

sla性能采集

package com.asiainfo.ainx.slaconf.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ainx.common.database.ConnectionPools;

import com.asiainfo.ainx.slaconf.util.DBEncodingUtil;
import com.asiainfo.ainx.slaconf.vo.ResTreeVo;
import com.asiainfo.ainx.slaconf.vo.SlaConfigVo;

/**
 * 此dao用于查询资源树的内容
 * @author panyy
 *
 */
public class ResTreeDao {
	//查询资源组
	private static final String SQL_RESGROUP = "select resparchild.*,resresgroups.alias from resparchild,resresgroups where resresgroups.id = resparchild.childid and resparchild.childtype=4";
	//查询设备
	private static final String SQL_DEVICE = "select resparchild.*,resnodes.alias from resparchild,resnodes where resparchild.childid = resnodes.id";
	//查询链路
	private static final String SQL_LINKS = "select resparchild.*,reslinks.alias from resparchild,reslinks where resparchild.childid = reslinks.id";
	
	
	/**
	 * 根据id和type查询资源名称
	 */
	public String findResName(String resId,String resType){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = null;
		String resName= null;
		if(resType.equals("links")){//链路
			sql = "select alias from reslinks where id='"+resId+"'";
		}
		if(resType.equals("device")){//设备
			sql = "select alias from resnodes where id='"+resId+"'";
		}
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				resName = rs.getString("alias");
			}
			resName = DBEncodingUtil.fromDBString(resName);
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, stmt);
		}
		return resName;
		
	}
	/**
	 * 查找所有资源
	 * @return
	 */
	public ResTreeVo[] findAllRes(){

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_RESGROUP+" union "+SQL_DEVICE+" union "+SQL_LINKS;
		List list = new ArrayList();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ResTreeVo vo = new ResTreeVo();
				vo.setParentId(rs.getString("parentid"));
				vo.setResId(rs.getString("childid"));
				vo.setResName(rs.getString("alias"));
				vo.setResType(rs.getString("childtype"));
				DBEncodingUtil.fromDBobj(vo);
				list.add(vo);
			}
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return (ResTreeVo[])list.toArray(new ResTreeVo[list.size()]);
	
	}
	/**
	 * 查询资源组
	 * @return
	 */
	public ResTreeVo[] findResGroup(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_RESGROUP;
		List list = new ArrayList();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ResTreeVo vo = new ResTreeVo();
				vo.setParentId(rs.getString("parentid"));
				vo.setResId(rs.getString("childid"));
				vo.setResName(rs.getString("alias"));
				vo.setResType(rs.getString("childtype"));
				DBEncodingUtil.fromDBobj(vo);
				list.add(vo);
			}
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return (ResTreeVo[])list.toArray(new ResTreeVo[list.size()]);
	}
	/**
	 * 查询设备
	 * @return
	 */
	public ResTreeVo[] findDevice(){

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_DEVICE;
		List list = new ArrayList();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ResTreeVo vo = new ResTreeVo();
				vo.setParentId(rs.getString("parentid"));
				vo.setResId(rs.getString("childid"));
				vo.setResName(rs.getString("alias"));
				vo.setResType(rs.getString("childtype"));
				DBEncodingUtil.fromDBobj(vo);
				list.add(vo);
			}
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return (ResTreeVo[])list.toArray(new ResTreeVo[list.size()]);
	
		
	}
	/**
	 * 查找链路
	 * @return
	 */
	public ResTreeVo[] findLinks(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_LINKS;
		List list = new ArrayList();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				ResTreeVo vo = new ResTreeVo();
				vo.setParentId(rs.getString("parentid"));
				vo.setResId(rs.getString("childid"));
				vo.setResName(rs.getString("alias"));
				vo.setResType(rs.getString("childtype"));
				DBEncodingUtil.fromDBobj(vo);
				list.add(vo);
			}
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return (ResTreeVo[])list.toArray(new ResTreeVo[list.size()]);
	
	}
	/**
	 * 关闭数据库连接
	 * 
	 * 
	 */
	private void dbClose(Connection conn, Statement stmt) {
		try {
			if (stmt != null){
				stmt.close();
				stmt = null;
			}
			if (conn != null){
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭数据库连接
	 * 
	 * 
	 * 
	 * 
	 */
	private void dbClose(Connection conn, PreparedStatement pstmt) {
		try {
			if (pstmt != null){
				pstmt.close();
				pstmt = null;
			}
			if (conn != null){
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}