package com.smtscript.lib.jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Base64;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.mozilla.javascript.Context;
import org.mozilla.javascript.NativeArray;
import org.mozilla.javascript.NativeFunction;
import org.mozilla.javascript.NativeObject;
import org.mozilla.javascript.Undefined;


import com.smtscript.lib.JSComment;
import com.smtscript.lib.JSStaticJDBC;
import com.smtscript.utils.SMTStatic;

public class ScriptJDBC 
{
	enum MergeRecordType
	{
		INSERT,
		UPDATE,
		SKIP,
		SAME,
	};
	
	private class MergeRecordValue
	{
		public MergeRecordType 	_mergeType;
		public NativeObject		_nvKey;
		public NativeObject		_nvValue;
		public NativeObject		_nvUpdate;
		
		public MergeRecordValue(NativeObject nvKey, NativeObject nvValue)
		{
			_mergeType = MergeRecordType.INSERT;
			_nvValue = nvValue;
			_nvKey = nvKey;
		}
	}

	protected static Class<?> _clzJarRootClass = ScriptJDBC.class;
	protected static Map<String, String> _mapDriver2URL = new HashMap<String, String>();
	protected boolean _rollback = false;
	protected int _tranCount = 0;
	protected Connection _conn;
	protected JSStaticJDBC _parentJDBC;
	protected String	_dbJarName;
	protected String	_dbDriver;
	protected String	_dbUrl;
	protected String	_dbUser;
	protected String	_dbPass;

	public ScriptJDBC(JSStaticJDBC parentJDBC, NativeObject nvConfig) throws Exception
	{
		String user;
		String pass;
		_parentJDBC = parentJDBC;
		String driver = (String) SMTStatic.getJSValue(nvConfig, "driver");
		String rawJarName = (String) SMTStatic.getJSValue(nvConfig, "jar", null);
		String jarName = (String) SMTStatic.getJSValue(nvConfig, "jar", driver + ".jar");
		String url = (String) SMTStatic.getJSValue(nvConfig, "url");
		user = (String) SMTStatic.getJSValue(nvConfig, "user");
		pass = (String) SMTStatic.getJSValue(nvConfig, "pass");


		try
		{
			Class.forName(driver);
		}
		catch(ClassNotFoundException ex)
		{
			if(_mapDriver2URL.containsKey(driver))
				throw new Exception("");
			
			File path = _parentJDBC.__parentScope__().__libPath__();
			File jarPath = new File(path, jarName);
			URLClassLoader classLoader = (URLClassLoader)Thread.currentThread().getContextClassLoader();
			if(jarPath.exists())
			{
				_mapDriver2URL.put(driver, jarPath.getAbsolutePath());
				SMTStatic.loadJarFiles(new File[]{jarPath}, classLoader);
			}
			else
			{
				jarPath = new File(jarName);
				if(!jarPath.isAbsolute())
					jarPath = new File(path, "jar/" + jarName);
				if(jarPath.exists())
				{
					_mapDriver2URL.put(driver, jarPath.getAbsolutePath());
					SMTStatic.loadJarFiles(new File[]{jarPath}, classLoader);
				}
				else 
				{
					URL jarURL = _clzJarRootClass.getResource("/" + jarName);
					if(jarURL != null)
					{
						_mapDriver2URL.put(driver, jarURL.toString());
						SMTStatic.loadJarFiles(new URL[]{jarURL}, classLoader);
					}
					else
					{
						throw new Exception("can't find jdbc driver from path / or /jar or res : " +  jarName + " or file : " + jarPath.getAbsolutePath());
					}
				}
			}
			Class.forName(driver);
			path = null;
		}
		_dbUrl = url;
		_dbUser = user;
		_dbPass = pass;
		_dbDriver = driver;
		_dbJarName = rawJarName;
		
		_conn = DriverManager.getConnection(url, user, pass);
	}
	
