prepared statement get inserted row

Question

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();

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

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.

Any advice?

Answer 1

Ellaborating a bit on Mark's idea in the question comments, I would suggest you to try

http://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#execute-java.lang.String-java.lang.String:A-

Being generated in a trigger, maybe your jdbc driver might need a little help to properly identify the generated key

Answer 2

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.

Assume that your database is Oracle.

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;

And call the above procedure from Java using callableStatement

Java code snippet

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);