MySQL的Json类型数据操作方法

前言

MySQL在5.7增加了json类型数据，项目中最近有在使用。直接使用JSONObject或者JSONArray类型的时候会报以下的异常。发现需要将Java实体字段设置为String类型，然后需要的时候自己转换为JSONObject或者JSONArray。使用很不方便，经研究发现，可以使用Mybatis的TypeHandler解决这个问题

【org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property=‘et.ext’, mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId=‘null’, jdbcTypeName=‘null’, expression=‘null’}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Cannot convert class com.example.mybatis_demo.dao.UserMapperTest$2 to SQL type requested due to com.mysql.cj.exceptions.WrongArgumentException - Invalid argument value: java.io.NotSerializableException】

环境

JDK：1.8
SpringBoot: 2.7.2
Mybatis：3.5.5
MybatisPlus：3.4.0
Mysql：5.7

解决方案

第一步、实体字段设置为JSONObject和JSONArray

第二步、增加对应的TypeHandler

package com.example.mybatis_demo.typeHandler;import com.alibaba.fastjson.JSONArray;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;/*** @author dzh* @since 2024/11/29*/
@MappedTypes({JSONArray.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JSONArrayTypeHandler extends FastjsonTypeHandler {public JSONArrayTypeHandler(Class<?> type) {super(type);}
}

package com.example.mybatis_demo.typeHandler;import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;@MappedTypes({JSONObject.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JSONObjectTypeHandler extends FastjsonTypeHandler {public JSONObjectTypeHandler(Class<?> type) {super(type);}
}

第三步、application.yml中增加配置

mybatis-plus:# 自定义的typeHandler的包路径type-handlers-package: com.example.mybatis_demo.typeHandler

源码

实体

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User {private Long id;private JSONObject ext;private JSONArray extArray;
}

测试类

@Slf4j
@SpringBootTest
class UserMapperTest {@Resourceprivate UserMapper userMapper;@Testvoid ext() {User user = User.builder().id(-1L).ext(new JSONObject() {{put("name", "Lucy");put("birthday", new Date());}}).extArray(new JSONArray(){{add(new JSONObject() {{put("name", "Lucy");put("age", 18);}});add(new JSONObject() {{put("name", "Lily");put("birthday", 22);}});add(new JSONObject() {{put("name", "Jack");put("birthday", 6);}});}}).build();userMapper.insert(user);System.out.println(userMapper.selectById(user.getId()));}
}

表结构

create table test.user
(id int auto_incrementprimary key,ext json null,ext_array json null
);

演示结果

User(id=-1, 
ext={"birthday":1732870727259,"name":"Lucy"}, 
extArray=[{"name":"Lucy","age":18},{"birthday":22,"name":"Lily"},{"birthday":6,"name":"Jack"}])

排坑

这个配置要配置正确，不然会报异常

mybatis-plus:type-handlers-package:

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='et.ext', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Cannot convert class com.example.mybatis_demo.dao.UserMapperTest$2 to SQL type requested due to com.mysql.cj.exceptions.WrongArgumentException - Invalid argument value: java.io.NotSerializableExceptionat org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)at com.sun.proxy.$Proxy72.update(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287)at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:65)at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)at com.sun.proxy.$Proxy77.updateById(Unknown Source)at com.example.mybatis_demo.dao.UserMapperTest.ext(UserMapperTest.java:47)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)at java.util.ArrayList.forEach(ArrayList.java:1257)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)at java.util.ArrayList.forEach(ArrayList.java:1257)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='et.ext', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Cannot convert class com.example.mybatis_demo.dao.UserMapperTest$2 to SQL type requested due to com.mysql.cj.exceptions.WrongArgumentException - Invalid argument value: java.io.NotSerializableExceptionat com.baomidou.mybatisplus.core.MybatisParameterHandler.setParameters(MybatisParameterHandler.java:228)at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:94)at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:99)at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doUpdate(MybatisSimpleExecutor.java:55)at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.update(MybatisCachingExecutor.java:85)at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)... 76 more