Talk about RowMapper of spring jdbc.

  jdbc

Order

This article mainly introduces the RowMapper of spring jdbc.

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 defines the RowMapper to allow applications to customize the mapping of database result sets to entities, thus isolating the changed parts.

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 result set to 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);
    }

}

Map individual fields, such as 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;
    }
}

When initializing the map construction, the stored key is underlined
If the name of the database field is the same as that of the entity class, or if the underline changes to hump, then this can be used directly.

Example

  • Use the version of 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);
    }
  • Do not use the version of 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);
    }

Did you find BeanPropertyRowMapper more concise?