MyBatis与数据库字段交互
基础mysql类型与MyBatis数据进行交互
JavaType | JDBCType | mysql |
---|---|---|
Char | CHAR | char |
String | VARCHAR | varchar |
int | INTEGER | int |
float | FLOAT | float |
Long | BITINT | bitint |
double | DOUBLE | double |
Date | TIMESTAMP | datetime |
LocalDateTime | TIMESTAMP | datetime |
除去这些基本的字段对应,如果我们有一个JSONObject属性的字段需要与数据库中json格式的数据进行交互,此时我们就需要新创建一个类,将数据库中json格式的对象映射为Java对象,也可以使用Mybatis对JSONObject对象进行映射,将其在数据库中以json格式保存。
交互操作
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
@Data
public class TestJsonDO {
private Long id;
private JSONObject jsonObject;
}
1. 导入fastjson包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
2. 编写JSONTypeHandler类
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JsonTypeHandler<T> extends BaseTypeHandler<T> {
private static final ObjectMapper mapper = new ObjectMapper();
private Class<T> clazz;
public JsonTypeHandler(Class<T> clazz) {
if (clazz == null) throw new IllegalArgumentException("Type argument cannot be null");
this.clazz = clazz;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, this.toJson(parameter));
}
@Override
public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
return this.toObject(rs.getString(columnName), clazz);
}
@Override
public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return this.toObject(rs.getString(columnIndex), clazz);
}
@Override
public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return this.toObject(cs.getString(columnIndex), clazz);
}
private String toJson(T object) {
try {
return mapper.writeValueAsString(object);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private T toObject(String content, Class<?> clazz) {
if (content != null && !content.isEmpty()) {
try {
return (T) mapper.readValue(content, clazz);
} catch (Exception e) {
throw new RuntimeException(e);
}
} else {
return null;
}
}
}
3. 在mybatis映射中加入调用类并编写insert方法
<resultMap id="TestJsonMapper" type="com.xxx.xxx.xxx.dao.DO.TestJsonDO">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="json_object" property="jsonObject" javaType="com.alibaba.fastjson.JSONObject" typeHandler="com.xxx.xxx.xxx.dao.jsonHandler.JsonTypeHandler"/>
</resultMap>
<insert id="insert" parameterType="com.xxx.xxx.xxx.dao.DO.TestJsonDO">
insert into `test` (`id`, `json_object`)
values (#{id,jdbcType=BIGINT}, #{jsonObject,typeHandler=com.xunxu.cloudcontrol.admin.dao.jsonHandler.JsonTypeHandler});
</insert>