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();