封装spring jdbc

import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedList;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

@Repository("Dao")
public class Dao {

	@Autowired
	private SimpleJdbcTemplate jdbc;

	// 查询
	public <T> T query(Object findBy, final Class<T> clazz, String ColumnName) {
		// -----拼接sql---------
		StringBuffer sb = new StringBuffer("select * from ");
		sb.append(clazz.getSimpleName());
		sb.append(" where ");
		sb.append(ColumnName);
		sb.append("=");
		if (findBy instanceof String || findBy instanceof Character)
			findBy = "'" + findBy + "'";
		sb.append(findBy);
		// --------------

		return jdbc.queryForObject(sb.toString(), getRowMapper(clazz));
	}

	// 添加
	public <T> void add(T obj) {
		// 维护一个值队列，用来和列名对应
		LinkedList<Object> queue = new LinkedList<Object>();

		Map<?, ?> properties = getPropertyMap(obj);

		// ------拼接sql---------
		StringBuffer sb = new StringBuffer("insert into ");
		sb.append(obj.getClass().getSimpleName());
		sb.append(" (");
		for (Object fieldName : properties.keySet()) {
			sb.append(fieldName);
			sb.append(",");
			queue.addLast(properties.get(fieldName));
		}

		// 删除最后一个逗号
		int charAt = sb.lastIndexOf(",");
		sb.replace(charAt, charAt + 1, ") values (");

		for (Object value : queue) {
			sb.append(value);
			sb.append(",");
		}
		
		// 删除最后一个逗号
		charAt = sb.lastIndexOf(",");
		sb.replace(charAt, charAt + 1, ")");
		// ------------------

		// 执行sql
		jdbc.update(sb.toString());
	}

	// 获取映射对象
	private <T> RowMapper<T> getRowMapper(final Class<T> clazz) {
		return new RowMapper<T>() {
			@Override
			public T mapRow(ResultSet rs, int arg1) throws SQLException {

				// 获取ResultSet头信息
				ResultSetMetaData rmd = rs.getMetaData();

				// 获取列总数
				int colCount = rmd.getColumnCount();
				T obj = null;
				try {
					obj = clazz.newInstance();
					String colName;
					
					// 获取并封装数据
					for (int i = 0; i < colCount; i++) {
						colName = rmd.getColumnName(i + 1);
						BeanUtils.setProperty(obj, colName,
								rs.getObject(colName));
					}
				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
				return obj;
			}
		};
	}

	// 获取属性Map（bug:当字段为9种类型之一，但是表中又没有该字段，无法剔除（除非先查一次数据库））
	@SuppressWarnings("unchecked")
	private Map<?, ?> getPropertyMap(Object obj) {
		Map<String, Object> properties = null;
		
		//筛选出的类型
		final ArrayList<String> fieldType = new ArrayList<String>(
				Arrays.asList("String", "Character", "Integer", "Byte",
						"Double", "Long", "Float", "Short", "Boolean"));
		try {
			properties = PropertyUtils.describe(obj);
			String fieldTypeName = null;
			ArrayList<String> removeKeys = new ArrayList<String>();

			// 选择字段（剔除除基本数据类型和String外的其他字段）
			for (String key : properties.keySet()) {
				fieldTypeName = properties.get(key).getClass().getSimpleName();

				// 如果不是基本数据类型和String就标记该字段
				if (!fieldType.contains(fieldTypeName)) {
					removeKeys.add(key);
				}

				// 在String或char的前后加 "'"
				if ("String".equals(fieldTypeName)
						|| "Character".equals(fieldTypeName)) {
					properties.put(key,
							("'" + properties.get(key).toString() + "'"));
				}
			}
			
			//移除标记的字段（之所以标记是因为不能在迭代时移除元素）
			for (String rkey : removeKeys) {
				properties.remove(rkey);
			}
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		}
		return properties;
	}
}