聊聊spring jdbc的RowMapper

本文主要介绍下spring jdbc的RowMapper

RowMapper

spring-jdbc-4.3.10.RELEASE-sources.jar!/org/springframework/jdbc/core/RowMapper.java

public interface RowMapper<T> {

    /**
     * Implementations must implement this method to map each row of data
     * in the ResultSet. This method should not call {@code next()} on
     * the ResultSet; it is only supposed to map values of the current row.
     * @param rs the ResultSet to map (pre-initialized for the current row)
     * @param rowNum the number of the current row
     * @return the result object for the current row
     * @throws SQLException if a SQLException is encountered getting
     * column values (that is, there's no need to catch SQLException)
     */
    T mapRow(ResultSet rs, int rowNum) throws SQLException;

}

spring定义了这个RowMapper,来让应用去自定义数据库结果集与实体的映射,这样来把变化的部分隔离出去

ColumnMapRowMapper

spring-jdbc-4.3.7.RELEASE-sources.jar!/org/springframework/jdbc/core/ColumnMapRowMapper.java

public class ColumnMapRowMapper implements RowMapper<Map<String, Object>> {

    @Override
    public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        Map<String, Object> mapOfColValues = createColumnMap(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            String key = getColumnKey(JdbcUtils.lookupColumnName(rsmd, i));
            Object obj = getColumnValue(rs, i);
            mapOfColValues.put(key, obj);
        }
        return mapOfColValues;
    }

    /**
     * Create a Map instance to be used as column map.
     * <p>By default, a linked case-insensitive Map will be created.
     * @param columnCount the column count, to be used as initial
     * capacity for the Map
     * @return the new Map instance
     * @see org.springframework.util.LinkedCaseInsensitiveMap
     */
    protected Map<String, Object> createColumnMap(int columnCount) {
        return new LinkedCaseInsensitiveMap<Object>(columnCount);
    }

    /**
     * Determine the key to use for the given column in the column Map.
     * @param columnName the column name as returned by the ResultSet
     * @return the column key to use
     * @see java.sql.ResultSetMetaData#getColumnName
     */
    protected String getColumnKey(String columnName) {
        return columnName;
    }

    /**
     * Retrieve a JDBC object value for the specified column.
     * <p>The default implementation uses the {@code getObject} method.
     * Additionally, this implementation includes a "hack" to get around Oracle
     * returning a non standard object for their TIMESTAMP datatype.
     * @param rs is the ResultSet holding the data
     * @param index is the column index
     * @return the Object returned
     * @see org.springframework.jdbc.support.JdbcUtils#getResultSetValue
     */
    protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index);
    }

}

将结果集映射为map

SingleColumnRowMapper

spring-jdbc-4.3.10.RELEASE-sources.jar!/org/springframework/jdbc/core/SingleColumnRowMapper.java

public class SingleColumnRowMapper<T> implements RowMapper<T> {

    private Class<?> requiredType;

    /**
     * Create a new {@code SingleColumnRowMapper} for bean-style configuration.
     * @see #setRequiredType
     */
    public SingleColumnRowMapper() {
    }

    /**
     * Create a new {@code SingleColumnRowMapper}.
     * <p>Consider using the {@link #newInstance} factory method instead,
     * which allows for specifying the required type once only.
     * @param requiredType the type that each result object is expected to match
     */
    public SingleColumnRowMapper(Class<T> requiredType) {
        setRequiredType(requiredType);
    }

    /**
     * Set the type that each result object is expected to match.
     * <p>If not specified, the column value will be exposed as
     * returned by the JDBC driver.
     */
    public void setRequiredType(Class<T> requiredType) {
        this.requiredType = ClassUtils.resolvePrimitiveIfNecessary(requiredType);
    }

