package com.taover.repository;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.annotation.Resource;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.taover.repository.annotation.TableSharding;
import com.taover.repository.annotation.TableShardingType;
import com.taover.repository.exception.MultiRowException;
import com.taover.repository.exception.NoShardingKeyException;
import com.taover.repository.exception.NotFoundException;
import com.taover.repository.exception.ObjectReflectException;
import com.taover.repository.jdbctemplate.JdbcTemplateWrapperTenant;
import com.taover.repository.mapper.CustomJdbcTemplateRowMapper;
import com.taover.repository.util.UtilsSql;

/**
 * 
 * @author root
 *
 * @param <T>
 * @param <ID>
 */
public class CustomJdbcTemplateWrapperTenant<T, ID extends Number> implements CustomJdbcTemplateWrapperTenantInterface<T, ID>{
	@Resource
	private JdbcTemplateWrapperTenant _jdbcTemplateWrapperTenant;
	
	private static int DEFAULT_BATCH_SIZE_WHEN_UPDATE_BY_IDLIST = 100;
	
	private Map<String, String> _beanToTableField;
	private Map<String, String> _tableToBeanField;
	private String _tableFieldNameListGapWithComma;
	private String _idTableFieldName;
	private String _dbName;
	private String _tableName;
	private TableShardingType _shardingType;
	private String _shardingColumn;
	private Class<T> _tClassInfo;
	private CustomJdbcTemplateRowMapper<T> _customJdbcTemplateRowMapper;
	
	public CustomJdbcTemplateRowMapper<T> getCustomJdbcTemplateRowMapper(){
		return this._customJdbcTemplateRowMapper;
	}	
	public CustomJdbcTemplateWrapperTenant(JdbcTemplateWrapperTenant jdbcTemplate) throws Exception{
		this();
		this._jdbcTemplateWrapperTenant = jdbcTemplate;
	}
	
	@SuppressWarnings("unchecked")
	public CustomJdbcTemplateWrapperTenant() throws Exception{				
        //获取泛型类Class
		this._tClassInfo = (Class<T>)((ParameterizedType)getClass().getGenericSuperclass()).getActualTypeArguments()[0];
        
        //检查实体声明
		Table annoTable = (Table) _tClassInfo.getAnnotation(Table.class);
		if(annoTable == null){
			throw new Exception("DAO层初始化失败，失败原因："+_tClassInfo.getName()+"实体类，没有@Table注解指定表名");
		}
		
		//解析分片信息
		TableSharding annoTableSharding = (TableSharding) _tClassInfo.getAnnotation(TableSharding.class);
		if(annoTableSharding == null) {
			this._shardingColumn = "";
			this._shardingType = TableShardingType.ONE_DB_ONE_TABLE;
		}else {
			this._shardingColumn = annoTableSharding.column();
			this._shardingType = annoTableSharding.type();
			if("".equals(this._shardingColumn)) {
				throw new Exception("请指定Sharding的column值");
			}
		}		
		
		this._tableName = annoTable.name();
		String schema = annoTable.schema();
		String catalog = annoTable.catalog();
		if(schema != null && !"".equals(schema)){
			this._dbName = schema;
		}else if(catalog != null && !"".equals(catalog)){
			this._dbName = catalog;
		}
		
		//初始化数据
		_beanToTableField = new HashMap<String, String>();
		_tableToBeanField = new HashMap<String, String>();
		_tableFieldNameListGapWithComma = "";
		Field[] declaredFields = _tClassInfo.getDeclaredFields();
		for(int i=0; i<declaredFields.length; ++i){
			Field currField = declaredFields[i];
			String fieldName = currField.getName();
			Id annoId = (Id)currField.getAnnotation(Id.class);
			Column annoColumn = (Column)currField.getAnnotation(Column.class);
			if(annoId != null){
				if(annoColumn == null){
					_idTableFieldName = UtilsSql.camelToUnderline(fieldName);
				}else{
					_idTableFieldName = annoColumn.name();
				}
				_tableFieldNameListGapWithComma += _idTableFieldName+",";
				_beanToTableField.put(fieldName, _idTableFieldName);
				_tableToBeanField.put(_idTableFieldName, fieldName);
			}else{
				if(annoColumn != null){
					String tableFieldName = annoColumn.name();
					_tableFieldNameListGapWithComma += tableFieldName+",";
					_beanToTableField.put(fieldName, tableFieldName);
					_tableToBeanField.put(tableFieldName, fieldName);					
				}
			}
		}
		
		//检验是否有属性
		if(_beanToTableField.isEmpty()){
			throw new Exception("DAO层初始化失败，失败原因："+this._tClassInfo.getName()+"实体类，没有在实体中找到属性信息");
		}
		
		//去除逗号
		_tableFieldNameListGapWithComma = _tableFieldNameListGapWithComma.substring(0, _tableFieldNameListGapWithComma.length()-1);
		
		//创建rowmapper
		this._customJdbcTemplateRowMapper = new CustomJdbcTemplateRowMapper<T>(this._tClassInfo, this._tableToBeanField);
	}
		
