MyBatis 处理 MySQL5.7 的json字段数据_qq_40250122的博客-CSDN博客_mybatis json_contains
网上查询后，了解到 MySQL 5.7 以后版本居然加入了 json 字段，这下有搞头了！
赶紧查了一下我的 MySQL 版本：是大于 V5.7 的，在支持范围内 
等等，我怎么在Navicat 上怎么找不到 json 字段类型 ，又是一番查找，原来 Navicat 版本太低不支持。。。所以我又升级到 Navicat Premium 12 版本了，这下 json 字段出来了，那这个 json 字段类型对应的 Java bean 属性又是什么呢？MyBatis 怎么写 sql 呢？ 
额，MyBatis 还不支持直接处理MySQL json字段。。。只能通过自定义 TypeHandler 来转化，行吧，那就写呗。不过之前要看看List 、Object 这两个是有区别的，所以我分别写了 ObjectJsonTypeHandler.java、ObjectListJsonTypeHandler.java
（ 选择 com.alibaba.fastjson 工具类下的）
然后就是两个自定义 TypeHandler 了：首先是ObjectJsonTypeHandler.java
/*
* Copyright 2019 Wicrenet, Inc. All rights reserved.
*/
package com.xy.pay.main.dao.handler;
/*
* Copyright 2019 Wicrenet, Inc. All rights reserved.
*/
import com.alibaba.fastjson.JSON;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author YJX
* @date 2019-08-23 11:30
* 普通对象存取数据库数据库字段为json类型
**/
public class ObjectJsonTypeHandler<T extends Object> extends BaseTypeHandler<T> {
private Class<T> clazz;
public ObjectJsonTypeHandler(Class<T> clazz) {
if (clazz == null) {
throw new IllegalArgumentException("Type argument cannot be null");
}
this.clazz = clazz;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, JSON.toJSONString(parameter));
}
@Override
public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
return this.toObject(rs.getObject(columnName), clazz);
}
@Override
public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return this.toObject(rs.getObject(columnIndex), clazz);
}
@Override
public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return this.toObject(cs.getObject(columnIndex), clazz);
}
private T toObject(Object content, Class<T> clazz) {
if (content == null) {
return null;
}
return JSON.parseObject(content.toString(), clazz);
}
}
ObjectListJsonTypeHandler.java 如下
/*
* Copyright 2019 Wicrenet, Inc. All rights reserved.
*/
package com.xy.pay.main.dao.handler;
/*
* Copyright 2019 Wicrenet, Inc. All rights reserved.
*/
import com.alibaba.fastjson.JSON;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* @author YJX
* @date 2019-08-23 11:30
* 数组对象存取数据库数据库字段为json类型
**/
public class ObjectListJsonTypeHandler<T> extends BaseTypeHandler<List<T>> {
private Class<T> clazz;
public ObjectListJsonTypeHandler(Class<T> clazz) {
if (clazz == null) {
throw new IllegalArgumentException("Type argument cannot be null");
}
this.clazz = clazz;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<T> parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, JSON.toJSONString(parameter));
}
@Override
public List<T> getNullableResult(ResultSet rs, String columnName) throws SQLException {
return this.toObject(rs.getObject(columnName), clazz);
}
@Override
public List<T> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return this.toObject(rs.getObject(columnIndex), clazz);
}
@Override
public List<T> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return this.toObject(cs.getObject(columnIndex), clazz);
}
private List<T> toObject(Object content, Class<T> clazz) {
if (content == null) {
return null;
}
return JSON.parseArray(content.toString(), clazz);
}
}
mybatis中使用 ObjectJsonTypeHandler.java和ObjectListJsonTypeHandler.java使用是不一样的呢
object和普通枚举一样使用
存list取的时候需要指定转换的typeJava对象 , 如下图 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xy.pay.main.dao.SysUserDao">
<!-- resultMap值
枚举转换器 typeHandler单一的时候不用写,如果是枚举集合则需要声明
这种要写 typeHandler<result column="providers" property="providers" typeHandler="com.xy.pay.dao.core.handler.JointProviderListEnumHandler"/>
这种可以不写typeHandler <result column="trade_tag" property="tradeTags" typeHandler="com.xy.pay.dao.core.handler.TradeTagsEnumHandler"/>
-->
<resultMap id="mapper" type="com.xy.pay.main.dao.model.SysUser">
<id column="id" property="id"/>
<result column="hierarchy" property="hierarchy"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<result column="enabled" property="enabled"/>
<result column="phone" property="phone"/>
<!-- 是返回list集合时需要 配置javaType和typeHandler -->
<result column="uservos" property="uservos" javaType="com.xy.pay.main.controller.model.SysUserVo"
typeHandler="com.xy.pay.main.dao.handler.ObjectListJsonTypeHandler"/>
<association property="parent" columnPrefix="parent_" resultMap="mapper"/>
</resultMap>
<!--【基础条件】-->
<sql id="baseWhere">
<trim prefixOverrides="and">
<if test="id_eq != null">
and `id` = #{id_eq}
</if>
<if test="id_in != null">
and id in
<foreach collection="id_in" open="(" item="id_in_item" separator="," close=")">
#{id_in_item}
</foreach>
</if>
<if test="parentId_eq != null">
and `parent_id` = #{parentId_eq}
</if>
<if test="hierarchy_eq != null">
and `hierarchy` = #{hierarchy_eq}
</if>
<if test="hierarchy_lk != null">
and `hierarchy` LIKE #{hierarchy_lk}
</if>
<if test="name_eq != null">
and `name` = #{name_eq}
</if>
<if test="password_eq != null">
and `password` = #{password_eq}
</if>
<if test="enabled_eq != null">
and `enabled` = #{enabled_eq}
</if>
<if test="phone_eq != null">
and `phone` = #{phone_eq}
</if>
<if test="userves_name_eq != null">
<!-- 查询json类型字段里面的数据-数组写法 -->
and JSON_CONTAINS( uservos -> '$[*].name',CONCAT('"',#{userves_name_eq},'"'), '$' )
<!-- 查询json类型字段里面的数据-普通Object写法 -->
<!-- and uservos -> '$.name' LIKE COUNT('%',#{userves_name_eq},'%')-->
</if>
</trim>
</sql>
<!--【默认条件】-->
<sql id="where">
<if test="default_where != null and default_where">
<where>
<include refid="baseWhere"/>
</where>
</if>
<if test="default_where == null or !default_where">
WHERE
<include refid="baseWhere"/>
</if>
</sql>
<sql id="columns">
<!--【动态字段】-->
<if test="use_dynamic_column != null and use_dynamic_column">
<trim suffixOverrides=",">
<if test="field_id != null">
id,
</if>
<if test="field_parent != null">
parent_id,
</if>
<if test="field_hierarchy != null">
hierarchy,
</if>
<if test="field_name != null">
name,
</if>
<if test="field_password != null">
password,
</if>
<if test="field_enabled != null">
enabled,
</if>
<if test="field_phone != null">
phone,
</if>
<if test="field_uservos != null">
uservos,
</if>
</trim>
</if>
<!--【固定的字段】 id, xxid,
【可选字段】 descr
-->
<!-- 这里要自行修改 -->
<if test="use_dynamic_column == null or !use_dynamic_column">
id
,parent_id
,hierarchy
,name
,password
,enabled
,phone
,uservos
</if>
</sql>
<!--排序-->
<sql id="orderBy">
<if test="order_by_map != null and !order_by_map.isEmpty()">
ORDER BY
<foreach collection="order_by_map" index="key" item="val" separator=",">
<if test="key == 'id'">
id ${val}
</if>
</foreach>
</if>
</sql>
<!--【新增】，必填与选填项必须分开-->
<insert id="insert" parameterType="com.xy.pay.main.dao.model.SysUser" useGeneratedKeys="true" keyProperty="id">
INSERT INTO sys_user (
name
<if test="password != null">
,password
</if>
<if test="enabled != null">
,enabled
</if>
<if test="phone != null">
,phone
</if>
<if test="uservos != null">
,uservos
</if>
<if test="parent != null and parent.id != null">
,parent_id
</if>
)
VALUES (
#{name}
<if test="password != null">
,#{password}
</if>
<if test="enabled != null">
,#{enabled}
</if>
<if test="phone != null">
,#{phone}
</if>
<if test="uservos != null">
<!-- 插入是需要配置typeHandler -->
,#{uservos,typeHandler=com.xy.pay.main.dao.handler.ObjectListJsonTypeHandler}
</if>
<if test="parent != null and parent.id != null">
,#{parent.id}
</if>
)
</insert>
<!--
【固定字段】：provider ,brand_id , create_time , app_type
【敏感字段】：
这里可以使用枚举转换器
<if test="tradeTags != null">
trade_tag = #{tradeTags,typeHandler=com.xy.pay.dao.core.handler.TradeTagsEnumHandler},
</if>
-->
<sql id="updateSet">
<trim suffixOverrides=",">
<if test="password != null">
password = #{password},
</if>
<if test="enabled != null">
enabled = #{enabled},
</if>
<if test="phone != null">
phone = #{phone},
</if>
<if test="uservos != null">
<!-- 修改时是需要配置typeHandler -->
uservos = #{uservos,typeHandler=com.xy.pay.main.dao.handler.ObjectListJsonTypeHandler},
</if>
</trim>
</sql>
<update id="update" parameterType="com.xy.pay.main.dao.model.SysUser">
UPDATE sys_user SET
<include refid="updateSet"/>
WHERE id = #{id}
</update>
<!--更新，不能选择【默认条件】-->
<update id="updateByCriteria" parameterType="map">
UPDATE sys_user SET
<include refid="updateSet"/>
WHERE
<include refid="baseWhere"/>
</update>
<!--【分页查询】，必须选带【默认条件】-->
<select id="select" parameterType="map" resultMap="mapper">
SELECT
<include refid="columns"/>
FROM sys_user
<include refid="where"/>
<include refid="orderBy"/>
</select>
<!--记录个数查询，必须选带【默认条件】-->
<select id="selectCount" parameterType="map" resultType="long">
SELECT count(*) from sys_user
<where>
<include refid="baseWhere"/>
</where>
</select>
<!--【删除】-->
<update id="delete" parameterType="map">
DELETE FROM sys_user
WHERE
<include refid="baseWhere"/>
</update>
</mapper>
Original url: Access
Created at: 2020-08-12 15:28:37
Category: default
Tags: none
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论