CODEWITHSUNDEEP
mysqlmybatisspring-mybatismybatis-mapperbadsqlgrammarexception
footlessbird
footlessbird

Reputation: 355

MyBatis xml update query using set tag throws org.springframework.jdbc.BadSqlGrammarException

Built update query in MyBatis xml but no luck to pass BadSqlGrammarException

Here's my query

    <update id="updateRecordingVideoStatus">
        UPDATE
            game_record_metadata
            <set>
                <if test="modifiedVideoStatus = null">
                        status = #{originalVideoStatus}
                </if>
                <if test="modifiedVideoStatus != null">
                        status = #{modifiedVideoStatus}
                </if>
            </set>
        WHERE id = #{gameRecordMetadataId}
        AND game_id = #{gameId}
    </update>

I've tried below(without set tag) but not working

                <if test="modifiedVideoStatus = null">
                       SET status = #{originalVideoStatus}
                </if>
                <if test="modifiedVideoStatus != null">
                       SET status = #{modifiedVideoStatus}
                </if>

EDIT

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5
### The error may exist in file [/Users/asd/admin-api/build/resources/main/mybatis/rel/game_recording.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE             game_record_metadata                                     WHERE id = ?         AND game_id = ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 2
        AND game_id = 204' at line 5

What would be the correct one for it?

Thanks in advance.

Upvotes: 2

Views: 711

Answers (2)

footlessbird
footlessbird

Reputation: 355

Somehow, test: in if tag does not work with null but not null and it's ignored then caused BadSqlGrammarException as SET statements do not exist in that update query.

<if test="modifiedVideoStatus = null"> /* assignment(NG) */
     status = #{originalVideoStatus}
</if>

So I passed a Boolean parameter as flag so that it can catch firt if.

-- solved
 <set>
            <if test="isModifiedVideoStatusNull">
                status = #{originalVideoStatus}
            </if>
            <if test="!isModifiedVideoStatusNull">
                status = #{modifiedVideoStatus}
            </if>
        </set>

As ave mentioned

EDIT

<if test="modifiedVideoStatus = null"> /* incorrect */      
     status = #{originalVideoStatus}
</if>

<if test="modifiedVideoStatus == null"> /* correct */      
     status = #{originalVideoStatus}
</if>

Upvotes: 1

Maksim Eliseev
Maksim Eliseev

Reputation: 1363

The problem is that both <if> statements are not selected. That is, both conditions in the test="..." are false. And the query beacame incorrect:

UPDATE game_record_metadata
 WHERE id = #{gameRecordMetadataId}
   AND game_id = #{gameId}

It happened because you use unexistied table columns in the test:

  • test="modifiedVideoStatus = null"
  • test="modifiedVideoStatus != null"

You should use the column names in the test condition first rather than passed parameters. Fortunately, there are solutions to this problem, but I am not sure that will work:

They look confusing and non-obvious.

I suggest you check the originalVideoStatus and modifiedVideoStatus parameters in the code and use the one general query to update the game_record_metadata.status column.

<update id="updateGameRecordMetadataStatus">
    UPDATE game_record_metadata
       SET status = #{newStatus}
     WHERE id = #{gameRecordMetadataId}
       AND game_id = #{gameId}
</update>

Upvotes: 2

Related Questions