	private void appendWhereCondition(StringBuffer sql, StringBuffer pql, List<Object> list, List<Object[]> condition) {
        if (condition == null || condition.size() == 0) return;
        Object[] con = condition.get(0);
        int iLen = condition.size();
        sql.append(" WHERE ");
        pql.append(" WHERE ");
        sql.append(con[0]);
        pql.append(con[0]);
        if (null != con[1] && con[1] != "" && con[1] != "useArg[0]") {
            sql.append(" " + con[1] + " ?");
            pql.append(" " + con[1] + " " + con[2]);
            list.add(con[2]);
        }
        for (int i = 1; i < iLen; i++) {
            con = condition.get(i);
            sql.append(" AND ");
            pql.append(" AND ");
            sql.append(con[0]);
            pql.append(con[0]);
            if (null == con[1] || "" == con[1] || con[1] == "useArg[0]") continue;
            sql.append(" " + con[1] + " ?");
            pql.append(" " + con[1] + " " + con[2]);
            list.add(con[2]);
        }
    }
	
	private void appendWhereConditionForCount(StringBuffer sql, List<Object[]> condition) {
        if (condition == null || condition.size() == 0) return;
        Object[] con = condition.get(0);
        int iLen = condition.size();
        sql.append(" WHERE ");
        sql.append(con[0]);
        if (null != con[1] && con[1] != "" && con[1] != "useArg[0]") {
            sql.append(" " + con[1] + " ?");
        }
        for (int i = 1; i < iLen; i++) {
            con = condition.get(i);
            sql.append(" AND ");
            sql.append(con[0]);
            if (null == con[1] || "" == con[1] || con[1] == "useArg[0]") continue;
            sql.append(" " + con[1] + " ?");
        }
    }
	
	private void appendSetSql(StringBuffer sql, StringBuffer pql, List<Object> list, List<Object[]> obj) {
        for (Object[] arg : obj) {
            if (arg.length > 2) {
            	sql.append(" " + arg[0] + " = " + arg[0] + arg[2] + " ?,");
                pql.append(" " + arg[0] + " = " + arg[0] + arg[2] + arg[1] +",");
                list.add(arg[1]);
            }else if(arg.length == 2) {
            	sql.append(" " + arg[0] + " = ?,");
                pql.append(" " + arg[0] + " = " + arg[1] + ",");
                list.add(arg[1]);
            }else if(arg.length == 1) {
            	sql.append(" " + arg[0] + ",");
            	pql.append(" " + arg[0] + ",");
            }
        }
    }
	
	private String getTableSql(){
		return (this._dbName == null || "".equals(this._dbName.trim()))?
				("`"+this._tableName+"`"):
				("`"+this._dbName+"`.`"+this._tableName+"`");
	}
		
