自己写一个增强的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 "";}?