Spring Boot+MyBatis中Oracle多余SQL查询优化求助

背景 #

我在API中使用Oracle、MyBatis和Spring Boot，之前已将Oracle会话生命周期延长至12小时，情况有所改善但问题仍存在。

问题根源 #

问题出在代码中频繁执行以下语句：

OracleConnection oracleConnection = (ps.getConnection().unwrap(OracleConnection.class));

fixedDataAttributes[156] = ps.getConnection().createStruct(Constants.SIM_TYP_LOOKUP, fixedDataAttributesByProduct);

在输入处理器中，ps.getConnection().createStruct(Constants.SIM_TYP_LOOKUP)被调用60次，导致出现大量冗余查询，大幅增加响应时间：

begin :1 := sys.dbms_pickler.get_type_shape ( :2, :3, :4, :5, :6, :7, :8, :9, :10 ) end

SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER FROM ALL_COLL_TYPES WHERE OWNER = :1 AND TYPE_NAME = :2

DECLARE CURSOR usyn_cur IS SELECT table_name from user_synonyms TYPE table_name_type IS TABLE OF usyn_cur%ROWTYPE; table_names table_name_type; lastrow BINARY_INTEGER := ?; l_syntname user_synonyms.table_name%TYPE; ...

SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER FROM USER_COLL_TYPES WHERE TYPE_NAME = :1

核心需求 #

缓存Struct，避免重复执行Oracle查询。已知可尝试实现SQLData接口，希望得到针对当前代码的具体实现指导或其他替代方案。

相关类型定义 #

Java对象（SimTypLookupTypeDto） #

import com.company.mybatis.util.core.annotation.CompanyObject;
import com.fasterxml.jackson.annotation.JsonAlias;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonPropertyOrder;
import lombok.*;

import java.io.Serializable;

/**
 * 对应Oracle类型 sim_typ_lookup
 */
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@JsonIgnoreProperties(ignoreUnknown = true)
@Builder
@CompanyObject(objectName = "SIM_TYP_LOOKUP")
@JsonPropertyOrder({"code", "value"})
public class SimTypLookupTypeDto implements Serializable {

    /**
     * 序列化版本号
     */
    public static final long serialVersionUID = 166L;

    /**
     * 对应codigo字段
     */
    @JsonProperty("codigo")
    @JsonAlias({"codigo"})
    private String code;

    /**
     * 对应valor字段
     */
    @JsonProperty("valor")
    @JsonAlias({"valor"})
    private String value;

    /**
     * 从对象数组初始化属性，顺序与Oracle结构一致
     * @param attributes 属性值数组
     */
    public SimTypLookupTypeDto(Object[] attributes){
        this.code = (String) attributes[0];
        this.value = (String) attributes[1];
    }
}

Oracle类型定义 #

CREATE OR REPLACE TYPE sim_typ_lookup AS OBJECT
                       (codigo   varchar2(60)
                       ,valor    varchar2(2000)
                       ,CONSTRUCTOR FUNCTION sim_typ_lookup RETURN SELF AS RESULT);


CREATE OR REPLACE TYPE BODY sim_typ_lookup AS
  CONSTRUCTOR FUNCTION sim_typ_lookup RETURN SELF AS RESULT
  AS
  BEGIN
     SELF.codigo   := null;
     SELF.valor    := null;
    RETURN;
  END;
END;

解决方案指导 #

方案1：实现SQLData接口（推荐） #

实现SQLData接口可让JDBC直接映射Java对象与Oracle自定义类型，避免每次调用createStruct时查询元数据。修改SimTypLookupTypeDto如下：

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

// 保留原有lombok和其他注解
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@JsonIgnoreProperties(ignoreUnknown = true)
@Builder
@CompanyObject(objectName = "SIM_TYP_LOOKUP")
@JsonPropertyOrder({"code", "value"})
public class SimTypLookupTypeDto implements SQLData, Serializable {

    public static final long serialVersionUID = 166L;
    // 必须指定Oracle类型名称
    private static final String SQL_TYPE = "SIM_TYP_LOOKUP";

    @JsonProperty("codigo")
    @JsonAlias({"codigo"})
    private String code;

    @JsonProperty("valor")
    @JsonAlias({"valor"})
    private String value;

