自定义typeHandler处理数据库json类型字段

MySQL5.7以后新增了对JSON数据类型的支持，但是自己一直没有使用过，今天就来简单看下使用mybatis框架如何处理JSON类型的数据。其实我开始知道是postgresql支持JSON，但是一直没有用过。我觉得真到有需要存储JSON的情况时，自己将相应model转成JSON串再保存不是一样的吗？用的时候从数据库取出后，自己在代码里面做个反序列化的操作就行了，并不觉得有什么差别，不知道大家是不是也是我这种想法。下面我们看看MySQL的JSON类型到底有什么好处，我还是建议先看看MySQL的官方文档中关于JSON的介绍，内容也不是很多，或者也可以自己看一些相关博客。
在开始写代码之前我们需要先创建两个表，这两个表基本相同，区别在于一个是用了JSON，另一个使用varchar而已，我们对比一下使用JSON类型和使用JSON串在使用上的区别，建表脚本如下：

// 使用JSON
create table if not exists spring.t_user
(
    id int auto_increment primary key,
    username varchar(20) null,
    mobile varchar(20) null,
    address json null,
    sex smallint(6) default 1 null,
    age int default 0 null,
    password varchar(200) null,
    family json null
);

// 使用varchar
create table if not exists spring.t_account
(
    id int auto_increment primary key,
    username varchar(20) null,
    mobile varchar(20) null,
    address varchar(2000) null,
    sex smallint(6) default 1 null,
    age int default 0 null,
    password varchar(200) null,
    family varchar(2000) null
);

一、创建项目

接下来是创建一个spring boot项目，因为第一次使用JSON数据类型，因此我决定使用mybatis－generator插件自动生成相应的model和mapper文件，关于mybatis－generator的使用这里不再说了，以前有讲到过，而且网上例子也多。根据生成的mapper.xml可以看到数据库的JSON类型对应的jdbcType是LONGVARCHAR，接下来需要处理一个问题，就是我们怎么映射成Java的model类型。因为根据mybatis－generator插件生成的User类的address和family两个属性（数据库是JSON类型）都是String，这个和我期望的不一致，因为我想是具体的model类型，即Address和Family两个类型（这两个model类型需要自己创建），所以我需要修改自动生成的User类，如下：

public class User {

    private Integer id;

    private String username;

    private String mobile;

    private Short sex;

    private Integer age;

    private String password;

    private Address address;

    private Family family;
    //省略get、set
    ......
}

如果这样直接存储会有一个问题，就是MySQL和jdbcType都没有Address和Family这两种类型，所以如果存User的时候我们必须将具体的数据类型转换一下，这就需要我们使用mybatis的类型处理器，而这个类型处理器需要我们自己定义，因为我们有Address和Family两个model需要处理，因此需要自定义一个泛型的类型处理器，自定义类型处理器继承自mybatis的BaseTypeHandler，代码如下：

public class JSONHandler<T extends Object> extends BaseTypeHandler<T> {

    private static final Logger LOGGER = LoggerFactory.getLogger(JSONHandler.class);

    private static final ObjectMapper mapper = new ObjectMapper();

    private Class<T> clazz;

    static {
        mapper.configure(JsonParser.Feature.ALLOW_MISSING_VALUES, false);
        mapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
    }

    public JSONHandler(Class<T> clazz) {
        if (clazz == null) {
            throw new NullPointerException("Type argument cannot be null");
        }
        this.clazz = clazz;
    }

    /**
     * object转json string
     * @param object
     * @return
     */
    private String toJSON(T object) {
        try {
            String string = mapper.writeValueAsString(object);
            LOGGER.info(">>>> json handler string:{} <<<<",string);
            return string;
        } catch (Exception e) {
            LOGGER.error(">>>> covert object to json string failed, error message: <<<<",e.getMessage());
        }
        return null;
    }

