Shulou(Shulou.com)06/02 Report--

This article mainly introduces how to operate the Json field type of mysql8 in Mybatis, which is very detailed and has certain reference value. Friends who are interested must read it!

The Json field is a new field type that has been added since mysql 5.7. now let's take a look at when this field type is used and how to manipulate it with mybatis

In general, it is appropriate to use this field when you do not know the exact number of fields, such as the infinite properties of goods.

Now let's assume a scenario in which enough attributes are defined for goods in the secondary classification of goods, so let's first design the class of attributes.

/ * NoArgsConstructor@AllArgsConstructorpublic class OtherProperty implements Serializable {@ Getter @ Setter private Long id; / / attribute id @ Getter @ Setter private FormType formType; / / the form type used at the front end @ Getter @ Setter private String name; / / attribute name @ Getter @ Setter private String unit; / / Unit @ Getter @ Setter private String values / / optional values are separated by @, such as accessories @ Getter private List valueList = new ArrayList (); / / A list of optional values @ Getter @ Setter private String defaultValue; / / the default value @ Getter @ Setter private boolean search; / / can you search @ Getter @ Setter private boolean mustWrite; / / whether @ Getter @ Setter private Boolean used = false is required / / if this attribute has been used in a commodity, it is not allowed to modify 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;}}

Where formType is an enumerated type

Public enum FormType implements Localisable {TYPE1 ("text box"), TYPE2 ("drop-down box"), TYPE3 ("checkbox"), TYPE4 ("check box"), TYPE5 ("multiline text box"); private String value; private FormType (String value) {this.value = value;} @ Override public String getValue () {return this.value;}}

Let's take a look at some of the codes for commodity classification.

@ AllArgsConstructor@NoArgsConstructorpublic class ProviderProductLevel implements Provider,Serializable

It contains a list of commodity attribute objects

@ Getter@Setterprivate List otherProperties

Some of the operation source codes are as follows

