postgresql 为什么MyBatis不能将JSONB类型Map到Map< String,Object>或JsonNode?

pdkcd3nj  于 2023-06-05  发布在  PostgreSQL
关注(0)|答案(1)|浏览(205)

我有一个DTO

@Data
@Accessors(chain = true)
public class UserHasAssetDTO {

    private Integer id;

    ...

    private Map<String, Object> props;
}

Repository,其中我有这样的方法:

@Select("select id, ..., props from bla bla bla")
List<UserHasAssetDTO> getALLAssetsFilteredByDate(@Param("username") String username, @Param("startDate") String startDate, @Param("endDate") String endDate);

注意:我的PostgreSQL数据库中的字段props是JSONB类型。
如果我手动执行查询,我可以看到props被正确地填充。
但是,在我服务的时候:

public List<UserHasAssetDTO> userHasALLAssets(String user, String start, String end) {

        List<UserHasAssetDTO> userHasAssetDTOList = userHasAssetRepository.getALLAssetsFilteredByDate(user, start, end);

        log.info("userHasALLAssets: {}", userHasAssetDTOList);

        return userHasAssetDTOList;
    }

prop 栏是null!!!
我怀疑我没有在DTO上正确MapPostgreSQL的JSONB类型。
如何修复它,以便正确填充props字段?

nzkunb0c

nzkunb0c1#

用这个类解决:

@MappedJdbcTypes({JdbcType.JAVA_OBJECT})
@MappedTypes({JsonNode.class})
public class JsonNodeTypeHandler extends BaseTypeHandler<JsonNode> {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JsonNode parameter, JdbcType jdbcType)
            throws SQLException {
        PGobject jsonObject = new PGobject();
        jsonObject.setType("jsonb");
        try {
            jsonObject.setValue(parameter.toString());
            ps.setObject(i, jsonObject);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String jsonSource = rs.getString(columnName);
        if (jsonSource != null) {
            try {
                return objectMapper.readTree(jsonSource);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return null;
    }

    @Override
    public JsonNode getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String jsonSource = rs.getString(columnIndex);
        if (jsonSource != null) {
            try {
                return objectMapper.readTree(jsonSource);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return null;
    }

    @Override
    public JsonNode getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String jsonSource = cs.getString(columnIndex);
        if (jsonSource != null) {
            try {
                return objectMapper.readTree(jsonSource);
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
        return null;
    }
}

JsonNode类型。

相关问题