0

Is using foreach attribute in mybatis/ibatis for oracle sql updates a best practice? Below is my query in the sql map.

<update id="updateFG"  parameterClass="java.util.Map">
    
        <foreach collection="entries.entrySet()" item="item" index="index" >
            UPDATE <<tablename>>
            SET description = #{item.value},
                last_mod_date= SYSDATE
            WHERE name = #{item.key}
        </foreach>
        
    </update>

When I try to run this piece of code it is throwing me an error:

Error parsing XML. Cause: org.xml.sax.SAXParseException; lineNumber: 49; columnNumber: 72; Element type "foreach" must be declared.

HookUp
  • 393
  • 1
  • 6
  • 20
  • 1
    It seems like a wrong DOCTYPE. See the [doc](https://mybatis.org/mybatis-3/getting-started.html#Exploring_Mapped_SQL_Statements). If that is not the case, check if there is any invisible control characters in the XML. – ave Jun 18 '21 at 19:07
  • Thanks @ave, I could possibly dig more into this based on your comment. – HookUp Jun 18 '21 at 22:40

1 Answers1

0

Okay so when I changed my DCOTYPE from sqlmap to mapper - it worked fine.. I think foreach cannot be used for sqlMap..

EDIT: realized the foreach is not efficient for multiple rows so used batch instead

HookUp
  • 393
  • 1
  • 6
  • 20
  • 1
    Oh, I forgot about the 'best practice' part of your question. The best practice is to perform batch operation instead of ``. See this [answer](https://stackoverflow.com/a/58914577/1261766). If you use Spring Boot, this [demo](https://github.com/jeffgbutler/mybatis-cockroach-demo) shows you how to setup batch and non-batch mappers. – ave Jun 19 '21 at 04:29
  • oh thanks for that.. but in our code we were not explicitly writing any code for committing to DB nor playing around with sqlSession.. Is using foreach considered a bad practice? I have seen people using foreach for batch updates as well.. – HookUp Jun 21 '21 at 19:25
  • 1
    If you use Spring, you don't have to manage SqlSession explicitly. See the demo that I linked. Using `` generates a single big string with a lot of parameter placeholders which performs poorly if there are many rows/columns to insert/update. Batch executor, OTOH, utilizes JDBC's batch operation API which is designed to process many rows efficiently. BTW, the SQL generated by `` is called [multi-row insert](https://en.wikipedia.org/wiki/Insert_(SQL)#Multirow_inserts) to avoid confusion. – ave Jun 21 '21 at 20:03
  • okay great explanation thank you.. we are not using springboot in our app so I think I will have to explicitly open the session in BATCH.. BTW, from your answer [here](https://stackoverflow.com/a/58914577/1261766) sqlSession.flushStatements() [end of the loop] is committing the changes to database before the .commit() statement for me..? do you know why and how we can avoid this? FYI, I am using/declaring the session as openSession(ExecutorType.BATCH, false) – HookUp Jun 22 '21 at 01:13
  • If you use [mybatis-spring](https://mybatis.org/spring/getting-started.html) or [mybatis-spring-boot-starter](https://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/), you should not call `openSession` directly in your code. Please read the docs...or remove Spring and manage session manually. :D – ave Jun 22 '21 at 15:55
  • yeah we are using mybatis-spring in our application.. why is that we should not be calling openSession directly in our code? is it because the MapperFactoryBean already handling a session and we dont want to have another session? Also rather than opening session what if I configure this in my mapper config xml file? - it would still work for single/multiple statements right? – HookUp Jun 22 '21 at 16:11
  • 1
    Regarding how to obtain SqlSession, read this [page](https://mybatis.org/spring/sqlsession.html). You should read other pages, too, though. – ave Jun 22 '21 at 19:16
  • oh yes sorry forgot to update.. I am able to get the session using SqlSessionTemplate.. thank you all your answers.. one last thing: do you know why flushStatements commits the changes to database before the transactionManager commits itself? – HookUp Jun 22 '21 at 19:28
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234083/discussion-between-hookup-and-ave). – HookUp Jun 22 '21 at 19:30