	private String constructUpdateSql(T entity, List<Field> beanFieldList) {
		StringBuffer sqlInsertPart =  new StringBuffer("INSERT INTO "+this.getTableSql()+"(");
		Iterator<String> beanFieldIter = this._beanToTableField.keySet().iterator();		
		while(beanFieldIter.hasNext()){
			String beanFieldName = beanFieldIter.next();
			String tableFieldName = this._beanToTableField.get(beanFieldName);
			Field beanField = null;
			try {
				beanField = this._tClassInfo.getDeclaredField(beanFieldName);
				beanField.setAccessible(true);
				if(beanField.get(entity) == null) {
					continue;
				}
			} catch (Exception e) {
				continue;
			}
			
			if(tableFieldName == null || beanFieldName == null){
				continue;
			}
			
			beanFieldList.add(beanField);
			sqlInsertPart.append("`"+tableFieldName+"`,");
		}
		return sqlInsertPart.substring(0, sqlInsertPart.length()-1)+")";
	}
	
	@Override
	public T findEntityByID(ID id, Long shardingKey) throws NotFoundException {
		return findEntityByID(id, shardingKey, false);
	}

	@Override
	public T findEntityByID(ID id, Long shardingKey, boolean isLock) throws NotFoundException {
		StringBuffer sql = new StringBuffer("SELECT "+this._tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		sql.append(" WHERE "+this.appendWhereSqlWithShardingCondition(_idTableFieldName+" = ? ", shardingKey));
		if (isLock) {
			sql.append(" FOR UPDATE ");
		}
		try {
			return (T) _jdbcTemplateWrapperTenant.queryForObject(sql.toString(), this._customJdbcTemplateRowMapper, shardingKey, id);
		}catch (DataAccessException e) {			
			throw new NotFoundException(e);
		}
	}

	@Override
	public T findEntityByCondition(List<Object[]> condition, Long shardingKey) throws NotFoundException, MultiRowException, NoShardingKeyException {
		List<T> tempList = findListByCondition(condition, shardingKey);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new MultiRowException();
		}
	}

