JdbcTemplate使用小结

　　org.springframework.jdbc.core.JdbcTemplate.query(String sql, Object[] args, RowMapper<StaffUnionVO> rowMapper) throws DataAccessException

1.自定义rowMapper

public class StaffUnionVO implements RowMapper<StaffUnionVO>, Serializable {
    private static final long serialVersionUID = 1544023434308856628L;

    public StaffUnionVO() {
    }
　　private String code;// 员工编码
　　
    private String name;// 员工姓名

  
　　public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }private boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        }

        return false;
    }

    @Override
    public StaffUnionVO mapRow(ResultSet rs, int row) throws SQLException {
        StaffUnionVO vo = new StaffUnionVO();     
　
　　　　if (isExistColumn(rs, "code"))
            vo.setCode(rs.getString("code"));
　　　　if (isExistColumn(rs, "name"))
            vo.setName(rs.getString("name"));        
　　
　　　　return vo;
    }
}

　　示例: List<StaffUnionVO> vos = JdbcTemplate.query(sql, new Object[0], new StaffUnionVO() );

 

2.使用BeanPropertyRowMapper

public class StaffUnionVO implements Serializable {
    private static final long serialVersionUID = 1544023434308856628L;

    public StaffUnionVO() {
    }

    private String code;// 员工编码

    private String name;// 员工姓名

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

　　示例：List<StaffUnionVO> vos = JdbcTemplate.query(sql, new Object[0], BeanPropertyRowMapper.newInstance(StaffUnionVO.class));

 

　　看一下 BeanPropertyRowMapper.java 的源码，可以学到不少东西。

/*** Eclipse Class Decompiler plugin, copyright (c) 2016 Chen Chao (cnfree2000@hotmail.com) ***/
package org.springframework.jdbc.core;

import java.beans.PropertyDescriptor;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.NotWritablePropertyException;
import org.springframework.beans.PropertyAccessorFactory;
import org.springframework.beans.TypeMismatchException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

public class BeanPropertyRowMapper<T> implements RowMapper<T> {
    protected final Log logger = LogFactory.getLog(super.getClass());
    private Class<T> mappedClass;
    private boolean checkFullyPopulated = false;

    private boolean primitivesDefaultedForNullValue = false;
    private Map<String, PropertyDescriptor> mappedFields;
    private Set<String> mappedProperties;

    public BeanPropertyRowMapper() {
    }

    public BeanPropertyRowMapper(Class<T> mappedClass) {
        initialize(mappedClass);
    }

    public BeanPropertyRowMapper(Class<T> mappedClass, boolean checkFullyPopulated) {
        initialize(mappedClass);
        this.checkFullyPopulated = checkFullyPopulated;
    }

    public void setMappedClass(Class<T> mappedClass) {
        if (this.mappedClass == null) {
            initialize(mappedClass);
        } else if (!(this.mappedClass.equals(mappedClass)))
            throw new InvalidDataAccessApiUsageException(
                    new StringBuilder().append("The mapped class can not be reassigned to map to ").append(mappedClass)
                            .append(" since it is already providing mapping for ").append(this.mappedClass).toString());
    }

    protected void initialize(Class<T> mappedClass) {
        this.mappedClass = mappedClass;
        this.mappedFields = new HashMap();
        this.mappedProperties = new HashSet();
        PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
        for (PropertyDescriptor pd : pds)
            if (pd.getWriteMethod() != null) {
                this.mappedFields.put(pd.getName().toLowerCase(), pd);
                String underscoredName = underscoreName(pd.getName());
                if (!(pd.getName().toLowerCase().equals(underscoredName))) {
                    this.mappedFields.put(underscoredName, pd);
                }
                this.mappedProperties.add(pd.getName());
            }
    }
　　//在大写的字符前加入下滑线
    private String underscoreName(String name) {
        if (!(StringUtils.hasLength(name))) {
            return "";
        }
        StringBuilder result = new StringBuilder();
        result.append(name.substring(0, 1).toLowerCase());
        for (int i = 1; i < name.length(); ++i) {
            String s = name.substring(i, i + 1);
            String slc = s.toLowerCase();
            if (!(s.equals(slc))) {
                result.append("_").append(slc);
            } else {
                result.append(s);
            }
        }
        return result.toString();
    }

