[MyBatis] ListのNullをifでスキップしたい

Question

閲覧ありがとうございます。
MyBatisでのnullの挙動で困っているので、ご教授お願い致します。
具体的には、list(1つ目のnull)をMapperでnull判定すると
org.springframework.jdbc.BadSqlGrammarException at SecondTest.java:38
Caused by: org.postgresql.util.PSQLException at SecondTest.java:38
というエラーが出てしまいます。
List.ofでfoodsMapper.findByQuery(new FoodsQuery(List.of("野菜"), null, null))のようにnullでない値を入力すると正常に動作します。
また、ほかの部分をnullにして、ifで書いた場合も正常に動作します。
つまり、Listの部分のみnullのスキップができない状態です。

説明に不備等ございましたら、コメントお願いいたします。
よろしくお願いします。ありがとう

public class SecondTest{
    @Autowired
    private FoodsMapper foodsMapper; 
    
    @Test
    void test(){
    System.out.println(foodsMapper.findByQuery(new FoodsQuery(null, null, "アイス"))); //エラー
System.out.println(foodsMapper.findByQuery(new FoodsQuery(List.of("氷菓"), null, "アイス"))); //成功する
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="curriculum.mapper.FoodsMapper">
    <select id="findByQuery" resultType="curriculum.domain.FoodsQuery">
        Select
         *
        From
         foods
        Where
           <if test = "types != null" >
           AND type IN
          <foreach item="item" index="index" collection="types"
      open="(" separator="," close=")" >
          #{item}
          </foreach>
          </if>
          <if test="limit != null">
          AND price <![CDATA[ <= ]]> #{limit}
          </if>
          <if test="word != null">
          AND name LIKE CONCAT('%', #{word}, '%')
          </if>
    </select>
</mapper>

@Mapper
public interface FoodsMapper{ 
    List<Map<String, Object>> findByQuery(FoodsQuery param);
}

@Data
public class FoodsQuery {
    @Column(nullable = false)
    public List<String> types;
    public Integer limit;
    public String word;

    public FoodsQuery(){
    }

    public FoodsQuery(List<String> types, Integer limit, String word) {
        this.types = types;
        this.limit = limit;
        this.word = word;
    }
}

create table foods
(
    id    SERIAL,
    name  text    NOT NULL,
    type  text    NOT NULL,
    price integer NOT NULL
);

insert into foods (name, type, price)
values ('りんご', '果物', 100),
       ('いちご', '果物', 500),
       ('西瓜', '果物', 2000),
       ('じゃがいも', '野菜', 150),
       ('白菜', '野菜', 300),
       ('かぼちゃ', '野菜', 1000),
       ('チョコレート', '菓子', 200),
       ('バニラアイス', '氷菓', 300),
       ('抹茶アイス', '氷菓', 300),
       ('いちごアイス', '氷菓', 300),
       ('雪見だいふく', '氷菓', 200);

Comments

[m.ts10806]

テーブル定義の問題では。
NULLを受け入れるか、受け入れないか　なので。

[sui_15]

m.ts10806様
ご回答ありがとうございます。
@Column(nullable = false)をテーブルに記載しましたが、うまくいきませんでした。

[m.ts10806]

質問本文編集して一通りコード提示してください。
あと「うまくいかない」だと何が起きてるか分からないので、具体的に記載を。

[sui_15]

m.ts10806様

一通りコードを提示させていただきました。
よろしくお願いします。

Answer 1

System.out.println(foodsMapper.findByQuery(new FoodsQuery(null, null, "アイス"))); //エラー 三つ目のword以外がnullだと、こういうSQLになるのでは?

        Select
         *
        From
         foods
        Where
          AND name LIKE CONCAT('%', #{word}, '%')

... where and ...はおかしいのではないかと。

けれど、System.out.println(foodsMapper.findByQuery(new FoodsQuery(List.of("氷菓"), null, "アイス"))); //成功する だとしても、

        Select
         *
        From
         foods
        Where
           AND type IN

だから、うまくはいかなないような...。

動的 SQLをみると、

<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

としているので、state = ‘ACTIVE’代わりにこうすればいいのかも。

    <select id="findByQuery" resultType="curriculum.domain.FoodsQuery">
        Select
         *
        From
         foods
        Where
1 = 1
           <if test = "types != null" >
           AND type IN

Comments

[sui_15]

shiketa様
ご回答ありがとうございます。
whereのあとに1=1を記載したら、求める回答を得ることができました。
本当にありがとうございます。
この、1=1というのはどういうことなのでしょうか？

[KoichiSugiyama]

shiketaさんの解説にも書いてありますよ。
＞三つ目のword以外がnullだと、こういうSQLになるのでは?
条件によってSQLとして成り立たないものができてしまうので、1 = 1という常に成立する条件とANDを取る形に調整してあるということです。

[shiketa]

です。
もし、全部の条件がnullだったらこうなる。`select * from foords where;`
全部の条件がnullだったら`where`句も除かないといけない。めんどうくさい。そのための`1=1`。`true`でもいいかもしれない。

[sui_15]

Sugiyama様、shiketa様
返信遅くなってしまいすみません。
とても参考になりました。
また機会がありましたら、その時は宜しくお願い致します。

Answer 2

<where>を利用されるとよいかと思います。

<where>
  <if test = "types != null">
    type IN <foreach item="item" index="index" collection="types" open="(" separator="," close=")">#{item}</foreach>
  </if>
  <if test="limit != null">
    AND price <![CDATA[ <= ]]> #{limit}
  </if>
  <if test="word != null">
    AND name LIKE CONCAT('%', #{word}, '%')
  </if>
</where>

参考: https://mybatis.org/mybatis-3/ja/dynamic-sql.html

Comments

[sui_15]

neko様

ご回答ありがとうございます。
そのようなwhereの書き方もあるのですね、参考になります。
次、またこのようなコードを書く際は試してみます！