package cn.com.capcloud.core.noentitydb;

import cn.com.capcloud.ccui.core.common.utils.StringUtil;
import cn.com.capcloud.ccui.core.common.utils.UUIDUtils;
import cn.com.capcloud.core.websocket.RedisPublication;
import cn.com.capcloud.core.system.user.User;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.joda.time.DateTime;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.nio.charset.StandardCharsets;
import java.sql.Statement;
import java.util.*;


public class BaseDao {

	private static final Logger logger = LogManager.getLogger("LogFileAppSql");

	//分页查询
	public static JtPage getPageListAllCol(String tableName,String where,int page,int pageSize,JdbcTemplate jdbcTemplate){
		page++;
		String sql = "select * from " + tableName;
		if(where!=null) {
			sql+=" "+where;
		}
		 
		PageTool pageTool = new PageTool(sql, page, pageSize, jdbcTemplate);
		return pageTool.getJtPage();
	}
	//分页查询
		public static JtPage getPageListAllCol(String sql,int page,int pageSize,JdbcTemplate jdbcTemplate){
			page++;
			
			 
			PageTool pageTool = new PageTool(sql, page, pageSize, jdbcTemplate);
			return pageTool.getJtPage();
		}
	public static JtPage getPageListAllCol(String sql,int page,int pageSize,JdbcTemplate jdbcTemplate,String sqlCount){
		page++;


		PageTool pageTool = new PageTool(sql, page, pageSize, jdbcTemplate,sqlCount);
		return pageTool.getJtPage();
	}
	public static String getPkFieldName(String tableName,JdbcTemplate jdbcTemplate) {
		String pkFieldName="";
		if(DBCheck.isKingbase()){
			pkFieldName=getPkFieldNameByTableName(tableName,jdbcTemplate);
		}else{
			List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
			for(int i=0;i<mapList.size();i++) {
				String key=String.valueOf(mapList.get(i).get("Key"));
				if(pkFieldName.equals("")&&key.length()>0&&key.equals("PRI")) {
					pkFieldName=String.valueOf(mapList.get(i).get("Field"));
				}
			}
		}
		return pkFieldName;
	}
	//查询所有列信息
	public static List<Map<String,Object>> getAllCommentsByTableName(String tableName,JdbcTemplate jdbcTemplate) {
		return getAllCommentsByTableName(tableName, jdbcTemplate,"");
	}
	//查询所有列信息
	public static List<Map<String,Object>> getAllCommentsByTableName(String tableName,JdbcTemplate jdbcTemplate,String whereSql) {
		if(DBCheck.isKingbase()){
			String sql;
			if(DBCheck.getCachedJpaDatabase().equals("mysql")){//使用Kingbase 兼容MySQL
				sql="select column_name,data_type,column_type as character_maximum_length,'' as key from information_schema.columns where  table_name='"+tableName+"'  ";
			}else{//使用Kingbase 兼容Oracle
				sql="select column_name,data_type,character_maximum_length,'' as key from information_schema.columns where  table_name='"+tableName+"'  ";
			}
			List<Map<String,Object>> mapList=jdbcTemplate.queryForList(sql);
			String pkFieldName=getPkFieldNameByTableName(tableName,jdbcTemplate);
			if(!pkFieldName.equals("")){
				for (int i = 0; i < mapList.size(); i++) {
					Map<String,Object> map=mapList.get(i);
					String columnName=String.valueOf(map.get("column_name"));
					if(pkFieldName.equals(columnName)){
						mapList.get(i).put("key","PRI");
						return mapList;
					}else{
						mapList.get(i).put("key","");
					}
				}
			}
			return mapList;
		}
		String sql;
		if(DBCheck.isH2()){
			sql="SELECT\n" +
					"    c.COLUMN_NAME AS \"Field\",\n" +
					"    c.DATA_TYPE AS \"Type\",\n" +
					"    c.CHARACTER_MAXIMUM_LENGTH AS \"Length\",\n" +
					"    c.IS_NULLABLE AS \"Null\",\n" +
					"    c.COLUMN_DEFAULT AS \"Default\",\n" +
					"    c.IS_IDENTITY AS \"Extra\",\n" +
					"    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END AS \"Key\",\n" +
					"    c.REMARKS AS \"Comment\",\n" +
					"    c.ORDINAL_POSITION AS \"Ordinal\"\n" +
					"FROM INFORMATION_SCHEMA.COLUMNS c\n" +
					"LEFT JOIN (\n" +
					"    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME\n" +
					"    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc\n" +
					"    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu\n" +
					"      ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME\n" +
					"     AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA\n" +
					"     AND tc.TABLE_NAME = kcu.TABLE_NAME\n" +
					"    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'\n" +
					") pk\n" +
					"  ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA\n" +
					" AND c.TABLE_NAME = pk.TABLE_NAME\n" +
					" AND c.COLUMN_NAME = pk.COLUMN_NAME\n" +
					"WHERE c.TABLE_SCHEMA = 'PUBLIC'\n" +
					"  AND c.TABLE_NAME = '"+tableName+"' "+whereSql+"\n" +
					"ORDER BY c.ORDINAL_POSITION;";
		}else{
			//默认执行的SQL，支持MySQL5.7/MySQL.8.0
			sql="show full columns from "+tableName;
		}
		List<Map<String,Object>> mapList=jdbcTemplate.queryForList(sql);
		return mapList;
	}
	// kingbase 查询所有列信息（不含主键，含备注）
	public static List<Map<String,Object>> getAllCommentsByTableNameNotPkField(String tableName,JdbcTemplate jdbcTemplate,String whereSql) {
		String sql="SELECT a.attnum,\n" +
				"       a.attname AS column_name,\n" +
				"       t.typname AS data_type,\n" +
				"       CASE WHEN a.atttypmod<=0 THEN null ELSE (a.atttypmod-4) END AS lengthvar,\n" +
				"       a.attnotnull AS notnull,\n" +
				"       b.description AS comment\n" +
				"  FROM sys_class c,\n" +
				"       sys_attribute a\n" +
				"       LEFT OUTER JOIN sys_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
				"       sys_type t\n" +
				" WHERE c.relname = '"+tableName+"'\n" +
				whereSql+
				"       and a.attnum > 0\n" +
				"       and a.attrelid = c.oid\n" +
				"       and a.atttypid = t.oid\n" +
				" ORDER BY a.attnum";
		List<Map<String,Object>> mapList=jdbcTemplate.queryForList(sql);
		return mapList;
	}
	//查询主键字段名
	public static String getPkFieldNameByTableName(String tableName,JdbcTemplate jdbcTemplate) {
		String sql=" SELECT tq.schema_name,\n" +
				"        tq.table_name,\n" +
				"        tq.index_name,\n" +
				"        tp.attname,\n" +
				"        tp.attnum,\n" +
				"        tq.amname,\n" +
				"        tz.description\n" +
				" FROM  sys_attribute tp INNER join\n" +
				"       (\n" +
				"        SELECT td.nspname schema_name, --schema name \n" +
				"        tc.relname table_name, -- table_name\n" +
				"        td.relname index_name, -- index_name\n" +
				"        UNNEST(indkey) attnum,\n" +
				"        tc.oid taboid ,-- table_id\n" +
				"        td.oid indexoid,\n" +
				"        tm.description,\n" +
				"        td.amname\n" +
				"        FROM sys_index ti \n" +
				"        INNER JOIN (SELECT tc.oid,\n" +
				"                           nspname , -- schema_name\n" +
				"                           relname -- table_name\n" +
				"                      FROM sys_class tc  \n" +
				"                      INNER JOIN sys_namespace  tn ON ( tc.relnamespace = tn.oid) \n" +
				"                      WHERE tc.relkind IN ('r','p')      AND   -- 表，分区表 \n" +
				"                            nspname = 'public'  --AND           -- schema\n" +
				"                            --relname = 'TBL_TEST001'\n" +
				"                            ) tc\n" +
				"                                    ON ( ti.indrelid = tc. oid )\n" +
				"          INNER JOIN (SELECT tc.oid,\n" +
				"                           nspname , -- schema_name\n" +
				"                           relname ,-- table_name\n" +
				"                           tc.relam,\n" +
				"                           ta.amname\n" +
				"                      FROM sys_class tc  \n" +
				"                      INNER JOIN sys_namespace  tn ON ( tc.relnamespace = tn.oid) \n" +
				"                      LEFT JOIN  sys_am  ta ON (tc.relam = ta.oid)\n" +
				"                      WHERE tc.relkind IN ('i','I')       AND   -- 表，分区表 \n" +
				"                            nspname = 'public' --AND           -- schema\n" +
				"                            --relname = 'TBL_TEST001'\n" +
				"                            ) td\n" +
				"        ON   (ti.indexrelid = td.oid)\n" +
				"        LEFT JOIN  sys_description tm\n" +
				"        ON (td.oid = tm.objoid)\n" +
				") tq\n" +
				"        ON (tp.attrelid =tq.taboid AND tp.attnum = tq.attnum )\n" +
				"        LEFT JOIN sys_description tz ON (  tz.objoid =tq.indexoid AND tz.objsubid = 0::oid )";
		List<Map<String,Object>> mapList=jdbcTemplate.queryForList(sql);
		for (int i = 0; i < mapList.size(); i++) {
			Map<String,Object> map=mapList.get(i);
			String pkTableName=String.valueOf(map.get("table_name"));
			if(pkTableName.equals(tableName)){
				return String.valueOf(map.get("attname"));
			}
		}
		return "";
	}
	//不分页查询
	public static List<Map<String,Object>> queryNotPage(String tableName,String where,JdbcTemplate jdbcTemplate){
		String sql = "select * from " + tableName + where; 
		return jdbcTemplate.queryForList(sql);
	}
	
