1. 場(chǎng)景舉例
sku表中存儲(chǔ)了一些規(guī)格屬性咨察，在sku表中的字段名字為specs蜓谋，用了一個(gè)json數(shù)組表示的担忧，數(shù)據(jù)格式如下所示
[{"key":"顏色","value":"金屬灰","keyId":1,"valueId":45},{"key":"圖案","value":"七龍珠","keyId":3,"valueId":9},{"key":"尺碼","value":"小號(hào) S","keyId":4,"valueId":14}]
- 字段解釋
keyId：規(guī)格id
key：規(guī)格名字
valueId：規(guī)格值Id
value：規(guī)格值
2. 遇到問(wèn)題
我使用DO映射數(shù)據(jù)庫(kù)表sku馁启，使用list數(shù)據(jù)類型來(lái)表示sku表的specs字段。這樣就會(huì)報(bào)錯(cuò)秋度。
正常來(lái)說(shuō)都是使用String來(lái)表示varchar炸庞，mybatisplus代碼生成器生成的代碼亦是如此。
為了給前端展示和修改查詢這些規(guī)格值方便荚斯，使用list來(lái)表示最舒服埠居。這樣前端操作的specs為list查牌，就不用自己轉(zhuǎn)化String為list再操作數(shù)據(jù)那么麻煩了
3. 解決問(wèn)題思路分析
- 我的需求：使用Java的list來(lái)對(duì)應(yīng)mysql數(shù)據(jù)庫(kù)的varchar類型
- 解決：我的想法很簡(jiǎn)單，我需要有個(gè)東西滥壕，在查詢mysql以后轉(zhuǎn)化為javaBean之前會(huì)轉(zhuǎn)化一下數(shù)據(jù)纸颜，轉(zhuǎn)化成符合javaBean的樣子。在插入數(shù)據(jù)到mysql的時(shí)候也是這樣绎橘，也是在插入數(shù)據(jù)前面把javaBean的數(shù)據(jù)轉(zhuǎn)化為符合mysql表的樣子(簡(jiǎn)單說(shuō)：插入數(shù)據(jù)的時(shí)候javaBean的specs屬性是個(gè)list胁孙，執(zhí)行插入的之前，會(huì)把這個(gè)specs轉(zhuǎn)為String金踪，這樣string就對(duì)應(yīng)mysql的varchar浊洞，這樣就可以牵敷。反之查詢的時(shí)候亦是如此)
- 結(jié)果：mybatis-plus有個(gè)@TableField注解有個(gè)typeHandler屬性可以解決
4. 解決問(wèn)題步驟
- 自定義一個(gè)通用的轉(zhuǎn)換器(任意類型與json互相轉(zhuǎn)換)
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);
}
}
}
- 自定義一個(gè)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字符串 互相轉(zhuǎn)化
*
* @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的注解
這第三步的重點(diǎn)是
@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對(duì)象", description = "")
public class SkuDO extends BaseDO {
@ApiModelProperty("主鍵id")
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty("價(jià)格")
@TableField("price")
private BigDecimal price;
@ApiModelProperty("上線：0-否;1->是;")
@TableField("is_online")
private Boolean online;
@ApiModelProperty("標(biāo)題")
@TableField("title")
private String title;
@ApiModelProperty("spu_id")
@TableField("spu_id")
private Long spuId;
@ApiModelProperty("規(guī)格")
@TableField(value = "specs", typeHandler = ObjectAndJsonHandler.class)
private List<SpecDO> specs;
- 該實(shí)體類的xml中的specs字段配置使用的handler
<result column="specs" property="specs" typeHandler="cn.wangningbo.mall.util.ObjectAndJsonHandler"/>
5. 最終效果
插入數(shù)據(jù)時(shí)參數(shù)格式
{
"online": true,
"price": 66.00,
"specs": [
{
"key": "顏色",
"keyId": 1,
"value": "青芒色",
"valueId": 42
},
{
"key": "圖案",
"keyId": 3,
"value": "灌籃高手",
"valueId": 10
},
{
"key": "尺碼",
"keyId": 4,
"value": "中號(hào) M",
"valueId": 15
}
],
"spuId": 1,
"stock": 0,
"title": "青芒色·灌籃高手"
}
插入到mysql后的樣式
image.png
查詢后的結(jié)果
{
"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": "青藍(lán)色"
},
{
"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": "小號(hào) 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": "中號(hào) M"
}
],
"code": null,
"stock": 0
}
]
}
