mybatis+oracle, program stuck on insertion
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 '18 at 5:51
Littlefoot
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
add a comment |
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 '18 at 5:51
Littlefoot
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 '18 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 '18 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06
add a comment |
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 '18 at 5:51
Littlefoot
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
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 '18 at 5:51
Littlefoot
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
edited Nov 20 '18 at 5:51
Littlefoot
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
edited Nov 20 '18 at 5:51
Littlefoot
22.3k71533
edited Nov 20 '18 at 5:51
Littlefoot
22.3k71533
edited Nov 20 '18 at 5:51
Littlefoot
22.3k71533
22.3k71533
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
asked Nov 20 '18 at 5:31
Andy LiAndy Li
15
15
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 '18 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 '18 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06
add a comment |
Why do you useforeachandSELECT FROM DUAL?
– Evgeni Enchev
Nov 20 '18 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 '18 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06
Why do you use
foreach and SELECT FROM DUAL?– Evgeni Enchev
Nov 20 '18 at 7:00
Why do you use
foreach and SELECT FROM DUAL?– Evgeni Enchev
Nov 20 '18 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 '18 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 '18 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
add a comment |
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 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
add a comment |
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 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
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 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
edited Nov 20 '18 at 12:53
answered Nov 20 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
answered Nov 20 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
answered Nov 20 '18 at 7:44
Roman KonovalRoman Konoval
7,97612232
7,97612232
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.
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 '18 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 '18 at 7:03
the grammar would be insert into table(...) (select ... from dual) union all (select ... from dual)
– Andy Li
Nov 20 '18 at 7:06