package weaver.conn;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;


import java.util.concurrent.ConcurrentHashMap;


import com.weaver.formmodel.util.StringHelper;

//import weaver.conn.ConnectionPool;
//import weaver.conn.DBConnectionPool;
import jxl.read.biff.Record;
import weaver.file.Prop;
import weaver.general.BaseBean;
import weaver.general.GCONST;
import weaver.general.StaticObj;
import weaver.general.Util;
import weaver.interfaces.datasource.BaseDataSource;
import weaver.servicefiles.DataSourceXML;
import weaver.systeminfo.SystemEnv;

/**
 * 外部数据源管理类<br>
 * @author lrf
 * @date 2018-11-23
 */
public class ExternalDataSourceManager{

	public static final String splitFlag = "#&EXTDATASOURCE_SPLITTAG&#";

	public static Map<String, Boolean> list = new ConcurrentHashMap<String,Boolean>();

	/**
	 * 销毁外部数据源<br>
	 * @param poolName
	 */
	public static synchronized void destoryDataSource(String poolName){
		//ConnectionPool connectionPool = ConnectionPool.getInstance();
		//Hashtable pools  = connectionPool.getPools();
		ConnectionPool connectionPool = ConnectionPool.getInstance();
		try{
			Class clazz = connectionPool.getClass();
			Field field = clazz.getDeclaredField("pools");
			field.setAccessible(true);
			Hashtable<String, DBConnectionPool> pools = (Hashtable<String, DBConnectionPool>) field.get(connectionPool);
			DBConnectionPool pool= getPool(poolName);
			if(pool!=null){
				pool.destroy();
				pools.remove(poolName);
			}
			StaticObj staticobj = StaticObj.getInstance();
			Map datasourceMap = (Map) (staticobj.getRecordFromObj("datasourceMap", "datasources"));
			if(datasourceMap != null){
				if(datasourceMap.containsKey(poolName)){
					datasourceMap.remove(poolName);
					staticobj.putRecordToObj("datasourceMap", "datasources", datasourceMap);
				}
			}
		}catch(Exception e){
			new BaseBean().writeLog(e);
		}

	}

	/**
	 * 根据poolName获取指定的连接池
	 * @param poolName
	 * @return
	 */
	public static DBConnectionPool getPool(String poolName){
		DBConnectionPool pool = null;
		try{
			ConnectionPool connectionPool = ConnectionPool.getInstance();
			Object pool_t = connectionPool.getPool(poolName);
			if(pool_t!=null){
				pool = (DBConnectionPool)pool_t;
			}
		}catch(Exception e){
			new BaseBean().writeLog(e);
		}
		return pool;
	}

	/**
	 * 获取连接池内的dataSource
	 * @param pool
	 * @return
	 */
	public static javax.sql.DataSource getPooled(DBConnectionPool pool){
		javax.sql.DataSource dataSource = null;
		if(pool!=null){
			try{
				Class clazz = pool.getClass();
				Field field = clazz.getDeclaredField("pooled");
				field.setAccessible(true);
				dataSource = (javax.sql.DataSource) field.get(pool);
			}catch(Exception e){
				new BaseBean().writeLog(e);
			}
		}
		return dataSource;
	}


	/**
	 * 创建外部数据源连接池放入ConnctionPool中<br>
	 * @param type
	 * @param datasourcename
	 * @param driverClasses
	 * @param turl
	 * @param user
	 * @param password
	 * @param maxconn
	 * @param minconn
	 * @return
	 */
	public static DBConnectionPool createPool(String type,String datasourcename,String driverClasses,String turl,
											  String user,String password,String provider,int maxconn,int minconn,int usepool)
	{
		DBConnectionPool pool = null;
		try{
			pool =  new DBConnectionPool(datasourcename,driverClasses,turl,
					user,password,type, provider,maxconn, minconn,20, 600, 3, 3600,usepool);
			ConnectionPool connectionPool = ConnectionPool.getInstance();
			Class clazz = connectionPool.getClass();
			Field field = clazz.getDeclaredField("pools");
			field.setAccessible(true);
			Hashtable<String, DBConnectionPool> pools = (Hashtable<String, DBConnectionPool>) field.get(connectionPool);
			if(pool!=null){
				pools.put(datasourcename, pool);
			}
		}catch(Exception e){
			new BaseBean().writeLog(e);
		}
		return pool;
	}


