mybatis 3 + pl/Pgsql

[Yosra TR]

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,

[Guy Rouillier]

PostgreSQL also has a FOR loop.  See an example here:

www.postgresqltutorial.com/plpgsql-loop-statements/

You can put that loop into an anonymous code block inside your XML mapper.

https://www.postgresql.org/docs/9.0/sql-do.html

--

Guy Rouillier

--
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 mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2be8db7e-14ce-480b-832d-fba2f9828996%40googlegroups.com.

[Yosra TRABELSI]

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 ?

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/emf29bb680-807c-4b77-a483-a7f51fa93472%40asus.

[Iwao AVE!]

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

[1] https://github.com/mybatis/mybatis-3/wiki/FAQ#how-do-i-code-a-batch-insert

[2] https://stackoverflow.com/a/55518327/

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1Ng10bYkhhgXy%2BYBxhdVJghqfmrR0ocaqcxDqS_y-zJoQ%40mail.gmail.com.

[Yosra TRABELSI]

  thank you very much 

So what is more performant stored procedure or batch operation ?

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2S35b3AAM2vEHoBSd-V_3rrp0abGMeaUtXNqx4dLHgsSg%40mail.gmail.com.

[Iwao AVE!]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAAjYA1NULd-A9tkFSXtDYjjd-5tA4tOZDgVv5F8Ou1NZxgbYtw%40mail.gmail.com.

[Yosra TRABELSI]

Thank you 

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.