    public final Class<T> getMappedClass() {
        return this.mappedClass;
    }

    public void setCheckFullyPopulated(boolean checkFullyPopulated) {
        this.checkFullyPopulated = checkFullyPopulated;
    }

    public boolean isCheckFullyPopulated() {
        return this.checkFullyPopulated;
    }

    public void setPrimitivesDefaultedForNullValue(boolean primitivesDefaultedForNullValue) {
        this.primitivesDefaultedForNullValue = primitivesDefaultedForNullValue;
    }

    public boolean isPrimitivesDefaultedForNullValue() {
        return this.primitivesDefaultedForNullValue;
    }

    public T mapRow(ResultSet rs, int rowNumber) throws SQLException {
        Assert.state(this.mappedClass != null, "Mapped class was not specified");
        Object mappedObject = BeanUtils.instantiate(this.mappedClass);
        BeanWrapper bw = PropertyAccessorFactory.forBeanPropertyAccess(mappedObject);
        initBeanWrapper(bw);

        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Set populatedProperties = (isCheckFullyPopulated()) ? new HashSet() : null;//稀少的特性

        for (int index = 1; index <= columnCount; ++index) {
            String column = JdbcUtils.lookupColumnName(rsmd, index);
            PropertyDescriptor pd = (PropertyDescriptor) this.mappedFields
                    .get(column.replaceAll(" ", "").toLowerCase());
            if (pd == null)
                continue;
            try {
                Object value = getColumnValue(rs, index, pd);
                if ((this.logger.isDebugEnabled()) && (rowNumber == 0))
                    this.logger.debug(
                            new StringBuilder().append("Mapping column '").append(column).append("' to property '")
                                    .append(pd.getName()).append("' of type ").append(pd.getPropertyType()).toString());
                try {
                    bw.setPropertyValue(pd.getName(), value);
                } catch (TypeMismatchException e) {
                    if ((value == null) && (this.primitivesDefaultedForNullValue)) {
                        this.logger.debug(new StringBuilder().append("Intercepted TypeMismatchException for row ")
                                .append(rowNumber).append(" and column '").append(column).append("' with value ")
                                .append(value).append(" when setting property '").append(pd.getName())
                                .append("' of type ").append(pd.getPropertyType()).append(" on object: ")
                                .append(mappedObject).toString());
                    } else {
                        throw e;
                    }
                }
                if (populatedProperties != null) {
                    populatedProperties.add(pd.getName());
                }
            } catch (NotWritablePropertyException ex) {
                throw new DataRetrievalFailureException(new StringBuilder().append("Unable to map column ")
                        .append(column).append(" to property ").append(pd.getName()).toString(), ex);
            }

        }

        if ((populatedProperties != null) && (!(populatedProperties.equals(this.mappedProperties)))) {
            throw new InvalidDataAccessApiUsageException(new StringBuilder()
                    .append("Given ResultSet does not contain all fields necessary to populate object of class [")
                    .append(this.mappedClass).append("]: ").append(this.mappedProperties).toString());
        }

        return mappedObject;
    }

    protected void initBeanWrapper(BeanWrapper bw) {
    }

    protected Object getColumnValue(ResultSet rs, int index, PropertyDescriptor pd) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index, pd.getPropertyType());
    }

    public static <T> BeanPropertyRowMapper<T> newInstance(Class<T> mappedClass) {
        BeanPropertyRowMapper newInstance = new BeanPropertyRowMapper();
        newInstance.setMappedClass(mappedClass);
        return newInstance;
    }
}

　　1.通过PropertyDescriptor反映射调用set和get方法

　　2.HashSet、TreeSet equals方法

　　AbstractSet.java

　　public boolean equals(Object o) {
        if (o == this)
            return true;

        if (!(o instanceof Set))
            return false;
        Collection<?> c = (Collection<?>) o;
        if (c.size() != size())
            return false;
        try {
            return containsAll(c);
        } catch (ClassCastException unused)   {
            return false;
        } catch (NullPointerException unused) {
            return false;
        }
    }

 

　　3.BeanPropertyRowMapper checkFullyPopulated 默认是false，这样的话如果 sql结果集中的字段 和 DTO 字段不匹配，就会抛异常。可以手动设置这个值。

　　4.PO BO VO DTO POJO DAO概念及其作用（附转换图）