mybatis+oracle, program stuck on insertion
up vote
0
down vote
favorite
I'm writing a program with Java and myBatis. The program gets stuck with the following insertion with no error message. I checked Oracle and no data was inserted. I'm clueless why this is the case. Normally there would be some sort of error from either the sql or myBatis.
id type number(10)
id_card_no varchar2(50)
gene_info varchar2(2000)
If more information is required, ask away. Thanks!
<insert id="addGenotype" parameterType="genotype" useGeneratedKeys="false" >
insert into person_genotype (ID,ID_CARD_NO,GENE_INFO)
<foreach collection="list" item="genotype" index="index" separator="UNION ALL">
(select
#{genotype.genotypeId},#{genotype.idCardNumber},#{genotype.geneInfo}
FROM DUAL)
</foreach>
</insert>
java sql oracle optimization mybatis
edited Nov 20 at 5:51
Littlefoot
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
add a comment |
up vote
0
down vote
favorite
I'm writing a program with Java and myBatis. The program gets stuck with the following insertion with no error message. I checked Oracle and no data was inserted. I'm clueless why this is the case. Normally there would be some sort of error from either the sql or myBatis.
id type number(10)
id_card_no varchar2(50)
gene_info varchar2(2000)
If more information is required, ask away. Thanks!
<insert id="addGenotype" parameterType="genotype" useGeneratedKeys="false" >
insert into person_genotype (ID,ID_CARD_NO,GENE_INFO)
<foreach collection="list" item="genotype" index="index" separator="UNION ALL">
(select
#{genotype.genotypeId},#{genotype.idCardNumber},#{genotype.geneInfo}
FROM DUAL)
</foreach>
</insert>
java sql oracle optimization mybatis
edited Nov 20 at 5:51
Littlefoot
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 at 7:00
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I'm writing a program with Java and myBatis. The program gets stuck with the following insertion with no error message. I checked Oracle and no data was inserted. I'm clueless why this is the case. Normally there would be some sort of error from either the sql or myBatis.
id type number(10)
id_card_no varchar2(50)
gene_info varchar2(2000)
If more information is required, ask away. Thanks!
<insert id="addGenotype" parameterType="genotype" useGeneratedKeys="false" >
insert into person_genotype (ID,ID_CARD_NO,GENE_INFO)
<foreach collection="list" item="genotype" index="index" separator="UNION ALL">
(select
#{genotype.genotypeId},#{genotype.idCardNumber},#{genotype.geneInfo}
FROM DUAL)
</foreach>
</insert>
java sql oracle optimization mybatis
edited Nov 20 at 5:51
Littlefoot
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
I'm writing a program with Java and myBatis. The program gets stuck with the following insertion with no error message. I checked Oracle and no data was inserted. I'm clueless why this is the case. Normally there would be some sort of error from either the sql or myBatis.
id type number(10)
id_card_no varchar2(50)
gene_info varchar2(2000)
If more information is required, ask away. Thanks!
<insert id="addGenotype" parameterType="genotype" useGeneratedKeys="false" >
insert into person_genotype (ID,ID_CARD_NO,GENE_INFO)
<foreach collection="list" item="genotype" index="index" separator="UNION ALL">
(select
#{genotype.genotypeId},#{genotype.idCardNumber},#{genotype.geneInfo}
FROM DUAL)
</foreach>
</insert>
java sql oracle optimization mybatis
java sql oracle optimization mybatis
edited Nov 20 at 5:51
Littlefoot
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
edited Nov 20 at 5:51
Littlefoot
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
edited Nov 20 at 5:51
Littlefoot
19.1k71333
edited Nov 20 at 5:51
Littlefoot
19.1k71333
edited Nov 20 at 5:51
Littlefoot
19.1k71333
19.1k71333
asked Nov 20 at 5:31
Andy Li
35
asked Nov 20 at 5:31
Andy Li
35
asked Nov 20 at 5:31
Andy Li
35
35
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 at 7:00
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06
add a comment |
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 at 7:00
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06
Why do you use
foreach and SELECT FROM DUAL?– Evgeni Enchev
Nov 20 at 7:00
Why do you use
foreach and SELECT FROM DUAL?– Evgeni Enchev
Nov 20 at 7:00
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Most probably there's another session to the database that inserted the row with the same primary key but has not committed yet. It is a rather common scenario to have SQL*Plus running with some uncommitted changes.
In Oracle if two concurrent transaction insert into the same table the row that conflicts on some unique key one of them would block until the other either commit or rollback.
One of the way to overcome this is to automatically generate identifiers for rows using a sequence. In this case every new record would get its own brand-new unique ID.
answered Nov 20 at 7:44
Roman Konoval
7,55812131
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Most probably there's another session to the database that inserted the row with the same primary key but has not committed yet. It is a rather common scenario to have SQL*Plus running with some uncommitted changes.
In Oracle if two concurrent transaction insert into the same table the row that conflicts on some unique key one of them would block until the other either commit or rollback.
One of the way to overcome this is to automatically generate identifiers for rows using a sequence. In this case every new record would get its own brand-new unique ID.
answered Nov 20 at 7:44
Roman Konoval
7,55812131
add a comment |
up vote
1
down vote
Most probably there's another session to the database that inserted the row with the same primary key but has not committed yet. It is a rather common scenario to have SQL*Plus running with some uncommitted changes.
In Oracle if two concurrent transaction insert into the same table the row that conflicts on some unique key one of them would block until the other either commit or rollback.
One of the way to overcome this is to automatically generate identifiers for rows using a sequence. In this case every new record would get its own brand-new unique ID.
answered Nov 20 at 7:44
Roman Konoval
7,55812131
add a comment |
up vote
1
down vote
up vote
1
down vote
Most probably there's another session to the database that inserted the row with the same primary key but has not committed yet. It is a rather common scenario to have SQL*Plus running with some uncommitted changes.
In Oracle if two concurrent transaction insert into the same table the row that conflicts on some unique key one of them would block until the other either commit or rollback.
One of the way to overcome this is to automatically generate identifiers for rows using a sequence. In this case every new record would get its own brand-new unique ID.
answered Nov 20 at 7:44
Roman Konoval
7,55812131
Most probably there's another session to the database that inserted the row with the same primary key but has not committed yet. It is a rather common scenario to have SQL*Plus running with some uncommitted changes.
In Oracle if two concurrent transaction insert into the same table the row that conflicts on some unique key one of them would block until the other either commit or rollback.
One of the way to overcome this is to automatically generate identifiers for rows using a sequence. In this case every new record would get its own brand-new unique ID.
answered Nov 20 at 7:44
Roman Konoval
7,55812131
edited Nov 20 at 12:53
answered Nov 20 at 7:44
Roman Konoval
7,55812131
answered Nov 20 at 7:44
Roman Konoval
7,55812131
answered Nov 20 at 7:44
Roman Konoval
7,55812131
7,55812131
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53386791%2fmybatisoracle-program-stuck-on-insertion%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why do you use
foreachandSELECT FROM DUAL?– Evgeni Enchev
Nov 20 at 7:00
@EvgeniEnchev Initially I used values instead of select from dual, comma instead of union all as separator, but I found out it would work with mysql but not oracle. I learned this way from another post.
– Andy Li
Nov 20 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 at 7:06