Mybatis insert error cause: Java sql. SQLException: SQL String cannot be empty

1. Error description

Scenario reproduction: when using mybatis to import a list for batch insertion, the code is as follows:

mapper

void insertTest(List<Test> list);

mapper.xml

<insert id="insertTest" parameterType="java.util.List">
	<if test="list != null and list.size() > 0"> 
		INSERT INTO test (test1, test2)
                VALUES
                <foreach collection="list" index="index" item="item" separator=",">
                    (#{item.test1}, #{item.test2})
                </foreach>
	</if>
</insert>

The reason for the error is that the list is passed in The list with size () 0 causes the SQL statement to be empty and an error is reported

Solution:

1. Make non empty judgment (list! = null &&! List. Isempty()) before using mapper, and set mapper If statement removal in XML

2. Use the choose, when and otherwise tags to judge. If it is empty, give a statement to query the empty string

Specific examples are as follows

	<insert id="insertTest" parameterType="java.util.List">
        <choose>
            <when test="list != null and list.size() > 0">
                INSERT INTO test (test1, test2)
                VALUES
                <foreach collection="list" index="index" item="item" separator=",">
                    (#{item.test1}, #{item.test2})
                </foreach>
            </when>
            <otherwise>
                select ""
            </otherwise>
        </choose>

    </insert>

If the scenario needs to implement the insert statement multiple times, it will not be elegant to judge the space multiple times in the code. You can consider using the following solutions for reference only. If there are better methods, you can exchange and discuss them