Reading a JSON from DB with myBatis

Question

I have a column in DB I want to save it as JSON and retrieve it back.

My approach is:

I am saving it as ClobTypeHandler and trying to retrieve it as ClobTypeHandler.

I am using Mybatis, However getting below error.

Could not set property 'idType' of 'class package.abc' with value '{"idNum":"123","text":"ENCkk-KZJTmN8Mr5jEims0rssRow8xaAnkOtl0RQHDth1ByVtshI7zQebtcogOvYM-gNo15DwwPmduaufk03CteqRL03cRyrG4%3B","key":"}P]H73}AF}TGB$OIDCYVIIB+VW{4TR)I?U}_`_ZXP[UY$BJNXV{U~/@!F%+SVMFYT/2IAXIOPB"}' Cause: java.lang.IllegalArgumentException: argument type mismatch

Below is the java layer and DB detail.

class abc{

    private JsonNode idType;

    public String getIdType() {
         return idType != null ? idType.toString():null;
    }
    public void setIdType(JsonNode idType) {
        this.idType = idType;
    }
}

mapper.xml (Inserting to DB)

INSERT INTO CUSTOMER
    (<include refid="common_customer_insert_columns"></include>,id_type)        
 VALUES
    (<include refid="common_customer_insert_fields"></include>,<include refid="cc_customer_insert_fields"></include>,
        <choose> <when test="abc.idType !=null">#{abc.idType,typeHandler= org.apache.ibatis.type.ClobTypeHandler}</when>
                <otherwise>''</otherwise></choose>);

mapper.xml (while reading from DB)

<resultMap>
    <result column="id_type" property="abc.idType" 
        typeHandler="org.apache.ibatis.type.ClobTypeHandler" />
</resultMap>

sample JSON value I am trying to save and retrieve:

"idType": {  
       "idNum": "123",  
       "text": "ENh-KZJTmN8Mr5jEims0rssRow8xaADth1ByVtshI7zQebtcogOvYM-gNo15DwwPmduaufk03CteqRLaVwF0b3cRyrG4%3D",  
       "key":"}P]H73}AF}TGB$OICQ*DCYVIIB+VW{4TR)I?U}_`_ZXP[UY$BJNXV{@!F%+SVMFYT/2IAXIOPB"  
      }  

Answer 1

To Save Data (Pojo Class) as JSON value and Read as POJO Class, we need to write custom TypeHandler

suppose we have an Employee class where config object need to save as JSON in DB and read as Config Object

Employee.java

public class Employee {
    private int id;
    private String name;
    private Config config; //save as josn in DB
}

Config.java

public class Config implements Serializable{
        private static final long serialVersionUID = 1L;
        private String key;
        private String value;
        private String msg;
        private int referenceNumber;
}

JsonTypeHandler.java

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


@MappedTypes({Config.class})
public class JsonTypeHandler extends BaseTypeHandler<Config>  {

    ObjectMapper objectMapper = new ObjectMapper();

    
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Config config, JdbcType jdbcType) throws SQLException {
        try {
            preparedStatement.setObject(i, objectMapper.writeValueAsString(config));

        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Config getNullableResult(ResultSet resultSet, String s) throws SQLException {
        if(resultSet.getString(s) != null){
            try {
                return objectMapper.readValue(resultSet.getString(s), Config.class);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public Config getNullableResult(ResultSet resultSet, int i) throws SQLException {
        if(resultSet.getString(i) != null){
            try {
                return objectMapper.readValue(resultSet.getString(i), Config.class);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public Config getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        if(callableStatement.getString(i) != null){
            try {
                return objectMapper.readValue(callableStatement.getString(i), Config.class);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

MyBatis insert query

@Insert("insert into employee(name, config) values(#{employee.name}, #{employee.config, typeHandler=com.example.demo.image.JsonTypeHandler})")
    void saveEmployee(@Param("employee") Employee employee);

MyBatis Select query

@Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "config", column = "config", typeHandler =com.example.demo.image.JsonTypeHandler.class),

    })
    @Select("select id, name, config from employee")
        List<Employee> getAll();

Answer 2

You can do that without using the POJO:

ObjectMapper mapper = new ObjectMapper();
JsonNode idType = mapper.readTree(<json-string>);

Answer 3

ClobTypeHandler extends BaseTypeHandler<String> so it allows to set and retrieve value from database column as a String. When you use ClobTypeHandler like this:

#{abc.idType,typeHandler= org.apache.ibatis.type.ClobTypeHandler}

mybatis under the hood expects that abc.getIdType returns String, which it does. So far so good.

When you retrieve the value from the database the mapping used is:

<result column="id_type" property="abc.idType"
     typeHandler="org.apache.ibatis.type.ClobTypeHandler" />

In this case ClobTypeHandler tries to set idType property using setIdType setter and expects it to accept String. But the setter argument is JsonNode hence you get the message that type is incompatible.

To fix this change the type setter gets and do conversion from String to JsonNode in it:

public void setIdType(String idTypeString) {
     ObjectMapper mapper = new ObjectMapper();
     this.idType = mapper.readTree(isTypeString);
}

Note: you can use static ObjectMapper to avoid creation it on every invocation.

A better approach is to create your own TypeHandler for type JsonNode. It would do String <=> JsonNode conversion internally so you abc would be not polluted with this (unrelated) logic.