Pg的连接配置加参数(stringtype=unspecified)踩坑说明

背景

一个删除操作异常引发的惊天血案

问题分析

在 PostgreSQL 中，当使用 stringtype=unspecified 参数时，
JDBC 驱动程序会将 Java 的 String 类型参数以 unknown 类型（而非默认的 varchar）传递给数据库。
如果 SQL 语句中参数的类型无法被 PostgreSQL 隐式推断，就会抛出 could not determine data type of parameter $1 错误。

目前发现业务中涉及到 concat 函数使用会出现异常的情况,样例如下:

delete from favorites where id in (
    select id from favorites 
    where sub_code like concat('%', #{id},'%') 
    and parent_id != 'root'
)

在 配置 (“jdbc:postgresql://X.X.X.18:5432/dbname?stringtype=unspecified”)下，执行会抛出异常could not determine data type of parameter $1

在 配置（“jdbc:postgresql://X.X.X.18:5432/dbname”）下，执行成功。

解决方案

方法 1：强制转换参数类型 或者函数包装 （使用参数 stringtype=unspecified）

在 SQL 中直接使用 cast 或 COALESCE() 将参数转换为字符串类型：

delete from favorites where id in (
    select id from favorites 
    where sub_code like concat('%', cast(#{id} as varchar), '%') 
    and parent_id != 'root'
)

或：

   delete from favorites where id in
        (select id from favorites where sub_code like concat('%', COALESCE(#{id}, ''),'%') and parent_id != 'root'
        )

或：

   delete from favorites where id in
        (select id from favorites where sub_code like ('%'|| #{id} ||'%') and parent_id != 'root'
        )

方法 2：使用jdbc:postgresql://X.X.X.18:5432/dbname配置不加stringtype=unspecified参数

更改项目的 ObjectJsonHandler 使用 PGobject对象进行解析可以处理jsonb报错问题。代替原来的 ObjectMapper
旧写法废弃

@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
public class ObjectJsonHandler<T> extends BaseTypeHandler<T> {
    private static final ObjectMapper objectMapper;
    private final Class<T> type;

    static {
        objectMapper = new ObjectMapper();
        objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
    }

    public ObjectJsonHandler(Class<T> type) {
        if (log.isTraceEnabled()) {
            log.trace("JacksonTypeHandler(" + type + ")");
        }
        if (null == type) {
            throw new PersistenceException("Type argument cannot be null");
        }
        this.type = type;
    }

    private T parse(String json) {
        if (json == null || json.isEmpty()) {
            return null;
        }
        return JSON.parseObject(json, type);
    }

    private String toJsonString(T obj) {
        try {
            return objectMapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getString(columnName));
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getString(columnIndex));
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parse(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int columnIndex, T parameter, JdbcType jdbcType)
            throws SQLException {
        ps.setString(columnIndex, toJsonString(parameter));
    }
}

---

新写法：

public class JSONTypePgHandler<T extends Object> extends BaseTypeHandler<T> {
    private static final PGobject jsonObject = new PGobject();
    private Class<T> clazz;

    public JSONTypePgHandler(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;
        }
    }
}

总结

• 关键点：stringtype=unspecified 禁用了 JDBC 的类型映射，导致参数类型丢失。
• 触发场景：在需要明确类型的上下文（如函数参数、运算符）中使用未指定类型的参数。目前发现concat 函数存在问题
• 解决思路：推荐使用2方案