Spring是如何将jdbc参数转换为对象的

2024-03-12 源码 converter, jdbc 0 评论

前言

jdbc如何将mysql数据转换为字段为java类型的map

对照DDL, 可以更清晰的理解字段是如何完成转换的

CREATE TABLE `book` (
  `id` bigint(20) NOT NULL,
  `create_time` datetime(6) DEFAULT NULL,
  `price` decimal(19,2) DEFAULT NULL,
  `publish_date` date DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

我们的单元测试如下

package cn.idea360.i18n;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;

import javax.annotation.Resource;
import java.sql.*;
import java.util.Map;
import java.util.Objects;

/**
 * @author cuishiying
 * @date 2024-03-12
 */
@SpringBootTest
public class JdbcTest {

    @Resource
    private JdbcTemplate jdbcTemplate;

    private String sql;

    @BeforeEach
    void init() {
        sql = "SELECT * FROM book where id = 1 order by id";
    }

    @Test
    void jdbc_test() {
        String url = "jdbc:mysql://localhost:3306/master?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";

        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnTypeName = rsmd.getColumnTypeName(i);
                    String column = JdbcUtils.lookupColumnName(rsmd, i);
                    Object value = JdbcUtils.getResultSetValue(rs, i);
                    String entityFieldTypeName = Objects.isNull(value) ? "" : value.getClass().getName();
                    System.out.printf("column: %s, value: %s, columnTypeName: %s, entityFieldTypeName: %s\n", column, value, columnTypeName, entityFieldTypeName);
                }
                System.out.println("-------");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    void spring_jdbc_test() {
        Map<String, Object> row = jdbcTemplate.queryForMap(sql);
        row.forEach((key, value) -> {
            String entityFieldTypeName = Objects.isNull(value) ? "" : value.getClass().getName();
            System.out.printf("key: %s, value: %s, entityFieldTypeName: %s\n", key, value, entityFieldTypeName);
        });
    }
}

上述2个测试结果是一致的, 为了分析 spring-jdbc 实现逻辑, 我们贴出 spring_jdbc_test 输出结果

key: id, value: 1, entityFieldTypeName: java.lang.Long
key: create_time, value: 2024-02-27T16:17, entityFieldTypeName: java.time.LocalDateTime
key: price, value: 77.77, entityFieldTypeName: java.math.BigDecimal
key: publish_date, value: 2007-11-11, entityFieldTypeName: java.sql.Date
key: title, value: 当我遇上你, entityFieldTypeName: java.lang.String

调用链分析

函数入口 org.springframework.jdbc.core.JdbcTemplate#queryForMap(java.lang.String), 默认类型转换是用 ColumnMapRowMapper 进行转换的.
关键实现如下

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> mapOfColumnValues = createColumnMap(columnCount);
        for (int i = 1; i <= columnCount; i++) {
            String column = JdbcUtils.lookupColumnName(rsmd, i);
            mapOfColumnValues.putIfAbsent(getColumnKey(column), getColumnValue(rs, i));
        }
        return mapOfColumnValues;
    }

    protected Map<String, Object> createColumnMap(int columnCount) {
        return new LinkedCaseInsensitiveMap<>(columnCount);
    }

    protected String getColumnKey(String columnName) {
        return columnName;
    }

    @Nullable
    protected Object getColumnValue(ResultSet rs, int index) throws SQLException {
        return JdbcUtils.getResultSetValue(rs, index);
    }

}

然后进入到 org.springframework.jdbc.support.JdbcUtils#getResultSetValue(java.sql.ResultSet, int), 这个方法就是进行值转换处理的