	public NativeObject getConnInfo()
	{
		NativeObject nvConn = new NativeObject();
		SMTStatic.putJSNotNullValue(nvConn, "driver", _dbDriver);
		SMTStatic.putJSNotNullValue(nvConn, "url", _dbUrl);
		SMTStatic.putJSNotNullValue(nvConn, "user", _dbUser);
		SMTStatic.putJSNotNullValue(nvConn, "pass", _dbPass);
		if(!SMTStatic.isNullOrEmpty(_dbJarName))
			SMTStatic.putJSNotNullValue(nvConn, "jar", _dbJarName);
		
		return nvConn;
	}
	
	public Connection __getConnection__()
	{
		return _conn;
	}
	
	public NativeObject getDriverList()
	{
		NativeObject nvRet = new NativeObject();
		for(Entry<String, String> entry : _mapDriver2URL.entrySet())
		{
			SMTStatic.putJSNotNullValue(nvRet, entry.getKey(), entry.getValue());
		}
		
		return nvRet;
	}

	public void close()
	{
		if(_conn != null)
		{
			try {
				if(_tranCount > 0)
				{
					_conn.rollback();
					_tranCount = 0;
				}
				
				_conn.close();
			} catch (SQLException e) {
			}
			_conn = null;
		}
	}
	
	public void beginTran() throws Exception
	{
		if(_tranCount == 0)
		{
			_rollback = false;
			_conn.setAutoCommit(false);
		}
		_tranCount ++;
	}
	
	public void commitTran() throws Exception
	{
		_tranCount --;
		if(_tranCount <= 0)
		{
			_tranCount = 0;
			if(_rollback)
				_conn.rollback();
			else
				_conn.commit();
		}
	}
	
	public void rollbackTran() throws Exception
	{
		_tranCount --;
		if(_tranCount <= 0)
		{
			_tranCount = 0;
			_conn.rollback();
		}
		else
		{
			_rollback = true;
		}
	}

	public void executeSQL(String sql, Object[] args) throws Exception
	{
		PreparedStatement stmt = _conn.prepareStatement(sql);
		try
		{
			if(args != null)
			{
				for(int i = 0; i < args.length; i ++)
				{
					Object arg = args[i];
					if(arg != null && arg instanceof Date)
						arg = new Timestamp(((Date)arg).getTime());
					
					stmt.setObject(i + 1, arg);
				}
			}
			stmt.execute();
		}
		catch(Exception ex)
		{
			StringBuilder sb = new StringBuilder();
			sb.append("error SQL : " + sql + "\r\n");
			if(args != null)
			{
				for(int i = 0; i < args.length; i ++)
				{
					Object arg = args[i];
					if(arg == null || arg instanceof Undefined)
						sb.append(String.format("    %d : NULL\r\n", i));
					else
						sb.append(String.format("    %d : %s = %s\r\n", i, arg.getClass().toString(), arg.toString()));
				}
			}
			throw new Exception(sb.toString(), ex);
		}
		finally
		{
			stmt.close();
		}
	}
	
	public static void __setJDBCRootClass(Class<?> clz)
	{
		_clzJarRootClass = clz;
	}
	
	public static NativeArray __queryRSToVALUEJS__(ResultSet rs) throws Exception
	{
		List<NativeObject> list = new ArrayList<NativeObject>();
		ResultSetMetaData meta = rs.getMetaData();
		int colCount = meta.getColumnCount();
		String[] names = new String[colCount];
		for(int i = 0; i < colCount; i ++)
		{
			names[i] = meta.getColumnLabel(i + 1).toUpperCase();
		}
		
		while(rs.next())
		{
			NativeObject nv = new NativeObject();
			for(int i = 0; i < colCount; i ++)
			{
				Object value = rs.getObject(i + 1);
				if(value != null)
				{
					if("oracle.sql.CLOB".equals(value.getClass().getName()))
					{
						Reader data = (Reader) value.getClass().getMethod("getCharacterStream").invoke(value);
						BufferedReader br = new BufferedReader(data);
						try
						{
							StringBuilder sb = new StringBuilder();
							char[] buf = new char[10240];
							int size;
							while((size = br.read(buf)) > 0)
							{
								sb.append(buf, 0, size);
							}
							value = sb.toString();
						}
						finally
						{
							br.close();
						}
					}
					else if("com.ibm.db2.jcc.am.le".equals(value.getClass().getName()))
					{
						Reader reader = rs.getClob(i + 1).getCharacterStream();
						//InputStream data = rs.getBlob(i + 1).getBinaryStream();
						BufferedReader br = new BufferedReader(reader);
						try
						{
							StringBuilder sb = new StringBuilder();
							char[] buf = new char[10240];
							int size;
							while((size = br.read(buf)) > 0)
							{
								sb.append(buf, 0, size);
							}
							value = sb.toString();
						}
						finally
						{
							br.close();
						}
					}
					else if(value instanceof BigDecimal)
					{
						value = ((BigDecimal)value).doubleValue();
					}
					else if(value instanceof Timestamp)
					{
						value = SMTStatic.toString(new Date(((Timestamp)value).getTime()));
					}
					else if(value instanceof byte[])
					{
						value = Base64.getEncoder().encodeToString((byte[])value);
					}
					SMTStatic.putJSNotNullValue(nv, names[i], value);
				}
			}
			list.add(nv);
		}
		
		return new NativeArray(list.toArray(new NativeObject[list.size()]));
	}
	
