MyBatis | Dynamic SQL | set, trim

DB table

test_table

id	string	number
1	hoge	100
2	hoge	200
3	fuga	200
4	piyo	100

Source code

TestTable.java

package sample.mybatis;

public class TestTable {
    private int id;
    private String string;
    private Integer number;

    public TestTable id(int id) {
        this.id = id;
        return this;
    }

    public TestTable string(String string) {
        this.string = string;
        return this;
    }

    public TestTable number(int number) {
        this.number = number;
        return this;
    }
}

sample_mapper.xml

<?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="sample.mybatis">

  <update id="updateTest">
    update test_table
    <set>
      <if test="string != null">
        string = #{string},
      </if>
      <if test="number != null">
        number = #{number},
      </if>
    </set>
    where id = #{id}
  </update>
</mapper>

Main.java

package sample.mybatis;

import java.io.InputStream;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class Main {

    public static void main(String[] args) throws Exception {
        try (InputStream in = Main.class.getResourceAsStream("/mybatis-config.xml")) {
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);

            try (SqlSession session = factory.openSession()) {
                session.update("sample.mybatis.updateTest",
                        new TestTable().id(1).number(555));

                session.update("sample.mybatis.updateTest",
                        new TestTable().id(3).string("update").number(999));

                session.commit();
            }
        }
    }
}

Execution result

[DEBUG] s.m.updateTest  - ==>  Preparing: update test_table SET number = ? where id = ? 
[DEBUG] s.m.updateTest  - ==> Parameters: 555(Integer), 1(Integer)
[DEBUG] s.m.updateTest  - <==    Updates: 1
[DEBUG] s.m.updateTest  - ==>  Preparing: update test_table SET string = ?, number = ? where id = ? 
[DEBUG] s.m.updateTest  - ==> Parameters: update(String), 999(Integer), 3(Integer)
[DEBUG] s.m.updateTest  - <==    Updates: 1
test_table

test_table

id	string	number
1	hoge	555
2	hoge	200
3	update	999
4	piyo	300

Explanation

When using the <set> tag, a SET clause is added at the beginning if the inner text is not empty.
Also, a trailing comma (,) is removed automatically.

Replacing it with the trim tag

As with <where>, this can also be replaced with <trim>.

sample_mapper.xml

<?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="sample.mybatis">

  <update id="updateTest">
    update test_table
    <trim prefix="set" suffixOverrides=",">
      <if test="string != null">
        string = #{string},
      </if>
      <if test="number != null">
        number = #{number},
      </if>
    </trim>
    where id = #{id}
  </update>
</mapper>

In the suffixOverrides attribute, specify the character to remove from the end.