slaconfigdao.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 com.asiainfo.ainx.slaconf.bo.impl.ResTreeBo;
import com.asiainfo.ainx.slaconf.vo.SlaConfigVo;


import ainx.common.database.ConnectionPools;
import ainx.common.util.UuidGenerator;



/**
 * 完成对SAL_CONFIG表的操作
 * @author panyy
 *
 */
public class SlaConfigDao {
	private static final String SQL_SELECT_ALL = "select * from (select a.*,rownum as rn from sla_config a) where rn>=? and rn <=?";
	private static final String SQL_SELECT_TOTALNUM = "select count(*) as num from sla_config";
	private static final String SQL_UPDATE = "update sla_config set res_id=?,res_type=?,item_type=?,upper=?,lower=?,upper_level=?,lower_level=? where id = ?";
	private static final String SQL_DELETE_BYID = "delete from sla_config where id in ";
	private static final String SQL_INSERT = "insert into sla_config(res_id,res_type,item_type,upper,lower,upper_level,lower_level,id) values(?,?,?,?,?,?,?,?)";
	private static final String SQL_SELECT_BYID = "select * from sla_config where id = ?";
	
	/**
	 * 根据id查询
	 * @param id
	 * @return
	 */
	public SlaConfigVo findConfigById(String id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_SELECT_BYID;
		SlaConfigVo vo = new SlaConfigVo();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				vo.setId(rs.getString("id"));
				vo.setResId(rs.getString("res_id"));
				vo.setResType(rs.getString("res_type"));
				vo.setItemType(rs.getString("item_type"));
				vo.setLower(rs.getString("lower"));
				vo.setUpper(rs.getString("upper"));
				vo.setLowerLevel(rs.getString("lower_level"));
				vo.setUpperLevel(rs.getString("upper_level"));
				
			}
			
			
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return vo;
	}
	/**
	 * 查询设备的监测指标配置，可分页
	 * @param fromPage
	 * @param toPage
	 * @return
	 */
	public ArrayList findConfigList(int fromPage,int toPage){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_SELECT_ALL;
		List list = new ArrayList();
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, fromPage);
			pstmt.setInt(2, toPage);
			rs = pstmt.executeQuery();
			while(rs.next()){
				SlaConfigVo vo = new SlaConfigVo();
				vo.setId(rs.getString("id"));
				vo.setResId(rs.getString("res_id"));
				vo.setResType(rs.getString("res_type"));
				if(rs.getString("res_type").equals("3"))
					vo.setResName(getBo().findResName(rs.getString("res_id"), "links"));
				if(rs.getString("res_type").equals("5"))
					vo.setResName(getBo().findResName(rs.getString("res_id"), "device"));
				vo.setItemType(rs.getString("item_type"));
				vo.setLower(rs.getString("lower"));
				vo.setUpper(rs.getString("upper"));
				vo.setLowerLevel(rs.getString("lower_level"));
				vo.setUpperLevel(rs.getString("upper_level"));
				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 (ArrayList)list;
		
	}
	/**
	 * 查询所有符合条件的纪录数
	 * @return
	 */
	public int findTotalNum(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = SQL_SELECT_TOTALNUM;
		int totalNum = 0;
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				totalNum = rs.getInt(1);
			}
		}catch(SQLException e){
			e.printStackTrace();
			
		}finally {
			try {
				if(rs!=null){
					rs.close();
					rs = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			dbClose(conn, pstmt);
		}
		return totalNum;
	}
	/**
	 * 添加
	 * @param vo
	 * @return
	 */
	public int addSlaConfig(SlaConfigVo vo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = SQL_INSERT;
		int flag = -1;
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getResId());
			pstmt.setString(2, vo.getResType());
			pstmt.setString(3, vo.getItemType());
			pstmt.setString(4, vo.getUpper());
			pstmt.setString(5, vo.getLower());
			pstmt.setString(6, vo.getUpperLevel());
			pstmt.setString(7, vo.getLowerLevel());
			pstmt.setString(8, vo.getId());
			flag = pstmt.executeUpdate();
			conn.commit();
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			
		}finally {
			dbClose(conn, pstmt);
		}
		return flag;
	
	}
	/**
	 * 删除
	 * @param ids
	 * @return
	 */
	public int deleteSlaConfigById(String[] ids){
		Connection conn = null;
		Statement stmt = null;
		String sql = SQL_DELETE_BYID;
		int flag = -1;
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			stmt = conn.createStatement();
			sql += "("+delString(ids)+")";
			flag = stmt.executeUpdate(sql);
			conn.commit();
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			
		}finally {
			dbClose(conn, stmt);
		}
		return flag;
		
	}
	/**
	 * 修改设备指标
	 * @param vo
	 * @return
	 */
	public int updateSlaConfig(SlaConfigVo vo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = SQL_UPDATE;
		int flag = -1;
		try{
			conn = ConnectionPools.getConnection("sla.oracle");
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vo.getResId());
			pstmt.setString(2, vo.getResType());
			pstmt.setString(3, vo.getItemType());
			pstmt.setString(4, vo.getUpper());
			pstmt.setString(5, vo.getLower());
			pstmt.setString(6, vo.getUpperLevel());
			pstmt.setString(7, vo.getLowerLevel());
			pstmt.setString(8, vo.getId());
			flag = pstmt.executeUpdate();
			conn.commit();
		}catch(SQLException e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
			
		}finally {
			dbClose(conn, pstmt);
		}
		return flag;
	}
	/**
	 * 关闭数据库连接
	 * 
	 * 
	 */
	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();
		}
	}
	
	public static void main(String[] args){
		SlaConfigDao test = new SlaConfigDao();
		ArrayList list = test.findConfigList(0, 1);
		System.out.println(((SlaConfigVo)list.get(0)).getResId());
		
	}
	
	private String delString(String[] ids){
		StringBuffer sb = new StringBuffer();
		for(int i=0;i<ids.length;i++){
			if(i==0)
				sb.append("\'"+ids[i]+"\'");
			else 
				sb.append(",\'"+ids[i]+"\'");
		}
		return sb.toString();
	}
	
	private ResTreeBo getBo(){
		return new ResTreeBo();
	}

}