CODEWITHSUNDEEP
javamybatis
Marcos Tanaka
Marcos Tanaka

Reputation: 3336

MyBatis NumberFormatException while executing query

Using MyBatis, I have a SQL that receives as parameter the String "fr.id_lotacao in (3007, 3008, 3009, 3010)"

SQL:

...
<if test="idLotacao != -1">
    and ${idLotacao}
</if>
...

I call from Java this way:

getDB1SqlSessionTemplate().selectList("sql", MyBatisUtil.createMap("idLotacao", getIdsLotacao(lotacao)));

Where getIdsLotacao(lotacao) returns the String passed as parameter.

But, when executed, MyBatis throws the error:

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: java.lang.NumberFormatException: For input string: "fr.id_lotacao in (3007, 3008, 3009, 3010)"

When receiving the parameter with $, isn't MyBatis supposed to replace ${idLotacao} with the String "fr.id_lotacao in (3007, 3008, 3009, 3010)"?

What am I doing wrong? What's causing this error?

Upvotes: 3

Views: 8114

Answers (3)

Richard Wiseman
Richard Wiseman

Reputation: 51

I had the same problem described by Aitor and resolved it a different way. I think it's trying to do a numeric comparison because it's parsing 'A' as a char.

So I swapped the single quotes and double quotes to ensure it is being parsed as a String.

<if test='parameter =="A"'> 
AND YOURCOLUMN IS NOT NULL 
</if>

Upvotes: 5

Aitor
Aitor

Reputation: 3429

Sometimes, even if you're comparing strings, and your parameter is also a String, Mybaits return a NFE.

To solve this, you have to replace the following expression:

<if test="parameter =='A'"> 
AND YOURCOLUMN IS NOT NULL 
</if>

for this:

<if test="parameter eq 'A'.toString()"> 
AND YOURCOLUMN IS NOT NULL 
</if>

Upvotes: 10

pinaci
pinaci

Reputation: 353

Actually you are comparing a string with a number ie -1,which is causing number format exception. Put -1 in quotes like '-1' to make it work

Upvotes: 0

Related Questions