package com.taover.repository;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

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

import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.ArgumentPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
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.mapper.CustomJdbcTemplateRowMapper;
import com.taover.repository.util.UtilsSql;

/**
 * 
 * @author root
 *
 * @param <T>
 * @param <ID>
 */
public class CustomJdbcTemplate<T, ID extends Serializable> {
	@Resource
	private JdbcTemplate jdbcTemplateRead;
	@Resource
	private JdbcTemplate jdbcTemplateWrite;
	
	private Map<String, String> beanToTableField;
	private Map<String, String> tableToBeanField;
	private String tableFieldNameListGapWithComma;
	private String idTableFieldName;
	private String idBeanFieldName;
	private String dbName;
	private String tableName;
	private Class<T> tClassInfo;
	private CustomJdbcTemplateRowMapper customJdbcTemplateRowMapper;
	
	public CustomJdbcTemplateRowMapper getCustomJdbcTemplateRowMapper(){
		return this.customJdbcTemplateRowMapper;
	}
	
	public CustomJdbcTemplate(JdbcTemplate jdbcTemplateWrite) throws Exception{
		this();
		this.jdbcTemplateWrite = jdbcTemplateWrite;
		this.jdbcTemplateRead = jdbcTemplateWrite;
	}
	
	public CustomJdbcTemplate() 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注解指定表名");
		}
		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 = this.camelToUnderline(fieldName);
				}else{
					idTableFieldName = annoColumn.name();
				}
				idBeanFieldName = fieldName;
				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(this.tClassInfo, this.tableToBeanField);
	}
	
	/**
	 * 将驼峰式命名的字符串转换为下划线大写方式。如果转换前的驼峰式命名的字符串为空，则返回空字符串。</br>
	 * 例如：HelloWorld->HELLO_WORLD
	 * @param name 转换前的驼峰式命名的字符串
	 * @return 转换后下划线大写方式命名的字符串
	 */
	private String camelToUnderline(String name) {
	    StringBuilder result = new StringBuilder();
	    if (name != null && name.length() > 0) {
	        // 将第一个字符处理成大写
	        result.append(name.substring(0, 1).toUpperCase());
	        // 循环处理其余字符
	        for (int i = 1; i < name.length(); i++) {
	            String s = name.substring(i, i + 1);
	            // 在大写字母前添加下划线
	            if (Character.isUpperCase(s.charAt(0)) && Character.isLetter(s.charAt(0))) {
	                result.append("_");
	            }
	            // 其他字符直接转成大写
	            result.append(s.toUpperCase());
	        }
	    }
	    return result.toString();
	}
	 
	/**
	 * 将下划线大写方式命名的字符串转换为驼峰式。如果转换前的下划线大写方式命名的字符串为空，则返回空字符串。</br>
	 * 例如：HELLO_WORLD->HelloWorld
	 * @param name 转换前的下划线大写方式命名的字符串
	 * @return 转换后的驼峰式命名的字符串
	 */
	private String underlineToCamel(String name) {
	    StringBuilder result = new StringBuilder();
	    // 快速检查
	    if (name == null || name.isEmpty()) {
	        // 没必要转换
	        return "";
	    } else if (!name.contains("_")) {
	        // 不含下划线，仅将首字母小写
	        return name.substring(0, 1).toLowerCase() + name.substring(1);
	    }
	    // 用下划线将原始字符串分割
	    String camels[] = name.split("_");
	    for (String camel :  camels) {
	        // 跳过原始字符串中开头、结尾的下换线或双重下划线
	        if (camel.isEmpty()) {
	            continue;
	        }
	        // 处理真正的驼峰片段
	        if (result.length() == 0) {
	            // 第一个驼峰片段，全部字母都小写
	            result.append(camel.toLowerCase());
	        } else {
	            // 其他的驼峰片段，首字母大写
	            result.append(camel.substring(0, 1).toUpperCase());
	            result.append(camel.substring(1).toLowerCase());
	        }
	    }
	    return result.toString();
	}
	
	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+"`");
	}
	
	/**
	 * 按主键查询
	 */
	public T findEntityByID(ID id) {
		return findEntityByID(id, true, false);
	}

	/**
	 * 按主键查询
	 * isLock 是否锁定, 默认不锁
	 * fromWriteDB  是否从写库读写,默认从读库查询
	 */
	public T findEntityByID(ID id, boolean fromWriteDB, boolean isLock) {
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		StringBuffer pql = new StringBuffer(sql.toString());
		sql.append(" WHERE "+idTableFieldName+" = ?");
		pql.append(" WHERE "+idTableFieldName+" = " + id);
		if (isLock) {
			sql.append(" FOR UPDATE");
			pql.append(" FOR UPDATE");
		}
		return (T) (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForObject(sql.toString(), this.customJdbcTemplateRowMapper, id);
	}

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public T findEntityByCondition(List<Object[]> condition) throws Exception {
		List<T> tempList = findListByCondition(condition, null, false);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new Exception("found multi rows with condition,but this func expected one row");
		}
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public T findEntityBySql(String sqlCondition) throws Exception{
		List<T> tempList = findListBySql(sqlCondition, false);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new Exception("found multi rows with condition,but this func expected one row");
		}
	}

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public List<T> findListByCondition(List<Object[]> condition) {
		return findListByCondition(condition,null, false);
	}	

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public List<T> findListByCondition(List<Object[]> condition, String sortCondition, boolean fromWriteDB) {		
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendWhereCondition(sql, pql, list, condition);
		if(sortCondition != null && !sortCondition.equals("")){
		   sql.append(" " + sortCondition + " ");
		   pql.append(" " + sortCondition + " ");
		}
		return (List<T>)(fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(sql.toString(), this.customJdbcTemplateRowMapper, list.toArray());
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public List<T> findListBySql(String sqlCondition) {
		return findListBySql(sqlCondition, false);
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public List<T> findListBySql(String sqlCondition, boolean fromWriteDB) {
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql()+" WHERE " + sqlCondition);
		return (List<T>)(fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(sql.toString(), this.customJdbcTemplateRowMapper);
	}

	/**
	 * 按条件分页查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public Map<String, Object> findPageByCondition(List<Object[]> condition,int page, int pageSize) {
		return findPageByCondition(condition, null , page, pageSize, false);
	}

	/**
	 * 按条件分页查询
	 * Object[]数组长度是3
	 * Object[]第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 * boolean isUseCache, 是否用缓存，默认用。
	 * boolean isAddCache, 是否添加缓存，默认添加。
	 */
	public Map<String, Object> findPageByCondition(List<Object[]> condition,String sortCondition, int page, int pageSize, boolean fromWriteDB) {
		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());

		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 = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForMap(sqlCount.toString(), count_list.toArray()) ;
		List<T> resultList = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(pageSql.toString(), this.customJdbcTemplateRowMapper, page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}

	/**
	 * 按sql分页查询, sqlCondition为where 后条件sql
	 */
	public Map<String, Object> findPageBySql(String sqlCondition, int page, int pageSize) {
		return findPageBySql(sqlCondition, page, pageSize, false);
	}

	/**
	 * 按sql分页查询, sqlCondition为where 后条件sql
	 */
	public Map<String, Object> findPageBySql(String sqlCondition, int page, int pageSize,boolean fromWriteDB) {			
		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);
		String pageSql = sql.toString() + " limit ?, ?";
		String pagePql = sql.toString() + " limit " + ((page -1) * pageSize) + ", " + (page * pageSize);
		List<Object> page_list = new ArrayList<Object>();
		page_list.add((page - 1) * pageSize);
		page_list.add(page * pageSize);
		
		Map<String, Object> totalRowsMap = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForMap(sqlCount.toString());
		List<T> resultList =(fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(pageSql.toString(), this.customJdbcTemplateRowMapper, page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}
	
	/**
	 * 添加
	 */
	public Number addEntityForAutoincrementId(T entity) {
		StringBuffer sqlForLog = new StringBuffer("INSERT INTO "+this.getTableSql()+"(");
		StringBuffer sqlValueForLog = new StringBuffer(") VALUES (");
	    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;
			}
			
			sqlForLog.append("`"+tableFieldName+"`,");
			sqlValueForLog.append(beanFieldValue.toString()+",");
			sqlInsertPart.append("`"+tableFieldName+"`,");
			sqlColumnPart.append(" ?,");
			paramList.add(beanFieldValue);
		}
		//打印日志内容
		sqlForLog.substring(0, sqlForLog.length()-1);
		sqlForLog.append(") VALUES (");
		sqlForLog.append(sqlValueForLog+")");
		//UtilsLog.infoForMessage(sqlForLog.toString(), this.getClass());
		
		//执行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));
		jdbcTemplateWrite.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();
	}
	
	/**
	 * 批量添加
	 * @throws Exception 
	 */
	public List<Number> addEntityList(List<T> entityList) throws Exception {
		if(entityList == null || entityList.isEmpty()) {
			throw new Exception("entitylist is empty or null");
		}
	    //构造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 = itemField.get(item);
				if(itemData == null) {
					args.add(this.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()));
		jdbcTemplateWrite.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;
	}
	
	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)+")";
	}

	private Object getDefaultValueByFieldType(Field itemField) {
		String simpleName = itemField.getType().getSimpleName();
		if("String".equals(simpleName)) {
			return "";
		}else if("Date".equals(simpleName) || "Timestamp".equals(simpleName)) {
			return "2000-01-01 00:00:00";
		}else if("BigDecimal".equals(simpleName)){
			return BigDecimal.ZERO;
		}else {
			return 0;
		}
	}

	/**
	 * 按ID删除
	 */
	public int deleteEntityByID(ID id) {
		StringBuffer sql = new StringBuffer("DELETE FROM "+this.getTableSql()+" WHERE");
		StringBuffer pql = new StringBuffer(sql.toString());
		pql.append(" "+this.idTableFieldName+" = " + id);
		sql.append(" "+this.idTableFieldName+" = ?");
		return jdbcTemplateWrite.update(sql.toString(), id);
	}
	/**
	 * 删除按List<Object[]>条件
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public int deleteEntityByCondition(List<Object[]> condition) throws Exception{
		if (null == condition || condition.size() == 0) {
			throw new Exception("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.appendWhereCondition(sql, pql, list, condition);
		return jdbcTemplateWrite.update( sql.toString(), list.toArray());
	}

	/**
	 * 删除按condition条件
	 * 建议使用deleteTByCondition(List<Object[]> condition), 如果removeTByCondition(List<Object[]> condition)满足不了where条件可以使用此方法。
	 * condition为where后面的条件，condition不能为空。
	 */
	public int deleteEntityBySql(String sqlCondition) throws Exception{
		if("".equals(sqlCondition)) {
			throw new Exception("params[sqlCondition] is empty");
		}
		return jdbcTemplateWrite.update( "DELETE FROM "+this.getTableSql()+" WHERE " + sqlCondition);
	}

	/**
	 * 根据list对象逐个删除。
	 */
	public List<Integer> deleteEntityList(List<T> entityList) {
		List<Integer> result = new ArrayList<Integer>();
		for (T entity : entityList) {
			Field beanField;
			Object beanFieldValue = Integer.valueOf(0);
			try {
				beanField = this.tClassInfo.getDeclaredField(this.idBeanFieldName);
				beanField.setAccessible(true);
				beanFieldValue = beanField.get(entity);
			} catch (Exception e) {
				e.printStackTrace();
			}
			result.add(deleteEntityByID((ID)beanFieldValue));
		}
		return result;
	}
	
	/**
	 * 根据ID修改指定的值
	 */
	public int updateEntityById(List<Object[]> changeList, ID id) throws Exception{
		if(null == id){			
			throw new Exception("params[id] is null");
		}
		if (null == changeList || changeList.size() == 0) {
			throw new Exception("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.idTableFieldName+"=?";
		String updateSql = sql.substring(0, sql.length()-1)+where;
		list.add(id);			
		return jdbcTemplateWrite.update(updateSql, list.toArray());
	}

	/**
	 * List<Object[]> updateObj 要修改成的值，数组长度为2，第一个值为列名，第二个值是要改成的值。
	 * List<Object[]> condition 修改的条件， 数组长度是3, 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public int updateEntityByCondition(List<Object[]> updateObj, List<Object[]> condition) throws Exception{
		if (null == updateObj || updateObj.size() == 0) {
			throw new Exception("params[updateObj] is empty");
		}
		if (null == condition || condition.size() == 0) {
			throw new Exception("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);
		
		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 jdbcTemplateWrite.update(updateSql, list.toArray());
	}

	/**
	 * List<Object[]> updateObj 要修改成的值，数组长度为2，第一个值为列名，第二个值是要改成的值。
	 * String sqlCondition 修改的条件。
	 */
	public int updateEntityBySql(List<Object[]> updateObj, String sqlCondition) throws Exception{
		if (null == updateObj || updateObj.size() == 0) {
			throw new Exception("params[updateObj] is empty");
		}
		if ("".equals(sqlCondition)) {
			throw new Exception("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 "+sqlCondition;
		return jdbcTemplateWrite.update(updateSql, list.toArray());
	}
	
	public Map<String, Object> getPageData(String coreSql, String orderByPartSql, Integer page, Integer pageSize){
		try {
			String[] splitedSql = UtilsSql.splitCoreSql(coreSql);
			return this.getPageData(splitedSql[0], splitedSql[1], orderByPartSql, page, pageSize);
		}catch (Exception e) {
			return UtilsSql.createPage(page, pageSize, 0, new ArrayList<Object>());
		}
	}
	
	public Map<String, Object> getPageData(String selectSql, String fromAndWhereSql, String orderByPartSql, Integer page, Integer pageSize){
		//构造查询语句
		String querySql = selectSql+" "+fromAndWhereSql+" "+orderByPartSql+" "+UtilsSql.getLimitCondition(page, pageSize);
		
		//构造统计计数语句
		String countSql = "select count(*) rowsCount from ( select 1 "+fromAndWhereSql+" ) t ";
		
		//执行查询
		List<Map<String, Object>> queryData = new ArrayList<Map<String, Object>>();
		Map<String, Object> countData = new HashMap<String, Object>();	
		queryData = this.jdbcTemplateRead.queryForList(querySql);
		countData = this.jdbcTemplateRead.queryForMap(countSql);		
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(countData.get("rowsCount").toString()), queryData);
	}
	
	public <E> Map<String, Object> getBeanPageData(String coreSql, String orderByPartSql, Integer page, Integer pageSize, Class<E> beanClass){
		try {
			String[] splitedSql = UtilsSql.splitCoreSql(coreSql);
			return this.getPageData(splitedSql[0], splitedSql[1], orderByPartSql, page, pageSize, beanClass);
		}catch (Exception e) {
			return UtilsSql.createPage(page, pageSize, 0, new ArrayList<Object>());
		}
	}
	
	public <E> Map<String, Object> getPageData(String selectSql, String fromAndWhereSql, String orderByPartSql, Integer page, Integer pageSize, Class<E> beanClass){
		//构造查询语句
		String querySql = selectSql+" "+fromAndWhereSql+" "+orderByPartSql+" "+UtilsSql.getLimitCondition(page, pageSize);
		
		//构造统计计数语句
		String countSql = "select count(*) rowsCount from ( select 1 "+fromAndWhereSql+" ) t ";
		
		//执行查询
		List<E> queryData = new ArrayList<E>();
		Map<String, Object> countData = new HashMap<String, Object>();	
		queryData = this.jdbcTemplateRead.queryForList(querySql, beanClass);
		countData = this.jdbcTemplateRead.queryForMap(countSql);		
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(countData.get("rowsCount").toString()), queryData);
	}

	/**
	 * 按主键查询
	 */
	public <E> E findBeanByID(ID id, Class<E> beanClass) {
		return findBeanByID(id, true, false, beanClass);
	}

	/**
	 * 按主键查询
	 * isLock 是否锁定, 默认不锁
	 * fromWriteDB  是否从写库读写,默认从读库查询
	 */
	public <E> E findBeanByID(ID id, boolean fromWriteDB, boolean isLock, Class<E> beanClass) {
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		StringBuffer pql = new StringBuffer(sql.toString());
		sql.append(" WHERE "+idTableFieldName+" = ?");
		pql.append(" WHERE "+idTableFieldName+" = " + id);
		if (isLock) {
			sql.append(" FOR UPDATE");
			pql.append(" FOR UPDATE");
		}
		return (E) (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForObject(sql.toString(), new CustomJdbcTemplateRowMapper(beanClass, this.tableToBeanField), id);
	}

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public <E> E findBeanByCondition(List<Object[]> condition, Class<E> beanClass) throws Exception {
		List<E> tempList = findBeanListByCondition(condition, null, false, beanClass);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new Exception("found multi rows with condition,but this func expected one row");
		}
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public <E> E findBeanBySql(String sqlCondition, Class<E> beanClass) throws Exception{
		List<E> tempList = findBeanListBySql(sqlCondition, false, beanClass);
		if(tempList == null || tempList.size() == 0){
			return null;
		}
		if(tempList != null && tempList.size() == 1){
			return tempList.get(0);
		}else{
			throw new Exception("found multi rows with condition,but this func expected one row");
		}
	}

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public <E> List<E> findBeanListByCondition(List<Object[]> condition, Class<E> beanClass) {
		return findBeanListByCondition(condition, null, false, beanClass);
	}	

	/**
	 * 根据条件List<Object[]>查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public <E> List<E> findBeanListByCondition(List<Object[]> condition, String sortCondition, boolean fromWriteDB, Class<E> beanClass) {		
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql());
		StringBuffer pql = new StringBuffer(sql.toString());
		List<Object> list = new ArrayList<Object>();
		this.appendWhereCondition(sql, pql, list, condition);
		if(sortCondition != null && !sortCondition.equals("")){
		   sql.append(" " + sortCondition + " ");
		   pql.append(" " + sortCondition + " ");
		}
		return (List<E>)(fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(sql.toString(), new CustomJdbcTemplateRowMapper(beanClass, this.tableToBeanField), list.toArray());
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public <E> List<E> findBeanListBySql(String sqlCondition, Class<E> beanClass) {
		return findBeanListBySql(sqlCondition, false, beanClass);
	}

	/**
	 * 根据条件sql查询
	 * sqlCondition 为where 后面的条件。
	 */
	public <E> List<E> findBeanListBySql(String sqlCondition, boolean fromWriteDB, Class<E> beanClass) {
		StringBuffer sql = new StringBuffer("SELECT "+this.tableFieldNameListGapWithComma+" FROM "+this.getTableSql()+" WHERE " + sqlCondition);
		return (List<E>)(fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(sql.toString(), new CustomJdbcTemplateRowMapper(beanClass, this.tableToBeanField));
	}

	/**
	 * 按条件分页查询
	 * Object[]数组长度是3
	 * Object[], 第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 */
	public <E> Map<String, Object> findBeanPageByCondition(List<Object[]> condition, int page, int pageSize, Class<E> beanClass) {
		return findBeanPageByCondition(condition, null , page, pageSize, false, beanClass);
	}

	/**
	 * 按条件分页查询
	 * Object[]数组长度是3
	 * Object[]第一个参数是列名，第二个参数是操作符，第三个参数是查询条件的值。
	 * boolean isUseCache, 是否用缓存，默认用。
	 * boolean isAddCache, 是否添加缓存，默认添加。
	 */
	public <E> Map<String, Object> findBeanPageByCondition(List<Object[]> condition,String sortCondition, int page, int pageSize, boolean fromWriteDB, Class<E> beanClass) {
		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());

		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 = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForMap(sqlCount.toString(), count_list.toArray()) ;
		List<E> resultList = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(pageSql.toString(), new CustomJdbcTemplateRowMapper(beanClass, this.tableToBeanField), page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}

	/**
	 * 按sql分页查询, sqlCondition为where 后条件sql
	 */
	public <E> Map<String, Object> findBeanPageBySql(String sqlCondition, int page, int pageSize, Class<E> beanClass) {
		return findBeanPageBySql(sqlCondition, page, pageSize, false, beanClass);
	}

	/**
	 * 按sql分页查询, sqlCondition为where 后条件sql
	 */
	public <E> Map<String, Object> findBeanPageBySql(String sqlCondition, int page, int pageSize,boolean fromWriteDB, Class<E> beanClass) {			
		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);
		String pageSql = sql.toString() + " limit ?, ?";
		String pagePql = sql.toString() + " limit " + ((page -1) * pageSize) + ", " + (page * pageSize);
		List<Object> page_list = new ArrayList<Object>();
		page_list.add((page - 1) * pageSize);
		page_list.add(page * pageSize);
		
		Map<String, Object> totalRowsMap = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).queryForMap(sqlCount.toString());
		List<E> resultList = (fromWriteDB ? jdbcTemplateWrite : jdbcTemplateRead).query(pageSql.toString(), new CustomJdbcTemplateRowMapper(beanClass, this.tableToBeanField), page_list.toArray());
		return UtilsSql.createPage(page, pageSize, Integer.valueOf(totalRowsMap.get("rowCount").toString()), resultList);
	}
}