1. 场景举例
sku表中存储了一些规格属性，在sku表中的字段名字为specs，用了一个json数组表示的，数据格式如下所示
[{"key":"颜色","value":"金属灰","keyId":1,"valueId":45},{"key":"图案","value":"七龙珠","keyId":3,"valueId":9},{"key":"尺码","value":"小号 S","keyId":4,"valueId":14}]
- 字段解释
keyId：规格id
key：规格名字
valueId：规格值Id
value：规格值
2. 遇到问题
我使用DO映射数据库表sku，使用list数据类型来表示sku表的specs字段。这样就会报错。
正常来说都是使用String来表示varchar，mybatisplus代码生成器生成的代码亦是如此。
为了给前端展示和修改查询这些规格值方便，使用list来表示最舒服。这样前端操作的specs为list，就不用自己转化String为list再操作数据那么麻烦了
3. 解决问题思路分析
- 我的需求：使用Java的list来对应mysql数据库的varchar类型
- 解决：我的想法很简单，我需要有个东西，在查询mysql以后转化为javaBean之前会转化一下数据，转化成符合javaBean的样子。在插入数据到mysql的时候也是这样，也是在插入数据前面把javaBean的数据转化为符合mysql表的样子(简单说：插入数据的时候javaBean的specs属性是个list，执行插入的之前，会把这个specs转为String，这样string就对应mysql的varchar，这样就可以。反之查询的时候亦是如此)
- 结果：mybatis-plus有个@TableField注解有个typeHandler属性可以解决
4. 解决问题步骤
- 自定义一个通用的转换器(任意类型与json互相转换)
package cn.wangningbo.mall.util;
import cn.wangningbo.mall.exception.server.ServerException;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
/**
* @author wangningbo
*/
@Component
public class GenericAndJson {
private static ObjectMapper mapper;
@Autowired
public void setMapper(ObjectMapper mapper) {
GenericAndJson.mapper = mapper;
}
public static <T> String objectToJson(T o) {
try {
return GenericAndJson.mapper.writeValueAsString(o);
} catch (Exception e) {
e.printStackTrace();
throw new ServerException(9999);
}
}
public static <T> T jsonToObject(String s, TypeReference<T> typeReference) {
if (s == null) {
return null;
}
try {
return GenericAndJson.mapper.readValue(s, typeReference);
} catch (JsonProcessingException e) {
e.printStackTrace();
throw new ServerException(9999);
}
}
}
- 自定义一个handler
package cn.wangningbo.mall.util;
import com.fasterxml.jackson.core.type.TypeReference;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* object和json字符串 互相转化
*
* @author wangningbo
*/
public class ObjectAndJsonHandler extends BaseTypeHandler<Object> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
String json = GenericAndJson.objectToJson(parameter);
ps.setString(i, json);
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
String string = rs.getString(columnName);
return GenericAndJson.jsonToObject(string, new TypeReference<>() {
});
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String string = rs.getString(columnIndex);
return GenericAndJson.jsonToObject(string, new TypeReference<>() {
});
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String string = cs.getString(columnIndex);
return GenericAndJson.jsonToObject(string, new TypeReference<>() {
});
}
}
- 配置自定义的handler到do上，配合mybatis-plus的注解
这第三步的重点是
@TableName(value = "sku", autoResultMap = true)
@TableField(value = "specs", typeHandler = ObjectAndJsonHandler.class)
package cn.wangningbo.mall.pojo.model;
import lombok.Getter;
import lombok.Setter;
/**
* @author wangningbo
*/
@Getter
@Setter
public class SpecDO {
private Long keyId;
private String key;
private Long valueId;
private String value;
}
package cn.wangningbo.mall.pojo.model;
import cn.wangningbo.mall.util.ObjectAndJsonHandler;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import java.math.BigDecimal;
import java.util.List;
/**
* <p>
*
* </p>
*
* @author wangningbo
* @since 2021-11-06
*/
@Getter
@Setter
@TableName(value = "sku", autoResultMap = true)
@ApiModel(value = "SkuDO对象", description = "")
public class SkuDO extends BaseDO {
@ApiModelProperty("主键id")
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty("价格")
@TableField("price")
private BigDecimal price;
@ApiModelProperty("上线：0-否;1->是;")
@TableField("is_online")
private Boolean online;
@ApiModelProperty("标题")
@TableField("title")
private String title;
@ApiModelProperty("spu_id")
@TableField("spu_id")
private Long spuId;
@ApiModelProperty("规格")
@TableField(value = "specs", typeHandler = ObjectAndJsonHandler.class)
private List<SpecDO> specs;
- 该实体类的xml中的specs字段配置使用的handler
<result column="specs" property="specs" typeHandler="cn.wangningbo.mall.util.ObjectAndJsonHandler"/>
5. 最终效果
插入数据时参数格式
{
"online": true,
"price": 66.00,
"specs": [
{
"key": "颜色",
"keyId": 1,
"value": "青芒色",
"valueId": 42
},
{
"key": "图案",
"keyId": 3,
"value": "灌篮高手",
"valueId": 10
},
{
"key": "尺码",
"keyId": 4,
"value": "中号 M",
"valueId": 15
}
],
"spuId": 1,
"stock": 0,
"title": "青芒色·灌篮高手"
}
插入到mysql后的样式
image.png
查询后的结果
{
"id": 1,
"title": "针织衫",
"subtitle": "秋日冬款，浪漫满屋",
"categoryId": 1,
"rootCategoryId": 2,
"price": "77.00",
"sketchSpecId": 1,
"defaultSkuId": 1,
"img": null,
"discountPrice": "62.00",
"description": null,
"tags": "秋日冬款;浪漫满屋",
"forThemeImg": null,
"skuVOList": [{
"id": 1,
"price": 13.80,
"discountPrice": null,
"online": true,
"img": null,
"title": "青峰·7英寸",
"spuId": 1,
"categoryId": null,
"rootCategoryId": null,
"specs": [{
"keyId": 1,
"key": "颜色",
"valueId": 1,
"value": "青蓝色"
},
{
"keyId": 2,
"key": "尺寸",
"valueId": 5,
"value": "7英寸"
}
],
"code": null,
"stock": 0
},
{
"id": 2,
"price": 77.76,
"discountPrice": null,
"online": true,
"img": null,
"title": "金属灰·七龙珠",
"spuId": 1,
"categoryId": null,
"rootCategoryId": null,
"specs": [{
"keyId": 1,
"key": "颜色",
"valueId": 45,
"value": "金属灰"
},
{
"keyId": 3,
"key": "图案",
"valueId": 9,
"value": "七龙珠"
},
{
"keyId": 4,
"key": "尺码",
"valueId": 14,
"value": "小号 S"
}
],
"code": null,
"stock": 0
},
{
"id": 3,
"price": 66.00,
"discountPrice": null,
"online": true,
"img": null,
"title": "青芒色·灌篮高手",
"spuId": 1,
"categoryId": null,
"rootCategoryId": null,
"specs": [{
"keyId": 1,
"key": "颜色",
"valueId": 42,
"value": "青芒色"
},
{
"keyId": 3,
"key": "图案",
"valueId": 10,
"value": "灌篮高手"
},
{
"keyId": 4,
"key": "尺码",
"valueId": 15,
"value": "中号 M"
}
],
"code": null,
"stock": 0
}
]
}