	@Override
	public T findEntityBySql(String sqlCondition, Long shardingKey) throws NotFoundException, MultiRowException {
		List<T> tempList = findListBySql(sqlCondition, shardingKey);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new MultiRowException();
		}
	}
	
	@Override
	public List<T> findListByCondition(List<Object[]> condition, Long shardingKey){		
		return findListByCondition(condition, null, shardingKey);
	}

	@Override
	public List<T> findListByCondition(List<Object[]> condition, String sortCondition, Long shardingKey){
		StringBuffer sql = new StringBuffer("SELECT "+this._tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		List<Object> list = new ArrayList<Object>();
		this.appendWhereConditionWithShardingCondition(condition, shardingKey);
		this.appendWhereCondition(sql, new StringBuffer(), list, condition);
		if(sortCondition != null && !sortCondition.equals("")){
		   sql.append(" " + sortCondition + " ");
		}
		return (List<T>)_jdbcTemplateWrapperTenant.query(sql.toString(), this._customJdbcTemplateRowMapper, shardingKey, list.toArray());
	}

	@Override
	public List<T> findListBySql(String sqlCondition, Long shardingKey){
		StringBuffer sql = new StringBuffer("SELECT "+this._tableFieldNameListGapWithComma+" FROM "+this.getTableSql()+" WHERE " + this.appendWhereSqlWithShardingCondition(sqlCondition, shardingKey));
		return (List<T>)_jdbcTemplateWrapperTenant.query(sql.toString(), this._customJdbcTemplateRowMapper, shardingKey);
	}

	@Override
	public Map<String, Object> findPageByCondition(List<Object[]> condition, int page, int pageSize, Long shardingKey){
		return findPageByCondition(condition, null , page, pageSize, shardingKey);
	}
	
	@Override
	public Map<String, Object> findPageByCondition(List<Object[]> condition, String sortCondition, int page, int pageSize, Long shardingKey){
		StringBuffer sql = new StringBuffer("SELECT "+this._tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		StringBuffer pql = new StringBuffer(sql.toString());
		StringBuffer sqlCount = new StringBuffer("SELECT COUNT(1) rowCount FROM "+this.getTableSql());
		this.appendWhereConditionWithShardingCondition(condition, shardingKey);
		List<Object> count_list = new ArrayList<Object>();
		List<Object> page_list = new ArrayList<Object>();
		this.appendWhereConditionForCount(sqlCount, condition);
		this.appendWhereCondition(sql, pql, count_list, condition);
		for (int i = 0; i < count_list.size(); i++)
			page_list.add(count_list.get(i));

		page_list.add((page - 1) * pageSize);
		page_list.add(pageSize);
		
		if(sortCondition != null && !sortCondition.equals("")){
		   sql.append(" " + sortCondition + " ");
		   pql.append(" " + sortCondition + " ");
		}
		
	  	String pageSql = sql.toString() + " limit ?, ?";
		Map<String, Object> totalRowsMap = _jdbcTemplateWrapperTenant.queryForMap(sqlCount.toString(), shardingKey, count_list.toArray()) ;
		List<T> resultList = _jdbcTemplateWrapperTenant.query(pageSql.toString(), this._customJdbcTemplateRowMapper, shardingKey, page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}

	@Override
	public Map<String, Object> findPageBySql(String sqlCondition, int page, int pageSize, Long shardingKey){
		sqlCondition = this.appendWhereSqlWithShardingCondition(sqlCondition, shardingKey);
		StringBuffer sql = new StringBuffer("SELECT "+this._tableFieldNameListGapWithComma+" FROM "+this.getTableSql()+" WHERE " + sqlCondition);
		StringBuffer sqlCount = new StringBuffer("SELECT count(1) rowCount FROM "+this.getTableSql()+" WHERE " + sqlCondition);
		sql.append(" limit ?, ?");
		List<Object> page_list = new ArrayList<Object>();
		page_list.add((page - 1) * pageSize);
		page_list.add(page * pageSize);
		
		Map<String, Object> totalRowsMap = _jdbcTemplateWrapperTenant.queryForMap(sqlCount.toString(), shardingKey);
		List<T> resultList = _jdbcTemplateWrapperTenant.query(sql.toString(), this._customJdbcTemplateRowMapper, shardingKey, page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}

	@Override
	public Number addEntity(T entity, Long shardingKey) {
	    StringBuffer sqlInsertPart =  new StringBuffer("INSERT INTO "+this.getTableSql()+"(");
		StringBuffer sqlColumnPart = new StringBuffer(") VALUES (");		
		List<Object> paramList = new ArrayList<Object>();
		
		Iterator<String> beanFieldIter = this._beanToTableField.keySet().iterator();
		while(beanFieldIter.hasNext()){
			String beanFieldName = beanFieldIter.next();
			String tableFieldName = this._beanToTableField.get(beanFieldName);
			Field beanField;
			Object beanFieldValue = null;
			try {
				beanField = this._tClassInfo.getDeclaredField(beanFieldName);
				beanField.setAccessible(true);
				beanFieldValue = beanField.get(entity);
			} catch (Exception e) {
				e.printStackTrace();
			}
			
			if(tableFieldName == null || beanFieldName == null || beanFieldValue == null){
				continue;
			}
			sqlInsertPart.append("`"+tableFieldName+"`,");
			sqlColumnPart.append(" ?,");
			paramList.add(beanFieldValue);
		}
		
		//执行SQL
		String exeSql = sqlInsertPart.substring(0, sqlInsertPart.length()-1)+sqlColumnPart.substring(0, sqlColumnPart.length()-1)+")";
		KeyHolder _keyHolder = new GeneratedKeyHolder(new ArrayList<Map<String,Object>>(1));
		_jdbcTemplateWrapperTenant.getJdbcTemplate().update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement stat = con.prepareStatement(exeSql, new String[] {_idTableFieldName});
				PreparedStatementSetter setter = new ArgumentPreparedStatementSetter(paramList.toArray());
				setter.setValues(stat);
				return stat;
			}
			
		}, _keyHolder);
		
		return _keyHolder.getKey();
	}

	@Override
	public List<Number> addEntityList(List<T> entityList, Long shardingKey) throws Exception {
		if(entityList == null || entityList.isEmpty()) {
			return Collections.EMPTY_LIST;
		}
	    //构造SQL语句及Entity Field列表
		List<Field> beanFieldList = new ArrayList<Field>(this._beanToTableField.size());
		StringBuffer exeSql = new StringBuffer(this.constructUpdateSql(entityList.get(0), beanFieldList));
		
		//构造参数信息
		List<Object> args = new ArrayList<Object>();
		exeSql.append(" VALUES");
		for(int itemIndex=0; itemIndex<entityList.size(); ++itemIndex) {
			T item = entityList.get(itemIndex);
			exeSql.append("(");
			for(int i=0; i<beanFieldList.size(); ++i) {
				Field itemField = beanFieldList.get(i);
				Object itemData = null;
				try {
					itemData = itemField.get(item);
				} catch (Exception e) {
					throw new ObjectReflectException();
				}
				if(itemData == null) {
					args.add(UtilsSql.getDefaultValueByFieldType(itemField));
				}else {
					args.add(itemData);
				}
				exeSql.append("?,");
			}
			exeSql.setCharAt(exeSql.length()-1, ' ');
			exeSql.append("),");
		}
		exeSql.setCharAt(exeSql.length()-1, ';');
		
		//调用更新接口
		KeyHolder _keyHolder = new GeneratedKeyHolder(new ArrayList<Map<String,Object>>(entityList.size()));
		_jdbcTemplateWrapperTenant.getJdbcTemplate().update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement stat = con.prepareStatement(exeSql.toString(), new String[] {_idTableFieldName});
				PreparedStatementSetter setter = new ArgumentPreparedStatementSetter(args.toArray());
				setter.setValues(stat);
				return stat;
			}
			
		}, _keyHolder);
		
		//处理结果数据
		List<Map<String, Object>> data = _keyHolder.getKeyList();
		if(data.size() != entityList.size()) {
			throw new Exception("param entity size not equal return generate key list size");
		}
		List<Number> dataT = new ArrayList<Number>(data.size());
		for(Map<String, Object> item: data) {
			Iterator<Object> keyIter = item.values().iterator();
			if (keyIter.hasNext()) {
				Object key = keyIter.next();
				if (!(key instanceof Number)) {
					throw new DataRetrievalFailureException(
							"The generated key is not of a supported numeric type. " +
							"Unable to cast [" + (key != null ? key.getClass().getName() : null) +
							"] to [" + Number.class.getName() + "]");
				}
				dataT.add((Number)key);
			}else {
				throw new DataRetrievalFailureException("Unable to retrieve the generated key. " +
						"Check that the table has an identity column enabled.");
			}
		}
		return dataT;
	}

	@Override
	public int deleteEntityByID(ID id, Long shardingKey) {
		StringBuffer sql = new StringBuffer("DELETE FROM "+this.getTableSql()+" WHERE ");
		sql.append(this.appendWhereSqlWithShardingCondition(this._idTableFieldName+" = ? ", shardingKey));
		return _jdbcTemplateWrapperTenant.update(sql.toString(), shardingKey, id);
	}

	private void appendWhereConditionWithShardingCondition(List<Object[]> condition, Long shardingKey) throws NoShardingKeyException {
		if("".equals(this._shardingColumn)) {
			return;
		}
		if(this._shardingType == TableShardingType.ONE_DB_ONE_TABLE
				|| this._shardingType == TableShardingType.BROADCAST) {
			if(Optional.ofNullable(shardingKey).isPresent()){
				condition.add(new Object[] {this._shardingColumn, "=", shardingKey});
			}
		}else {
			Optional.ofNullable(shardingKey).orElseThrow(()->new NoShardingKeyException(this._shardingColumn));
			condition.add(new Object[] {this._shardingColumn, "=", shardingKey});
		}
	}
	
	private String appendWhereSqlWithShardingCondition(String whereSql, Long shardingKey) throws NoShardingKeyException {
		if("".equals(this._shardingColumn)) {
			return whereSql;
		}
		if(this._shardingType == TableShardingType.ONE_DB_ONE_TABLE
				|| this._shardingType == TableShardingType.BROADCAST) {
			if(Optional.ofNullable(shardingKey).isPresent()){
				return this._shardingColumn + "=" + shardingKey + " and " + whereSql;
			}
		}else if(this._shardingType == TableShardingType.MULTI_DB_MULTI_TABLE){
			Optional.ofNullable(shardingKey).orElseThrow(()->new NoShardingKeyException(this._shardingColumn));
			return this._shardingColumn + "=" + shardingKey + " and " + whereSql;
		}
		return whereSql;
	}
	
	@Override
	public int deleteEntityByCondition(List<Object[]> condition, Long shardingKey){
		if (null == condition || condition.isEmpty()) {
			throw new RuntimeException("params[condition] is empty");
		}
		
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("DELETE FROM "+this.getTableSql()+" ");
		StringBuffer pql = new StringBuffer(sql.toString());
		this.appendWhereConditionWithShardingCondition(condition, shardingKey);
		this.appendWhereCondition(sql, pql, list, condition);
		return _jdbcTemplateWrapperTenant.update(sql.toString(), shardingKey, list.toArray());
	}

	@Override
	public int deleteEntityBySql(String sqlCondition, Long shardingKey){
		if("".equals(sqlCondition.trim())) {
			throw new RuntimeException("params[sqlCondition] is empty");
		}
		return _jdbcTemplateWrapperTenant.update( "DELETE FROM "+this.getTableSql()+" WHERE "+this.appendWhereSqlWithShardingCondition(sqlCondition, shardingKey), shardingKey);
	}

	@Override
	public int deleteEntityList(List<ID> idList, Long shardingKey){
		StringBuffer idSb = new StringBuffer();
		for(ID id: idList) {
			idSb.append(id+",");
		}
		return this.deleteEntityBySql(this.appendWhereSqlWithShardingCondition(this._idTableFieldName + " in ("+idSb.toString().substring(0, idSb.length()-1)+") ", shardingKey), shardingKey);
	}

	@Override
	public int updateEntityById(List<Object[]> changeList, ID id, Long shardingKey) {
		if(null == id){			
			throw new RuntimeException("params[id] is null");
		}
		if (null == changeList || changeList.size() == 0) {
			throw new RuntimeException("params[changeList] is empty");
		}
		
		StringBuffer sql = new StringBuffer("UPDATE "+this.getTableSql()+" SET");
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendSetSql(sql, pql, list, changeList);
		
		String where = " WHERE "+this.appendWhereSqlWithShardingCondition(this._idTableFieldName+"=? ", shardingKey);
		String updateSql = sql.substring(0, sql.length()-1)+where;
		list.add(id);		
		return _jdbcTemplateWrapperTenant.update(updateSql, shardingKey, list.toArray());
	}
	
	@Override
	public void updateEntityByIdList(List<Object[]> changeList, Collection<ID> idList, Integer batchSize, Long shardingKey) {
		if(null == idList){	
			throw new RuntimeException("params[idList] is null");
		}
		if (null == changeList || changeList.size() == 0) {
			throw new RuntimeException("params[changeList] is empty");
		}
		if(batchSize == null) {
			batchSize = DEFAULT_BATCH_SIZE_WHEN_UPDATE_BY_IDLIST;
		}
		
		StringBuffer sql = new StringBuffer("UPDATE "+this.getTableSql()+" SET");
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendSetSql(sql, pql, list, changeList);
		String updateAndFromSql = sql.substring(0, sql.length()-1);
		
		Iterator<ID> idIter = idList.iterator();
		int idCount = 0;
		StringBuffer idSb = new StringBuffer();
		while(idIter.hasNext()) {		
			idSb.append(idIter.next()+",");
			++idCount;
			
			//满100条则向mysql发送更新请求
			if(idCount%batchSize == 0) {
				String whereSql = " WHERE "+this.appendWhereSqlWithShardingCondition(this._idTableFieldName+" in ("+idSb.toString().substring(0, idSb.length()-1)+") ", shardingKey);
				this._jdbcTemplateWrapperTenant.update(updateAndFromSql+whereSql, shardingKey, list.toArray());
				idSb.setLength(0);
			}
		}

		//是否剩余id列表
		if(idSb.length() > 0) {
			String whereSql = " WHERE "+this.appendWhereSqlWithShardingCondition(this._idTableFieldName+" in ("+idSb.toString().substring(0, idSb.length()-1)+") ", shardingKey);
			this._jdbcTemplateWrapperTenant.update(updateAndFromSql+whereSql, shardingKey, list.toArray());
		}
	}
	
	@Override
	public void updateEntityByIdList(List<Object[]> changeList, Collection<ID> idList, Long shardingKey) {
		this.updateEntityByIdList(changeList, idList, DEFAULT_BATCH_SIZE_WHEN_UPDATE_BY_IDLIST, shardingKey);
	}

	@Override
	public int updateEntityByCondition(List<Object[]> updateObj, List<Object[]> condition, Long shardingKey){
		if (null == updateObj || updateObj.size() == 0) {
			throw new RuntimeException("params[updateObj] is empty");
		}
		if (null == condition || condition.size() == 0) {
			throw new RuntimeException("params[condition] is empty");
		}

		StringBuffer sql = new StringBuffer("UPDATE "+this.getTableSql()+" SET ");
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendSetSql(sql, pql, list, updateObj);
		
		this.appendWhereConditionWithShardingCondition(condition, shardingKey);
		StringBuffer where = new StringBuffer("");
		StringBuffer pwhere = new StringBuffer("");
		this.appendWhereCondition(where, pwhere, list, condition);

		String updateSql = sql.substring(0, sql.length()-1)+where.toString();
		return _jdbcTemplateWrapperTenant.update(updateSql, shardingKey, list.toArray());
	}

	@Override
	public int updateEntityBySql(List<Object[]> updateObj, String sqlCondition, Long shardingKey){
		if (null == updateObj || updateObj.size() == 0) {
			throw new RuntimeException("params[updateObj] is empty");
		}
		if ("".equals(sqlCondition)) {
			throw new RuntimeException("params[sqlCondition] is empty");
		}
		
		StringBuffer sql = new StringBuffer("UPDATE "+this.getTableSql()+" SET ");
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendSetSql(sql, pql, list, updateObj);
		
		String updateSql = sql.toString().substring(0, sql.length()-1) + " WHERE "+this.appendWhereSqlWithShardingCondition(sqlCondition, shardingKey);
		return _jdbcTemplateWrapperTenant.update(updateSql, shardingKey, list.toArray());
	}

	@Override
	public Map<String, Object> getPageData(String coreSql, String orderByPartSql, Integer page, Integer pageSize, Long shardingKey){
		String[] splitedSql = UtilsSql.splitCoreSql(coreSql);
		return this.getPageData(splitedSql[0], splitedSql[1], orderByPartSql, page, pageSize, shardingKey);
	}

	@Override
	public Map<String, Object> getPageData(String selectSql, String fromAndWhereSql, String orderByPartSql, Integer page, Integer pageSize, Long shardingKey){
		//构造查询语句
		String querySql = selectSql+" "+fromAndWhereSql+" "+orderByPartSql+" "+UtilsSql.getLimitCondition(page, pageSize);
		
		//构造统计计数语句
		String countSql = null;
		if(UtilsSql.containsGroupBySql(fromAndWhereSql)) {
			countSql = "select count(*) rowsCount from ( select 1 "+fromAndWhereSql+" ) t ";
		}else {
			countSql = "select count(*) "+fromAndWhereSql;
		}		
		
		//执行查询
		List<Map<String, Object>> queryData = new ArrayList<Map<String, Object>>();
		Map<String, Object> countData = new HashMap<String, Object>();
		queryData = this._jdbcTemplateWrapperTenant.queryForList(querySql, shardingKey);
		countData = this._jdbcTemplateWrapperTenant.queryForMap(countSql, shardingKey);		
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(countData.get("rowsCount").toString()), queryData);
	}
}