准备好的语句插入行 [英] prepared statement get inserted row

问题描述

我正在使用准备好的语句通过以下方式进行插入:

I am using prepared statement to make insert in following way:

String query = "Insert into ROLE (ROLEID,ROLENAME,TYPEID,UPDATETIMESTAMP) values     (?,?,?,?)";
Class.forName(driver);
        conn = DriverManager.getConnection(url, userName, password);
        preparedStatement = conn.prepareStatement(query);
        preparedStatement.setInt(1, 0);
        preparedStatement.setString(2, roleName);
        preparedStatement.setInt(3, roleId);
        preparedStatement.setTimestamp(4,null);
        preparedStatement.executeUpdate();

此处ROLEID是主键，并通过某个触发器通过序列在内部进行更新.

Here ROLEID is the primary key and is getting updated internally by a sequence through some trigger.

我在这里的关注点是插入后，我需要获取已插入的ROLEID.我可以使用像select * from ROLEID desc的顺序这样的查询，但是如果我找出此准备好的语句使用的特定ID，那就更好了.

My concern here is after insert i need to fetch the ROLEID that has been inserted. I can use a query like select * from order by ROLEID desc, but would be better if i find out the specific id used by this prepared statement.

有什么建议吗?

推荐答案

另一种方法是使用数据库存储过程从Java进行插入和调用存储过程.存储过程应具有一个OUT参数，该参数不过是您的ROLEID.因此，您具有更大的灵活性，并将所有数据库逻辑保留在后端.

Alternate approach would be to use a database stored procedure to do the insertion and call stored procedure from Java. Stored procedure should have an OUT parameter which is nothing but your ROLEID. Thus you have greater flexibility and keep all database logic in backend.

假设您的数据库是Oracle.

Assume that your database is Oracle.

例如首先创建过程来插入并返回roleid

E.g. first create procedure to do insertion and return roleid

CREATE SEQUENCE role_seq;

CREATE OR REPLACE PROCEDURE p_proc (
   p_rolename          IN     T_ROLE.ROLENAME%TYPE,
   p_typeid            IN     T_ROLE.TYPEID%TYPE,       
   o_roleid            OUT T_ROLE.ROLEID%TYPE)
IS
BEGIN
   INSERT INTO t_role (roleid,
                     rolename,
                     typeid,
                     updatetimestamp)
        VALUES (role_seq.NEXTVAL,
                p_rolename,
                p_typeid,
                sysdate)
     RETURNING roleid
          INTO o_roleid;               
END;

然后使用callableStatement

Java代码段

CallableStatement callablestatement = null;

callablestatement = 
                    connection.prepareCall("{call p_proc(?,?,?)}");
callablestatement.setString(1, 'Test');
callablestatement.setString(2, 'TestType');
callablestatement.registerOutParameter(3, java.sql.Types.INTEGER);

callablestatement.executeUpdate();
int roleId= callablestatement.getInt(3);

这篇关于准备好的语句插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！