Mybatis spring boot how to create dynamic update query

Question

I have given the mybatis update qry below, In which update query specified in a static way. In my case i have to make it dynamic based on the incoming fields.

@Update("UPDATE guestpayment SET " +
            "SourceSystemUpdated=#{sourcesysupdated}," +
            "SourceSystemUpdateComment=#{sourcesysupdatedcomments}" +
           "WHERE PrimaryId=#{PrimaryId}")
    void updateguestpayment(Guestpayment updateguestpayment);

How to make the update query dynamic based on the incoming fields as jsonobject?

Mapper xml object

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.mybatis.dao.GuestpaymentDAO">
<resultMap id="Guestpayment" type="com.mybatis.mybatis.models.Guestpayment" >
    <id property="PrimaryId" column="PrimaryId" javaType="int" jdbcType="integer" />
    <result property="SourceSystemUpdated" column="SourceSystemUpdated" javaType="string" jdbcType="VARCHAR" />
    <result property="SourceSystemUpdateComment" column="SourceSystemUpdateComment" javaType="string" jdbcType="VARCHAR" />
</resultMap>

<update id="updateguestpayment">
    update guestpayment as g
    <set >
        <if test="updateguestpayment.SourceSystemUpdated != null and updateguestpayment.SourceSystemUpdated != ''" >
            g.SourceSystemUpdated = #{updateguestpayment.SourceSystemUpdated} ,
        </if>
        <if test="updateguestpayment.SourceSystemUpdateComment != null and updateguestpayment.SourceSystemUpdateComment != ''">
            <!-- No need to deal with commas, <set> will auto delete extra commas -->
            g.SourceSystemUpdateComment = #{updateguestpayment.SourceSystemUpdateComment},
        </if>
    </set>
    where g.PrimaryId = #{updateguestpayment.PrimaryId}
</update>
</mapper>

Guestpayment.java

package com.mybatis.mybatis.models;

import java.util.Date;

public class Guestpayment {
    private int PrimaryId;

    private String sourcesysupdated;
    private String sourcesysupdatedcomments;

    public int getPrimaryId() {
        return PrimaryId;
    }

    public void setPrimaryId(int primaryId) {
        PrimaryId = primaryId;
    }

    public String getSourcesysupdated() {
        return sourcesysupdated;
    }

    public void setSourcesysupdated(String sourcesysupdated) {
        this.sourcesysupdated = sourcesysupdated;
    }

    public String getSourcesysupdatedcomments() {
        return sourcesysupdatedcomments;
    }

    public void setSourcesysupdatedcomments(String sourcesysupdatedcomments) {
        this.sourcesysupdatedcomments = sourcesysupdatedcomments;
    }

}

GuestpaymentDAO(interface)

@Mapper
@Repository
public interface GuestpaymentDAO {

    void updateguestpayment(@Param("updateguestpayment") Guestpayment updateguestpayment);

}

Answer 1

Why not use mybatis xml and entity to cooperate with each other?
eg :
DAO

@Mapper
@Repository
public interface GuestpaymentDAO {

    void updateguestpayment(@Param("updateguestpayment") Guestpayment updateguestpayment);

}

mybatis mapper xml file: GuestpaymentDAO.xml

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.dao.GuestpaymentDAO">
<resultMap id="Guestpayment" type="com.example.demo.entity.Guestpayment" >
    <id property="PrimaryId" column="PrimaryId" javaType="string" jdbcType="VARCHAR" />
    <result property="SourceSystemUpdated" column="SourceSystemUpdated" javaType="string" jdbcType="VARCHAR" />
    <result property="SourceSystemUpdateComment" column="SourceSystemUpdateComment" javaType="string" jdbcType="VARCHAR" />
</resultMap>

<update id="updateguestpayment">
    update Guestpayment as g
    <set >
        <if test="updateguestpayment.SourceSystemUpdated != null and updateguestpayment.SourceSystemUpdated != ''" >
            g.SourceSystemUpdated = #{updateguestpayment.SourceSystemUpdated} ,
        </if>
        <if test="updateguestpayment.SourceSystemUpdateComment != null and updateguestpayment.SourceSystemUpdateComment != ''">
            <!-- No need to deal with commas, <set> will auto delete extra commas -->
            g.SourceSystemUpdateComment = #{updateguestpayment.SourceSystemUpdateComment},
        </if>
    </set>
    where g.PrimaryId = #{updateguestpayment.PrimaryId}
</update>
</mapper>

doc: https://mybatis.org/mybatis-3/sqlmap-xml.html
src:src image

Answer 2

You can use Criteria to create a dynamic update query. In your case, you can do it like below,

public class GuestPaymentDAO {

    @PersistenceContext
    private EntityManager em;

   public void updateGustPayment(String sourcesysupdated, String sourcesysupdatedcomments, int PrimaryId) {
       CriteriaBuilder cb = this.em.getCriteriaBuilder();

       // create update
       CriteriaUpdate<Guestpayment> update = cb.createCriteriaUpdate(Guestpayment.class);

       // set the root class
       Root e = update.from(Guestpayment.class);

       // set update clause
       if (sourcesysupdated != null) {
           update.set("SourceSystemUpdated", sourcesysupdated);
       }
       if (sourcesysupdatedcomments != null) {
           update.set("SourceSystemUpdateComment", sourcesysupdatedcomments);
       }
       // set where clause
       update.where(cb.equal(e.get("PrimaryId"), PrimaryId));

       // perform update
       this.em.createQuery(update).executeUpdate();
   }
}