	//单条更新
	public static Map<String,Object> updateOne(String tableName,JdbcTemplate jdbcTemplate,Map<String,Object> objMap){
		String sql="update "+tableName+" set ";
		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
		String type="";
		String pkFieldName="";
		if(DBCheck.isKingbase()){
			for(int i=0;i<mapList.size();i++) {
				Object objectField=mapList.get(i).get("column_name");
				Object objectValue=objMap.get(String.valueOf(objectField));
				if(objectValue!=null){
					if(StringUtil.isNotEmpty(mapList.get(i).get("key"))) {
						pkFieldName=String.valueOf(objectField);
					}else {
						boolean isdanyinhao=true;
						type=String.valueOf(mapList.get(i).get("data_type"));
						type=type.toLowerCase();//将字符串中的所有字符转换为小写字母
						if(type.indexOf("integer")>=0||type.indexOf("int")>=0||type.indexOf("bigint")>=0) {
							isdanyinhao=false;
						}
						if(isdanyinhao) {
							sql+=objectField+"='"+objectValue+"',";
						}else {
							if(StringUtil.isEmpty(objectValue)) {
								sql+=objectField+"=0,";
							}else {
								sql+=objectField+"="+objectValue+",";
							}

						}
					}
				}
			}
		}else{
			for(int i=0;i<mapList.size();i++) {
				Object objectField=mapList.get(i).get("Field");
				Object objectValue=objMap.get(String.valueOf(objectField));
				if(objectValue!=null){
					String key=String.valueOf(mapList.get(i).get("Key"));
					if(pkFieldName.equals("")&&key.length()>0&&key.equals("PRI")) {
						pkFieldName=String.valueOf(objectField);
					}else {
						type=String.valueOf(mapList.get(i).get("Type"));
						type=type.toLowerCase();//将字符串中的所有字符转换为小写字母
						boolean isdanyinhao=true;
						if(type.indexOf("bit")>=0||type.indexOf("int")>=0) {
							isdanyinhao=false;
						}
						if(isdanyinhao) {
							sql+=objectField+"='"+objectValue+"',";
						}else {
							if(StringUtil.isEmpty(objectValue)) {
								sql+=objectField+"=0,";
							}else {
								sql+=objectField+"="+objectValue+",";
							}
						}
					}

				}
			}
		}
		sql=sql.substring(0,sql.length()-1);
		sql+=" where "+pkFieldName+"="+objMap.get(pkFieldName);
		logger.info(sql);
		jdbcTemplate.execute(sql);
		return objMap;
		
	}
	//批量保存
	public static Map<String,Object> saveBatch(String tableName, List<Map<String,Object>> objMaps, JdbcTemplate jdbcTemplate, RedisPublication redisPublication, String wsCode, User user) {
		Map<String,Object> retMap=new HashMap<>();
		String[] sqls=new String[objMaps.size()];
		if(redisPublication!=null)redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"开始检测数据是否规范...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
		String insertSqlTemplate="insert into "+tableName+" (";
		Map<String,String> typeMap=new HashMap<String,String>();
		Map<String,Integer> widthMap=new HashMap<String,Integer>();
		List<String> fieldNameList=new ArrayList<String>();
		Map<String,Object> objMap=objMaps.get(0);
		Integer textLength=65535;//text最大长度，超过这个长度请设置longtext
		String rsColumnName="Field";
		String rsColumnDataType="Type";
		String rsColumnKeyType="Key";
		if(DBCheck.isKingbase()){
			rsColumnName="column_name";
			rsColumnDataType="data_type";
			rsColumnKeyType="key";
		}
		String sqlChar= DBCheck.getIdentifierQuoteChar();
		for(int k=0;k<mapList.size();k++) {
			String fieldName=String.valueOf(mapList.get(k).get(rsColumnName));
			String fieldType=String.valueOf(mapList.get(k).get(rsColumnDataType));
			if(objMap.get(fieldName)!=null){
				typeMap.put(fieldName, fieldType);
				insertSqlTemplate+=""+sqlChar+fieldName+sqlChar+",";
				fieldNameList.add(fieldName);
				if(fieldType.indexOf("(")>0) {
					String width=fieldType;
					width=width.substring(width.indexOf("(")+1,width.indexOf(")"));
					widthMap.put(fieldName, Integer.parseInt(width));
				}
				if(fieldType.equals("text")){
					widthMap.put(fieldName, textLength);
				}
			}
		}
		insertSqlTemplate=insertSqlTemplate.substring(0,insertSqlTemplate.length()-1);
		insertSqlTemplate+=") values (";
		for (int j = 0; j < objMaps.size(); j++) {
			objMap=objMaps.get(j);
			String sql=insertSqlTemplate;
			if(redisPublication!=null){
				if((j+1) % 500 == 0){
					redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"检测数据是否规范"+objMaps.size()+"条，当前检测到"+(j+1)+"条...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
				}
			}
			for(int i=0;i<fieldNameList.size();i++) {
				String fieldName=fieldNameList.get(i);
				String fieldValue=String.valueOf(objMap.get(fieldName));
				String type=typeMap.get(fieldName).toLowerCase();
				boolean isdanyinhao=true;
				if(type.indexOf("bit")>=0||type.indexOf("int")>=0) {
					isdanyinhao=false;
				}
				if(isdanyinhao) {
					sql+="'";
				}
				if(StringUtil.isEmpty(fieldValue)){
					if(isdanyinhao) {
						sql+="";
					}else{
						sql+="0";
					}
				}else {
					Integer fieldWidth=widthMap.get(fieldName);
					if(fieldWidth!=null&&fieldValue.length()>=fieldWidth){
						retMap.put("status","error");
						retMap.put("fieldName",fieldName);
						retMap.put("fieldValue",fieldValue);
						retMap.put("maxLength",fieldWidth);
						retMap.put("fieldLength",fieldValue.length());
						return retMap;
					}
					if(fieldWidth!=null&&String.valueOf(fieldWidth).equals(textLength+"")){
						Integer strLength=fieldValue.getBytes(StandardCharsets.UTF_8).length;
						if(strLength>=textLength){
							retMap.put("status","error");
							retMap.put("fieldName",fieldName);
							retMap.put("fieldValue",fieldValue);
							retMap.put("maxLength",fieldWidth);
							retMap.put("fieldLength",strLength);
							return retMap;
						}
					}
					if(fieldValue.endsWith("\\")){
						fieldValue+="\\";
					}
					fieldValue=fieldValue.replaceAll("\'","&quot;");
					sql+=fieldValue;
				}
				if(isdanyinhao) {
					sql+="',";
				}else {
					sql+=",";
				}
			}
			sql=sql.substring(0,sql.length()-1);
			sql+=")";
			if(j==0)logger.info("#####"+sql);
			sqls[j]=sql;
		}
		if(redisPublication!=null){
			redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"检测通过，开始执行入库...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
		}
		for (int i = 0; i < sqls.length; i++) {
			if(redisPublication!=null&&(i+1) % 100 == 0){
				redisPublication.sendMessage("{\"wsCode\":\""+wsCode+"\",\"message\":\"正在入库，总："+objMaps.size()+" 条，已入库："+(i+1)+" 条...\",\"status\":\"ok\",\"isHideMask\":\"N\"}",String.valueOf(user.getId()));
			}
			jdbcTemplate.execute(sqls[i]);
		}
		//jdbcTemplate.batchUpdate(sqls);
		retMap.put("status","ok");
		return retMap;
	}
	//批量更新
	public static List<Map<String,Object>> updateBatch(String tableName,JdbcTemplate jdbcTemplate,List<Map<String,Object>> objMaps){
		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
		String rsColumnName="Field";
		String rsColumnDataType="Type";
		String rsColumnKeyType="Key";
		if(DBCheck.isKingbase()){
			rsColumnName="column_name";
			rsColumnDataType="data_type";
			rsColumnKeyType="key";
		}
		String sqlChar= DBCheck.getIdentifierQuoteChar();
		String pkFieldName="";
		String[] sqls=new String[objMaps.size()];
		for (int j = 0; j < objMaps.size(); j++) {
			Map<String,Object> objMap=objMaps.get(j);
			String sql="update "+tableName+" set ";
			for(int i=0;i<mapList.size();i++) {
				String fieldName=String.valueOf(mapList.get(i).get(rsColumnName));
				Object fieldValue=objMap.get(fieldName);
				if(fieldValue!=null){
					String key=String.valueOf(mapList.get(i).get(rsColumnKeyType));
					if(pkFieldName.equals("")&&key.length()>0&&key.equals("PRI")) {
						pkFieldName=fieldName;
					}else {
						String fieldType=String.valueOf(mapList.get(i).get(rsColumnDataType));
						fieldType=fieldType.toLowerCase();
						boolean isdanyinhao=true;
						if(DBCheck.isKingbase()){
							if(fieldType.indexOf("integer")>=0||fieldType.indexOf("int")>=0||fieldType.indexOf("bigint")>=0) {
								isdanyinhao=false;
							}
						}else{
							if(fieldType.indexOf("bit")>=0||fieldType.indexOf("int")>=0) {
								isdanyinhao=false;
							}
						}
						if(isdanyinhao) {
							sql+=""+sqlChar+fieldName+sqlChar+"='"+fieldValue+"',";
						}else {
							if(StringUtil.isEmpty(fieldValue)) {
								sql+=""+sqlChar+fieldName+sqlChar+"=0,";
							}else {
								sql+=""+sqlChar+fieldName+sqlChar+"="+fieldValue+",";
							}
						}
					}
				}
			}
			sql=sql.substring(0,sql.length()-1);
			sql+=" where "+pkFieldName+"='"+objMap.get(pkFieldName)+"'";
			sqls[j]=sql;
		}
		jdbcTemplate.batchUpdate(sqls);
		return objMaps;
	}
	//单条查询
	public static Map<String,Object> findOne(String tableName,String pkId,JdbcTemplate jdbcTemplate){
		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
		String sql="select ";
		String pkIdType="long";
		String pkIdName=getPkFieldName(tableName, jdbcTemplate);
		if(DBCheck.isKingbase()){
			for(int i=0;i<mapList.size();i++) {
				String fieldName=String.valueOf(mapList.get(i).get("column_name"));
				if(pkIdName.equals(fieldName)) {
					if(String.valueOf(mapList.get(i).get("data_type")).indexOf("varchar")>=0) {
						pkIdType="String";
					}
				}
				if(i==mapList.size()-1) {
					sql+=fieldName;
				}else {
					sql+=fieldName+",";
				}
			}
		}else{
			for(int i=0;i<mapList.size();i++) {
				String fieldName=String.valueOf(mapList.get(i).get("Field"));
				if(pkIdName.equals(fieldName)) {
					if(String.valueOf(mapList.get(i).get("Type")).indexOf("varchar")>=0) {
						pkIdType="String";
					}
				}
				if(i==mapList.size()-1) {
					sql+=fieldName;
				}else {
					sql+=fieldName+",";
				}
			}
		}
		sql+=" from "+tableName+" where "+pkIdName+"=";
		if(pkIdType.equals("long")) {
			sql+=pkId;
		}else {
			sql+="'"+pkId+"'";
		}
		logger.info(sql);
		List<Map<String,Object>> resultMapList=jdbcTemplate.queryForList(sql);
		
		Map<String,Object> resultMap=new HashMap<String,Object>();
		if(resultMapList.size()>0) {
			resultMap=resultMapList.get(0);
		}
		return resultMap;
	}
	public static Map<String,Object> findOne(String tableName,String pkId,JdbcTemplate jdbcTemplate,String pkFileId){
		String sql="select *";
		sql+=" from "+tableName+" where "+pkFileId+"=";
		sql+="'"+pkId+"'";
		List<Map<String,Object>> resultMapList=jdbcTemplate.queryForList(sql);

		Map<String,Object> resultMap=new HashMap<String,Object>();
		if(resultMapList.size()>0) {
			resultMap=resultMapList.get(0);
		}
		return resultMap;
	}
	//有long型主键增加
	public static long addForLongPkId(String tableName,Map<String,Object> objMap,JdbcTemplate jdbcTemplate) {
		 KeyHolder keyHolder = new GeneratedKeyHolder();
	        PreparedStatementCreator preparedStatementCreator = con -> {
	        	String sql="insert into "+tableName+" (";
	    		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
	    		Map<String,String> typeMap=new HashMap<String,String>();
	    		List<Object> valueList=new ArrayList<Object>();
	    		String fieldKey="Field";
	    		String fieldType="Type";
				if(DBCheck.isKingbase()){
					fieldKey="column_name";
					fieldType="data_type";
				}
				String sqlChar= DBCheck.getIdentifierQuoteChar();
	    		for(int i=0;i<mapList.size();i++) {
					String fieldName=String.valueOf(mapList.get(i).get(fieldKey));
	    			if(objMap.get(fieldName)!=null){
	    				typeMap.put(fieldName, String.valueOf(mapList.get(i).get(fieldType)));
						sql+=""+sqlChar+fieldName+sqlChar+",";

						valueList.add(fieldName);
	    			}
	    		}
	    		sql=sql.substring(0,sql.length()-1);
	    		sql+=") values (";
	    		for(int i=0;i<valueList.size();i++) {
	    			boolean isdanyinhao=true;
	    			if(typeMap.get(valueList.get(i)).toLowerCase().indexOf("bit")>=0||typeMap.get(valueList.get(i)).toLowerCase().indexOf("int")>=0) {
	    				isdanyinhao=false;
	    			}
					if(isdanyinhao) {
						sql+="'";
					}
					if(objMap.get(valueList.get(i))==null||objMap.get(valueList.get(i)).toString().length()==0) {
						if(isdanyinhao) {
							sql+="";
						}else{
							sql+="0";
						}
					}else {
						String value=String.valueOf(objMap.get(valueList.get(i)));
						value=value.replaceAll("\'","&quot;");
						sql+=value;
					}
					if(isdanyinhao) {
						sql+="',";
					}else {
						sql+=",";
					}
	    		}
				sql=sql.substring(0,sql.length()-1);
				sql+=")";
				logger.info("#####"+sql);
	            java.sql.PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
	            return ps;
	        };
	        jdbcTemplate.update(preparedStatementCreator, keyHolder);
		try {
			keyHolder.getKey().longValue();
		} catch (Exception e) {
			return -1;
		}
		return keyHolder.getKey().longValue();
	}
	//无主键增加
	public static void add(String tableName,Map<String,Object> objMap,JdbcTemplate jdbcTemplate) {
		String sql="insert into "+tableName+" (";
		List<Map<String,Object>> mapList=getAllCommentsByTableName(tableName, jdbcTemplate);
		Map<String,String> typeMap=new HashMap<String,String>();
		List<String> fieldNameList=new ArrayList<String>();
		for(int i=0;i<mapList.size();i++) {
			String fieldName=String.valueOf(mapList.get(i).get("Field"));
			if(StringUtil.isNotEmpty(fieldName)){
				typeMap.put(fieldName, String.valueOf(mapList.get(i).get("Type")));
				if(i<mapList.size()-1) {
					sql+=fieldName+",";
				}else {
					sql+=fieldName+") values (";
				}
				fieldNameList.add(fieldName);
			}
		}
		for(int i=0;i<fieldNameList.size();i++) {
			String fieldName=fieldNameList.get(i);
			String fieldValue=String.valueOf(objMap.get(fieldName));
			boolean isdanyinhao=true;
			if(typeMap.get(fieldName).toLowerCase().indexOf("int")>=0) {
				isdanyinhao=false;
			}
			if(i<fieldNameList.size()-1) {
				if(isdanyinhao) {
					sql+="'";
				}
				sql+=fieldValue;
				if(isdanyinhao) {
					sql+="',";
				}else {
					sql+=",";
				}
			}else {
				sql+="'"+fieldValue+"')";
			}
		}
		System.out.println(sql);
		jdbcTemplate.execute(sql);
	}
	//set save 一些默认值
	public static void setDefaultValue(Map<String,Object> dataMap,User user){
		DateTime d = new DateTime();
		String time=d.toString("yyyy-MM-dd HH:mm:ss");
		String date=d.toString("yyyy-MM-dd");
		if(StringUtil.isEmpty(dataMap.get("status"))){
			dataMap.put("status","0");
		}
		if(StringUtil.isEmpty(dataMap.get("create_time"))){
			dataMap.put("create_time", time);
			dataMap.put("create_date", date);
			dataMap.put("update_time", time);
			dataMap.put("update_date", date);
		}
		if(StringUtil.isEmpty(dataMap.get("uuid"))){
			dataMap.put("uuid", UUIDUtils.randomUuidWithoutHyphen());
		}
		if(user!=null){
			dataMap.put("create_user_id",user.getId());
			dataMap.put("create_org_id",user.getOrgId());
			dataMap.put("create_org_name",user.getOrgName());
			dataMap.put("create_user",user.getUserName());
		}else{
			if(StringUtil.isEmpty(dataMap.get("create_user_id"))){
				dataMap.put("create_user_id","");
			}
			if(StringUtil.isEmpty(dataMap.get("create_org_id"))){
				dataMap.put("create_org_id","");
			}
			if(StringUtil.isEmpty(dataMap.get("create_org_name"))){
				dataMap.put("create_org_name","");
			}
			if(StringUtil.isEmpty(dataMap.get("create_user"))){
				dataMap.put("create_user","");
			}
		}
	}

	//set update 一些默认值
	public static void setUpdateDefaultValue(Map<String,Object> dataMap,User user){
		DateTime d = new DateTime();
		String time=d.toString("yyyy-MM-dd HH:mm:ss");
		String date=d.toString("yyyy-MM-dd");
		if(StringUtil.isEmpty(dataMap.get("update_time"))){
			dataMap.put("update_time", time);
			dataMap.put("update_date", date);
		}
		if(user!=null){
			dataMap.put("update_user",user.getUserName());
		}
	}


}
