我有一个原生查询（native query），它接收4个（可选）参数来进行过滤。4个参数对可读性来说有点多，因此我在考虑用一个对象来替代。期间遇到了两个不同的问题。

首先，我只是尝试把一个POJO作为过滤器：

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder(toBuilder = true)
public class QuestionCategoryFilter {
    private String name;
    private Boolean active;
    private Integer orderNumber;
    private String answerTypeName;
}

但随后出现了如下错误：

org.springframework.orm.jpa.JpaSystemException: A problem occurred in the SQL executor : No JDBC mapping could be inferred for parameter - org.hibernate.sql.exec.internal.JdbcParameterImpl@6c173735

后来我偶然发现直接在Hibernate的类上按Ctrl点击就能找到一个解决方案，我的搜索结果指向了org.hibernate.type.QueryParameterJavaObjectType。

扩展这个类确实解决了问题，我的查询可以正常执行，但后来在项目中添加Swagger-UI时，我遇到了一个问题：原本简单的4字段对象其实变得庞大无比：

{
  "jdbcType": {
    "integer": true,
    "float": true,
    "number": true,
    "binary": true,
    "castType": "STRING",
    "ddlTypeCode": 0,
    "duration": true,
    "nationalized": true,
    "string": true,
    "interval": true,
    "friendlyName": "string",
    "lobOrLong": true,
    "jdbcTypeCode": 0,
    "temporal": true,
    "stringLike": true,
    "lob": true,
    "defaultSqlTypeCode": 0,
    "decimal": true
  },
  "jdbcValueBinder": {},
  "jdbcValueExtractor": {},
  "jdbcLiteralFormatter": {},
  "name": "string",
  "active": true,
  "orderNumber": 0,
  "answerTypeName": "string",
  "mutable": true,
  "associationType": true,
  "componentType": true,
  "collectionType": true,
  "anyType": true,
  "entityType": true,
  "castType": "STRING",
  "registrationKeys": [
    "string"
  ],
  "javaTypeDescriptor": {
    "javaType": {
      "typeName": "string"
    },
    "typeName": "string",
    "defaultValue": {},
    "comparator": {},
    "mutabilityPlan": {
      "mutable": true
    },
    "longSqlLength": 0,
    "temporalType": true
  },
  "valueConverter": {
    "relationalJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "domainJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    }
  },
  "mappedType": {
    "mappedJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    }
  },
  "jdbcTypeCount": 0,
  "jdbcMapping": {
    "jdbcJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "jdbcType": {
      "integer": true,
      "float": true,
      "number": true,
      "binary": true,
      "castType": "STRING",
      "ddlTypeCode": 0,
      "duration": true,
      "nationalized": true,
      "string": true,
      "interval": true,
      "friendlyName": "string",
      "lobOrLong": true,
      "jdbcTypeCode": 0,
      "temporal": true,
      "stringLike": true,
      "lob": true,
      "defaultSqlTypeCode": 0,
      "decimal": true
    },
    "jdbcValueBinder": {},
    "castType": "STRING",
    "valueConverter": {
      "relationalJavaType": {
        "javaType": {
          "typeName": "string"
        },
        "typeName": "string",
        "defaultValue": {},
        "comparator": {},
        "mutabilityPlan": {
          "mutable": true
        },
        "longSqlLength": 0,
        "temporalType": true
      },
      "domainJavaType": {
        "javaType": {
          "typeName": "string"
        },
        "typeName": "string",
        "defaultValue": {},
        "comparator": {},
        "mutabilityPlan": {
          "mutable": true
        },
        "longSqlLength": 0,
        "temporalType": true
      }
    },
    "jdbcTypeCount": 0,
    "mappedJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "javaTypeDescriptor": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "jdbcLiteralFormatter": {},
    "singleJdbcMapping": "string",
    "jdbcValueExtractor": {}
  },
  "mappedJavaType": {
    "javaType": {
      "typeName": "string"
    },
    "typeName": "string",
    "defaultValue": {},
    "comparator": {},
    "mutabilityPlan": {
      "mutable": true
    },
    "longSqlLength": 0,
    "temporalType": true
  },
  "expressibleJavaType": {
    "javaType": {
      "typeName": "string"
    },
    "typeName": "string",
    "defaultValue": {},
    "comparator": {},
    "mutabilityPlan": {
      "mutable": true
    },
    "longSqlLength": 0,
    "temporalType": true
  },
  "singleJdbcMapping": {
    "jdbcJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "jdbcType": {
      "integer": true,
      "float": true,
      "number": true,
      "binary": true,
      "castType": "STRING",
      "ddlTypeCode": 0,
      "duration": true,
      "nationalized": true,
      "string": true,
      "interval": true,
      "friendlyName": "string",
      "lobOrLong": true,
      "jdbcTypeCode": 0,
      "temporal": true,
      "stringLike": true,
      "lob": true,
      "defaultSqlTypeCode": 0,
      "decimal": true
    },
    "jdbcValueBinder": {},
    "castType": "STRING",
    "valueConverter": {
      "relationalJavaType": {
        "javaType": {
          "typeName": "string"
        },
        "typeName": "string",
        "defaultValue": {},
        "comparator": {},
        "mutabilityPlan": {
          "mutable": true
        },
        "longSqlLength": 0,
        "temporalType": true
      },
      "domainJavaType": {
        "javaType": {
          "typeName": "string"
        },
        "typeName": "string",
        "defaultValue": {},
        "comparator": {},
        "mutabilityPlan": {
          "mutable": true
        },
        "longSqlLength": 0,
        "temporalType": true
      }
    },
    "jdbcTypeCount": 0,
    "mappedJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "javaTypeDescriptor": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "jdbcLiteralFormatter": {},
    "singleJdbcMapping": "string",
    "jdbcValueExtractor": {}
  },
  "persistenceType": "ENTITY",
  "sqmType": {
    "sqmType": "string",
    "tupleLength": 0,
    "typeName": "string",
    "expressibleJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    },
    "relationalJavaType": {
      "javaType": {
        "typeName": "string"
      },
      "typeName": "string",
      "defaultValue": {},
      "comparator": {},
      "mutabilityPlan": {
        "mutable": true
      },
      "longSqlLength": 0,
      "temporalType": true
    }
  },
  "tupleLength": 0,
  "typeName": "string",
  "relationalJavaType": {
    "javaType": {
      "typeName": "string"
    },
    "typeName": "string",
    "defaultValue": {},
    "comparator": {},
    "mutabilityPlan": {
      "mutable": true
    },
    "longSqlLength": 0,
    "temporalType": true
  },
  "jdbcJavaType": {
    "javaType": {
      "typeName": "string"
    },
    "typeName": "string",
    "defaultValue": {},
    "comparator": {},
    "mutabilityPlan": {
      "mutable": true
    },
    "longSqlLength": 0,
    "temporalType": true
  }
}