    /**
     * Extract a value for the single column in the current row.
     * <p>Validates that there is only one column selected,
     * then delegates to {@code getColumnValue()} and also
     * {@code convertValueToRequiredType}, if necessary.
     * @see java.sql.ResultSetMetaData#getColumnCount()
     * @see #getColumnValue(java.sql.ResultSet, int, Class)
     * @see #convertValueToRequiredType(Object, Class)
     */
    @Override
    @SuppressWarnings("unchecked")
    public T mapRow(ResultSet rs, int rowNum) throws SQLException {
        // Validate column count.
        ResultSetMetaData rsmd = rs.getMetaData();
        int nrOfColumns = rsmd.getColumnCount();
        if (nrOfColumns != 1) {
            throw new IncorrectResultSetColumnCountException(1, nrOfColumns);
        }

        // Extract column value from JDBC ResultSet.
        Object result = getColumnValue(rs, 1, this.requiredType);
        if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) {
            // Extracted value does not match already: try to convert it.
            try {
                return (T) convertValueToRequiredType(result, this.requiredType);
            }
            catch (IllegalArgumentException ex) {
                throw new TypeMismatchDataAccessException(
                        "Type mismatch affecting row number " + rowNum + " and column type '" +
                        rsmd.getColumnTypeName(1) + "': " + ex.getMessage());
            }
        }
        return (T) result;
    }

    /**
     * Retrieve a JDBC object value for the specified column.
     * <p>The default implementation calls
     * {@link JdbcUtils#getResultSetValue(java.sql.ResultSet, int, Class)}.
     * If no required type has been specified, this method delegates to
     * {@code getColumnValue(rs, index)}, which basically calls
     * {@code ResultSet.getObject(index)} but applies some additional
     * default conversion to appropriate value types.
     * @param rs is the ResultSet holding the data
     * @param index is the column index
     * @param requiredType the type that each result object is expected to match
     * (or {@code null} if none specified)
     * @return the Object value
     * @throws SQLException in case of extraction failure
     * @see org.springframework.jdbc.support.JdbcUtils#getResultSetValue(java.sql.ResultSet, int, Class)
     * @see #getColumnValue(java.sql.ResultSet, int)
     */
    protected Object getColumnValue(ResultSet rs, int index, Class<?> requiredType) throws SQLException {
        if (requiredType != null) {
            return JdbcUtils.getResultSetValue(rs, index, requiredType);
        }
        else {
            // No required type specified -> perform default extraction.
            return getColumnValue(rs, index);
        }
    }

    /**
     * Retrieve a JDBC object value for the specified column, using the most
     * appropriate value type. Called if no required type has been specified.
     * <p>The default implementation delegates to {@code JdbcUtils.getResultSetValue()},
     * which uses the {@code ResultSet.getObject(index)} method. Additionally,
     * it includes a "hack" to get around Oracle returning a non-standard object for
     * their TIMESTAMP datatype. See the {@code JdbcUtils#getResultSetValue()}
     * javadoc for details.
     * @param rs is the ResultSet holding the data
     * @param index is the column index
     * @return the Object value
     * @throws SQLException in case of extraction failure
     * @see org.springframework.jdbc.support.JdbcUtils#getResultSetValue(java.sql.ResultSet, int)
     */
    protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index);
    }

    /**
     * Convert the given column value to the specified required type.
     * Only called if the extracted column value does not match already.
     * <p>If the required type is String, the value will simply get stringified
     * via {@code toString()}. In case of a Number, the value will be
     * converted into a Number, either through number conversion or through
     * String parsing (depending on the value type).
     * @param value the column value as extracted from {@code getColumnValue()}
     * (never {@code null})
     * @param requiredType the type that each result object is expected to match
     * (never {@code null})
     * @return the converted value
     * @see #getColumnValue(java.sql.ResultSet, int, Class)
     */
    @SuppressWarnings("unchecked")
    protected Object convertValueToRequiredType(Object value, Class<?> requiredType) {
        if (String.class == requiredType) {
            return value.toString();
        }
        else if (Number.class.isAssignableFrom(requiredType)) {
            if (value instanceof Number) {
                // Convert original Number to target Number class.
                return NumberUtils.convertNumberToTargetClass(((Number) value), (Class<Number>) requiredType);
            }
            else {
                // Convert stringified value to target Number class.
                return NumberUtils.parseNumber(value.toString(),(Class<Number>) requiredType);
            }
        }
        else {
            throw new IllegalArgumentException(
                    "Value [" + value + "] is of type [" + value.getClass().getName() +
                    "] and cannot be converted to required type [" + requiredType.getName() + "]");
        }
    }

    /**
     * Static factory method to create a new {@code SingleColumnRowMapper}
     * (with the required type specified only once).
     * @param requiredType the type that each result object is expected to match
     * @since 4.1
     */
    public static <T> SingleColumnRowMapper<T> newInstance(Class<T> requiredType) {
        return new SingleColumnRowMapper<T>(requiredType);
    }

}

