Mybatis操作mysql 8的Json字段类型
Json字段是从mysql 5.7起加进来的全新的字段类型，现在我们看看在什么情况下使用该字段类型，以及用mybatis如何操作该字段类型
一般来说，在不知道字段的具体数量的时候，使用该字段是非常合适的，比如说——商品的无限属性。
现在我们来假设这么一个场景，在商品的二级分类中给商品定义足够多的属性，我们先设计属性的类
/**
* 商品自定义属性
*/
@NoArgsConstructor
@AllArgsConstructor
public class OtherProperty implements Serializable {
@Getter
@Setter
private Long id; //属性id
@Getter
@Setter
private FormType formType; //前端使用的表单类型
@Getter
@Setter
private String name; //属性名称
@Getter
@Setter
private String unit; //单位
@Getter
@Setter
private String values; //可选值以@分隔，如配件@车品
@Getter
private List<String> valueList = new ArrayList<>(); //对可选值的取值列表
@Getter
@Setter
private String defaultValue; //可选值中的默认值
@Getter
@Setter
private boolean search; //是否可搜索
@Getter
@Setter
private boolean mustWrite; //是否必录
@Getter
@Setter
private Boolean used = false; //是否已经在商品中使用，已使用该属性则不允许修改
public OtherProperty changeValuesToList() {
String[] split = this.values.split("@");
for (String value : split) {
this.valueList.add(value);
}
this.values = null;
return this;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
OtherProperty that = (OtherProperty) o;
if (!id.equals(that.id)) return false;
if (search != that.search) return false;
if (mustWrite != that.mustWrite) return false;
if (formType != that.formType) return false;
if (!name.equals(that.name)) return false;
if (unit != null ? !unit.equals(that.unit) : that.unit != null) return false;
if (values != null ? !values.equals(that.values) : that.values != null) return false;
return defaultValue != null ? defaultValue.equals(that.defaultValue) : that.defaultValue == null;
}
@Override
public int hashCode() {
int result = id.hashCode() + formType.hashCode() + name.hashCode();
result = result + (unit != null ? unit.hashCode() : 0);
result = result + (values != null ? values.hashCode() : 0);
result = result + (defaultValue != null ? defaultValue.hashCode() : 0);
result = result + (search ? 1 : 0);
result = result + (mustWrite ? 1 : 0);
return result;
}
}其中formType为枚举类型
public enum FormType implements Localisable {
TYPE1("文本框"),
TYPE2("下拉框"),
TYPE3("单选框"),
TYPE4("复选框"),
TYPE5("多行文本框");
private String value;
private FormType(String value) {
this.value = value;
}
@Override
public String getValue() {
return this.value;
}
}我们来看一下商品分类的部分代码
@AllArgsConstructor
@NoArgsConstructor
public class ProviderProductLevel implements Provider,Serializable其中包含一个商品属性对象的列表
@Getter
@Setter
private List<OtherProperty> otherProperties;部分操作源码如下
/**
* 通过二级配件分类id查找其包含的所有其他属性
* @param
* @return
*/
public List<OtherProperty> findOtherProperties() {
if (this.level == 2) {
LevelDao levelDao = SpringBootUtil.getBean(LevelDao.class);
String ids = levelDao.findIdsByLevel2Id(this.id);
return levelDao.findOtherProperties(ids);
}
return null;
}
/**
* 在二级配件分类中删除其他属性的id
* @param paramIds
* @return
*/
public boolean deletePropertyIdfromLevel(String paramIds) {
if (this.level == 2) {
LevelDao levelDao = SpringBootUtil.getBean(LevelDao.class);
String ids = levelDao.findIdsByLevel2Id(this.id);
String[] idsArray = ids.split(",");
List<String> idsList = Arrays.asList(idsArray);
List<String> contentIdsList = new ArrayList<>();
contentIdsList.addAll(idsList);
String[] paramArray = paramIds.split(",");
List<String> paramList = Arrays.asList(paramArray);
if (contentIdsList.containsAll(paramList)) {
contentIdsList.removeAll(paramList);
}
if (contentIdsList.size() > 0) {
StringBuilder builder = new StringBuilder();
contentIdsList.stream().forEach(eachId -> builder.append(eachId + ","));
String newIds = builder.toString().substring(0, builder.toString().length() - 1);
levelDao.addOtherPropertiesToLevel(new ParamOtherPropertiesId(newIds, this.id));
}else {
levelDao.addOtherPropertiesToLevel(new ParamOtherPropertiesId("",this.id));
}
return true;
}
return false;
}/**
* 展示某二级配件分类的所有其他属性
* @param id
* @return
*/
@SuppressWarnings("unchecked")
@Transactional
@GetMapping("/productprovider-anon/showproperties")
public Result<List<OtherProperty>> showOtherProperties(@RequestParam("id") Long id) {
Provider level2 = levelDao.findLevel2(id);
return Result.success(((ProviderProductLevel)level2).findOtherProperties());
}
/**
* 修改某二级配件分类的其他属性
* @param id
* @param otherProperties
* @return
*/
@SuppressWarnings("unchecked")
@Transactional
@PostMapping("/productprovider-anon/changeother")
public Result<String> changeOtherProperties(@RequestParam("id") Long id,@RequestBody List<OtherProperty> otherProperties) {
//获取配件二级分类对象
Provider level2 = levelDao.findLevel2(id);
//获取未使用的配件二级分类的其他属性(没有任何商品使用过该属性)
List<OtherProperty> unUsedList = Optional.ofNullable(((ProviderProductLevel) level2).getOtherProperties()).map(otherProperties1 -> otherProperties1.stream())
.orElse(new ArrayList<OtherProperty>().stream())
.filter(otherProperty -> !otherProperty.getUsed())
.collect(Collectors.toList());
//获取已使用的配件二级分类的其他属性
List<Long> usedIdList = Optional.ofNullable(((ProviderProductLevel) level2).getOtherProperties()).map(otherProperties1 -> otherProperties1.stream())
.orElse(new ArrayList<OtherProperty>().stream())
.filter(otherProperty -> otherProperty.getUsed())
.map(OtherProperty::getId)
.collect(Collectors.toList());
//在传递回来的配件二级分类其他属性中校对没有修改过的，没有使用过的其他属性，只对修改过的，没有使用过的其他属性进行
//存储，否则不处理
List<OtherProperty> changeList = otherProperties.stream().filter(otherProperty -> Optional.ofNullable(otherProperty.getId()).isPresent())
.filter(otherProperty -> !unUsedList.contains(otherProperty))
.filter(otherProperty -> !usedIdList.contains(otherProperty.getId()))
.peek(otherProperty -> otherPropertyDao.deleteOtherPropertiesById(otherProperty.getId()))
.collect(Collectors.toList());
if (changeList.size() > 0) {
StringBuilder builder = new StringBuilder();
changeList.stream().map(OtherProperty::getId).forEach(eachId -> builder.append(eachId + ","));
String newIds = builder.toString().substring(0, builder.toString().length() - 1);
((ProviderProductLevel) level2).deletePropertyIdfromLevel(newIds);
((ProviderProductLevel) level2).addOtherProperties(changeList);
}
//获取新增的其他属性进行追加到配件二级分类的其他属性中
List<OtherProperty> newList = otherProperties.stream().filter(otherProperty -> !Optional.ofNullable(otherProperty.getId()).isPresent())
.peek(otherProperty -> otherProperty.setId(idService.genId()))
.collect(Collectors.toList());
((ProviderProductLevel) level2).addOtherProperties(newList);
return Result.success("修改成功");
}在进行一番增删改查后，数据库中的数据大致如下
我们查高级项链的所有属性的结果如下
现在我们要在属于该商品分类中添加商品，商品类定义大致如下
@Data
@NoArgsConstructor
public class ProviderProduct implements Provider {
private Product product; //配件元信息对象
private String code; //配件编码
private Brand brand; //品牌
private String details; //配件图文说明
private String levelName; //二级配件分类名称
private DefaultProvider provider; //配件商
private ExtBeanWrapper otherValues; //其他属性集合
}其中对应于属性列表的字段为otherValues，这个值正是我们要存入数据库的Json字段类型映射。
商品的数据库表结构如下
要使用mybatis的数据对Json字段类型的转换，可以先引用一个网上写好的转换器，当然也可以自己写
pom
<dependency>
<groupId>com.github.jeffreyning</groupId>
<artifactId>extcol</artifactId>
<version>0.0.1-RELEASE</version>
</dependency>配置文件中添加 type-handlers-package: com.nh.micro.ext.th
mybatis:
type-aliases-package: com.cloud.model.productprovider
type-handlers-package: com.nh.micro.ext.th
mapper-locations: classpath:/mybatis-mappers/*
configuration:
mapUnderscoreToCamelCase: true在mapper文件中写入一段插入语句
<insert id="saveProduct" parameterType="com.cloud.productprovider.composite.ProviderProduct">
insert into product (id,name,code,model,normal_price,price_begin,product_imgs,details,brand_id,other_property_value)
values (#{product.id},#{product.name},#{code},#{product.model},#{product.price.normalPrice},
<choose>
<when test="product.price.begin">
1
</when>
<otherwise>
0
</otherwise>
</choose>,
#{product.productImgs},
#{details},
#{brand.id},
#{otherValues,jdbcType=VARCHAR}
)
</insert>对应商品分类的每一个自定义属性，我们可以先拿到该自定义属性的id,然后以该id，取值为键值对进行插入
{ "product":{ "name":"AAAA", "model":"AAAAA", "price":{ "normalPrice":199, "begin":false }, "productImgs":"http://123.433.567.988" }, "code":"0001", "details":"<html><body><a href='sfasffg'><img url='sdfsgwer' /></a></body></html>", "brand":{ "id":1, "name":"飞利浦" }, "otherValues":{ "innerMap":{ "2459623566996408120":"10", "2459623566996409144":"呼和浩特", "2459623566996410168":"飞利浦", "2459623566996411192":"国际", "2459623566996412216":"包邮" } } }
执行之后，数据库的数据如下
该插件的数据类和转换器的源码如下，其实也是很简单的
public class ExtBeanWrapper {
public ExtBeanWrapper() {
};
public ExtBeanWrapper(Object entity) {
this.setObj(entity);
}
private Map innerMap = new HashMap();
public Map getInnerMap() {
return innerMap;
}
public void setInnerMap(Map innerMap) {
this.innerMap = innerMap;
}
public void setObj(Object entity) {
if (entity == null) {
innerMap = null;
}
JSON jobj = (JSON) JSON.toJSON(entity);
innerMap = JSON.toJavaObject(jobj, Map.class);
}
public Object getObj() {
if (innerMap == null) {
return null;
}
JSON jobj = (JSON) JSON.toJSON(innerMap);
Map entity = JSON.toJavaObject(jobj, Map.class);
return entity;
}
public Object getObj(Class targetClass) {
if (innerMap == null) {
return null;
}
JSON jobj = (JSON) JSON.toJSON(innerMap);
Object entity = JSON.toJavaObject(jobj, targetClass);
return entity;
}
}MappedTypes(com.nh.micro.ext.ExtBeanWrapper.class)
@MappedJdbcTypes(JdbcType.VARCHAR)
public class TagToJsonTypeHandler extends BaseTypeHandler<ExtBeanWrapper> {
private Map jsonToMap(String value) {
if (value == null || "".equals(value)) {
return Collections.emptyMap();
} else {
return JSON.parseObject(value, new TypeReference<Map<String, Object>>() {
});
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, ExtBeanWrapper parameter, JdbcType jdbcType)
throws SQLException {
ps.setString(i, JSON.toJSONString(parameter.getInnerMap()));
}
public boolean isJson(String value){
if(value==null || "".equals(value)){
return false;
}else{
if(value.startsWith("{")){
return true;
}
}
return false;
}
@Override
public ExtBeanWrapper getNullableResult(ResultSet rs, String columnName) throws SQLException {
String value=rs.getString(columnName);
Map innerMap=jsonToMap(value);
ExtBeanWrapper extBeanTag=new ExtBeanWrapper();
extBeanTag.setInnerMap(innerMap);
return extBeanTag;
}
@Override
public ExtBeanWrapper getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String value=rs.getString(columnIndex);
Map innerMap=jsonToMap(value);
ExtBeanWrapper extBeanTag=new ExtBeanWrapper();
extBeanTag.setInnerMap(innerMap);
return extBeanTag;
}
@Override
public ExtBeanWrapper getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String value=cs.getString(columnIndex);
Map innerMap=jsonToMap(value);
ExtBeanWrapper extBeanTag=new ExtBeanWrapper();
extBeanTag.setInnerMap(innerMap);
return extBeanTag;
}
}现在我们来看一下如何将该Json字段从数据库取出，还是以上面的案例为例，先在mapper文件中定义一组resultMap
<resultMap id="productMap" type="com.cloud.productprovider.composite.ProviderProduct">
<id property="code" column="code" />
<result property="details" column="details" />
<association property="product" javaType="com.cloud.model.productprovider.Product"
column="id">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="model" column="model" />
<result property="productImgs" column="product_imgs" />
<association property="price" javaType="com.cloud.model.serviceprovider.Price">
<id property="normalPrice" column="normal_price" />
<result property="secKillPrice" column="seckill_price" />
<result property="begin" column="price_begin" typeHandler="com.cloud.productprovider.untils.BoolIntTypeHandler" />
</association>
</association>
<association property="brand" javaType="com.cloud.model.productprovider.Brand" column="brand_id" select="findBrandById" />
<association property="levelName" column="level_id" javaType="java.lang.String" select="findLevelNameById" />
<association property="provider" column="default_provider_id" javaType="com.cloud.productprovider.composite.DefaultProvider"
select="findProviderById" />
<association property="otherValues" javaType="com.nh.micro.ext.ExtBeanWrapper" column="other_property_value">
<id property="entry" column="other_property_value" jdbcType="VARCHAR" typeHandler="com.nh.micro.ext.th.TagToJsonTypeHandler" />
</association>
</resultMap>这里稍微解释一下，price里的begin是boolean类型，price_begin在数据库中是整形，有一个转换器，代码如下
public class BoolIntTypeHandler extends BaseTypeHandler<Boolean> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Boolean parameter, JdbcType jdbcType) throws SQLException {
ps.setBoolean(i,parameter);
}
@Override
public Boolean getNullableResult(ResultSet rs, String columnName) throws SQLException {
int value = rs.getInt(columnName);
if (rs.wasNull()) {
return false;
}else {
if (value == 0) {
return false;
}else if (value == 1) {
return true;
}
}
return false;
}
@Override
public Boolean getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
int value = rs.getInt(columnIndex);
if (rs.wasNull()) {
return false;
}else {
if (value == 0) {
return false;
}else if (value == 1) {
return true;
}
}
return false;
}
@Override
public Boolean getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
int value = cs.getInt(columnIndex);
if (cs.wasNull()) {
return false;
}else {
if (value == 0) {
return false;
}else if (value == 1) {
return true;
}
}
return false;
}
}品牌这里有一个查找
<select id="findBrandById" parameterType="java.lang.Long" resultType="com.cloud.model.productprovider.Brand">
select id,code,name,sort,log_url logoUrl from brand
<where>
id=#{brand_id}
</where>
</select>配件二级分类名称
<select id="findLevelNameById" parameterType="java.lang.Long" resultType="java.lang.String">
select name from product_level
<where>
id=#{level_id}
</where>
</select>配件商信息
<resultMap id="defaultProviderMap" type="com.cloud.productprovider.composite.DefaultProvider">
<id property="code" column="code" />
<association property="productProvider" javaType="com.cloud.model.productprovider.ProductProvider">
<id property="id" column="id" />
<result property="name" column="name" />
</association>
</resultMap><select id="findProviderById" parameterType="java.lang.Long" resultMap="defaultProviderMap" resultType="com.cloud.productprovider.composite.DefaultProvider">
select a.id,a.name,b.code from product_provider a
inner join default_provider b on a.id=b.id
<where>
a.id=#{default_provider_id}
</where>
</select>当然我们的重点还是otherValues这里
<association property="otherValues" javaType="com.nh.micro.ext.ExtBeanWrapper" column="other_property_value">
<id property="entry" column="other_property_value" jdbcType="VARCHAR" typeHandler="com.nh.micro.ext.th.TagToJsonTypeHandler" />
</association>获取数据的全部select代码如下
<select id="findProductById" parameterType="java.lang.Long" resultMap="productMap"
resultType="com.cloud.productprovider.composite.ProviderProduct">
select id,code,name,model,brand_id,normal_price,level_id,default_provider_id,other_property_value
from product
<where>
id=#{id}
</where>
</select>获取出来的数据如下
{ "code": 200, "data": { "brand": { "code": "001", "id": 1, "logoUrl": "http://123.456.789", "name": "飞利浦", "sort": 1 }, "code": "0001", "levelName": "高级项链", "otherValues": { "innerMap": { "2459623566996411192": "国际", "2459623566996408120": "10", "2459623566996409144": "呼和浩特", "2459623566996410168": "飞利浦", "2459623566996412216": "包邮" }, "obj": { "2459623566996410168": "飞利浦", "2459623566996411192": "国际", "2459623566996408120": "10", "2459623566996409144": "呼和浩特", "2459623566996412216": "包邮" } }, "product": { "id": 2459722970793247544, "model": "AAAAA", "name": "AAAA", "onShelf": false, "price": { "begin": false, "normalPrice": 199 } }, "provider": { "code": "0001", "productProvider": { "id": 2459698718186668856, "name": "大众4S店", "productList": [] }, "status": false } }, "msg": "操作成功" }
当然我们这里要把其他属性的id替换成用户能看懂的其他属性的名称
@Override
public Provider findProduct(Long id) {
ProductDao productDao = SpringBootUtil.getBean(ProductDao.class);
OtherPropertyDao otherPropertyDao = SpringBootUtil.getBean(OtherPropertyDao.class);
Provider product = productDao.findProductById(id);
Map map = ((ProviderProduct) product).getOtherValues().getInnerMap();
Map<String,String> insteadMap = new HashMap<>();
for (Object key : map.keySet()) {
log.info("键名为：" + String.valueOf(key));
String name = otherPropertyDao.findNameById(Long.parseLong(String.valueOf(key)));
insteadMap.put(name,(String) map.get(key));
}
((ProviderProduct) product).getOtherValues().setObj(insteadMap);
return product;
}最后我们获取的结果为
{ "code": 200, "data": { "brand": { "code": "001", "id": 1, "logoUrl": "http://123.456.789", "name": "飞利浦", "sort": 1 }, "code": "0001", "levelName": "高级项链", "otherValues": { "innerMap": { "商品等级": "国际", "运费设置": "包邮", "生产厂家": "飞利浦", "包装规格": "10", "商品产地": "呼和浩特" }, "obj": { "$ref": "$.data.otherValues.innerMap" } }, "product": { "id": 2459722970793247544, "model": "AAAAA", "name": "AAAA", "onShelf": false, "price": { "begin": false, "normalPrice": 199 } }, "provider": { "code": "0001", "productProvider": { "id": 2459698718186668856, "name": "大众4S店", "productList": [] }, "status": false } }, "msg": "操作成功" }