	public NativeArray querySQL(String sql, Object[] args)throws Exception
	{
		PreparedStatement stmt = _conn.prepareStatement(sql);
		try
		{
			if(args != null)
			{
				for(int i = 0; i < args.length; i ++)
				{
					Object arg = args[i];
					if(arg instanceof Date)
						arg = new Timestamp(((Date)arg).getTime());
					stmt.setObject(i + 1, arg);
				}
			}
			ResultSet rs = stmt.executeQuery();
			
			try
			{
				return __queryRSToVALUEJS__(rs);
			}
			finally
			{
				rs.close();
			}
		}
		finally
		{
			stmt.close();
		}
		
	}
	
	public static Object SQLVStrToObject(String SQLVStr) throws Exception
	{
		char cType = SQLVStr.charAt(0);
		SQLVStr = SQLVStr.length() > 1 ? SQLVStr.substring(1) : "";
		
		switch(cType)
		{
		case 'N':
			return null;
			
		case 'S':
			return SQLVStr;
			
		case 'D':
			return Double.parseDouble(SQLVStr);
			
		case 'I':
			return Integer.parseInt(SQLVStr);
			
		case 'L':
			return Long.parseLong(SQLVStr);
			
		case 'T':
			return new java.sql.Timestamp(SMTStatic.toDate(SQLVStr).getTime());
			
		case 'B':
			return Base64.getDecoder().decode(SQLVStr);
			
		default:
			throw new Exception("unsupport type " + cType + " = " + SQLVStr);
		}
	}
	
	public static String ObjectToSQLVStr(Object sqlValue) throws Exception
	{
		if(sqlValue == null)
		{
			return "N";
		}
		else if(sqlValue instanceof Integer)
		{
			return "I" + ((Integer)sqlValue);
		}
		else if(sqlValue instanceof Long)
		{
			return "L" + ((Long)sqlValue);
		}
		else if(sqlValue instanceof String)
		{
			return "S" + ((String)sqlValue);
		}
		else if("oracle.sql.CLOB".equals(sqlValue.getClass().getName()))
		{
			Reader data = (Reader) sqlValue.getClass().getMethod("getCharacterStream").invoke(sqlValue);
			BufferedReader br = new BufferedReader(data);
			
			StringBuilder sb = new StringBuilder();
			char[] buf = new char[10240];
			int size;
			while((size = br.read(buf)) > 0)
			{
				sb.append(buf, 0, size);
			}
			return "S" + sb.toString();
		}
		else if(sqlValue instanceof BigDecimal)
		{
			return "D" + sqlValue.toString();
		}
		else if(sqlValue instanceof Double)
		{
			return "D" + SMTStatic.toString(sqlValue);
		}
		else if(sqlValue instanceof java.sql.Timestamp)
		{
			return "T" + SMTStatic.toString(new Date(((java.sql.Timestamp)sqlValue).getTime()));
		}
		else
		{
			throw new Exception("unsupport sql type : " + sqlValue.getClass() + " = " + sqlValue.toString());
		}
	}
	