    /**
     * json转object
     * @param json
     * @param clazz
     * @return
     * @throws IOException
     */
    private T toObject(String json, Class<T> clazz) throws IOException {
        if (json != null && json != "") {
            return mapper.readValue(json,clazz);
        }
        return null;
    }

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, T t, JdbcType jdbcType) throws SQLException {
        try {
            preparedStatement.setString(i,toJSON(t));
        } catch (Exception e) {
            LOGGER.error(">>>> preparedStatement set string failed, error message:{} <<<<",e.getMessage());
        }
    }

    @Override
    public T getNullableResult(ResultSet resultSet, String s) throws SQLException {
        try {
            return toObject(resultSet.getString(s), clazz);
        } catch (IOException e) {
            LOGGER.error(">>>> convert json string to object failed, error message:{} <<<<",e.getMessage());
        }
        return null;
    }

    @Override
    public T getNullableResult(ResultSet resultSet, int i) throws SQLException {
        try {
            return toObject(resultSet.getString(i), clazz);
        } catch (IOException e) {
            LOGGER.error(">>>> convert json string to object failed, error message:{} <<<<",e.getMessage());
        }
        return null;
    }

    @Override
    public T getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        try {
            return toObject(callableStatement.getString(i), clazz);
        } catch (IOException e) {
            LOGGER.error(">>>> convert json string to object failed, error message:{} <<<<",e.getMessage());
        }
        return null;
    }
}