	public static Map<String,String> getConnectionParam(String type,String port,String host,String dbname,String iscluster,String url){
		Map<String,String> param = new HashMap<String, String>();
		String turl = "";
		String forname = "";

		RecordSet rs = new RecordSet();
		rs.executeSql("select driverclass, driverurl from datasource_type where dbtype = '" + type + "' ");
		if(rs.next()) {
			forname = Util.null2String(rs.getString("driverclass"));
			turl = Util.null2String(rs.getString("driverurl"));
		}
		if(!"".equals(turl)) {
			turl = turl.replace("[host]", host).replace("[port]", port);
			if(turl.indexOf("[servicename]") > -1) {// informix专用
				String servicename = "myserver";
				if(dbname.indexOf("@$") > -1) {
					String dbnametemp = dbname;
					dbname = dbnametemp.substring(0, dbnametemp.indexOf("@$"));
					servicename = dbnametemp.substring(dbnametemp.indexOf("@$") + 2);
				}
				turl = turl.replace("[dbname]", dbname).replace("[servicename]", servicename);
			} else {
				turl = turl.replace("[dbname]", dbname);
				if(turl.indexOf("jtds")>-1&&type.indexOf("sqlserver")>-1){
					forname = "net.sourceforge.jtds.jdbc.Driver";
				}
			}
		}
		if(iscluster.equals("2") && !url.equals("")) {
			turl = url;
			if(turl.indexOf("jtds")>-1&&type.indexOf("sqlserver")>-1){
				forname = "net.sourceforge.jtds.jdbc.Driver";
			}
		}
		if(port==null||"".equals(port)){
			port = getDefaultPort(type);
		}
		//For E8
    	/*if (type.equalsIgnoreCase("sqlserver")) {
            if(port==null||"".equals(port))
            port="1433";
            turl = "jdbc:jtds:sqlserver://" + host + ":" + port + ";DatabaseName=" + dbname;
            forname = "net.sourceforge.jtds.jdbc.Driver";
        }
        else if (type.equalsIgnoreCase("oracle")) {
        	if(port==null||"".equals(port))
            port="1521";
            turl = "jdbc:oracle:thin:@" + host + ":" + port + ":" + dbname;
            forname = "oracle.jdbc.OracleDriver";
        }
        else if (type.equalsIgnoreCase("oracle12c")) {
        	 if(port==null||"".equals(port))
            port="1521";
            turl = "jdbc:oracle:thin:@" + host + ":" + port + "/" + dbname;
            forname = "oracle.jdbc.driver.OracleDriver";
        }

        else if (type.equalsIgnoreCase("dm")) {
        	if(port==null||"".equals(port))
                port="5236";
            turl = "jdbc:dm://" + host + ":" + port;
            forname = "dm.jdbc.driver.DmDriver";
        }

		else if (type.equalsIgnoreCase("sybase")) {
			if(port==null||"".equals(port))
            port="2638";
            turl = "jdbc:sybase:Tds:" + host + ":" + port + "/" + dbname;
            forname = "com.sybase.jdbc2.jdbc.SybDriver";
        }
		else if (type.equalsIgnoreCase("informix")) {
			String servicename="myserver";
            if (dbname.indexOf("@$")>-1)
            {   String dbnametemp=dbname;
				dbname=dbnametemp.substring(0,dbnametemp.indexOf("@$"));
                servicename=dbnametemp.substring(dbnametemp.indexOf("@$")+2);
            }
            if(port==null||"".equals(port))
            port="1533";
            turl = "jdbc:informix-sqli://" + host + ":" + port + "/" + dbname+":INFORMIXSERVER="+servicename+";NEWLOCALE=zh_cn,en_us;NEWCODESET=GBK,8859_1,819";
            forname = "com.informix.jdbc.IfxDriver";
        }
		else if (type.equalsIgnoreCase("db2")) {
			if(port==null||"".equals(port))
            port="6789";
            turl = "jdbc:db2://" + host + ":" + port + "/" + dbname;
            forname = "com.ibm.db2.jcc.DB2Driver";
        }
		else if(type.equalsIgnoreCase("mysql")) {
			if(port==null||"".equals(port))
            port="3306";
            turl = "jdbc:mysql://" + host + ":" + port + "/" + dbname;
            forname = "org.gjt.mm.mysql.Driver";
        }
		else if(type.equalsIgnoreCase("odbc")) {
        	turl = "jdbc:odbc:" + dbname;
            forname = "sun.jdbc.odbc.JdbcOdbcDriver";
        }
		else if(type.equalsIgnoreCase("sqlserver2005") || type.equalsIgnoreCase("sqlserver2008")) {
			if(port==null||"".equals(port))
            port="1433";
            turl = "jdbc:sqlserver://" + host + ":" + port + ";DatabaseName=" + dbname;
            forname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        } else if(type.equalsIgnoreCase("hana")){
        	turl = "jdbc:sap://" + host+":"+port+"?reconnect=true";
            forname = "com.sap.db.jdbc.Driver";
        }
    	//判断集群状态和url参数
    	if(iscluster.equals("2")&&!url.equals(""))
        {
        	turl = url;
        }*/
		param.put("port", port);
		param.put("url", turl);
		param.put("driverclasses", forname);
		return param;
	}


	public static BaseDataSource getExternalDS(String datasourceName){
		BaseDataSource baseDataSource = null;
//		DataSourceXML dataSourceXML = new DataSourceXML();
//		List pointArrayList = dataSourceXML.getPointArrayList();
//		Hashtable dataHST = dataSourceXML.getDataHST();
//		for(int i=0;i<pointArrayList.size();i++){
//			String pointid = (String)pointArrayList.get(i);
//			if(pointid.equals("")) continue;
			Hashtable detailHST =DataSourceXML.getDatasourceInfo(datasourceName);
			if(detailHST!=null){
//				if (pointid.equals(datasourceName)) {
					String dbtype = Util.null2String((String) detailHST.get("type"));
					String iscluster = Util.null2String((String) detailHST.get("iscluster"));
					iscluster = iscluster.equals("") ? "1": iscluster;
					String url = Util.null2String((String) detailHST.get("url"));
					String host = Util.null2String((String) detailHST.get("host"));
					String port = Util.null2String((String) detailHST.get("port"));
					String DBname = Util.null2String((String) detailHST.get("dbname"));
					String username = Util.null2String((String) detailHST.get("user"));
					String password = Util.null2String((String) detailHST.get("password"));
					int minconn =  Util.getIntValue(Util.null2String(detailHST.get("minconn")),2);
					int maxconn =  Util.getIntValue(Util.null2String(detailHST.get("maxconn")),5);
					String iscode = Util.null2String((String) detailHST.get("iscode"));
					String usepool = Util.null2String((String) detailHST.get("usepool"));
					String typename = Util.null2String((String) detailHST.get("typename"));
					String sortid = Util.null2String((String) detailHST.get("sortid"));

					baseDataSource = new BaseDataSource();
					baseDataSource.setType(dbtype);
					baseDataSource.setIscluster(iscluster);
					baseDataSource.setUrl(url);
					baseDataSource.setHost(host);
					baseDataSource.setPort(port);
					baseDataSource.setDbname(DBname);
					baseDataSource.setUser(username);
					baseDataSource.setPassword(password);
					baseDataSource.setMaxconn(maxconn);
					baseDataSource.setMinconn(minconn);
					baseDataSource.setIscode(iscode);
					baseDataSource.setDatasourcename(datasourceName);
					baseDataSource.setUsepool(Util.getIntValue(usepool));
					baseDataSource.setTypename(typename);
					baseDataSource.setSortid(Util.getIntValue(sortid));
//				}
			}
//		}
		return baseDataSource;
	}


	public static String getValidSql(String type){
		String testsql = "";
		if (type.equalsIgnoreCase("sqlserver")) {
			testsql = "select 1";
		}else if (type.equalsIgnoreCase("oracle") || type.equalsIgnoreCase("jc")
				|| type.equalsIgnoreCase("gs")) {
			testsql = "select 1 from dual";
		}else if (type.equalsIgnoreCase("oracle12c")) {
			testsql = "select 1 from dual";
		}else if (type.equalsIgnoreCase("dm")||type.equalsIgnoreCase("st")) {
			testsql = "select 1 from dual";
		}else if (type.equalsIgnoreCase("sybase")) {
			//pdataSource.setHouseKeepingTestSql("select 1 from dual");
		}else if (type.equalsIgnoreCase("informix")) {
			testsql = "select 1 from systables where 1=2";
		}else if (type.equalsIgnoreCase("db2")) {
			testsql = "SELECT 1 FROM SYSIBM.SYSDUMMY1";
		}else if(type.equalsIgnoreCase("mysql")
				||type.equalsIgnoreCase("mysql8")
				||type.equalsIgnoreCase("nt")
				||type.equalsIgnoreCase("ob")
		        ||type.equalsIgnoreCase("pg")
				||type.equalsIgnoreCase("hg")
				||type.equalsIgnoreCase("og")
				||type.equalsIgnoreCase("ux")
				||type.equalsIgnoreCase("obmysql")
		) {
			testsql = "SELECT 1";
		}else if(type.equalsIgnoreCase("odbc")) {
			//pdataSource.setHouseKeepingTestSql("select 1 from dual");
		}else if(type.equalsIgnoreCase("sqlserver2005") || type.equalsIgnoreCase("sqlserver2008")) {
			testsql = "select 1";
		}
		return testsql ;
	}


