CODEWITHSUNDEEP
spring-bootmybatis
EVADA
EVADA

Reputation: 9

How to use Array in spring boot mybatis annotation?

I received an array of columns from Frontend. I need SELECT ---- WHERE [Received Array] = 1. However, the length of the array is dynamic.

That is, a query with a value of 1 should be written for each column received in the array.

@Select("SELECT * FROM reservation WHERE product_type = #{product_type} AND product_location = #{product_location} AND $(myArray) = 1")
    List<product> getOptionedProduct(@Param("product_type") String product_type, @Param("product_location") String product_location, @Param("myArray") List<String> myArray);

myArray has [A, B, C] I want to write like

@Select("SELECT * FROM reservation WHERE product_type = #{product_type} AND product_location = #{product_location} AND A = 1 AND B = 1 AND C = 1")

Is it possible?

Upvotes: 0

Views: 647

Answers (1)

ave
ave

Reputation: 3594

You need to use <foreach /> tag (doc).

To use tags in annotation, you need to enclose the entire statement with <script /> tag.

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

If you use Java 15 or later, you can benefit from text blocks (JEP-378).

@Select("""
  <script>
  SELECT * FROM reservation WHERE product_type = #{product_type}
  AND product_location = #{product_location}
  <if test="myArray != null">
    <foreach item="columnName" collection="myArray" separator=" and " open=" and ">
      ${columnName} = 1
    </foreach>
  </if>
  </script>
  """)

As myArray is a list of column names (right?), you have to use ${} instead of #{}.
So, you need to make sure that myArray does not contain any unexpected string to avoid SQL injection, etc..
See the FAQ entry for the details.

Upvotes: 1

Related Questions