java - 使用 myBatis 从数据库读取 JSON

Question

我在数据库中有一列，我想将其保存为 JSON 并检索回来。

我的做法是:

我将其保存为 ClobTypeHandler 并尝试将其检索为 ClobTypeHandler。

我正在使用 Mybatis，但是出现以下错误。

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

下面是 java 层和 DB 的详细信息。

class abc{

    private JsonNode idType;

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

mapper.xml(插入数据库)

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(从数据库读取时)

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

我正在尝试保存和检索的示例 JSON 值:

"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

要将数据(Pojo 类)保存为 JSON 值并读取为 POJO 类，我们需要编写自定义 TypeHandler

假设我们有一个 Employee 类，其中配置对象需要在数据库中保存为 JSON 并读取为配置对象

员工.java

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

配置.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("insert into employee(name, config) values(#{employee.name}, #{employee.config, typeHandler=com.example.demo.image.JsonTypeHandler})")
    void saveEmployee(@Param("employee") Employee employee);

MyBatis 选择查询

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