Getting BuilderException for null values [Mybatis-Spring Boot]

[Purbarun Chakrabarti]

Team,

I am trying to execute a dynamic like below:

Case 1: If year has data

SELECT * 

FROM books
WHERE id=1
   OR
publicaton_year in (2005,2006)

Case 2: If year is null or empty

SELECT * 

FROM books
WHERE id=1

The code is as follows:

@Mapper

public interface BookMapper {

List<Book> searchBook(@Param("id") Long title,@Param("year") List<Integer> year);

}

select id="searchBook" resultMap="Book">

SELECT * FROM books

WHERE id=#{id}

OR

<foreach item="item" index="index" collection="year"

open="publication_year in (" separator="," close=")" >

#{item}

</foreach>

</select>

When I don`t have any values for year its giving the below error.

### Error querying database. Cause: org.apache.ibatis.builder.BuilderException: The expression 'year' evaluated to a null value.

### Cause: org.apache.ibatis.builder.BuilderException: The expression 'year' evaluated to a null value.] with root cause

org.apache.ibatis.builder.BuilderException: The expression 'year' evaluated to a null value.

2024-11-28T21:15:28.441+05:30 [0;39m [32mDEBUG [0;39m [35m496 [0;39m [2m--- [dynamic-sql-demo-2] [nio-8080-exec-3] [0;39m [36mo.p.m.mapper.BookMapper.searchBook [0;39m [2m: [0;39m ==> Preparing: SELECT * FROM books WHERE id=? OR

[2m2024-11-28T21:15:28.526+05:30 [0;39m [31mERROR [0;39m [35m496 [0;39m [2m--- [dynamic-sql-demo-2] [nio-8080-exec-3] [0;39m [36mo.a.c.c.C.[.[.[/].[dispatcherServlet] [0;39m [2m: [0;39m Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.BadSqlGrammarException:

### Error querying database. Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT * FROM books \000a\0009 \0009WHERE id=?\000a\0009\0009\0009OR[*]"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:

SELECT * FROM books

WHERE id=?

OR [42001-232]

### The error may exist in file [D:\workspcace\mybatis\dynamic-sql-demo-2\bin\main\mappers\BookMapper.xml]

### The error may involve org.purbarun.mybatis.mapper.BookMapper.searchBook

The interesting thing, in ibatis it can be executed like this but its not happening in mybatis.

ibatis:

where <condition-1>

<dynamic prepend='OR'>

<iterate property="" open="" close="" conjunction="">

</iterate>

</dynamic>

Can anyone kindly help here pls!

Code files attached herewith.

[Iwao AVE!]

Hello Purbarun,

It would be better if you put the OR in the open attribute.
With the latest version, you can write as follows.

<select id="searchBook" resultMap="Book">

  SELECT * FROM books
  WHERE id=#{id}
  <foreach item="item" index="index" collection="year" nullable="true"
      open="or publication_year in (" separator="," close=")" >
    #{item}
  </foreach>
</select>

The nullable was introduced in 3.5.9, so if you are using an older version, you may have to use <if />.

<select id="searchBook" resultMap="Book">

  SELECT * FROM books
  WHERE id=#{id}
  <if test="year != null">
    

<foreach item="item" index="index" collection="year"

        open="or publication_year in (" separator="," close=")" >
      #{item}
    </foreach>
  </if>
</select>

Hope this helps,
Iwao

--
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 mybatis-user...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/ab602479-74a0-4fdc-a59a-cea2de51accan%40googlegroups.com.

[Purbarun Chakrabarti]

Yes, it helped and its working!!

But there is one small thing. I am noticing an error in the nullable attribute as given below. Code is executing fine but due to this the project is having an error. I am not sure if its STS IDE issue which will get resolved in IntelliJ. Let me know if you have any idea.  

Thanks,

Purbarun

[Guy Rouillier]

Iwao's message states "The nullable was introduced in 3.5.9".  So make sure you've loaded the correct DTD into your IDE.

--

Guy Rouillier

To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/3a7b5aef-1d9e-4095-87fe-d77f931db9a9n%40googlegroups.com.

[Vishal Pandya]

I guess one should avoid using xml files and have java mapper files. I think it's easier to maintain and understand. Just a thought.

To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/em659102ab-dd5d-4fde-89c5-01c0ac1f59d0%404598d2d0.com.

--

Regards,

Vishal

[Iwao AVE!]

Hi all,

IDEs, without proper configuration, may use the online DTD files (which I forgot to update until now 🙂).

Still, the recommended way, as Guy mentioned, is to configure your IDE to use local DTD files.

Regards,

Iwao

To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/CALK4D07pZ7SQFziMJU_goAeB5dL8AYTo16LjAbs-zktsefxGUw%40mail.gmail.com.

[Purbarun Chakrabarti]

My DTD is picking from the below source:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.5.9//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

Can you point me to the right path for mybatis 3.5.9 please.

Thanks,

Purbarun

[Purbarun Chakrabarti]

This is right thought. But this I am doing for a special purpose as my company project has large xml files that too written in old ibatis, still haven`t migrated yet. It's sad that even around 2025 we are not able to move out of XML. 

Thanks,

Purbarun

[Iwao AVE!]

DOCTYPE is the same for all 3.x versions.
https://mybatis.org/mybatis-3/getting-started.html
Be sure to use https instead of http.

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">

By the way, there is a good chance that your IDE uses the Language Server for XML validation these days.
If that is the case, it may take a while until the DTD change is picked up.

Regards,
Iwao

To view this discussion visit https://groups.google.com/d/msgid/mybatis-user/b483ec86-a9f6-420b-be94-41c2fd988ddcn%40googlegroups.com.

[Purbarun Chakrabarti]

Thanks @Iwao. This worked!! Appreciate the effort.

Regards,

Purbarun