Insert into two separate tables in a sngle transaction

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Insert into two separate tables in a sngle transaction

sreekanth.r.vanguru

I have a requirement where I need to do two inserts to two different tables in a single mapper transaction.


My mapper.xml would look something like:

<insert id="VlanIntf" parameterType="VlanIntf">

INSERT interface (device_id ,interface_name)
VALUES ( #{device_id}, {#interface_name});

INSERT interface_vlan (interface_id, vlan_tag)
VALUES (LAST_INSERT_AUTOGEN_KEY(), #{vlan_tag})

</insert>

When I do the first insert into "interface" table, mysql DB would generate autogen-key for a field named "interface_id". Unfortunately "VlanIntf" class doesn't have a Id field (it's a POJO and I can't really change it) where I can store this value, so I am looking for a global variable that can be accessed through a function similar to LAST_INSERT_AUTOGEN_KEY(). Internet search showed me that I can use "KeyProperty" tag to store the  auto-gen value the first insert has generated but as I mentioned I don't have field in the class to store this.


Please let me know any solutions that I can use.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Insert into two separate tables in a sngle transaction

Guy Rouillier-2
What you've outlined wouldn't work anyway.  The statements defined in mapper files are passed to the DBMS (after processing by the appropriate scripting language of course.)  The DBMS SQL processor is expecting a statement, and not a sequence of statements.  If you were to pass the two statements you show below, the DBMS would respond with "INSERT not expected at this time" or maybe "; not expected".

You have a couple options, all variations of the same thing.  You can:

(1) Use multiple independent statements with MyBatis, and surround them with a transaction.

(2) Put your multiple statements into a stored procedure within the database

(3) Put your multiple statements into anonymous code block (BEGIN-END block) in your XML mapper file, and invoke that as a single statement from MyBatis

Your choice may be limited by changes you are allowed to make to the database.

--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/10/2018 9:35:29 PM
Subject: Insert into two separate tables in a sngle transaction

I have a requirement where I need to do two inserts to two different tables in a single mapper transaction.


My mapper.xml would look something like:

<insert id="VlanIntf" parameterType="VlanIntf">

INSERT interface (device_id ,interface_name)
VALUES ( #{device_id}, {#interface_name});

INSERT interface_vlan (interface_id, vlan_tag)
VALUES (LAST_INSERT_AUTOGEN_KEY(), #{vlan_tag})

</insert>

When I do the first insert into "interface" table, mysql DB would generate autogen-key for a field named "interface_id". Unfortunately "VlanIntf" class doesn't have a Id field (it's a POJO and I can't really change it) where I can store this value, so I am looking for a global variable that can be accessed through a function similar to LAST_INSERT_AUTOGEN_KEY(). Internet search showed me that I can use "KeyProperty" tag to store the  auto-gen value the first insert has generated but as I mentioned I don't have field in the class to store this.


Please let me know any solutions that I can use.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Insert into two separate tables in a sngle transaction

sreekanth.r.vanguru
Hi Guy,

Apparently there's a way to do what I'm looking for. We can add a parameter called "Allowmultiqueries" to JDBC url and that will let you do multiple MySQL operations in one transaction.

More details here.
 
But my other question that I asked earlier is to read the LAST_INSERT_ID() from the first insert and use it in the second insert. If you have a field in the POJO that can be used to store this value, we can use KeyProperty and KeyColumn fields and that updates the field n the POJO. But unfortunately I don't have such field available in POJO, so I'm looking for any other way to access the LAST_INSERT_ID(). Any pointers would be appreciated.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re[2]: Insert into two separate tables in a sngle transaction

Guy Rouillier-2
Did you try my suggestion of an anonymous block?  Does it work?

--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/11/2018 7:09:54 PM
Subject: Re: Insert into two separate tables in a sngle transaction

Hi Guy,

Apparently there's a way to do what I'm looking for. We can add a parameter called "Allowmultiqueries" to JDBC url and that will let you do multiple MySQL operations in one transaction.

More details here.
 
But my other question that I asked earlier is to read the LAST_INSERT_ID() from the first insert and use it in the second insert. If you have a field in the POJO that can be used to store this value, we can use KeyProperty and KeyColumn fields and that updates the field n the POJO. But unfortunately I don't have such field available in POJO, so I'm looking for any other way to access the LAST_INSERT_ID(). Any pointers would be appreciated.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Insert into two separate tables in a sngle transaction

Frank Martínez
Hi Sreekanth,
Why not just wrap your VlanIntf parameter inside a decorator with the additional id field and use keyProperty?

Frank.

El vie., 11 de mayo de 2018 8:34 PM, Guy Rouillier <[hidden email]> escribió:
Did you try my suggestion of an anonymous block?  Does it work?

--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/11/2018 7:09:54 PM
Subject: Re: Insert into two separate tables in a sngle transaction

Hi Guy,

Apparently there's a way to do what I'm looking for. We can add a parameter called "Allowmultiqueries" to JDBC url and that will let you do multiple MySQL operations in one transaction.

More details here.
 
But my other question that I asked earlier is to read the LAST_INSERT_ID() from the first insert and use it in the second insert. If you have a field in the POJO that can be used to store this value, we can use KeyProperty and KeyColumn fields and that updates the field n the POJO. But unfortunately I don't have such field available in POJO, so I'm looking for any other way to access the LAST_INSERT_ID(). Any pointers would be appreciated.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Insert into two separate tables in a sngle transaction

sreekanth.r.vanguru
In reply to this post by Guy Rouillier-2

 HI Guy,

I  couldn't find any references to mapper xml anonymous block on the net, could you please point me to some references?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[2]: Insert into two separate tables in a sngle transaction

sreekanth.r.vanguru
In reply to this post by Frank Martínez
HI Frank.

That's a good suggestion, Just want to make sure that I understood you correctly. "Decorator" is nothing but a wrapper class build on top of VlanIntf, correct?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re[4]: Insert into two separate tables in a sngle transaction

Guy Rouillier-2
In reply to this post by sreekanth.r.vanguru
As I said in an earlier reply, an anonymous block is simply a BEGIN-END set of code that gets run as soon as it is encountered.  Oracle, PostgreSQL and MariaDB all support it, don't know about MySQL.  Here's the MariaDB documentation:


--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/14/2018 2:22:50 PM
Subject: Re: Re[2]: Insert into two separate tables in a sngle transaction


 HI Guy,

I  couldn't find any references to mapper xml anonymous block on the net, could you please point me to some references?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[4]: Insert into two separate tables in a sngle transaction

Frank Martínez
Hi Guy, if I understand well, the problem is the return of the generated id, not the multiple statement excecution.

Sreekanth, yes, a decorator is simply a class that extends the original but adds something new and delegates old behaviour to an instance of the original. It is a basic CS concept. You will find a lot of examples in internet.

Cheers,
Frank.

El lun., 14 de mayo de 2018 4:08 PM, Guy Rouillier <[hidden email]> escribió:
As I said in an earlier reply, an anonymous block is simply a BEGIN-END set of code that gets run as soon as it is encountered.  Oracle, PostgreSQL and MariaDB all support it, don't know about MySQL.  Here's the MariaDB documentation:


--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/14/2018 2:22:50 PM
Subject: Re: Re[2]: Insert into two separate tables in a sngle transaction


 HI Guy,

I  couldn't find any references to mapper xml anonymous block on the net, could you please point me to some references?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re[6]: Insert into two separate tables in a sngle transaction

Guy Rouillier-2
Frank, I retained all the messages in this thread.  Sreekanth, on 5/10, said his goal was to perform 2 related inserts in a single transaction.  He needs to pass a generated ID from the first to the second (parent-child).  He does not state a requirement to get any generated IDs back to the calling program.  An anonymous block is one way to achieve this.

--
Guy Rouillier

------ Original Message ------
From: "Frank Martínez" <[hidden email]>
Sent: 5/15/2018 3:02:17 PM
Subject: Re: Re[4]: Insert into two separate tables in a sngle transaction

Hi Guy, if I understand well, the problem is the return of the generated id, not the multiple statement excecution.

Sreekanth, yes, a decorator is simply a class that extends the original but adds something new and delegates old behaviour to an instance of the original. It is a basic CS concept. You will find a lot of examples in internet.

Cheers,
Frank.

El lun., 14 de mayo de 2018 4:08 PM, Guy Rouillier <[hidden email]> escribió:
As I said in an earlier reply, an anonymous block is simply a BEGIN-END set of code that gets run as soon as it is encountered.  Oracle, PostgreSQL and MariaDB all support it, don't know about MySQL.  Here's the MariaDB documentation:


--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/14/2018 2:22:50 PM
Subject: Re: Re[2]: Insert into two separate tables in a sngle transaction


 HI Guy,

I  couldn't find any references to mapper xml anonymous block on the net, could you please point me to some references?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[6]: Insert into two separate tables in a sngle transaction

Frank Martínez
Guy, maybe I am wrong, but I see two explicit questions in the first message: how to pass the generated id from the first statement to the second, and how to store the generated id. Your answer is about the fisrt one and mine is about the second one. 

Cheers,
Frank.

El mar., 15 de mayo de 2018 8:08 PM, Guy Rouillier <[hidden email]> escribió:
Frank, I retained all the messages in this thread.  Sreekanth, on 5/10, said his goal was to perform 2 related inserts in a single transaction.  He needs to pass a generated ID from the first to the second (parent-child).  He does not state a requirement to get any generated IDs back to the calling program.  An anonymous block is one way to achieve this.

--
Guy Rouillier

------ Original Message ------
From: "Frank Martínez" <[hidden email]>
Sent: 5/15/2018 3:02:17 PM
Subject: Re: Re[4]: Insert into two separate tables in a sngle transaction

Hi Guy, if I understand well, the problem is the return of the generated id, not the multiple statement excecution.

Sreekanth, yes, a decorator is simply a class that extends the original but adds something new and delegates old behaviour to an instance of the original. It is a basic CS concept. You will find a lot of examples in internet.

Cheers,
Frank.

El lun., 14 de mayo de 2018 4:08 PM, Guy Rouillier <[hidden email]> escribió:
As I said in an earlier reply, an anonymous block is simply a BEGIN-END set of code that gets run as soon as it is encountered.  Oracle, PostgreSQL and MariaDB all support it, don't know about MySQL.  Here's the MariaDB documentation:


--
Guy Rouillier

------ Original Message ------
To: "mybatis-user" <[hidden email]>
Sent: 5/14/2018 2:22:50 PM
Subject: Re: Re[2]: Insert into two separate tables in a sngle transaction


 HI Guy,

I  couldn't find any references to mapper xml anonymous block on the net, could you please point me to some references?

sreekanth
 

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.

--
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].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Re[6]: Insert into two separate tables in a sngle transaction

sreekanth.r.vanguru
Hi Frank and Guy,
Thanks a lot for your answers. Yes I had two questions. Both of you answered one each. Guy, looks like mysql can take multiple statements with just; as a delimiter (all block references point me to this) but with mybatis I couldn't get it to work. The only way I could get it to work is to set "allowmultiqueries" in jdbc url.

Sreekanth

--
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].
For more options, visit https://groups.google.com/d/optout.