java -postgresql在插入时最后插入的id不能获取 [英] java -postgresql last inserted id on insertion not getting
问题描述
我有一个插入postgresql
的功能,如下所示:
I have a function for insertion in postgresql
as follows:
CREATE OR REPLACE FUNCTION insert_orderhead(order_id integer, order_dt text, customer_id integer, route_id integer, routenum integer, ordertype text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer) RETURNS integer AS $BODY$
INSERT INTO ordermaster
VALUES(DEFAULT,
order_dt,
customer_id,
route_id,
routenum,
ordertype,
create_station_id,
create_stationtype,
create_time,
create_user_id,
tran_time,
tran_user_id) returning order_id $BODY$ LANGUAGE SQL VOLATILE COST 100;
ALTER FUNCTION insert_orderhead(integer, text, integer, integer, integer, text, integer, text, text, integer, text, integer) OWNER TO postgres;
我正在使用Java进行插入,并且调用如下:
And I am using java to insert and I am calling as follows:
cstorderhead = conn.prepareCall("{call insert_orderhead(?,?)}");
cstorderhead.setString(1, order_date);
cstorderhead.setInt(2, custidup);
.........
cstorderhead.executeUpdate();
该值已正确插入.
我需要返回最后插入的ID ,这里是串行自动增量.
I need to return the last inserted id here it is the serial auto increment.
我尝试如下:
ResultSet rstd = cstorderhead.getGeneratedKeys();
if (rstd.next()) {
int newId = rstd.getInt(1);
out.print("Value returned=="+newId);
}
但是它不返回最后插入的ID.我需要对代码进行哪些更改才能得到它?
But it is not returning the last inserted id. What change I need to make my code to get it?
这是表的定义:
create table ordermaster
(
order_id serial NOT NULL, order_dt text, customer_id integer, route_id integer, routenum integer, ordertype text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer, CONSTRAINT order_id PRIMARY KEY (order_id)
);
推荐答案
您不能将getGeneratedKeys()
与CallableStatement
一起使用.但是,由于您的insert
在函数中被隐藏",因此您也不能将常规的PreparedStatement
与getGeneratedKeys()
一起使用,因为驱动程序会将RETURNING
子句附加到SQL语句中-不适用于函数调用.
You can't use getGeneratedKeys()
with a CallableStatement
. However as your insert
is "hidden" in the function, you also can't use a regular PreparedStatement
with getGeneratedKeys()
because the driver will append a RETURNING
clause to the SQL statement - which doesn't work with a function call.
我看到了两种解决您的问题的方法:
I see two solutions to your problem:
CREATE OR REPLACE FUNCTION insert_orderhead(
p_order_id integer,
p_order_dt text,
p_customer_id integer,
p_route_id integer,
p_routenum integer,
p_ordertype text,
p_create_station_id integer,
p_create_stationtype text,
p_create_time text,
p_create_user_id integer,
p_tran_time text,
p_tran_user_id integer)
RETURNS integer AS
$BODY$
INSERT INTO ordermaster
(order_dt, customer_id, route_id, routenum, ordertype, create_station_id, create_stationtype, create_time,create_user_id,tran_time, tran_user_id)
values
(p_order_dt, p_customer_id, p_route_id, p_routenum, p_ordertype, p_create_station_id, p_create_stationtype, p_create_time, p_create_user_id, p_tran_time, p_tran_user_id)
returning orderline_id;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
请注意,我删除了未使用的order_id
参数,并将其重命名为其他两个参数-因为通常不建议使用与列名称相同的参数.
Note that I removed the unused order_id
parameter and renamed the other two parameters - because it is usually not a good idea to have parameters with the same name as columns.
然后在您的代码中可以使用如下功能:
Then in your code you can use the function like this:
PreparedStatement pstmt = con.prepareStatement("select insert_order(?,?)");
pstmt.setString(1, "foo");
pstmt.setInt(2, 42);
rs = pstmt.executeQuery();
if (rs.next()) {
System.out.println("Generated ID is: " + rs.getInt(1));
}
2.手动查询序列:
调用函数后,可以运行另一条语句来获取最后生成的序列值:
2. Query the sequence manually:
After calling your function, you can run another statement to obtain the last generated sequence value:
ResultSet rs = stmt.executeQuery("select lastval()");
if (rs.next()) {
System.out.println("Generated ID is: " + rs.getInt(1));
}
以上内容仅在该函数未插入多个表的情况下有效.如果是这样,则需要将currval()
与序列名一起使用:
The above will only work if the function doesn't insert into multiple tables. If it does, you need to use currval()
with the sequence name:
ResultSet rs = stmt.executeQuery("select currval('ordermaster.order_id_seq')");
if (rs.next()) {
System.out.println("Generated ID is: " + rs.getInt(1));
}
这篇关于java -postgresql在插入时最后插入的id不能获取的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!