    public SimTypLookupTypeDto(Object[] attributes){
        this.code = (String) attributes[0];
        this.value = (String) attributes[1];
    }

    @Override
    public String getSQLTypeName() throws SQLException {
        return SQL_TYPE;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        // 按Oracle类型字段顺序读取
        this.code = stream.readString();
        this.value = stream.readString();
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        // 按Oracle类型字段顺序写入
        stream.writeString(this.code);
        stream.writeString(this.value);
    }
}

使用方式：

• 在MyBatis XML映射文件中，调用存储过程时参数类型指定为SimTypLookupTypeDto，JDBC类型设为STRUCT。
• 直接传递Java对象即可，无需手动调用createStruct，JDBC会自动完成映射。

方案2：缓存Struct实例 #

如果暂时不想修改对象结构，可在同一个连接会话内缓存创建好的Struct，避免重复创建：

import java.sql.Connection;
import java.sql.Struct;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class StructCache {
    // 按连接哈希+类型名称+属性值组合作为键，缓存Struct
    private static final ThreadLocal<Map<String, Struct>> CONNECTION_STRUCT_CACHE = ThreadLocal.withInitial(ConcurrentHashMap::new);

    public static Struct getOrCreateStruct(Connection conn, String typeName, Object[] attributes) throws SQLException {
        // 生成唯一键：连接哈希+类型名称+属性值的哈希
        String key = conn.hashCode() + "_" + typeName + "_" + computeAttributesHash(attributes);
        Struct struct = CONNECTION_STRUCT_CACHE.get().get(key);
        if (struct == null) {
            struct = conn.createStruct(typeName, attributes);
            CONNECTION_STRUCT_CACHE.get().put(key, struct);
        }
        return struct;
    }

    private static int computeAttributesHash(Object[] attributes) {
        int hash = 1;
        for (Object attr : attributes) {
            hash = 31 * hash + (attr == null ? 0 : attr.hashCode());
        }
        return hash;
    }

    // 事务结束后清理缓存（比如用Spring的@AfterTransaction注解）
    public static void clearCache() {
        CONNECTION_STRUCT_CACHE.remove();
    }
}

修改原有代码：

// 替换原来的createStruct调用
fixedDataAttributes[156] = StructCache.getOrCreateStruct(ps.getConnection(), Constants.SIM_TYP_LOOKUP, fixedDataAttributesByProduct);

注意：必须在事务结束后清理缓存，避免内存泄漏（连接关闭后Struct会失效）。

方案3：MyBatis自定义类型处理器 #

编写MyBatis的TypeHandler处理SimTypLookupTypeDto与OracleSTRUCT的转换，内部可加入缓存逻辑：

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.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Struct;

@MappedTypes(SimTypLookupTypeDto.class)
@MappedJdbcTypes(JdbcType.STRUCT)
public class SimTypLookupTypeHandler extends BaseTypeHandler<SimTypLookupTypeDto> {

    private static final String SQL_TYPE = "SIM_TYP_LOOKUP";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, SimTypLookupTypeDto parameter, JdbcType jdbcType) throws SQLException {
        Connection conn = ps.getConnection();
        // 可在此加入方案2的缓存逻辑，避免重复创建Struct
        Struct struct = conn.createStruct(SQL_TYPE, new Object[]{parameter.getCode(), parameter.getValue()});
        ps.setObject(i, struct);
    }

    @Override
    public SimTypLookupTypeDto getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Struct struct = (Struct) rs.getObject(columnName);
        return struct != null ? new SimTypLookupTypeDto(struct.getAttributes()) : null;
    }

    @Override
    public SimTypLookupTypeDto getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Struct struct = (Struct) rs.getObject(columnIndex);
        return struct != null ? new SimTypLookupTypeDto(struct.getAttributes()) : null;
    }

    @Override
    public SimTypLookupTypeDto getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Struct struct = (Struct) cs.getObject(columnIndex);
        return struct != null ? new SimTypLookupTypeDto(struct.getAttributes()) : null;
    }
}

使用方式：

• 在MyBatis配置文件中注册该类型处理器。
• 映射文件中直接使用SimTypLookupTypeDto作为参数或返回类型，无需手动创建Struct。

内容的提问来源于stack exchange，提问作者Olivetz