wp/nwyj/.svn/pristine/78/78e58ad8eb247fe7cf647d172c6c28ad0c187f89.svn-base

package com.sinosoft.cm;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.sinosoft.cm.chart.util.UUIDUtil;
import com.sinosoft.cm.common.DateUtil;
import com.sinosoft.cm.common.StringUtils;
import com.sinosoft.cm.ex.SQLStringException;

public class CMTemplate {
	public static final int SUCCESS=0;
	public static final int FAILURE=1;
	public static final Boolean autoCommit=true;
	public static final Boolean unAutoCommit=false;
	private Logger	log			= Logger.getLogger(this.getClass());
	private Connection connection=null;
	/**
	 *無參構造方法
	 */
	public CMTemplate() {
	}
	/**
	 * 創建Connection構造方法
	 * @param conn
	 */
	public CMTemplate(Connection conn){
		if(conn==null){
			this.connection=DBUtils.getConnection();
		}else{
			this.connection=conn;
		}
	}
	public  int setAutoCommit(Boolean autoCommitState){
		int state=CMTemplate.SUCCESS;
		try {
			if(connection.getAutoCommit())connection.setAutoCommit(autoCommitState);
		} catch (SQLException e) {
			state=CMTemplate.FAILURE;
			log.error(e.getMessage());
		}finally{
			return state;
		}
	}
	/**
	 * 事务回滚
	 * @return
	 */
	public  int rollBack(){
		if(connection==null)return CMTemplate.FAILURE; 
		int state=CMTemplate.SUCCESS;
		try {
			connection.rollback();
		} catch (SQLException e) {
			state=CMTemplate.FAILURE;
			log.error(e.getMessage());
		}finally{
			return state;
		}
		
	}
	/**
	 * 关闭事务链接
	 * @return
	 */
	public  int closeTConnection(){
		if(connection==null)return CMTemplate.FAILURE;
		int state=CMTemplate.SUCCESS;
		try {
			connection.commit();
			connection.close();
		} catch (SQLException e) {
			state=CMTemplate.FAILURE;
			log.error(e.getMessage());
		}finally{
			return state;
		}
	}
	
	
	/**
	 * 
	 * @param sql
	 * @param args
	 * @return -1:异常；>0:返回成功更新的结果
	 * @throws SQLException
	 */
	public int update(String sql,Object...args) throws SQLException{
		Connection conn=null;
		PreparedStatement ps=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			//设置占位符的参数
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1, args[i]);
				}
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLStringException(this.getClass()+":update(String sql,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(null, ps, conn);
		}
	}

	/**
	 * 查询
	 * @param sql
	 * @param handler
	 * @param args
	 * @return null:查询异常
	 * @throws SQLStringException 
	 */
	public Object query(String sql,CMHandlerResultSet handler,Object...args) throws SQLStringException{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);		
				}
			}
			rs=ps.executeQuery();
			return handler.doHandler(rs);
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	
	/**
	 * 查询指定的字段
	 * @param sql
	 * @param list
	 * @param args
	 * @return
	 * @throws SQLStringException
	 */
	public List<Map<String,String >> queryToMap(String sql,List<String> list,Object...args) throws SQLStringException{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);		
				}
			}
			rs=ps.executeQuery();
			List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
			while(rs.next()){
				Map<String ,String> map=new HashMap<String, String>();
				for(String key:list){
					if(rs.getObject(key)==null){
						map.put(key,"0");	
					}else{
						map.put(key, rs.getObject(key).toString());	
					}
				}
				listMap.add(map);
				
			}
			return listMap;
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	public List<String> queryToList(String sql,Object...args)throws SQLStringException{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);		
				}
			}
			rs=ps.executeQuery();
			List<String> list=new ArrayList<String>();
			while(rs.next()){
				list.add(rs.getObject(1).toString());	
			}
			return list;
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	
	
	
	
	public List<Map<String,String >> queryMoreTypeToMap(String sql,List<String> list,Object...args) throws SQLStringException{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);	
				}
			}
			rs=ps.executeQuery();
			List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
			while(rs.next()){
				Map<String ,String> map=new HashMap<String, String>();
				for(String key:list){
					if(rs.getObject(key)==null){
						map.put(key,"0");	
					}else{
						if(rs.getObject(key) instanceof Integer) map.put(key,String.valueOf((Integer)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Date)
							map.put(key, DateUtil.toString((java.sql.Date)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Timestamp)
							map.put(key, DateUtil.toString(new Date(((java.sql.Timestamp)(rs.getObject(key))).getTime())));
						if(rs.getObject(key) instanceof java.sql.Clob)
							map.put(key, StringUtils.clobToString((java.sql.Clob)(rs.getObject(key))));
						if(rs.getObject(key) instanceof String)
							map.put(key, rs.getObject(key).toString());
						if(rs.getObject(key) instanceof Number)
							map.put(key, String.valueOf(rs.getObject(key)));
					}
				}
				listMap.add(map);
				
			}
			return listMap;
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	
	
	public List<Map<String,String >> queryMoreTypeToMap(String sql,Object...args) throws SQLStringException{
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareStatement(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);	
				}
			}
			
			rs=ps.executeQuery();
			ResultSetMetaData metaData=ps.getMetaData();
			int count=metaData.getColumnCount();
			List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
			while(rs.next()){
				Map<String ,String> map=new HashMap<String, String>();
				
				for(int index=0;index<count;index++){
					String key=metaData.getColumnLabel(index+1);
					if(rs.getObject(key)==null){
						map.put(key,"0");	
					}else{
						if(rs.getObject(key) instanceof Integer) map.put(key,String.valueOf((Integer)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Date)
							map.put(key, DateUtil.toString((java.sql.Date)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Timestamp)
							map.put(key, DateUtil.toString(new Date(((java.sql.Timestamp)(rs.getObject(key))).getTime())));
						if(rs.getObject(key) instanceof java.sql.Clob)
							map.put(key, StringUtils.clobToString((java.sql.Clob)(rs.getObject(key))));
						if(rs.getObject(key) instanceof String)
							map.put(key, rs.getObject(key).toString());
						if(rs.getObject(key) instanceof Number)
							map.put(key, String.valueOf(rs.getObject(key)));
					}
				}
				listMap.add(map);
				
			}
			return listMap;
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	
	public List<Map<String,String >> queryMoreTypeToMapByCallableStatement(String sql,Object...args) throws SQLStringException{
		Connection conn=null;
		CallableStatement ps=null;
		ResultSet rs=null;
		try {
			conn=DBUtils.getConnection();
			ps=conn.prepareCall(sql);
			if(args!=null){
				for(int i=0;i<args.length;i++){
					ps.setObject(i+1,args[i]);	
				}
			}
			rs=ps.executeQuery();
			ResultSetMetaData metaData=ps.getMetaData();
			int count=metaData.getColumnCount();
			List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
			while(rs.next()){
				Map<String ,String> map=new HashMap<String, String>();
				
				for(int index=0;index<count;index++){
					String key=metaData.getColumnLabel(index+1);
					if(rs.getObject(key)==null){
						map.put(key,"0");	
					}else{
						if(rs.getObject(key) instanceof Integer) map.put(key,String.valueOf((Integer)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Date)
							map.put(key, DateUtil.toString((java.sql.Date)rs.getObject(key)));
						if(rs.getObject(key) instanceof java.sql.Timestamp)
							map.put(key, DateUtil.toString(new Date(((java.sql.Timestamp)(rs.getObject(key))).getTime())));
						if(rs.getObject(key) instanceof java.sql.Clob)
							map.put(key, StringUtils.clobToString((java.sql.Clob)(rs.getObject(key))));
						if(rs.getObject(key) instanceof String)
							map.put(key, rs.getObject(key).toString());
						if(rs.getObject(key) instanceof Number)
							map.put(key, String.valueOf(rs.getObject(key)));
					}
				}
				listMap.add(map);
				
			}
			return listMap;
			
		} catch (SQLException e) {
			throw new SQLStringException(this.getClass()+": query(String sql,CMHandlerResultSet handler,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs,ps,conn);
			
		}
	}
	public Connection getConnection() {
		return connection;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	
	
	public static void main(String[] args) throws SQLException {
		String str="insert into  NWYJ . DEPARTMENT_CEDIAN ( XZDW ,CDYCM ,CDID ,FD_OBJECTID ) VALUES(?,?,?,?);";
		CMTemplate cmt=new CMTemplate();
	/*	cmt.update(str,"佛山","佛山地区负荷地区总负荷","121878665083486867",UUIDUtil.getFdObjectId());
		cmt.update(str,"中山","中山地区负荷地区总负荷","121878665083486982",UUIDUtil.getFdObjectId());
		cmt.update(str,"清远","清远地区负荷地区总负荷 ","121878665083486960",UUIDUtil.getFdObjectId());
		cmt.update(str,"珠海","珠海地区负荷地区总负荷","121878665083486845",UUIDUtil.getFdObjectId());
		cmt.update(str,"阳江","阳江地区负荷地区总负荷","121878665083486950",UUIDUtil.getFdObjectId());
		cmt.update(str,"江门","江门地区负荷地区总负荷","121878665083486877",UUIDUtil.getFdObjectId());
		cmt.update(str,"汕头","汕头地区负荷地区总负荷","121878665083486855",UUIDUtil.getFdObjectId());
		cmt.update(str,"东莞","东莞地区负荷地区总负荷","121878665083486971",UUIDUtil.getFdObjectId());
		cmt.update(str,"肇庆","肇庆地区负荷地区总负荷","121878665083486907",UUIDUtil.getFdObjectId());
		cmt.update(str,"潮州","潮州地区负荷地区总负荷","121878665083486990",UUIDUtil.getFdObjectId());
		cmt.update(str,"揭阳","揭阳地区负荷地区总负荷 ","121878665083487000",UUIDUtil.getFdObjectId());
		cmt.update(str,"梅州","梅州地区负荷地区总负荷","121878665083486925",UUIDUtil.getFdObjectId());
		cmt.update(str,"河源","河源地区负荷地区总负荷","121878665083486942",UUIDUtil.getFdObjectId());
		cmt.update(str,"云浮","云浮地区负荷地区总负荷 ","121878665083487008",UUIDUtil.getFdObjectId());
		cmt.update(str,"韶关","韶关地区负荷地区总负荷","121878665083486824",UUIDUtil.getFdObjectId());
		cmt.update(str,"汕尾","汕尾地区负荷地区总负荷 ","121878665083486934",UUIDUtil.getFdObjectId());
		cmt.update(str,"茂名","茂名地区负荷地区总负荷","121878665083486897",UUIDUtil.getFdObjectId());
		cmt.update(str,"湛江","湛江地区负荷地区总负荷 ","121878665083486887",UUIDUtil.getFdObjectId());
		cmt.update(str,"惠州","惠州地区负荷地区总负荷","121878665083486918",UUIDUtil.getFdObjectId());*/
		cmt.update(str,"","统调负荷有功","121878665083489910",UUIDUtil.getFdObjectId());
		

		
		
		
		
		
		
		
		
		
		

		
	}
	
	
	
}