E.Yusipiv E.Yusipiv -4 years ago 139
Java Question

Something strange with mybatis and isnull on BigDecimal with Sybase

Day ago I found something strange behavior in my code after adding new implementation of interface. I see mybatis update return -1 always, and table not updated.
Log told me about some interesting behavior:

  • Creating a new SqlSession

  • SqlSession [...] was not registered for synchronization because synchronization is not active

  • JDBC Connection [...] will not be managed by Spring

  • ==> Preparing: update Card set amount=isnull(?, amount), name=isnull(?, name), balanceTime=isnull(?, balanceTime) where number=? and clientId=?.

  • ==> Parameters: 0.00(BigDecimal), Test Card(String), 2017-02-22 09:05:24.78(Timestamp), 0000000000000000(String), 111000(Long)

  • Closing non transactional SqlSession [...]

After sending parameters to server nothing happened.
This happens after my DAO was refactored :

public class DBaseCard{
private long id;
private String number;
private String name;
private BigDecimal amount; // was Double

In production I use Sybase IQ. For test I use H2 db and for it, all works fine.

If I change "amount" field type to Double, all works fine.

If I leave BigDecimal type and replace "amount=isnull(#{card.amount}, amount)" to "amount=#{card.amount}" or something like "amount=isnull(0.00, amount)" all works fine, and in log I see:

  • ==> Parameters: 0.0(BigDecimal), ....

  • <== Updates: 1

Please, help me understand why this happen.

Answer Source

It could be related to the type of the column in Sybase, H2 might have a more permissive behavior.

Check documentation on Mybatis default type handlers:

With BigDecimal:

BigDecimalTypeHandler java.math.BigDecimal Any compatible NUMERIC or DECIMAL

With Double:

DoubleTypeHandler java.lang.Double, double Any compatible NUMERIC or DOUBLE

It could come from isnull function. You can try to replace with coalesce.

Even though in the log, you see 0.00(BigDecimal), 0.00 is only the String representation of a Java BigDecimal and the value actually bound by the driver could be homogeneous to null for example 0, and sometimes 0==null whereas 0!==null, that would explain why hard coded 0.00 works as expected.

Writing a stored procedure is option.

You may also consider using Mybatis dynamic SQL (trim, where, set) to build the SET, so that you don't have to rely on isnull anymore:

      <if test="amount!= null">amount=#{amount},</if>
      <if test="name!= null">name=#{name},</if>
      <if test="balanceTime!= null">balanceTime=#{balanceTime} </if>
WHERE number=#{number} AND clientId=#{clientId}

If you cannot guaranty there will be something in the SET (at least 1 not null parameter), the the <set> must contain (on last line) something neutral (no actual update), like: id=id.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download