@Nullable
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
    Object obj = rs.getObject(index);
    String className = null;
    if (obj != null) {
        className = obj.getClass().getName();
    }
    if (obj instanceof Blob) {
        Blob blob = (Blob) obj;
        obj = blob.getBytes(1, (int) blob.length());
    }
    else if (obj instanceof Clob) {
        Clob clob = (Clob) obj;
        obj = clob.getSubString(1, (int) clob.length());
    }
    else if ("oracle.sql.TIMESTAMP".equals(className) || "oracle.sql.TIMESTAMPTZ".equals(className)) {
        obj = rs.getTimestamp(index);
    }
    else if (className != null && className.startsWith("oracle.sql.DATE")) {
        String metaDataClassName = rs.getMetaData().getColumnClassName(index);
        if ("java.sql.Timestamp".equals(metaDataClassName) || "oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
            obj = rs.getTimestamp(index);
        }
        else {
            obj = rs.getDate(index);
        }
    }
    else if (obj instanceof java.sql.Date) {
        if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
            obj = rs.getTimestamp(index);
        }
    }
    return obj;
}

java.sql.ResultSet#getObject(int) 是jdk中定义的接口，由各个驱动实现, 如 mysql 驱动实现如下:

@Override
public Object getObject(int columnIndex) throws SQLException {
    checkRowPos();
    checkColumnBounds(columnIndex);

    int columnIndexMinusOne = columnIndex - 1;

    // we can't completely rely on code below because primitives have default values for null (e.g. int->0)
    if (this.thisRow.getNull(columnIndexMinusOne)) {
        return null;
    }

    Field field = this.columnDefinition.getFields()[columnIndexMinusOne];
    switch (field.getMysqlType()) {
        case BIT:
            // TODO Field sets binary and blob flags if the length of BIT field is > 1; is it needed at all?
            if (field.isBinary() || field.isBlob()) {
                byte[] data = getBytes(columnIndex);

                if (this.connection.getPropertySet().getBooleanProperty(PropertyKey.autoDeserialize).getValue()) {
                    Object obj = data;

                    if ((data != null) && (data.length >= 2)) {
                        if ((data[0] == -84) && (data[1] == -19)) {
                            // Serialized object?
                            try {
                                ByteArrayInputStream bytesIn = new ByteArrayInputStream(data);
                                ObjectInputStream objIn = new ObjectInputStream(bytesIn);
                                obj = objIn.readObject();
                                objIn.close();
                                bytesIn.close();
                            } catch (ClassNotFoundException cnfe) {
                                throw SQLError.createSQLException(Messages.getString("ResultSet.Class_not_found___91") + cnfe.toString()
                                        + Messages.getString("ResultSet._while_reading_serialized_object_92"), getExceptionInterceptor());
                            } catch (IOException ex) {
                                obj = data; // not serialized?
                            }
                        } else {
                            return getString(columnIndex);
                        }
                    }

                    return obj;
                }

                return data;
            }

            return field.isSingleBit() ? Boolean.valueOf(getBoolean(columnIndex)) : getBytes(columnIndex);

        case BOOLEAN:
            return Boolean.valueOf(getBoolean(columnIndex));

        case TINYINT:
            return Integer.valueOf(getByte(columnIndex));

        case TINYINT_UNSIGNED:
        case SMALLINT:
        case SMALLINT_UNSIGNED:
        case MEDIUMINT:
        case MEDIUMINT_UNSIGNED:
        case INT:
            return Integer.valueOf(getInt(columnIndex));

        case INT_UNSIGNED:
        case BIGINT:
            return Long.valueOf(getLong(columnIndex));

        case BIGINT_UNSIGNED:
            return getBigInteger(columnIndex);

        case DECIMAL:
        case DECIMAL_UNSIGNED:
            String stringVal = getString(columnIndex);

            if (stringVal != null) {
                if (stringVal.length() == 0) {
                    return new BigDecimal(0);
                }

                try {
                    return new BigDecimal(stringVal);
                } catch (NumberFormatException ex) {
                    throw SQLError.createSQLException(
                            Messages.getString("ResultSet.Bad_format_for_BigDecimal", new Object[] { stringVal, Integer.valueOf(columnIndex) }),
                            MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
                }
            }
            return null;

        case FLOAT:
        case FLOAT_UNSIGNED:
            return new Float(getFloat(columnIndex));

        case DOUBLE:
        case DOUBLE_UNSIGNED:
            return new Double(getDouble(columnIndex));

        case CHAR:
        case ENUM:
        case SET:
        case VARCHAR:
        case TINYTEXT:
            return getString(columnIndex);

        case TEXT:
        case MEDIUMTEXT:
        case LONGTEXT:
        case JSON:
            return getStringForClob(columnIndex);

        case GEOMETRY:
            return getBytes(columnIndex);

        case BINARY:
        case VARBINARY:
        case TINYBLOB:
        case MEDIUMBLOB:
        case LONGBLOB:
        case BLOB:
            if (field.isBinary() || field.isBlob()) {
                byte[] data = getBytes(columnIndex);

                if (this.connection.getPropertySet().getBooleanProperty(PropertyKey.autoDeserialize).getValue()) {
                    Object obj = data;

                    if ((data != null) && (data.length >= 2)) {
                        if ((data[0] == -84) && (data[1] == -19)) {
                            // Serialized object?
                            try {
                                ByteArrayInputStream bytesIn = new ByteArrayInputStream(data);
                                ObjectInputStream objIn = new ObjectInputStream(bytesIn);
                                obj = objIn.readObject();
                                objIn.close();
                                bytesIn.close();
                            } catch (ClassNotFoundException cnfe) {
                                throw SQLError.createSQLException(Messages.getString("ResultSet.Class_not_found___91") + cnfe.toString()
                                        + Messages.getString("ResultSet._while_reading_serialized_object_92"), getExceptionInterceptor());
                            } catch (IOException ex) {
                                obj = data; // not serialized?
                            }
                        } else {
                            return getString(columnIndex);
                        }
                    }

                    return obj;
                }

                return data;
            }

            return getBytes(columnIndex);

        case YEAR:
            return this.yearIsDateType ? getDate(columnIndex) : Short.valueOf(getShort(columnIndex));

        case DATE:
            return getDate(columnIndex);

        case TIME:
            return getTime(columnIndex);

        case TIMESTAMP:
            return getTimestamp(columnIndex);

        case DATETIME:
            return getLocalDateTime(columnIndex);

        default:
            return getString(columnIndex);
    }
}

如果默认的类型不满足, 可以自定义映射关系

@Test
    void custom_jdbc_test() {
        String url = "jdbc:mysql://localhost:3306/master?serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "root";

        try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    String columnTypeName = rsmd.getColumnTypeName(i);
                    String column = JdbcUtils.lookupColumnName(rsmd, i);

                    // 自定义类型转换
                    int columnType = rsmd.getColumnType(i);
                    Object value = null;
                    switch (columnType) {
                        case Types.CHAR:
                        case Types.NCHAR:
                        case Types.VARCHAR:
                        case Types.LONGVARCHAR:
                        case Types.NVARCHAR:
                        case Types.LONGNVARCHAR:
                            value = rs.getString(i);
                            break;
                        case Types.SMALLINT:
                        case Types.TINYINT:
                        case Types.INTEGER:
                        case Types.BIGINT:
                            value = rs.getLong(i);
                            break;
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                            value = rs.getDouble(i);
                            break;
                        case Types.DATE:
                        case Types.TIMESTAMP:
                            value = rs.getDate(i);
                            break;
                        default:
                            System.out.println("columnType:" + columnType);
                            throw new RuntimeException("不支持的参数类型");
                    }

                    String entityFieldTypeName = Objects.isNull(value) ? "" : value.getClass().getName();
                    System.out.printf("column: %s, value: %s, columnTypeName: %s, entityFieldTypeName: %s\n", column, value, columnTypeName, entityFieldTypeName);
                }
                System.out.println("-------");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

补充

jdbcTemplate是通过bean定义实现通用转换的, 如果需要了解jpa中根据@Column转换, 可以参考hibernate内部实现
hibernate中MySqlDialect描述了java字段和mysql字段的映射关系

本文链接： https://idea360.cn/2024/03/12/spring-jdbc-convert/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！