映射单个字段,比如count(*)这种

BeanPropertyRowMapper

spring-jdbc-4.3.7.RELEASE-sources.jar!/org/springframework/jdbc/core/BeanPropertyRowMapper.java

public class BeanPropertyRowMapper<T> implements RowMapper<T> {
//...
    protected void initialize(Class<T> mappedClass) {
        this.mappedClass = mappedClass;
        this.mappedFields = new HashMap<String, PropertyDescriptor>();
        this.mappedProperties = new HashSet<String>();
        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());
            }
        }
    }

    @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.debug("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.debug("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 {
                            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 {
                // No PropertyDescriptor found
                if (rowNumber == 0 && logger.isDebugEnabled()) {
                    logger.debug("No property found for column '" + column + "' mapped to field '" + field + "'");
                }
            }
        }

        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;
    }
}

初始化构造map的时候,存的key是下划线的 如果数据库字段命名跟实体类一致,或者是下划线变驼峰的这种,那么可以直接使用这个

实例

  • 使用BeanPropertyRowMapper的版本 public Book findById(Integer id){ return jdbcTemplate.query("select * from book where book_id=?",new Object[]{id},new BeanPropertyRowMapper<Book>(Book.class)).get(0); }
  • 不使用BeanPropertyRowMapper的版本 public Book findById2(Integer id){ return jdbcTemplate.query("select * from book where book_id=?",new Object[]{id},new RowMapper<Book>() { @Override public Book mapRow(ResultSet rs, int rowNum) throws SQLException { Book book = new Book(); book.setBookId(rs.getInt("book_id")); book.setTitle(rs.getString("title")); book.setCreatedAt(rs.getTimestamp("created_at")); return book; } }).get(0); }

有没有发现使用了BeanPropertyRowMapper更为简洁。

原文发布于微信公众号 - 码匠的流水账(geek_luandun)

原文发表时间:2017-11-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JMCui

项目工具类

一、前言     在工作中,难免遇到各种各样的问题,每个人似乎都有一套自己的解决方案。而我,又不想每次解决完问题就把东西扔了,捡了芝麻,丢了西瓜,什么时候才能进...

49260
来自专栏HansBug's Lab

2060: [Usaco2010 Nov]Visiting Cows 拜访奶牛

2060: [Usaco2010 Nov]Visiting Cows 拜访奶牛 Time Limit: 3 Sec  Memory Limit: 64 MB S...

36180
来自专栏Hongten

Java Web Commons-Utils (数据库连接方法)

    数据库驱动类:Oracle: ojdbc6.jar                   MySQL: mysql-connector-java-5.1....

19430
来自专栏用户画像

Mysql数据库连接类

10210
来自专栏landv

excel vba获取拼音

18520
来自专栏文武兼修ing——机器学习与IC设计

基于迭代单元的除法器基于迭代单元的除法器

基于迭代单元的除法器 迭代单元 数字信号处理中,有大量的算法是基于迭代算法,即下一次的运算需要上一次运算的结果,将运算部分固化为迭代单元可以将数据处理和流程控制...

30650
来自专栏函数式编程语言及工具

PICE(1):Programming In Clustered Environment - 集群环境内编程模式

首先声明:标题上的所谓编程模式是我个人考虑在集群环境下跨节点(jvm)的流程控制编程模式,纯粹按实际需要构想,没什么理论支持。在5月份的深圳scala mee...

9430
来自专栏专注研发

封装jdbc、DBUtil

JDBC-util 封装了jdbc并封装dbutil方法,查询直接返回List<map<String,Object>>,将一大堆代码放在一块是真的不好看

32120
来自专栏函数式编程语言及工具

SDP(7):Cassandra- Cassandra-Engine:Streaming

  akka在alpakka工具包里提供了对cassandra数据库的streaming功能。简单来讲就是用一个CQL-statement读取cassandra...

35560
来自专栏函数式编程语言及工具

SDP(12): MongoDB-Engine - Streaming

   在akka-alpakka工具包里也提供了对MongoDB的stream-connector,能针对MongoDB数据库进行streaming操作。这个M...

505100

扫码关注云+社区

领取腾讯云代金券