Extending Mybatis for DML Returning capability

Prashant Neginahal

unread,

Jul 6, 2010, 2:00:50 AM7/6/10

to mybatis-user

Hi All,

I am looking at Returning capability in DML statements something like
this.

insert into party_contact (party_id, contact_info_id ) values (?, ?)
returning contact_info_id into ?;

Is it possible in MyBatis 3.0? If Not, can somebody advice me on this
how can we extend MyBatis for achieving this functionality?

Thanks in advance.

Regards,
Prashant

Prashant Neginahal

unread,

Jul 6, 2010, 2:15:58 AM7/6/10

to mybatis-user

I was trying out below query.

<insert id="insert_1" statementType="CALLABLE"
parameterType="test.ContactDdo">
{insert into
SR1CUST1.CONTACT_INFORMATION( CONTACT_TYPE,
CONTACT_MODE,
VALUE)

values(#{contactType,mode=IN,jdbcType=CHAR},#{contactMode,mode=IN,jdbcType=CHAR},
#{value,mode=IN,jdbcType=VARCHAR} returning contact_info_id into
#{contactId,
mode=OUT,jdbcType=BIGINT}) }
</insert>

It gives below error as expected :)
Cause: java.sql.SQLException: Not all return parameters registered

Is there any way i can achieve this? Please advice.

Thanks,
Prashant

Prashant Neginahal

unread,

Jul 6, 2010, 1:54:59 PM7/6/10

to mybatis-user

I tried this and works perfectly with Oracle

<parameterMap class="ContactDdo" id="selectDdo">
<parameter property="contactType" mode="IN"
jdbcType="CHAR" />
<parameter property="contactMode" mode="IN"
jdbcType="CHAR" />
<parameter property="value" mode="IN" jdbcType="VARCHAR" /
>
<parameter property="contactId" mode="OUT"
jdbcType="BIGINT" />
<parameter property="updatedTime" mode="OUT"
jdbcType="TIMESTAMP" />
</parameterMap>

<procedure id="insert" parameterMap="selectDdo">
begin insert into
SR1CUST1.CONTACT_INFORMATION( CONTACT_TYPE, CONTACT_MODE,
VALUE)
values(?,?,?) returning contact_info_id, updated_time
into ?, ?; end;
</procedure>

Is it correct approach? can we do this?Please somebody advice.

Thanks,
Prashant

On Jul 6, 11:15 am, Prashant Neginahal <prashu.n...@gmail.com> wrote:
> I was trying out below query.
>
> <insert id="insert_1" statementType="CALLABLE"
> parameterType="test.ContactDdo">
> {insert into
> SR1CUST1.CONTACT_INFORMATION( CONTACT_TYPE,
> CONTACT_MODE,
> VALUE)
>

> values(#{contactType,mode=IN,jdbcType=CHAR},#{contactMode,mode=IN,jdbcType=CHAR},

> #{value,mode=IN,jdbcType=VARCHAR} returning contact_info_id into
> #{contactId,
> mode=OUT,jdbcType=BIGINT}) }
> </insert>
>
> It gives below error as expected :)
> Cause: java.sql.SQLException: Not all return parameters registered
>
> Is there any way i can achieve this? Please advice.
>
> Thanks,
> Prashant
>
> On Jul 6, 11:00 am, Prashant Neginahal <prashu.n...@gmail.com> wrote:
>
>
>
> > Hi All,
>
> > I am looking at Returning capability in DML statements something like
> > this.
>
> > insert into party_contact (party_id, contact_info_id ) values (?, ?)
> > returning contact_info_id into ?;
>
> > Is it possible in MyBatis 3.0? If Not, can somebody advice me on this
> > how can we extend MyBatis for achieving this functionality?
>
> > Thanks in advance.
>
> > Regards,

> > Prashant- Hide quoted text -
>
> - Show quoted text -

Guy Rouillier

unread,

Jul 6, 2010, 3:18:53 PM7/6/10

to mybati...@googlegroups.com

Yes, I found this old question courtesy of Google then forgot to send to
the list - sorry:

http://old.nabble.com/Oracle---INSERT-UPDATE-RETURNING-INTO-td15772285.html

He ends up using an anonymous PL/SQL block also. The problems with
using a prepared statement are that (1) doing so is an Oracle extension,
not standard JDBC, so you need to use OraclePreparedStatement, and (2)
while the return type indicates the datatype of an individual result,
the actual data passed back by Oracle is a ResultSet; you need to
iterate over it to retrieve your return value.

Since iBATIS doesn't support vendor extensions*, the anonymous PL/SQL
block is the way to go.

* Blue-skying for a moment, statementType is currently restricted to
STATEMENT, PREPARED, or CALLABLE. If we leave that alone for
compatibility, we could define another attribute "extension" that
contains a fully qualified class name that must extend the specified
statementType. Then we might be able to handle vendor extensions in a
clean way (at least simple ones such as the one we are discussing here.)

--
Guy Rouillier

Prashant Neginahal

unread,

Jul 7, 2010, 5:24:45 AM7/7/10

to mybatis-user

Thank you for the reply.

Thanks,
Prashant

On Jul 7, 12:18 am, Guy Rouillier <g...@burntmail.com> wrote:
> Yes, I found this old question courtesy of Google then forgot to send to
> the list - sorry:
>