上面的代码整体还是比较简单的，继承BaseTypeHandler后，重写其相应的方法即可。这里其实可以加注解@MappedJdbcTypes和@MappedTypes这个两个注解，即表明这个handler处理的jdbcType的类型和JavaType类型（Java的model类）。另外定义了一个Class成员变量，并且作为构造参数，这样创建JSONHandler实例时就能够根据具体的Class类可以得到具体的类型T。
接着需要修改我们的UserMapper.xml，因为xml文件也是自动生成的，其实生成的CRUD很多是我们用不到的，我觉得最好把这些删除掉，尤其是扩展类，即类似于UserExample这种，修改后的UserMapper.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.ypc.mysql.json.mapper.UserMapper">
  <resultMap id="BaseResultMap" type="com.ypc.mysql.json.model.User">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="username" jdbcType="VARCHAR" property="username" />
    <result column="mobile" jdbcType="VARCHAR" property="mobile" />
    <result column="sex" jdbcType="SMALLINT" property="sex" />
    <result column="age" jdbcType="INTEGER" property="age" />
    <result column="password" jdbcType="VARCHAR" property="password" />
    <result column="address" property="address" javaType="com.ypc.mysql.json.model.Address" jdbcType="LONGVARCHAR" typeHandler="com.ypc.mysql.json.config.JSONHandler" />
    <result column="family" property="family" javaType="com.ypc.mysql.json.model.Family" jdbcType="LONGVARCHAR" typeHandler="com.ypc.mysql.json.config.JSONHandler" />
  </resultMap>

  <sql id="Base_Column_List">
    id, username, mobile, sex, age, password,address,family
  </sql>

  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_user
    where id = #{id,jdbcType=INTEGER}
  </delete>

  <!-- insert new user -->
  <insert id="insert" parameterType="com.ypc.mysql.json.model.User" useGeneratedKeys="true" keyProperty="id">
    insert into t_user
    (username, mobile, sex, age, password, address, family)
    values(#{username,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR},
      #{sex,jdbcType=SMALLINT}, #{age,jdbcType=INTEGER}, #{password,jdbcType=VARCHAR},
 #{address,jdbcType=VARCHAR,javaType=com.ypc.mysql.json.model.Address,typeHandler=com.ypc.mysql.json.config.JSONHandler},
      #{family,jdbcType=VARCHAR,javaType=com.ypc.mysql.json.model.Family,typeHandler=com.ypc.mysql.json.config.JSONHandler} )
  </insert>

  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_user
    where id = #{id,jdbcType=INTEGER}
  </select>

</mapper>

因为篇幅问题我只保留了3个mapper方法。这里需要注意的是数据JSON类型的列，即address和family，凡是有着两个字段的地方，可以指定具体的javaType，即model属性对应的类型（Address和Family）；typeHandler指定为自定义的JSONHandler，需要注意的是：必须指定typeHandler，JavaType我本以为也应该要有，但是经过试验发现不添加也是可以的。但是这里又引申出一个问题，就是我的JSONHandler创建的时候，怎么获取对应的Class？？？创建JSONHandler时的Class属性从哪里来的？？本来以为是根据xml中的javaType去创建JSONHandler的，现在发现不是这么回事。关于这个问题以后有时间再好好的跟踪一下启动的过程，看看怎么获取Class的。
另外如果项目启动中报错：

java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
可以修改MySQL的URL，在链接上添加allowPublicKeyRetrieval=true。

二、代码测试

1、测试User

接下来通过一个简单的查询、添加测试一下User类，创建User相关的controller和service，代码如下：

@RestController
@RequestMapping("/user")
public class UserController {

    private static final Logger LOGGER = LoggerFactory.getLogger(UserController.class);

    @Resource
    private UserService userService;

    private Gson gson = new Gson();

    @PostMapping("/save")
    public Map<String,Object> saveUser(@RequestBody User user) {
        LOGGER.info(">>>> save user action start,user info={} <<<<",gson.toJson(user));
        return userService.saveUser(user);
    }

    @PostMapping("/update/{userId}")
    public Map<String,Object> updateUser(@RequestBody User user, @PathVariable("userId") Integer userId) {
        LOGGER.info(">>>> update user action start,userId={}, user={} <<<<",userId,gson.toJson(user));
        return userService.updateUser(userId,user);
    }

    @GetMapping("/get/{id}")
    public User getById(@PathVariable("id") Integer id) {
        return userService.findById(id);
    }

}
// service
@Service
public class UserServiceImpl implements UserService {

    private static final Logger LOGGER = LoggerFactory.getLogger(UserServiceImpl.class);

    @Autowired
    private UserMapper userMapper;

    @Override
    public Map<String, Object> saveUser(User user) {
        Map<String,Object> resultMap = new HashMap<>();
        int insert = userMapper.insert(user);
        if (insert != 1) {
            LOGGER.error(">>>> save user failed,return count={} <<<<",insert);
            resultMap.put("message","save user to database failed");
            return resultMap;
        }
        resultMap.put("success",true);
        resultMap.put("code",200);
        return resultMap;
    }

    @Override
    public Map<String, Object> updateUser(Integer userId, User user) {
        Map<String,Object> resultMap = new HashMap<>();
        int update = userMapper.updateByPrimaryKey(user);
        if (update != 1) {
            LOGGER.error(">>>> update user failed,return count={} <<<<",update);
            resultMap.put("message","update user to database failed");
            return resultMap;
        }

        resultMap.put("success",true);
        resultMap.put("code",200);
        return resultMap;
    }

    @Override
    public User findById(Integer id) {
        User user = userMapper.selectByPrimaryKey(id);

        Address address = user.getAddress();
        Family family = user.getFamily();
        List<Child> children = family.getChildren();
        return user;
    }
}

测试添加操作，准备好要插入的数据，使用rest client调用/user/save接口，顺便给大家安利一下rest client这个idea自带工具，非常的好用。参数信息如下：


图－1.png

接口调用成功后，通过数据库图形化管理工具可以看到数据成功写入到数据库：


图－2.png

这就说明自定义的typeHandler生效了，有了这个typeHandler不需要在代码中手动将数据库JSON类型数据转成相应的model类型，使用起来非常的方便。
查询接口非常简单，直接返回相应user的JSON数据，这里就不再放查询结果了。
2、测试Account

接下来测试使用字符串保存数据的Account代码，Account的model类和相关的mapper都是自动生成的，不需要手动进行修改，Account代码如下：

public class Account {
    private Integer id;

    private String username;

    private String mobile;

    private String address;

    private Short sex;

    private Integer age;

    private String password;

    private String family;
    // 省略 get set
    ......
｝

因为我们使用的是字符串保存Address和Family，因此也不需要定义typeHandler，AccountMapper.xml也不需要修改，这里就不再放相应代码了，创建controller和service，代码如下：

// controller
@RestController
@RequestMapping("/account")
public class AccountController {

    private static final Logger LOGGER = LoggerFactory.getLogger(AccountController.class);

    private Gson gson = new Gson();

    @Autowired
    private AccountService accountService;

    @PostMapping("/save")
    public Map<String,Object> saveUser(@RequestBody Account account) {
        LOGGER.info(">>>> save user action start,user info={} <<<<",gson.toJson(account));
        return accountService.saveUser(account);
    }


    @GetMapping("/get/{id}")
    public Account getById(@PathVariable("id") Integer id) {
        return accountService.findById(id);
    }
}
// service
@Service
public class AccountServiceImpl implements AccountService {

    private static final Logger LOGGER = LoggerFactory.getLogger(AccountServiceImpl.class);

    @Autowired
    private AccountMapper accountMapper;

    private Gson gson = new Gson();

    @Override
    public Map<String, Object> saveUser(Account account) {
        LOGGER.info(">>>> save account service start,account;{} <<<<",gson.toJson(account));
        Map<String,Object> resultMap = new HashMap<>();

        int insert = accountMapper.insert(account);
        if (insert != 1) {
            // TODO throw exception
            LOGGER.error(">>>> error message when insert new account,result count={} <<<<",insert);
        }
        resultMap.put("success",true);
        resultMap.put("code",200);
        return resultMap;
    }

    @Override
    public Account findById(Integer id) {
        LOGGER.info(">>>> find by id,id={} <<<<",id);
        Account account = accountMapper.selectByPrimaryKey(id);
        String addressStr = account.getAddress();
        Address address = gson.fromJson(addressStr, Address.class);
        LOGGER.info(">>>> get address model, address to string:{} <<<<",address);
        String familyStr = account.getFamily();
        Family family = gson.fromJson(familyStr,Family.class);
        LOGGER.info(">>>> get family model, family to string:{} <<<<",family);

        return account;
    }
}

代码都很简单没什么可说的，还是先测试添加操作，这里需要注意一下，添加时address和family虽然类型是字符串，但是为了能够反序列成功，一定要使用JSON串，即JSON对象转义成String。调用/account/save接口，接口调用信息如下：


图-3.png

接口调用成功后，我们还是通过工具下数据库，看看数据库中存储的数据：


图-4.png

如果只通过工具查看数据库数据的话，存储的address和family的数据和t_user没什么差别。确实是这样的，本质它们都是JSON串，只不过数据类型不同罢了，但是实际使用上却大不相同。
接下测试下Account的查询方法，调用/account/get/{id}接口，我在service中将address和family两个类型转成了对应的model且输出在命令行，结果如下：
图－5.png

上面这个输出结果表明转成对应model类是没问题，也说明添加account时将JSON串转义成String是成功的。但是我们response的相应结果却是这样的：


图-6.png

address和family依然是String类型，并不是JSON类型，前端获取到这个响应结果以后需要手动将其转成JSON的，虽然不如User测试里面返回全是JSON那么方便，但是也还可以接受吧。

三、比较

上面通过使用JSON类型和String类型测试，虽然使用上String稍微麻烦一点，但是也还可以接受。所以JSON类型相比使用String类型到底优势在哪里？举个简单的场景，有一个查询需要将address列中"city"等于"nanjing"作为一个查询条件。那么使用String类型的话应该怎么做？我想到使用like(当然可能有更好的方法)，SQL如下：

select id,username,mobile,address from spring.t_account where address like "%\"city\":\"nanjing\"%";

SQL执行输出如下：


图－7.png

注意这条SQL的执行时间，总共数据记录不到10条。我们都知道使用like查询效率会比较低，虽然可以添加索引解决，但是address列如果数据较多，那么造成索引也会比较大，维护起来并不轻松。
如果使用JSON结果会怎么样？SQL如下：

select id,username,mobile,address from spring.t_user where address -> '$.city' = 'nanjing';

SQL执行输出如下：

图－8.png

两个表除了主键索引之外都没有任何其他索引，根据输出对比就可以看出来，使用JSON数据时执行时间只有3ms，而是String类型是8ms，这还是在user表数据比account多的情况下。当然我这个比较其实不科学也不合理，但是至少还是可以反应出一点问题的。
好了，今天关于如何自定义typeHanler处理JSON类型数据就到这里了，其实主要学习怎么自定义类型处理器，还有就是JSON类型的使用，以及JSON类型和String类型对比和使用上的差别。关于最后的SQL执行时间对比大家看看就好，其实自己对数据库并不怎么熟悉，尤其是JSON这点，文档也没怎么看，只是临时起意想对比一下，感兴趣的小伙伴可以专门进行测试一下。这次代码也上传到我的github

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,013评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,205评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,370评论 0赞 342
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,168评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,153评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,954评论 1赞 283
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,271评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,916评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,382评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,877评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,989评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,624评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,209评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,199评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,418评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,401评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,700评论 2赞 345

推荐阅读更多精彩内容