自己写一个增强的JdbcDaoTemplate，欢迎拍砖

应用场景如下，一个实体，需要对应N个表，这N个表的结构一致，但是后缀不同。用户登录后，取得用户的组，组名即为那张表的后缀。Hibernate做这个不容易，所以考虑采用JDBC来完成。

自己胡乱写了一个增强的JdbcDaoTemplate类，可以实现一些简单的查询，不必写sql语句。

也是靠我自己的想法来写的，有什么不妥之处，欢迎拍砖。

/**
 * 提供一些简单的方法供子类使用
 * 其中RowMapper接口有3个现成的实现类，分别是：
 * 1、BeanPropertyRowMapper，提供将查询结果转换为对象，数据库列名应带下划线命名，会自动转换为骆驼命名规则的字段名
 * 2、ColumnMapRowMapper，返回一个List对象，对象中的每一个元素都是一个Map对象，Map实际上是org.apache.commons.collections.map.ListOrderedMap
 * 3、SingleColumnRowMapper，返回一个List对象，对象中的每个元素是数据库中的某列的值，SingleColumnRowMapper的构造方法可以传入需要的数据类型
 * 上述3个实现不能满足要求时，可自行实现RowMapper接口
 * @author Sunshine
 *
 */
@SuppressWarnings("unchecked")
public class BasicJdbcDaoSupport<T, PK extends Serializable> extends JdbcDaoSupport {

	protected Class<T> entityClass;

	public BasicJdbcDaoSupport() {
		this.entityClass = ReflectionUtils.getSuperClassGenricType(getClass());
	}

	@Resource(name = "secondaryDataSource")
	public void setSuperDataSource(DataSource dataSource) {
		super.setDataSource(dataSource);
	}

	public <X> X get(final PK id, final RowMapper rowMapper) {
		String sql = "select * from " + getTableName(entityClass) + " where id = ?";
		List results = getJdbcTemplate().query(sql, new Object[] { id }, rowMapper);
		return (X) DataAccessUtils.singleResult(results);
	}

	public <X> List<X> getAll(final RowMapper rowMapper) {
		String sql = "select * from " + getTableName(entityClass);
		return getJdbcTemplate().query(sql, rowMapper);
	}

	public <X> List<X> getAll(final String orderBy, final boolean isAsc, final RowMapper rowMapper) {
		String direction = (isAsc == true) ? "asc" : "desc";
		String sql = "select * from " + getTableName(entityClass) + " order by " + orderBy + " " + direction;
		return getJdbcTemplate().query(sql, rowMapper);
	}

	public <X> List<X> find(final String[] properties, final Object[] values, final RowMapper rowMapper) {
		Assert.notNull(properties, "properties cannot be null");
		Assert.notNull(values, "values cannot be null");
		if (properties.length == 0 || values.length == 0 || properties.length != values.length) {
			throw new IllegalArgumentException("invalid properties or values");
		}
		String sql = "select * from " + getTableName(entityClass) + " where ";
		String[] columns = new String[properties.length];
		for (int i = 0; i < properties.length; i++) {
			columns[i] = underscoreName(properties[i]) + " = ?"; // 将属性转换为数据库对应列名
		}
		sql += StringUtils.join(columns, " and ");
		return getJdbcTemplate().query(sql, values, rowMapper);
	}

	public <X> List<X> find(final String property, final Object value, final RowMapper rowMapper) {
		return find(new String[] { property }, new Object[] { value }, rowMapper);
	}

