springboot~mybatis-plus更优雅的处理mysql8.0的json字段，以下是一些主要的新特

springboot~mybatis-plus更优雅的处理mysql8.0的json字段，以下是一些主要的新特

MySQL 8.0 引入了许多新特性和改进，旨在增强性能、可用性和安全性。以下是一些主要的新特性：数据字典，窗口函数，公共表表达式 (CTE)，JSON 改进，隐式列和生成列，字符集和排序规则，原生支持 GIS 功能，支持更强的 SSL/TLS 加密选项等。

下面文章主要介绍mysql字段为Json类型自动映射到java的实体类型的方法：

数据表结构

CREATE TABLE user_actions (
    action_id INT PRIMARY KEY,
    user_id INT,
    action_time DATETIME,
    action_details JSON
);

-- 示例数据插入
INSERT INTO user_actions (action_id, user_id, action_time, action_details) VALUES
(1, 1, '2025-04-01 10:00:00', '{"action": "login", "duration": 120}'),
(2, 1, '2025-04-02 11:00:00', '{"action": "view", "item_id": 101, "duration": 300}'),
(3, 2, '2025-04-05 12:00:00', '{"action": "login", "duration": 150}'),
(4, 3, '2025-04-08 13:00:00', '{"action": "purchase", "item_id": 102, "amount": 29.99}'),
(5, 2, '2025-04-09 14:00:00', '{"action": "view", "item_id": 103, "duration": 200}'),
(6, 3, '2025-04-09 15:00:00', '{"action": "view", "item_id": 101, "duration": 100}');

实体结构

@Data
@TableName(value = "user_actions", autoResultMap = true)
public class UserAction {

	private Integer actionId;

	private Integer userId;

	private LocalDateTime actionTime;

	@TableField(typeHandler = JsonTypeHandler.class)
	private ActionDetail actionDetails;

}

@Data
class ActionDetail {

	private String action;

	private Integer duration;

	@JsonProperty("item_id")
	private Integer itemId;

	private double amount;

}

注意：实体类需要添加@TableName(autoResultMap = true)

字段上添加对应的注解@TableField(typeHandler = JsonTypeHandler.class)

类型转换器JsonTypeHandler代码

@MappedTypes(Map.class)
public class JsonTypeHandler<T> extends BaseTypeHandler<T> {

	private static final ObjectMapper objectMapper = new ObjectMapper();

	private Class<T> type;

	public JsonTypeHandler(Class<T> type) {
		this.type = type;
	}

	@Override
	public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
		try {
			ps.setString(i, objectMapper.writeValueAsString(parameter));
		}
		catch (IOException e) {
			throw new SQLException("Failed to convert JSON to String", e);
		}
	}

	@Override
	public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
		String json = rs.getString(columnName);
		if (json != null) {
			try {
				return objectMapper.readValue(json, type);
			}
			catch (IOException e) {
				throw new SQLException("Failed to convert String to JSON", e);
			}
		}
		return null;
	}

	@Override
	public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
		String json = rs.getString(columnIndex);
		if (json != null) {
			try {
				return objectMapper.readValue(json, type);
			}
			catch (IOException e) {
				throw new SQLException("Failed to convert String to JSON", e);
			}
		}
		return null;
	}

	@Override
	public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
		String json = cs.getString(columnIndex);
		if (json != null) {
			try {
				return objectMapper.readValue(json, type);
			}
			catch (IOException e) {
				throw new SQLException("Failed to convert String to JSON", e);
			}
		}
		return null;
	}

}

最后，可正常解析出结果

作者：仓储大叔，张占岭，
荣誉：微软MVP
QQ：853066980

支付宝扫一扫，为大叔打赏!