How to use dynamic SQL query in MyBatis with annotation(how to use selectProvider)?

Question

I am trying to avoid having an additional xml to define the mapper in mybatis3. Annotation fits right in.

I am a bit confused by the usage of @SelectProvider/@InsertProvider/etc. Don't think there are many resources online guiding me through this.

Basically, I will like to find the annotation version of alternative for in mybatis3.

For example, I have a xml mapper and I wanna convert it to use annotation

<select ...>
  <where>
    <if cause.....>
    </if>
    <if cause......>
    </if>
  </where>
</select>

Could anyone provide a concrete answer/solution including the code?

Thanks in advance!

Answer 1

An alternative solution for you could be:

Add <script> at the beginning of your @annotation

@Update("<script>
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</script>")

In additional, we compile .groovy to .class in our projects, thus, we can write SQL in @annotation like above

Answer 2

1. in your mapper interface:

   @SelectProvider(type=MyClass.class, method="myMethod")
   public Object selectById(int id);
   

2. in MyClass:

   public static String myMethod() {
       return "select * from MyTable where id=#{id}"; 
   }