HI ,
-- I am using MyBatis 3.3.0 with Postgresql 12. I want to inject some pl/pgsql in my xml mapper for example: to insert data with for loop <update id="generateNumbers" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[ declare ..... begin for number in ...... insert into.....(....) values (...) end loop; end; ]]> </update> with oracle and Pl/sql it works fine but I can't convert it to pl/pgsql How can I do it I know I can use stored procedure but my question is how to inject pl/pgsql in xml mapper files if it is possible? Best regards, You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com. |
PostgreSQL also has a FOR loop. See an example here: You can put that loop into an anonymous code block inside your XML mapper. --
Guy Rouillier On 1/29/2020 4:17:26 AM, "'Yosra TR' via mybatis-user" <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus. |
Thank you for your response To test anonymous code block
I tried to delete a row from the table RANGE : <update id="deleteRange" parameterType="tn.tt.nbms.dto.RangeDTO" statementType="CALLABLE"> <![CDATA[ DO $$DECLARE BEGIN delete from RANGE where ID_RANGE = #{idRange}; END $$; </update> it throws an exception : org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='idRange', mode=IN, javaType=class java.math.BigDecimal, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: L'indice de la colonne est hors limite : 1, nombre de colonnes : 0. but if I replace
#{idRange} by a value like 3381 for example it works so how I can set parameters ? can you help me ? Le jeu. 30 janv. 2020 à 05:06, Guy Rouillier <[hidden email]> a écrit :
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com. |
Hello, The doc says: > The code block is treated as though it were the body of a function with no parameters Which means that (unlike Oracle) you cannot pass parameters to the code block. Executing multiple statements with a single execute() call is not portable and usually comes with limitation. I would recommend using batch operation [1][2]. It is a better solution especially when dealing with many rows On Fri, Jan 31, 2020 at 12:50 AM 'Yosra TRABELSI' via mybatis-user <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com. |
thank you very much So what is more performant stored procedure or batch operation ? Le jeu. 30 janv. 2020 à 18:15, Iwao AVE! <[hidden email]> a écrit :
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1NULd-A9tkFSXtDYjjd-5tA4tOZDgVv5F8Ou1NZxgbYtw%40mail.gmail.com. |
In general, stored procedure performs better, I think, but you should compare both with your actual data if the performance is your primary concern. I On Fri, Jan 31, 2020 at 2:29 AM 'Yosra TRABELSI' via mybatis-user <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2T9ieH_oC3d%2B9r8r9eoLsf4Qxorq_cyOF_1uJyuAfwDZA%40mail.gmail.com. |
Thank you Le ven. 31 janv. 2020 à 08:23, Iwao AVE! <[hidden email]> a écrit :
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Nwc9QKeM4XySmWeA%2BNgXs9MJmgPhor%3Do83nJ3zYuA33Q%40mail.gmail.com. |
Free forum by Nabble | Edit this page |