	public void executeSQLVStr(String sql, String[] args) throws Exception
	{
		PreparedStatement stmt = __getConnection__().prepareStatement(sql);
		try
		{
			for(int i = 0; i < args.length; i ++)
			{
				stmt.setObject(i + 1, SQLVStrToObject(args[i]));
			}
			stmt.execute();
		}
		catch(Exception ex)
		{
			StringBuilder sb = new StringBuilder();
			sb.append("error SQL : " + sql + "\r\n");
			for(int i = 0; i < args.length; i ++)
			{
				Object arg = args[i];
				if(arg == null || arg instanceof Undefined)
					sb.append(String.format("    %d : NULL\r\n", i));
				else
					sb.append(String.format("    %d : %s = %s\r\n", i, arg.getClass().toString(), arg.toString()));
			}
			throw new Exception(sb.toString(), ex);
		}
		finally
		{
			stmt.close();
		}
	}
	
	private Object[] parseSQLFmt(String sqlFmt, final NativeObject nvParams, StringBuilder r_sql)
	{
		final List<Object> params = new ArrayList<Object>();
		r_sql.append(SMTStatic.stringFormat(sqlFmt, new SMTStatic.StringNamedNotify(){

			@Override
			public Object getNamedValue(String name, Object[] args) throws Exception {
				Object value = SQLVStrToObject((String)SMTStatic.getJSValue(nvParams, name));
				params.add(value);
				return "?";
			}
			
		}));
		
		return params.toArray(new Object[params.size()]);
	}
	
	public void querySQLFmtNotify(String sqlFmt, NativeObject nvParams, NativeFunction func)throws Exception
	{
		StringBuilder sbSQL = new StringBuilder();
		Object[] args = parseSQLFmt(sqlFmt, nvParams, sbSQL);
		
		final Context cx = _parentJDBC.__parentScope__().__runtime__().entryContext();
		
		try
		{
			PreparedStatement stmt = _conn.prepareStatement(sbSQL.toString());
			try
			{
				for(int i = 0; i < args.length; i ++)
				{
					stmt.setObject(i + 1, args[i]);
				}
				ResultSet rs = stmt.executeQuery();
				
				try
				{
					ResultSetMetaData meta = rs.getMetaData();
					int colCount = meta.getColumnCount();
					String[] names = new String[colCount];
					for(int i = 0; i < colCount; i ++)
					{
						names[i] = meta.getColumnLabel(i + 1).toUpperCase();
					}
					
					while(rs.next())
					{
						NativeObject nv = new NativeObject();
						for(int i = 0; i < colCount; i ++)
						{
							Object value = rs.getObject(i + 1);
							if(value != null)
							{
								if("oracle.sql.CLOB".equals(value.getClass().getName()))
								{
									Reader data = (Reader) value.getClass().getMethod("getCharacterStream").invoke(value);
									BufferedReader br = new BufferedReader(data);
									try
									{
										StringBuilder sb = new StringBuilder();
										char[] buf = new char[10240];
										int size;
										while((size = br.read(buf)) > 0)
										{
											sb.append(buf, 0, size);
										}
										value = sb.toString();
									}
									finally
									{
										br.close();
									}
								}
								else if("com.ibm.db2.jcc.am.le".equals(value.getClass().getName()))
								{
									Reader reader = rs.getClob(i + 1).getCharacterStream();
									//InputStream data = rs.getBlob(i + 1).getBinaryStream();
									BufferedReader br = new BufferedReader(reader);
									try
									{
										StringBuilder sb = new StringBuilder();
										char[] buf = new char[10240];
										int size;
										while((size = br.read(buf)) > 0)
										{
											sb.append(buf, 0, size);
										}
										value = sb.toString();
									}
									finally
									{
										br.close();
									}
								}
								else if(value instanceof BigDecimal)
								{
									value = ((BigDecimal)value).doubleValue();
								}
								else if(value instanceof Timestamp)
								{
									value = SMTStatic.toString(new Date(((Timestamp)value).getTime()));
								}
								else if(value instanceof byte[])
								{
									value = Base64.getEncoder().encodeToString((byte[])value);
								}
								SMTStatic.putJSNotNullValue(nv, names[i], value);
							}
						}
						func.call(cx, func, null, new Object[]{nv});
					}
				}
				finally
				{
					rs.close();
				}
			}
			finally
			{
				stmt.close();
			}

		}
		finally
		{
			Context.exit();
		}

	}
	
