依赖如下：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.5.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>2.0.36</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-annotation</artifactId>
<version>3.5.2</version>
</dependency>
UUIDTypeHandler
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.UUID;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeHandler;
@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(UUID.class)
public class UUIDTypeHandler implements TypeHandler<UUID> {
@Override
public void setParameter(PreparedStatement ps, int i, UUID parameter, JdbcType jdbcType) throws SQLException {
ps.setObject(i, parameter);
}
@Override
public UUID getResult(ResultSet rs, String columnName) throws SQLException {
return this.converter(rs.getObject(columnName));
}
@Override
public UUID getResult(ResultSet rs, int columnIndex) throws SQLException {
return this.converter(rs.getObject(columnIndex));
}
@Override
public UUID getResult(CallableStatement cs, int columnIndex) throws SQLException {
return this.converter(cs.getObject(columnIndex));
}
private UUID converter(Object object) {
if (object == null)
return null;
return UUID.fromString(object.toString());
}
}
数据库中uuid对应的sid，在java实体类中，用String声明：
数据库中city_list字段为jsonb属性
数据库中content字段为text属性
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
private String sid ;
private String name ;
private String address ;
private List<City> cityList;
private JSONObject content;
}
对应的Mapper如下：
<resultMap id="BaseResultMap" type="com.demo.domain.Student">
<id property="sid" column="sid" jdbcType="OTHER" typeHandler="com.demo.conf.UUIDTypeHandler"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="address" column="address" jdbcType="VARCHAR"/>
<result property="cityList" column="city_list" jdbcType="OTHER" typeHandler="com.demo.conf.JsonbTypeHandler"/>
<result property="content" column="content" jdbcType="OTHER" typeHandler="com.demo.conf.JsonTypeHandler"/>
</resultMap>
jdbc连接中加入&stringtype=unspecified
spring:
datasource:
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5432/demo01?characterEncoding=utf8&stringtype=unspecified&connectTimeout=1000&socketTimeout=3000&autoReconnect=true&useUnicode=true&useSSL=false&TimeZone=Asia/Shanghai
username: postgres
password: 123456
JsonbTypeHandler
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Description //TODO pgSql jsonb 数据处理器
**/
@MappedTypes({Object.class})
@MappedJdbcTypes({JdbcType.OTHER})
public class JsonbTypeHandler extends AbstractJsonTypeHandler<Object> {
private static final PGobject jsonObject = new PGobject();
private final Class<?> type;
public JsonbTypeHandler(Class<?> type) {
this.type = type;
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
if (ps != null) {
jsonObject.setType("jsonb");
jsonObject.setValue(JSON.toJSONString(parameter));
ps.setObject(i, jsonObject);
}
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
Object v = rs.getObject(columnName);
return toFill(v);
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
Object v = rs.getObject(columnIndex);
return toFill(v);
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
Object v = cs.getObject(columnIndex);
return toFill(v);
}
@Override
protected Object parse(String json) {
return JSON.parseObject(json, this.type);
}
//必须将v转成 PGObject处理
private Object toFill(Object v) {
if (v != null && v instanceof PGobject) {
PGobject p = (PGobject) v;
String pv = p.getValue();
if (StringUtils.isNotEmpty(pv) && ("jsonb".equals(p.getType()) || "json".equals(p.getType()))) {
return parse(p.getValue());
}
}
return v;
}
@Override
protected String toJson(Object obj) {
return JSON.toJSONString(obj, SerializerFeature.WriteMapNullValue, SerializerFeature.WriteNullListAsEmpty, SerializerFeature.WriteNullStringAsEmpty);
}
}
存储JSONObject对象
定义 JsonTypeHandler
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@MappedJdbcTypes(JdbcType.OTHER)
@MappedTypes(JSONObject.class)
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;
}
}
}
使用
@Autowired
private StudentService stuentService;
public void test(){
Student s1 = new Student()
s1.setSid(UUID.randomUUID().toString());
s1.setName("zhang san");
JSONObject item = new JSONObject();
item.put("name","zhang san");
item.put("age",23);
item.put("address","北京");
s1 .setContent(item);
...
stuentService.save(s1);
}
网友评论