而且我不能添加 @JsonIgnore 或其他类似的方式来避免让它们暴露给swagger，因为除了这4个字段外，其余字段都来自我不拥有的类。

那么，针对我的问题，常见的解决方案是什么？

Should I forgo using an object within the query and unwrap the object when calling it?
Should I create a separate Filter object for the API that I then convert into the JPA-compatible filter object?
Or is there a way simpler JavaType for me to extend instead of org.hibernate.type.QueryParameterJavaObjectType?

编辑1：我也不知道为什么之前一直没把源代码贴上来，下面是相关部分：

    @Transactional(readOnly = true)
    @Query(nativeQuery = true, value = """
        SELECT qc.id, qc.name, qc.active, qc.order_number as orderNumber, qc.answer_type_id as answerTypeId,
            at.name as answerTypeName, at.max_value as 'maxValue',
            q.id as questionId, q.question, q.active AS questionActive, q.order_number AS questionOrderNumber,
            ai.num_answer as numAnswer,
            a.answer_time as answerTime, a.active AS answerActive,
            u.name as username, u.ldap_id as ldapId, u.active as userActive
        FROM QUESTION_CATEGORIES qc
            INNER JOIN ANSWER_TYPES at on at.id = qc.answer_type_id
            INNER JOIN QUESTIONS q ON q.category_id = qc.id
            LEFT JOIN ANSWER_ITEMS ai ON ai.question_id = q.id
            LEFT JOIN ANSWERS a ON a.id = ai.answer_id
            LEFT JOIN USERS u ON (u.id = a.user_id AND u.ldap_id = :username)
        WHERE q.active = true
            AND :filter IS NULL OR (
                (:#{#filter.getName()} IS NULL OR LOWER(qc.name) LIKE CONCAT('%', LOWER(:#{#filter.getName()}), '%'))
                AND (:#{#filter.getActive()} IS NULL OR qc.active = :#{#filter.getActive()})
                AND (:#{#filter.getOrderNumber()} IS NULL OR qc.order_number = :#{#filter.getOrderNumber()})
                AND (:#{#filter.getAnswerTypeName()} IS NULL OR at.name = :#{#filter.getAnswerTypeName()})
            )
        ORDER BY qc.order_number
    """)
    List<QuestionCategoryDAO> findAllResponsesByFilterAndUser(@Param("filter") QuestionCategoryFilter filter, @Param("username") String username);

这确实可用，但只是因为 public class QuestionCategoryFilter extends QueryParameterJavaObjectType。如果没有对 QueryParameterJavaObjectType 的扩展，我就会得到我之前提到的错误。

我现在发现我跟随的每个示例似乎只有一个参数，即过滤对象，而我却有对象和一个额外的参数。

最终我解决了我的问题，原因是 lombok 生成的构造函数参数数量荒诞，而 springdoc 随后把它们直接暴露到了 swagger-ui 界面。

但为了将来参考，我想知道正式的解决方案应该是什么（不是靠反向工程Hibernate并靠运气得到一个扩展），或者说，为查询参数再包装一个对象并附带额外参数，是否只是坏实践并且有意不被支持。

解决方案

我将基于一个类似的问答来给出我的回答，该问答在评论中被提及（来自Andrew S）。这条问答专门指出：https://stackoverflow.com/a/76779816/1688441，它建议在一个非常相似的问题上使用SpEL支持。

所以，如果我理解正确，你只是想传入一个对象，但用它来包裹4个参数。

大致像这样？

    @Query("select * from questionCategory qc
    where (:#{#qcf.name} is null or qc.name = :#{#qcf.name})
      and (:#{#qcf.active} is null or qc.active = :#{#qcf.active})
      and (:#{#qcf.orderNumber} is null or qc.orderNumber = :#{#qcf.orderNumber})
      and (:#{#qcf.answerTypeName} is null or qc.answerTypeName = :#{#qcf.answerTypeName})
    ")
    List<QuestionCategory> findByFilter(@Param("qcf") QuestionCategoryFilter f);

我对你的对象有一些假设……（例如questionCategory与 questionCategoryFilter的区别）

免责声明/参考

此回答基于：

• 在JPA/JPQL @Param @Query中使用对象替代单个参数

它也引用：

• https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions