如何使用Ibatis返回带有插入内容的ID(使用RETURNING关键字) [英] Howto return ids on Inserts with Ibatis ( with RETURNING keyword )
问题描述
我正在使用iBatis / Java和Postgres 8.3。
当我在ibatis中插入时,我需要返回id。
我使用下表来描述我的问题:
CREATE TABLE sometable (id serial NOT NULL,somefield VARCHAR(10));
序列 sometable_id_seq
通过运行create语句自动生成。
I'm using iBatis/Java and Postgres 8.3.
When I do an insert in ibatis i need the id returned.
I use the following table for describing my question:
CREATE TABLE sometable ( id serial NOT NULL, somefield VARCHAR(10) );
The Sequence sometable_id_seq
gets autogenerated by running the create statement.
目前我使用以下sql地图:
At the moment i use the following sql map:
<insert id="insertValue" parameterClass="string" >
INSERT INTO sometable ( somefield ) VALUES ( #value# );
<selectKey keyProperty="id" resultClass="int">
SELECT last_value AS id FROM sometable_id_seq
</selectKey>
</insert>
这似乎是检索新插入的id的ibatis方式。 Ibatis首先运行一个INSERT语句,然后它询问序列的最后一个id。
我怀疑这将适用于许多并发插入。 (在此问题中讨论)
It seems this is the ibatis way of retrieving the newly inserted id. Ibatis first runs a INSERT statement and afterwards it asks the sequence for the last id.
I have doubts that this will work with many concurrent inserts. ( discussed in this question )
我想在ibatis中使用以下语句:
INSERT INTO sometable(somefield)VALUES(#value #)RETURNING id;
I'd like to use the following statement with ibatis:
INSERT INTO sometable ( somefield ) VALUES ( #value# ) RETURNING id;
但是当我尝试在< insert> $ c中使用它时$ c> sqlMap ibatis不返回id。它似乎需要
< selectKey>
标记。
But when i try to use it within a <insert>
sqlMap ibatis does not return the id. It seems to need the <selectKey>
tag.
所以问题出现了:
我如何在ibatis上使用上述声明?
How can i use the above statement with ibatis?
推荐答案
< selectKey>
元素是< insert>
元素的子元素,其内容在之前执行主 INSERT
语句。您可以使用两种方法。
The <selectKey>
element is a child of the <insert>
element and its content is executed before the main INSERT
statement. You can use two approaches.
在插入记录后获取密钥
这种方法取决于您的驱动程序。线程可能是一个问题。
This approach works depending on your driver. Threading can be a problem with this.
在插入记录之前获取密钥
这种方法避免了线程问题,但更多的工作。示例:
This approach avoids threading problems but is more work. Example:
<insert id="insert">
<selectKey keyProperty="myId"
resultClass="int">
SELECT nextVal('my_id_seq')
</selectKey>
INSERT INTO my
(myId, foo, bar)
VALUES
(#myId#, #foo#, #bar#)
</insert>
在Java方面你可以做到
On the Java side you can then do
Integer insertedId = (Integer) sqlMap.insert("insert", params)
这应该为您提供从 my_id_seq
序列中选择的密钥。
This should give you the key selected from the my_id_seq
sequence.
这篇关于如何使用Ibatis返回带有插入内容的ID(使用RETURNING关键字)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!