	public static String getDefaultPort(String type){
		String port = "";
		if(type.toLowerCase().indexOf("sqlserver")>-1) {
			port = "1433";
		}else if(type.toLowerCase().indexOf("oracle")>-1) {
			port = "1521";
		}else if(type.toLowerCase().indexOf("dm")>-1) {
			port = "5236";
		}else if(type.toLowerCase().indexOf("st")>-1) {
			port = "2003";
		}else if(type.toLowerCase().indexOf("nt")>-1) {
			port = "5258";
		}else if(type.toLowerCase().indexOf("mysql")>-1) {
			port = "3306";
		}else if(type.toLowerCase().indexOf("db2")>-1) {
			port = "6789";
		}else if(type.toLowerCase().indexOf("jc")>-1) {
			port = "54321";
		}else if(type.toLowerCase().indexOf("sybase")>-1) {
			port = "2638";
		}else if(type.toLowerCase().indexOf("informix")>-1) {
			port = "1533";
		}else if(type.toLowerCase().contains("gs")) {
			port = "1888";
		}else if(type.toLowerCase().contains("ob")) {
			port = "2881";
		}
		else if(type.toLowerCase().contains("obmysql")) {
			port = "2881";
		}
		else if(type.toLowerCase().contains("pg")) {
			port = "5432";
		}
		else if(type.toLowerCase().contains("hg")) {
			port = "5866";
		}
		else if(type.toLowerCase().contains("og")) {
			port = "15400";
		}
		else if(type.toLowerCase().contains("ux")) {
			port = "5432";
		}
		return port;
	}

	public static List<Map<String, Object>> getTablesByDS(String dsName) throws Exception{
		return getTablesByDS(null,dsName);
	}

	public static List<Map<String, Object>> getFieldsByTable(String dsName, String tbName) {
		return getFieldsByTableAndSchema(null,tbName,dsName);
	}

	public static List<Map<String, Object>> getTablesByDS(String schema,String dsName) throws Exception{
		return getTableByConnection(schema,dsName);
	}

	public static List<Map<String, Object>> getFieldsByTable(String schema,String dsName, String tbName) {
		return getFieldsByTableAndSchema(schema,tbName,dsName);
	}

	public static Connection getConnectionByDS(String dsName){
		if(dsName==null||"".equals(dsName.trim())||dsName.trim().equals(GCONST.getServerName())
				||dsName.trim().equals(DataSourceXML.SYS_LOCAL_POOLNAME)){
			ConnectionPool cp = ConnectionPool.getInstance();
			return cp.getConnection();
		}
		BaseDataSource baseDataSource = ExternalDataSourceManager.getExternalDS(dsName);
		if(baseDataSource!=null){
			return baseDataSource.getConnection();
		}
		return null;
	}

