0

I am trying to insert a HashMap into database columns using mybatis. My code is as shown below

@Insert({ 
        "<script>",     
        "INSERT INTO msg_monitored_grp(",
            "message_id, ",
            "monitored_grp_id, ",
            "monitored_grp_name,",
            "record_creation_date) ",
            "VALUES ", 
            "<foreach collection='monitoredGroupsMap' index='key' item='value' ",
                        "separator=','>",
                    "(#{messageId}, ",
                    "#{key}, ",
                    "#{value},",
                    "#{recordCreationDate})",
            "</foreach>",
     "</script>"     
    })
    void insert(MessageMonitoredGroup monitoredGroupsMap);

I get the below error:

### The error may exist in com/db/lrc/commsurv/alert/recordstore/consumer/persistence/MessageMonitoredGroupMapper.java (best guess)
### The error may involve com.db.lrc.commsurv.alert.recordstore.consumer.persistence.MessageMonitoredGroupMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO msg_monitored_grp( message_id,  monitored_grp_id,  monitored_grp_name, record_creation_date)  VALUES     (?,  ?,  ?, ?)  ,  (?,  ?,  ?, ?)  ,  (?,  ?,  ?, ?)  ,  (?,  ?,  ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.1.RELEASE.jar:5.2.1.RELEASE]
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at com.sun.proxy.$Proxy83.insert(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272) ~[mybatis-spring-2.0.6.jar:2.0.6]
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.6.jar:3.5.6]
    at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152) ~[mybatis-3.5.6.jar:3.5.6]
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[mybatis-3.5.6.jar:3.5.6]
    at com.sun.proxy.$Proxy96.insert(Unknown Source)

Please can you tell what is wrong here.

2
  • 1
    Oracle does not support multi-row insert (you should check the SQL before implementing it in MyBatis 😉). You can use INSERT ALL ... SELECT instead. Note that it is recommended to use batch executor when there are many entries to insert for performance reasons. Commented Jul 18, 2021 at 4:22
  • Yep, that's not a valid syntax in Oracle. This is not an issue with MyBatis, but with the SQL you are producing. Commented Jul 18, 2021 at 4:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.