Spring Boot MyBatis注解方式下如何实现动态列数组的WHERE条件拼接查询

Yes, this requirement is absolutely achievable! MyBatis has built-in dynamic SQL support that's designed exactly for scenarios where your query conditions depend on dynamic input like a variable-length list of column names.

Fixed Code Implementation #

Replace your current @Select annotation with this dynamic SQL version:

@Select("<script>" +
        "SELECT * FROM reservation " +
        "WHERE product_type = #{product_type} " +
        "AND product_location = #{product_location} " +
        "<if test='myArray != null and myArray.size() > 0'>" +
        "AND " +
        "<foreach collection='myArray' item='column' separator=' AND '>" +
        "${column} = 1" +
        "</foreach>" +
        "</if>" +
        "</script>")
List<Product> getOptionedProduct(
        @Param("product_type") String product_type,
        @Param("product_location") String product_location,
        @Param("myArray") List<String> myArray
);

Key Details Explained #

• <script> Tag: This tells MyBatis to parse the string as dynamic SQL, allowing XML-style dynamic tags inside the @Select annotation.
• <if> Condition: Ensures we only add the dynamic column conditions if myArray is not empty. This prevents invalid SQL syntax if the list has no elements.
• <foreach> Loop:
  • collection='myArray': Specifies the list parameter we want to iterate over.
  • item='column': Assigns each element in the list to a variable named column for use in the loop.
  • separator=' AND ': Adds an AND between each generated condition, so your list [A, B, C] becomes A = 1 AND B = 1 AND C = 1.
  • ${column}: Uses string substitution (instead of #{column}) because we're referencing column names, not values. Using #{} would wrap the column name in quotes, which breaks SQL syntax.

Example Generated SQL #

When myArray contains [A, B, C], MyBatis will generate exactly the query you're looking for:

SELECT * FROM reservation 
WHERE product_type = ? 
AND product_location = ? 
AND A = 1 AND B = 1 AND C = 1

Important Notes #

• Make sure all values in myArray match valid column names in your reservation table—invalid column names will cause SQL syntax errors.
• Follow Java naming conventions: Your entity class should be Product (capitalized) instead of product.

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