Question 249967 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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"> /* null is not working(NG) */
     status = #{originalVideoStatus}
</if>
<if test="modifiedVideoStatus != null"> /* works(OK) */
     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>

  2. 0

    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>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search