	public NativeArray querySQLFmt(String sqlFmt, NativeObject nvParams)throws Exception
	{
		StringBuilder sbSQL = new StringBuilder();
		Object[] params = parseSQLFmt(sqlFmt, nvParams, sbSQL);
		return querySQL(sbSQL.toString(), params);
	}

	public void executeSQLFmt(String sqlFmt, NativeObject nvParams)throws Exception
	{
		StringBuilder sbSQL = new StringBuilder();
		Object[] params = parseSQLFmt(sqlFmt, nvParams, sbSQL);
		this.executeSQL(sbSQL.toString(), params);
	}
	
	public static NativeArray __queryRSToVSTRJS__(ResultSet rs) throws Exception
	{
		List<NativeObject> list = new ArrayList<NativeObject>();
		ResultSetMetaData meta = rs.getMetaData();
		int colCount = meta.getColumnCount();
		String[] names = new String[colCount];
		for(int i = 0; i < colCount; i ++)
		{
			names[i] = meta.getColumnLabel(i + 1).toUpperCase();
		}
		
		while(rs.next())
		{
			NativeObject nv = new NativeObject();
			for(int i = 0; i < colCount; i ++)
			{
				Object value = rs.getObject(i + 1);
				String str = ObjectToSQLVStr(value);
					
				SMTStatic.putJSNotNullValue(nv, names[i], str);
			}
			list.add(nv);
		}
		
		return new NativeArray(list.toArray(new NativeObject[list.size()]));
	}
	
	public NativeArray querySQLVStr(String sql, String[] args)throws Exception
	{
		PreparedStatement stmt = __getConnection__().prepareStatement(sql);
		try
		{
			for(int i = 0; i < args.length; i ++)
			{
				stmt.setObject(i + 1, SQLVStrToObject(args[i]));
			}
			ResultSet rs = stmt.executeQuery();
			try
			{
				return __queryRSToVSTRJS__(rs);
			}
			finally
			{
				rs.close();
			}
		}
		finally
		{
			stmt.close();
		}
		
	}

	public ScriptJDBCMetaData getMetaData() throws Exception
	{
		return new ScriptJDBCMetaData(_conn);
	}
	
	public NativeArray getQueryFieldNames(String sql) throws Exception
	{
		List<String> listNames = new ArrayList<String>();
		PreparedStatement stmt = _conn.prepareStatement(sql);
		try
		{
			ResultSet rs = stmt.executeQuery();
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int fieldCount = rsMetaData.getColumnCount();
			for(int i = 1; i <= fieldCount; i ++)
			{
				String colName = rsMetaData.getColumnName(i).toUpperCase();
				listNames.add(colName);
			}
		}
		finally
		{
			stmt.close();
		}
		return new NativeArray(listNames.toArray(new String[listNames.size()]));
	}
	
	protected String convCRLF(String value, String conv)
	{
		if(SMTStatic.isNullOrEmpty(conv))
			return value;
		return value.replace("\r\n", "\n").replace("\n", conv);
	}
	
