Mybatis自動映射Java對象 與 MySQL8后的JSON數(shù)據(jù)

1.轉(zhuǎn)化成為正常Json類型

自認為
優(yōu)點:數(shù)據(jù)庫存儲為單純的數(shù)據(jù)，不需要額外存儲類型
缺點：不夠通用 對于Map、Object、List<T>能用，但是對于List<List<T>>不太行需要寫兩個Typehander不夠優(yōu)雅

接下來，簡單過一下流程（只有插入和查找）

1.1 JsonTypeHander

notice: 使用的轉(zhuǎn)換JSON工具是hutool中的，如果自己有別的轉(zhuǎn)換可以使用別的json轉(zhuǎn)換工具

hutool工具包

<dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.11</version></dependency>

JsonTypeHandler 這個負責(zé)非List，一般存儲的都是Map，Object，和List

public class JsonTypeHandler<T> extends BaseTypeHandler<T>{private Class<T> clazz;//在Mybatis中將類型注入進來public JsonTypeHandler(Class<T> clazz) {this.clazz = clazz;}//寫入數(shù)據(jù)@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {ps.setString(i, JSONUtil.toJsonStr(parameter));}/*** 一般是根據(jù)列名字獲取下方就不再詳述“（因為我也不知道，還沒用到）* @param rs  結(jié)果* @param colName 列名*/@SneakyThrows@Overridepublic  T getNullableResult(ResultSet rs, String colName) {String data = rs.getString(colName);return StrUtil.isBlank(data) ? null : JSONUtil.toBean(data, clazz);}@SneakyThrows@Overridepublic T getNullableResult(ResultSet rs, int colIndex) {String data = rs.getString(colIndex);return StrUtil.isBlank(data) ? null : JSONUtil.toBean(data, clazz);}@SneakyThrows@Overridepublic T getNullableResult(CallableStatement cs, int i) {String data = cs.getString(i);return StrUtil.isBlank(data) ? null : JSONUtil.toBean(data, clazz);}}

1.2 ListJsonTypeHandler 負責(zé)List 類型

public class ListJsonTypeHandler<T> extends BaseTypeHandler<List<T>> implements InitializingBean {private Class<T> clazz;public ListJsonTypeHandler(Class<T> clazz) {this.clazz = clazz;}@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, List<T> parameter, JdbcType jdbcType) throws SQLException {ps.setString(i, JSONUtil.toJsonStr(parameter));}@Overridepublic List<T> getNullableResult(ResultSet rs, String columnName) throws SQLException {String data = rs.getString(columnName);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}@SneakyThrows@Overridepublic List<T> getNullableResult(ResultSet rs, int colIndex) {String data = rs.getString(colIndex);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}@SneakyThrows@Overridepublic List<T> getNullableResult(CallableStatement cs, int i) {String data = cs.getString(i);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}

1.3 實體類

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Json {/*** 唯一標識*/private Integer id;/*** map*/private Map<String,Object> mapJson;/*** 對象* 這個對象自定義的放在下面*/private Object objJson;/*** list集合* <? extends Object>這個我也不知道怎么設(shè)置  但是用這個沒錯*/private List<? extends Object> listJson;
}//測試的對象
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class JsonObj {/*** 唯一id*/private Integer id;/*** 測試所用*/private String str;
}

1.4 mapper

//查找 不建議用 * 代替 我是為啦偷工減料@Select(" SELECT * from json ")@Results(value = {@Result(property = "id", column = "id"),@Result(property = "mapJson", column = "mapJson", typeHandler = JsonTypeHandler.class,javaType = HashMap.class),@Result(property = "objJson", column = "objJson", typeHandler = JsonTypeHandler.class,javaType = JsonObj.class),@Result(property = "listJson", column = "listJson", typeHandler = ListJsonTypeHandler.class,javaType = JsonObj.class)})List<Json> select1();
// 增加@Insert("insert into json( mapJson, objJson, listJson) values (#{mapJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler}," +"#{objJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler}," +"#{listJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler})")int insert1(Json vo);

1.5 測試類

@SpringBootTest
public class ProductTest {
//這個就是剛才放sql的mapper@AutowiredJsonMapper jsonMapper;@Testvoid test11() throws JsonProcessingException {JsonObj js = JsonObj.builder().str("這真的是一個簡單的測試對象").build();Map<String,Object> map =  new HashMap<>();map.put("gaga","亂殺");List<JsonObj> jsonObjs = new ArrayList<>();for (int i = 0; i < 10; i++) {JsonObj js0 = JsonObj.builder().id(i).str("這真的是一個簡單的測試對象").build();jsonObjs.add(js0);}Json build = Json.builder().mapJson(map).listJson(jsonObjs).objJson(js).build();jsonMapper.insert1(build);}@Testvoid test12(){List<Json> select = jsonMapper.select1();System.out.println(select);}}

2. 存儲為攜帶類型的Json

因為在java在編譯的時候是泛型擦除的，所以對于，List里面的類型無法確定，于是便可以把數(shù)據(jù)類型存入數(shù)據(jù)庫之中，并且不需要再指出java類型

實體類還和上面一樣，只需調(diào)整mapper中，對應(yīng)的typeHandler即可

 @Select(" SELECT * from json ")@Results(value = {@Result(property = "id", column = "id"),@Result(property = "mapJson", column = "mapJson", typeHandler = JsonTypeHandler2.class),@Result(property = "objJson", column = "objJson", typeHandler = JsonTypeHandler2.class),@Result(property = "listJson", column = "listJson", typeHandler = JsonTypeHandler2.class)})List<Json> select();@Insert("insert into json( mapJson, objJson, listJson) values (#{mapJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler2}," +"#{objJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler2}," +"#{listJson,typeHandler=com.hb.springredis.typeHandler.JsonTypeHandler2})")int insert(Json vo);

ListJsonTypehander2

public class ListJsonTypeHandler<T> extends BaseTypeHandler<List<T>>{private Class<T> clazz;public ListJsonTypeHandler(Class<T> clazz) {this.clazz = clazz;}@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, List<T> parameter, JdbcType jdbcType) throws SQLException {ps.setString(i, JSONUtil.toJsonStr(parameter));}@Overridepublic List<T> getNullableResult(ResultSet rs, String columnName) throws SQLException {String data = rs.getString(columnName);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}@SneakyThrows@Overridepublic List<T> getNullableResult(ResultSet rs, int colIndex) {String data = rs.getString(colIndex);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}@SneakyThrows@Overridepublic List<T> getNullableResult(CallableStatement cs, int i) {String data = cs.getString(i);return StrUtil.isBlank(data) ? null : JSONUtil.parseArray(data).toList(clazz);}
}

測試類，和上面基本類似

  @Testvoid test() throws JsonProcessingException {JsonObj js = JsonObj.builder().str("這真的是一個簡單的測試對象").build();Map<String,Object> map =  new HashMap<>();map.put("gaga","亂殺");List<JsonObj> jsonObjs = new ArrayList<>();for (int i = 0; i < 10; i++) {JsonObj js0 = JsonObj.builder().id(i).str("這真的是一個簡單的測試對象").build();jsonObjs.add(js0);}Json build = Json.builder().mapJson(map).listJson(jsonObjs).objJson(js).build();jsonMapper.insert(build);}@Testvoid test1(){List<Json> select = jsonMapper.select();System.out.println(select);}