	private static List<Map<String, Object>> getTableByConnection(String schema,String poolname){
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			Map<String,Object> map = convertSchema(conn,poolname,schema);
			String dbtype = (String) map.get("dbtype");
			schema = (String) map.get("schema");
			conn = (Connection) map.get("conn");
//			String origindbtype=null;
//			if(conn instanceof WeaverConnection)
//				origindbtype=((WeaverConnection) map.get("conn")).getOrgindbtype();
			DatabaseMetaData dbmd = conn.getMetaData();
			String dbname=dbmd.getDatabaseProductName();
			String[] types = { "TABLE","VIEW" };
			if(dbtype.toLowerCase().indexOf("db2")>-1){
				rs = dbmd.getTables(null, null, null, types);
			}else if(dbtype.indexOf("mysql")>-1){
				rs = dbmd.getTables(null, schema,"%",types);
			}
			else if(dbtype.indexOf("sqlserver")>-1){ //sqlserver数据库
				String sql =  " select name as TABLE_NAME, " +
						" CASE WHEN type = 'U' THEN 'TABLE'  " +
						" ELSE 'VIEW'   " +
						" END   " +
						" as TABLE_TYPE  " +
						" from sysobjects where type='U'or type='V'  " +
						" order by name asc ";

				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String TABLE_NAME = rs2.getString("TABLE_NAME");  //列名
					String TABLE_TYPE = rs2.getString("TABLE_TYPE"); //字段类型名
					Map<String, Object> m = new HashMap<String, Object>();
					if(tmpMap.get(TABLE_NAME)!=null){
						continue;
					}
					m.put("table_name", TABLE_NAME);
					m.put("table_type", TABLE_TYPE);
					tmpMap.put(TABLE_NAME, "true");
					resultList.add(m);
				}
				return resultList;
			}
			else if(dbname.indexOf("Kingbase")>-1)
			{
				rs =dbmd.getTables(null, schema, "%", types);
			}else if(dbname.toLowerCase().indexOf("postgresql")>-1)
			{
				rs =dbmd.getTables(null, schema, "%", types);
			}
			else if(dbname.indexOf("GBase 8s Server")>-1){ // hana大小写敏感
				rs = dbmd.getTables(null, schema, "%",types);
			}
			else{ //oracle和其他数据库
				rs = dbmd.getTables(null, schema.toUpperCase(), "%", types);
			}
			while (rs.next()) {
				String schema_tmp = rs.getString("TABLE_SCHEM");
				String tableName = rs.getString("TABLE_NAME");  //表名
				String tableType = rs.getString("TABLE_TYPE");  //表类型
				//存在schema且不相同的情况下,过滤掉其他schema下的表
	              /*
		    		if(schema_tmp!=null&&!"".equals(schema_tmp)&&!schema_tmp.equalsIgnoreCase(schema)){
		    			continue;
		    		}*/
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("table_name", tableName);
				m.put("table_type", tableType);
				resultList.add(m);
			}
		}catch(Exception e){
			//throw new RuntimeException(e);
			throw new RuntimeException(SystemEnv.getHtmlLabelName(126769, Util.threadVarLanguage()));//获取数据库连接失败，请检查数据源配置是否正确!
		}finally{
			close(rs,conn);
		}
		return resultList;
	}

	private static List<Map<String, Object>> getFieldsByTableConnection(Connection connection,String tbName,String poolname){
		if(DataSourceXML.SYS_LOCAL_POOLNAME.equals(poolname)){
			poolname = "";
		}
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		try {
			DatabaseMetaData meta = connection.getMetaData();
			String username = meta.getUserName();
			String dbname = meta.getDatabaseProductName();
			if (dbname.indexOf("Microsoft SQL Server")>-1) {
				RecordSet recordSet = new RecordSet();
				if(StringHelper.isEmpty(poolname)){
//					recordSet.execute("select user");
					recordSet.executeSql("select SCHEMA_NAME()");
				}else{
					recordSet.executeSql("select SCHEMA_NAME()",poolname);
				}
				if(recordSet.next()){
					username = recordSet.getString(1);
				}else{
					username = "dbo";
				}
				if(StringHelper.isEmpty(username)){
					username = "dbo";
				}
			}
			ResultSet rs;
			if(dbname.toLowerCase().indexOf("db2")>-1){
				rs = meta.getColumns(null, null, tbName, "%");
			}else if(dbname.toLowerCase().indexOf("mysql")>-1){
				rs = meta.getColumns(null, username, tbName, "%");
			}else if(dbname.indexOf("hana")>-1){ // hana大小写敏感
				rs = meta.getColumns(null, username, tbName, "%");
			}
			else if(dbname.indexOf("Kingbase")>-1) {
				rs = meta.getColumns(null, "PUBLIC", tbName.toUpperCase(), "%");
			}
			else if(dbname.toLowerCase().indexOf("postgresql")>-1) {
				rs = meta.getColumns(null, "public", tbName, "%");
			}
//			else if(dbname.indexOf("Zenith")>-1)
//			{
//				rs = meta.getColumns(null, username.toUpperCase(), tbName.toUpperCase(), "%");
//			}
			else{
				rs = meta.getColumns(null, username.toUpperCase(), tbName.toUpperCase(), "%");
			}
			List<String> list = new ArrayList<String>();//用于消除重复
			while(rs.next()){
				String name =rs.getString("COLUMN_NAME");
				if(!list.contains(name)){
					Map<String, Object> m = new HashMap<String, Object>();
					m.put("column_name", name);
					m.put("data_type", rs.getInt("DATA_TYPE"));
					m.put("decimal_digits", rs.getInt("DECIMAL_DIGITS"));
					m.put("column_size", rs.getInt("COLUMN_SIZE"));
					m.put("column_label", "");
					list.add(name);
					resultList.add(m);
				}
			}
			rs.close();
		}catch(SQLException e){
			throw new RuntimeException(e);
		}finally{
			try {
				if(connection!=null){
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return resultList;
	}


	private static List<Map<String, Object>> getFieldsByTableAndSchema(String schema,String tbName,String poolname){
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			Map<String,Object> map = convertSchema(conn,poolname,schema);
			String dbtype = (String) map.get("dbtype");
			schema = (String) map.get("schema");
			conn = (Connection) map.get("conn");
//			String origindbtype=null;
//			if(conn instanceof WeaverConnection)
//				origindbtype=((WeaverConnection) map.get("conn")).getOrgindbtype();
			DatabaseMetaData dbmd = conn.getMetaData();
			//String username = meta.getUserName();
			String dbname = dbmd.getDatabaseProductName();
			String[] types = { "TABLE","VIEW" };
			if(dbtype.indexOf("db2")>-1){
				rs = dbmd.getColumns(null, null, tbName, "%");
			}else if(dbtype.indexOf("mysql")>-1){
				if("".equals(Util.null2String(map.get("dbname")))){
					rs = dbmd.getColumns(null, schema, tbName, "%");
				}else{
					rs = dbmd.getColumns(Util.null2String(map.get("dbname")), schema, tbName, "%");
				}
			}else if(dbtype.indexOf("sqlserver")>-1){
				String sql = "select * from ( select SCHEMA_NAME(o.[uid]) schemaname,c.colid, c.name as COLUMN_NAME,"+
						" CASE WHEN t.name = 'text' THEN 'text' "+
						" WHEN  t.name = 'ntext' THEN 'ntext' "+
						" WHEN  c.length = '-1' THEN  t.name+'(max)' "+
						" ELSE t.name+'('+convert(varchar,c.length,120)+')' "+
						" END "+
						" as DATA_TYPE,"+
						" t.xtype as DATA_TYPE2,"+
						" isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as DECIMAL_DIGITS,"+
						" c.length as COLUMN_SIZE "+
						" from sysobjects o,syscolumns c,systypes t "+
						" where o.id=c.id  and  o.name=? and c.xtype=t.xtype and t.name <>'sysname' )t where schemaname=?"+
						" order by colid ";
				//使用jdbc操作，查询数据库元字段,避免sql缓存命中
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, tbName);
				ps.setString(2, schema);
				ResultSet rs2 = ps.executeQuery();
				List<String> list1 = new ArrayList<String>();
				while (rs2.next()) {
					String name =rs2.getString("COLUMN_NAME");
					if(!list1.contains(name)){
						Map<String, Object> m = new HashMap<String, Object>();
						String data_type = rs2.getString("DATA_TYPE");
						m.put("column_name", name);
						m.put("data_type", rs2.getInt("DATA_TYPE2"));
						m.put("decimal_digits", rs2.getInt("DECIMAL_DIGITS"));
						m.put("column_size", rs2.getInt("COLUMN_SIZE"));
						m.put("column_label", "");
						m.put("whether_bigdata", false);
						if("varchar(max)".equals(data_type) || "nvarchar(max)".equals(data_type) || "text".equals(data_type) || "ntext".equals(data_type)){
							m.put("whether_bigdata", true);
						}
						list1.add(name);
						resultList.add(m);
					}
				}
				return resultList;
			}else if(dbtype.indexOf("hana")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tbName, "%");
			}
			else if(dbtype.indexOf("informix")>-1){ 
				rs = dbmd.getColumns(null, schema, tbName, "%");
			}
			else if(dbname.indexOf("Kingbase")>-1){
				rs = dbmd.getColumns(null, schema, tbName, "%");
			}
			else if(dbname.toLowerCase().indexOf("postgresql")>-1){
				rs = dbmd.getColumns(null, schema, tbName, "%");
			}
			else if(dbname.indexOf("GBase 8s Server")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tbName, "%");
			}
			else{ //oracle和其他数据库
				rs = dbmd.getColumns(null, schema.toUpperCase(), tbName.toUpperCase(), "%");
			}
			List<String> list = new ArrayList<String>();
			while (rs.next()) {
				String name =rs.getString("COLUMN_NAME");
				if(!list.contains(name)){
					int decimal_digits = rs.getInt("DECIMAL_DIGITS");
					int column_size = rs.getInt("COLUMN_SIZE");
					int NUM_PREC_RADIX =  rs.getInt("NUM_PREC_RADIX");
					Map<String, Object> m = new HashMap<String, Object>();
					int data_type = rs.getInt("DATA_TYPE");
					m.put("column_name", name);
					m.put("data_type",data_type);
					if(dbtype.equals("gs")){
						if(NUM_PREC_RADIX>0) {
							m.put("decimal_digits", rs.getInt("NUM_PREC_RADIX"));
						}else{
							m.put("decimal_digits", rs.getInt("DECIMAL_DIGITS"));
						}
					}else{
						m.put("decimal_digits", rs.getInt("DECIMAL_DIGITS"));
					}
					m.put("column_size", rs.getInt("COLUMN_SIZE"));
					m.put("column_label", "");
					m.put("whether_bigdata", false);
					if(Types.LONGVARCHAR == data_type || Types.LONGNVARCHAR == data_type || Types.CLOB == data_type || Types.NCLOB == data_type || Types.BLOB == data_type){
						m.put("whether_bigdata", true);
					}
					list.add(name);
					resultList.add(m);
				}
			}
		}catch(SQLException e){
			throw new RuntimeException(e);
		}finally{
			close(rs,conn);
		}
		return resultList;
	}


	public static boolean isOA(String datasourceName){
		boolean isOA = false;
		if(list.get(datasourceName)!=null){
			isOA = list.get(datasourceName);
		}else{
			RecordSetDataSource rsd = new RecordSetDataSource(datasourceName);
			rsd.executeSql("select companyname from license");
			if(rsd.next()){
				isOA = true;
			}
			list.put(datasourceName, isOA);
		}
		return isOA;
	}


	private static void close(ResultSet rs, Connection conn) {
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 获取对应数据源的schemas列表
	 * @param datasourceid
	 * @return
	 */
	public static List<String> getSchemaList(String datasourceid){

		List<String> schemas = new ArrayList<String>();
		Connection conn = null;
		String dbtype=new RecordSet().getDBType();
		try{
			if(datasourceid==null||"".equals(datasourceid.trim())||datasourceid.trim().equals(GCONST.getServerName())
					||datasourceid.trim().equals(DataSourceXML.SYS_LOCAL_POOLNAME)){
				conn = ConnectionPool.getInstance().getConnection();
			}else{
				BaseDataSource bds =  ExternalDataSourceManager.getExternalDS(datasourceid);
				conn = bds.getConnection();
				dbtype=bds.getType();
			}
			DatabaseMetaData dbmd = conn.getMetaData();
			ResultSet rst;
		 /* if (dbtype.indexOf("sqlserver")>=0) {
			  rst = dbmd.getSchemas();
		  }else {
			  rst = dbmd.getSchemas(null, "%");
		  }*/
			rst = dbmd.getSchemas();
			while(rst.next()){
				schemas.add(rst.getString("TABLE_SCHEM"));
			}
		}catch(Exception e ){
			e.printStackTrace();
		}finally{
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return   schemas;
	}

	/**
	 * 根据datasourceId获取schema
	 * @param datasourceid
	 * @return
	 */
	public static String convertSchema(String datasourceid) {
		Map<String,Object> map = new HashMap<String,Object>();
		String dbtype = "";
		String schema= "";
		if(datasourceid==null||"".equals(datasourceid.trim())||datasourceid.trim().equals(GCONST.getServerName())
				||datasourceid.trim().equals(DataSourceXML.SYS_LOCAL_POOLNAME)){ //OA数据源
			dbtype = ConnectionPool.getInstance().getDbtype().toLowerCase();
			if(dbtype.indexOf("sqlserver")>-1){ //sqlserver schema 特殊处理
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select SCHEMA_NAME()");
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "dbo";
					}
				}else{
					schema = "dbo";
				}
			}else{
				if(schema==null||"".equals(schema.trim())){
					schema = Prop.getPropValue(GCONST.getConfigFile(), GCONST.getServerName() + ".user");
				}
			}
		}else{ //外部数据源
			BaseDataSource bds =  ExternalDataSourceManager.getExternalDS(datasourceid);
			dbtype = bds.getType().toLowerCase();
			if(dbtype.indexOf("sqlserver")>-1){  //sqlserver schema 特殊处理
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select SCHEMA_NAME()",datasourceid);
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "dbo";
					}
				}else{
					schema = "dbo";
				}
			}else{
				if(schema==null||"".equals(schema.trim())){
					schema = bds.getUser();
				}
			}
		}
		return schema;
	}


	private static Map<String,Object> convertSchema(Connection conn ,String datasourceid,String schema){
		Map<String,Object> map = new HashMap<String,Object>();
		String dbtype = "";
		String origindbtype = "";
		String dbname="";
		if(datasourceid==null||"".equals(datasourceid.trim())||datasourceid.trim().equals(GCONST.getServerName())
				||datasourceid.trim().equals(DataSourceXML.SYS_LOCAL_POOLNAME)){ //OA数据源
			conn = ConnectionPool.getInstance().getConnection();
			dbtype = ConnectionPool.getInstance().getDbtype().toLowerCase();
			origindbtype=ConnectionPool.getInstance().getOrgindbtype().toLowerCase();
			if(dbtype.indexOf("sqlserver")>-1&&(schema==null||"".equals(schema))){ //sqlserver schema 特殊处理
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select SCHEMA_NAME()");
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "dbo";
					}
				}else{
					schema = "dbo";
				}
			}
			else if(dbtype.indexOf("postgresql")>-1&&(schema==null||"".equals(schema))){  //postgresql schema 特殊处理
				RecordSet recordSet = new RecordSet();
				recordSet.executeQuery("select current_schema()");
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "public";
					}
				}else{
					schema = "public";
				}
			}
			else if(origindbtype.indexOf("jc")>-1&&(schema==null||"".equals(schema)))
			{
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select current_schema()");
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "public";
					}
				}else{
					schema = "public";
				}
			}
			else if(origindbtype.indexOf("nt")>-1&&(schema==null||"".equals(schema)))
			{
				RecordSet recordSet = new RecordSet();
				recordSet.executeQuery("select dbinfo('dbname') from dual;");
				if(recordSet.next()){
					schema = recordSet.getString(1);
				}
			}
			else{
				if(schema==null||"".equals(schema.trim())){
					schema = Prop.getPropValue(GCONST.getConfigFile(), GCONST.getServerName() + ".user");
				}
			}
		}else{ //外部数据源
			BaseDataSource bds =  ExternalDataSourceManager.getExternalDS(datasourceid);
			conn = bds.getConnection();
			dbtype = bds.getType().toLowerCase();
			dbname=bds.getDbname();
			String [] arr={"og","hg","postgresql","ux"};
			if(dbtype.indexOf("sqlserver")>-1&&(schema==null||"".equals(schema))){  //sqlserver schema 特殊处理
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select SCHEMA_NAME()",datasourceid);
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "dbo";
					}
				}else{
					schema = "dbo";
				}
			}
			else if(Arrays.asList(arr).indexOf(dbtype)>-1&&(schema==null||"".equals(schema))){  //postgresql schema 特殊处理
			    dbtype="postgresql";
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select current_schema()",datasourceid);
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "public";
					}
				}else{
					schema = "public";
				}
			}
			else if(dbtype.indexOf("jc")>-1&&(schema==null||"".equals(schema)))
			{
				RecordSet recordSet = new RecordSet();
				recordSet.executeSql("select current_schema()",datasourceid);
				if(recordSet.next()){
					schema = recordSet.getString(1);
					if(StringHelper.isEmpty(schema)){
						schema = "public";
					}
				}else{
					schema = "public";
				}
			}
			else if(dbtype.indexOf("gbasedbt")>-1&&(schema==null||"".equals(schema)))
			{
				RecordSet recordSet = new RecordSet();
				recordSet.executeQueryWithDatasource("select dbinfo('dbname') from dual;",datasourceid);
				if(recordSet.next()){
					schema = recordSet.getString(1);
				}
			}
			else{
				if(schema==null||"".equals(schema.trim())){
					schema = bds.getUser();
				}
			}
		}
		map.put("dbname", dbname);
		map.put("dbtype", dbtype);
		map.put("schema", schema);
		map.put("conn", conn);
		return map;
	}



	public static List<Map<String, Object>> getTablesList(String datasourceid) {
		return getTablesList(datasourceid,null);
	}

	/**
	 *
	 * @param datasourceid
	 * @param schema
	 * @return
	 */
	public static List<Map<String, Object>> getTablesList(String datasourceid,String schema) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			Map<String,Object> map = convertSchema(conn,datasourceid,schema);
			String dbtype = (String) map.get("dbtype");
			schema = (String) map.get("schema");
			conn = (Connection) map.get("conn");
