Execute anonymous PL/SQL block and get resultSet in Mybatis

[zhi man]

Hi all,

in our application, we are tring to execute anonymous PL/SQL block and get resultSet in Java code.  We can do it by JDBC，but  have some problems when using Mybatis。

My SQL is as shown，In Mybatis , it can work  with right in parameter , such as  txId，but I can not get its out parameter, such as v_result

How can I get the resultSet or out parameter(the red box in the picture) in Mybatis ?

Thank you very much

Zhiman

[Biao Huang]

You should define the store procedure in the database instead of in the Mapper of MyBatis, then call the store procedure in the Mapper.

zhi man <zhim...@gmail.com> 于2021年1月14日周四 下午6:10写道：

--
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/5fcb5935-4cc4-41c3-9f03-4b8a14fe3000n%40googlegroups.com.

--

=====================================

QQ:     26664141

Skype: biao.mac

EMail:  biao...@gmail.com

Tel: Home:  495312217000
Tel: Mobile: 4917620793369

[zhi man]

PL can not meet our business requirements in transaction control, and PL is difficult to maintain and relatively uncontrollable

[Iwao AVE!]

Hello Zhiman,

It should work if `MainTransDO` has a property `result`.

Please avoid using images to post code. 🙏

Code in an image is not copy-able nor searchable.

Regards,

Iwao

--

[zhi man]

In fact, it can use the in parameter in JDBC and return the correct out parameter. Can mybatis support this?  If it can, please give me an example!  Thank you.

Java code:

JDBC sql

在2021年1月15日星期五 UTC+8 上午8:41:52<biao...@gmail.com> 写道：

[zhi man]

Thank you for your suggestion, the email is not friendly enough for the code format support, so I used pictures, but I will pay attention to it later.

`MainTransDO` has a property `result`，but Mybatis return null。

I suspect there is something wrong with my usage, but I tried all kinds of methods, but I still didn't solve the problem

<select id="lockMainTx" statementType="CALLABLE" parameterType="MainTransDO" resultType="MainTransDO">
DECLARE
p_tx_id varchar2(128) := NULL;
p_sys_tx_id varchar2(128) := NULL;
v_status varchar2(8) := NULL ;

v_tx_id varchar2(128) := NULL;
v_gmt_create timestamp(6) := NULL;
v_result varchar2(256) := 'SUCCESS';
BEGIN
p_tx_id := #{txId, mode=IN};
P_sys_tx_id := #{systemTransactionId, mode=IN};
BEGIN
SELECT TX_ID, STATUS, GMT_CREATE INTO v_tx_id, v_status,v_gmt_create FROM MAIN_TRANSACTION
WHERE TX_ID = p_tx_id AND SYSTEM_TRANSACTION_ID = p_sys_tx_id FOR UPDATE WAIT 3;
EXCEPTION
WHEN OTHERS THEN
v_result := 'LOCK_ERROR: '||SUBSTR(SQLERRM , 1 , 200);
END;

SELECT v_result INTO #{result, mode=OUT, jdbcType=VARCHAR} FROM DUAL;
END;
</select>

[Guy Rouillier]

I found some old working code that did this successfully:

  <insert id="insertTicket" statementType="CALLABLE" parameterType="InsertTicketData">
    BEGIN
      INSERT INTO ticket
      (
        ...
      )
      VALUES
        (
          ...
        )
      RETURNING
        ticket_id
      INTO
        #{ticketId, mode=OUT, jdbcType=INTEGER};
    END;
  </insert>

where ticketId is a field in InsertTicketData with a defined getter and setter.

Looking briefly at your code, it appears to only assign a value to v_result when an exception occurs.  So that may be why your return value is null.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/2af13a74-f84a-4868-b369-4f5a63d9c2cbn%40googlegroups.com.

[zhi man]

I've some new discoveries through debug，I can get the out parameter in MainTransDO.result，but why not ResultDO.result ? 

Although they have the same properties, v_result is set to the variable mainTransDo instead of ResultDo. Why?

<select id="lockMainTx" statementType="CALLABLE" parameterType="MainTransDO" resultType="ResultDO">

DECLARE
p_tx_id varchar2(128) := NULL;
p_sys_tx_id varchar2(128) := NULL;
v_status varchar2(8) := NULL ;

v_tx_id varchar2(128) := NULL;
v_gmt_create timestamp(6) := NULL;
v_result varchar2(256) := 'SUCCESS';
BEGIN
p_tx_id := #{txId, mode=IN};
P_sys_tx_id := #{systemTransactionId, mode=IN};
BEGIN
SELECT TX_ID, STATUS, GMT_CREATE INTO v_tx_id, v_status,v_gmt_create FROM MAIN_TRANSACTION
WHERE TX_ID = p_tx_id AND SYSTEM_TRANSACTION_ID = p_sys_tx_id FOR UPDATE WAIT 3;
EXCEPTION
WHEN OTHERS THEN
v_result := 'LOCK_ERROR: '||SUBSTR(SQLERRM , 1 , 200);
END;

-- var result is set to MainTransDO instead of ResultDO
#{result, mode=OUT, jdbcType=VARCHAR} := v_result;

END;
</select>

[Guy Rouillier]

Because the #{x} strings represent parameter markers.  You can specify whether they are input or output parameters, but they are all parameters, as defined by JDBC.  The object identified by resultType is used by MyBatis to return output values from SQL statements, be that row counts from INSERT, UPDATE or DELETE, or structured data from SELECT.  That mapping of output values to program variables is the purpose of MyBatis, and is *not* JDBC.  So, by definition, when MyBatis encounters a parameter marker, it is working with JDBC parameters, which in the MyBatis framework come from the parameterType object.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/e0000a17-74b7-49ee-89f8-3b1a7ac9838bn%40googlegroups.com.

[Steve Hill]

Hi All:

We are attempting to migrate to mybatis generator 1.4.1.  In doing we
are having to rewrite a number of plugin's as things are somewhat
different now; however we have stumbled into a problem and can't find
the answer.  Wondering if someone knows?

We have overridden

    public boolean clientGenerated(Interface interfaze,
IntrospectedTable introspectedTable) {

And have the following code in it so that in the mapper interface it
will have references to the new XML sql blocks we have added to the
generated XML mappers.

        Method newMethod = new Method(LOGICAL_DELETE_METHOD_NAME);
context.getCommentGenerator().addGeneralMethodComment(newMethod,
introspectedTable);

        FullyQualifiedJavaType recordParam = new
FullyQualifiedJavaType(introspectedTable.getBaseRecordType());
        newMethod.addParameter(new Parameter(recordParam, "record"));

        newMethod.setReturnType(new FullyQualifiedJavaType("int"));
        interfaze.addMethod(newMethod);

In the prior version of the generator we ended up with the following

    int logicalDelete(User record);

But now it is added the following to the interface which doe not compile
as it is an interface.

    int logicalDelete(User record) {
    }

Any help would be appreciated.

Thanks!
Steve.