Get the inserted id of the record before or after execute the insert statement in sql

Question

I have this method in my DAO class to insert record to a table called idea this is my method:

    public long  addIdea(AddIdeaDto addIdeaDto, int userId) {

    Connection connection = null;

    PreparedStatement preparedStatement = null;
    try {
        connection = getConnection();



        preparedStatement = connection.prepareStatement(
                "INSERT INTO IDEA ( IDEA.I_ID,IDEA.I_NO,IDEA.I_APPROVER_NAME_CODE, IDEA.I_TITLE,IDEA.I_DESCRIPITION, IDEA.I_CREATED_DATE,IDEA.I_STATUS_CODE, "
                        + "IDEA.I_IS_CODE, IDEA.I_CONTRIBUTION_CODE, IDEA.I_POSITIVE_IMPACT, IDEA.I_SECOND_MEMBER_ID,IDEA.I_THIRD_MEMBER_ID,IDEA.I_FOURTH_MEMBER_ID,"
                        + "IDEA.I_FIFTH_MEMBER_ID, IDEA.I_POINTS,IDEA.I_CREATED_USER_ID)"
                        + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");


        preparedStatement.executeQuery("SELECT IDEA_SEQ.nextval FROM DUAL");

        // Set parameters
        preparedStatement.setObject(1, Types.NUMERIC);
        preparedStatement.setObject(2, Types.NUMERIC);

        preparedStatement.setObject(3, addIdeaDto.getApproverNameCode());

        preparedStatement.setString(4, addIdeaDto.getTitle());

        preparedStatement.setString(5, addIdeaDto.getDescription());
        preparedStatement.setDate(6, addIdeaDto.getCreatedDate() == null ? null
                : new java.sql.Date(addIdeaDto.getCreatedDate().getTime()));

        preparedStatement.setObject(7, addIdeaDto.getStatusCode());
        preparedStatement.setObject(8, addIdeaDto.getIsNewCode());

        preparedStatement.setObject(9, addIdeaDto.getContributionCode());

        preparedStatement.setString(10, addIdeaDto.getPositiveImpact());
        preparedStatement.setObject(11, addIdeaDto.getSecondMemberName());
        preparedStatement.setObject(12, addIdeaDto.getThirdMemberName());
        preparedStatement.setObject(13, addIdeaDto.getFourthMemberName());
        preparedStatement.setObject(14, addIdeaDto.getFifthMemberName());

        preparedStatement.setObject(15, addIdeaDto.getPoints());
        preparedStatement.setInt(16, userId);

        preparedStatement.executeQuery();
        return addIdeaDto.getIdeaId();

    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        try {
            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

actually what I want is after or before the insert statement I want to get the id (IDEA_SEQ.nextval) and save it in a value in order to use it as an input to insert in anther table.

For example, I insert this record : id = 1 , no = 1, approver code = 2, title = 'test'.............

I want this value id = 1 to use it in order to insert in table A, A_id = 33, IDEA.I_ID = 1, A_name ='testing'

how i can achieve it in properer way?

I update the code based on the comments that i receive but I did not achieve it

Take a look at this: https://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc — Devon_C_Miller, Nov 04 '18 at 13:20

Ori Marko · Answer 1 · 2018-11-04T11:27:02.607

0

Usually ID that need to be reuse can be handle using a previous and separate SQL query

 previousPreparedStatement = connection.prepareStatement(
            "select IDEA_SEQ.nextval as nextval from dual");

Result saved as a int or String parameter according to column (number or varchar) which is passed to the existing insert statement:

(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

Notice also an answer from DBA forum

you won't be able to use plain SQL to overcome this limitation: you will need some PL/SQL

edited Nov 04 '18 at 11:27

answered Nov 04 '18 at 11:04

Ori Marko

56,308
23
131
233

It will be like : String id = "select IDEA_SEQ.nextval as nextval from dual " and preparedStatement.setObject(1, id); – uma Nov 04 '18 at 11:14
Thank you, and when i set the parameter I will set it as string – uma Nov 04 '18 at 11:24
I will have to statements select and insert – uma Nov 04 '18 at 11:26
@wadhaalketbi or int, according to oracle column varchar or number – Ori Marko Nov 04 '18 at 11:26
both is int but I will save the first statement (select) as int then when I will set the parameter of second statement I will put this variable ? – uma Nov 04 '18 at 11:33
@wadhaalketbi yes, put the variable twice index 1 and 2 – Ori Marko Nov 04 '18 at 11:35
I will try it if it will work with, but this is what I want – uma Nov 04 '18 at 11:42
java.sql.SQLSyntaxErrorException: ORA-01722: invalid number – uma Nov 04 '18 at 11:44
@wadhaalketbi you should you `setInt` instead of `setObject(1, Types.NUMERIC)` – Ori Marko Nov 05 '18 at 07:28
it will be like setInt(1, Types.NUMERIC)??? And is right to have to prepare statements?? – uma Nov 05 '18 at 13:14
@wadhaalketbi `setInt(1, yourId)` , `yourId` should be returned from the new SQL to get the nextval – Ori Marko Nov 05 '18 at 13:17
so there is ResultSet, the 2 prepare statements it will be in the same method (addIdea) and the ResultSet of the Select statment, it will be the parameter of the I_ID ?? – uma Nov 05 '18 at 13:47

score 0 · Answer 2 · answered Nov 05 '18 at 04:47

0

A better way to handle this is the RETURNING INTO clause, which uses a single, atomic statement:

INSERT INTO mytable (id, col1, col2)
VALUES ( seq_id.nextval, c1, c2 )
RETURNING id INTO myval;

answered Nov 05 '18 at 04:47

eaolson

14,717
7
43
58

I add returning to my query it give me missing keyword – uma Nov 05 '18 at 05:56
`returning into` can only be used inside a PL/SQL block, not in a SQL query. – Nov 05 '18 at 07:39
I would argue that the best way to handle this is to turn it into a function that returns the new id. See #SmartDB on Twitter. Stop writing SQL! (See Feuerstein) – eaolson Nov 05 '18 at 15:39

score 0 · Answer 3 · answered Nov 05 '18 at 08:03

You can use PreparedStatement.getGeneratedKeys() to obtain the generated value. There is no need to use a separate statement:

You also can't prefix column names with the table name in list of columns of an INSERT statement.

String insert = 
     "INSERT INTO IDEA ( I_ID,I_NO,I_APPROVER_NAME_CODE, I_TITLE,I_DESCRIPITION, I_CREATED_DATE,I_STATUS_CODE, "
   + "I_IS_CODE, I_CONTRIBUTION_CODE, I_POSITIVE_IMPACT, I_SECOND_MEMBER_ID,I_THIRD_MEMBER_ID,I_FOURTH_MEMBER_ID,"
   + "I_FIFTH_MEMBER_ID, I_POINTS,I_CREATED_USER_ID)"
   + " VALUES (idea_seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";


preparedStatement = connection.prepareStatement(insertSql, new String[] {"I_ID"});
preparedStatement.setInt(1, ???); // don't know where the value for I_NO comes from
preparedStatement.setString(2, addIdeaDto.getApproverNameCode());
preparedStatement.setString(3, addIdeaDto.getTitle());
... other parameters
preparedStatement.executeUpdate();
ResultSet rs = preparedStatement.getGeneratedKeys();
long newId = -1;

if (rs.next()) {
  newId = rs.getLong("I_ID");
}

... use the NewId ...

The parameter new String[] {"I_ID"} for the prepareStatement() call tells the JDBC driver to return the generated value for that column. That value can be retrieved through getGeneratedKeys() which returns a ResultSet that contains one row for each inserted row (so exactly one in this case). The ID value can then be extracted from the ResultSet using the the usual getLong() (or getInt()) methods.

Get the inserted id of the record before or after execute the insert statement in sql

3 Answers3