为何使用json格式存储
1 存储内容经常改变，需要新增或者删减字段，但是字段的删除可能会出错，字段的新增个数不确定(field命名没规律)
2 不想多存储字段的 父类字段 parent_id ，因为sql语法会很复杂
3 不想用其他数据库，比如mogoDB ，多引入框架，会增加复杂度
4 mysql 支持json，但是语法复杂； 借助 mybatis 即可实现 jsonString <==> java jsonObject 的双向操作
本文基于原生的 mybatis ,而不是 mybatis-plus ，请知悉。
目标1-查询：查询数据库的json字段，转换为java的json对象，并优雅的返回前端
目标2-更新：识别前端的请求参数，转换为 数据库的 Json 字段 ，比如新增/更新
目标3-注解：不使用 xml增加 typeHandler，而是使用注解方式
目标4-智能：不在sql中的字段上指定 typeHandler,不要每次都手写，要 自动化识别
-- 建表 json 字段，添加1条json 数据
create table t_test_json(id int primary key auto_increment,json_field JSON default null);
insert into t_test_json( json_field) values ('{"hello":"world"}');
JsonEntity
@Table(name="t_test_json")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class JsonEntity{
@Id
private Integer id;
// 为何不是 ArrayNode 或者 ObjectNode ?
// 因为 JsonNode 是他们俩的父类，可以自动兼容2种格式的json : [{},{}] 和 {}
private JsonNode jsonField;
@SneakyThrows
@Override
public String toString() {
return JacksonUtils.writeValueAsString(this);
}
}
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.SneakyThrows;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* includeNullJdbcType 在 Mybatis 3.4.0 开始 默认为true。
* 想让mybatis 自动化处理映射关系，则必须保证 includeNullJdbcType =true,
* 因为如果只是设置了 @MappedJdbcTypes(value = JdbcType.VARCHAR ) 则该处理器就无法自动处理 JdbcType是json 的情况。
* 实际上，根据官方文档，mybatis 是把所有的返回值都当作 JdbcType = null 来自动 选择类型处理器的
* 如果includeNullJdbcType =false，则必须在 sql中返回的字段上 明确标注 typeHandler= xxx.class
*
* @MappedJdbcTypes的value 设置为 JdbcType.LONGVARCHAR 或者 JdbcType.LONGVARCHAR 都可以。
* 建议JdbcType.LONGVARCHAR，据测试，json 类型的返回结果的JdbcType = LONGVARCHAR
*
* @ColumnType when the reult is ResultMap
*/
// @MappedTypes(JsonNode.class) // 因为BaseTypeHandler<JsonNode> 泛型中指定了JsonNode 的话，这个注解也可以省略
@MappedJdbcTypes(value = JdbcType.VARCHAR, includeNullJdbcType = true)
@Component
public class JsonNodeTypeHandler extends BaseTypeHandler<JsonNode> implements InitializingBean {
static JsonNodeTypeHandler j;
@Autowired
ObjectMapper objectMapper;
/**
* 魔法 注入 单例bean objectMapper;
* 在 @Controller 中注入ObjectMapper 不需要这么麻烦，直接 @Autowired 即可 。
* 非Controller 注入原理：spring 启动过程中 实例化JsonNodeTypeHandler 的 bean 时，会自动把 objectMapper 携带过来；
* spring 启动完成后的bean 又会被擦除 。所以，这个要及时赋值一下引用 objectMapper
*/
@Override
public void afterPropertiesSet() {
j = this; // 初始化静态实例
j.objectMapper = this.objectMapper; //及时拷贝引用
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, JsonNode jsonNode, JdbcType jdbcType) throws SQLException {
ps.setString(i, jsonNode != null ? jsonNode.toString() : null);
}
@SneakyThrows
@Override
public JsonNode getNullableResult(ResultSet rs, String colName) {
return read(rs.getString(colName));
}
@SneakyThrows
@Override
public JsonNode getNullableResult(ResultSet rs, int colIndex) {
return read(rs.getString(colIndex));
}
@SneakyThrows
@Override
public JsonNode getNullableResult(CallableStatement cs, int i) {
return read(cs.getString(i));
}
@SneakyThrows
private JsonNode read(String json) {
return json != null ? j.objectMapper.readTree(json) : null;
}
}
public static SqlSessionFactory getSqlSessionFactory(DataSource dataSource, String javaEntityPath, String xmlMapperLocation) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setTypeAliasesPackage(javaEntityPath);
//mybatis configuration
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
// 下划线转驼峰
configuration.setMapUnderscoreToCamelCase(true);
// 返回Map类型时，数据库为空的字段也要返回 https://www.cnblogs.com/guo-xu/p/12548949.html
configuration.setCallSettersOnNulls(true);
// 配置 拦截器 打印 sql : TODO 补充 拦截器实现代码
// configuration.addInterceptor(new PrintMybatisSqlInterceptor());
factoryBean.setConfiguration(configuration);
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
factoryBean.setMapperLocations(resolver.getResources(xmlMapperLocation));
// 自定义的类型处理器： 自动双向解析 JsonNode 类型 和 mysql中的 json； 千万别写xml 了，太low
factoryBean.setTypeHandlers(new JsonNodeTypeHandler());
return factoryBean.getObject();
}
/**
* 每个类型的数据库连接，都需要单独创建1个SqlSessionFactory
* 比如mysql DataSource 需要 配置1个 @Bean SqlSessionFactory
* oracle 或者 hive 或者其他的数据库，都需要单独配置自己的 @Bean SqlSessionFactory
* 但是 ，getSqlSessionFactory() 这个静态方法是共用的，只需要修改对应entity和xml文件地址的参数 javaEntityPath 和 xmlMapperLocation 即可
*/
@Bean
public SqlSessionFactory yourSqlSessionFactory(DataSource yourDataSource) throws Exception {
return getSqlSessionFactory(yourDataSource,"com.server.model.entity.testpath","classpath:mapper/testpath/*.xml");
}
@Select(" SELECT * from t_test_json where JSON_CONTAINS(json_field, #{vo.jsonField}) limit 1 ")
List<JsonEntity> testQueryJson(@Param("vo") JsonEntity vo);
查询到结果ResultSet ，去交给DefaultResultSetHandler类的createAutomaticMappings方法去处理映射关系
/**
* 查询到结果ResultSet ，去交给DefaultResultSetHandler类的createAutomaticMappings方法去处理映射关系
* @Param metaObject MetaObject#findProperty() 能把 rs 结果中columnName 的下划线去除，对应到java对象 JsonEntity 的属性名
* @Param rsw final TypeHandler<?> typeHandler = rsw.getTypeHandler(propertyType, columnName);
*/
private List<UnMappedColumnAutoMapping> createAutomaticMappings(ResultSetWrapper rsw, ResultMap resultMap, MetaObject metaObject, String columnPrefix)
这个 createAutomaticMappings 方法，内部主要干了几件事
找出resultset结果集中那些无法用 mybatis内置的类型处理器映射的字段名，比如json类型的 json_field
将json_field改为驼峰格式jsonField，反射查找到Java对象中该属性为 private JsonNode jsonField;
public String findProperty(String name, boolean useCamelCaseMapping) {
if (useCamelCaseMapping) {
name = name.replace("_", "");
}
return findProperty(name);
}
再根据 JsonNode 去查找已注册的类型处理器，就定位到 我们手动 自定义的类型处理器 JsonNodeTypeHandler 了
json 字段对应的jdbcType 其实是 jdbcType.LONGVARCHAR ,JdbcType.VARCHAR, includeNullJdbcType = true)，includeNullJdbcType = true，防止 JdbcType 手动设置错误导致 定位失败接下来，就是 执行 自定义类型处理器的 方法 typeHandler.getResult(ResultSet rs, String colName) 获取到值了
使用 通用mapper，可以少写很多单表操作的sql ，增删改查，单表操作非常方便
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>${mapper.version}</version><!-- 4.0.4 -->
</dependency>
@ColumnType 明确标注出来 json 字段(比如T)，必须是 确定的已知的java类型 (比如 JsonNode)import tk.mybatis.mapper.annotation.ColumnType;
@Table(name="t_test_json")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class JsonEntity{
@Id
private Integer id;
// 多了这个ColumnType，通用mapper生成sql必须的；如果没有该注解，则最终生成sql时 该JsonNode类型的字段将会被忽略
// 如果你没有使用 通用mapper，而是完全手写sql，那么完全没必要加该注解，mybatis的自动发现足咦！！
@ColumnType(typeHandler = JsonNodeTypeHandler.class)
private JsonNode jsonField;
@SneakyThrows
@Override
public String toString() {
return JacksonUtils.writeValueAsString(this);
}
}
@AutoWired
IDao dao;
@ApiOperation(value = "查询 自动转换 JsonNode 和 json 类型，自动发现 typeHandler ")
@PostMapping("test/json")
public ResultBean testJson(@RequestBody(required = false) JsonEntity vo) {
return ResultUtils.oK(dao.select(vo));
}
请求 1: 查询 全量 json 结果
curl -X POST "http://localhost:8080/api/test/json" -H "accept: */*" -H "Content-Type: application/json" -d "{}"
请求 2: 根据 id 查询 json 结果，-d “{}” 里加参数即可
curl -X POST "http://localhost:8080/api/test/json" -H "accept: */*" -H "Content-Type: application/json" -d "{ id:1}"
{id:1}
以上 2种情况，dao 层都使用 通用mapper 生成sql即可。
都能正常被 @RequestBody(required = false) JsonEntity vo 识别 并且 生成JsonEntity 对象，return 正常的JsonEntity 结果
请求 3: 根据 JsonNode 字段 查询 JsonEntity 结果
curl -X POST "http://localhost:8034/api/test/json" -H "accept: */*" -H "Content-Type: application/json" -d "{ jsonField:{\"hello\":\"world\"}}"
where json_field = {"hello":"world"} ，查询结果为空 ❌。JSON_CONTAINS 去匹配json ，ok ✔ /**
* 直接 查询 json 字段
* 无法使用 通用mapper 生成的sql, 必须 手写 sql判断 json 是否存在的 JSON_CONTAINS 语法：
* where JSON_CONTAINS(json, #{vo.jsonField})
* 最终生成的sql 是
* select * from t_test_json where JSON_CONTAINS(json_field,'{"hello": "world"}')
* 如此，才能让 mysql 正常查询json 字段和返回结果
* 参考 mysql 正确比较 2个json 字段 的写法 https://blog.csdn.net/weixin_39926042/article/details/118812599
*/
@Select(" select * from t_test_json where JSON_CONTAINS(json_field, #{vo.jsonField}) ")
List<JsonEntity> selectByJson(@Param("vo") JsonEntity vo);
swagger 或者 postaman 请求 json 格式参数
curl -X POST "http://localhost:8034/api/test/json/update" -H "accept: */*" -H "Content-Type: application/json" -d "{\"id\": 1, \"jsonField\": {\"hello\":\"world again!\"}}"
{
"id": 1,
"jsonField": {"hello":"world again!"}
}
controlller 层
@ApiOperation(value = "更新 json 字段，自动生成sql或者手写sql 均可，都无需指定 typehandler 属性 ")
@PostMapping("/json/update")
public ResultBean testUpdateJson(@RequestBody JsonEntity vo) {
final IJsonDao dao = SpringContextUtils.getBean(IJsonDao.class);
// 通用mapper生成 sql UPDATE T_TEST_JSON SET id = id,json_field = {"hello":"world again!"} WHERE id = 1 ;
final int i = dao.updateByPrimaryKeySelective(vo);
// 手动写 sql UPDATE t_test_json SET json_field = {"hello":"world again!"} where id = 1 ;
return DFResultUtils.oK(dao.testUpdateJson((vo)));
}
通用mapper Dao 的写法
@Repository("jsonDao")
public interface IJsonDao extends MyMapper<JsonEntity> {
@Transactional(value = JK.transactionManager, rollbackFor = Exception.class)
@Select(" UPDATE t_test_json SET json_field = #{vo.jsonField} where id = #{vo.id}")
List<Integer> testUpdateJson(@Param("vo") JsonEntity vo);
}
/** MyMapper 通用mapper类 写法
public interface MyMapper<T>
extends
BaseMapper<T>,
ExampleMapper<T>,
ConditionMapper<T>,
MySqlMapper<T> {
}*/
更新后结果