	public <X> List<X> find(final Map<String, Object> map, final RowMapper rowMapper) {
		List<String> properties = new ArrayList<String>();
		List<Object> values = new ArrayList<Object>();
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			properties.add(entry.getKey());
			values.add(entry.getValue());
		}
		return find(properties.toArray(new String[properties.size()]), values.toArray(new Object[values.size()]),
				rowMapper);
	}

	public <X> List<X> find(final String sql, final RowMapper rowMapper) {
		return getJdbcTemplate().query(sql, rowMapper);
	}

	public <X> List<X> find(final String sql, final Object[] values, final RowMapper rowMapper) {
		return getJdbcTemplate().query(sql, values, rowMapper);
	}

	public <X> List<X> find(final String property, final List<?> list, final RowMapper rowMapper) {
		if (list.size() > 0) {
			List<String> placeholders = new ArrayList<String>();
			for (int i = 0; i < list.size(); i++) {
				placeholders.add("?");
			}
			String sql = "select * from " + getTableName(entityClass) + " where " + underscoreName(property) + " in ";
			sql += "(" + placeholders.toArray(new String[placeholders.size()]) + ")";
			return getJdbcTemplate().query(sql, list.toArray(new Object[list.size()]), rowMapper);
		} else {
			return null;
		}
	}

	public <X> X findUnique(final String[] properties, final Object[] values, final RowMapper rowMapper) {
		return (X) DataAccessUtils.uniqueResult(find(properties, values, rowMapper));
	}

	public <X> X findUnique(final String property, final Object value, final RowMapper rowMapper) {
		return (X) DataAccessUtils.uniqueResult(find(property, value, rowMapper));
	}

	public <X> X findUnique(final Map<String, Object> map, final RowMapper rowMapper) {
		return (X) DataAccessUtils.uniqueResult(find(map, rowMapper));
	}

	public <X> X findUnique(final String sql, final RowMapper rowMapper) {
		return (X) DataAccessUtils.uniqueResult(find(sql, rowMapper));
	}

	public <X> X findUnique(final String sql, final Object[] values, final RowMapper rowMapper) {
		return (X) DataAccessUtils.uniqueResult(find(sql, values, rowMapper));
	}

	public T get(final PK id) {
		return get(id, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> getAll() {
		return getAll(new BeanPropertyRowMapper(entityClass));
	}

	public List<T> getAll(final String orderBy, final boolean isAsc) {
		return getAll(orderBy, isAsc, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final String[] properties, final Object[] values) {
		return find(properties, values, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final String property, final Object value) {
		return find(property, value, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final Map<String, Object> map) {
		return find(map, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final String sql) {
		return find(sql, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final String sql, final Object[] values) {
		return find(sql, values, new BeanPropertyRowMapper(entityClass));
	}

	public List<T> find(final String sql, final List<?> list) {
		return find(sql, list, new BeanPropertyRowMapper(entityClass));
	}

	public T findUnique(final String[] properties, final Object[] values) {
		return findUnique(properties, values, new BeanPropertyRowMapper(entityClass));
	}

	public T findUnique(final String property, final Object value) {
		return findUnique(property, value, new BeanPropertyRowMapper(entityClass));
	}

	public T findUnique(final Map<String, Object> map) {
		return findUnique(map, new BeanPropertyRowMapper(entityClass));
	}

	public T findUnique(final String sql) {
		return findUnique(sql, new BeanPropertyRowMapper(entityClass));
	}

	public T findUnique(final String sql, final Object[] values) {
		return findUnique(sql, values, new BeanPropertyRowMapper(entityClass));
	}

	public int count() {
		String sql = "select count(*) from " + getTableName(entityClass);
		return (Integer) DataAccessUtils.uniqueResult(getJdbcTemplate().query(sql,
				new SingleColumnRowMapper(Integer.class)));
	}

	public int count(final String[] properties, final Object[] values) {
		Assert.notNull(properties, "properties cannot be null");
		Assert.notNull(values, "values cannot be null");
		if (properties.length == 0 || values.length == 0 || properties.length != values.length) {
			throw new IllegalArgumentException("invalid properties or values");
		}
		String sql = "select count(*) from " + getTableName(entityClass) + " where ";
		String[] columns = new String[properties.length];
		for (int i = 0; i < properties.length; i++) {
			columns[i] = underscoreName(properties[i]) + " = ?"; // 将属性转换为数据库对应列名
		}
		sql += StringUtils.join(columns, " and ");
		return (Integer) DataAccessUtils.uniqueResult(getJdbcTemplate().query(sql, values,
				new SingleColumnRowMapper(Integer.class)));
	}

	public int count(final String property, final Object value) {
		return count(new String[] { property }, new Object[] { value });
	}

	public int count(final Map<String, Object> map) {
		List<String> properties = new ArrayList<String>();
		List<Object> values = new ArrayList<Object>();
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			properties.add(entry.getKey());
			values.add(entry.getValue());
		}
		return count(properties.toArray(new String[properties.size()]), values.toArray(new Object[values.size()]));
	}

	public int count(final String sql, final Object[] values) {
		return findUnique(sql, values, new SingleColumnRowMapper(Integer.class));
	}

	public int deleteBy(final String[] properties, final Object[] values) {
		Assert.notNull(properties, "properties cannot be null");
		Assert.notNull(values, "values cannot be null");
		if (properties.length == 0 || values.length == 0 || properties.length != values.length) {
			throw new IllegalArgumentException("invalid properties or values");
		}
		String sql = "delete from " + getTableName(entityClass) + " where ";
		String[] columns = new String[properties.length];
		for (int i = 0; i < properties.length; i++) {
			columns[i] = underscoreName(properties[i]) + " = ?"; // 将属性转换为数据库对应列名
		}
		sql += StringUtils.join(columns, " and ");
		return getJdbcTemplate().update(sql, values);
	}

	public int deleteBy(final String property, final Object value) {
		return deleteBy(new String[] { property }, new Object[] { value });
	}

	public int deleteBy(Map<String, Object> map) {
		List<String> properties = new ArrayList<String>();
		List<Object> values = new ArrayList<Object>();
		for (Map.Entry<String, Object> entry : map.entrySet()) {
			properties.add(entry.getKey());
			values.add(entry.getValue());
		}
		return deleteBy(properties.toArray(new String[properties.size()]), values.toArray(new Object[values.size()]));
	}

	/**
	 * 按照ID删除对象
	 */
	public int deleteBy(final PK id) {
		String sql = "delete from " + getTableName(entityClass) + " where id = ?";
		return getJdbcTemplate().update(sql, new Object[] { id });
	}

	/**
	 * 保存对象
	 */
	public int save(final T entity) {
		try {
			BeanInfo beanInfo = Introspector.getBeanInfo(entityClass, Object.class);
			PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
			List<String> columns = new ArrayList<String>();
			List<Object> values = new ArrayList<Object>();
			List<String> placeholders = new ArrayList<String>();

			for (PropertyDescriptor descriptor : descriptors) {
				Object value = descriptor.getReadMethod().invoke(entity);
				if (value != null) {
					columns.add(underscoreName(descriptor.getName()));
					values.add(value);
					placeholders.add("?");
				}
			}
			StringBuilder builder = new StringBuilder();
			builder.append("insert into ").append(getTableName(entityClass));
			builder.append(" (").append(StringUtils.join(columns, ",")).append(") ");
			builder.append(" values (").append(StringUtils.join(placeholders, ",")).append(")");
			return getJdbcTemplate().update(builder.toString(), values.toArray(new Object[values.size()]));
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		}
	}

	/**
	 * 更新对象
	 * 对象必须有且只有一个主键。在更新之前，必须先将对象查询出来
	 */
	public int update(final T entity) {
		try {
			String idName = getIdName(entityClass);
			Assert.notNull(idName);

			BeanInfo beanInfo = Introspector.getBeanInfo(entityClass, Object.class);
			PropertyDescriptor[] descriptors = beanInfo.getPropertyDescriptors();
			List<String> columns = new ArrayList<String>();
			List<Object> values = new ArrayList<Object>();
			Object idValue = null;

			for (PropertyDescriptor descriptor : descriptors) {
				String columnName = underscoreName(descriptor.getName());
				if (idName.equals(columnName)) {
					idValue = descriptor.getReadMethod().invoke(entity);
					if (idValue == null) {
						throw new IllegalArgumentException("no primary key is set");
					}
				} else {
					columns.add(columnName + " = ?");
					values.add(descriptor.getReadMethod().invoke(entity));
				}
			}
			values.add(idValue);
			StringBuilder builder = new StringBuilder();
			builder.append("update ").append(getTableName(entityClass));
			builder.append(" set ");
			builder.append(StringUtils.join(columns, ","));
			builder.append(" where ").append(idName).append(" = ?");
			return getJdbcTemplate().update(builder.toString(), values.toArray(new Object[values.size()]));
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		}
	}

	/**
	 * 取得实体对应的表名
	 * 对于有特殊需求者，可在子类中覆盖此方法
	 */
	protected String getTableName(Class<?> entityClass) {
		Table table = entityClass.getAnnotation(Table.class);
		return StringUtils.isNotBlank(table.catalog()) ? table.catalog() + "." + table.name() : table.name();
	}

	/**
	 * 取得实体对应表的主键名
	 */
	protected String getIdName(Class<?> entityClass) {
		String idName = null;
		Field[] fields = entityClass.getDeclaredFields();
		for (Field field : fields) {
			Id id = field.getAnnotation(Id.class);
			if (id != null) {
				idName = id.name();
				break;
			}
		}
		return idName;
	}

	/**
	 * 将属性名转换为数据库的列名
	 * 其中，将大写字母转换为下划线加大写字母
	 * @param name
	 * @return
	 */
	private String underscoreName(String name) {
		StringBuilder result = new StringBuilder();
		if (name != null && name.length() > 0) {
			result.append(name.substring(0, 1).toLowerCase());
			for (int i = 1; i < name.length(); i++) {
				String s = name.substring(i, i + 1);
				if (s.equals(s.toUpperCase())) {
					result.append("_");
					result.append(s.toLowerCase());
				} else {
					result.append(s);
				}
			}
		}
		return result.toString();
	}

}

 另附两个注解类：

@Target(FIELD)
@Retention(RUNTIME)
public @interface Id {

	String name();
}

@Target(TYPE)
@Retention(RUNTIME)
public @interface Table {

	String name();

	String catalog() default "";
}