这篇文章主要介绍Mybatis中怎么样操作mysql8的Json字段类型，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！
Json字段是从mysql 5.7起加进来的全新的字段类型，现在我们看看在什么情况下使用该字段类型，以及用mybatis如何操作该字段类型
一般来说，在不知道字段的具体数量的时候，使用该字段是非常合适的，比如说——商品的无限属性。
现在我们来假设这么一个场景，在商品的二级分类中给商品定义足够多的属性，我们先设计属性的类
/** * 商品自定义属性 */@NoArgsConstructor@AllArgsConstructorpublic 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@NoArgsConstructorpublic class ProviderProductLevel implements Provider,Serializable
其中包含一个商品属性对象的列表
@Getter@Setterprivate 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@NoArgsConstructorpublic 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替换成用户能看懂的其他属性的名称
@Overridepublic 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": "操作成功"
}以上是“Mybatis中怎么样操作mysql8的Json字段类型”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注亿速云行业资讯频道！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
