Mybatis 原生JSON类型字段处理

johnrui

已于 2024-10-28 17:48:18 修改

阅读量1.2k

点赞数 2

分类专栏： JAVA-ORM 文章标签： mybatis

于 2024-09-18 13:59:51 首次发布

本文链接：https://blog.youkuaiyun.com/johnrui/article/details/142331418

版权

JAVA-ORM 专栏收录该内容

4 篇文章

订阅专栏

1. mysql 字段类型为json数据处理

1.1 配置文件配置json数据类型处理类，并自定义json处理类

package com.dahua.evo.attendance.handler;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
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 fuwu314
 * @describe：mysqlJson类型处理类
 * @date 2024/9/18
 */
public class MySqlJsonHandler <T extends Object> extends BaseTypeHandler<T> {

    private static final ObjectMapper mapper = new ObjectMapper();
    private Class<T> clazz;
    public MySqlJsonHandler(Class<T> clazz) {
        if (clazz == null) throw new IllegalArgumentException("Type argument cannot be null");
        this.clazz = clazz;
    }

    static {
//        mapper.configure(JsonParser.Feature.WRITE_NULL_MAP_VALUES, false);
        mapper.configure(SerializationFeature.WRITE_NULL_MAP_VALUES, false);
        mapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
    }
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }
    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.toObject(rs.getString(columnName), clazz);
    }
    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.toObject(rs.getString(columnIndex), clazz);
    }
    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.toObject(cs.getString(columnIndex), clazz);
    }

    /**
     * 将字符串内容转换为指定类型的对象
     *
     * @param content 字符串内容
     * @param clazz   目标类型
     * @return 目标类型的对象
     */
    private T toObject(String content, Class<?> clazz) {
        if (content != null && !content.isEmpty()) {
            try {
                return (T) mapper.readValue(content, clazz);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        } else {
            return null;
        }
    }

    /**
     * 将对象转换为JSON字符串
     *
     * @param object 要转换的对象
     * @return JSON字符串
     * @throws RuntimeException 如果转换过程中发生异常
     */
    private String toJson(T object) {
        try {
            return mapper.writeValueAsString(object);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}

配置文件：
mybatis.type-handlers-package=com.dahua.evo.attendance.handler

1.2实体类正常注解，mapper.xml 文件注明字段处理类

实体类：
@Data
@TableName(value = "inspect_classes")
public class InspectClasses implements Serializable {

	private static final long serialVersionUID = 6888405245126579026L;

	/**
	 * 主键 -2休班
	 */
	@TableId(value="ID", type= IdType.AUTO)
	private Long id;

	/**
	 * 班次名称
	 */
	@TableField("CLASSES_NAME")
	private String classesName;

	/**
	 * 绑定颜色
	 */
	@TableField("COLOUR")
	private String colour;

	/**
	 * 班次类型, 1-固定班 2-弹性班 3-签到班 4-休班
	 */
	@TableField("TYPE")
	private Integer type;

	/**
	 * 备注
	 */
	@TableField("MEMO")
	private String memo;

	/**
	 * 系统默认 1-是 0-否 系统默认不允许删除 默认为否
	 */
	@TableField("SYS_DEFAULT")
	private Integer sysDefault;

	/**
	 * 是否删除, 逻辑删除 1-是 0-否
	 */
	@TableField("IS_DELETE")
	private Integer isDelete;

	/**
	 * 创建时间
	 */
	@TableField("CREATE_TIME")
	private Date createTime;

	/**
	 * 更新时间
	 */
	@TableField("UPDATE_TIME")
	private Date updateTime;

	/**
	 * 记录所属用户组织编码
	 */
	@TableField("ORG_CODE")
	private String orgCode;
	/**
	 * 餐补规则（json类型）
	 */
	@TableField(value="MEAL_ALLOWANCE")
	private List<ClasseMealAllowance> classeMealAllowanceList;



	public InspectClasses() {

	}

	public InspectClasses(Long id, String classesName) {
		this.id = id;
		this.classesName = classesName;
	}
}

xml文件：
<?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.dahua.evo.attendance.business.mapper.classes.ClassesMapper">

	<!-- 通用查询映射结果 -->
	<resultMap id="BaseResultMap" type="com.dahua.evo.attendance.business.domain.classes.InspectClasses">
		<id column="ID" property="id" />
		<result column="CLASSES_NAME" property="classesName" />
		<result column="COLOUR" property="colour" />
		<result column="TYPE" property="type" />
		<result column="MEMO" property="memo" />
		<result column="SYS_DEFAULT" property="sysDefault" />
		<result column="IS_DELETE" property="isDelete" />
		<result column="CREATE_TIME" property="createTime" />
		<result column="UPDATE_TIME" property="updateTime" />
		<result column="ORG_CODE" property="orgCode" />
		<result column="MEAL_ALLOWANCE" property="classeMealAllowanceList" typeHandler="com.dahua.evo.attendance.handler.MySqlJsonHandler" />
	</resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        ID AS id, CLASSES_NAME AS classesName, COLOUR AS colour, TYPE AS type, MEMO AS memo, SYS_DEFAULT AS sysDefault,
        IS_DELETE AS isDelete, CREATE_TIME AS createTime, UPDATE_TIME AS updateTime, ORG_CODE AS orgCode,
		MEAL_ALLOWANCE AS mealAllowanceList
    </sql>



	<select id="selectListByCondition" resultType="com.dahua.evo.attendance.business.vo.classes.ClassesVo">
		SELECT
		<include refid="Base_Column_List" />
		FROM inspect_classes
		<where>
			AND IS_DELETE = 0
			<if test="searchKey != null and searchKey != ''">
				AND instr(CLASSES_NAME,#{searchKey}) > 0
			</if>
			<if test="queryDefault != null">
				AND SYS_DEFAULT = #{queryDefault}
			</if>
			<if test="ownerCode != null and ownerCode != ''">
				AND ORG_CODE LIKE CONCAT(#{ownerCode}, '%')
			</if>
		</where>
		ORDER BY UPDATE_TIME DESC
	</select>
<!--	查询sql -->
	<select id="getClassById" resultMap="BaseResultMap">
		SELECT * FROM inspect_classes where ID =#{classesId}
	</select>
	<!--新增sql -->
	<insert id="insertClass"
			useGeneratedKeys="true" keyProperty="id"
			parameterType="com.dahua.evo.attendance.business.domain.classes.InspectClasses">
		insert into inspect_classes (CLASSES_NAME, COLOUR,TYPE,MEMO,CREATE_TIME,UPDATE_TIME,ORG_CODE,MEAL_ALLOWANCE)
		values (#{classesName,jdbcType=VARCHAR}, #{colour,jdbcType=VARCHAR},#{type,jdbcType=INTEGER},#{memo,jdbcType=VARCHAR},
		#{createTime,jdbcType=VARCHAR},#{updateTime,jdbcType=TIMESTAMP},#{orgCode,jdbcType=VARCHAR},
		#{classeMealAllowanceList,jdbcType=OTHER,typeHandler=com.dahua.evo.attendance.handler.MySqlJsonHandler})
	</insert>
	<!-- 更新sql -->
	<update id="updateClassById" parameterType="com.dahua.evo.attendance.business.domain.classes.InspectClasses">
		UPDATE `evo_attendance`.`inspect_classes`
		SET `CLASSES_NAME` = #{classesName,jdbcType=VARCHAR}, `COLOUR` = #{colour,jdbcType=VARCHAR}, `TYPE` = #{type,jdbcType=INTEGER},
		 `MEMO` = #{memo,jdbcType=VARCHAR}, `SYS_DEFAULT` = #{sysDefault,jdbcType=VARCHAR},
		 `UPDATE_TIME` = #{updateTime,jdbcType=VARCHAR}, `ORG_CODE` = #{orgCode,jdbcType=VARCHAR},
		 `MEAL_ALLOWANCE` = #{classeMealAllowanceList,jdbcType=OTHER,typeHandler=com.dahua.evo.attendance.handler.MySqlJsonHandler}
		WHERE `ID` = #{id,jdbcType=INTEGER};
	</update>
    <insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `evo_custom`.`smart_brain_project`(`TYPE`, `TYPE_NAME`, `DETAIL_INFO`, `CREATE_DATETIME`,
        `CREATE_USER_ID`, `UPDATE_TIME`, `UPDATE_USER_ID`)
        VALUES
        <foreach item="item" collection="projects" separator="," open="" close="" index="">
            ( #{item.type,jdbcType=VARCHAR }, #{item.typeName,jdbcType=VARCHAR },
            #{item.detailInfo, jdbcType=OTHER,typeHandler=com.dahua.evo.custom.handler.MySqlJsonHandler}, now (), now
            (), #{item.createUserId }, #{item.updateUserId })
        </foreach>
    </insert>
</mapper>

2. mysql json数据查询：

参考链接：https://blog.youkuaiyun.com/hollycloud/article/details/142375951

1   test01   {"obj": {"title": "工程师"}, "name": "研发部", "colors": ["red", "blue"]}
2   test02   {"jobs": [{"name": "经理"}, {"name": "工程师"}], "name": "研发部"}
3   test03   {"name": "行政部"}
4   test04   [{"name": "研发部"}, {"name": "工程部"}]

2.1 查询extend里面name等于研发部的数据
查询语句跟普通的sql语句差不多，也就是字段名要用到path表达式

查询sql: select * from test_my_json where extend ->'$.name' = '研发部'

模糊查：select * from test_my_json where extend ->'$.name' like '%研发部%'

sql结果：

2.2 查询extend里面obj.title等于工程师的数据
select * from test_my_json where extend ->'$.obj.title' like '%工程师%'

2.3查询colors包含red的数据
需要用到json_contains函数,第一个参数是表的字段名，第二个参数是要查询的值，如果是字符串需要用双引号,第三个参数是path路径
select * from test_my_json where JSON_CONTAINS(extend,'"red"', '$.colors')

2.4查询jobs里面的name包含经理的数据
select * from test_my_json where extend->'$.jobs[*].name' like '%经理%'

2.4.1 精确匹配
select * from test_my_json where JSON_CONTAINS(extend->'$.jobs[*].name','"经理"')

Mybatis中javaType和jdbcType对应关系
JDBC Type	Java Type
BIT	boolean / Boolean
TINYINT	byte / Byte
SMALLINT	short / Short
INTEGER	int / Integer
BIGINT	long / Long
REAL	float / Float
FLOAT	double / Double
DOUBLE	double / Double
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
CHAR	String
VARCHAR / VARCHAR 2	String
LONGVARCHAR	String
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp (在较新版本中推荐使用java.time.LocalDateTime)
BINARY	byte[]
VARBINARY	byte[]
LONGVARBINARY	byte[]
CLOB	java.sql.Clob
NCLOB	java.sql.NClob
BLOB	java.sql.Blob
BOOLEAN	boolean / Boolean
OTHER	依赖于数据库特定类型（如：java.sql.Struct, java.sql.Array等）
NULL	不适用于基本映射，但在某些特殊情况下可能有用
VARCHAR_IGNORECASE	同 VARCHAR，但某些数据库驱动可能支持大小写不敏感比较
UNICODE_STREAM	字符流，用于处理Unicode字符数据
BINARY_STREAM	二进制流，用于处理二进制数据