项目中用的是hibernate作为dao层，但是使用hibernate进行sql查询总觉得不方便，于是又整合了jdbcTemplate,但是如果字段不是驼峰命名就无法正确映射，研究一番，找到下面这个办法：重写BeanPropertyRowMapper
/**
* @Author: ZFY
* @Date: 2018/10/29 19:18
* @Description: 由org.springframework.jdbc.core.BeanPropertyRowMapper改造,扩展注解映射
* 优先使用驼峰命名映射，找不到映射的则根据注解{@link Column}映射
*/
public class PropertyAnnotationRowMapper<T> implements RowMapper<T> {
/** Logger available to subclasses */
protected final Log logger = LogFactory.getLog(getClass());
/** The class we are mapping to */
private Class<T> mappedClass;
/** Whether we're strictly validating */
private boolean checkFullyPopulated = false;
/** Whether we're defaulting primitives when mapping a null value */
private boolean primitivesDefaultedForNullValue = false;
/** ConversionService for binding JDBC values to bean properties */
private ConversionService conversionService = DefaultConversionService.getSharedInstance();
/** Map of the fields we provide mapping for */
private Map<String, PropertyDescriptor> mappedFields;
/** 注解到类属性值的映射 */
private Map<String, PropertyDescriptor> mappedAnnotation;
/** Set of bean properties we provide mapping for */
private Set<String> mappedProperties;
/**
* Create a new {@code BeanPropertyRowMapper} for bean-style configuration.
* @see #setMappedClass
* @see #setCheckFullyPopulated
*/
public PropertyAnnotationRowMapper() {
initialize(mappedClass);
}
/**
* Create a new {@code BeanPropertyRowMapper}, accepting unpopulated
* properties in the target bean.
* <p>Consider using the {@link #newInstance} factory method instead,
* which allows for specifying the mapped type once only.
* @param mappedClass the class that each row should be mapped to
*/
public PropertyAnnotationRowMapper(Class<T> mappedClass) {
initialize(mappedClass);
}
/**
* Create a new {@code BeanPropertyRowMapper}.
* @param mappedClass the class that each row should be mapped to
* @param checkFullyPopulated whether we're strictly validating that
* all bean properties have been mapped from corresponding database fields
*/
public PropertyAnnotationRowMapper(Class<T> mappedClass, boolean checkFullyPopulated) {
initialize(mappedClass);
this.checkFullyPopulated = checkFullyPopulated;
}
/**
* Set the class that each row should be mapped to.
*/
public void setMappedClass(Class<T> mappedClass) {
if (this.mappedClass == null) {
initialize(mappedClass);
}
else {
if (this.mappedClass != mappedClass) {
throw new InvalidDataAccessApiUsageException("The mapped class can not be reassigned to map to " +
mappedClass + " since it is already providing mapping for " + this.mappedClass);
}
}
}
/**
* Get the class that we are mapping to.
*/
public final Class<T> getMappedClass() {
return this.mappedClass;
}
/**
* Set whether we're strictly validating that all bean properties have been mapped
* from corresponding database fields.
* <p>Default is {@code false}, accepting unpopulated properties in the target bean.
*/
public void setCheckFullyPopulated(boolean checkFullyPopulated) {
this.checkFullyPopulated = checkFullyPopulated;
}
/**
* Return whether we're strictly validating that all bean properties have been
* mapped from corresponding database fields.
*/
public boolean isCheckFullyPopulated() {
return this.checkFullyPopulated;
}
/**
* Set whether we're defaulting Java primitives in the case of mapping a null value
* from corresponding database fields.
* <p>Default is {@code false}, throwing an exception when nulls are mapped to Java primitives.
*/
public void setPrimitivesDefaultedForNullValue(boolean primitivesDefaultedForNullValue) {
this.primitivesDefaultedForNullValue = primitivesDefaultedForNullValue;
}
/**
* Return whether we're defaulting Java primitives in the case of mapping a null value
* from corresponding database fields.
*/
public boolean isPrimitivesDefaultedForNullValue() {
return this.primitivesDefaultedForNullValue;
}
/**
* Set a {@link ConversionService} for binding JDBC values to bean properties,
* or {@code null} for none.
* <p>Default is a {@link DefaultConversionService}, as of Spring 4.3. This
* provides support for {@code java.time} conversion and other special types.
* @since 4.3
* @see #initBeanWrapper(BeanWrapper)
*/
public void setConversionService(ConversionService conversionService) {
this.conversionService = conversionService;
}
/**
* Return a {@link ConversionService} for binding JDBC values to bean properties,
* or {@code null} if none.
* @since 4.3
*/
public ConversionService getConversionService() {
return this.conversionService;
}
/**
* Initialize the mapping meta-data for the given class.
* @param mappedClass the mapped class
*/
protected void initialize(Class<T> mappedClass) {
this.mappedClass = mappedClass;
this.mappedFields = new HashMap<String, PropertyDescriptor>();
this.mappedProperties = new HashSet<String>();
//添加根据注解映射的代码
this.mappedAnnotation = new HashMap<String,PropertyDescriptor>();
PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
for (PropertyDescriptor pd : pds) {
if (pd.getWriteMethod() != null) {
this.mappedFields.put(lowerCaseName(pd.getName()), pd);
String underscoredName = underscoreName(pd.getName());
if (!lowerCaseName(pd.getName()).equals(underscoredName)) {
this.mappedFields.put(underscoredName, pd);
}
this.mappedProperties.add(pd.getName());
//添加根据注解映射的代码 zfy
Method method = pd.getReadMethod();
Column column = method.getAnnotation(Column.class);
if(column!=null){
//logger.debug("找到注解："+JSON.toJSONString(column));
this.mappedAnnotation.put(column.name().replaceAll(" ",""),pd);
}//结束
}
}
logger.trace("初始化结果："+ JSON.toJSONString(mappedAnnotation));
}
/**
* Convert a name in camelCase to an underscored name in lower case.
* Any upper case letters are converted to lower case with a preceding underscore.
* @param name the original name
* @return the converted name
* @since 4.2
* @see #lowerCaseName
*/
protected String underscoreName(String name) {
if (!StringUtils.hasLength(name)) {
return "";
}
StringBuilder result = new StringBuilder();
result.append(lowerCaseName(name.substring(0, 1)));
for (int i = 1; i < name.length(); i++) {
String s = name.substring(i, i + 1);
String slc = lowerCaseName(s);
if (!s.equals(slc)) {
result.append("_").append(slc);
}
else {
result.append(s);
}
}
return result.toString();
}
/**
* Convert the given name to lower case.
* By default, conversions will happen within the US locale.
* @param name the original name
* @return the converted name
* @since 4.2
*/
protected String lowerCaseName(String name) {
return name.toLowerCase(Locale.US);
}
/**
* Extract the values for all columns in the current row.
* <p>Utilizes public setters and result set meta-data.
* @see java.sql.ResultSetMetaData
*/
@Override
public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
Assert.state(this.mappedClass != null, "Mapped class was not specified");
T mappedObject = BeanUtils.instantiateClass(this.mappedClass);
BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
initBeanWrapper(bw);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);
for (int index = 1; index <= columnCount; index++) {
String column = JdbcUtils.lookupColumnName(rsmd, index);
String field = lowerCaseName(column.replaceAll(" ", ""));
PropertyDescriptor pd = this.mappedFields.get(field);
if (pd != null) {
try {
Object value = getColumnValue(rs, index, pd);
if (rowNumber == 0 && logger.isDebugEnabled()) {
logger.trace("property Mapping column '" + column + "' to property '" + pd.getName() +
"' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "'");
}
try {
bw.setPropertyValue(pd.getName(), value);
}
catch (TypeMismatchException ex) {
if (value == null && this.primitivesDefaultedForNullValue) {
if (logger.isDebugEnabled()) {
logger.trace("Intercepted TypeMismatchException for row " + rowNumber +
" and column '" + column + "' with null value when setting property '" +
pd.getName() + "' of type '" +
ClassUtils.getQualifiedName(pd.getPropertyType()) +
"' on object: " + mappedObject, ex);
}
} else if (ex instanceof ConversionNotSupportedException) {
//logger.warn("没有对应的映射策略，"+ex.getMessage());
} else {
throw ex;
}
}
if (populatedProperties != null) {
populatedProperties.add(pd.getName());
}
}
catch (NotWritablePropertyException ex) {
throw new DataRetrievalFailureException(
"Unable to map column '" + column + "' to property '" + pd.getName() + "'", ex);
}
}
else {
//添加根据注解映射的代码 zfy
PropertyDescriptor pda = this.mappedAnnotation.get(column.replaceAll(" ", ""));
if(pda==null){
// No PropertyDescriptor found
if (rowNumber == 0 && logger.isDebugEnabled()) {
logger.warn("No property found for column '" + column + "' mapped to field '" + field + "'");
}
continue;
}
try {
Object value = getColumnValue(rs, index, pda);
if (rowNumber == 0 && logger.isDebugEnabled()) {
logger.trace("annotation Mapping column '" + column + "' to property '" + pda.getName() +
"' of type '" + ClassUtils.getQualifiedName(pda.getPropertyType()) + "'");
}
try {
bw.setPropertyValue(pda.getName(), value);
}catch (TypeMismatchException ex){
//if (value == null && this.primitivesDefaultedForNullValue) {
logger.debug("Intercepted TypeMismatchException for row " + rowNumber +
" and column '" + column + "' with null value when setting property '" +
pda.getName() + "' of type '" +
ClassUtils.getQualifiedName(pda.getPropertyType()) +
"' on object: " + mappedObject, ex);
// }else {
// throw ex;
// }
}
}catch (NotWritablePropertyException ex) {
throw new DataRetrievalFailureException(
"Unable to map column '" + column + "' to property '" + pda.getName() + "'", ex);
}
//结束 zfy
}
}
if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields " +
"necessary to populate object of class [" + this.mappedClass.getName() + "]: " +
this.mappedProperties);
}
return mappedObject;
}
public T mapRow(Map<String, Object> rs) throws Exception {
Assert.state(this.mappedClass != null, "Mapped class was not specified");
T mappedObject = BeanUtils.instantiateClass(this.mappedClass);
BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
initBeanWrapper(bw);
// ResultSetMetaData rsmd = rs.getMetaData();
// int columnCount = rsmd.getColumnCount();
Set<String> populatedProperties = (isCheckFullyPopulated() ? new HashSet<String>() : null);
for (Map.Entry<String, Object> stringObjectEntry : rs.entrySet()) {
String column = stringObjectEntry.getKey();
String field = lowerCaseName(column.replaceAll(" ", ""));
PropertyDescriptor pd = this.mappedFields.get(field);
if (pd != null) {
try {
Object value = stringObjectEntry.getValue();
// if (rowNumber == 0 && logger.isDebugEnabled()) {
// logger.trace("property Mapping column '" + column + "' to property '" + pd.getName() +
// "' of type '" + ClassUtils.getQualifiedName(pd.getPropertyType()) + "'");
// }
try {
logger.debug("getPropertyType ：{}" + pd.getPropertyType().getName() + " ,value: " + stringObjectEntry.getValue());
if (value == null || value.equals("")) {
if (pd.getPropertyType().getName().equals(String.class.getName())) {
bw.setPropertyValue(pd.getName(), value);
}
} else if (pd.getPropertyType().getName().equals(Date.class.getName())) {
// StdDateFormat dateFormat = new StdDateFormat();
bw.setPropertyValue(pd.getName(), TypeUtils.castToDate(value));
} else {
bw.setPropertyValue(pd.getName(), value);
}
} catch (TypeMismatchException ex) {
if (value == null && this.primitivesDefaultedForNullValue) {
if (logger.isDebugEnabled()) {
logger.trace("Intercepted TypeMismatchException for column '" + column + "' with null value when setting property '" +
pd.getName() + "' of type '" +
ClassUtils.getQualifiedName(pd.getPropertyType()) +
"' on object: " + mappedObject + " ,value: " + stringObjectEntry.getValue(), ex);
}
} else {
logger.error("Intercepted TypeMismatchException for column '" + column + "' with null value when setting property '" +
pd.getName() + "' of type '" +
ClassUtils.getQualifiedName(pd.getPropertyType()) +
"' on object: " + mappedObject);
throw ex;
}
}
if (populatedProperties != null) {
populatedProperties.add(pd.getName());
}
} catch (NotWritablePropertyException ex) {
throw new DataRetrievalFailureException(
"Unable to map column '" + column + "' to property '" + pd.getName() + "'", ex);
}
} else {
//添加根据注解映射的代码 zfy
PropertyDescriptor pda = this.mappedAnnotation.get(column.replaceAll(" ", ""));
if (pda == null) {
// No PropertyDescriptor found
if (logger.isDebugEnabled()) {
logger.warn("No property found for column '" + column + "' mapped to field '" + field + "'");
}
continue;
}
try {
Object value = stringObjectEntry.getValue();
if (logger.isDebugEnabled()) {
logger.trace("annotation Mapping column '" + column + "' to property '" + pda.getName() +
"' of type '" + ClassUtils.getQualifiedName(pda.getPropertyType()) + "'");
}
try {
bw.setPropertyValue(pda.getName(), value);
} catch (TypeMismatchException ex) {
//if (value == null && this.primitivesDefaultedForNullValue) {
logger.debug("Intercepted TypeMismatchException for column '" + column + "' with null value when setting property '" +
pda.getName() + "' of type '" +
ClassUtils.getQualifiedName(pda.getPropertyType()) +
"' on object: " + mappedObject, ex);
// }else {
// throw ex;
// }
}
} catch (NotWritablePropertyException ex) {
throw new DataRetrievalFailureException(
"Unable to map column '" + column + "' to property '" + pda.getName() + "'", ex);
}
//结束 zfy
}
}
if (populatedProperties != null && !populatedProperties.equals(this.mappedProperties)) {
throw new InvalidDataAccessApiUsageException("Given ResultSet does not contain all fields " +
"necessary to populate object of class [" + this.mappedClass.getName() + "]: " +
this.mappedProperties);
}
return mappedObject;
}
/**
* Initialize the given BeanWrapper to be used for row mapping.
* To be called for each row.
* <p>The default implementation applies the configured {@link ConversionService},
* if any. Can be overridden in subclasses.
* @param bw the BeanWrapper to initialize
* @see #getConversionService()
* @see BeanWrapper#setConversionService
*/
protected void initBeanWrapper(BeanWrapper bw) {
ConversionService cs = getConversionService();
if (cs != null) {
bw.setConversionService(cs);
}
}
/**
* Retrieve a JDBC object value for the specified column.
* <p>The default implementation calls
* {@link JdbcUtils#getResultSetValue(java.sql.ResultSet, int, Class)}.
* Subclasses may override this to check specific value types upfront,
* or to post-process values return from {@code getResultSetValue}.
* @param rs is the ResultSet holding the data
* @param index is the column index
* @param pd the bean property that each result object is expected to match
* @return the Object value
* @throws SQLException in case of extraction failure
* @see org.springframework.jdbc.support.JdbcUtils#getResultSetValue(java.sql.ResultSet, int, Class)
*/
protected Object getColumnValue(ResultSet rs, int index, PropertyDescriptor pd) throws SQLException {
return JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
}
/**
* Static factory method to create a new {@code BeanPropertyRowMapper}
* (with the mapped class specified only once).
* @param mappedClass the class that each row should be mapped to
*/
public static <T> BeanPropertyRowMapper<T> newInstance(Class<T> mappedClass) {
return new BeanPropertyRowMapper<T>(mappedClass);
}
}具体使用方式如下：
/**
* @Description: 通用的dao操作模板
*/
@Slf4j
@Service
public class DaoTemplate {
@Resource
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private Map<Class<?>, RowMapper> rowMapper=new HashMap<>();
/**
* sql查询 返回指定对象
* @param sql
* @param parameter
* @param tClass
* @return
*/
public <T> T queryForObject(String sql, Map<String,Object> parameter, Class<T> tClass){
try {
T t = namedParameterJdbcTemplate.queryForObject(sql,parameter,getRowMapper(tClass));
return t;
}catch (EmptyResultDataAccessException e){
log.warn("{}:{}",e,e.getMessage());
}
return null;
}
/**
* 统一获取属性映射处理器，避免重复实例化
* @param t
* @param <T>
* @return
*/
private <T> RowMapper<T> getRowMapper(Class<T> t){
if(rowMapper.containsKey(t)){
return rowMapper.get(t);
}else{
PropertyAnnotationRowMapper propertyAnnotationRowMapper = new PropertyAnnotationRowMapper(t);
rowMapper.put(t,propertyAnnotationRowMapper);
return propertyAnnotationRowMapper;
}
}
}搞定。