//			String origindbtype=null;
//			if(conn instanceof WeaverConnection)
//				origindbtype=((WeaverConnection) map.get("conn")).getOrgindbtype();
			DatabaseMetaData dbmd = conn.getMetaData();
			String dbname=dbmd.getDatabaseProductName();
			String[] types = { "TABLE","VIEW" };
			if(dbtype.toLowerCase().indexOf("db2")>-1){
				rs = dbmd.getTables(null, null, null, types);
			}else if(dbtype.indexOf("mysql")>-1){
				if("".equals(Util.null2String(map.get("dbname")))){
					rs = dbmd.getTables(null,schema ,"%",types);
				}else{
					rs = dbmd.getTables(Util.null2String(map.get("dbname")),schema ,"%",types);
				}
			}else if(dbtype.indexOf("sqlserver")>-1){ //sqlserver数据库
				 /*  sys.schemas支持版本 SQL Server 2008 及以上
				 ( select
				   t.[name] as TABLE_NAME,
				   'TABLE' as  TABLE_TYPE
				   from
				   sys.tables as t,
				   sys.schemas as s
				   where
				    t.schema_id = s.schema_id
                    and s.[name] = 'dbo'  --schema
					and t.type_desc = 'USER_TABLE'

				)
			union all
				  ( select
				   t.[name] as TABLE_NAME,
				    'VIEW' as TABLE_TYPE
				   from
				   sys.views as t,
				   sys.schemas as s
				   where t.schema_id = s.schema_id
                    and s.[name] = 'dbo'  --schema
					and t.type_desc = 'VIEW'
					)
					order by TABLE_NAME asc
				*/
				String sql =  "select TABLE_NAME,TABLE_TYPE from ( select name as TABLE_NAME,SCHEMA_NAME([uid]) schemaname,  " +
						" CASE WHEN type = 'U' THEN 'TABLE'  " +
						" ELSE 'VIEW'   " +
						" END   " +
						" as TABLE_TYPE  " +
						" from sysobjects where type='U'or type='V'   )t   where schemaname='"+schema+"'  " +
						" order by TABLE_NAME asc ";

				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String TABLE_NAME = rs2.getString("TABLE_NAME");  //列名
					String TABLE_TYPE = rs2.getString("TABLE_TYPE"); //字段类型名
					Map<String, Object> m = new HashMap<String, Object>();
					if(tmpMap.get(TABLE_NAME)!=null){
						continue;
					}
					m.put("table_name", TABLE_NAME);
					m.put("table_type", TABLE_TYPE);
					tmpMap.put(TABLE_NAME, "true");
					resultList.add(m);
				}
				return resultList;
			}
			else if(dbname.indexOf("Kingbase")>-1)
			{
				rs =dbmd.getTables(null, schema, "%", types);
			}
			else if(dbname.toLowerCase().indexOf("postgresql")>-1)
			{
				rs =dbmd.getTables(null, schema, "%", types);
			}
			else if(dbname.toLowerCase().indexOf("informix")>-1)
			{
				rs =dbmd.getTables(null, schema, "%", types);
			}
			else if(dbname.indexOf("GBase 8s Server")>-1){ // hana大小写敏感
				rs = dbmd.getTables(null, schema, "%",types);
			}
			else{ //oracle和其他数据库
				rs = dbmd.getTables(null, schema.toUpperCase(), "%", types);
			}
			while (rs.next()) {
				String schema_tmp = rs.getString("TABLE_SCHEM");
				String tableName = rs.getString("TABLE_NAME");  //表名
				String tableType = rs.getString("TABLE_TYPE");  //表类型
				//存在schema且不相同的情况下,过滤掉其他schema下的表
              /*
	    		if(schema_tmp!=null&&!"".equals(schema_tmp)&&!schema_tmp.equalsIgnoreCase(schema)){
	    			continue;
	    		}*/
				Map<String, Object> m = new HashMap<String, Object>();
				m.put("table_name", tableName);
				m.put("table_type", tableType);
				resultList.add(m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			close(rs,conn);
		}
		return resultList;
	}

	/**
	 *
	 * @param datasourceid
	 * @param tablename
	 * @return
	 */
	public static ArrayList getAllColumns(String datasourceid, String tablename) {
		return getAllColumns(datasourceid,null,tablename);
	}

	/**
	 *
	 * @param datasourceid
	 * @param schema
	 * @param tablename
	 * @return
	 */
	public static ArrayList getAllColumns(String datasourceid,String schema, String tablename) {
		ArrayList returnArr = new ArrayList();
		tablename = Util.null2String(tablename);
		if (tablename.equals("")){
			return returnArr;
		}
		String targertSchema = "";
		if(tablename.indexOf(".")>-1){
			String[] tableinfo = tablename.split("\\.") ;
			targertSchema = tableinfo[0];
			tablename = tableinfo[1];
		}
		Connection conn = null;
		ResultSet rs = null;
		try{
			Map<String,Object> map = convertSchema(conn,datasourceid,schema);
			String dbtype = (String) map.get("dbtype");
			schema = (String) map.get("schema");
			if(!"".equals(targertSchema)){
				schema = targertSchema;
			}
			conn = (Connection) map.get("conn");
			DatabaseMetaData dbmd = conn.getMetaData();
			String dbname=dbmd.getDatabaseProductName();
			if(dbtype.indexOf("db2")>-1){
				rs = dbmd.getColumns(null, null, tablename, "%");
			}else if(dbtype.indexOf("mysql")>-1){
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbname.toLowerCase().indexOf("postgresql")>-1) {
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbname.indexOf("GBase 8s Server")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
//			else if (dbtype.indexOf("postgresql")>-1) {//qc:505872 新增PostgreSQL数据库类型
//				String sql = "SELECT a.attname AS COLUMN_NAME,t.typname AS data_type FROM pg_class c, pg_attribute a LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, pg_type t WHERE lower(c.relname) = lower('" + tablename + "') and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum";
//				//使用jdbc操作，查询数据库系统表元字段,避免开启sql缓存命中后,查询数据不对
//				PreparedStatement ps = conn.prepareStatement(sql);
//				ResultSet rs2 = ps.executeQuery();
//				while (rs2.next()) {
//					String columnname = Util.null2String(rs2.getString(1));
//					returnArr.add(columnname);
//				}
//				return returnArr;
//			}
			else if(dbtype.indexOf("sqlserver")>-1){
				//  1. sqlserver 的schema概念等同于架构，少数情况下会出现目标库架构不对的情况，导致查询不出元字段
				//  2. sqlserver 低版本驱动下,查询出来的varchar(max) nvarchar(max)字段会变成text ,ntext ,sqljdbc42.jar 才正常
				//  3. sqlserver 外部数据源排序规则为Chinese_PRC_BIN 排序规则下，利用jdbc无法获取元字段信息

				String sql = "select c.name as COLUMN_NAME, " +
						" CASE WHEN t.name = 'text' THEN 'text' " +
						"  WHEN  t.name = 'ntext' THEN 'ntext' " +
						"  WHEN  c.length = '-1' THEN  t.name+'(max)' "  +
						" ELSE t.name+'('+convert(varchar,c.length,120)+')' " +
						" END " +
						" as data_type " +
						" from sysobjects o,syscolumns c,systypes t " +
						" where o.id=c.id and o.name=?  and c.xtype=t.xtype and t.name <>'sysname' "+
						" order by c.colid ";
				//sqlserver2000 不支持SCHEMA_NAME()函数。
				//jtds驱动不支持查询sqlserver版本
				//使用如下sql判断数据库是否sql2000
				PreparedStatement ps = conn.prepareStatement("select lower(replace(@@version,' ','')) as version");
				ResultSet rs2 = ps.executeQuery();
				if(rs2.next()){
					String version = rs2.getString("version");
					if(version.indexOf("server2000")>-1){
						sql = "select c.name as COLUMN_NAME, " +
								" CASE WHEN t.name = 'text' THEN 'text' " +
								"  WHEN  t.name = 'ntext' THEN 'ntext' " +
								"  WHEN  c.length = '-1' THEN  t.name+'(max)' "  +
								" ELSE t.name+'('+convert(varchar,c.length,120)+')' " +
								" END " +
								" as data_type " +
								" from sysobjects o,syscolumns c,systypes t " +
								" where o.id=c.id and o.name=?  and c.xtype=t.xtype and t.name <>'sysname' "+
								" order by c.colid ";
					}
				}


				//使用jdbc操作，查询数据库元字段,避免sql缓存命中
				ps = conn.prepareStatement(sql);
				ps.setString(1, tablename);
				rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String columnname = Util.null2String(rs2.getString(1));
					if(tmpMap.get(columnname)!=null){
						continue;
					}
					tmpMap.put(columnname, "true");
					returnArr.add(columnname);
				}
				return returnArr;
			}else if(dbtype.indexOf("sybase")>-1){
				String sql = "select c.name as COLUMN_NAME,'' as data_type from sysobjects o,syscolumns c,systypes t" +
						" where o.id=c.id and o.name=? and c.type=t.type order by c.colid";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, tablename);
				ResultSet rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String columnname = Util.null2String(rs2.getString(1));
					if(tmpMap.get(columnname)!=null){
						continue;
					}
					tmpMap.put(columnname, "true");
					returnArr.add(columnname);
				}
				return returnArr;

			}else if(dbtype.indexOf("hana")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbname.indexOf("Kingbase")>-1) {
				rs = dbmd.getColumns(null, schema, tablename.toUpperCase(), "%");
			}
			else{
				rs = dbmd.getColumns(null, schema.toUpperCase(), tablename.toUpperCase(), "%");
			}

			while(rs.next()){
				String schema_tmp = rs.getString("TABLE_SCHEM");
				//存在schema且不相同的情况下,先跳过,不确定是否能满足跨schema访问的需求
  	    		/*if(schema_tmp!=null&&!"".equals(schema_tmp)&&!schema_tmp.equalsIgnoreCase(schema)){
  	    			continue;
  	    		}*/
				String columnName = rs.getString("COLUMN_NAME");
				if(!returnArr.contains(columnName)){
					returnArr.add(columnName);
				}
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			close(rs, conn);
		}
		return returnArr;
	}


	/**
	 *
	 * @param datasourceid
	 * @param tablename
	 * @return
	 */
	public static Map getAllColumnWithTypes(String datasourceid, String tablename){
		return getAllColumnWithTypes(datasourceid,null,tablename);
	}

	/**
	 *
	 * @param datasourceid
	 * @param schema
	 * @param tablename
	 * @return
	 */
	public static Map getAllColumnWithTypes(String datasourceid,String schema, String tablename){
		Map returnArr = new TreeMap();
		tablename = Util.null2String(tablename);
		if (tablename.equals("")){
			return returnArr;
		}
		String targertSchema = "";
		if(tablename.indexOf(".")>-1){
			String[] tableinfo = tablename.split("\\.") ;
			targertSchema = tableinfo[0];
			tablename = tableinfo[1];
		}
		Connection conn = null;
		ResultSet rs = null;
		try{
			Map<String,Object> map = convertSchema(conn,datasourceid,schema);
			String dbtype = (String) map.get("dbtype");
			schema = (String) map.get("schema");
			if(!"".equals(targertSchema)){
				schema = targertSchema;
			}
			conn = (Connection) map.get("conn");
			DatabaseMetaData dbmd = conn.getMetaData();
			String dbname=dbmd.getDatabaseProductName();
			if(dbtype.indexOf("db2")>-1){
				rs = dbmd.getColumns(null, null, tablename, "%");
			}else if(dbtype.indexOf("mysql")>-1){
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbtype.indexOf("postgresql")>-1){
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbname.indexOf("GBase 8s Server")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
//			else if (dbtype.indexOf("postgresql")>-1) {//qc:505872 新增PostgreSQL数据库类型
//				String sql = "SELECT a.attname AS COLUMN_NAME,t.typname AS data_type FROM pg_class c, pg_attribute a " +
//						" LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, pg_type t " +
//						" WHERE lower(c.relname) = lower('" + tablename + "') and a.attnum > 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY a.attnum";
//				//使用jdbc操作，查询数据库系统表元字段,避免开启sql缓存命中后,查询数据不对
//				PreparedStatement ps = conn.prepareStatement(sql);
//				ResultSet rs2 = ps.executeQuery();
//				while (rs2.next()) {
//					String columnname = Util.null2String(rs2.getString(1)).toLowerCase();
//					String data_type = Util.null2String(rs2.getString(2)).toLowerCase();
//					returnArr.put(columnname,data_type);
//				}
//				return returnArr;
//			}
			else if(dbtype.indexOf("sqlserver")>-1){
				//  1. sqlserver 的schema概念等同于架构，少数情况下会出现目标库架构不对的情况，导致查询不出元字段
				//  2. sqlserver 低版本驱动下,查询出来的varchar(max) nvarchar(max)字段会变成text ,ntext ,sqljdbc42.jar 才正常
				//  3. sqlserver 外部数据源排序规则为Chinese_PRC_BIN 排序规则下，利用jdbc无法获取元字段信息
				String sql = " select COLUMN_NAME,data_type,schemaname from ( select c.name as COLUMN_NAME, SCHEMA_NAME(o.uid) schemaname,c.colid, " +
						" CASE WHEN t.name = 'text' THEN 'text' " +
						"  WHEN  t.name = 'ntext' THEN 'ntext' " +
						"  WHEN  c.length = '-1' THEN  t.name+'(max)' "  +
						" ELSE t.name+'('+convert(varchar,c.length,120)+')' " +
						" END " +
						" as data_type " +
						" from sysobjects o,syscolumns c,systypes t " +
						" where o.id=c.id and o.name=?  and c.xtype=t.xtype and t.name <>'sysname'   )t "+
						" where schemaname ='"+schema+"'  order by colid";

				//sqlserver2000 不支持SCHEMA_NAME()函数。
				//jtds驱动不支持查询sqlserver版本
				//使用如下sql判断数据库是否sql2000
				PreparedStatement ps = conn.prepareStatement("select lower(replace(@@version,' ','')) as version");
				ResultSet rs2 = ps.executeQuery();
				if(rs2.next()){
					String version = rs2.getString("version");
					if(version.indexOf("server2000")>-1){
						sql = "select c.name as COLUMN_NAME, " +
								" CASE WHEN t.name = 'text' THEN 'text' " +
								"  WHEN  t.name = 'ntext' THEN 'ntext' " +
								"  WHEN  c.length = '-1' THEN  t.name+'(max)' "  +
								" ELSE t.name+'('+convert(varchar,c.length,120)+')' " +
								" END " +
								" as data_type " +
								" from sysobjects o,syscolumns c,systypes t " +
								" where o.id=c.id and o.name=?  and c.xtype=t.xtype and t.name <>'sysname' "+
								" order by c.colid ";
					}
				}
				//使用jdbc操作，查询数据库元字段,避免sql缓存命中
				ps = conn.prepareStatement(sql);
				ps.setString(1, tablename);
				rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String columnName = rs2.getString("COLUMN_NAME").toLowerCase();  //列名
					String dataTypeName = rs2.getString("data_type").toLowerCase(); //字段类型名
					if(!returnArr.containsKey(columnName)){
						returnArr.put(columnName,dataTypeName);
					}
				}
				return returnArr;
			}else if(dbtype.indexOf("sybase")>-1){
				//  1. sqlserver 的schema概念等同于架构，少数情况下会出现目标库架构不对的情况，导致查询不出元字段
				//  2. sqlserver 低版本驱动下,查询出来的varchar(max) nvarchar(max)字段会变成text ,ntext ,sqljdbc42.jar 才正常
				//  3. sqlserver 外部数据源排序规则为Chinese_PRC_BIN 排序规则下，利用jdbc无法获取元字段信息

				String sql = "select c.name as COLUMN_NAME,'' as data_type from sysobjects o,syscolumns c,systypes t " +
						"where o.id=c.id and o.name=? and c.type=t.type order by c.colid";
				///使用jdbc操作，查询数据库系统表元字段,避免开启sql缓存命中后,查询数据不对
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, tablename);
				ResultSet rs2 = ps.executeQuery();
				Map<String , String> tmpMap  = new HashMap<String, String>();
				while(rs2.next()){
					String columnName = rs2.getString("COLUMN_NAME").toLowerCase();  //列名
					String dataTypeName = rs2.getString("data_type").toLowerCase(); //字段类型名
					if(!returnArr.containsKey(columnName)){
						returnArr.put(columnName,dataTypeName);
					}
				}
				return returnArr;
			}else if(dbtype.indexOf("hana")>-1){ // hana大小写敏感
				rs = dbmd.getColumns(null, schema, tablename, "%");
			}
			else if(dbname.indexOf("Kingbase")>-1) {
				rs = dbmd.getColumns(null, schema, tablename.toUpperCase(), "%");
			}else{ // oracle,sqlserver和其他数据库
				rs = dbmd.getColumns(null, schema.toUpperCase(), tablename.toUpperCase(), "%");
			}
			while(rs.next()){
				String schema_tmp = rs.getString("TABLE_SCHEM");
				//存在schema且不相同的情况下,先跳过,不确定是否能满足跨schema访问的需求
				/*
	    		if(schema_tmp!=null&&!"".equals(schema_tmp)&&!schema_tmp.equalsIgnoreCase(schema)){
	    			continue;
	    		}*/
				String columnName = rs.getString("COLUMN_NAME").toLowerCase();  //列名
				int dataType = rs.getInt("DATA_TYPE");     //对应的java.sql.Types的SQL类型(列类型ID)
				String dataTypeName = rs.getString("TYPE_NAME").toLowerCase(); //字段类型名
				if(!returnArr.containsKey(columnName)){
					returnArr.put(columnName,dataTypeName);
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			close(rs, conn);
		}
		return returnArr;
	}

	public static String getOrgindbtypeByType(String type){
		if(type==null){
			return "";
		}
		type = type.toLowerCase();
		String orgindbtype = "";
		if(type.indexOf("oracle") >= 0) {
			orgindbtype = "oracle";
		}else if (type.indexOf("sqlserver") >= 0){
			orgindbtype = "sqlserver";
		}else if (type.indexOf("db2") >= 0) {
			orgindbtype = "db2";
		}
		else if(type.contains("obmysql")) {
			orgindbtype = "obmysql";
		}
		else if(type.indexOf("mysql") >= 0) {
			orgindbtype = "mysql";
		}else if(type.indexOf("dm") >= 0) {//dm国产数据库高度兼容oracle，设置dbtype为oracle并保留原始类型在orgindbtype属性中
			orgindbtype = "dm";
		}else if(type.indexOf("st") >= 0) {//神通国产数据库高度兼容oracle，设置dbtype为oracle并保留原始类型在orgindbtype属性中
			orgindbtype = "st";
		}else if(type.indexOf("jc") >= 0) {//人大金仓国产数据库高度兼容oracle，设置dbtype为oracle并保留原始类型在orgindbtype属性中
			orgindbtype = "jc";
		}else if(type.indexOf("nt") >= 0) {
			orgindbtype = "nt";
		}else if(type.contains("gs")) {
			orgindbtype = "gs";
		}else if(type.contains("hana")) {
			orgindbtype = "hana";
		}else if(type.contains("sybase")) {
			orgindbtype = "sybase";
		}else if(type.contains("infomix")) {
			orgindbtype = "informix";
		}else if(type.contains("ob")) {
			orgindbtype = "ob";
		}else if(type.contains("pg")) {
			orgindbtype = "pg";
		}else if(type.contains("hg")) {
			orgindbtype = "hg";
		}else if(type.contains("og")) {
			orgindbtype = "og";
		}else if(type.contains("ux")) {
			orgindbtype = "ux";
		}
		return orgindbtype;
	}
}
