wp/nwyj/.svn/pristine/29/29e2c56358143b5b5e23840e2ba0498b2bffcc2c.svn-base

package com.sinosoft.am.org.jdbcUtil;

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.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.sinosoft.cm.DBUtils;
import com.sinosoft.cm.ex.SQLStringException;

public class ORGTemplate {
	
	private static final Logger log = Logger.getLogger(ORGTemplate.class);
	public ORGTemplate() {
		super();
	}
	/**
	 * 
	 * @param sql PreparedStatement 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);
			
		}
		
	}
	
	public ArrayList<Map<String,String>> query(String sql,Object...args) throws SQLException{
		ArrayList<Map<String, String>> al = new ArrayList<Map<String, String>>();
		String[] fields = null;
		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  rsmd = rs.getMetaData();
				int colCount = rsmd.getColumnCount();
				fields = new String[colCount];
				for(int col =1;col<=fields.length;col++){
					fields[col - 1] = rsmd.getColumnName(col);
				}
				while(rs.next()){
					
					Map<String,String> dataMap = new HashMap<String,String>();
					for (int i = 0; i < fields.length; i++) {
						
						dataMap.put(fields[i], rs.getString(fields[i]));
					}
					al.add(dataMap);
				}
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLStringException(this.getClass()+":query(String sql,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		return al;
	}
	
	
	public ArrayList<Map<String,String>> getSerachResult(String sql,String...args) throws SQLException{
		ArrayList<Map<String, String>> al = new ArrayList<Map<String, String>>();
		String[] fields = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		ArrayList<String> parList = new ArrayList<String>();
		try {
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			if(args!=null){
				for(int i = 0;i<args.length;i++){
					if(args[i].endsWith("_needUsein")){
						args[i] = args[i].substring(0,args[i].indexOf("_needUsein"));
					String[] parArr = args[i].split(",");
						for (int j = 0; j < parArr.length; j++) {
							parList.add(parArr[j]);
						}
					}else{
						parList.add(args[i]);
					}
				}
				
				for(int m=0;m<parList.size();m++){
					ps.setObject(m+1, parList.get(m));
				}
				rs = ps.executeQuery();
				ResultSetMetaData  rsmd = rs.getMetaData();
				int colCount = rsmd.getColumnCount();
				fields = new String[colCount];
				for(int col =1;col<=fields.length;col++){
					fields[col - 1] = rsmd.getColumnName(col);
				}
				while(rs.next()){
					
					Map<String,String> dataMap = new HashMap<String,String>();
					for (int i = 0; i < fields.length; i++) {
						
						dataMap.put(fields[i], rs.getString(fields[i]));
					}
					al.add(dataMap);
				}
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLStringException(this.getClass()+":query(String sql,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		return al;
	}
	
	
	
	public List<String> getResultList(String sql,String...args) throws SQLStringException{
		String[] fields = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		ArrayList<String> list = new ArrayList<String>();
		ArrayList<String> parList = new ArrayList<String>();
		try {
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			if(args!=null&&args.length>0){
				
				for(int i=0;i<args.length;i++){
					if(args[i].endsWith("_needUsein")){
						args[i] = args[i].substring(0,args[i].indexOf("_needUsein"));
					String[] parArr = args[i].split(",");
						for (int j = 0; j < parArr.length; j++) {
							parList.add(parArr[j]);
						}
					}else{
						parList.add(args[i]);
					}
				}
				
				for(int m=0;m<parList.size();m++){
					ps.setObject(m+1, parList.get(m));
				}
				
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			fields = new String[count];
			for(int i=1;i<=fields.length;i++){
				fields[i-1] = rsmd.getColumnName(i);
			}
			while(rs.next()){
				String val = "";
				for(int k=0;k<fields.length;k++){
					val = rs.getString(fields[k]);
				}
				list.add(val);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLStringException(this.getClass()+":query(String sql,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		
		return list;
	}
	
	
	public List<String[]> getSearchResult(String sql,String...args) throws SQLException{
		String[] fields = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		ArrayList<String[]> list = new ArrayList<String[]>();
		ArrayList<String> parList = new ArrayList<String>();
		try {
			conn = DBUtils.getConnection();
			ps = conn.prepareStatement(sql);
			if(args!=null&&args.length>0){
				
				for(int i=0;i<args.length;i++){
					if(args[i].endsWith("_needUsein")){
						args[i] = args[i].substring(0,args[i].indexOf("_needUsein"));
					String[] parArr = args[i].split(",");
						for (int j = 0; j < parArr.length; j++) {
							parList.add(parArr[j]);
						}
					}else{
						parList.add(args[i]);
					}
				}
				
				for(int m=0;m<parList.size();m++){
					ps.setObject(m+1, parList.get(m));
				}
				
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int count = rsmd.getColumnCount();
			fields = new String[count];
			for(int i=1;i<=fields.length;i++){
				fields[i-1] = rsmd.getColumnName(i);
			}
			while(rs.next()){
				String[] val = new String[count];
				for(int k=0;k<val.length;k++){
					val[k] = rs.getString(fields[k]);
				}
				list.add(val);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLStringException(this.getClass()+":query(String sql,Object...args)；sql语句异常");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
		
		return list;
	}
	
	
	public String  listToString(List<Map<String,String>> list){
		StringBuffer sb = new StringBuffer();
		for(Map<String,String> map :list){
			sb.append("{");
			for (Map.Entry<String, String> entry : map.entrySet()) {
				   sb.append("\""+entry.getKey()+"\":\""+entry.getValue()+"\",");
				  }
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			sb.append("},");
		}
		if(sb.lastIndexOf(",")>-1){
			sb.deleteCharAt(sb.lastIndexOf(","));
		}
		return sb.toString();
		
	}
	
	
	public String mapToJson(Map<?,?> map){
		
		
		StringBuffer sb = new StringBuffer();
		for(Map.Entry<?, ?> m:map.entrySet()){
			sb.append("{");
			sb.append("\""+m.getKey()+"\":\""+m.getValue()+"\",");
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			sb.append("},");
		}
		if(sb.lastIndexOf(",")>-1){
			sb.deleteCharAt(sb.lastIndexOf(","));
		}
		return sb.toString();
	}
	/**
	 * 
	 * 给list中的map 添加属性 key value 根据的map中的key 对应的value值
	 * @return
	 *//*
	public List<Map<String,String>> addExtParamToList(List<Map<String,String>> list,String key,String newKey){
		
		for(Map<String,String> map : list){
			String keyValue = map.get(key);
			String pinyin = Pinyin4jUtil.getPinYin(src);
			map.put(newKey, keyValue); 
			
		}
		
		return null;
	}*/
	public static void main(String[] args){
		
		StringBuffer sql=new StringBuffer();
		String id = "8F6F2D6B24494CB6B1F1E0931E7AF87D";
		ORGTemplate org = new ORGTemplate();
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		try {
			/*sql.append("--查询给定id及其一级子机构的信息  \n");
			sql.append("SELECT A.ORG_ID,A.ORG_NAME,A.PARENT_ID,A.ORG_LEVEL,A.IS_VIRTUAL, \n");
			sql.append("DECODE((SELECT COUNT(1) FROM EMC_DEPARTMENT_IN B WHERE B.PARENT_ID = A.ORG_ID),'0','0','1') AS 是否有子机构   \n");
			sql.append("FROM EMC_DEPARTMENT_IN A WHERE PARENT_ID = ? OR ORG_ID= ? AND IS_DEL='0'\n");*/
			
			/*sql.append("--查询给定id及其一级子机构的信息  \n");
			sql.append("WITH P AS(SELECT U.USER_ID \"id\",U.REAL_NAME \"name\",U.ORG_ID \"parentId\",'/nwyj/scripts/qui/libs/icons/home.png' \"icon\",U.POSITION \"orgLevel\", U.MOBILE \"perPhone\",U.GENDER \"isParent\",'per' \"leafType\"  \n");
			sql.append("FROM SYS_USER_INFO U ,SYS_DEPARTMENT D,SYS_DEPT_USER DU   \n");
			sql.append("WHERE U.USER_ID = DU.USER_ID AND D.DEPT_ID = DU.DEPT_ID AND D.DEPT_ID=?   \n");
			sql.append("AND U.USER_ID NOT IN (SELECT INLINK_INDEX FROM EMC_AM_ORG_INLINK X  \n");
			sql.append("WHERE X.VIRTUAL_ORG_ID =? AND X.IS_DEL ='0') ) ,   \n");
			sql.append("O AS(SELECT A.DEPT_ID ,A.DEPT_NAME ,A.PARENT_ID ,'/nwyj/scripts/qui/libs/icons/user-black.png' icon,A.DEPT_LEVEL,A.CORP_ID e,   \n");
			sql.append("DECODE(DECODE((SELECT COUNT(1) FROM SYS_DEPARTMENT B WHERE B.PARENT_ID = A.DEPT_ID),0,0,1)+  \n");
			sql.append("DECODE((SELECT COUNT(1) FROM SYS_USER_INFO U ,SYS_DEPARTMENT D,SYS_DEPT_USER DU   \n");
			sql.append("WHERE U.USER_ID = DU.USER_ID AND D.DEPT_ID = DU.DEPT_ID AND D.DEPT_ID=A.DEPT_ID   \n");
			sql.append("AND U.USER_ID NOT IN (SELECT INLINK_INDEX FROM EMC_AM_ORG_INLINK X  \n");
			sql.append("WHERE X.VIRTUAL_ORG_ID =? AND X.IS_DEL ='0')  \n");
			sql.append("),0,0,1),0,'false','true') AS isParent,'org' leafType   \n");
			sql.append("FROM SYS_DEPARTMENT A WHERE (A.PARENT_ID = ? OR A.DEPT_ID=?) AND A.IS_DEL='0')   \n");
			sql.append("SELECT * FROM P   \n");
			sql.append("UNION ALL  \n");
			sql.append("SELECT * FROM O  \n");*/
			
			sql.append("--查询给定id一级子机构的信息(不包括他本身) \n");
			sql.append("WITH T AS (SELECT * FROM SYS_DEPARTMENT WHERE INSTR(DEPT_PATH,'_'||?||'_')>0) \n");
			sql.append("SELECT A.DEPT_ID \"id\",A.DEPT_NAME \"name\",A.PARENT_ID \"parentId\",A.DEPT_LEVEL \"orgLevel\", \n");
			sql.append("DECODE((SELECT COUNT(1) FROM T B WHERE B.PARENT_ID = A.DEPT_ID),0,'false','true') AS \"isParent\"   \n");
			sql.append("FROM T A WHERE A.PARENT_ID = ? OR A.DEPT_ID =? \n ");
			list = org.query(sql.toString(),id,id,id);
			
			System.out.println(org.listToString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		Map<String,String> map = new HashMap<String,String>();
		map.put("1", "2");
		map.put("2", "3");
		map.put("3", "4");
		String str = new ORGTemplate().mapToJson(map);
		System.out.println(str);
	}
	

}