/ * * find all other attributes contained in it through the secondary accessory category id * @ param * @ return * / public List findOtherProperties () {if (this.level = = 2) {LevelDao levelDao = SpringBootUtil.getBean (LevelDao.class); String ids = levelDao.findIdsByLevel2Id (this.id); return levelDao.findOtherProperties (ids);} return null } / * remove 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 idsList = Arrays.asList (idsArray); List contentIdsList = new ArrayList () ContentIdsList.addAll (idsList); String [] paramArray = paramIds.split (","); List 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 } / * show all other attributes of a secondary accessory category * @ param id * @ return * / @ SuppressWarnings ("unchecked") @ Transactional@GetMapping ("/ productprovider-anon/showproperties") public Result showOtherProperties (@ RequestParam ("id") Long id) {Provider level2 = levelDao.findLevel2 (id); return Result.success (ProviderProductLevel) level2). FindOtherProperties ()) } / * modify other attributes of a secondary accessory category * @ param id * @ param otherProperties * @ return * / @ SuppressWarnings ("unchecked") @ Transactional@PostMapping ("/ productprovider-anon/changeother") public Result changeOtherProperties (@ RequestParam ("id") Long id,@RequestBody List otherProperties) {/ / get the accessory secondary classification object Provider level2 = levelDao.findLevel2 (id) / / get other attributes of the secondary category of unused accessories (no item has ever used this attribute) List unUsedList = Optional.ofNullable (ProviderProductLevel) level2) .getOtherProperties () .map (otherProperties1-> otherProperties1.stream ()) .orElse (new ArrayList (). Stream ()) .filter (otherProperty->! otherProperty.getUsed ()) .filter (Collectors.toList ()) / / get other attributes List usedIdList = Optional.ofNullable (ProviderProductLevel) level2). GetOtherProperties ()) .map (otherProperties1-> otherProperties1.stream ()) .orElse (new ArrayList (). Stream ()) .filter (otherProperty-> otherProperty.getUsed ()) .map (OtherProperty::getId) .filter (Collectors.toList ()) / / other attributes that have not been modified or used are checked in the secondary classification of accessories passed back, and only other attributes that have been modified and not used are stored. Otherwise, List 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 () .filter (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) } / / get the additional attributes and append them to the other attributes in the secondary category of accessories List newList = otherProperties.stream (). Filter (otherProperty->! Optional.ofNullable (otherProperty.getId ()). IsPresent ()) .peek (otherProperty-> otherProperty.setId (idService.genId () .properties (Collectors.toList ()); ((ProviderProductLevel) level2) .addOtherProperties (newList); return Result.success ("modified successfully");}

After some additions, deletions, modifications and queries, the data in the database is roughly as follows

The results of our search for all the properties of the advanced necklace are as follows

Now we are going to add commodities to the category that belongs to this commodity, and the definition of commodity category is roughly as follows

@ Data@NoArgsConstructorpublic class ProviderProduct implements Provider {private Product product; / / Accessories Meta Information object private String code; / / Accessories Code private Brand brand; / / Brand private String details; / / Accessories description private String levelName; / / Secondary accessory Category name private DefaultProvider provider; / / Accessories manufacturer private ExtBeanWrapper otherValues; / / other attribute collections}

The field corresponding to the attribute list is otherValues, which is the Json field type mapping that we want to store in the database.

The database table structure of the commodity is as follows

To use mybatis data to convert Json field types, you can first refer to a converter written online, or you can write it yourself.

Pom

Com.github.jeffreyning extcol 0.0.1-RELEASE

Add type-handlers-package: com.nh.micro.ext.th to the configuration file

Mybatis: type-aliases-package: com.cloud.model.productprovider type-handlers-package: com.nh.micro.ext.th mapper-locations: classpath:/mybatis-mappers/* configuration: mapUnderscoreToCamelCase: true

Write an insert statement in the mapper file

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}, 1 0, # {product.productImgs}, # {details}, # {brand.id}, # {otherValues JdbcType=VARCHAR})

For each custom attribute of the commodity category, we can first get the id of the custom attribute, and then insert the id with the value as the key value pair

{

"product": {

"name": "AAAA"

"model": "AAAAA"

"price": {

NormalPrice: 199

"begin": false

}

"productImgs": "http://123.433.567.988""

}

"code": "0001"

Details ":"

"

"brand": {

"id": 1

"name": "Philips"

}

"otherValues": {

"innerMap": {

"2459623566996408120": "10"

"2459623566996409144": "Hohhot"

2459623566996410168: Philips

"2459623566996411192": "International"

"2459623566996412216": "Free delivery"

}

}

}

After execution, the data in the database is as follows

The source code of the data class and converter of the plug-in is as follows, which is actually very simple.

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 {private Map jsonToMap (String value) {if (value = = null | | ".equals (value)) {return Collections.emptyMap ();} else {return JSON.parseObject (value, new TypeReference () {}) } @ 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;}}

Now let's take a look at how to get the Json field out of the database, or take the above example as an example, first define a set of resultMap in the mapper file

To explain a little bit, begin in price is of type boolean, price_begin is shaping in the database, and there is a converter, as shown in the following code

Public class BoolIntTypeHandler extends BaseTypeHandler {@ Override public void setNonNullParameter (PreparedStatement ps, int I, Boolean parameter, JdbcType jdbcType) throws SQLException {ps.setBoolean (iForce 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;}}

Here is a search for the brand.

Select id,code,name,sort,log_url logoUrl from brand id=# {brand_id}

Secondary classification name of fittings

Select name from product_level id=# {level_id}

Accessory manufacturer information

Select a.id,a.name,b.code from product_provider an inner join default_provider b on a.id=b.id a.id=# {default_provider_id}

Of course, our focus is still on otherValues here.

The full select code to get the data is as follows

Select id,code,name,model,brand_id,normal_price,level_id,default_provider_id,other_property_value from product id=# {id}

The data obtained is as follows

{"code": 200, "data": {"brand": {"code": "001", "id": 1, "logoUrl": "http://123.456.789"," name ":" Philips "," sort ": 1} "code": "0001", "levelName": "Premium necklace", "otherValues": {"innerMap": {"2459623566996411192": "International", "2459623566996408120": "10", "2459623566996409144": "Hohhot" "2459623566996410168": "Philips", "2459623566996412216": "Free delivery"}, "obj": {"2459623566996410168", "2459623566996411192": "International", "2459623566996408120": "10" "2459623566996409144": "Hohhot", "2459623566996412216": "Free delivery"}, "product": {"id": 2459722970793247544, "model": "AAAAA", "name": "AAAA", "onShelf": false "price": {"begin": false, "normalPrice": 199}}, "provider": {"code": "0001", "productProvider": {"id": 2459698718186668856, "name": "Volkswagen 4S store" "productList": []}, "status": false}}, "msg": "Operation successful"}

Of course, we need to replace the id of other attributes with the names of other attributes that the user can understand.

@ 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 insteadMap = new HashMap (); for (Object key: map.keySet ()) {log.info ("key name:" + 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;}

In the end, the result we got is

{"code": 200, "data": {"brand": {"code": "001", "id": 1, "logoUrl": "http://123.456.789"," name ":" Philips "," sort ": 1} "code": "0001", "levelName": "Premium necklace", "otherValues": {"innerMap": {"Commodity Grade": "International", "Freight setting": "Free delivery", "manufacturer": "Philips" "packing specifications": "10", "Origin of goods": "Hohhot"}, "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": "Volkswagen 4S store", "productList": []}, "status": false}} "msg": "Operation succeeded"} these are all the contents of the article "how to manipulate the Json field type of mysql8 in Mybatis" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.