Dynamic SQL does not work for checking parameter is null.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Dynamic SQL does not work for checking parameter is null.

Ming Xia
Hi,

I was trying to use mybatis dynamic sql, but seems it does not produce correct sql statement when checking if a specific parameter is null. Even `record.description==null`, the generated sql still include set description=?, and the record in db is updated with description=`null`, even it was not null before updating. Any idea on this problem, or it is a bug of MyBatis? My Mybatis version is 3.4.0.

Thanks,
Ming


@Update({
   
"<script>",
      "update city",
      "<set>",
        "<if test='#{description} != null'>description = #{description,jdbcType=VARCHAR},</if>",
      "</set>",
      "where name = #{name,jdbcType=VARCHAR}",
    "</script>"
})
@Override
int update(CityDO record);

My `CityDO` has only `name` and `description` field, and I verified that the description field is null before passing it to the `update` method.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL does not work for checking parameter is null.

Guy Rouillier-2
On Thu, 2019-04-25 at 22:49 -0700, Ming Xia wrote:
Hi,

I was trying to use mybatis dynamic sql, but seems it does not produce correct sql statement when checking if a specific parameter is null. Even `record.description==null`, the generated sql still include set description=?, and the record in db is updated with description=`null`, even it was not null before updating. Any idea on this problem, or it is a bug of MyBatis? My Mybatis version is 3.4.0.

Thanks,
Ming


@Update
({

    
"<script>"
,

      
"update city"
,

      
"<set>"
,

        
"<if test='#{description} != null'>description = #{description,jdbcType=VARCHAR},</if>"
,

      
"</set>"
,

      
"where name = #{name,jdbcType=VARCHAR}"
,

    
"</script>"

})

@Override

int
 
update
(
CityDO
 record
)
;

My `CityDO` has only `name` and `description` field, and I verified that the description field is null before passing it to the `update` method.

Expressions within the tags - <if>, in this case - are Java expressions (specifically, OGNL expressions), not parameter expressions. So, the proper test here would be:

<pre>
<if test='description != null'>
description = #{description,jdbcType=VARCHAR},
</if>
</pre>

You don't have to have the entire clause on a single line, though you may if you prefer.
I think it's a little more readable to have the tags on separate lines from the text within the tags.

--
Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Dynamic SQL does not work for checking parameter is null.

Ming Xia
Verified and it works, thank you Guy, appreciate your help.

Best,
Ming

On Fri, Apr 26, 2019 at 9:26 PM Guy Rouillier <[hidden email]> wrote:
On Thu, 2019-04-25 at 22:49 -0700, Ming Xia wrote:
Hi,

I was trying to use mybatis dynamic sql, but seems it does not produce correct sql statement when checking if a specific parameter is null. Even `record.description==null`, the generated sql still include set description=?, and the record in db is updated with description=`null`, even it was not null before updating. Any idea on this problem, or it is a bug of MyBatis? My Mybatis version is 3.4.0.

Thanks,
Ming


@Update
({

    
"<script>"
,

      
"update city"
,

      
"<set>"
,

        
"<if test='#{description} != null'>description = #{description,jdbcType=VARCHAR},</if>"
,

      
"</set>"
,

      
"where name = #{name,jdbcType=VARCHAR}"
,

    
"</script>"

})

@Override

int
 
update
(
CityDO
 record
)
;

My `CityDO` has only `name` and `description` field, and I verified that the description field is null before passing it to the `update` method.

Expressions within the tags - <if>, in this case - are Java expressions (specifically, OGNL expressions), not parameter expressions. So, the proper test here would be:

<pre>
<if test='description != null'>
description = #{description,jdbcType=VARCHAR},
</if>
</pre>

You don't have to have the entire clause on a single line, though you may if you prefer.
I think it's a little more readable to have the tags on separate lines from the text within the tags.

--
Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.