MyBatis+PostgreSQL
postgrel中存在json和数组类型的字段,而mybatis原生并不支持这种类型(即jdbcType不存在JSON或者数组类型),如果想要将json或者数组格式的数据插入到pg数据库,那么mybatis提供了BaseTypeHandler已供开发者自己扩展,开发者需要根据自己的业务实现(implements)或者继承(extends)BaseTypeHandler。
下面将展示把List<String> 和 JSONObject类型(java)的数据插入到pg数据库。
1.Array
1.1.typeHandler
package com.hikvision.idatafusion.dDevcenter.common.typeHandler;
import org.apache.ibatis.executor.result.ResultMapException;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;
import org.apache.ibatis.type.TypeHandler;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
public class ArrayTypeHandlerPg implements TypeHandler<List<?>> {
@Override
public void setParameter(PreparedStatement ps, int i, List<?> parameter, JdbcType jdbcType) throws SQLException {
if (parameter == null) {
try {
ps.setNull(i, JdbcType.ARRAY.TYPE_CODE);
} catch (SQLException e) {
throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . "
+ "Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. "
+ "Cause: " + e, e);
}
} else {
try {
ps.setArray(i, ps.getConnection().createArrayOf(jdbcType.name(), parameter.toArray()));
} catch (Exception e) {
throw new TypeException("Error setting non null for parameter #" + i + " with JdbcType " + jdbcType
+ " . "
+ "Try setting a different JdbcType for this parameter or a different configuration property. "
+ "Cause: " + e, e);
}
}
}
@Override
public List<?> getResult(ResultSet rs, String columnName) throws SQLException {
List<?> result;
try {
Array array = rs.getArray(columnName);
result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
} catch (Exception e) {
throw new ResultMapException(
"Error attempting to get column '" + columnName + "' from result list. Cause: " + e, e);
}
if (rs.wasNull()) {
return null;
} else {
return result;
}
}
@Override
public List<?> getResult(ResultSet rs, int columnIndex) throws SQLException {
List<?> result;
try {
Array array = rs.getArray(columnIndex);
result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
} catch (Exception e) {
throw new ResultMapException(
"Error attempting to get column #" + columnIndex + " from result list. Cause: " + e, e);
}
if (rs.wasNull()) {
return null;
} else {
return result;
}
}
@Override
public List<?> getResult(CallableStatement cs, int columnIndex) throws SQLException {
List<?> result;
try {
Array array = cs.getArray(columnIndex);
result = array == null ? null : new ArrayList<>(Arrays.asList((Object[]) array.getArray()));
} catch (Exception e) {
throw new ResultMapException(
"Error attempting to get column #" + columnIndex + " from callable statement. Cause: " + e, e);
}
if (cs.wasNull()) {
return null;
} else {
return result;
}
}
}
1.2.mapper.xml
<!--1.插入数据: 其中字段next_users的类型为(_varchar:字符串数组)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="com.qin.entity.ApplyInfo">
INSERT INTO APPLY_INFO (
<trim prefixOverrides=",">
<if test="nextUsers!=null">,next_users</if>
</trim>
)
VALUES
(
<trim prefixOverrides=",">
<if test="nextUsers!=null">
,#{nextUsers,jdbcType=VARCHAR,typeHandler=com.qin.common.typeHandler.ArrayTypeHandlerPg}
</if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="com.qin.entity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="next_users" property="nextUsers" jdbcType="VARCHAR"
typeHandler="com.qin.common.typeHandler.ArrayTypeHandlerPg"/>
</resultMap>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
next_users
FROM APPLY_INFO
WHERE id = #{id}
</select>
<!--3.查询数据:数组中是否包某个字符串元素-->
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
next_users
FROM APPLY_INFO
WHERE next_users @> array[#{userName}] ::_varchar
</select>
注意事项:
- 如果想要把pg数据库表中的字符串数组字段的值映射到java的pojo对象中,必须使用resultMap标签来映射,不然此字段的值为null。
2.Json
2.1.typeHandler
package com.qin.common.typeHandler;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.postgresql.util.PGobject;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JSONTypeHandlerPg<T extends Object> extends BaseTypeHandler<T> {
private static final PGobject jsonObject = new PGobject();
private Class<T> clazz;
public JSONTypeHandlerPg(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 {
jsonObject.setType("json");
jsonObject.setValue(this.toJson(parameter));
ps.setObject(i, jsonObject);
}
@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 JSON.toJSONString(object, SerializerFeature.WriteNullListAsEmpty);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
private T toObject(String content, Class<?> clazz) {
if (content != null && !content.isEmpty()) {
try {
return (T) JSON.parseObject(content,clazz);
} catch (Exception e) {
throw new RuntimeException(e);
}
} else {
return null;
}
}
}
2.2.mapper.xml
<!--1.插入数据: 其中字段url的类型为(json:json格式)-->
<insert id="insert" useGeneratedKeys="true" keyProperty="id"
parameterType="com.qin.entity.ApplyInfo">
INSERT INTO APPLY_INFO (
<trim prefixOverrides=",">
<if test="url!=null">,url</if>
</trim>
)
VALUES
(
<trim prefixOverrides=",">
<if test="url!=null">
,#{nextUsers,jdbcType=OTHER,typeHandler=com.qin.common.typeHandler.JSONTypeHandlerPg}
</if>
</trim>
)
</insert>
<!--2.查询数据-->
<resultMap id="BaseResultMap" type="com.qin.entity.ProcessInfo">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="url" property="nextUsers" jdbcType="OTHER"
typeHandler="com.qin.common.typeHandler.JSONTypeHandlerPg"/>
</resultMap>
<select id="getById" parameterType="java.lang.Long" resultMap="BaseResultMap">
SELECT
id,
url
FROM APPLY_INFO
WHERE id = #{id}
</select>
注意事项:
- 如果想要把pg数据库表中的json字段类型的值映射到java的pojo对象中,必须使用resultMap标签来映射,不然此字段的值为null。