CODEWITHSUNDEEP
javaspringoracle-databasejdbc
Gaurav
Gaurav

Reputation: 93

How to get generated ID after inserting new record in database table using Spring JDBCTemplate?

I know that this is an duplicated question but the other solutions on the other pages isn't working for me.

I have a Oracle database and I want to fetch inserted record ID (primary key) but I'm unable to do so. Below is the error and my code.

org.springframework.dao.DataRetrievalFailureException: The generated key is not of a supported numeric type. Unable to cast [oracle.sql.ROWID] to [java.lang.Number]

String query = "INSERT INTO JOBS (USERNAME, CREATED_ON, STATUS, JOBTYPE, DATA) " + "VALUES (?, ?, ?, ?, ?)";

try {

 KeyHolder holder = new GeneratedKeyHolder();

 jdbcTemplate.update(new PreparedStatementCreator() {

  @Override
  public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
   PreparedStatement ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
   ps.setString(1, "username");
   ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
   ps.setString(3, "status");
   ps.setString(4, "jobtype");
   ps.setString(5, "job-data");

   return ps;
  }
 }, holder);
 System.out.println("Holder tostring: " + holder.toString());
 System.out.println("Ran an update statement and got generated key. Key = " + holder.getKey().intValue());
 System.out.println("Key: " + holder.getKey().longValue());
 return true;
} catch (DataAccessException e) {
 System.err.println("Exception thrown inserting record into table. Error: " + e.getMessage());
 e.printStackTrace();
}

When I run the app in debug mode I'm seeing holder variable keyList value is: [{ROWID=AAAKy2AAAAALRgdAAD}] I'm not getting the inserted record Id.

Create table script is:

CREATE TABLE JOBS (
    ID          INTEGER                 GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
    USERNAME    VARCHAR2(20)            NOT NULL,
    CREATEDON   TIMESTAMP               NOT NULL,
    STATUS      VARCHAR2(10)            NOT NULL,
    JOBTYPE     VARCHAR2(15)             NOT NULL,
    DATA        VARCHAR2(1000 CHAR)     NOT NULL,

    PRIMARY KEY(ID)
);

Upvotes: 4

Views: 3649

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108937

When you use con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS), the Oracle JDBC driver will not return the value of the id column, but instead it will return the ROW_ID (a pseudo column that identifies a specific row), to allow you to retrieve the value yourself.

Historically the Oracle driver did it this way, because previous Oracle versions didn't have identity columns.

Instead you should use:

con.prepareStatement(query, new String[] { "ID" });

Which instructs Oracle to return the value of the specified column.

Upvotes: 6

Related Questions