数据库映射为实体类
有一些数据想要映射为实体类是比较麻烦的,特别是一些存储在数据库里的 JSON 数据,如何查询处理该类数据呢?
1. 数据库JSON数据展示
== 现有如下类型的 JSON 数据存储在数据库里 ==
数据库中的数据:
区域坐标:List<List<Coordinate>> pointsArea
[
[{"latitude":31.5330733903248,"longitude":120.290795214727},{"latitude":31.5324866150653,"longitude":120.291653693163}],
[{"latitude":31.5309520811737,"longitude":120.291427281644},{"latitude":31.5303686190166,"longitude":120.291151310285}],
[{"latitude":31.5303686190166,"longitude":120.291151310285},{"latitude":31.5303686190166,"longitude":120.291151310285}]
]
=================================================================================================================================================
路段坐标:List<Coordinate> pointsRoad
[
{"latitude": 31.50184166903764, "longitude": 120.34707069396974}, {"latitude": 31.499607874509053, "longitude": 120.34785389900208},
{"latitude": 31.488777200863705, "longitude": 120.3506112098694}, {"latitude": 31.48862645725139, "longitude": 120.3506112098694},
{"latitude": 31.48846155139795, "longitude": 120.3506809473038}, {"latitude": 31.487921760889336, "longitude": 120.35077750682832},
{"latitude": 31.487656442554535, "longitude": 120.35082042217256}, {"latitude": 31.487381222076483, "longitude": 120.35080432891849}
]
==================================================================================================================================================
地图点位:List<Long> pointsId
[31, 32, 33, 34, 35, 36]
==================================================================================================================================================
保障区域(行政区):List<Integer> belongRegions
[0, 1, 2, 3, 4, 5, 6]
分别映射到下面的参数中
2. JSON数据映射到实体类
注:autoResultMap = true 一定要添加,否则无法成功映射
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName(value = "guarantee_task" , autoResultMap = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class GuaranteeTask extends BaseEntity {
private static final long serialVersionUID = 1L;
/** 保障区域(行政区) */
@TableField(typeHandler = JacksonTypeHandler.class)
private List<Integer> belongRegions;
/** 地图点位 id */
@TableField(typeHandler = ListInteger2ListLongTypeHandler.class)
private List<Long> pointsId;
/** 保障等级*/
private Integer taskLevel;
/** 区域坐标 */
@TableField(typeHandler = JacksonTypeHandler.class)
private List<List<Coordinate>> pointsArea;
/** 路段坐标 */
@TableField(typeHandler = JacksonTypeHandler.class)
private List<Coordinate> pointsRoad;
}
- Coordinate映射的实体类
/**
* @author Memo
* @version 1.0
* @description: 坐标点
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Coordinate {
private BigDecimal latitude;
private BigDecimal longitude;
}
3. JSON数据映射到实体类后的注意点
- 实体类映射
对实体类映射完毕后,List<Coordinate>
里,Coordinate里的数据,其实是一个 List<LinkedMap>
,集合内部并非想要的 Coordinate 实体类,故需对内部LinkedMap 数据格式进行处理,转为目标实体类
转换方法:List<LinkMap>
-> JSON-String -> List<Coordinate>
使用工具:Alibaba jackson 或 fastJson
import com.fasterxml.jackson.databind.ObjectMapper;
class test {
private transMethod() {
List<Coordinate> points = areaInfoDO.getPoints();
ObjectMapper objectMapper = new ObjectMapper();
String json = objectMapper.writeValueAsString(points);
points = JSON.parseObject(json, new TypeReference<List<Coordinate>>() {});
}
}
如果需要遍历并处理映射完毕的 List,需要把List先转为可使用的 List后,进行处理
如果只是单纯查询出来返给前端,无须转换
直接用MP查询数据时,在类的字段上添加属性,如果写了sql,此处的注解会不起作用
- Long 类型映射
MP对Long类型的映射,并没有实现,需要自行实现一个
/** @TableField(typeHandler = JacksonTypeHandler.class)无法映射 Long 类型,
* 故在此给一个自定义的 helper 来处理 Long 类型的映射 <=========> 工具类
* */
import com.alibaba.fastjson2.JSON;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.type.CollectionType;
import com.justai.icp.smartcity.exception.IcpBizException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.util.StringUtils;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 数据类型转换
*/
@MappedJdbcTypes(JdbcType.VARCHAR) // 数据库中该字段存储的类型
@MappedTypes(List.class) // 需要转换的对象
public class ListInteger2ListLongTypeHandler extends BaseTypeHandler<List<Long>> {
private static ObjectMapper objectMapper = new ObjectMapper();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<Long> parameter, JdbcType jdbcType) throws SQLException {
ps.setObject(i, JSON.toJSONString(parameter));
}
@Override
public List<Long> getNullableResult(ResultSet rs, String columnName) throws SQLException {
return getLongs(rs.getString(columnName));
}
@Override
public List<Long> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return getLongs(rs.getString(columnIndex));
}
@Override
public List<Long> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return getLongs(cs.getString(columnIndex));
}
private List<Long> getLongs(String value) {
if (StringUtils.hasText(value)) {
try {
CollectionType type = objectMapper.getTypeFactory().constructCollectionType(ArrayList.class, Long.class);
return objectMapper.readValue(value, type);
//List<Long> longs = JsonUtil.parseArray(value, Long.class);
} catch (JsonProcessingException e) {
e.printStackTrace();
throw new IcpBizException(e);
}
}
return null;
}
}
- mybatis.xml 文件里的字段映射
使用sql语句查询时,使用如下方式映射
<resultMap id="BaseResultMap" type="com.justai.icp.smartcity.entity.AssessProblem">
<id column="id" property="id"/>
<result column="task_user_name" property="taskUserName"/>
<result column="attachment" property="attachment" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<result column="violate_info" property="violateInfo" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<result column="task_level" property="taskLevel"/>
<result column="problem_points" property="problemPoints" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<result column="work_comp_name" property="workCompName"/>
</resultMap>
其中,typeHandler可以写自己的处理类,填入自己处理类的全类名就好
自行实现处理类:
- 实现目标接口:mybatis包下的处理类
package org.apache.ibatis.type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface TypeHandler<T> {
void setParameter(PreparedStatement var1, int var2, T var3, JdbcType var4) throws SQLException;
T getResult(ResultSet var1, String var2) throws SQLException;
T getResult(ResultSet var1, int var2) throws SQLException;
T getResult(CallableStatement var1, int var2) throws SQLException;
}
举个别人的栗子,这个类主要用于处理数据库中的几何类型数据,细节暂时不理解,后续明白了再补充
原理:读出原生数据后,对数据进行处理,得到处理后的数据
public class GeometryTypeHandler implements TypeHandler<Geometry> {
/**
* WKTReader非线程安全
*/
private static final ThreadLocal<WKTReader> READER_POOL = ThreadLocal.withInitial(WKTReader::new);
/**
* WKTWriter非线程安全
*/
private static final ThreadLocal<WKTWriter> WRITER_POOL = ThreadLocal.withInitial(WKTWriter::new);
@Override
public void setParameter(PreparedStatement preparedStatement, int i, Geometry geometry, JdbcType jdbcType) throws SQLException {
if (geometry == null) {
preparedStatement.setNull(i, Types.VARCHAR);
} else {
String wktString = WRITER_POOL.get().write(geometry).toString();
preparedStatement.setObject(i, wktString, Types.OTHER);
}
}
@Override
public Geometry getResult(ResultSet resultSet, String columnName) throws SQLException {
return parseFromString(resultSet.getString(columnName));
}
@Override
public Geometry getResult(ResultSet resultSet, int columnIndex) throws SQLException {
return parseFromString(resultSet.getString(columnIndex));
}
@Override
public Geometry getResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
return parseFromString(callableStatement.getString(columnIndex));
}
private Geometry parseFromString(String str) {
try {
return READER_POOL.get().read(str);
} catch (Exception e) {
throw new RuntimeException("Failed to parse geometry from string.", e);
}
}
}