1.自定义typeHandler类型
mybatis默认是没有实现jsonb类型字段对应的TypeHandler,所以一般我们需要自定义mybatis的TypeHandler的一个简单实现:
1、jsonarray数组json:
public class JSONArrayHandler extends BaseTypeHandler<JSONArray> {
public JSONArray delResult(String jsonSource) throws SQLException {
if(jsonSource != null){
JSONArray jsonArray;
try{
jsonArray = JSONArray.parseArray(jsonSource);
}catch (JSONException ex){
throw new SQLException("There is an error converting JSONArray to json format for the content:" + jsonSource);
}
return jsonArray;
}
return null;
}
@Override
public void setNonNullParameter(PreparedStatement ps,
int i,
JSONArray parameter, //需要转换的类型,JSON类型
JdbcType jdbcType) throws SQLException {
ps.setString(i,parameter.toJSONString());
}
@Override
public JSONArray getNullableResult(ResultSet rs, String columnName) throws SQLException {
return delResult(rs.getString(columnName));
}
@Override
public JSONArray getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return delResult(rs.getString(columnIndex));
}
@Override
public JSONArray getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return delResult(cs.getString(columnIndex));
}
}
2、jsonObjectd对象:
**
* JSON格式
*
*/
public class JSONTypeHandler extends BaseTypeHandler<JSONObject> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, JSONObject parameter, JdbcType jdbcType)
throws SQLException {
ps.setString(i, (parameter == null ? null : parameter.toJSONString()));
}
@Override
public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
String value = rs.getString(columnName);
if(rs.wasNull()) {
return null;
}
return StringUtils.isEmpty(value) ? null : JSONObject.parseObject(value);
}
@Override
public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String value = rs.getString(columnIndex);
if(rs.wasNull()) {
return null;
}
return StringUtils.isEmpty(value) ? null : JSONObject.parseObject(value);
}
@Override
public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String value = cs.getString(columnIndex);
return StringUtils.isEmpty(value) ? null : JSONObject.parseObject(value);
}
}
在使用mybatis的xml文件形式select时:使用typeHandler选择返回数据类型
@ColumnType(typeHandler = JSONArrayHandler.class)
private JSONArray gender;
@ColumnType(typeHandler = JSONTypeHandler.class)
private JSONObject excludedConvertedAudience;
<resultMap id="selectMap" type="com.ads.model.entity.audience">
<result property="excludedConvertedAudience" column="excluded_converted_audience" typeHandler="com.ads.mybatis.handlers.JSONTypeHandler"/>
<result property="gender" column="gender" typeHandler="com.ads.mybatis.handlers.JSONArrayHandler"/>
</resultMap>
<select id="selectList" resultMap="selectMap">
SELECT
*
FROM
list
</select>
在使用mybatis的xml文件形式insert时:需在values时选择其他类型返回数据,并在typeHandler选择返回数据类型
<insert id="insertAndGetId" parameterType="com.ads.model.entity.audience.AdsGdtAudienceConfig" useGeneratedKeys="true" keyProperty="id">
INSERT INTO ads_gdt_audience_config (genderexcluded_converted_audience)
VALUES(#{gender,jdbcType=OTHER,typeHandler=com.ads.mybatis.handlers.JSONArrayHandler},#{excludedCustomAudience,jdbcType=OTHER,typeHandler=com.ads.mybatis.handlers.JSONArrayHandler})
</insert>
数据类型举例:
{
"excludedConvertedAudience": {"conversion_behavior_list":["OPTIMIZATIONGOAL_APP_ACTIVATE"],"excluded_dimension":"EXCLUDED_DIMENSION_APP"},
"gender": ["MALE"]
}