The function I have been working on recently, because there are values in other databases, needs to be queried from different entrances here, so it needs to be synchronized. It would be inconvenient to continue to generate corresponding columns one by one. If there is no other operation, just store and Query, then it can be stored in the json format supported by MySql.
MySql’s json can only be processed after 5.7, so the version must be this or higher than this!
First of all, in the first step, we need to define a json type processing class, which can be called BaseAttributeTypeHandler to inherit the BaseTypeHandler class of ibatis. The type must be defined and passed in parameters later.
package xx;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.fasterxml.jackson.databind.JavaType;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.type.TypeFactory;
import io.lettuce.core.dynamic.support.ResolvableType;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springframework.util.Assert;
import java.io.IOException;
import java.lang.reflect.Type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Set;
/**
* @Author df
* @Description: Basic class, processing mySql field as json type
* @Date 2023/10/19 9:52
*/
public abstract class BaseAttributeTypeHandler<T> extends BaseTypeHandler<Object> {
private JavaType javaType;
/**
*ObjectMapper
*/
private static final ObjectMapper OBJECT_MAPPER = new ObjectMapper();
/**
* Construction method
*/
public BaseAttributeTypeHandler() {
// Construct a ResolvableType object through class
ResolvableType resolvableType = ResolvableType.forClass(getClass());
// Get the corresponding generic entity
Type type = resolvableType.as(BaseAttributeTypeHandler.class).getGeneric() != null ?
resolvableType.as(BaseAttributeTypeHandler.class).getGeneric().getType() :
null;
//Construct the java type according to the corresponding type
javaType = constructType(type);
}
private static JavaType constructType(Type type) {
Assert.notNull(type, "[Assertion failed] - type is required; it must not be null");
return TypeFactory.defaultInstance().constructType(type);
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, JSONUtil.toJsonStr(parameter));
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
String value = rs.getString(columnName);
return convertToEntityAttribute(value);
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return convertToEntityAttribute(rs.getString(columnIndex));
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String value = cs.getString(columnIndex);
return convertToEntityAttribute(value);
}
private Object convertToEntityAttribute(String dbData) {
// Determine the type based on the obtained data
if (StrUtil.isEmpty(dbData)) {
if (List.class.isAssignableFrom(javaType.getRawClass())) {
return Collections.emptyList();
} else if (Set.class.isAssignableFrom(javaType.getRawClass())) {
return Collections.emptySet();
} else if (Map.class.isAssignableFrom(javaType.getRawClass())) {
return Collections.emptyMap();
} else {
return null;
}
}
return toObject(dbData, javaType);
}
private static <T> T toObject(String json, JavaType javaType) {
Assert.hasText(json, "[Assertion failed] - this json must have text; it must not be null, empty, or blank");
Assert.notNull(javaType, "[Assertion failed] - javaType is required; it must not be null");
try {
//Set value to object
return (T) OBJECT_MAPPER.readValue(json, javaType);
} catch (com.fasterxml.jackson.core.JsonParseException e) {
throw new RuntimeException(e.getMessage(), e);
} catch (JsonMappingException e) {
throw new RuntimeException(e.getMessage(), e);
} catch (IOException e) {
throw new RuntimeException(e.getMessage(), e);
}
}
}
Define the json type entity class to be stored. If user information is stored, we define UserSportTypeHandler, then inherit the class BaseAttributeTypeHandler just now, and pass in the json entity UserSport (the entity you define to be stored or queried)
public class UserSportTypeHandler extends BaseAttributeTypeHandler<UserSport> {
}
When storing, add the following description to the po entity so that MySql knows it is json. The typeHandler here is defined as UserSportTypeHandler
@Data
public class User {
private Long id;
private String username;
@TableField(jdbcType = JdbcType.OTHER, typeHandler = UserSportTypeHandler.class)
private UserSport userSport;
}
New
Just call the mybatis plug-in and save it directly.
public class UserBusinessImpl extends ServiceImpl<UserMapper, User> implements UserBusiness {
public Boolean saveSportDataBatch(List<User> users) {
super.saveBatch(users);
}
}
After testing, it works. It stores json data directly.
Of course, you can also store lists and list
public class UserSportTypeHandler extends BaseAttributeTypeHandler<List<UserSport>> {
}
You can also add it like this in mapper.xml, and also specify typeHadler
<insert id="saveBatch" parameterType="java.util.List">
INSERT INTO user_sport_record (user_sport)
<foreach collection="list" separator="," item="item">
VALUES (
#{item.userSport,javaType=com.uniigym.Uniiuser.infrastructure.po.HeartDistribute,typeHandler=HeartDistributeTypeHandler,jdbcType=OTHER});
</foreach>
</insert>
Query
Of course, if you write it in mapper yourself, you can write it under resultMap. Example:
<resultMap type="xx.po.User" id="user">
<result property="id" column="id" jdbcType="INTEGER"/>
<!-- json needs to be configured as follows to query the data -->
<result typeHandler="com.uniigym.Uniiuser.common.config.mybatis.HeartDistributeTypeHandler"
property="heartDistribute" column="heart_distribute"
jdbcType="OTHER" javaType="com.uniigym.Uniiuser.infrastructure.po.HeartDistribute"/>
</resultMap>
This can be used for both query and addition, but the id of the resultMap must be specified in SQL. However, if @TableField(jdbcType = JdbcType.OTHER, typeHandler = UserSportTypeHandler.class) is written on the entity, there is no need to define the ResultMap tag. If typeHandler is not configured, the field query about json will be empty, and it can only be processed after querying and saving.