How to use Array in spring boot mybatis annotation?

Question

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?

ave · Accepted Answer · 2021-02-27 17:16:21Z

1

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.

answered Feb 27, 2021 at 17:16

ave

3,7242 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

The Impaler Over a year ago

Good answer, However, the OP should not consider using an array with a massive number of values. The database engine may have some limits in terms of the length of the resulting SQL statement.

ave Over a year ago

As the example A, B, C is not descriptive enough, I am not so sure, but the requirement implies a bad design. A separate table with two columns (reservation_id and flag), for example, might be more appropriate.

Collectives™ on Stack Overflow

How to use Array in spring boot mybatis annotation?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related