【问题解决】Mybatis中使用net.sf.json.JSONObject接收数据库为null的数据时报错
使用SpringBoot+Mybatis+PostgreSQL搭建后端服务接口。Mybatis接收数据库返回数据,resultType使用json作为接收容器。用于实现给定一个多边形空间范围查找对应范围内的目标结果。
项目场景:
使用SpringBoot+Mybatis+PostgreSQL搭建后端服务接口。
Mybatis接收数据库返回数据,resultType使用json作为接收容器。
control层:
@Operation(summary = "地址数据")
@GetMapping("/doorplate")
public ResultResponse findByDoorplateExtent(@Parameter(description = "空间范围,组织为多边形坐标的形式,注意首尾坐标一致,例如POLYGON((112.20576805114746 30.367256514643596, 112.10380554199219 30.457256514643596, 112.10380554199219 30.39434753102366, 112.26576805114746 30.39434753102366, 112.20576805114746 30.367256514643596),(112.16049236297609 30.38571390562601, 112.22049236297609 30.414260761324122, 112.13951110839845 30.414260761324122, 112.13951110839845 30.44571390562601, 112.16049236297609 30.38571390562601))")String polygon){
JSONObject obj = pointExtentService.findDoorplateByExtent(polygon);
return ResultResponse.success(obj); }
server层:
@Override
public JSONObject findDoorplateByExtent(String polygon){
JSONObject jsonObj = extentMapper.findDoorplateByExtent(polygon).getJSONObject("row_to_json");
String value = jsonObj.getString("value");
return JSONObject.fromObject(value);
}
mapper层:
package com.example.toponym.mapper;
import net.sf.json.JSONObject;
public interface ExtentMapper {
JSONObject findDoorplateByExtent(String polygon);
}
xml文件里的sql语句: 用于实现给定一个多边形空间范围查找对应范围内的目标结果。
<select id="findDoorplateByExtent" parameterType="String" resultType="net.sf.json.JSONObject">
SELECT row_to_json ( fc ) FROM
( SELECT 'FeatureCollection' AS TYPE, json_agg ( f ) AS features
FROM
(SELECT 'Feature' AS TYPE,
(SELECT row_to_json ( T )
FROM
(SELECT "uid","road") AS T ) AS properties,
ST_AsGeoJSON ( "geometry" ) :: JSON AS geometry
FROM "T_S_DOORPLATE" WHERE
ST_Intersects ( "geometry", ST_GeomFromText ( #{ POLYGON }, 4326 ) )
) AS f
) AS fc
</select>">
问题描述
存在当数据库中值为空的时候,报错:
Caused by: com.fasterxml.jackson.databind.JsonMappingException: Object is null (through reference chain:
com.example.toponym.model.ResultResponse["result"]
->net.sf.json.JSONObject["features"]->net.sf.json.JSONArray[3]->net.sf.json.JSONObject["properties"]
->net.sf.json.JSONObject["road"]->net.sf.json.JSONNull["empty"])
报错原因为
:查询结果中,road字段有一个值为空值,返回的json数据中有属性为null的情况net.sf.json.JSONObject无法处理。
解决方案:
方案一:
引入fastjson依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.66</version>
</dependency>
xml容器修改;同时mapper层引入fastjson
<select id="findDoorplateByExtent" parameterType="String"
resultType="com.alibaba.fastjson.JSONObject">
方案二:
修改sql,设置默认值
应用场景:
当代码中使用net.sf.json.JSONObject过多,不便重构代码时。修改xml中sql语句的检索方式即可,当sql查询某个字段为空时赋新值。
pgsql函数:
NULLIF(value1,value2) :当value1 == value2时,返回null
COALESCE(col, ‘replacement’) :如果col列的值为null,则col的值将被替换为’replacement’
select COALESCE(NULLIF(trim(字段名), ''), '默认值') as 别称 from 表名
对比试验:
(1)普通select:
(2)使用COALESCE,将数据库中为null的值修改为‘null’字符串,使用as别名road(不使用as默认coalesce)
(3)使用COALESCE+NULLIF,当road==‘发展大道’&&road == null时,将数据库中的值修改为‘aaa’字符串
(4)注意:替换值要和字段值类型一致。
应用:
修改xml代码(同时添加了一些其他字段):
<select id="findDoorplateByExtent" parameterType="String" resultType="net.sf.json.JSONObject">
SELECT row_to_json ( fc ) FROM
( SELECT 'FeatureCollection' AS TYPE, json_agg ( f ) AS features
FROM
(SELECT 'Feature' AS TYPE,
(SELECT row_to_json ( T )
FROM
(SELECT "uid","district","township","village",
COALESCE(road, 'null') as road,
COALESCE(doorplate, 'null') as doorplate,
COALESCE(doorplate_sub, 'null') as doorplate_sub
) AS T ) AS properties,
ST_AsGeoJSON ( "geometry" ) :: JSON AS geometry
FROM "T_S_DOORPLATE" WHERE
ST_Intersects ( "geometry", ST_GeomFromText ( #{ POLYGON }, 4326 ) )
) AS f
) AS fc
</select>">
成功检索结果:
更多推荐
所有评论(0)