	@JSComment(
		"<per>\n" + 
		"@param nvConfig \n" +
		"    table   - String : table name\n" +
		"    query   - String : query string, if not exist then use table\n" +
		"    mode    - String : UPDATE/SKIP\n" +
		"    execute - Boolean: true : UPDATE, false : check(default)\n" +
		"    records - Map    : List<Map<field : value>> \n" +
		"    fields  - Map    : Map<field : mode> : mode : KEY/UPDATE/SKIP\n" +
		"	 unexec  - Array[]: String[] : skip operate\n" +
		"    convcrlf- String : CR/LF mode\n" +
		"</per>")	
	public NativeArray mergeRecords(NativeObject nvConfig) throws Exception
	{
		String convcrlf = (String) SMTStatic.getJSValue(nvConfig, "convcrlf", "\n");
		String tableName = (String) SMTStatic.getJSValue(nvConfig, "table");
		String queryName = (String) SMTStatic.getJSValue(nvConfig, "query", tableName);
		String mode = (String) SMTStatic.getJSValue(nvConfig, "mode");
		boolean execute = (Boolean) SMTStatic.getJSValue(nvConfig, "execute", false);
		NativeObject nvFields = (NativeObject)SMTStatic.getJSValue(nvConfig, "fields");
		NativeArray  arrRecords = (NativeArray)SMTStatic.getJSValue(nvConfig, "records");
		NativeArray  arrUnexec = (NativeArray)SMTStatic.getJSValue(nvConfig, "unexec", null);
		List<NativeObject> delKeyList = new ArrayList<NativeObject>();
		Map<String, MergeRecordValue> mapKeyToMergeType = new LinkedHashMap<String, MergeRecordValue>();
		Map<String, Integer> mapFieldNameToIndex = null;
		List<String> keyFields = new ArrayList<String>();
		List<String> updateFields = new ArrayList<String>();
		List<String> skipFields = new ArrayList<String>();
		List<String> insertFields = new ArrayList<String>();
		Set<String> setUnexec = new HashSet<String>();
		
		// 设置不操作的指令
		if(arrUnexec != null)
		{
			for(int index = 0; index < arrUnexec.size(); index ++)
			{
				setUnexec.add((String)arrUnexec.get(index));
			}
		}
		
		// 首先生成查询条件语句
		for(Entry<Object, Object> entry : nvFields.entrySet())
		{
			String fieldName = (String)entry.getKey().toString(); 
			String fieldMode = (String)entry.getValue(); 
			if("KEY".equals(fieldMode))
			{
				keyFields.add(fieldName);
			}
			else if("UPDATE".equals(fieldMode))
			{
				updateFields.add(fieldName);
			}
			else if("SKIP".equals(fieldMode))
			{
				skipFields.add(fieldName);
			}
			else
				throw new Exception("unsupport field mode : " + fieldMode);
			
			insertFields.add(fieldName);
		}
		
		// 编译所有输入的记录，导入到mapKeyToMergeType中
		if(keyFields.size() > 0)
		{
			StringBuilder sbKey = new StringBuilder();
			for(int row = 0; row < arrRecords.size(); row ++)
			{
				sbKey.setLength(0);
				NativeObject nvRecord = (NativeObject) arrRecords.get(row);
				NativeObject nvKey = new NativeObject();
				NativeObject nvValue = new NativeObject();
				for(String keyField : keyFields)
				{
					String value = convCRLF((String) SMTStatic.getJSValue(nvRecord, keyField), convcrlf);
					SMTStatic.putJSNotNullValue(nvKey, keyField, value);
					sbKey.append(SMTStatic.toCStr(value) + "\n");
				}
				for(Entry<Object, Object> entry : nvFields.entrySet())
				{
					String fieldName = (String)entry.getKey().toString(); 
					String value = convCRLF((String) SMTStatic.getJSValue(nvRecord, fieldName), convcrlf);
					SMTStatic.putJSNotNullValue(nvValue, fieldName, value);
				}
				mapKeyToMergeType.put(sbKey.toString(), new MergeRecordValue(nvKey, nvValue));
			}
		
			// 查询数据库中的数据
			PreparedStatement stmt = _conn.prepareStatement("SELECT * FROM " + queryName + " T ");
			try
			{
				ResultSet rs = stmt.executeQuery();
				try
				{
					while(rs.next())
					{
						// 读取字段名
						if(mapFieldNameToIndex == null)
						{
							mapFieldNameToIndex = new LinkedHashMap<String, Integer>();
							ResultSetMetaData rsMetaData = rs.getMetaData();
							int fieldCount = rsMetaData.getColumnCount();
							for(int i = 1; i <= fieldCount; i ++)
							{
								String fieldName = rsMetaData.getColumnName(i).toUpperCase();
								mapFieldNameToIndex.put(fieldName, i);
							}
						}
						
						// 读取主键值
						sbKey.setLength(0);
						NativeObject nvKeyToValues = new NativeObject();
						for(String keyField : keyFields)
						{
							Integer fieldIndex = mapFieldNameToIndex.get(keyField);
							if(fieldIndex == null)
								throw new Exception("can't find filed : " + keyField);
							
							String sValue = convCRLF(ObjectToSQLVStr(rs.getObject(fieldIndex)), convcrlf);;
							SMTStatic.putJSNotNullValue(nvKeyToValues, keyField, sValue);
							sbKey.append(SMTStatic.toCStr(sValue) + "\n");
						}
						
						// 根据key找到对应的数据
						MergeRecordValue mergeValue = mapKeyToMergeType.get(sbKey.toString());
						
						// 如果不存在当前key, 则加入要删除的记录
						if(mergeValue == null)
						{
							delKeyList.add(nvKeyToValues);
							continue;
						}
						
						// 如果当前是更新，则对比计算值, 如果当前记录和数据库中不一致，则触发更新
						if("UPDATE".equals(mode) || "SKIP".equals(mode))
						{
							NativeObject nvUpdate = null;
							for(String updateField : updateFields)
							{
								Integer fieldIndex = mapFieldNameToIndex.get(updateField);
								if(fieldIndex == null)
									throw new Exception("can't find filed : " + updateField);	
								String sDBValue = convCRLF(ObjectToSQLVStr(rs.getObject(fieldIndex)), convcrlf);
								String sNVValue = convCRLF((String) SMTStatic.getJSValue(mergeValue._nvValue, updateField), convcrlf);
								if(!sDBValue.equals(sNVValue))
								{
									if(nvUpdate == null)
										nvUpdate = new NativeObject();
									SMTStatic.putJSNotNullValue(nvUpdate, updateField, sDBValue);
								}
							}
							
							// 如果发现不同，则标记为更新
							if(nvUpdate != null)
							{
								mergeValue._nvUpdate = nvUpdate;
								if("UPDATE".equals(mode))
									mergeValue._mergeType = MergeRecordType.UPDATE;
								else
									mergeValue._mergeType = MergeRecordType.SKIP;
							}
							// 如果未发现不同，则标记为相同
							else
							{
								mergeValue._mergeType = MergeRecordType.SAME;
							}
							continue;
						}
						
						// 如果设置模式错误，则抛异常
						throw new Exception("unsupport mode : " + mode);
					}
				}
				finally
				{
					rs.close();
				}
			}
			finally
			{
				stmt.close();
			}
		}
		
		// 生成返回的NaviteObject
		{
			StringBuilder sbSQL = new StringBuilder();
			StringBuilder sbValue = new StringBuilder();
			List<String> params = new ArrayList<String>();
			List<NativeObject> listRet = new ArrayList<NativeObject>();
			for(MergeRecordValue mergeRecord : mapKeyToMergeType.values())
			{
				NativeObject nvInsert = new NativeObject();
				if(mergeRecord._mergeType == MergeRecordType.INSERT)
				{
					SMTStatic.putJSNotNullValue(nvInsert, "type", "INSERT");
					SMTStatic.putJSNotNullValue(nvInsert, "keys", mergeRecord._nvKey);
					SMTStatic.putJSNotNullValue(nvInsert, "values", mergeRecord._nvValue);
					
					if(setUnexec.contains("INSERT"))
					{
						SMTStatic.putJSNotNullValue(nvInsert, "skip", true);
					}
					else if(execute)
					{
						sbSQL.setLength(0);
						sbValue.setLength(0);
						params.clear();
						for(String insertField : insertFields)
						{
							if(sbSQL.length() > 0)
							{
								sbSQL.append(",");
								sbValue.append(",");
							}
							sbSQL.append(insertField);
							sbValue.append("?");
							params.add((String)SMTStatic.getJSValue(mergeRecord._nvValue, insertField));
						}
						
						PreparedStatement stmt = _conn.prepareStatement("INSERT INTO " + tableName + " (" + sbSQL.toString() + ")VALUES(" + sbValue.toString() + ")");
						try
						{
							for(int i = 0; i < params.size(); i ++)
							{
								stmt.setObject(i + 1, SQLVStrToObject(params.get(i)));
							}
							
							stmt.execute();
						}
						finally
						{
							stmt.close();
						}
						
					}
				}
				else if(mergeRecord._mergeType == MergeRecordType.UPDATE)
				{
					SMTStatic.putJSNotNullValue(nvInsert, "type", "UPDATE");
					SMTStatic.putJSNotNullValue(nvInsert, "keys", mergeRecord._nvKey);
					SMTStatic.putJSNotNullValue(nvInsert, "values", mergeRecord._nvValue);
					SMTStatic.putJSNotNullValue(nvInsert, "updates", mergeRecord._nvUpdate);
					
					if(setUnexec.contains("UPDATE"))
					{
						SMTStatic.putJSNotNullValue(nvInsert, "skip", true);
					}
					else if(execute)
					{
						sbSQL.setLength(0);
						sbValue.setLength(0);
						params.clear();
						
						for(Entry<Object, Object> entry : mergeRecord._nvUpdate.entrySet())
						{
							if(sbSQL.length() > 0)
								sbSQL.append(",");
							String fieldName = (String) entry.getKey();
							sbSQL.append(fieldName);
							sbSQL.append("=?");
							params.add((String)SMTStatic.getJSValue(mergeRecord._nvValue, fieldName));
						}
						
						for(Entry<Object, Object> entry : mergeRecord._nvKey.entrySet())
						{
							if(sbValue.length() > 0)
								sbValue.append(" AND ");
							String fieldName = (String) entry.getKey();
							sbValue.append(fieldName);
							sbValue.append("=?");
							params.add((String)SMTStatic.getJSValue(mergeRecord._nvKey, fieldName));
						}	
						
						PreparedStatement stmt = _conn.prepareStatement("UPDATE " + tableName + " SET " + sbSQL.toString() + " WHERE " + sbValue.toString());
						try
						{
							for(int i = 0; i < params.size(); i ++)
							{
								stmt.setObject(i + 1, SQLVStrToObject(params.get(i)));
							}
							
							stmt.execute();
						}
						finally
						{
							stmt.close();
						}
					}
				}
				else if(mergeRecord._mergeType == MergeRecordType.SKIP)
				{
					SMTStatic.putJSNotNullValue(nvInsert, "type", "SKIP");
					SMTStatic.putJSNotNullValue(nvInsert, "keys", mergeRecord._nvKey);
					SMTStatic.putJSNotNullValue(nvInsert, "values", mergeRecord._nvValue);
					SMTStatic.putJSNotNullValue(nvInsert, "updates", mergeRecord._nvUpdate);
				}
				else if(mergeRecord._mergeType == MergeRecordType.SAME)
				{
					SMTStatic.putJSNotNullValue(nvInsert, "type", "SAME");
					SMTStatic.putJSNotNullValue(nvInsert, "keys", mergeRecord._nvKey);
					SMTStatic.putJSNotNullValue(nvInsert, "values", mergeRecord._nvValue);
				}
				else
					continue;
				listRet.add(nvInsert);
			}
			
			for(NativeObject nvDelKey : delKeyList)
			{
				NativeObject nvDelete = new NativeObject();
				SMTStatic.putJSNotNullValue(nvDelete, "type", "DELETE");
				SMTStatic.putJSNotNullValue(nvDelete, "keys", nvDelKey);
				listRet.add(nvDelete);
				
				if(setUnexec.contains("DELETE"))
				{
					SMTStatic.putJSNotNullValue(nvDelete, "skip", true);
				}
				else if(execute)
				{
					sbValue.setLength(0);
					params.clear();
					for(Entry<Object, Object> entry : nvDelKey.entrySet())
					{
						if(sbValue.length() > 0)
							sbValue.append(" AND ");
						String fieldName = (String) entry.getKey();
						String fieldValue = (String)entry.getValue();
						sbValue.append(fieldName);
						sbValue.append("=?");
						params.add(fieldValue);
						
						PreparedStatement stmt = _conn.prepareStatement("DELETE FROM " + tableName + " WHERE " + sbValue.toString());
						try
						{
							for(int i = 0; i < params.size(); i ++)
							{
								stmt.setObject(i + 1, SQLVStrToObject(params.get(i)));
							}
							
							stmt.execute();
						}
						finally
						{
							stmt.close();
						}
					}
				}
			}
			
			return new NativeArray(listRet.toArray(new NativeObject[listRet.size()]));
		}		
	}
}