统一模板的增删改查

package com.sc.gjsms.dao;

import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;


public class CommonDAO<T> {


	/**
	 * 新增
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public boolean save(JdbcTemplate jdbcTemplate,String tableName,Map<String,Object> columnsAndValues){
		if(columnsAndValues==null){
			return false;
		}
		StringBuffer sql = new StringBuffer();
		Set<String> columnNames = columnsAndValues.keySet();
		int size = columnNames.size();
		if(size>0){
			String[] cols = new String[size];
			Object[] values = new Object[size];		
			int i=0;
			for(String c :columnNames){
				cols[i]=c;
				values[i]=columnsAndValues.get(c);
				i++;
			}
			sql.append("insert into " + tableName + "(");
			int j=0;
			int k=0;
			for(;j<size-1;j++){
				sql.append(cols[j]+",");
			}
			
			sql.append(cols[j]+") values(");
			for(;k<size-1;k++){
				sql.append("?,");
			}
			sql.append("?)");
			
			int r = jdbcTemplate.update(sql.toString(),values);
			
			columnNames = null;
			sql = null;
			
			if(r>0){
				return true;
			}		
		}		
		return false;
	}
	
	
	/**
	 * 新增
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public boolean update(JdbcTemplate jdbcTemplate,String tableName,
			Map<String,Object> updateColumns,
			Map<String,Object> whereConditions){
		
		int rows = 0;
		
		if(updateColumns==null){
			return false;
		}
		int size = updateColumns.size();
		if(whereConditions!=null){
			size = size + whereConditions.size();
		}
		
		StringBuffer sql = null;
		Object[] values = null;
		//待更新的列
		Set<String> colsSet = null;
		if(size>0){
			sql = new StringBuffer();		
			values = new Object[size];		
			int i=0;
			
			colsSet = updateColumns.keySet();
			int colsCount = colsSet.size();
			sql.append("update " + tableName + " set ");
			for(String c :colsSet){
				values[i]=updateColumns.get(c);
				i++;
				if(i<colsCount){
					sql.append(c+"=?,");
				}else{
					sql.append(c+"=?");
					break;
				}
			}
			
			if(whereConditions!=null){
				sql.append(" where ");
				colsSet = whereConditions.keySet();
				colsCount = colsSet.size();
				for(String c :colsSet){
					values[i]=whereConditions.get(c);
					i++;
					if(i<colsCount){
						sql.append(c+"=? and ");
					}else{
						sql.append(c+"=?");
						break;
					}
				}
			}		
			
			System.out.println("update==>"+sql);
			
			rows = jdbcTemplate.update(sql.toString(),values);									
		}		
		return rows>0 ? true : false; 
		
	}
	
	
	/**
	 * 删除
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public boolean delete(JdbcTemplate jdbcTemplate,String tableName,
			String whereConditions,
			Object[] values){		
		int rows = 0;
		StringBuffer sql = new StringBuffer("delete from "+tableName);
		if(StringUtils.isNotBlank(whereConditions)){		
			sql.append(" " + whereConditions);		
			rows= jdbcTemplate.update(sql.toString(),values);
		}else{
			rows= jdbcTemplate.update(sql.toString());
		}
		sql = null;
		return rows>0 ? true : false;
	}
	
	/**
	 * 获取单条记录
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public T getOne(JdbcTemplate jdbcTemplate,String tableName,Class className,String columnset,
			Map<String,Object> wheres,
			Map<String,String> orders){
		T o = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select "+ columnset + " from "  + tableName + " where 1=1");
		Object[] values = null;
		if(wheres!=null){
			Set<String> whereConditions = wheres.keySet();
			int size = whereConditions.size();		
			if(size>0){
				values = new Object[size];		
				int i=0;
				for(String c :whereConditions){
					values[i]=wheres.get(c);
					sql.append(" and "+c+"=?");
					i++;
				}
			}
		}
		
		if(orders!=null){
			sql.append("order by ");
			Set<String> orderCols = orders.keySet();
			int size = orderCols.size();
			if(size>0){
				int i = 0;
				for(String c :orderCols){
					i++;
					if(i<size){
						sql.append(c + " " + orders.get(c) + ",");
					}else{
						sql.append(c + " " + orders.get(c));
						break;
					}				
				}				
			}			
		}
			
		System.out.println("getone="+sql.toString());
		List<T> list = jdbcTemplate.query(sql.toString(),
				new BeanPropertyRowMapper<T>(className),values);
		if(list!=null && list.size()>0){
			o = list.get(0);
		}
		list = null;
		wheres = null;
		orders = null;
		sql = null;
		return o;
	}
	
	/**
	 * 获取列表
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public List<T> getList(JdbcTemplate jdbcTemplate,String tableName,String columnset,Map<String,Object> wheres,
			Map<String,String> orders){
		
		StringBuffer sql = new StringBuffer();
		sql.append("select "+ columnset + " from "  + tableName + " where 1=1");
		Object[] values = null;
		if(wheres!=null){
			Set<String> whereConditions = wheres.keySet();
			int size = whereConditions.size();		
			if(size>0){
				String[] cols = new String[size];
				values = new Object[size];		
				int i=0;
				for(String c :whereConditions){
					cols[i]=c;
					values[i]=wheres.get(c);
					sql.append("and "+c+"?");
				}
			}
		}
		
		if(orders!=null){
			sql.append("order by ");
			Set<String> orderCols = orders.keySet();
			int size = orderCols.size();
			if(size>0){
				int i = 0;
				for(String c :orderCols){
					i++;
					if(i<size){
						sql.append(c + " " + orders.get(c) + ",");
					}else{
						sql.append(c + " " + orders.get(c));
						break;
					}				
				}				
			}			
		}
			
		List<T> list = jdbcTemplate.query(sql.toString(),new BeanPropertyRowMapper<T>(),values);		
		list = null;
		wheres = null;
		orders = null;
		sql = null;
		return list;
	}
	
	
	
	/**
	 * 统计数量
	 * @param jdbcTemplate
	 * @param tableName
	 * @param columnsAndValues
	 * @return
	 */
	public Integer count(JdbcTemplate jdbcTemplate,String tableName,String countCol,Map<String,Object> wheres,
			Map<String,String> orders){
		
		StringBuffer sql = new StringBuffer();
		sql.append("select count("+countCol+") from "+tableName + " where 1=1" );
		Object[] values = null;
		if(wheres!=null){
			Set<String> whereConditions = wheres.keySet();
			int size = whereConditions.size();		
			if(size>0){
				String[] cols = new String[size];
				values = new Object[size];		
				int i=0;
				for(String c :whereConditions){
					cols[i]=c;
					values[i]=wheres.get(c);
					sql.append(" and "+c+"=?");
				}
			}
		}
		
		int r = jdbcTemplate.queryForInt(sql.toString(),values);		
	
		wheres = null;
		orders = null;
		sql = null;
		return r;
	}
}